TL;DR: Working with db transactions in Go should be simple, so I made some utilities to help with that. You can see the gist containing all the code here.
The database/sql package has everything you need in order to work with db
transactions for most vendors. Typically, you’ll write something like this:
While this code works, there are a few of things I think could be better about it.
Every transaction has to worry about the details
The flexibility of handling everything yourself can be nice, but its also a burden sometimes. For example, if one statement fails, the next move is to rollback, almost always.
Similarly, I would be worried about forgetting to commit the transaction at some point (wouldn’t be the first time).
This kind of code seems like it should be a primitive in your codebase
When you deal with common error scenarios (timeouts, retries, etc.) it’s generally a good practice to introduce some primitives in your codebase to handle these.
For example, a function like FetchJSON(url string, params ...interface{}) error can be used to set open/request
timeouts and handle any application specific errors in the same way everywhere. Then you just call this method whenever
you need to fetch some JSON.
Managing db transactions seems to fit this case well.
Not all “errors” are covered
In the example above, what happens if one of the calls panics? This might not be a problem, but depending on the
IsolationLevel for the transaction, failure to explictly rollback could be an issue.
A better solution would recover from the panic, rollback, and then re-panic.
The first thing we’ll want to do is create a func to wrap the transaction logic so we don’t need to do this
everywhere. There are several ways to do this, but I generally use something like this.
Transaction Helper
Updated code for main
This is better (IMO), but as we copy/paste this around the codebase, there’s an aweful lot of repetition still happening.
Ideally, we’d be able to supply a set of commands to be run, and have them all take place in the same transaction.
We can create the concept of a pipeline here that will run statements one by one within a transaction and chain the last insert id along with each call.
Updated code for main
This seems much easier to read, and is certainly simple to replicate across your application. Obviously, this doesn’t cover every possible scenario, but putting something like this in place can really make your application’s code nicer to read and ensure that you’re handling error cases consistently.
I’m definitely interested in opinions, so please leave a comment if you’ve got one!