How to scan a QueryRow into a struct with pgx
Asked Answered
G

4

23

We are using a user struct with alot of fields as follow :

type user struct {
    ID       int    `json:"id,omitempty"`
    UUID     string `json:"uuid,omitempty"`
    Role     int    `json:"role,omitempty"`
    Name     string `json:"name,omitempty"`
    Surname  string `json:"surname,omitempty"`
    Phone    string `json:"phone,omitempty"`
    Email    string `json:"email,omitempty"`
    Street   string `json:"street,omitempty"`
    City     string `json:"city,omitempty"`
    Password string `json:"password,omitempty"`
}

And a function to get a user by its email :

func getUserByEmail(email string) (u user, err error) {
    row := db.Psql.QueryRow(
        context.Background(),
        "SELECT * FROM users WHERE email=$1",
        email)
    err = row.Scan(&u.ID, &u.UUID, &u.Role, &u.Name, &u.Surname, &u.Phone, &u.Email, &u.Street, &u.City, &u.Password)
    if err != nil {
        log.Fatal(err)
    }
    return
}

Is there a way to scan directly to a struct rather than all of its property ? Ideally :

row.Scan(&u)
Gyrostat answered 9/5, 2020 at 22:51 Comment(0)
C
19

Since version 5.1.0 PGX supports struct scanning.

type product struct {
    ID    int32
    Name  string
    Price int32
}

rows, _ := conn.Query(ctx, "select * from products")
products, err := pgx.CollectRows(rows, pgx.RowToStructByName[product])
if err != nil {
    fmt.Printf("CollectRows error: %v", err)
    return
}

for _, p := range products {
    fmt.Printf("%s: $%d\n", p.Name, p.Price)
}

You can name the fields using db-tags:

type Name struct {
    Last  string `db:"last_name"`
    First string `db:"first_name"`
}

Docs: https://pkg.go.dev/github.com/jackc/pgx/v5#RowToStructByName

Crocodile answered 17/8, 2023 at 16:51 Comment(2)
Hi @Niels Thanks for the solution, but this returns the result in a bad format, how can I format the result beautifully. (Example: in json?)Sequestration
And how to ignore no need field, on response? (Example password field of user) if you know it would be very helpful:). Thank you! PS: I try db:"-" not helped.Sequestration
D
28

There is another library scany. It works with pgx native interface and with database/sql as well:

package main

import (
    "context"

    "github.com/jackc/pgx/v4/pgxpool"

    "github.com/georgysavva/scany/pgxscan"
)

type User struct {
    ID    string
    Name  string
    Email string
    Age   int
}

func main() {
    ctx := context.Background()
    db, _ := pgxpool.Connect(ctx, "example-connection-url")

    var users []*User
    pgxscan.Select(ctx, db, &users, `SELECT id, name, email, age FROM users`)
    // users variable now contains data from all rows.
}

It's well tested and documented and has much fewer concepts than sqlx.

Disclaimer, I am the author of this library.

Dor answered 30/7, 2020 at 15:22 Comment(3)
Can you please show how to use scany with query parameters? I searched the library but could not find an example on how to use query parameters; otherwise the query could be subject to sql injection attacks.Pectize
@Pectize Hi. You work with query parameters with scany the same way as you would work with them using your database library directly. You either query database rows from db library on your own, then scany stays away from the SQL processing and query parameters completely. Or you use one of the .Select() or .Get() scany functions and pass your parameters normally, scany passes them to the underlying db library which is responsible for escaping them so no injection attacks are possible.Dor
This answer is outdated; since PGX 5.1.0 the PGX lib does support struct scanning. See https://mcmap.net/q/558245/-how-to-scan-a-queryrow-into-a-struct-with-pgxCrocodile
C
19

Since version 5.1.0 PGX supports struct scanning.

type product struct {
    ID    int32
    Name  string
    Price int32
}

rows, _ := conn.Query(ctx, "select * from products")
products, err := pgx.CollectRows(rows, pgx.RowToStructByName[product])
if err != nil {
    fmt.Printf("CollectRows error: %v", err)
    return
}

for _, p := range products {
    fmt.Printf("%s: $%d\n", p.Name, p.Price)
}

You can name the fields using db-tags:

type Name struct {
    Last  string `db:"last_name"`
    First string `db:"first_name"`
}

Docs: https://pkg.go.dev/github.com/jackc/pgx/v5#RowToStructByName

Crocodile answered 17/8, 2023 at 16:51 Comment(2)
Hi @Niels Thanks for the solution, but this returns the result in a bad format, how can I format the result beautifully. (Example: in json?)Sequestration
And how to ignore no need field, on response? (Example password field of user) if you know it would be very helpful:). Thank you! PS: I try db:"-" not helped.Sequestration
E
2

Not with plain database/sql but there is an extension library called sqlx that builds on top of database/sql and adds some useful extensions such as row unmarshalling into structs (including nested), slices and arrays:

type Place struct {
    Country       string
    City          sql.NullString
    TelephoneCode int `db:"telcode"`
}

rows, err := db.Queryx("SELECT * FROM place")
for rows.Next() {
    var p Place
    err = rows.StructScan(&p)
}

See documentation and look for StructScan.

Embroideress answered 10/5, 2020 at 3:11 Comment(0)
M
1

If google brought you here and you just don't want to deal with struct but rather expecting a direct map[string]interface{} type output, this might be the way:

query := `SELECT * FROM product WHERE id = @id`
bindVars := make(map[string]interface{})
bindVars["id"] = id

/*
    with pgx.NamedArgs
    @link https://github.com/jackc/pgx/issues/387#issuecomment-1107666716
*/
jsonbody, err := json.Marshal(bindVars)
if err != nil {
    return nil, err
}
var namedArgs pgx.NamedArgs
if err := json.Unmarshal(jsonbody, &namedArgs); err != nil {
    return nil, err
}
jsonSQL := fmt.Sprintf("SELECT row_to_json(t) FROM (%s) t", query)
var data interface{}
err = conn.QueryRow(context.Background(), jsonSQL, namedArgs).Scan(&data)
if err != nil {
    return nil, err
}
if data == nil {
    return nil, errors.New("no document was found")
}
Martini answered 3/2, 2023 at 10:17 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.