go-pgx-kit

README

pgx-kit/sql

A lightweight, pgx-native toolkit for PostgreSQL in Go.

It is a thin ergonomic layer over pgx v5 — not a wrapper, not an ORM, not a query builder. You write SQL by hand with @name placeholders; the package handles struct scanning, transaction lifecycle, retries, and connection setup. Everything composes with pgx's own tools (NamedArgs, the row collectors, pgx.Tx) rather than hiding them.

Design

• Composes with pgx, doesn't hide it. Functions take and return pgx types. Drop to raw pgx any time.
• No SQL generation. The package executes and scans; it never builds queries from structs. That keeps the surface tiny and the behaviour predictable.
• Generics for scanning. Row[T] / Rows[T] / Values[T] scan straight into your types by column name.
• Lenient by default. SELECT * and partial structs just work; strict variants are available when you want drift to error.
• Small. A handful of functions, two dependencies (pgx, pgerrcode).

Install

go get codeberg.org/obadness/go-pgx-kit

import "codeberg.org/obadness/go-pgx-kit/sql"

The package is named sql. In the rare file that also imports database/sql, alias one of them, e.g. import pgxkit "codeberg.org/obadness/go-pgx-kit/sql".

Quick start

type User struct {
	ID        int64     `db:"id"`
	Email     string    `db:"email"`
	OrgID     int64     `db:"org_id"`
	Active    bool      `db:"active"`
	CreatedAt time.Time `db:"created_at"`
}

pool, err := sql.Connect(ctx, sql.Config{
	Host: "localhost", 
	Database: "app", 
	User: "app",
	Password: os.Getenv("DB_PASSWORD"), 
	MaxConns: 20,
})
if err != nil {
	log.Fatal(err)
}
defer pool.Close()

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

Reads

Row scans exactly one row. It returns pgx.ErrNoRows when there is none and an error when there is more than one.

u, err := sql.Row[User](ctx, db,
	"select * from users where id = @id", sql.Args{"id": 7})
if errors.Is(err, pgx.ErrNoRows) {
	// not found
}

Rows scans every row into a slice (non-nil and empty when there are no rows).

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

Values scans a single-column result set into a slice of scalars.

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

Row, Rows, StrictRow and StrictRows scan by column name into a struct. For a scalar result use Values — passing a non-struct T to Row panics inside pgx's reflection rather than returning an error.

Strict scanning

Row/Rows are lenient: struct fields with no matching column are left zero. Use StrictRow/StrictRows when every selected column must map to a field and vice versa — handy for catching schema/struct drift.

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

Writes

Exec returns 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 it can. Columns come from the same db struct tags the read helpers scan by, so one struct describes both directions.

type User struct {
	ID          int64     `db:"id"`
	Email       string    `db:"email"`
	PartnerID   *int64    `db:"partner_id"`
	DisplayName string    `db:"display_name"`
	CreatedAt   time.Time `db:"created_at"`
}

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 without it id would be written as 0 and created_at as the zero time rather than falling to BIGSERIAL and DEFAULT now(). Reads can be lenient about a missing column; writes cannot.

An untagged field's column name is its Go name in snake_case (DisplayName → display_name, HTTPCode → http_code) — a name pgx would have matched on the way back in. A wrong guess fails as an undefined_column error, not silently, but tagging the field removes the guess.

On conflict

Conflict adds an ON CONFLICT clause. Empty Update means DO NOTHING; otherwise each named column becomes c = EXCLUDED.c.

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"},
	},
})

Conflict.Where repeats the predicate of a partial unique index verbatim. Postgres will not infer a partial index without it, and since an index cannot be named as a constraint, the inference clause is the only way to reach one — omitting it fails with "there is no unique or exclusion constraint matching the ON CONFLICT specification".

With DO NOTHING the result counts rows actually inserted, not rows offered. A result below len(rows) is the only signal that some already existed, so it is reported rather than smoothed over.

Duplicates within one batch

Two rows in the same statement that share a conflict key make ON CONFLICT DO UPDATE fail with "ON CONFLICT DO UPDATE command cannot affect row a second time". This is deterministic, not a race, and it is the most common way a bulk upsert breaks.

When the target index is not partial, InsertMany finds this before sending and names the rows:

insert into iam_users: rows 0 and 2 share the conflict key (email) = (a@b.com);
ON CONFLICT DO UPDATE cannot touch a row twice, so Postgres would reject this
batch — de-duplicate the rows, or set Conflict.LastWins

Conflict.LastWins keeps the last occurrence instead of failing.

When the target is partial, whether two rows collide depends on which of them the predicate admits, and this package does not evaluate SQL in Go to find out. Rather than reject batches Postgres would have accepted, the check is skipped and Postgres' own error is rewritten into the same explanation on the way out. LastWins is refused there for the same reason — it could discard a row that never conflicted.

Rows whose conflict key contains a NULL are never treated as duplicates: two NULLs are distinct under a unique index.

Statement splitting

A statement carries at most 65535 bind parameters, so InsertMany spends len(columns) per row and splits at 65535 / len(columns) rows. 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:

err := sql.Tx(ctx, pool, func(tx pgx.Tx) error {
	_, err := sql.InsertMany(ctx, tx, "iam_users", users, opts)
	return err
})

The duplicate check runs across the whole slice, not per statement, so which rows are rejected never depends on where a chunk boundary happened to fall.

Getting generated values back

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

R is explicit, T is inferred. The result is not positionally aligned with the input — DO NOTHING returns only the rows it inserted and LastWins drops rows before sending — so match rows up by a key carried in R, never by index.

COPY

CopyInto streams rows with COPY, the fast path for large volumes of data already known to be clean.

n, err := sql.CopyInto(ctx, db, "iam_users", users, sql.Insert{Columns: cols})

It takes a Copier rather than a DB — also satisfied by a pool, a connection and a transaction, so it composes with Tx unchanged. COPY has no ON CONFLICT and no RETURNING, so passing a Conflict is an error rather than being ignored, and one violation anywhere aborts the entire load. Column defaults still apply; row-level triggers fire, rules do not.

	InsertMany	CopyInto
ON CONFLICT	yes	no
RETURNING	via InsertManyReturning	no
statements	one per 65535/columns rows	one stream
a bad row	fails that statement	aborts everything

Unqualified table names are left unqualified and resolve through the connection's search_path, the way hand-written DML does — unlike the migrator, which always qualifies because a catalog lookup has to agree with where the DDL lands. Pass "schema.table" to be explicit.

Transactions

Tx runs a function in a transaction, committing on success and rolling back on error or panic. Because every read/write helper takes a DB (satisfied by *pgxpool.Pool, *pgx.Conn, and pgx.Tx), the same query code runs inside or outside a transaction.

err := sql.Tx(ctx, pool, func(tx pgx.Tx) error {
	if _, err := sql.Exec(ctx, tx,
		"update accounts set balance = balance - @amt where id = @from",
		sql.Args{"amt": amount, "from": from}); err != nil {
		return err
	}
	_, err := sql.Exec(ctx, tx,
		"update accounts set balance = balance + @amt where id = @to",
		sql.Args{"amt": amount, "to": to})
	return err
})

TxOpts adds isolation / access-mode control. TxRetry runs at serializable isolation and retries serialization failures and deadlocks — make the function idempotent, as it may run more than once. Failed attempts back off exponentially with jitter (capped at one second) so transactions that just collided don't line up and collide again, and a cancelled context abandons the wait.

err := sql.TxRetry(ctx, pool, 3, func(tx pgx.Tx) error {
	// ... work that may hit a serialization conflict ...
	return nil
})

