How do you handle transactions with the repository pattern?
Previously, I showed how to put a small interface between your service logic and your storage layer so the service doesn't know whether it's talking to sqlc, raw SQL, or anything else. The interface looked like this:
A service depends on Store, a concrete postgres package satisfies it, and in tests you swap in an in-memory fake. The service never imports database/sql.
In the same Reddit thread, user xinoiP asked:
How would you handle transactions with this approach? Since they are very specific to SQL. I tend to use context and store an optional transaction in there that can be used on the implementation of that interface. So, sqlc checks the context, if there is a transaction, uses it etc. I just wonder how you would handle it.
If each method on the interface runs independently, there's no way to make two writes atomic. Say RegisterBook needs to insert a book and an audit log entry, and both must commit or roll back together.
The key is something sqlc already gives you. It generates a DBTX interface that both sql.DB and sql.Tx satisfy:
If your store struct accepts DBTX instead of sql.DB, you can construct a store backed by either a connection pool or a live transaction. Same struct, same methods, different underlying executor. That means the interface can offer a Tx method that hands the caller a transactional version of itself:
The Postgres implementation of Tx starts a sql.Tx, wraps it in a fresh BookStore, and passes that into the callback. If the callback returns an error, it rolls back. Otherwise it commits:
NewBookStore(tx) works because the struct field is DBTX, and sql.Tx satisfies that interface. No new type, no wrapper. The Get, Create, and CreateAuditLog methods on this transactional store run their queries against the tx automatically.
The service uses Tx when it needs atomicity. Everything inside the callback goes through the transactional store:
Both writes commit or roll back together. RegisterBook never sees sql.Tx, sql.DB, or anything from database/sql. If the audit log insert fails, the book insert is rolled back too.
For tests, Tx just calls the function directly against the in-memory store:
No real transaction needed. The test exercises the same service code as production. If you need to verify actual commit/rollback behavior, swap the in-memory store for something like SQLite.
Back to xinoiP's approach of storing a *sql.Tx in the context: it works, but it leaks storage into the service layer through the back door. The service has to set up the transaction in context before calling the store, which means it knows a SQL transaction exists. That's the coupling the interface was supposed to prevent.
With the callback approach, the service says "run these operations atomically" and the store decides how. Swap Postgres for DynamoDB tomorrow and the service code doesn't change - you just implement Tx differently in the new storage package.
The full working example with an HTTP server and SQLite is on GitHub.
See also:
Discussion in the ATmosphere