How to scan a QueryRow into a struct with pgx

Question

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)

score 28 · Answer 1 · answered Jul 30 '20 at 15:22

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.

answered Jul 30 '20 at 15:22

Georgy Savva

659
7
14

1

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. – Monty Mar 10 '22 at 21:41
@Monty 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. – Georgy Savva Mar 11 '22 at 11:56
This answer is outdated; since PGX 5.1.0 the PGX lib does support struct scanning. See https://stackoverflow.com/a/76923598/2044661 – Niels Krijger Aug 17 '23 at 16:52

blami · Answer 2 · 2020-05-10T03:45:59.343

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.

score 1 · Accepted Answer · answered Aug 17 '23 at 16:51

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

goldsky · Answer 4 · 2023-02-03T16:32:31.450

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")
}

How to scan a QueryRow into a struct with pgx

4 Answers4