Tx, TxOpts, and TxRetry take a Beginner — satisfied by *pgxpool.Pool and *pgx.Conn, but not pgx.Tx (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: it starts a real transaction for a pool/conn and a savepoint for a pgx.Tx.

// Works whether db is a pool or an already-open transaction.
func transfer(ctx context.Context, db sql.DB, from, to, amt int64) error {
	return sql.TxNested(ctx, db, func(tx pgx.Tx) error {
		// ...
		return nil
	})
}

Connections

Connect builds a pool from a Config and verifies it with a ping. Optional mutators reach any pgxpool.Config setting the struct doesn't cover; they run last, so they win over the struct.

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

Config fields (zero values fall back to defaults):

Field	Default	Notes
Host	PGHOST, else pgx's default
Port	PGPORT, else 5432
Database	PGDATABASE, else pgx's default
User	PGUSER, else the OS user
Password	—	assigned to the parsed config, never in a connection string
SSLMode	disable	validated against the six libpq modes; use require or stronger in production
MaxConns	pgx default
MinConns	pgx default
MaxConnLifetime	pgx default (1h)
MaxConnIdleTime	pgx default (30m)
ConnectTimeout	none	per-connection dial timeout

Every field except SSLMode is assigned to the parsed pgx config rather than interpolated into a connection string, so values containing spaces, quotes or = are safe. Leave a field empty to fall back to libpq's environment variables (PGHOST, PGUSER, PGDATABASE, PGPASSWORD, …). SSLMode is the exception — it selects the TLS setup pgx builds while parsing, so it travels through the DSN and is checked against the six accepted values instead.

Named arguments

sql.Args is an alias for pgx.NamedArgs. @name placeholders are rewritten to positional parameters before execution, so all bind-parameter safety applies. Naming a value once and referencing it several times is the main ergonomic win over positional $1.

events, err := sql.Rows[Event](ctx, db,
	"select * from events where actor_id = @uid or target_id = @uid",
	sql.Args{"uid": userID})

Caveat: the rewriter does not understand SQL string literals, so a literal @ in the query body (e.g. inside a quoted string) may be mis-parsed. Use positional args for those statements.

Catching mistakes early

CheckNamed is a test helper that flags @name tokens with no value and keys that are never used. Co-locate your queries and run it in a unit test so typos surface in CI, with zero runtime cost.

func TestNamedArgs(t *testing.T) {
	cases := map[string]struct {
		sql  string
		args sql.Args
	}{
		"byID":  {"select * from users where id = @id", sql.Args{"id": 1}},
		"byOrg": {"select * from users where org_id = @org", sql.Args{"org": 1}},
	}
	for name, tc := range cases {
		t.Run(name, func(t *testing.T) {
			if err := sql.CheckNamed(tc.sql, tc.args); err != nil {
				t.Error(err)
			}
		})
	}
}

Schema migrations

A struct-driven migrator brings tables up to a desired shape: it creates missing tables and applies additive changes to existing ones. It is opinionated and deliberately scoped — it is not a replacement for hand-written migrations when you need fine control, but it removes the boilerplate for the common case of keeping a table in sync with its definition.

Describe the desired schema with Table, Column, and Index:

users := sql.Table{
	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: "org_id", Type: sql.Int64},
		{Name: "active", Type: sql.Bool, Default: sql.DefaultTrue},
		{Name: "created_at", Type: sql.TimestampTZ, Default: sql.DefaultNow},
	},
	Indexes: []sql.Index{
		{ColumnName: "org_id"},
		{ColumnName: "email", IsUnique: true},
		{Columns: []string{"org_id", "active"}}, // composite
	},
}

if err := sql.Migrate(ctx, pool, []sql.Table{users}); err != nil {
	log.Fatal(err)
}

Schemas

Each table names its own schema. Empty means public:

sql.Table{
    Schema: "tenant_a",   // "" => "public"
    Name:   "widgets",
    Columns: []sql.Column{ /* ... */ },
}

Tables in one Migrate call may name different schemas, so a multi-tenant setup migrates in a single pass:

var tables []sql.Table
for _, tenant := range tenants {
    tables = append(tables, widgetsTable(tenant), ordersTable(tenant))
}
tables = append(tables, sharedTable)   // Schema: "" => public

err := sql.Migrate(ctx, pool, tables)

Every statement is schema-qualified and every catalog lookup is scoped to the same schema, so migrations never depend on the connection's search_path. Derived names — indexes and unique constraints — stay unqualified, because they already live in their table's schema and index names are per-schema: the same table replicated across ten tenants gets idx_widgets_code in each, with no collision.

A schema that doesn't exist yet is created. Only genuinely missing ones are: Migrate checks first, so the steady state — every schema already present — issues no schema DDL at all and needs no CREATE privilege on the database. A role granted only USAGE, CREATE on its own schema migrates fine.

sql.Migrate(ctx, pool, tables)                          // creates missing schemas
sql.Migrate(ctx, pool, tables, sql.WithSchemaCreation(false))  // requires them to exist

