Returning last inserted record or id in postgresql using sqlx prepared statement in a transaction

sqlx_last_inserted_id.go

package main

import (
	"fmt"
	"log"

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

var db *sqlx.DB
var names = []string{
	"Liam", "Olivia",
	"Noah", "Emma",
	"Oliver", "Charlotte",
	"Elijah", "Amelia",
	"James", "Ava",
	"William", "Sophia",
	"Benjamin", "Isabella",
	"Lucas", "Mia",
	"Henry", "Evelyn",
	"Theodore", "Harper",
}

type User struct {
	Id   int64  `db:"id"`
	Name string `db:"name"`
}

func init() {
	var err error
	db, err = sqlx.Connect("postgres", "dbname=sample sslmode=disable")
	if err != nil {
		log.Fatalln(err)
	}
}

func main() {
	defer db.Close()

	// migrations
	db.Exec("drop table if exists users;")
	db.Exec("create table users (id serial primary key, name text);")
	db.Exec("drop table if exists address;")
	db.Exec("create table address (id serial primary key, user_id integer, address text);")

	// example
	Example0()
}

// Example0 use transaction + prepared statement + return last inserted
func Example0() {
	var users []User

	userStmt, err := db.Preparex("insert into users (name) values ($1) returning *;")
	if err != nil {
		log.Fatalln(err)
	}

	addressStmt, err := db.Preparex("insert into address (user_id, address) values ($1, $2);")
	if err != nil {
		log.Fatalln(err)
	}

	tx, _ := db.Beginx()

	for _, name := range names {
		var user User

		// err := userStmt.Get(&user, name)
		err := tx.Stmtx(userStmt).Get(&user, name)
		if err != nil {
			tx.Rollback()
			log.Fatalln(err)
		}

		address := fmt.Sprintf("0x%040d", user.Id)
		// _, err = addressStmt.Exec(user.Id, address)
		_, err = tx.Stmtx(addressStmt).Exec(user.Id, address)
		if err != nil {
			tx.Rollback()
			log.Fatalln(err)
		}

		// fmt.Printf("%v\n", user)
		users = append(users, user)
	}
	tx.Commit()

	pp.Println(users)
}

// Example1 returns whole object
func Example1() {
	stmt, err := db.Preparex("insert into users (name) values ($1) returning *;")
	if err != nil {
		log.Fatalln(err)
	}
	for _, name := range names {
		var user User
		err := stmt.Get(&user, name)
		if err != nil {
			log.Fatalln(err)
		}
		fmt.Printf("%v\n", user)
	}
}

// Example2 returns only id
func Example2() {
	stmt, err := db.Preparex("insert into users (name) values ($1) returning id;")
	if err != nil {
		log.Fatalln(err)
	}
	for _, name := range names {
		var id int64
		err := stmt.Get(&id, name)
		if err != nil {
			log.Fatalln(err)
		}
		fmt.Printf("[%d] %s\n", id, name)
	}
}

// Example3 returns only id
func Example3() {
	for _, name := range names {
		var id int64
		err := db.QueryRow("insert into users (name) values ($1) returning id;", name).Scan(&id)
		if err != nil {
			log.Fatalln(err)
		}
		fmt.Printf("[%d] %s\n", id, name)
	}
}