sql

sql

import "github.com/kitsunium/sdk/pkg/v1/data/sql"

Package sql is the public facade for the SDK's relational-database domain: transaction ownership, a pool policy, a health check and a migration runner, over the standard library's database/sql.

It is NOT an ORM, and it never will be. There is no entity mapping, no query builder, no lazy loading and no repository generation — you write SQL, the SDK owns the transaction around it (ADR 0055).

It also ships no driver. Import the one you want, open the pool yourself, and hand it over:

import _ "github.com/jackc/pgx/v5/stdlib"

pool, err := stdsql.Open("pgx", dsn)
dialect, err := sql.ParseDialect("postgres")

tm, err := sql.NewTransactor(sql.Config{
	DB:      pool,
	Dialect: dialect,
	Pool:    sql.PoolConfig{MaxOpen: 20},
})

Who is allowed to commit

Nobody but the manager. Transact hands your unit of work an Executor — Exec, Query, QueryRow, and deliberately nothing else. A helper that receives one CANNOT commit or roll back the transaction it was lent, because those methods do not exist on the type it was given:

err := sql.Transact(ctx, tm, func(ctx context.Context, ex sql.Executor) error {
	if _, err := ex.ExecContext(ctx, "UPDATE accounts SET cents = cents - $1 WHERE id = $2", n, from); err != nil {
		return err
	}
	return credit(ctx, ex, to, n) // credit cannot commit; it has no way to
})

Returning nil commits. Returning an error rolls back and hands the error back to you unchanged, so errors.Is keeps working.

Nesting is a savepoint

database/sql has no nested transactions. Call Transact with a context that already carries one and you get a SAVEPOINT of that transaction rather than a second connection deadlocking against the first:

_ = sql.Transact(ctx, tm, func(ctx context.Context, ex sql.Executor) error {
	if err := mustHappen(ctx, ex); err != nil {
		return err
	}
	// best-effort: if this fails, only ITS work is undone
	if err := sql.Transact(ctx, tm, optional); err != nil {
		log.Warn("optional step skipped", "err", err)
	}
	return nil
})

Three consequences worth knowing before you rely on it:

• Only postgres, mysql and sqlite are supported, because those three spell savepoints identically. SQL Server, Oracle and Db2 are refused BY NAME at ParseDialect — their nesting is a different algorithm, not a different string, and generating SQL that fails at the first nested transaction in production is not an option.
• A failed nested scope does NOT condemn the outer transaction. ROLLBACK TO SAVEPOINT is precisely the statement that clears PostgreSQL's aborted state, so catching the inner error leaves you a usable transaction. If that statement itself fails, the transaction is poisoned: every later operation is refused and the commit never happens.
• A nested call must pass the ZERO TxOptions. A savepoint cannot change isolation or read-only mode, and being handed a weaker transaction than you asked for under a nil error is worse than being told no.

Joining the caller's transaction, and waiting for its commit

The transactor NewTransactor returns has two more capabilities, each an interface of its own beside Transactor, found by type assertion (ADR 0139):

ex, inTx := tm.(sql.Joiner).Join(ctx)       // where a statement under ctx runs
held := tm.(sql.Deferrer).Defer(ctx, notify) // notify runs once ctx's transaction commits

Joiner answers the executor of the transaction ctx carries — so a repository called inside its caller's transaction reads what that transaction wrote — or the pool when it carries none. A context that outlived its transaction still names it, and its executor refuses rather than fall back to the pool. Deferrer holds a function until that transaction commits: a rollback drops it, and so does the rollback of the savepoint it was held in. It is how a message, a mail or a store's write hook waits for the work it announces to be committed. The SDK's document store over SQL is built on both.

Migrations

NewMigrator applies an ordered, versioned set under a lock that excludes every other runner and dies with its holder, so two instances starting together cannot apply the same migration twice and a runner killed mid-run leaves nothing held. On PostgreSQL and MySQL it is the session's advisory lock, which the server drops with the connection. On SQLite it is the database file's write lock (ADR 0140): the run is ONE transaction that takes it with its first statement, each migration a savepoint of it, and the operating system drops a dead process's file locks. A SQLite run needs a single connection, and a migration there cannot run what SQLite refuses inside a transaction — VACUUM, or PRAGMA journal_mode.

Migrations are VALUES you build. The SDK ships no directory, no file format and no naming convention, because the moment it reads a directory it has invented one you did not choose:

m := sql.Migration{
	Version: 20260910143000,
	Name:    "create accounts",
	Up:      sql.Statements("CREATE TABLE accounts (id BIGINT PRIMARY KEY, cents BIGINT NOT NULL)"),
	Down:    sql.Statements("DROP TABLE accounts"),
}

Each migration runs in its own transaction together with its version-table row, so on PostgreSQL and SQLite a failed migration rolls back BOTH halves and the database sits exactly at the previous version.