Turn it off where schemas are provisioned out of band, or to make a typo in Schema fail loudly rather than quietly create a stray schema.

The existence check reads pg_namespace, not information_schema.schemata. The latter is filtered by privilege — a schema your role holds no rights on is simply absent from it — so it would report "missing", and the CREATE SCHEMA IF NOT EXISTS that followed would fail with permission denied for database, naming the wrong object entirely.

Setting search_path on the pool is still the right way to keep queries unqualified, and is independent of the above:

pool, err := sql.Connect(ctx, cfg, func(c *pgxpool.Config) {
    c.ConnConfig.RuntimeParams["search_path"] = "tenant_a"
})

Identifier rules

Schema, table, column and index names must be lower case, unqualified, and at most 63 bytes (Postgres' identifier limit). Migrate validates every name — including the derived idx_<table>_<cols…> index names — before running any DDL, and fails the whole migration if one doesn't qualify.

This is not fussiness. The migrator emits unquoted DDL, and Postgres folds unquoted identifiers to lower case and truncates them at 63 bytes. A name that doesn't survive that round trip can never be found by introspection again: CREATE TABLE IF NOT EXISTS matches the folded name and does nothing, every run takes the create path, and the migration reports success while applying no changes at all. A startup error is much better than that silence.

{Name: "userAccounts"}                    // rejected: must be lower case
{Name: "tenant_b.gadgets"}                // rejected: use Schema instead
{Schema: "tenant_b", Name: "gadgets"}     // fine
{Name: "user_accounts"}                   // fine

A schema-qualified Name is rejected rather than accepted, because it used to be the worst kind of wrong: the table was created correctly on the first run and then silently stopped migrating, since introspection looked for a table literally called tenant_b.gadgets and never found it.

Migrate takes a concrete *pgxpool.Pool (introspection and DDL are startup-time operations, intentionally off the query path). It is safe by default — only additive changes run unless you opt in:

Always (no flag)	Opt-in flag	Covers
add column, add index	—	new columns/indexes
set / drop NOT NULL	—	nullability changes on existing columns
	WithDrops(true)	drop columns and indexes absent from the desired schema
	WithConstraintChanges(true)	set/change/drop column DEFAULT; add/drop column-level UNIQUE
	WithTypeChanges(true)	ALTER COLUMN ... TYPE

One option is on by default:

Default	Flag	Covers
create a missing Table.Schema	WithSchemaCreation(false) disables	CREATE SCHEMA

// Production: additive only.
sql.Migrate(ctx, pool, tables)

// Development: allow everything.
sql.Migrate(ctx, pool, tables,
	sql.WithDrops(true),
	sql.WithConstraintChanges(true),
	sql.WithTypeChanges(true))

Indexes

Each sql.Index is one index. Set ColumnName for a single column, or Columns for a composite (multi-column) index — Columns takes precedence when both are set:

Indexes: []sql.Index{
	{ColumnName: "email", IsUnique: true},                   // single-column, unique
	{Columns: []string{"org_id", "active"}},                 // composite
	{Columns: []string{"tenant_id", "external_id"}, IsUnique: true}, // composite unique
},

The physical name is derived from the columns (idx_<table>_<col1>_<col2>…), so column order matters — (a, b) and (b, a) are different indexes — and changing the column set creates a new index (dropping the old one only under WithDrops).

Toggling IsUnique on an index over the same columns rebuilds it — Postgres has no ALTER INDEX for this, so it is a drop and recreate, and it needs WithConstraintChanges(true). Gaining uniqueness fails if the column already holds duplicates.

Composite uniqueness can be expressed either as a unique composite index (IsUnique: true) or as a sql.UniqueConstraint — see Unique constraints and unique indexes for which does what. Expression indexes and CHECK constraints are still yours to install with hand-written DDL.

Partial indexes

Where makes an index partial: it becomes the WHERE clause of CREATE INDEX, so only rows satisfying it are indexed. On a unique index this narrows uniqueness to those rows:

Indexes: []sql.Index{
	{
		Name:       "uq_users_admin_email",
		ColumnName: "email",
		IsUnique:   true,
		Where:      "partner_id IS NULL",
	},
},

CREATE UNIQUE INDEX uq_users_admin_email ON iam.users (email) WHERE partner_id IS NULL;

One admin email per account with no partner; once partner_id is set, the same address can repeat freely. This is the case a UNIQUE constraint cannot express at all.

Name overrides the derived name, and is required when two indexes cover the same columns — a partial index alongside a full one, or two partial indexes with different predicates. They would otherwise derive the same idx_<table>_<columns>, and since the diff keys indexes by name only one would ever be created. Migrate rejects a colliding pair before running any DDL rather than applying half of it:

invalid schema: table users: two indexes are both named idx_users_email; give one an
explicit Index.Name, which is what indexes over the same columns need — a partial
index alongside a full one, say

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

Postgres does not store the predicate as you wrote it. It parses the expression and reports back its own deparsed form, fully parenthesised and with implicit casts made explicit:

declared	stored by Postgres
partner_id IS NULL	(partner_id IS NULL)
status = 'active'	((status)::text = 'active'::text)
created_at > '2020-01-01'	(created_at > '2020-01-01'::timestamp with time zone)

Comparison strips casts, parentheses, whitespace and case to bring the two back together, so a partial index does not rebuild on every run. It is lossy in the safe direction: predicates differing only in bracketing — (a OR b) AND c against a OR (b AND c) — compare equal, so such a change is missed rather than churned. Rewrite the index by hand if you need one.

Keep literal timestamps out of predicates. A timestamptz literal is not merely cast, it is evaluated at parse time and rendered in the session's TimeZone:

-- declared
WHERE created_at > '2020-01-01'
-- stored, under TimeZone=UTC
WHERE created_at > '2020-01-01 00:00:00+00'::timestamp with time zone
-- stored, under TimeZone=America/New_York
WHERE created_at > '2020-01-01 00:00:00-05'::timestamp with time zone

No normalisation recovers the short form from the long one, and the offset depends on the session that created the index, so such a predicate compares unequal forever: it rebuilds on every run under WithConstraintChanges. (The failure is churn, not a lost change.) date literals are fine — those are cast, not evaluated — as are IS NULL, boolean, numeric and text comparisons. A fixed date cutoff also goes stale in an index predicate, so this is rarely what you want anyway.

Like Column.Default, the predicate is raw SQL emitted verbatim into DDL rather than bound as a parameter. It must come from your source, never from user input.

Foreign keys

A column becomes a foreign key by setting References:

userRoles := sql.Table{
    Name: "user_roles",
    Columns: []sql.Column{
        {Name: "user_id", Type: sql.UUID, Flag: sql.ColumnFlag{PrimaryKey: true},
            References: &sql.Reference{Table: "users", OnDelete: sql.Cascade}},
        {Name: "role_id", Type: sql.UUID, Flag: sql.ColumnFlag{PrimaryKey: true},
            References: &sql.Reference{Table: "roles", OnDelete: sql.Cascade}},
        {Name: "granted_at", Type: sql.TimestampTZ, Default: sql.DefaultNow},
    },
    Indexes: []sql.Index{{ColumnName: "role_id"}},
}

That is the equivalent of:

CREATE TABLE user_roles (
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id uuid NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    granted_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, role_id)
);
CREATE INDEX ON user_roles (role_id);

