sql

package
v0.10.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Aug 26, 2026 License: MIT Imports: 17 Imported by: 0

Documentation

Overview

Package sql is a lightweight, pgx-native toolkit for talking to PostgreSQL. It is a thin ergonomic layer over jackc/pgx v5: it does not hide pgx or generate SQL, it composes with pgx's own tools (named args, row collectors, transactions) to remove the boilerplate that surrounds them.

The design is deliberately small. You write SQL by hand using @name placeholders; the package handles scanning into structs, transaction lifecycle, retries, and connection setup.

Reads

Row, Rows, and Values scan query results into typed values. Scanning is by column name and lenient by default (struct fields without a matching column are left zero), so SELECT * and partial structs work without fuss. StrictRow and StrictRows require an exact column/field match. 

u, err := sql.Row[User](ctx, db,
	"select * from users where id = @id", sql.Args{"id": 7})

users, err := sql.Rows[User](ctx, db,
	"select * from users where org_id = @org", sql.Args{"org": orgID})

ids, err := sql.Values[int64](ctx, db,
	"select id from users where active")

Row returns pgx.ErrNoRows when there is no row; check it with errors.Is(err, pgx.ErrNoRows). Rows returns a non-nil empty slice for an empty result.

Row, Rows, StrictRow and StrictRows scan by column name into a struct. Use Values for a scalar result: a non-struct T panics inside pgx's reflection rather than returning an error.

Writes

Exec runs a statement and returns the number of rows affected. For INSERT ... RETURNING, use Row to scan the returned row. 

n, err := sql.Exec(ctx, db,
	"update users set active = false where id = @id", sql.Args{"id": 7})

created, err := sql.Row[User](ctx, db,
	"insert into users (email, org_id) values (@email, @org) returning *",
	sql.Args{"email": "a@b.com", "org": orgID})

Bulk writes

InsertMany writes a slice of structs in as few statements as the bind-parameter limit allows, taking its columns from the same db struct tags the read helpers scan by. 

n, err := sql.InsertMany(ctx, db, "iam_users", users, sql.Insert{
	Columns: []string{"email", "partner_id", "display_name"},
})

Insert.Columns is how a column is left to its database default. It has to be explicit: a struct field holding the zero value is indistinguishable from one that was never set, so an omitted id would otherwise be written as 0 rather than falling to BIGSERIAL. An untagged field's column name is its Go name in snake_case, which is a name pgx would have matched on the way back in; a wrong guess fails as undefined_column rather than silently.

Conflict adds ON CONFLICT. Empty Update means DO NOTHING; otherwise each named column becomes c = EXCLUDED.c. Conflict.Where repeats the predicate of a partial unique index verbatim — Postgres will not infer one without it, and an index cannot be named as a constraint, so the inference clause is the only route (see the Partial indexes section). 

n, err := sql.InsertMany(ctx, db, "iam_users", users, sql.Insert{
	Columns: []string{"email", "partner_id", "display_name"},
	Conflict: &sql.Conflict{
		Columns: []string{"email"},
		Where:   "partner_id IS NULL",
		Update:  []string{"display_name"},
	},
})

With DO NOTHING the result counts rows actually inserted, not rows offered: a result below len(rows) is the caller's only signal that some already existed.

Two rows in one statement that share a conflict key make DO UPDATE fail with "ON CONFLICT DO UPDATE command cannot affect row a second time" — deterministically, not as a race. Against a non-partial index InsertMany catches this before sending and names the offending rows; Conflict.LastWins keeps the last occurrence instead. Against a partial index, whether two rows collide depends on which the predicate admits, which this package does not evaluate, so the check is skipped and Postgres' own error is rewritten into the same explanation. LastWins is refused there, because it could discard a row that never conflicted. A NULL in the conflict key is never a duplicate: two NULLs are distinct under a unique index.

A statement carries at most 65535 bind parameters, so a slice large enough to be split is not applied atomically — an error on the third statement leaves the first two committed. Wrap the call in Tx when that matters. The duplicate check runs across the whole slice, so which rows are rejected never depends on where a chunk boundary fell.

InsertManyReturning adds RETURNING *. Its result is not positionally aligned with the input, since DO NOTHING returns only inserted rows and LastWins drops rows before sending; match rows up by a key in R, not by index. 

