litex

README

LiteX

Shared SQLite plumbing for Go services: tuned connection setup, transaction helpers, embedded migrations, and small query builders. Pure Go via modernc.org/sqlite, no cgo.

Extracted from years of production use and made public so its improvements are easy to share. Inspired by Ben Johnson's WTF Dial and its approach to structuring Go applications around a domain package with SQLite-backed implementations.

Not an ORM

LiteX is not an ORM, and it has no ambition to become one. There is no struct-to-table mapping, no reflection, no relationship graph, and no query DSL to learn. You write SQL.

What it removes is the minimal, repetitive boilerplate around SQL: opening tuned read/write connections, scoping transactions, running migrations, and assembling the filtered SELECT/UPDATE strings that every service otherwise rewrites by hand. The query builders are thin string helpers; you still supply your own columns, WHERE clauses, and row scans.

Raw SQL stays first-class and is never discouraged. Exec, Query, QueryRow, and Find all take plain SQL and arguments, and BaseTX.TX() hands you the underlying *sql.Tx for anything the helpers do not cover. If a query reads better as hand-written SQL, write it as hand-written SQL.

Features

• Split read/write pools - a single writer connection (SQLite allows only one) plus a pool of read-only connections, each with sensible pragmas (WAL, busy_timeout, foreign keys, synchronous=NORMAL, mmap).
• BEGIN IMMEDIATE writes - the write connection takes its lock up front, avoiding SQLITE_BUSY_SNAPSHOT errors that busy_timeout will not retry.
• Embedded migrations - versioned migrations/N.sql files applied in order against PRAGMA user_version, with foreign keys safely disabled during table rebuilds, an integrity check before commit, and a VACUUM afterwards when disk space allows.
• Transaction helpers - CommitRead, CommitWrite, and a buffered CommitBatch, each adapting a base transaction into your own project-specific type.
• Query builders - BuildQuery, BuildComplexQuery (joins and GROUP BY), UpdateQuery, and Where, driven by a Filter that stays out of your JSON (every field is json:"-").
• Structured logging - every statement logs through zerolog.

Install

go get github.com/robtme/litex

Quick start

The raw primitives, using an in-memory database. See Recommended pattern for how real applications wrap these.

ctx := context.Background()
logger := zerolog.New(os.Stdout)

// Use litex.MemoryDSN for an in-memory database, or a file path such as "data/app.db".
db := litex.NewDB(litex.MemoryDSN, logger)
if err := db.Open(); err != nil {
	panic(err)
}
defer db.Close()

// migrations is an embed.FS (or any fs.FS) holding migrations/*.sql.
if err := db.Migrate(ctx, migrations); err != nil {
	panic(err)
}

// The newTx callback adapts the base transaction; here it passes the *litex.BaseTX straight through.
err := db.CommitWrite(ctx,
	func(b *litex.BaseTX) any { return b },
	func(tx any) error {
		_, err := tx.(*litex.BaseTX).Exec(ctx, `INSERT INTO users (id, email) VALUES (?, ?)`, "u1", "a@example.com")
		return err
	},
)

Migrations

Place versioned SQL files under a migrations/ directory and embed them. Files are named <version>.sql, where version is a positive integer; an optional _description may follow (1.sql, 1_init.sql, 20260716_add_users.sql). Migrate discovers the files that exist, orders them by version, applies any newer than the database's PRAGMA user_version in a single transaction, and records the highest version applied. Versions need not be contiguous, so plain integers and later date-based versions (YYYYMMDD) can coexist.

migrations/
  1.sql
  2.sql

-- migrations/1.sql
CREATE TABLE users
(
    id      TEXT PRIMARY KEY,
    created TIMESTAMP NOT NULL,
    updated TIMESTAMP NOT NULL,
    email   TEXT      NOT NULL UNIQUE,
    name    TEXT      NOT NULL DEFAULT '',
    role    TEXT      NOT NULL DEFAULT 'member'
);

Foreign key enforcement is disabled for the duration of the migration transaction so table-rebuild patterns (create new, copy, drop old, rename) do not cascade-delete child rows, then re-enabled and verified with PRAGMA foreign_key_check before commit.

Migrations are forward-only by design: there are no down or rollback scripts. To undo a change, add a new migration that reverses it; in development, delete the database file and start clean. Do not edit a migration once it has been applied, since Migrate keys off PRAGMA user_version and skips any file at or below the current version.

