How do you handle transactions, NULLs and "no rows" correctly with database/sql?

Transactions pin a single connection; all statements must use the *sql.Tx, not db (using db inside a tx runs on another connection and can deadlock with MaxOpenConns=1).

No rows: QueryRow(...).Scan returns sql.ErrNoRows; check with errors.Is. NULLs: scanning NULL into a string errors — use sql.NullString, a pointer, or the generic sql.Null[T] (Go 1.22).

func transfer(ctx context.Context, db *sql.DB, from, to int64, amt int64) (err error) {
	tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
	if err != nil {
		return err
	}
	defer tx.Rollback() // no-op if committed

	var bal int64
	var note sql.Null[string]
	err = tx.QueryRowContext(ctx,
		"SELECT balance, note FROM accounts WHERE id = $1 FOR UPDATE", from).Scan(&bal, &note)
	if errors.Is(err, sql.ErrNoRows) {
		return fmt.Errorf("account %d not found", from)
	} else if err != nil {
		return err
	}
	if bal < amt {
		return errors.New("insufficient funds")
	}
	if _, err = tx.ExecContext(ctx, "UPDATE accounts SET balance = balance - $1 WHERE id = $2", amt, from); err != nil {
		return err
	}
	if _, err = tx.ExecContext(ctx, "UPDATE accounts SET balance = balance + $1 WHERE id = $2", amt, to); err != nil {
		return err
	}
	return tx.Commit()
}

Always use placeholders (never fmt.Sprintf SQL) to prevent injection; placeholder syntax is driver-specific ($1 Postgres, ? MySQL).