created, err := sql.InsertManyReturning[User](ctx, db, "iam_users", users)

CopyInto streams rows with COPY for large volumes of already-clean data. It takes a Copier — satisfied by a pool, a connection and a transaction, like DB — so it composes with Tx unchanged. COPY has no ON CONFLICT and no RETURNING, so a Conflict is rejected rather than ignored, and one violation aborts the whole load.

Unqualified table names resolve through the connection's search_path, the way hand-written DML does, unlike the migrator's always-qualified DDL.

Transactions

Tx runs a function inside a transaction, committing on success and rolling back on error or panic. TxOpts adds isolation/access-mode control; TxRetry retries serialization failures and deadlocks at serializable isolation, backing off exponentially with jitter between attempts (make the function idempotent).

A rollback is issued on a context detached from the caller's cancellation, so a transaction that fails because its context died still rolls back cleanly and returns the caller's own error rather than a rollback failure. 

err := sql.Tx(ctx, pool, func(tx pgx.Tx) error {
	if _, err := sql.Exec(ctx, tx, "...", args); err != nil {
		return err
	}
	return nil
})

Tx, TxOpts, and TxRetry take a Beginner, satisfied by *pgxpool.Pool and *pgx.Conn. A pgx.Tx does not satisfy Beginner — a transaction nests via Begin (a savepoint), which takes no options. To run a function in a transaction that may itself already be a transaction, use TxNested, which starts a real transaction for a pool/conn and a savepoint for a pgx.Tx.

The read and write helpers take a DB, satisfied by *pgxpool.Pool, *pgx.Conn, and pgx.Tx alike, so the same query code runs inside or outside a transaction.

Connections

Connect opens a pool from a Config struct and verifies it with a ping. Optional mutators reach any pgxpool.Config setting the struct does not cover. Every field except SSLMode is assigned to the parsed pgx config rather than interpolated into a connection string, so a value containing a space, a quote or an '=' is safe; an empty field defers to libpq's usual environment fallback. 

pool, err := sql.Connect(ctx, sql.Config{
	Host: "localhost", Database: "app", User: "app", Password: pw,
	MaxConns: 20,
})

Schema migrations

A struct-driven migrator brings tables up to a desired shape. Describe the schema with Table, Column, and Index, then call Migrate. Missing tables are created; existing tables are altered. Migrate takes a concrete *pgxpool.Pool because introspection and DDL are startup-time operations, off the query path. 

users := sql.Table{
	Schema: "tenant_a", // optional; "" means DefaultSchema ("public")
	Name:   "users",
	Columns: []sql.Column{
		{Name: "id", Type: sql.BigSerial, Flag: sql.ColumnFlag{PrimaryKey: true}},
		{Name: "email", Type: sql.Text, Flag: sql.ColumnFlag{Unique: true}},
		{Name: "created_at", Type: sql.TimestampTZ, Default: sql.DefaultNow},
	},
	Indexes: []sql.Index{{ColumnName: "email", IsUnique: true}},
}
err := sql.Migrate(ctx, pool, []sql.Table{users})

Migrate is safe by default: it only adds columns and indexes and changes nullability. Destructive and risky steps are opt-in via options, each off by default: 

WithDrops              — drop columns and indexes absent from the schema
WithConstraintChanges  — set/drop column DEFAULTs; add/drop column UNIQUE
WithTypeChanges        — ALTER COLUMN ... TYPE

One option is on by default: WithSchemaCreation(false) stops Migrate creating a schema named by Table.Schema that does not exist yet.

WithLogger directs the migrator's slog output at a specific logger.

Table.Schema selects the Postgres schema, defaulting to DefaultSchema ("public"), and tables in one Migrate call may name different schemas — so a multi-tenant schema-per-tenant layout migrates in a single pass. Every statement is schema-qualified and every catalog lookup is scoped to match, so migrations do not depend on the connection's search_path. Derived index and constraint names stay unqualified: they live in their table's schema already, and index names are per-schema, so the same table replicated across tenants keeps identical index names without colliding. A schema that does not exist yet is created; only genuinely missing ones are, so the steady state issues no schema DDL and needs no CREATE privilege on the database. See WithSchemaCreation to require schemas to be provisioned out of band.

