使用sqlx重新获取Postgres中新插入的行

I use https://github.com/jmoiron/sqlx to make queries to Postgres.

Is it possible to get back the whole row data when inserting a new row?

Here is the query I run:

result, err := Db.Exec("INSERT INTO users (name) VALUES ($1)", user.Name)

Or should I just use my existing user struct as the source of truth about the new entry in the database?

PostgreSQL supports RETURNING syntax for INSERT statements.

Example:

INSERT INTO users(...) VALUES(...) RETURNING id, name, foo, bar

Documentaion: https://www.postgresql.org/docs/9.6/static/sql-insert.html

The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted (or updated, if an ON CONFLICT DO UPDATE clause was used). This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number. However, any expression using the table's columns is allowed. The syntax of the RETURNING list is identical to that of the output list of SELECT. Only rows that were successfully inserted or updated will be returned.

Here is docs about transaction of sqlx:

The result has two possible pieces of data: LastInsertId() or RowsAffected(), the availability of which is driver dependent. In MySQL, for instance, LastInsertId() will be available on inserts with an auto-increment key, but in PostgreSQL, this information can only be retrieved from a normal row cursor by using the RETURNING clause.

So I made a complete demo for how to execute transaction using sqlx, the demo will create an address row in addresses table and then create a user in users table using the new address_id PK as user_address_id FK of the user.

package transaction

import (
    "database/sql"
    "github.com/jmoiron/sqlx"
    "log"
    "github.com/pkg/errors"
)
import (
    "github.com/icrowley/fake"
)

type User struct {
    UserID int `db:"user_id"`
    UserNme string `db:"user_nme"`
    UserEmail string `db:"user_email"`
    UserAddressId sql.NullInt64 `db:"user_address_id"`
}

type ITransactionSamples interface {
    CreateUserTransaction() (*User, error)
}

type TransactionSamples struct {
    Db *sqlx.DB
}

func NewTransactionSamples(Db *sqlx.DB) ITransactionSamples {
    return &TransactionSamples{Db}
}

func (ts *TransactionSamples) CreateUserTransaction() (*User, error) {
    tx := ts.Db.MustBegin()
    var lastInsertId int
    err := tx.QueryRowx(`INSERT INTO addresses (address_id, address_city, address_country, address_state) VALUES ($1, $2, $3, $4) RETURNING address_id`, 3, fake.City(), fake.Country(), fake.State()).Scan(&lastInsertId)
    if err != nil {
        tx.Rollback()
        return nil, errors.Wrap(err, "insert address error")
    }
    log.Println("lastInsertId: ", lastInsertId)

    var user User
    err = tx.QueryRowx(`INSERT INTO users (user_id, user_nme, user_email, user_address_id) VALUES ($1, $2, $3, $4) RETURNING *;`, 6, fake.UserName(), fake.EmailAddress(), lastInsertId).StructScan(&user)
    if err != nil {
        tx.Rollback()
        return nil, errors.Wrap(err, "insert user error")
    }
    err = tx.Commit()
    if err != nil {
        return nil, errors.Wrap(err, "tx.Commit()")
    }
    return &user, nil
}

Here is test result:

☁  transaction [master] ⚡  go test -v -count 1 ./...
=== RUN   TestCreateUserTransaction
2019/06/27 16:38:50 lastInsertId:  3
--- PASS: TestCreateUserTransaction (0.01s)
    transaction_test.go:28: &transaction.User{UserID:6, UserNme:"corrupti", UserEmail:"reiciendis_quam@Thoughtstorm.mil", UserAddressId:sql.NullInt64{Int64:3, Valid:true}}
PASS
ok      sqlx-samples/transaction        3.254s