If the database's version is newer than the newest migration a build ships, Migrate returns an error rather than run against a schema the build does not recognise, catching an older binary deployed against a database a newer one already migrated.

Recommended pattern

For real applications, follow the WTF Dial layout: a domain package declares your models and transaction interfaces, and a sqlite package implements them by wrapping LiteX. Wrapping the transaction helpers once gives callers typed transactions instead of any, and your CRUD methods hang off those transaction types using plain SQL and the query helpers.

// Wrap *litex.Service so callers depend on your package and get typed transactions.
type Service struct {
	*litex.Service
}

type ReadTX struct{ *litex.BaseTX }
type WriteTX struct{ *ReadTX }

func (s *Service) CommitWrite(ctx context.Context, fn func(tx *WriteTX) error) error {
	return s.Service.CommitWrite(
		ctx,
		func(base *litex.BaseTX) any { return &WriteTX{ReadTX: &ReadTX{BaseTX: base}} },
		func(tx any) error { return fn(tx.(*WriteTX)) },
	)
}

// CreateUser hangs off your write transaction and uses plain SQL.
func (w *WriteTX) CreateUser(ctx context.Context, u *User) error {
	_, err := w.Exec(ctx, `INSERT INTO users (id, email) VALUES (?, ?)`, u.ID, u.Email)
	return err
}

See the example/ directory for a complete, runnable application: domain interfaces, the full sqlite implementation, CRUD via the query builders, and batched writes with CommitBatch.

Batched writes

CommitBatch buffers writes in memory and flushes them to a real transaction on a timer (and on Close), for high-volume, latency-tolerant inserts. Queue statements with QueueExec instead of Exec. See example/ for a worked QueueUser method.

QueueExec only fills the transaction's own list; those statements reach the shared buffer when the CommitBatch callback returns nil. A callback that returns an error writes nothing, as with CommitWrite.

Past that point the buffer is service-wide, not per-CommitBatch: a flush commits everything buffered in that window as one transaction, so one failing statement rolls back and drops every entry in it, including other callers' writes. Because QueueExec returns before the write runs, it cannot report that failure; register OnBatchFlushError to observe async flush errors, and check the error returned by Close (which flushes any remainder on shutdown). Use CommitWrite for writes whose failure must be attributable to their own caller.

A flush that cannot open its transaction at all (the single write connection busy with a migration, SQLITE_BUSY past busy_timeout) puts its entries back for the next flush, since nothing was attempted. A failure after a statement has run drops the batch, since SQLite will reject that statement again.

Filtering

litex.Filter supplies limit, ordering, keyset pagination, and grouping to the query builders.

type Filter struct {
	Offset              any    // Keyset cursor: excludes rows ordered past this value on the OrderBy column.
	GroupBy             string // Comma-separated columns to GROUP BY.
	OrderBy             string // Column to order by; defaults to the primary field.
	Limit               int    // Max rows to return; <= 0 omits the LIMIT clause (all rows).
	ExactMatchByDefault bool   // Make operator-less clauses match the whole value; defaults to substring.
	OrderAsc            bool   // Order ascending; defaults to descending.
}

The zero value returns all rows ordered by the primary field descending. LiteX never caps or truncates silently, so if your API needs a maximum, clamp Limit before building the query.

Offset is keyset pagination, not SQL OFFSET: it seeks on the column the rows are ordered by, becoming WHERE <OrderBy> < ? (or > when OrderAsc is set), so pass the last row's value for that column, not its ID. With OrderBy unset that column is the primary field. For stable paging the ordering column should be unique, otherwise ties at a page boundary may be skipped.

Where operators

Where(field, operator...) builds a field <op> ? fragment. Three forms are special-cased by the builder:

You write	Emitted SQL	Meaning
Where("name")	name LIKE ? with %value%, or name = ?	follows Filter.ExactMatchByDefault (substring unless set)
Where("name", litex.OpSubstring)	name LIKE ? with %value%	always substring
Where("id", litex.OpExact)	id = ?	always whole-value equality
Where("age", ">")	age > ?	passed through as-is

Substring matching silently over-matches numeric columns, since 12 matches 1, 2 and 123, so keep it to free text. A rewrite applies only where the operator is directly followed by a placeholder, so multi-value clauses (IN, BETWEEN) and comparisons against literals (deleted = 0) pass through untouched. A single clause may carry several rewritable operators, and each wraps the value bound to it.