Schema, table, column and derived index names must be lower case, unqualified, and within Postgres' 63-byte identifier limit; Migrate validates them before running any DDL, because the migrator emits unquoted identifiers and a name the server folds or truncates could never be matched by introspection again.

Foreign keys

Column.References makes a column a foreign key. Reference.Column defaults to "id" and Reference.Schema to the referencing table's own schema, so a schema-per-tenant layout gets tenant-local references without saying so. 

{Name: "user_id", Type: sql.UUID,
	References: &sql.Reference{Table: "users", OnDelete: sql.Cascade}},

Foreign keys are applied after every table in the call has been created, so the order of the slice does not matter and self-referencing and mutually-referencing tables need no special handling. Adding one is additive; changing its target or action requires WithConstraintChanges (it is a drop and re-add), and dropping one no longer declared requires WithDrops. Only single-column foreign keys are expressible, and composite ones are invisible to the migrator, so WithDrops will not disturb a composite foreign key installed by hand.

ADD ... FOREIGN KEY fails if the column already holds values with no matching parent row, exactly as ADD ... UNIQUE fails on existing duplicates.

All alters for a table run in one transaction, so each table migrates atomically. Caveats inherent to a struct-diff migrator: SET NOT NULL fails on existing nulls and ADD UNIQUE fails on existing duplicates (the migration errors and rolls back); type changes can corrupt incompatible data; width and precision changes are not detected at all, since comparison comes down to the base type; default comparison is best-effort (though serial columns are exempt); and under WithDrops the migrator assumes it owns every plain index on the table.

UNIQUE constraints of any width are managed through Table.Unique, with ColumnFlag.Unique as the single-column spelling of the same thing. A constraint and a unique index are not alternatives: only a constraint can be named in ON CONFLICT ON CONSTRAINT, and only an index can be partial. Constraints are matched on their column set rather than their name, so one Postgres named itself is recognised rather than duplicated. Adding, dropping, and rebuilding an index whose uniqueness changed all require WithConstraintChanges.

Partial indexes

Index.Where makes an index partial: it becomes the WHERE clause of CREATE INDEX, and only rows satisfying it are indexed. On a unique index this constrains uniqueness to those rows, which is how a rule like "at most one admin email per account with no partner" is expressed: 

{Name: "uq_users_admin_email", ColumnName: "email", IsUnique: true,
	Where: "partner_id IS NULL"}

Index.Name overrides the derived idx_<table>_<columns> name. It is required whenever two indexes cover the same columns — a partial index alongside a full one, or two partial indexes with different predicates — because they would otherwise derive the same name and the diff, which keys indexes by name, would create only one of them. Migrate rejects a colliding pair up front rather than applying half of it.

Adding a partial index is additive and needs no flag. Changing or removing the predicate is a drop and rebuild, gated behind WithConstraintChanges alongside the uniqueness toggle, because moving rows into a unique index can fail on duplicates just as gaining uniqueness can.

Postgres does not store the predicate as written: it parses it and reports its own deparsed form, fully parenthesised and with implicit casts made explicit, so "status = 'active'" comes back as "((status)::text = 'active'::text)". Comparison strips casts, parentheses, whitespace and case to bring the two together (see normalisePredicate). It is lossy in the safe direction — predicates differing only in bracketing compare equal, so such a change is missed rather than rebuilt on every run.

Keep literal timestamps out of predicates: a timestamptz literal is evaluated at parse time and rendered in the session's TimeZone, so '2020-01-01' is stored as '2020-01-01 00:00:00+00' and compares unequal forever, rebuilding the index on every run under WithConstraintChanges. Date literals, IS NULL, boolean, numeric and text comparisons all round-trip cleanly.

Like Column.Default the predicate is raw SQL emitted verbatim, so it must come from your source and never from user input.

A primary key is set when the table is created and never altered afterwards. A declared key that disagrees with the table's actual key fails the migration before any change is applied, rather than being silently ignored; change a key with a hand-written migration. A table that declares no key is not checked, so the migrator can be adopted without describing existing constraints.

Named arguments

Args is an alias for pgx.NamedArgs. Placeholders use @name syntax and are rewritten to positional parameters before execution, so the usual bind-parameter safety applies. The rewriter does not understand SQL string literals, so a literal '@' in the query body may be mis-parsed; use positional args for those statements. CheckNamed is a test helper that flags missing or unused named arguments.