On MySQL and MariaDB that guarantee does not hold, and it is not something this SDK can fix: DDL causes an IMPLICIT COMMIT, so a migration whose second statement fails leaves the first one applied while its version row is rolled back. Write ONE DDL statement per migration on MySQL and the failure is atomic by construction. See ADR 0055 D8.

Migrator.Plan is the dry run: it reports what Migrator.Up would apply, applies nothing and takes no lock. Irreversible is how a migration says out loud that it cannot be rolled back — a nil Down is refused, because "I forgot the reversal" and "there is no reversal" are the same nil.

Errors

Every failure is a typed SDK error joined with the driver's own, side by side, so neither question loses its answer:

errors.Is(err, sql.CommitFailed)              // the SDK's verdict
errs.HasCode(err, sql.CommitFailed.Code())    // the same, by code
errors.Is(err, driver.ErrBadConn)             // the driver's own

The sentinels above are the matchable values; their Code() is the ADR 0005 dotted quad. No Public message in this domain ever carries a connection string, a query or a bound argument.

Index

• Variables
• func Irreversible(ctx context.Context, ex Executor) error
• func Transact(ctx context.Context, tm Transactor, fn TxFunc) error
• type Checker
  • func NewChecker(cfg Config) (probe Checker, err error)
• type Config
• type Deferrer
• type Dialect
  • func ParseDialect(name string) (dialect Dialect, err error)
• type Executor
• type Joiner
• type MigrateConfig
• type Migration
• type Migrator
  • func NewMigrator(cfg Config, mig MigrateConfig) (runner Migrator, err error)
• type PoolConfig
• type Preparer
• type Step
  • func Statements(stmts ...string) Step
• type Transactor
  • func NewTransactor(cfg Config) (manager Transactor, err error)
• type TxFunc
• type TxOptions

Variables

var (
    // UnknownDialect is returned by ParseDialect for a name this SDK has
    // never heard of.
    UnknownDialect = coresql.UnknownDialect
    // DialectRefused is returned by ParseDialect for an engine this SDK
    // recognises and declines. The reason travels in a field.
    DialectRefused = coresql.DialectRefused
    // NestedIsolation is returned when a nested Transact asks for isolation
    // or read-only mode a savepoint cannot provide.
    NestedIsolation = coresql.NestedIsolation
    // InvalidMigration is returned for a migration with version 0, an empty
    // name, a nil Up or a nil Down.
    InvalidMigration = coresql.InvalidMigration
    // MigrationIrreversible is what the Irreversible step returns.
    MigrationIrreversible = coresql.MigrationIrreversible
    // ConfigInvalid is returned by every constructor for a Config that could
    // never produce a working port.
    ConfigInvalid = coresql.ConfigInvalid
    // PoolMisconfigured refuses a pool policy whose zero value database/sql
    // would read as a decision nobody made — a non-positive MaxOpen.
    PoolMisconfigured = coresql.PoolMisconfigured
    // BeginFailed reports a transaction the driver would not open.
    BeginFailed = coresql.BeginFailed
    // CommitFailed reports a transaction that had NO effect.
    CommitFailed = coresql.CommitFailed
    // RollbackFailed travels alongside the error that triggered the rollback.
    RollbackFailed = coresql.RollbackFailed
    // SavepointFailed reports a rejected savepoint statement; the "statement"
    // field names which of the three.
    SavepointFailed = coresql.SavepointFailed
    // TxPoisoned refuses every operation on a transaction whose savepoint
    // rollback failed. It is never committed.
    TxPoisoned = coresql.TxPoisoned
    // TxClosed reports an Executor used after the scope that lent it
    // returned.
    TxClosed = coresql.TxClosed
    // HealthCheckFailed reports a database that answered the probe with an
    // error.
    HealthCheckFailed = coresql.HealthCheckFailed
    // HealthCheckTimeout reports a database that did not answer in time.
    HealthCheckTimeout = coresql.HealthCheckTimeout
    // MigrationFailed reports a migration that did not apply; it was rolled
    // back.
    MigrationFailed = coresql.MigrationFailed
    // MigrationOutOfOrder refuses a pending migration older than one already
    // applied.
    MigrationOutOfOrder = coresql.MigrationOutOfOrder
    // MigrationLockUnsupported refuses a Migrator on a dialect with no lock
    // that dies with its holder. No dialect this SDK speaks answers it since
    // SQLite's runner serialises on its file's write lock (ADR 0140).
    MigrationLockUnsupported = coresql.MigrationLockUnsupported
    // MigrationLockTimeout reports another process holding the migration lock
    // for the whole budget. Nothing was applied.
    MigrationLockTimeout = coresql.MigrationLockTimeout
    // MigrationUnknownVersion refuses a Down over a version this build does
    // not carry.
    MigrationUnknownVersion = coresql.MigrationUnknownVersion
    // VersionTableInvalid refuses a version-table name that is not a plain
    // SQL identifier.
    VersionTableInvalid = coresql.VersionTableInvalid
    // DuplicateMigration refuses two migrations declaring the same version.
    DuplicateMigration = coresql.DuplicateMigration
)

