My day job currently includes writing and maintaining golang services. It’s not a bad language, and it certainly forces you to understand that everything will fail, far more obviously than Java’s checked exceptions. I’m not going to argue about the utility of checking/returning error instances, but I do find myself writing the same code over and over again, especially when I’m handling any SQL operations. So I’ve come up with a couple of templates to remove a bunch of the boilerplate code for transactions and for looping over SQL query result rows.

Transaction wrapper

Kicks off the transaction, executes the callback function, commits the transaction if the callback does not return an error, or rolls-back the transaction if the callback returns an error. If the transaction rollback fails, return both the error from the callback and the failed rollback error.

import (
	"database/sql"
	"fmt"
)

type txFn func(*sql.Tx) error

func withTX(db *sql.DB, fn txFn) error {
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	err = fn(tx)
	if err != nil {
		if ex := tx.Rollback(); ex != nil {
			return fmt.Errorf("rollback for '%w' failed with: %v", err, ex)
		}
		return err
	}
	return tx.Commit()
}

Use it like this:

const insertUserSQL = `
insert into users (
  uuid, name, slug, password, email, status, created_by, created_at, updated_by, updated_at
) values (
  ?, ?, ?, ?, ?, 'locked', ?, NOW(), ?, NOW()
)
`

const insertUserRoleSQL = `
insert into roles_users (user_id, role_id) values (?, ?)
`

func (h *holder) CreateAuthor(name string, email string, pwd string, adminID int64) error {
	pwHash, err := hashPassword(pwd)
	if err != nil {
		return err
	}
	roleID, err := h.authorID()
	if err != nil {
		return err
	}
	return withTX(h.db, func(tx *sql.Tx) error {
		r, err := tx.Exec(
			insertUserSQL,
			uuid.New(),
			name,
			createSlug(name),
			pwHash,
			email,
			adminID,
			adminID,
		)
		if err != nil {
			return err
		}
		userID, err := r.LastInsertId()
		if err != nil {
			return err
		}
		_, err = tx.Exec(insertUserRoleSQL, userID, roleID)
		return err
	})
}

Wrapper to iterate over query result rows

During the execution of a query and parsing of its results there is a lot of error checking at each step, and it all gets very noisy to read, and quite error prone to write out each time. This wrapper returns a partial function that is then applied to an eachRowFn callback to scan every row in the result set.

import "database/sql"

type scanFn func(dest ...interface{}) error

type eachRowFn func(row scanFn) error

type partialQuery func(row eachRowFn) error

func queryRows(db *sql.DB, query string, args ...interface{}) partialQuery {
	return func(row eachRowFn) error {
		rows, err := db.Query(query, args...)
		if err != nil {
			return err
		}
		defer rows.Close()
		for rows.Next() {
			if err := row(rows.Scan); err != nil {
				return err
			}
		}
		return rows.Err()
	}
}

Use it like this:

const listUsersSQL = `
select u.id, u.name, u.status, r.name
from users u, roles_users ru, roles r
where u.id = ru.user_id
and r.id = ru.role_id
order by u.name
`

func (h *holder) ListUsers() ([]User, error) {
	var users []User
	err := queryRows(h.db, listUsersSQL)(func(row scanFn) error {
		user := User{}
		if err := row(&user.ID, &user.Name, &user.Status, &user.Role); err != nil {
			return err
		}
		users = append(users, user)
		return nil
	})
	return users, err
}

I usually stick these into a single file somewhere near my database code, and get on with writing the rest of the service without worrying that I’ve again copied & pasted something broken ;-)

Hope this helps.

EDIT: (2021-06-07) These are now available as part of my gotools project on GitHub.