If most clauses in a query are exact, set Filter.ExactMatchByDefault instead of repeating OpExact:

// role and status match whole values; only name is a substring search.
filter := litex.Filter{ExactMatchByDefault: true}

where = append(where, litex.Where("role"), litex.Where("status"))
where = append(where, litex.Where("name", litex.OpSubstring))

The setting only decides what a plain = means, so OpSubstring and OpExact pin individual clauses either way.

Both operators are only rewritten by the query builders. A clause carrying one that you execute directly, without passing it through BuildQuery or BuildComplexQuery, runs as-is and neither errors: SQLite reads == as a synonym for =, and parses name =~ ? as name = ~?, a comparison against the bitwise complement of the bound value, which silently matches nothing. Build clauses with Where and pass them to a builder.

Substring values are matched literally. Any % or _ in the bound value is escaped, so a search term typed by a user cannot act as a wildcard.

Identifiers you pass in are interpolated, not parameterized. primaryField and the Filter fields OrderBy and GroupBy are validated as simple or dotted column identifiers (id, users.id), so expressions and quoted identifiers such as COALESCE(updated, created) or "order" are rejected at query-build time. table, fields, joins and BuildComplexQuery's groupBy argument are unvalidated: they may hold expressions, but must be trusted, developer-controlled values, never request data.

License

MIT.

Star History

Documentation

Overview

Package litex provides shared SQLite utilities including connection setup, transaction management, and query building helpers.

Index

• Constants
• func BuildComplexQuery(table string, primaryField string, fields string, filter *Filter, ...) (string, []any, error)
• func BuildQuery(table string, primaryField string, fields string, filter *Filter, ...) (string, []any, error)
• func UpdateQuery(table string, fn func(fields []string, args []any) ([]string, []any)) (string, []any, error)
• func Where(field string, operator ...string) string
• type BaseTX
  • func (b *BaseTX) Exec(ctx context.Context, stmt string, args ...any) (sql.Result, error)
  • func (b *BaseTX) ExecPrepared(ctx context.Context, stmt string, rows [][]any) error
  • func (b *BaseTX) Find(ctx context.Context, stmt string, args []any, ...) error
  • func (b *BaseTX) Logger() *zerolog.Logger
  • func (b *BaseTX) Query(ctx context.Context, stmt string, args ...any) (*sql.Rows, error)
  • func (b *BaseTX) QueryRow(ctx context.Context, stmt string, args ...any) *sql.Row
  • func (b *BaseTX) TX() *sql.Tx
• type BatchTX
  • func (b *BatchTX) Logger() zerolog.Logger
  • func (b *BatchTX) QueueExec(_ context.Context, stmt string, args ...any) error
• type Filter
• type Service
  • func NewDB(dsn string, logger zerolog.Logger) *Service
  • func (s *Service) Close() error
  • func (s *Service) CommitBatch(_ context.Context, newTx func(*BatchTX) any, fn func(any) error) error
  • func (s *Service) CommitRead(ctx context.Context, newTx func(*BaseTX) any, fn func(any) error) error
  • func (s *Service) CommitWrite(ctx context.Context, newTx func(*BaseTX) any, fn func(any) error) error
  • func (s *Service) Migrate(ctx context.Context, migrations fs.FS) error
  • func (s *Service) OnBatchFlushError(handler func(error))
  • func (s *Service) Open() error

Constants

const (
	// OpExact matches the column against the whole value, normalized to SQL "=". Use it for IDs,
	// enums, booleans, and other exact-valued columns.
	OpExact = "=="

	// OpSubstring matches a value anywhere in the column, rewritten to "LIKE ?" with the bound value
	// wrapped in "%" wildcards. On numeric columns this silently over-matches (12 matches 1, 2, 123,
	// ...), so keep it to free text.
	OpSubstring = "=~"
)

Operators for Where that the query builders treat specially; every other operator passes through verbatim. Matching is per comparison, so one clause can mix both, and neither follows Filter.ExactMatchByDefault, which resolves only a plain "=".

const (
	// MemoryDSN is used to define an in-memory-only database.
	MemoryDSN = ":memory:"

	// TimeFormat is the default time format for SQLite timestamps.
	// Source: https://pkg.go.dev/modernc.org/sqlite#Driver.Open.
	TimeFormat = "2006-01-02 15:04:05.999 -0700 MST"
)

