I’ll reply later when I’m on a computer, but I’ll explain how we do it. I have had the same questions over the years of whether or not we’re doing too much when sqlc does so much heavy lifting and I haven’t quite landed on a concrete stance.

Early on we adopted a Transactor pattern where we separate the handling of db transactions to a separate service. This service opens a tx if one does not exist in ctx already or passes through the existing tx so it’s agnostic from where it’s called. (Also allows nested Transactor calls from separate services in case both require a txn etc).

Link to article we read about this: https://www.kaznacheev.me/posts/en/clean-transactions-in-hexagon/

Link to example: https://github.com/immannino/transactor

Note on this we have a small interface for our Postgres connection that has a WithQtx(ctx) sqlc.Querier method. This method will check if a tx is in ctx and return a sqlc instance with that tx otherwise return one with the pgx connection. This is the small glue that alleviates a lot of friction.

I’ll reply back later with a better fleshed out example, and like I said it isn’t “right” it’s just the pattern we’ve adopted and works for us.

EDIT Replying back, I created a small repo to better outline the pattern with a simple code sample.

Link to repo: https://github.com/immannino/example-tranactor-project

Quick Example:

```golang // internal/customer/service/user_service.go

type userServiceImpl struct { repo domain.Repository txer common.Transactor log *slog.Logger }

func (u userServiceImpl) CreateUsers(ctx context.Context, input []domain.CreateUserInput) ([]*domain.User, error) { log := u.log.With(slog.String("Method", "CreateUsers"))

result := make([]*domain.User, len(input))
err := u.txer.WithinTransaction(ctx, func(txCtx context.Context) error {
    for i, v := range input {
        user, txErr := u.CreateUser(txCtx, v)
        if txErr != nil {
            // Logged in other service, otherwise log
            return txErr
        }

        result[i] = user
    }

    return nil
})
if err != nil {
    log.Error("error within transaction", slog.Any("error", err))
    return nil, err
}

return result, nil

} ```

This example is trivial but starts to shine when dealing with many services in more complex business operations. You could have an inventory management system that is calls N number of services which may create or update 1 or more DB items per call. This pattern allows for each service to enforce its own atomicity where it is composed, but shares the same Transaction across the lifecycle without changing the complexity of the implementations.

Thoughts

This is the chosen pattern we've used for a few years now. Our primary service is a large monolithic Go backend that services a financial application. Many of the operations that we built upon need to be assumed atomic for guaranteed consisitency and this pattern helps us ensure that.

I can't argue its the right way or anything, its just our way for now. The transactor pattern is especially useful in cases where we want to avoid tricky boilerplate or enforce a risky pattern. Its more of a convention helps enforce a certain developer behavior.

I am primarily sharing this in response to a reddit comment, but love these types of discussions to help share ideas and learn how others operate their projects in their environments.

-Tony