1. Use a single DB connection, it will pool automatically
2. Use this pattern for all single row queries:
err = db.QueryRow(`...`, ...).Scan(&...)
if err == sql.ErrNoRows {
// Handle no rows
} else if err != nil {
// Handle actual error
}
// All fine
3. Use this pattern for all multi-row queries where you want to return a slice of structs containing the row values. Note that it is fine to call rows.Close() as soon as possible in addition to deferring it, defer takes care of handling it whenever something goes wrong and the explicit call returns the connection as soon as possible:
rows, err := db.Query(`...`, ...)
if err != nil {
// Handle connection or statement error
}
defer rows.Close()
things := []rowStruct{}
for rows.Next() {
thing := rowStruct{}
err = rows.Scan(
&thing.id,
&thing.value,
)
if err != nil {
// Handle row parsing error
}
things = append(things, thing)
}
err = rows.Err()
if err != nil {
// Handle any errors within rows
}
rows.Close()
4. Use transactions as serial things, if you need to call another query whilst in a loop where you can't rows.Close(), then read the rows into a slice and range over the slice. You must never have two queries running in the same transaction... so code to do one thing before you do another, and be mindful of this if you are passing the transaction to other funcs.
An extra bit of info:
5. defer doesn't just have to be used to call rows.Close(), if you want to know when things happen you can wrap the defer and log:
On which point, beware there are some theoretically uncaught errors, for example tx.Rollback() can return an error http://golang.org/pkg/database/sql/#Tx.Rollback but if you have called it using defer tx.Rollback() after creating a transaction you'll never know. I hope that the only reason that might error is that something has already ended the transaction, but there is definitely scope for deferred finalisation within a func to cause errors that you might miss and it's worth considering the pattern above if you have any mysterious behaviour going on.
With defers and named return values, it is actually possible to alter the return value inside of a defer. I had to do this recently to properly log an error (to abort in the calling code). Also to do with database operations of course:
If you know where the deadlocks are likely to occur, consider turning to a sync.Mutex and wrapping the statement in a lock. It will cause other goroutines to wait until the lock is free.
It all depends where the deadlocks are though, you can easily achieve them in the Go code as well as the database queries.
I'm not around much today as I'm with a client this morning and lunch, but if you're stuck later I may well be in https://gophers.slack.com/ . Happy to help out if I can, as I'm sure most others will be.
http://go-database-sql.org/
The second speaks of database connections:
http://jmoiron.net/blog/gos-database-sql/
The general approach:
1. Use a single DB connection, it will pool automatically
2. Use this pattern for all single row queries:
3. Use this pattern for all multi-row queries where you want to return a slice of structs containing the row values. Note that it is fine to call rows.Close() as soon as possible in addition to deferring it, defer takes care of handling it whenever something goes wrong and the explicit call returns the connection as soon as possible: 4. Use transactions as serial things, if you need to call another query whilst in a loop where you can't rows.Close(), then read the rows into a slice and range over the slice. You must never have two queries running in the same transaction... so code to do one thing before you do another, and be mindful of this if you are passing the transaction to other funcs.An extra bit of info:
5. defer doesn't just have to be used to call rows.Close(), if you want to know when things happen you can wrap the defer and log:
On which point, beware there are some theoretically uncaught errors, for example tx.Rollback() can return an error http://golang.org/pkg/database/sql/#Tx.Rollback but if you have called it using defer tx.Rollback() after creating a transaction you'll never know. I hope that the only reason that might error is that something has already ended the transaction, but there is definitely scope for deferred finalisation within a func to cause errors that you might miss and it's worth considering the pattern above if you have any mysterious behaviour going on.