Two columns flagged PrimaryKey give you the composite primary key — declaration order is the key order, and (a, b) is not (b, a). Reference.Column defaults to "id", and Reference.Schema defaults to the referencing table's own schema — so a schema-per-tenant layout gets tenant-local references for free.

Actions are sql.NoAction (the default), sql.Restrict, sql.Cascade, sql.SetNull and sql.SetDefault, on OnDelete and OnUpdate.

Foreign keys are applied in a second pass, after every table in the call has been created. Slice order therefore doesn't matter, and self-referencing and mutually-referencing tables work without any special handling:

// The child may come first; it makes no difference.
sql.Migrate(ctx, pool, []sql.Table{userRoles, users, roles})

Change	Requires
add a declared foreign key	— (additive)
change its target or action	WithConstraintChanges(true) (drop + re-add)
drop one no longer declared	WithDrops(true)

Constraints are named fk_<table>_<column>. Only single-column foreign keys are expressible; a composite one is invisible to the migrator, so WithDrops will never touch a composite foreign key you installed by hand.

Like ADD ... UNIQUE, ADD ... FOREIGN KEY fails if the column already holds values with no matching parent row. The migration errors and rolls back — a table's foreign keys share one transaction, so it can't half-apply. Clean up the orphans first.

Unique constraints and unique indexes