Index

Constants

View Source 
const (
	DefaultNow             = "NOW()"
	DefaultUUID            = "gen_random_uuid()"
	DefaultTrue            = "true"
	DefaultFalse           = "false"
	DefaultZero            = "0"
	DefaultEmptyJSON       = "'{}'::json"
	DefaultEmptyJSONB      = "'{}'::jsonb"
	DefaultEmptyJSONArray  = "'[]'::json"
	DefaultEmptyJSONBArray = "'[]'::jsonb"
)
View Source 
const DefaultRefColumn = "id"

DefaultRefColumn is the referenced column assumed when Reference.Column is empty.

View Source 
const DefaultSchema = "public"

DefaultSchema is the schema a Table with no Schema set belongs to.

Variables

This section is empty.

Functions

func CheckNamed 

func CheckNamed(sql string, args Args) error

CheckNamed reports @name tokens in sql with no matching key in args, and keys in args never referenced by sql. Intended for tests, not the hot path: it does not understand SQL string literals, so a literal '@' inside quotes is a false positive — the signal to use positional args there.

func Connect 

func Connect(ctx context.Context, cfg Config, mutators ...func(*pgxpool.Config)) (*pgxpool.Pool, error)

Connect opens a pool from cfg, applies any mutators to the parsed pgxpool.Config, and verifies the pool with a ping. Mutators run after the struct fields are applied, so they can override anything or reach settings Config doesn't cover (tracer, connection hooks, runtime params): 

pool, err := sql.Connect(ctx, sql.Config{
	Host: "localhost", Database: "app", User: "app", Password: pw,
	MaxConns: 20, ConnectTimeout: 5 * time.Second,
}, func(c *pgxpool.Config) {
	c.ConnConfig.RuntimeParams["application_name"] = "billing"
})

func CopyInto added in v0.10.0 

func CopyInto[T any](ctx context.Context, db Copier, table string, rows []T, opt ...Insert) (int64, error)

CopyInto bulk-loads rows with COPY and reports how many rows were copied. This is the fast path for large volumes of data already known to be clean.

COPY has no ON CONFLICT and no RETURNING, so Insert.Conflict is rejected rather than ignored, and a single constraint violation aborts the whole load — there is no per-row recovery. Use InsertMany when rows may already exist or when generated values are needed back.

Row-level triggers fire; rules do not.

func Exec 

func Exec(ctx context.Context, db DB, sql string, args ...any) (int64, error)

Exec runs a statement and returns the number of rows affected. For INSERT ... RETURNING, use Row[T] instead to scan the returned row.

func InsertMany added in v0.10.0 

func InsertMany[T any](ctx context.Context, db DB, table string, rows []T, opt ...Insert) (int64, error)

InsertMany inserts rows into table and reports how many rows Postgres actually wrote. Columns are derived from T's db struct tags — the same tags Row and Rows scan by — or taken from Insert.Columns.

With Conflict set to DO NOTHING the result counts only the rows that were inserted, so a result below len(rows) is the signal that some already existed. The result is not the number of rows offered.

Rows are sent in as few statements as the bind-parameter limit allows, which means a slice large enough to be split is not applied atomically: an error on the third statement leaves the first two committed. Wrap the call in Tx when all-or-nothing matters. The returned count covers the statements that did succeed, even alongside an error.

func InsertManyReturning added in v0.10.0 

func InsertManyReturning[R, T any](ctx context.Context, db DB, table string, rows []T, opt ...Insert) ([]R, error)

InsertManyReturning is InsertMany with RETURNING *, scanning the returned rows into R by column name (lenient, like Rows).

R is explicit and T is inferred: 

created, err := sql.InsertManyReturning[User](ctx, db, "iam_users", users)

The result is not positionally aligned with rows. DO NOTHING returns only the rows that were inserted, and LastWins drops rows before sending, so use a key carried in R to match them up rather than an index.

func JSONBDefault 

func JSONBDefault(value string) string

JSONBDefault wraps a literal as a ::jsonb default expression.

func JSONDefault 

func JSONDefault(value string) string

JSONDefault wraps a literal as a ::json default expression.

func Migrate 

func Migrate(ctx context.Context, pool *pgxpool.Pool, tables []Table, opts ...MigrateOption) error

