Passing around database transactions

I'm not a huge fan of reflection-based ORMs, which is one of the reasons that I've been using sqlc and some service types for my business logic.

Sqlc is great. I define my migrations and my queries and with the POWER OF CODE GENERATION™ ... I get a bunch of go types and functions that perform my database queries. They don't always map directly to my actual data models though, sometimes they don't have every field or it's in another form that needs some additional massaging -- so I like to have a layer on top of the sqlc-generated stuff that makes interacting with the database easier.

Not only does this make the API to the data more friendly, I find that it can make testing easier too. My models don't really have much database stuff in them (save for an ID) and my raw queries are made safe with sqlc. Hooray for the power of abstractions!

One thing that is slightly challenging, or at least annoying, is when I need to work with transactions and compose my business-logic. Each function of my service layer needs to create a transaction or work within an existing transaction. So, how do I make it work without just copying the logic between the service functions?

Enter, my fancy-pants transaction function that I name (boringly enough) transaction.

type ctxKey int

const (
    ctxKeyTX ctxKey = iota
)
func transaction(ctx context.Context, fn func(context.Context, *dao.Queries) error) error {
    var (
        tx  *sql.Tx
        err error
        ok  bool
    )
    tx, ok = ctx.Value(ctxKeyTX).(*sql.Tx)
    if !ok {
        tx, err = global.db.BeginTx(ctx, nil)
        if err != nil {
            return err
        }
        ctx = context.WithValue(ctx, ctxKeyTX, tx)
        defer tx.Rollback()
    }

    err = fn(ctx, global.dao.WithTx(tx))
    if err != nil {
        return err
    }
    if ok {
        return tx.Commit()
    }
    return nil
}

Using it is easy enough, you just need to wrap the database bits of the service in a transaction. I've basically pulled the example below from my application. There are two functions UpdateCallsign and SaveSettings. Each one can be composed elsewhere in the application. By wrapping the actual logic bits -- I can share the actual database transaction if we're in one, otherwise a new one gets created.

func (s Account) UpdateCallsign(ctx context.Context, ...) error {
    return transaction(ctx, func(ctx context.Context, qtx *dao.Queries) error {
        // ....
        return qtx.UpdateCallsignForAccount(ctx, ...)
    })
}

func (s Account) SaveSettings(ctx context.Context, ...) error {
    return transaction(ctx, func(ctx context.Context, qtx *dao.Queries) error {
        // ....
        return qtx.UpdateSettingsForAccount(ctx, ...)
    })
}

Also, since the transaction is tied to a context at the beginning, if the http request (or any request really) is cancelled midway through -- the transactions all get rolled back.

That about does it, relatively simple. Just toss the transaction into the context and pull it out if it's available.

Oh, and the reason I am using s Account for the type and not a Account is because those are functions on a service. I've been using the single letter of the category: m for model, s for service, h for handler, e for event.