Functions

func BuildComplexQuery

func BuildComplexQuery(table string, primaryField string, fields string, filter *Filter, joins []string, groupBy []string,
	fn func(where []string, args []any) ([]string, []any),
) (string, []any, error)

BuildComplexQuery is BuildQuery with JOINs and GROUP BY, for joins or aggregation such as GROUP_CONCAT over a one-to-many. Its operator and identifier rules are the same, except that the groupBy argument is not validated, so it may hold expressions but must be a trusted value.

Example:

BuildComplexQuery(
    "participants", "participants.id", "participants.*, GROUP_CONCAT(ases.id) AS asns",
    filter,
    []string{"LEFT JOIN ases ON ases.claimee_id = participants.id"},
    []string{"participants.id"},
    func(where []string, args []any) ([]string, []any) {
        if filter.ID != "" {
            where = append(where, litex.Where("participants.id", litex.OpExact))
            args = append(args, filter.ID)
        }
        return where, args
    },
)

func BuildQuery

func BuildQuery(table string, primaryField string, fields string, filter *Filter, fn func(where []string, args []any) ([]string, []any)) (string, []any, error)

BuildQuery creates a SELECT statement with filtering and ordering, the fn callback populating the WHERE clauses and their arguments. Pass nil for filter to return all rows ordered by primaryField DESC. See OpSubstring and OpExact for how clause operators are rewritten.

Identifiers are interpolated rather than bound, so table, fields and joins must be trusted values. primaryField, Filter.OrderBy and Filter.GroupBy are validated as simple or dotted identifiers.

func UpdateQuery

func UpdateQuery(table string, fn func(fields []string, args []any) ([]string, []any)) (string, []any, error)

UpdateQuery creates an UPDATE statement of the form "UPDATE table SET a = ?, b = ?", the fn callback populating the fields and their arguments. It deliberately emits no WHERE clause: the caller must append one, otherwise the statement updates every row in the table.

Field names are interpolated, so each is validated as a simple or dotted identifier. table is not, and must be a trusted value.

func Where

func Where(field string, operator ...string) string

Where builds a "field <operator> ?" clause fragment. The operator is optional; omitting it emits a plain "=", read as substring matching unless Filter.ExactMatchByDefault is set. Pass OpSubstring or OpExact to pin the clause regardless of that setting, or any other SQL operator to pass it through verbatim.

Types

type BaseTX

type BaseTX struct {
	// contains filtered or unexported fields
}

BaseTX provides common transaction functionality that can be embedded in project-specific transaction types.

func (*BaseTX) Exec

func (b *BaseTX) Exec(ctx context.Context, stmt string, args ...any) (sql.Result, error)

Exec executes a write statement against the transaction. Prefer this over TX().ExecContext() so that logging is consistent across all write operations.

func (*BaseTX) ExecPrepared

func (b *BaseTX) ExecPrepared(ctx context.Context, stmt string, rows [][]any) error

ExecPrepared runs stmt once for each row of args against the transaction, reusing a single prepared statement. Use it for bulk writes where the same statement repeats with different values.

func (*BaseTX) Find

func (b *BaseTX) Find(ctx context.Context, stmt string, args []any, scanFn func(rows *sql.Rows) error) error

Find is a convenience wrapper for querying rows. The given scanFn is called on each rows.Next() iteration.

func (*BaseTX) Logger

func (b *BaseTX) Logger() *zerolog.Logger

Logger returns the logger for this transaction.

func (*BaseTX) Query

func (b *BaseTX) Query(ctx context.Context, stmt string, args ...any) (*sql.Rows, error)

Query executes a query against the transaction and returns the resulting rows. Prefer this over TX().QueryContext() so that logging is consistent across all read operations.

func (*BaseTX) QueryRow

func (b *BaseTX) QueryRow(ctx context.Context, stmt string, args ...any) *sql.Row

QueryRow executes a query against the transaction and returns a single row. Prefer this over TX().QueryRow() so that logging is consistent across all read operations.

func (*BaseTX) TX

func (b *BaseTX) TX() *sql.Tx

TX returns the underlying sql.Tx for custom operations.

type BatchTX

type BatchTX struct {
	// contains filtered or unexported fields
}

