sqltx

Elegant, defer-based transaction management for your Go code

sqltx is a minimal Go library for reliable, database-agnostic transaction management. It builds on database/sql to unify transactions and savepoints, providing a consistent API that aims to work identically across PostgreSQL, MySQL, and SQLite with no dependencies and no surprises.

Why?

Go’s database/sql package offers powerful primitives, but handling nested transactions and savepoints reliably across different SQL dialects can be challenging. sqltx provides a database-agnostic abstraction layer for PostgreSQL, MySQL, and SQLite, exposing a unified API for safe, composable transaction management in Go.

More importantly, sqltx follows a defer-first philosophy: transactions are resources, not callbacks. You start one, defer its completion, and write code that flows top-to-bottom like any other Go function.

Design Philosophy

The Never-Nester Principle

Most transaction helpers in Go look like this:

err := RunTx(ctx, db, func(tx *sql.Tx) error {
    // your logic here
    return nil
})

That pattern hides control flow inside closures and makes nested operations clumsy.

sqltx takes the opposite approach. It embraces Go’s native defer mechanism to manage transactional scope:

tx, release, err := sqltx.Begin(ctx, db)
if err != nil {
    return err
}
defer release(&errOut)

Each transaction (or savepoint) becomes a resource with a deterministic lifetime, making your code explicit, composable, and easy to reason about — without callback pyramids or control-flow gymnastics.

Caveats and Dialect Differences

sqltx relies on standard SQL semantics for BEGIN, COMMIT, ROLLBACK, and SAVEPOINT. while the library behaves consistently across drivers, the underlying databases implement these features with subtle differences worth being aware of:

Database	Behavior	Notes
PostgreSQL	✅ Full support for nested transactions using named savepoints.	Considered the reference implementation. All operations are atomic and reversible.
MySQL / MariaDB	⚠️ Partial support. Only the InnoDB engine supports SAVEPOINT.	DDL statements may cause implicit commits. If you mix DDL and DML inside one transaction, nesting guarantees are limited.
SQLite	✅ Full support, but savepoints are emulated within a single connection.	Nested savepoints work, but concurrent access to the same database file may serialize writes differently than on client-server systems.
Other SQL dialects	❌ Not guaranteed.	Drivers that do not expose SAVEPOINT will fall back to top-level transactions only.

Additional notes

• Error semantics: a rollback to a savepoint discards changes made after that point but keeps the transaction open. sqltx follows this strictly, which means inner scopes may fail without aborting the outer scope.
• Connection pooling: ensure that your *sql.DB or driver connection pool does not automatically reuse connections mid-transaction; database/sql manages this correctly, but custom pools may not.
• DDL statements: schema-changing operations (CREATE TABLE, ALTER, etc.) often force implicit commits in several databases. such statements cannot be nested reliably under savepoints.
• Performance: each nested level incurs a SAVEPOINT and RELEASE round-trip. in high-throughput code, prefer shallow nesting or batch operations.

Features

• Unified API for both transactions and savepoints
• Smart fallback to savepoints within transactions for nesting
• Defer-friendly design prevents resource leaks and nesting in your code
• Zero dependencies beyond the standard library
• Tested with real-world database scenarios
• Minimal overhead with no reflection, small wrappers and slim interfaces

Installation

go get -u github.com/0x5a17ed/sqltx

Quick Start

package main

import (
	"context"

	"github.com/0x5a17ed/sqltx"
)

func MyDBOperation(ctx context.Context, dbq sqltx.DBQ) (errOut error) {
	// Start an inner transaction or savepoint.
	tx, leaveFn, err := sqltx.Begin(ctx, dbq)
	if err != nil {
		return err
	}
	defer leaveFn(&errOut)

	// Do some work...
	_, err = tx.ExecContext(ctx, `INSERT INTO cities (name) VALUES (?)`, "Paris")
	if err != nil {
		return err
	}

	return nil
}

How It Works

The primary interface of the sqltx package is a single Begin function that intelligently handles both SQL transactions and savepoints:

func Begin(ctx context.Context, q Querier) (Tx, LeaveFn, error)

• If q supports transaction initiation (like *sql.DB), a new transaction is started.
• If q is already a transaction encapsulating *sql.Tx, a savepoint is created.
• If q is a transaction encapsulating a savepoint, a nested savepoint is created.

The returned LeaveFn clean-up function automatically commits or rolls back the transaction/savepoint appropriately and transparently based on whether the code encounters a panic or an error through observing the error return value of the caller.

Commits and rollbacks apply only to the outermost transaction or specifically requested transactions/savepoints and their nested savepoints.

This design allows for seamless nesting of database operations without the need for manual error handling or explicit transaction management while ensuring that the database is always in a consistent state.

Examples

Nested Transactions

func complexOperation(ctx context.Context, dbq sqltx.DBQ) (errOut error) {
	// Start the outer transaction
	tx1, releaseTx1, err := sqltx.Begin(ctx, dbq)
	if err != nil {
		return err
	}
	defer releaseTx1(&errOut)

	// Do some work...
	_, err = tx1.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS cities (name TEXT NOT NULL UNIQUE);`)
	if err != nil {
		return err
	}

	for _, item := range []string{"Paris", "London", "Berlin"} {
		err = (func() error {
			// Start an inner transaction (becomes a savepoint)
			tx2, releaseTx2, err := sqltx.Begin(ctx, tx1)
			if err != nil {
				return err
			}
			defer releaseTx2(&errOut)

			// Do more work...
			_, err = tx2.ExecContext(ctx, `INSERT INTO cities (name) VALUES (?)`, item)
			return err
		})()

		if err != nil {
			_, _ = fmt.Fprintf(os.Stderr, "error: %s\n", err)
		}
	}

	return nil
}

With Transaction Options

func readOnlyOperation(ctx context.Context, db *sql.DB) (errOut error) {
	// Use read-only transaction.
	tx, releaseTx, err := sqltx.Begin(ctx, sqltx.WithOptions(db, sqltx.OptionReadOnly))
	if err != nil {
		return err
	}
	defer releaseTx(&errOut)

	// Read operations ...

	return nil
}

Works beautifully with sqlc

sqltx pairs naturally with sqlc, the SQL-first code generator for Go.

Both tools share the same philosophy:

• No ORMs
• No reflection or runtime magic
• Predictable, type-safe, and composable design

Use sqlc to generate your query layer and sqltx to manage its transactional scope:

func CreateUser(ctx context.Context, db *sql.DB, arg *sqlc.CreateUserParams) (errOut error) {
    tx, releaseTx, err := sqltx.Begin(ctx, db)
    if err != nil {
        return err
    }
    defer releaseTx(&errOut)

    q := sqlc.New(tx)
    err = q.CreateUser(ctx, *arg)
    return err
}

License

This project is licensed under the 0BSD Licence. See the LICENCE file for details.

---

Made with ❤️ for elegant database operations