Please add a PGX adapter

[veqryn]

Is your feature request related to a problem? Please describe.
Right now the default for Jet is to let postgres arrays get generated into golang strings.
You can override the generated types, but then you'd also have to implement your own scan/value functions too.
Using PGX directly (github.com/jackc/pgx/v5) lets us do things like scanning postgres arrays directly to a slice ([]int64), and many other great features.
Unfortunately, Jet only accepts the standard library database/sql interface, which forces us to use github.com/jackc/pgx/v5/stdlib

Describe the solution you'd like
Other scanning libraries such as scany and ksql provide an adapter that lets their library use both the standard library interface and also PGX directly.
It would be great if Jet provided similar.

[go-jet]

Right now the default for Jet is to let postgres arrays get generated into golang strings.
You can override the generated types, but then you'd also have to implement your own scan/value functions too.

Check #380, this PR should add full array support.

Other scanning libraries such as scany and ksql provide an adapter that lets their library use both the standard library interface and also PGX directly.

I don't see how it is possible to have adapter that converts pgx to standard database/sql calls. For instance standard database connection has this method:

QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)

All sql.Rows fields are private and there is no public constructor. Thus no adapter is possible. The only way is to add a new set of methods just for pgx.

Also, note that pgx does provide standard database interface - "github.com/jackc/pgx/v4/stdlib".

[veqryn]

You'd have to look at scany and ksql to see how they do it. It might not be an adapter, it might be a copy of the implementation or something else.

[safaci2000]

At least for scany, they simply re-implement the entire service, so it's duplicated to some degree. They implement their own interface Rows{} and then have an implementation using sql.Rows and another one using pgx.Rows.

The interface itself is generic and has no reference to sql.Rows, which I don't believe is the case for Jet.

qrm.Queryable return sql.Rows, which would have to be modified to be a more generic object/iterator as a response rather than sql.db

[DevX86]

@go-jet Is this possible in any capacity? We need it to get rid of database/sql.

[DevX86]

Scany seem the way to go but I love this library and want to see it flourish.

[houtn11]

@DevX86 Why do you want to get rid of database/sql?

[DevX86]

Massive speed improvements, even selecting 15,000 records I saw a 3x improvement due to the binary transfer and native bindings.

[DevX86]

I'm trying to refactor into scany, my friends we were spoiled by go-jet. I can't live without it, especially when it comes to complex queries. @go-jet @houten11 Is there honestly nothing we can do for native pgx? (no database/sql)

[body20002]

You actually can use them together, use go-jet to generate the sql and pgx to execute it
Here's a simple example

selectStmt := SELECT(Table.AllColumns).FROM(Table).WHERE(Table.ID.EQ(1))).LIMIT(1)

func query[T any](db *pgxpool.Pool, ctx context.Context, stmt Statement) (*T, error) {
	query, args := stmt.Sql()

	row, _ := db.Query(ctx, query, args...)
	data, err := pgx.CollectOneRow(row, pgx.RowToAddrOfStructByPos[T])

	return data, err
}

# and just use it like this
data, err := query[Table](pool, ctx, selectStmt)

[DevX86]

@body20002

I need something for complex structs like

type A struct {
    B struct {
        C
        D E `db:"F"`
        G H `db:"I"`
        J []struct {
            K
            L struct {
                M
                N struct {
                    O `alias:"P"`
                    Q []struct {
                        R `alias:"S"`
                    }
                }
            } `db:"T"`
            U []V `db:"W"`
        } `db:"X"`
        Y Z  `db:"AA"`
        AB AC `db:"AD"`
    }
}

---

Worked on something like that for hours last night combining scany with go jet, I don't like the results.
I flatten the struct then parse it like this.