Migrate brings each table up to its desired schema. Tables are created if absent and altered (additively by default) if present. All alters for a table run in a single transaction.

Each table names its own schema via Table.Schema, defaulting to DefaultSchema, so one call can migrate several schemas at once. Every statement is schema-qualified and every catalog lookup is scoped to the same schema, so migrations do not depend on the connection's search_path. A schema that does not exist yet is created; see WithSchemaCreation to disable that.

Every schema, table, column and derived index name must be lower case, unqualified, and within Postgres' 63-byte identifier limit; anything else is rejected before any DDL runs. The migrator emits unquoted DDL, and Postgres folds and truncates those identifiers, so a name that does not survive the round trip could never be found by introspection again (see validateIdentifier).

func Row 

func Row[T any](ctx context.Context, db DB, sql string, args ...any) (T, error)

Row runs sql and scans exactly one row into T by column name (lenient: struct fields without a matching column are left zero). Returns pgx.ErrNoRows if no rows, or an error if more than one.

func Rows 

func Rows[T any](ctx context.Context, db DB, sql string, args ...any) ([]T, error)

Rows runs sql and scans every row into []T by column name (lenient). Returns an empty (non-nil) slice when there are no rows.

func StrictRow 

func StrictRow[T any](ctx context.Context, db DB, sql string, args ...any) (T, error)

StrictRow is Row with strict scanning: every selected column must map to a struct field and vice versa. Use when you want schema/struct drift to surface as an error rather than a silently-zero field.

func StrictRows 

func StrictRows[T any](ctx context.Context, db DB, sql string, args ...any) ([]T, error)

StrictRows is Rows with strict scanning. See StrictRow.

func Tx 

func Tx(ctx context.Context, db Beginner, fn func(pgx.Tx) error) error

Tx runs fn inside a transaction, committing on success and rolling back on error or panic (the panic is re-raised after rollback).

func TxNested 

func TxNested(ctx context.Context, db DB, fn func(pgx.Tx) error) (err error)

TxNested runs fn in a transaction, transparently handling the case where db is already a transaction. Given a *pgxpool.Pool or *pgx.Conn it starts a real transaction; given a pgx.Tx it starts a savepoint. Use this for functions that may be called both standalone and within a caller's transaction.

func TxOpts 

func TxOpts(ctx context.Context, db Beginner, opts pgx.TxOptions, fn func(pgx.Tx) error) error

TxOpts is Tx with explicit transaction options (isolation, access mode).

func TxRetry 

func TxRetry(ctx context.Context, db Beginner, attempts int, fn func(pgx.Tx) error) error

TxRetry runs fn in a serializable transaction, retrying up to attempts times when it fails with a serialization failure or deadlock. Make fn idempotent — it may run more than once.

Failed attempts back off exponentially with jitter before the next try, and the wait is abandoned if ctx is cancelled.

func Values 

func Values[T any](ctx context.Context, db DB, sql string, args ...any) ([]T, error)

Values runs sql and scans a single-column result set into []T (e.g. []int64 of ids). The query must select exactly one column.

Types

type AlterOp 

type AlterOp struct {
	SQL     string
	Comment string
}

AlterOp is a single migration statement with a human-readable comment.

type Args 

type Args = pgx.NamedArgs

Args is pgx's named-argument map. Use @name placeholders in SQL: 

sql.Row[User](ctx, db, "select * from users where id = @id", sql.Args{"id": 7})

A single Args value passed as the sole argument is rewritten to positional parameters before execution, so all bind-parameter safety applies.

type Beginner 

type Beginner interface {
	DB
	BeginTx(ctx context.Context, opts pgx.TxOptions) (pgx.Tx, error)
}

Beginner is a DB that can start a transaction with options. It is satisfied by *pgxpool.Pool and *pgx.Conn.

Note: pgx.Tx does NOT satisfy Beginner. A transaction starts a nested transaction (savepoint) via Begin(ctx) — which takes no options — not BeginTx. To run a function in a transaction that may itself already be a transaction, use TxNested, which handles both cases.

type Column 

