How to scan returning struct with github.com/jmoiron/sqlx

Question

How can I scan SQL returning values into a struct with github.com/jmoiron/sqlx?

I tried the following approach:

package main

import (
    "log"

    "github.com/jmoiron/sqlx"
    _ "github.com/lib/pq"
)

const CreateUserSQL = `INSERT INTO users
(
    first_name,
    last_name
)
VALUES (
    :first_name,
    :last_name
)
RETURNING
(
    first_name,
    last_name
)
`

type User struct {
    FirstName string `db:"first_name"`
    LastName  string `db:"last_name"`
}

func main() {
    db, err := sqlx.Connect("postgres", "postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }

    user := &User{
        FirstName: "John",
        LastName:  "Doe",
    }

    err = db.QueryRowx(CreateUserSQL, user).StructScan(user)
    if err != nil {
        log.Fatal(err)
    }
}

But it's not working, I get an error:

2024/02/04 17:46:21 sql: converting argument $1 type: unsupported type main.User, a struct

I've search on information and read the official documentation, but didn't find anything.

Answer 1

As of writing this, you can not do what you want with QueryRowx() directly. QueryRowx() takes arguments list, not named arguments. The workaround is to use NamedQuery() + StructScan() combination. Here is how you can do this:

...

const CreateUserSQL = `INSERT INTO users
(
    first_name,
    last_name
)
VALUES (
    :first_name,
    :last_name
)
RETURNING first_name, last_name` // notice this change here

...

func main() {
    // notice. do not use pointer here. otherwise StructScan() will fail
    user := User{
        FirstName: "John",
        LastName:  "Doe",
    }

    rows, err := db.NamedQuery(CreateUserSQL, user)
    if err != nil {
        log.Fatal(err)
    }

    for rows.Next() {
        err = rows.StructScan(&user)
        if err != nil {
            // handle error
        }
    }
}

Answer 2

I'm not 100% sure about this particular SQL library, but most that I've used have only allowed read operations with a function called Query. Operations that cause a change usually need an Exec function. In this package, maybe you need to use MustExec() function instead.

https://pkg.go.dev/github.com/jmoiron/sqlx#DB.MustExec