var resultViews []views.ResultView
for _, tempGroup := range groupedTemps {
    firstTemp := tempGroup[0]

    // Extract embedded structs
    modelA := utils.ExtractEmbeddedStruct[models.ModelA](firstTemp)
    modelB := utils.ExtractEmbeddedStruct[models.ModelB](firstTemp)
    modelC := utils.ExtractEmbeddedStruct[models.ModelC](firstTemp)
    modelD := utils.ExtractEmbeddedStruct[models.ModelD](firstTemp)
    modelE := utils.ExtractEmbeddedStruct[models.ModelE](firstTemp).Resolve()

    // Collect items and build views
    modelFItems := utils.ExtractEmbeddedSlice[Temp, models.ModelF](tempGroup)
    modelGItems := utils.ExtractEmbeddedSlice[Temp, models.ModelG](tempGroup)
    modelHItems := utils.ExtractEmbeddedSlice[Temp, models.ModelH](tempGroup)
    modelIItems := utils.ExtractEmbeddedSlice[Temp, models.ModelI](tempGroup)

    // Continue processing with the extracted data
    // For example, building a result view:
    resultView := views.NewResultView(
        &modelA,
        modelFItems,
        &modelB,
        &modelC,
        modelE,
        // ... other parameters
    )

    resultViews = append(resultViews, *resultView)
}

And this which has a struct plus slice, but we have structs into slices into structs etc. So this wont scale.

type ProjectData struct {
    models.Project       `db:"projects"`
    ProjectRecords       []models.ProjectRecord `db:"project_records" json:"project_records"`
}

func (r *ResearchRepository) GetFilteredProjectData(ctx context.Context, researcherID uuid.UUID, projectID *uuid.UUID) ([]views.ProjectData, error) {
    ctx, span := researchTracer.Start(ctx, "ResearchRepository.GetFilteredProjectData")
    defer span.End()

    // SQL statement builder
    stmt := sqlbuilder.
        SELECT(
            projectTable.AllColumns,
            projectRecordTable.AllColumns).
        FROM(researcherTable.
            INNER_JOIN(projectTable, projectTable.ResearcherID.EQ(researcherTable.ID)).
            INNER_JOIN(projectDetailTable, projectDetailTable.ProjectID.EQ(projectTable.ID)).
            INNER_JOIN(projectTable, projectDetailTable.ProjectID.EQ(projectTable.ID)).
            INNER_JOIN(projectRecordTable, projectRecordTable.ProjectID.EQ(projectTable.ID))).
        WHERE(researcherTable.ID.EQ(sqlbuilder.UUID(researcherID)))

    fmt.Println(stmt.DebugSql())

    if projectID != nil {
        stmt = stmt.WHERE(projectTable.ID.EQ(sqlbuilder.UUID(*projectID)))
    }

    // Define a temporary structure to hold results
    type Temp struct {
        models.Project       `db:"projects"`
        models.ProjectRecord `db:"project_records"`
    }

    var temps []Temp
    if err := pgxscan.Select(ctx, r.db, &temps, stmt.DebugSql()); err != nil {
        return nil, utils.SpanErr(span, err)
    }

    groupedTemps, err := utils.GroupByID(temps)
    if err != nil {
        return nil, err
    }

    var projectDataList []views.ProjectData
    for _, tempGroup := range groupedTemps {
        firstTemp := tempGroup[0]

        projectData := views.ProjectData{
            Project:        utils.ExtractEmbeddedStruct[models.Project](firstTemp),
            ProjectRecords: utils.ExtractEmbeddedSlice[Temp, models.ProjectRecord](tempGroup),
        }

        projectDataList = append(projectDataList, projectData)
    }

    return projectDataList, nil
}

[DevX86]

@go-jet For reference, scany can't handle maps, or nested structs, only flat structs and flat slices.

[dropwhile]

@DevX86 Do you really need scany? As @body20002 pointed out in their example, since v5 pgx has nice support for scanning on its own (CollectRows, CollectOneRow, RowToStructByName, RowToStructByNameLax, etc).
reference: https://ectobit.com/blog/pgx-v5-3/

I used to use scany, and just converted to using plain pgx after v5 came out.

[body20002]

@DevX86 I haven't tried it on complex queries and certainty not a query that complex, but It should work with nested structs given the struct is correct or corresponds to the query return values

I think pgx docs states so

[DevX86]

@dropwhile @body20002 Hate to ping you guys but I'm in the middle of a major refactor of our database layer. As long as it's pgx native I don't mind which I use (or something new) my main concern are these complex queries. Can you post an example of a nested struct with slices on it?