type Column struct {
	Name string
	Type SQLType
	Flag ColumnFlag

	// Default is a raw default expression, emitted verbatim. It is applied when
	// the table is created and when the column is added; changing or removing
	// the default on an existing column additionally requires
	// WithConstraintChanges. Leave it empty for Serial and BigSerial columns —
	// their sequence default comes from Postgres (see isSequenceDefault).
	Default string

	// References makes this column a foreign key. Nil means no foreign key.
	//
	// Foreign keys are applied after every table in the Migrate call has been
	// created, so the order of the slice does not matter and self-referencing
	// and mutually-referencing tables work without special handling.
	References *Reference
}

Column is a desired column definition.

type ColumnFlag 

type ColumnFlag struct {
	PrimaryKey bool
	Nullable   bool
	Unique     bool
}

ColumnFlag carries the boolean attributes of a column.

There is no AutoIncrement flag: auto-increment is a property of the column type in Postgres, so use the Serial or BigSerial type instead.

type Config 

type Config struct {
	Host     string
	Port     int
	Database string
	User     string
	Password string
	SSLMode  string // disable, allow, prefer, require, verify-ca, verify-full; default "disable"

	MaxConns        int32         // 0 = pgx default (typically 4)
	MinConns        int32         // 0 = pgx default
	MaxConnLifetime time.Duration // 0 = pgx default (1h)
	MaxConnIdleTime time.Duration // 0 = pgx default (30m)
	ConnectTimeout  time.Duration // applied to ConnConfig; 0 = no explicit timeout
}

Config holds the common connection settings. Zero-valued fields fall back to sensible defaults (see Connect). For anything not covered here, use a mutator in Connect.

Host, Port, Database, User and Password are assigned to the parsed pgx config, never interpolated into a connection string, so any value is safe regardless of the characters in it. Leaving one empty defers to libpq's usual environment fallback (PGHOST, PGUSER, PGDATABASE, PGPASSWORD, …).

type Conflict added in v0.10.0 

type Conflict struct {
	// Columns is the inference target: the columns of the unique index or
	// constraint that a duplicate would violate. Required.
	Columns []string

	// Where is the predicate of a partial unique index, repeated verbatim from
	// the index definition. Postgres will not infer a partial index without it:
	//
	//	CREATE UNIQUE INDEX uq_users_admin_email
	//	  ON iam_users (email) WHERE partner_id IS NULL;
	//
	//	Conflict{Columns: []string{"email"}, Where: "partner_id IS NULL"}
	//
	// Omitting it against a partial index fails with
	// "there is no unique or exclusion constraint matching the ON CONFLICT
	// specification". Leave it empty for an ordinary index or constraint.
	//
	// The text is emitted verbatim, like Index.Where — it is SQL, not a value,
	// so it must not be built from untrusted input.
	Where string

	// Update lists the columns to overwrite from the proposed row. Empty means
	// DO NOTHING; otherwise each column c becomes c = EXCLUDED.c.
	Update []string

	// LastWins resolves rows that share a conflict key by keeping the last
	// occurrence, instead of rejecting the batch. It applies only to DO UPDATE,
	// and only when Where is empty — see the package documentation on
	// duplicates within one statement.
	LastWins bool
}

Conflict is an ON CONFLICT clause. The target is always an inference clause over Columns rather than ON CONSTRAINT, because a partial unique index is not a constraint and cannot be named — see the Where field.

type Copier added in v0.10.0 

type Copier interface {
	DB
	CopyFrom(ctx context.Context, tableName pgx.Identifier, columnNames []string, rowSrc pgx.CopyFromSource) (int64, error)
}

Copier is a DB that can also stream rows with COPY. It is satisfied by *pgxpool.Pool, *pgx.Conn and pgx.Tx, exactly like DB, so a function taking a Copier works inside or outside a transaction without change.

type DB 

type DB interface {
	Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error)
	Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error)
	QueryRow(ctx context.Context, sql string, args ...any) pgx.Row
}

DB is the query surface shared by a pool, a connection, and a transaction. Every read/write helper takes a DB, so the same code works inside or outside a transaction. Satisfied by *pgxpool.Pool, *pgx.Conn, and pgx.Tx.

type Existing 

type Existing struct {
	Columns []Column
	Indexes []Index

	// PrimaryKey is the table's primary key columns in key order, empty when
	// the table has none. Order is part of the key's identity: (a, b) and
	// (b, a) are different keys.
	PrimaryKey []string

	// Unique holds every UNIQUE constraint on the table, of any width, with its
	// columns in constraint order and the name Postgres reports.
	Unique []UniqueConstraint
}

