Does pgx support scanning into structs?

[mrdulin]

something like: https://godoc.org/github.com/jmoiron/sqlx#StructScan

And struct tags like this:

type TenantModule struct {
	Id   string `db:"id"`
	Name string `db:"type"`
        Channels Channel `json:"channels"`
}
type Channel struct {
	Channels []string
}

Or this:

type TenantModule struct {
	Id   string `sql:"id"`
	Name string `sql:"type"`
}

I didn't find any information on https://github.com/jackc/pgx/blob/master/query_test.go file and documentation.
There are only examples which scan into the basic data type like int, float

Please give an example of how to scan rows into a struct. Thanks

[jackc]

pgx does not have anything like sqlx. But you can use sqlx with pgx when pgx is used as a database/sql driver.

[mvrhov]

Also the sqlc in next version will do what you need albeit in a bit different way that you expect.

[ftjfo]

pgx does not have anything like sqlx. But you can use sqlx with pgx when pgx is used as a database/sql driver.

While this works, it is a somewhat annoying to have to drop down from sqlx.DB to pgx.Conn by AcquireConn() in order to use the very much faster bulk-copy (CopyFrom()). In libpq, you could feed a sqlx Prepare statement with the output of pq.CopyIn() in order do do bulk-inserts.

[georgysavva]

@mrdulin here is the library exactly for what you asked: https://github.com/georgysavva/scany
It's feature-rich and supports pgx native interface.

Disclaimer: I am the author of this library.

[omsec]

Well, well..this should really be part of the core, since its fundamentals. Yet another dependecy to a private person.

[BigBoulard]

maybe there ... https://github.com/randallmlough/pgxscan

[bfontaine]

See also pgx.RowToStructByPos.

[elllot]

In addition to the API @bfontaine pointed out, pgx.RowToStructByName is available in pgx v5.

[tamis-laan]

@elllot How do I apply pgx.RowToStructByName onto a single row returned by QueryRow?

[elllot]

It's been a while since I was looking into this, but you could use a combination of Query & RowToAddrOfStructByName (variant of RowToStructByName) for a single row:

// Supplied from somewhere.
var dbPool *pgxpool.Pool
rows, err := dbPool.Query(context.Background(), someQuery, ...someQueryArgs)
if err != nil {
  return nil, err
}

// Assumes the returned row only has a single hit. StructToFill is the target struct. 
p, err := pgx.CollectOneRow(rows, pgx.RowToAddrOfStructByName[StructToFill])

You can check the API definitions to see what the differences are between the API variants. I might be misremembering, but I remember there wasn't a way to use QueryRow with RowToStructByName.

[tamis-laan]

@elllot yup that's how I'm using it now, tried looking for a way to use a single row QueryRow but couldn't find any. Thanks for affirming.

[jackc]

@tamis-laan

As mentioned by @elllot Query + CollectOneRow is the way in v5. But you can make it a little more concise. You can skip checking the error from Query and only check the error from CollectOneRow. When Query returns an error it always still returns a Rows with that error, and CollectOneRow will return that original error. e.g.

// Supplied from somewhere.
var dbPool *pgxpool.Pool
rows, _ := dbPool.Query(context.Background(), someQuery, ...someQueryArgs)
// Assumes the returned row only has a single hit. StructToFill is the target struct. 
p, err := pgx.CollectOneRow(rows, pgx.RowToAddrOfStructByName[StructToFill])
if err != nil {
 return nil, err
}

Also, for an even higher level interface, check out SelectRow from my personal pgx utility library.

[robfordww]

in that pgx utility lib, I miss something like this:

func StructToValueMap(s interface{}) map[string]any {
	res := make(map[string]any)
	v := reflect.ValueOf(s)
	t := reflect.TypeOf(s)
	for i := 0; i < t.NumField(); i++ {
		if t.Field(i).Tag.Get("db") != "" {
			res[t.Field(i).Tag.Get("db")] = v.Field(i).Interface()
		} else {
			res[strings.ToLower(t.Field(i).Name)] = v.Field(i).Interface()
		}
	}
	return res
}

It is useful in when inserting "structs":

id, err = db.InsertRowReturning(ctx, conn, pgx.Identifier{"public", "missingreports"}, db.StructToValueMap(sp),
			"id",pgx.RowTo[int64])