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

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.

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