Existing is the introspected current state of a table. Columns carry their introspected Default and Flag.Unique (derived from unique constraints).

type Index 

type Index struct {
	// Name overrides the derived physical name. Empty means
	// idx_<table>_<col1>_<col2>… (see indexName), so changing the column set
	// changes the name and the index is treated as a new one.
	//
	// Set it when two indexes over the same columns must coexist — a partial
	// index alongside a full one, or two partial indexes with different
	// predicates — because they would otherwise derive the same name and one
	// would silently swallow the other. Migrate rejects a table whose indexes
	// collide on a name rather than letting that happen.
	//
	// On the introspected side Name always carries the physical name Postgres
	// reports.
	Name       string
	ColumnName string
	Columns    []string
	IsUnique   bool

	// Where makes the index partial: it is emitted verbatim as the WHERE clause
	// of CREATE INDEX, and only rows satisfying it are indexed. A partial unique
	// index constrains uniqueness to those rows, which is how "at most one
	// admin email per account with no partner" is expressed:
	//
	//	{Name: "uq_users_admin_email", ColumnName: "email", IsUnique: true,
	//		Where: "partner_id IS NULL"}
	//
	// Like Column.Default this is raw SQL, interpolated into DDL rather than
	// bound, so it must come from your source and never from user input.
	//
	// Changing the predicate on an existing index is a drop and rebuild, gated
	// behind WithConstraintChanges. Comparison against the database is
	// best-effort, because Postgres stores its own deparsed form of the
	// expression — see normalisePredicate.
	Where string
}

Index is a desired index over one or more columns. For a single-column index set ColumnName; for a composite index set Columns (which takes precedence).

Uniqueness can be expressed here (IsUnique) or as a UniqueConstraint; the two are not alternatives — see UniqueConstraint for which does what. A partial index is the case only an index can serve.

type Insert added in v0.10.0 

type Insert struct {
	// Columns restricts (and orders) the columns written. Every name must map
	// to a field of the row type. Use it to leave a column to its database
	// default — an identity primary key, or a created_at with DEFAULT now() —
	// which is otherwise impossible to express, because a struct field holding
	// the zero value is indistinguishable from a field that was never set.
	Columns []string

	// Conflict adds an ON CONFLICT clause. Nil means a plain insert, where a
	// duplicate key surfaces as Postgres' unique_violation.
	Conflict *Conflict
}

Insert configures a bulk insert. The zero value inserts every column derived from the row type's db tags, with no ON CONFLICT clause.

type MigrateOption 

type MigrateOption func(*migrateConfig)

MigrateOption configures Migrate.

func WithConstraintChanges 

func WithConstraintChanges(allow bool) MigrateOption

WithConstraintChanges controls whether the migrator adds/drops column-level UNIQUE constraints and sets/changes/drops column DEFAULTs on existing columns. OFF by default. Adding a UNIQUE constraint fails if the column holds duplicate values; default-change detection is best-effort (see normaliseDefault) and exotic defaults may re-apply.

Serial columns are exempt: a SERIAL/BIGSERIAL column's nextval default is owned by its sequence rather than declared in the schema, and is never touched (see isSequenceDefault).

func WithDrops 

func WithDrops(allow bool) MigrateOption

WithDrops controls whether columns and indexes present in the database but absent from the desired schema are dropped. OFF by default: migrations are additive unless you opt in. Enable in development; be deliberate in production, where a stray struct change could drop a column.

func WithLogger added in v0.3.0 

func WithLogger(l *slog.Logger) MigrateOption

WithLogger directs the migrator's output at a specific logger instead of slog.Default(). Pass slog.New(slog.DiscardHandler) to silence it.

func WithSchemaCreation added in v0.5.0 

func WithSchemaCreation(allow bool) MigrateOption

WithSchemaCreation controls whether Migrate creates a schema named by Table.Schema that does not exist yet. ON by default: creating a schema is additive, like creating a table, and a migrator told to manage tenant_a should not fail because nobody ran CREATE SCHEMA first.

Only genuinely missing schemas are created. When every schema already exists — the normal case in production — Migrate issues no schema DDL at all and needs no privilege beyond what it already has, so the default is safe for a role without CREATE on the database.