These are not alternatives — declare whichever you need, or both:

sql.Table{
    Name: "members",
    Columns: []sql.Column{
        {Name: "email", Type: sql.Text, Flag: sql.ColumnFlag{Unique: true}},  // single column
        {Name: "tenant_id", Type: sql.UUID},
        {Name: "nickname", Type: sql.Text},
    },
    Unique: []sql.UniqueConstraint{
        {Columns: []string{"tenant_id", "nickname"}},   // composite constraint
    },
    Indexes: []sql.Index{
        {Columns: []string{"tenant_id", "email"}, IsUnique: true},  // unique index
    },
}

ColumnFlag{Unique: true} and a one-column Unique entry are the same thing — both produce uq_<table>_<column> — and declaring a column set twice is rejected.

Both forms enforce the same rule, but they are not interchangeable:

	UNIQUE constraint	unique index
ON CONFLICT ON CONSTRAINT <name>	yes	no — the index is not a constraint
appears in information_schema.table_constraints	yes	no
partial (Where: "…") or custom operator class	no	yes
can be referenced by a foreign key	yes	yes

Constraints are named uq_<table>_<col1>_<col2>…, and matching is on the column set, not the name — so a table adopted from elsewhere keeps its members_email_key rather than gaining a duplicate under the migrator's name. Column order within the set is significant, since it decides the order of the backing index.

Adding and dropping constraints requires WithConstraintChanges(true); ADD ... UNIQUE fails on existing duplicates.

Column types and defaults

Column.Type takes any SQL type string; the package provides constants for the common ones (sql.Int, sql.Int64, sql.Text, sql.Bool, sql.TimestampTZ, sql.UUID, sql.JSONB, sql.Blob, …) and helpers for parameterised types: sql.VarChars(n) and sql.Numeric(precision, scale).

Column.Default is a raw default expression. The constants below cover the usual cases:

Constant	Emits	Use for
sql.DefaultNow	NOW()	timestamp columns
sql.DefaultUUID	gen_random_uuid()	UUID primary keys
sql.DefaultTrue	true	boolean flags
sql.DefaultFalse	false	boolean flags
sql.DefaultZero	0	numeric counters
sql.DefaultEmptyJSON	'{}'::json	empty JSON object
sql.DefaultEmptyJSONB	'{}'::jsonb	empty JSONB object
sql.DefaultEmptyJSONArray	'[]'::json	empty JSON array
sql.DefaultEmptyJSONBArray	'[]'::jsonb	empty JSONB array

For other JSON literals, sql.JSONDefault(v) / sql.JSONBDefault(v) wrap a value as a ::json / ::jsonb default expression.

Serial columns are exempt from default management. A sql.Serial / sql.BigSerial column gets a nextval('…_seq'::regclass) default from Postgres, which is owned by the sequence rather than declared in your schema — so Column.Default is empty for it. Under WithConstraintChanges(true) that would otherwise look like a default you'd removed, and dropping it would leave the column with no value generator and break every subsequent INSERT. The migrator recognises sequence defaults and leaves them alone.

Logging

The migrator logs each table it creates or alters, and every operation it applies, through slog. By default that's slog.Default(); WithLogger points it somewhere else:

sql.Migrate(ctx, pool, tables, sql.WithLogger(myLogger))
sql.Migrate(ctx, pool, tables, sql.WithLogger(slog.New(slog.DiscardHandler))) // silent

A table that needs no changes is logged at Debug, since that's the steady state on every start-up; creations and alterations are Info.

Caveats

These are inherent to a struct-diff migrator, not bugs — know them before enabling the destructive flags:

• SET NOT NULL fails on existing nulls, and ADD ... UNIQUE fails on existing duplicates. The ALTER errors, the transaction rolls back, and the migration fails loudly. Clean the data with a hand-written migration first.
• Type changes can corrupt or fail when existing values aren't convertible (e.g. text → integer). That's why they sit behind their own flag; risky type migrations belong in hand-written SQL.
• Width and precision changes are not detected. Type comparison comes down to the base type, so VARCHAR(255) → VARCHAR(50) and NUMERIC(10,2) → NUMERIC(4,1) look like no change at all. Resize columns by hand.
• Default-change detection is best-effort. Postgres normalises stored defaults, so comparison is loose; an exotic default expression may re-apply on each run (harmless but noisy). Serial columns are exempt entirely — see above.
• The migrator assumes it owns the table's plain indexes. Under WithDrops(true), any index on the table that isn't in the desired schema is dropped, including one you created by hand. Indexes backing a UNIQUE or PRIMARY KEY constraint are recognised as constraints and left alone. A partial index installed by hand is visible to the migrator, so declare it (Index.Where) or keep it off tables you migrate with WithDrops. Expression indexes are invisible and never dropped.
• Partial index predicates are compared loosely. Postgres stores its own deparsed form of the expression, so comparison strips casts, parentheses, whitespace and case. Two predicates differing only in bracketing compare equal, and such a change is missed rather than applied — see Partial indexes.
• Primary key changes are rejected, not applied. ColumnFlag.PrimaryKey shapes the CREATE TABLE; on an existing table a declared key that disagrees with the actual one fails the migration, before anything else is applied. Changing a key means dropping its constraint and index and rebuilding, which usually belongs in a hand-written migration alongside a backfill — but silently ignoring the difference is worse, so it is loud. A table that declares no key is not checked at all, which keeps adoption on an existing database painless.
• CHECK constraints, expression indexes and composite foreign keys are out of scope. Install them with hand-written DDL; the migrator leaves composite foreign keys alone even under WithDrops.

All alters for a single table run inside one transaction (via the package's own Tx helper), so a table is migrated atomically: either every change for it applies, or none do.

API

Function	Purpose
Row[T] / StrictRow[T]	Scan exactly one row into T
Rows[T] / StrictRows[T]	Scan all rows into []T
Values[T]	Scan a single column into []T
Exec	Run a statement, return rows affected
InsertMany[T]	Bulk insert a slice of structs, return rows written
InsertManyReturning[R, T]	Bulk insert with RETURNING *, scanned into []R
CopyInto[T]	Bulk load a slice of structs with COPY
Tx / TxOpts / TxRetry	Run a function in a transaction
TxNested	Run in a transaction or savepoint (handles pool/conn/tx)
Connect	Open and verify a pool from Config
CheckNamed	Test helper for @name arguments
Migrate	Bring tables up to their desired schema
WithDrops / WithConstraintChanges / WithTypeChanges	Opt-in flags enabling destructive/risky migration steps
WithSchemaCreation	Create a missing Table.Schema (on by default)
WithLogger	Direct the migrator's slog output at a specific logger

Type	Purpose
DB	Query surface (pool, conn, or tx)
Beginner	A DB that can start a transaction (pool or conn)
Copier	A DB that can stream rows with COPY (pool, conn, or tx)
Config	Connection settings for Connect
Args	Alias for pgx.NamedArgs
Table / Column / Index	Desired-schema definitions for Migrate (Table.Schema selects the Postgres schema; Index.Where makes an index partial)
Reference / RefAction	Foreign key target and referential action (Column.References)
UniqueConstraint	A UNIQUE constraint over one or more columns (Table.Unique)
ColumnFlag	Column attributes (primary key, nullable, unique)
MigrateOption	Option passed to Migrate
Insert / Conflict	Bulk-insert column selection and ON CONFLICT clause
SQLType	Column type string, with constants for common types
DefaultSchema	The schema a Table with no Schema belongs to ("public")

Tests

Unit tests need nothing but Go:

go test -race -cover ./...

The migrator's introspection — index column ordering, primary-key detection, constraint-backed index handling, schema scoping — can only be exercised against a real server, so those tests sit behind the integration build tag and a DSN:

GO_PGX_KIT_TEST_DSN='postgres://user:pass@localhost:5432/db?sslmode=disable' \
  go test -tags integration -race -cover ./...

The role in that DSN must be allowed to create and drop tables and schemas. CI runs both, against a postgres:16-alpine service.

License

MIT © 2026 Jeremy Obado