func Irreversible

func Irreversible(ctx context.Context, ex Executor) error

Irreversible is the Step a migration assigns to Down to declare, out loud, that it cannot be reversed.

func Transact

func Transact(ctx context.Context, tm Transactor, fn TxFunc) error

Transact runs fn inside a transaction with the driver's default isolation.

It is the ergonomic form of Transactor.Transact for the common case. The port itself keeps the options parameter so it never needs a second method (ADR 0039); this helper keeps the call site short.

type Checker

Checker is the public alias for the bounded liveness probe.

type Checker = coresql.Checker

func NewChecker

func NewChecker(cfg Config) (probe Checker, err error)

NewChecker returns a liveness probe bounded by cfg.CheckTimeout.

type Config

Config is the public alias for the parameters every port is built from.

type Config = svcsql.Config

type Deferrer

Deferrer is the public alias for the ADR 0039 sibling of Transactor that holds a function until the transaction a context carries has committed, and drops it with a rollback. Discover it by type assertion.

type Deferrer = coresql.Deferrer

type Dialect

Dialect is the public alias for the closed set of SQL engines this SDK can spell. For SQL you write yourself, it spells the engine's vocabulary — the tokens the SDK's own statements are spelled with, never a statement: Dialect.Placeholder renders a bind marker ($1 on PostgreSQL, ? on MySQL and SQLite), Dialect.QuoteIdent a delimited name, its delimiter doubled inside, and Dialect.ForUpdate and Dialect.ForUpdateSkipLocked a row-lock clause, which SQLite — whose exclusion is its one write lock — renders as nothing. QuoteIdent validates nothing: check a name before you interpolate it. An unset Dialect renders no marker and no name, so a statement built from it fails rather than passing for another engine's.

type Dialect = coresql.Dialect

DialectMySQL is MySQL 5.7+ / MariaDB 10.x with InnoDB.

const DialectMySQL Dialect = coresql.DialectMySQL

DialectPostgres is PostgreSQL 9.1+.

const DialectPostgres Dialect = coresql.DialectPostgres

DialectSQLite is SQLite 3.6.8+. It has no advisory lock, so NewMigrator serialises a run on the database file's write lock instead (ADR 0140).

const DialectSQLite Dialect = coresql.DialectSQLite

func ParseDialect

func ParseDialect(name string) (dialect Dialect, err error)

ParseDialect resolves a dialect name and never guesses. A recognised but unsupported engine returns DialectRefused carrying why; an unrecognised one returns UnknownDialect.

type Executor

Executor is the public alias for the read/write surface a statement runs against. *sql.DB, *sql.Tx and *sql.Conn all satisfy it with no adapter, and it deliberately carries no Commit, Rollback or Begin.

type Executor = coresql.Executor

type Joiner

Joiner is the public alias for the ADR 0039 sibling of Transactor that says where a statement issued under a context runs: in the transaction the context carries, or on the pool. Discover it by type assertion.

type Joiner = coresql.Joiner

type MigrateConfig

MigrateConfig is the public alias for the migration runner's parameters.

type MigrateConfig = svcsql.MigrateConfig

type Migration

Migration is the public alias for one versioned schema change and its reversal.

type Migration = coresql.MigrationValue

type Migrator

Migrator is the public alias for the schema-migration runner.

type Migrator = coresql.Migrator

func NewMigrator

func NewMigrator(cfg Config, mig MigrateConfig) (runner Migrator, err error)

NewMigrator returns the migration runner. It refuses a dialect with no session-scoped advisory lock, by name and at construction.

type PoolConfig

PoolConfig is the public alias for the connection-pool policy.

type PoolConfig = svcsql.PoolConfig

type Preparer

Preparer is the public alias for the ADR 0039 sibling of Executor: an executor that can also prepare a statement. Discover it by type assertion.

type Preparer = coresql.Preparer

type Step

Step is the public alias for one direction of a migration.

type Step = coresql.Step

func Statements

func Statements(stmts ...string) Step

Statements returns a Step that runs the given statements in order on the transaction's executor.

It is the smallest useful helper and deliberately not a file loader: it invents no directory layout, no naming convention and no parser. Where the text comes from — a literal, an embed.FS, a generator — stays yours.

type Transactor

Transactor is the public alias for the transaction manager.

type Transactor = coresql.Transactor

func NewTransactor

func NewTransactor(cfg Config) (manager Transactor, err error)

NewTransactor returns the transaction manager for cfg.DB, applying cfg.Pool to it.

type TxFunc

TxFunc is the public alias for a unit of work run inside a transaction.

type TxFunc = coresql.TxFunc

type TxOptions

TxOptions is the public alias for one transaction's isolation and read-only-ness. The zero value defers to the driver.

type TxOptions = coresql.TxOptionsValue

Generated by gomarkdoc