migrate

func Diff ¶

func Diff(current, target *schema.Registry, opts ...Option) ([]Change, error)

Diff computes the changes that take current to target.

It is a pure function over two registries rather than a comparison between a registry and a live database, which is the point: introspection produces the same *schema.Registry the DSL produces, so the same machinery generates a migration forwards and an import backwards, and the whole engine is testable without a database (ADR-0014).

One case where the two registries are not comparable as they stand ¶

Constraints are compared by their definition text, and for a CHECK that text is not the same on both sides. Postgres stores a check as a parse tree and hands back a canonical spelling — fully parenthesised, with explicit casts on literals — so `status <> 'done'` comes back from introspect as `(status <> 'done'::text)`. Diffing a declared registry against an introspected one therefore proposes dropping and re-adding every check they have in common, forever (issue #24).

The same is true of a partial index's WHERE, which Postgres stores the same way — a declared `latitude IS NOT NULL` comes back as `(latitude IS NOT NULL)`, and the diff proposes creating an index that is already there with DDL that reads identically (issue #63).

Call shadow.Normalize on the declared registry first, which puts both through the same normalisation by asking a Postgres. That is a separate call rather than something Diff does, because doing it here would mean taking a database — and being a pure function over two registries is what the paragraph above is about. `sqlb migrate` makes the call; anything diffing against introspect.Registry output should too.

A caller that skips it gets a diff whose statements look identical to what the database already has, which is the part of both reports that cost the most to work out. So a rebuilt index and a replaced CHECK whose expression differs only in formatting say so in their Comment — an explanation, never a decision: see onlyThePredicateFormattingDiffers for why that distinction is the whole of its safety.

Enums are unaffected: an enum is text plus a CHECK (ADR-0017), and introspect reads the values back out of the normalised form rather than comparing it.

What Destructive means here ¶

A change is marked Destructive when applying it can lose data that cannot be recovered by reversing it: dropping a table or column, or a type change that is not a widening. Adding NOT NULL to an existing column is included, because the fix for a failure is a backfill rather than a retry. Changes that merely fail loudly — adding a CHECK that existing rows violate, removing a value from an enum — are not destructive, since nothing is lost; they carry a Comment saying what to check instead.

What Lock means here ¶

Most DDL is a catalog write that nobody notices. A few statements hold their lock for a time proportional to the number of rows — because they rewrite the table, scan it, or build an index over it — and those are the ones that turn a routine migration into an outage. A change that does one carries Lock and Hazard, naming the lock and the sequence to use instead on a table too large to hold it.

It is a note rather than a refusal, and unlike a destructive change it is not commented out. Whether a full scan matters depends on how many rows the table holds, which is not in the schema and never will be. Commenting out every SET NOT NULL would make the generator useless for the ordinary case and train people to uncomment without reading — which is how the destructive guard would stop working too. Migration.Blocking is the hook for a project that does know which of its tables are big.

For the changes whose remedy is a fixed rewrite — adding a CHECK, a FOREIGN KEY or a UNIQUE, requiring a column — Unblock performs it, moving the scan or the index build out from under the lock. It is called rather than applied automatically, for the same reason the hazard is a note: the sequence is longer, splits the migration across files, and buys nothing on a table small enough that the scan is instant. What is left after Unblock is the type change, which has no mechanical alternative at all.

What waits for what ¶

A commented-out change is a statement that will not run, so anything needing what it would have created cannot run either. Adding a column NOT NULL with no default is destructive and comes out commented out; the UNIQUE, the CHECK and the index over that column would then fail with "column does not exist", turning a file that was meant to be a reviewable no-op into a migration that dies halfway. Those changes carry DependsOn and are commented out with it, so that one decision uncomments the whole set. See markDependents.

What is not inferred ¶

A rename is indistinguishable from a drop and an add when only the before and after states are known, so it has to be declared: schema.RenamedFrom says a column or a table used to be called something else, and the diff emits ALTER TABLE … RENAME. Without the hint a rename is a drop and an add, which is correct, lossy, and never silently wrong. Inferring one from a similar name and type is the tempting alternative and is rejected on consequence asymmetry: a wrong inference destroys a column of production data, a missing one costs a hint (ADR-0014).

Ordering ¶

Changes are ordered so that each one's dependencies already exist and nothing is dropped out from under something that still refers to it:

0. CREATE EXTENSION, before anything that declares a column of its type
1. CREATE TABLE for new tables
2. DROP INDEX for removed and changed indexes — before the columns they cover can disappear
3. DROP CONSTRAINT, foreign keys first, since a foreign key depends on the unique or primary key constraint it points at
4. RENAME, of tables, columns, constraints and indexes
5. ADD COLUMN and ALTER COLUMN
6. DROP COLUMN
7. ADD CONSTRAINT, foreign keys last, once every table and column exists
8. CREATE INDEX
9. DROP TABLE

Rendering reverses this list for the Down section, which is exactly the mirror of it, so reversibility falls out of the ordering rather than being arranged separately.

The renames sit where they do because that is the only place both sides work out. Everything before them is expressed in the old names and everything after in the new ones — and because the Down runs the list backwards, each half is reversed while the names it was written against are the ones in effect. Putting the renames first instead would leave every drop's Down re-adding a constraint against a column that no longer answers to that name.

package main

import (
	"fmt"

	"github.com/jryannel/sqlb/migrate"
	"github.com/jryannel/sqlb/schema"
)

func main() {
	current := schema.NewRegistry()
	current.Table("posts",
		schema.UUIDv7("id").PrimaryKey(),
		schema.Text("title"),
	)

	// The same table after someone added a column to the schema file.
	target := schema.NewRegistry()
	target.Table("posts",
		schema.UUIDv7("id").PrimaryKey(),
		schema.Text("title"),
		schema.BigInt("view_count").Default(schema.Value(0)),
	)

	changes, err := migrate.Diff(current, target)
	if err != nil {
		panic(err)
	}
	for _, c := range changes {
		fmt.Println(c.Up)
		fmt.Println(c.Down)
	}
}

Output:
ALTER TABLE "posts" ADD COLUMN "view_count" bigint NOT NULL DEFAULT 0;
ALTER TABLE "posts" DROP COLUMN "view_count";

package main

import (
	"fmt"

	"github.com/jryannel/sqlb/migrate"
	"github.com/jryannel/sqlb/schema"
)

func main() {
	current := schema.NewRegistry()
	current.Table("posts",
		schema.UUIDv7("id").PrimaryKey(),
		schema.Text("title"),
		schema.Text("legacy_slug"),
	)

	target := schema.NewRegistry()
	target.Table("posts",
		schema.UUIDv7("id").PrimaryKey(),
		schema.Text("title"),
	)

	changes, err := migrate.Diff(current, target)
	if err != nil {
		panic(err)
	}
	for _, c := range changes {
		fmt.Println(c.Up)
		fmt.Println("destructive:", c.Destructive)
		fmt.Println("reason:", c.Reason)
	}
}

Output:
ALTER TABLE "posts" DROP COLUMN "legacy_slug";
destructive: true
reason: dropping posts.legacy_slug deletes its contents. The Down restores the column but not the values

package main

import (
	"fmt"

	"github.com/jryannel/sqlb/migrate"
	"github.com/jryannel/sqlb/schema"
)

func main() {
	current := schema.NewRegistry()
	current.Table("authors",
		schema.UUIDv7("id").PrimaryKey(),
		schema.Text("email"),
	)

	target := schema.NewRegistry()
	target.Table("authors",
		schema.UUIDv7("id").PrimaryKey(),
		schema.Text("email_address").RenamedFrom("email"),
	)

	changes, err := migrate.Diff(current, target)
	if err != nil {
		panic(err)
	}
	for _, c := range changes {
		fmt.Println(c.Up)
	}
}

Output:
ALTER TABLE "authors" RENAME COLUMN "email" TO "email_address";

func Unblock ¶

func Unblock(changes []Change) []Change

Unblock replaces the changes that hold a long lock with the sequences that do not, where such a sequence exists. There are three:

• An ADD CONSTRAINT that would scan the table — a CHECK or a FOREIGN KEY — becomes an ADD ... NOT VALID and a VALIDATE CONSTRAINT in a later migration, moving the scan under a lock writers pass through.
• A SET NOT NULL becomes the same pair with the requirement set between them, since Postgres accepts a validated check as proof and skips its own scan.
• A UNIQUE or PRIMARY KEY, which has no NOT VALID form because there is no way to build an index without reading every row, becomes a CREATE UNIQUE INDEX CONCURRENTLY and an ADD CONSTRAINT ... USING INDEX that adopts it.

A type change is left alone. Rewriting a table has no in-place form at all: the alternative is a second column, a batched backfill and a cutover, and only the person doing it knows what a batch costs or when the cutover can happen.

It is a deliberate act rather than the default, for two reasons. The sequence is longer, splits the migration across files, and buys nothing on a table small enough that the scan is instant — which most tables are. And none of them is equivalent under failure. A plain statement that meets a bad row leaves nothing behind; these leave a constraint in place unvalidated and binding, or an invalid index that has to be dropped before the migration can be retried. That is usually the right trade on a large table and it is still a different outcome, so it is chosen rather than assumed.

The end state on success is identical, which is what makes the substitution safe: the temporary check a SET NOT NULL needs is dropped by the same sequence that created it, and the index a unique constraint adopts is built under the name the constraint will take.

The usual shape is to look before deciding:

changes, err := migrate.Diff(current, target)
if len(migrate.Migration{Changes: changes}.Blocking()) > 0 {
	changes = migrate.Unblock(changes)
}

Changes with no alternative are passed through untouched and still report themselves through Blocking.

func ByName ¶

func ByName(name string) (Format, error)

ByName resolves a format from configuration.

func Split ¶

func Split(m Migration) []Migration

Split separates changes that cannot share a file.

Transaction control in both goose and golang-migrate is per file, not per statement, so a change needing a transaction of its own needs a file of its own. A migration containing CREATE INDEX CONCURRENTLY must disable transactions for everything in it — which would silently remove the rollback guarantee from every other change generated at the same time — and a VALIDATE CONSTRAINT must land after the transaction holding the ADD CONSTRAINT it validates has committed. Splitting keeps the ordinary changes transactional and gives each of the others what it needs.

Files come out in stage order, so the tables, columns and indexes each one depends on exist by the time it runs.

func MinPostgres ¶

func MinPostgres(major int) Option

MinPostgres declares the oldest Postgres major version the generated migration has to run on, which lets the DDL layer use a built-in where one exists instead of requiring an extension.

Today it changes exactly one thing. schema.GenUUIDv7 emits uuid_generate_v7(), which is the pg_uuidv7 extension's spelling — so a migration for a UUIDv7 primary key does not apply to a stock Postgres at all. Postgres 18 has uuidv7() built in, and MinPostgres(18) emits that instead.

Unset means the old spelling, which is the behaviour every migration generated before this option existed already has. A default that silently changed emitted DDL would be the one mistake ADR-0014 says is not recoverable by regenerating.

Pass it consistently across a project. Generating one migration with it and the next without leaves a table whose columns default through two different spellings of the same generator — harmless to the database, confusing to read, and a diff will not flag it because both import back to the same schema.GenUUIDv7.