BatchTX is provided to project-specific transaction types via CommitBatch. It exposes QueueExec for buffered writes that are flushed to a real transaction by a background timer. It is safe for concurrent use, so a callback may fan QueueExec out across goroutines.

func (*BatchTX) Logger

func (b *BatchTX) Logger() zerolog.Logger

Logger returns the logger for this transaction.

func (*BatchTX) QueueExec

func (b *BatchTX) QueueExec(_ context.Context, stmt string, args ...any) error

QueueExec buffers a write statement for deferred commit rather than executing immediately. It always returns nil: execution failures surface via OnBatchFlushError, the logger, or Close. The context is not retained; the flush runs under its own timeout.

type Filter

type Filter struct {
	// Keyset cursor: excludes rows ordered past this value on the OrderBy column. That column should be
	// unique, otherwise ties at a page boundary may be skipped.
	Offset any `json:"-"`

	GroupBy string `json:"-"` // Comma-separated columns to GROUP BY.
	OrderBy string `json:"-"` // Column to order by; defaults to the primary field.
	Limit   int    `json:"-"` // Max rows to return; <= 0 omits the LIMIT clause (all rows).

	// ExactMatchByDefault decides only what a plain "=" means; clauses pinned with OpSubstring or
	// OpExact keep their behaviour either way.
	ExactMatchByDefault bool `json:"-"`

	OrderAsc bool `json:"-"` // Order ascending; defaults to descending.
}

Filter supplies optional limit, ordering, keyset pagination, and grouping controls to the query builders. Every field is optional: the zero value, or a nil *Filter, returns all matching rows ordered by the primary field descending, with no LIMIT.

Every field is tagged json:"-", so the struct can be embedded in request types without appearing in their JSON.

type Service

type Service struct {
	// contains filtered or unexported fields
}

Service represents a SQLite database service.

func NewDB

func NewDB(dsn string, logger zerolog.Logger) *Service

NewDB creates a new SQLite database service.

func (*Service) Close

func (s *Service) Close() error

Close flushes any buffered batch writes and closes the connections, joining any flush failure with the close errors so shutdown data loss is visible. Closing twice is a no-op, and a Service is single-use: construct a new one with NewDB rather than reopening. It blocks for up to twice batchFlushTimeout, since the final flush waits out one the timer had already started.

func (*Service) CommitBatch

func (s *Service) CommitBatch(_ context.Context, newTx func(*BatchTX) any, fn func(any) error) error

CommitBatch provides a BatchTX to the given function for buffered writes. Entries queued via QueueExec reach the service buffer only once fn returns nil, and are flushed roughly every batchFlushInterval and on Close.

The buffer is service-wide, so one failing statement rolls back every entry in that window, including other callers'. Use CommitWrite where failure must be attributable to its own caller.

func (*Service) CommitRead

func (s *Service) CommitRead(ctx context.Context, newTx func(*BaseTX) any, fn func(any) error) error

CommitRead opens a read-only database transaction. The newTx function converts a BaseTX into your project-specific transaction type.

func (*Service) CommitWrite

func (s *Service) CommitWrite(ctx context.Context, newTx func(*BaseTX) any, fn func(any) error) error

CommitWrite opens a read-write database transaction. The newTx function converts a BaseTX into your project-specific transaction type.

func (*Service) Migrate

func (s *Service) Migrate(ctx context.Context, migrations fs.FS) error

Migrate applies, in ascending order, every migration newer than the database's PRAGMA user_version, then records the highest applied. Call it after Open.

Files live under migrations/ and are named <version>.sql with an optional _description (1.sql, 1_init.sql, 20260716_add_users.sql). Versions need not be contiguous. Migrations are forward-only and may not exceed 2147483647, the user_version ceiling (YYYYMMDD fits; a full timestamp does not).

func (*Service) OnBatchFlushError

func (s *Service) OnBatchFlushError(handler func(error))

OnBatchFlushError registers a handler invoked when an asynchronous batch flush fails. This handler and Close's error are the only way to observe such failures, since the write runs long after QueueExec returned. When unset, failures are logged.

func (*Service) Open

func (s *Service) Open() error

Open opens the database connections. Call Migrate separately afterwards, and call Open itself once, before sharing the Service: it is the only writer of the connection fields, which is what lets every other method read them unsynchronized.