Pass false where the schema must be provisioned out of band, or to make a typo in Table.Schema fail loudly instead of quietly creating a stray schema.

func WithTypeChanges 

func WithTypeChanges(allow bool) MigrateOption

WithTypeChanges controls whether the migrator emits ALTER COLUMN ... TYPE for columns whose type differs from the database. OFF by default because a type change can fail or corrupt data when the existing values are not convertible (e.g. text to integer). Risky type migrations should be written by hand; this flag is for the safe, compatible cases.

type RefAction added in v0.6.0 

type RefAction string

RefAction is the referential action for a foreign key — what happens to the referencing row when the referenced row is deleted or its key updated. 

const (
	// NoAction is Postgres' default: the delete or update is rejected if it
	// would leave a referencing row orphaned. Unlike Restrict, the check can be
	// deferred to the end of the transaction.
	NoAction   RefAction = "NO ACTION"
	Restrict   RefAction = "RESTRICT"
	Cascade    RefAction = "CASCADE"
	SetNull    RefAction = "SET NULL"
	SetDefault RefAction = "SET DEFAULT"
)

type Reference added in v0.6.0 

type Reference struct {
	// Schema holds the referenced table. Empty means the referencing table's
	// own schema, which is what a schema-per-tenant layout wants.
	Schema string

	// Table is the referenced table. Required.
	Table string

	// Column is the referenced column. Empty means "id". It must carry a unique
	// or primary key constraint, which Postgres requires of any foreign key
	// target.
	Column string

	// OnDelete and OnUpdate default to NoAction, matching Postgres.
	OnDelete RefAction
	OnUpdate RefAction
}

Reference is the target of a foreign key. Only single-column foreign keys are expressible; a composite foreign key is yours to install by hand, and the migrator ignores composite ones entirely rather than trying to manage them.

type SQLType 

type SQLType = string

SQLType is a Postgres column type, written as it appears in DDL. 

const (
	Int         SQLType = "INTEGER"
	SmallInt    SQLType = "SMALLINT"
	Int64       SQLType = "BIGINT"
	Serial      SQLType = "SERIAL"
	BigSerial   SQLType = "BIGSERIAL"
	Float32     SQLType = "REAL"
	Float64     SQLType = "DOUBLE PRECISION"
	Text        SQLType = "TEXT"
	VarChar     SQLType = "VARCHAR(255)"
	Bool        SQLType = "BOOLEAN"
	Date        SQLType = "DATE"
	Time        SQLType = "TIME"
	Timestamp   SQLType = "TIMESTAMP"
	TimestampTZ SQLType = "TIMESTAMPTZ"
	UUID        SQLType = "UUID"
	JSON        SQLType = "JSON"
	JSONB       SQLType = "JSONB"
	Blob        SQLType = "BYTEA"
)

func Numeric 

func Numeric(precision, scale int) SQLType

Numeric returns NUMERIC(precision, scale).

func VarChars 

func VarChars(n int) SQLType

VarChars returns VARCHAR(n).

type Table 

type Table struct {
	// Schema is the Postgres schema holding the table. Empty means
	// DefaultSchema. Tables in a slice passed to Migrate may name different
	// schemas, so one call can bring several tenants up to date at once.
	//
	// Migrate does not create the schema; a missing one surfaces as an error
	// from the first CREATE TABLE.
	Schema string

	Name    string
	Columns []Column
	Indexes []Index

	// Unique holds UNIQUE constraints over one or more columns. A single-column
	// constraint can equivalently be written as ColumnFlag.Unique; both produce
	// the same uq_<table>_<column> constraint, and declaring the same column set
	// twice is rejected by validation.
	Unique []UniqueConstraint
}

Table is the desired schema for one table.

type UniqueConstraint added in v0.8.0 

type UniqueConstraint struct {
	Name    string
	Columns []string
}

UniqueConstraint is a UNIQUE constraint over one or more columns.

A constraint and a unique index enforce the same rule, and neither replaces the other. Only a constraint can be named in ON CONFLICT ON CONSTRAINT and appears in information_schema.table_constraints; only an index can be partial or use a non-default operator class. Declare whichever you need — or both.

The constraint is named uq_<table>_<col1>_<col2>… (see uniqueConstraintName). Name is populated by inspection with whatever Postgres reports; it is ignored on the desired side.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.