go database/sql tx example

tx.go

package main

import (
	"context"
	"database/sql"
	"fmt"

	_ "github.com/lib/pq"
)

type user struct {
	name    string
	answers int
}

// shortcut error catcher for demonstration
func panicOnErr(context string, err error) {
	if err != nil {
		fmt.Println(context, err)
		panic(err)
	}
}

// schemaSet sets the schema
func schemaSet(tx *sql.Tx, schema string) {
	_, err := tx.Exec(fmt.Sprintf(`set search_path="%s"`, schema))
	panicOnErr("schema", err)
}

// inserter inserts rows
func inserter(tx *sql.Tx, name string, answers int) {
	sql := `INSERT INTO test (name, answers) VALUES ($1, $2)`
	_, err := tx.Exec(sql, name, answers)
	panicOnErr("inserter", err)
}

// inserterQuery inserts rows and reports the result
func inserterQuery(tx *sql.Tx, name string, answers int) {
	sql := `INSERT INTO test (name, answers) VALUES ($1, $2) RETURNING *`
	output := tx.QueryRow(sql, name, answers)
	u := user{}
	err := output.Scan(&u.name, &u.answers)
	panicOnErr("inserterQuery scan", err)
	fmt.Printf("%#v\n", u)
}

// lister reads rows
func lister(tx *sql.Tx) {
	sql := `SELECT * FROM test`
	users := []user{}
	rows, err := tx.Query(sql)
	panicOnErr("get rows", err)
	for rows.Next() {
		u := user{}
		err = rows.Scan(&u.name, &u.answers)
		panicOnErr("row", err)
		users = append(users, u)
	}
	fmt.Printf("%d : %#v\n", len(users), users)
}

// transact runs a set of SQL statements in a transaction
func transact(db *sql.DB, schema string) {

	fmt.Println("transacting for schema:", schema)

	// start a transaction
	ctx := context.Background()
	tx, err := db.BeginTx(ctx, nil)
	panicOnErr("tx setup", err)

	// defer a rollback in case anything fails
	defer tx.Rollback()

	schemaSet(tx, schema)
	inserter(tx, "user1", 5)
	inserterQuery(tx, "user2", 7)
	lister(tx)

	// explicit rollback
	err = tx.Rollback()
	panicOnErr("rollback err", err)
}

func main() {
	creds := fmt.Sprintf(
		"host=%s port=%d user=%s "+"password=%s dbname=%s sslmode=disable",
		"127.0.0.1", 5432, "so", "sosueme", "so")

	// connect
	db, err := sql.Open("postgres", creds)
	panicOnErr("opener", err)
	defer db.Close()

	for _, schema := range []string{"schema1", "schema2"} {
		transact(db, schema)
	}
}