Hand-written changes

Some things your models can't express. Each section here is a home for one of them.

An empty migration

diff --empty writes a blank file for extensions, types, triggers, or anything else. Your models and snapshot stay as they are, and if the models changed too, snapshot accepts them.

go run ./cmd/migrate diff enable_citext --empty

Views and functions

Files in migrations/code run after the versioned ones, in name order, and again whenever they change. That way the file stays the one definition of the view or function, and git keeps its history. Number them if one depends on another, as in 10_views/ and 20_functions/. Each file has to be safe to run again.

migrations/code/10_post_stats.sql

DROP VIEW IF EXISTS post_stats;
CREATE VIEW post_stats AS SELECT user_id, count(*) AS posts FROM posts GROUP BY user_id;

Data, in Go

For changes SQL can't express, register a migration in Go. It runs once, in order with the files, inside a transaction, and tx is your context bound to it.

func init() {
	migrate.Register[AppDB](
		"0005_split_names",
		func(ctx context.Context, tx *AppDB) error {
			_, err := ant.Raw[any](tx, "UPDATE users SET name = trim(name)").Exec(ctx)

			return err
		},
	)
}

Put it in a package that both cmd/migrate and any program calling migrate.Upfunc Up[DB any](ctx context.Context, db *DB, config ...Config) errorUp applies pending migrations, then repeatable ones that are new or changed. import. A model describes today's schema, not the schema at that point in history, so prefer ant.Rawfunc Raw[T any](h Handle, query string, args ...any) *RawQuery[T]Raw prepares a raw SQL statement. in code that has to outlive a model change. For work too big for one transaction, add migrate.NoTransactionfunc NoTransaction(m *Migration)NoTransaction runs a Go migration outside a transaction, for work on large tables that commits in batches. and commit in batches.

Statements that refuse a transaction

-- ant:no-transaction at the top of a file runs it without one, a statement at a time. .Concurrently() on an index adds it for you.

-- ant:no-transaction
CREATE INDEX CONCURRENTLY "posts_title_idx" ON "posts" ("title");

If such a file fails partway, up stops until someone has looked: see troubleshooting.

Seed data

seed runs Go seeders, each in its own transaction, with model hooks off. They run whenever you ask, so write them to be safe to repeat.

func init() {
	migrate.Seed[AppDB]("roles", func(ctx context.Context, db *AppDB) error {
		_, _, err := db.Roles.FirstOrCreate(ctx, &Role{Name: "admin"}, ant.Eq(&R.Name, "admin"))

		return err
	})
}

go run ./cmd/migrate seed

Config.SeedHooks turns hooks on. For data that should track a file, such as a list of countries, use an idempotent INSERT ... ON CONFLICT in migrations/code.

Tests and prototypes

db.EnsureCreated(ctx)func (c *Context) EnsureCreated(ctx context.Context) errorEnsureCreated creates the tables, indexes, and foreign keys of models that have no table yet, directly from the structs, and leaves existing tables as they are. builds tables straight from the structs, and db.EnsureDeleted(ctx)func (c *Context) EnsureDeleted(ctx context.Context) errorEnsureDeleted drops the tables of the context's models, with their data and whatever depends on them. removes them. go run ./cmd/migrate reset wipes a local database and applies everything. It asks for the database's name first, or takes --confirm name.

See it tested: tests/features/migrations/data_test.go