Writing a Go package that interacts with a relational
data store such as Postgres is full of messiness.
Those of us who appreciate the strong-typedness of Go probably also appreciate
the strong-typedness of SQL, and vice versa. Unfortunately, communication
between Go and SQL is less than ideal. This is due partly to the mostly
free-form text format of data exchange (queries) and partly to some subtle
differences in data types.
Database nulls are a particular headache, leading to the contortions of defining
types such NullString,
NullInt64, and
NullBool, and an extra check
is required every time you want distinguish a null from a zero value.
Why not use an ORM? There has been
a lot written
on this already,
but in a nutshell, the level of generality required means that
pretty much everything is an interface{}
with runtime checks to cast stuff into the types you need, and at this point
we’ve lost the benefits of Go’s strong typing and may as well write our whole
application in Ruby.
I’ve found that programmers who appreciate the power and control that comes from
writing in a low-level compiled language such as Go also appreciate the power
can control that comes from writing queries yourself in SQL.
So what’s the problem, really?
The real headache of Go + SQL is the
volume of boilerplate code that goes with even relatively simple operations.
(1) Run a query that doesn’t return any results.
_, err := db.Exec(query, ...args)
if err != nil {
return err
}
(1a) Run a query that doesn’t return any results, but we want to know how many
rows were changed.
res, err := db.Exec(query, ...args)
if err != nil {
return err
}
count, err := res.RowsAffected()
if err != nil {
return err
}
(1b) Run a query that doesn’t return any results, and we’d like to catch and
process integrity violations (e.g. duplicate entry on a unique field). This one
requires some database-specific code; the example here is for Postgres.
_, err := db.Exec(query, ...args)
duplicate := false
if err != nil {
if pgerr, ok := err.(*pq.Error); ok {
duplicate = pgerr.Code.Class().Name() == "integrity_constraint_violation"
}
if !duplicate {
return err
}
}
(1c) Run a query that doesn’t return any results, and we’d like to catch and
process data exceptions (e.g. number out of range). This uses the same strategy as 1b and can be combined with it.
(2) Run a query that returns one row.
err := db.QueryRow(query, ...args).Scan(&arg1, &arg2, ... )
if err != nil {
return err
}
(2a) Run a query that returns one row, and we’d like to catch and process the
case where no rows are returned.
err := db.QueryRow(query, ...args).Scan(&arg1, &arg2, ... )
noRows := err == ErrNoRows
if err != nil && !noRows {
return err
}
(3) Run a query that returns multiple rows.
rows, err := db.Query(query, ...args)
if err != nil {
return err
}
defer rows.Close()
for rows.Next() {
err := rows.Scan(&arg1, &arg2, ... )
if err != nil {
return err
}
}
err = rows.Err()
if err != nil {
return err
}
None of these is particularly bad as far as boilerplate goes, but unless we’re
writing an ORM (and we’ve already decided we’re not), we’re going to have tens,
perhaps hundreds of these scattered throughout our application. Add to that an
other if err != nil every time we start a transaction, and I’m thinking
there’s got to be a better way.
Organizing database access around high-level functionality
We would like to follow the
unit of work
pattern and create something akin to the
session model
of SQLAlchemy.
A simple example of a unit of work is a password reset, which checks for an
email match, and then generates, saves, and returns a reset code. This will
involve a minimum of two queries, which need to be in the same transaction.
(Much more complicated units of work are possible, of course, both read-only
and read-write.)
Our goal then is to find a way to have just one copy of all the boilerplate above and be able to substitute queries and argument lists as needed.
I’m going to propose that it’s straightforward to implement such a thing Go
by defining a custom transaction handler which extends
the one in database/sql.
This is done within the package that uses it.
type Tx struct {
sql.Tx
}
We extend sql.Tx with methods to (a) convert all database errors to panics so
that we can catch and process them all in one place, and (b) easily iterate over
result sets.
To accomplish (a), we add the methods MustExec, MustQuery, and MustQueryRow.
These are identical to Exec, Query, and QueryRow except that they panic
instead of returning an error code. Also, in the case of MustQuery and MustQueryRow,
they return custom Rows and Row objects that have similar extensions.
To accomplish (b), we add the method Each to the custom Rows object returned
by MustQuery. Method Each iterates over the result set and calls a
callback function for each row.
The ourError type is used to wrap errors that we want to convert back to error
codes. It distinguishes them from other kinds of panics (e.g. out of memory).
type ourError struct {
err error
}
func (tx Tx) MustExec(query string, args ...interface{}) sql.Result {
res, err := tx.Exec(query, args...)
if err != nil {
panic(ourError{err})
}
return res
}
func (tx Tx) MustQuery(query string, args ...interface{}) *Rows {
rows, err := tx.Query(query, args...)
if err != nil {
panic(ourError{err})
}
return &Rows{*rows}
}
func (tx Tx) MustQueryRow(query string, args ...interface{}) *Row {
row := tx.QueryRow(query, args...)
return &Row{*row}
}
The custom Row and Rows types are defined analogously.
Row is extended with a MustScan method:
type Row struct {
sql.Row
}
func (row Row) MustScan(args ...interface{}) {
err := row.Scan(args...)
if err != nil {
panic(ourError{err})
}
}
Rows is extended with a MustScan method and also with the Each iterator
described above.
type Rows struct {
sql.Rows
}
func (rows Rows) MustScan(args ...interface{}) {
err := rows.Scan(args...)
if err != nil {
panic(ourError{err})
}
}
func (rows *Rows) Each(f func(*Rows)) {
defer rows.Close()
for rows.Next() {
f(rows)
}
err := rows.Err()
if err != nil {
panic(ourError{err})
}
}
Now to make it all work, we define a custom transaction function. It
sets up the transaction, provides the custom transaction handler to our
callback, and then catches the panics.
func Xaction(db *sql.DB, f func(*Tx)) (err error) {
var tx *sql.Tx
tx, err = db.Begin()
if err != nil {
return
}
defer func() {
if r := recover(); r != nil {
if ourerr, ok := r.(ourError); ok {
// This panic of from tx.Fail() or the equivalent. Unwrap it,
// process it, and return it as an error code.
tx.Rollback()
err = ourerr.err
if err == sql.ErrNoRows {
err = ErrDoesNotExist
} else if pgerr, ok := err.(*pq.Error); ok {
switch pgerr.Code.Class().Name() {
case "data_exception":
err = ErrInvalidValue
case "integrity_constraint_violation":
// This could be lots of things: foreign key violation,
// non-null constraint violation, etc., but we're generally
// checking those in advance. As long as our code is in
// order, unique constraints will be the only things we're
// actually relying on the database to check for us.
err = ErrDuplicate
}
}
} else {
// not our panic, so propagate it
panic(r)
}
}
}()
f(&Tx{*tx}) // this runs the queries
tx.Commit()
return
}
This covers all of our boilerplate needs except for (1a) above.
To accommodate (1a), we could extend sql.Result the same way we extended
the others, but I haven’t really needed it yet, so I’ll leave it as an
exercise for the reader.
One final method that’s there just to make everything neat and tidy is a Fail
method on the transaction which can be used to return an arbitrary error.
func (tx Tx) Fail(err error) {
panic(ourError{err})
}
The result
Our application code is now a lot neater.
err := Xaction(func(tx *Tx) {
// Run a query that doesn't return any results.
tx.MustExec(query1, ...args)
// Run a query that returns one row.
tx.MustQueryRow(query2, ...args).MustScan(&arg1, &arg2, ... )
// Run a query that returns multiple rows.
tx.MustQuery(query3, ...args).Each(func(r *Rows) {
r.MustScan(&arg1, &arg2, ... )
})
})
if err != nil {
switch err {
case ErrDoesNotExist:
// query2 returned no rows
case ErrInvalidValue:
// data exception
case ErrDuplicate:
// integrity violation
default:
return err
}
}
And since this is an extension to the stock transaction handler rather than
a replacement for it, we can still use the original non-must methods for
any edge case that might require a different kind of error handling.