migrate

package module
v0.1.0 Latest Latest
Warning

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

 Go to latest Published: Oct 11, 2026 License: Apache-2.0 Imports: 19 Imported by: 0

README

contrib/migrate — ordering and running DDL

import "github.com/ubgo/sqlb/contrib/migrate"

Orders several packages' tables so foreign keys resolve, from either the ddl statements a library already writes or a schema declaration, and runs a ddl.Script the way its flags say.

It exists as much to prove that schema carries its weight as to be used. A schema is only worth describing if something can act on the description without knowing who wrote it, and this package acts on a Set assembled from packages that never imported each other.

Contents

Deliberately narrow

It orders the statements to create a database that does not yet exist, and it runs a script correctly, and it stops there.

It does not diff against a live database; contrib/vet's CompareTables does that, and reports rather than repairs. Its ledger is opt-in and records, nothing more: Ledger.Apply runs the migrations it is handed, once each, under a lock, and Ledger.Applied reads back what it recorded. Which migrations exist, what they are called, what order they come in and when a deploy runs them stay the application's decision, per ADR-0003: there is no migration directory, no file loader, no command line and no down migration.

So this is not a migration tool in the golang-migrate or Atlas sense, and it does not want to become one.

Installing from DDL

A library already writes its CREATE TABLE with the ddl builders, over the same Col[T] values its queries use. Install takes those statements from every package, orders them by the foreign keys each one's own Describe reports, and returns one ddl.Script:

script, err := migrate.Install(
	migrate.Contribution{Owner: "ubgo/auth", Tables: []migrate.Table{authUsersTable}},
	migrate.Contribution{Owner: "the application", Tables: []migrate.Table{ordersTable}},
)

A migrate.Table is anything with Build and Describe, which pg.CreateTable(...), sqlite.CreateTable(...) and mysql.CreateTable(...) all are once they have a column. Each Contribution names its owner. The order is schema.Set.Sorted over the descriptions, so it is deterministic and cycle-checked. A table two contributors both create is refused with schema.ErrDuplicateTable, naming both owners. A foreign key to a table no contribution creates is refused with schema.ErrMissingTable, because Install is for a whole database installed together. A nil entry in Tables is migrate.ErrNilTable naming its owner, not a panic, and a statement whose Build fails cannot be described, so it fails Install with Build's error. A foreign key to a table in a schema, auth.users, resolves to exactly that table. The same contributions in any order give the same script.

This is the converged form: the table is declared once, as DDL, and the description that orders it is derived from that DDL rather than written beside it. Plan, below, is the older form that starts from a hand-written schema.Set.

Running a script

Execute runs a ddl.Script on one *sql.Conn, honouring the three flags a ddl.Statement carries:

  • an OutsideTransaction statement runs before any transaction opens, because SQLite's PRAGMA foreign_keys is a silent no-op inside one;
  • a statement that is not Transactional, such as Postgres's CREATE INDEX CONCURRENTLY or SQLite's VACUUM, runs alone;
  • a statement with a SkipIf query is skipped when that query returns a row, which is how MySQL writes CREATE INDEX IF NOT EXISTS;
  • consecutive transactional statements share one transaction, which rolls back on any failure. That undoes the whole transaction on Postgres and SQLite, but not on MySQL: MySQL commits every DDL statement implicitly, so a failure there leaves the statements before it applied. Recover by running the script again, which works only when each statement can run twice, as a CREATE TABLE with IfNotExists can and a MySQL CREATE INDEX cannot;
  • a MustReturnNoRows statement is queried, and a row fails the run with ErrCheckFailed. SQLite's PRAGMA foreign_key_check reports a broken reference by returning one.

It takes a connection rather than a pool by design. A setting like PRAGMA foreign_keys applies to one connection, and a pool may run the next statement on another. ExecuteDB pins a connection from a *sql.DB for the whole script. The tests run a SQLite table rebuild through ExecuteDB on a pool of four and show it succeeds, and that a rebuild leaving a dangling reference fails with ErrCheckFailed. TestExecute_PinningIsWhatMakesAConnectionSettingHold reproduces the failure an unpinned runner hits, deterministically: with one of two connections held busy, as a pool under load has, PRAGMA foreign_keys = OFF runs on one connection and the next insert on the other, which still enforces the key and refuses the row. The same script through ExecuteDB writes it.

A ledger and a lock

A Migration is an Owner, a Name and a ddl.Script. NewLedger(dialect, table, lock) keeps a record of each applied migration in a table of its own, DefaultLedgerTable unless told another, and Apply(ctx, conn, migrations...) runs every one the ledger has not recorded, in the order given, and returns the names it applied. ApplyDB pins a connection from a pool.

l, err := migrate.NewLedger(sqlb.PostgresDialect{}, "", migrate.EngineLock(sqlb.PostgresDialect{}, "auth_migrations"))
applied, err := l.ApplyDB(ctx, db,
	migrate.Migration{Owner: "ubgo/auth", Name: "001_users", Script: usersScript},
	migrate.Migration{Owner: "ubgo/auth", Name: "002_sessions", Script: sessionsScript},
)
  • Once each: a migration the ledger has is skipped. Its identity is Owner and Name, so renaming one makes it new.
  • Edited after it ran: each record holds Checksum(script), SHA-256 over every statement and its flags, and a migration whose script no longer matches fails with ErrChecksumMismatch, and nothing after it runs. The database holds what the old script did, and pretending otherwise is how schemas drift.
  • Atomic where the engine allows: the ledger row is the last statement of the migration's own script, so on Postgres and SQLite it commits in the same transaction as the change, and a failure leaves neither. MySQL commits DDL implicitly, so a failure there can leave the change without its row, and the next Apply runs it again: a MySQL migration should be safe to repeat.
  • One deploy at a time: Apply takes its Locker first and holds it throughout, so a second process waits and then finds the work done. EngineLock(dialect, key) is PostgresAdvisoryLock (pg_advisory_lock, released by the server if the connection drops), MySQLNamedLock (GET_LOCK, waiting until the context's deadline or DefaultLockWait), or on SQLite, which has neither, TableLock: a row in a lock table, taken over after StaleAfter (DefaultStaleAfter) from a holder that crashed, and naming its process in Holder. While another connection holds SQLite's write lock the row's insert is refused as busy, and that is treated as the lock being held. A lock not got in time is ErrLocked.
  • Two deploys on one SQLite file: they wait for each other rather than one failing with SQLITE_BUSY, so a caller needs no retry loop. Two refusals used to escape, both measured with two handles on one file under WAL and a busy timeout. A deferred transaction whose first statement reads, a SkipIf guard say, holds a read lock, and SQLite refuses its upgrade to the write lock at once, without consulting busy_timeout, while another connection writes; so the ledger begins its SQLite transactions BEGIN IMMEDIATE, taking the write lock up front where the busy timeout applies. And on a file not yet in WAL mode the connection ApplyDB opens runs PRAGMA journal_mode=WAL, which fails as busy whatever the timeout while another connection is in the file; ApplyDB and AppliedDB retry that open. BEGIN and COMMIT are retried on busy too, so a handle with no busy timeout still waits; all three retries end at the context's deadline, or DefaultLockWait without one, with ErrLocked. Busy is recognised without importing a driver: a Code() int method on the error, as modernc.org/sqlite has, or SQLite's own message, "database is locked".
  • Refusals: a migration with no owner, name or script, or a name used twice, is ErrBadMigration; a dialect other than the three is ErrNoLedgerDialect.
Reading the ledger

Applied(ctx, conn) returns every recorded migration as an AppliedMigration, its Owner, Name, Checksum and AppliedAt in UTC, oldest first, with rows recorded at the same instant in owner then name order. AppliedDB pins a connection from a pool. A database with no ledger table yet returns an empty list, not an error: the table's existence is read from the engine's catalogue (to_regclass on Postgres, information_schema.tables in the current database on MySQL, sqlite_master on SQLite), so any other failure is still returned rather than read as "nothing applied". It takes no lock, since a ledger row commits with its migration. A caller uses it to report a schema's version, or to refuse a database newer than its code without reading the ledger table's columns itself.

rows, err := l.AppliedDB(ctx, db)
for _, r := range rows {
	fmt.Println(r.Owner, r.Name, r.AppliedAt)
}

l.RefuseUnknown() returns a copy of the ledger whose Apply does that refusal itself: when the ledger records a migration it was not given, under an owner it was given, Apply fails with ErrUnknownMigration, naming each such migration as owner/name, before running anything. After a rollback the ledger may hold 003_x while the code knows only 001 and 002; plain Apply skips what it recognises and says nothing, and the old code starts against a schema it was never written for. Owners not given are not judged, because one ledger table can serve several packages that each know only their own migrations. The default is unchanged: a ledger from NewLedger ignores rows it was not given.

strict := l.RefuseUnknown()
_, err := strict.ApplyDB(ctx, db, myMigrations...)
if errors.Is(err, migrate.ErrUnknownMigration) {
	// the database was migrated by a newer release of this package
}

On all three engines the tests apply a history, apply it again to no effect, refuse an edited migration, and hold the lock on one connection while a second deploy starts: it waits, and once released finds the migration applied by the holder and applies nothing. They also show that on SQLite a failed migration leaves no ledger row, read Applied back before and after with every field, and that RefuseUnknown refuses the one unknown row by name, ignores an owner it was not given, and leaves plain Apply as it was. The SQLite concurrency tests hold SQLite's write lock on one connection while ApplyDB starts on another handle, with and without a busy timeout and on a file whose first open must convert it to WAL, and require it to wait and then succeed; a third runs two handles on ten fresh files at once and requires every migration recorded exactly once. Before the fix each of the deterministic ones failed with database is locked, and the fresh-file run failed about one round in five. The lock test is deterministic because a test where two deploys race only fails when their timing lines up, and the first version of this one passed with the lock removed.

Use

Plan is the whole API for ordinary use: a Set in, ordered statements out.

// One package's tables, under the name it is known by.
set, err := schema.New("ubgo/auth", auth.Tables()...)
if err != nil {
	return err
}

// Another's, which reference them. The two never imported each other.
if err := set.Add("the application", billing.Tables()...); err != nil {
	return err
}

stmts, err := migrate.Plan(postgres.Dialect{}, *set)
if err != nil {
	return err
}
for _, s := range stmts {
	if _, err := db.ExecContext(ctx, s); err != nil {
		return err
	}
}

Both New and Add take an owner name, and it is used only in errors. That is worth a parameter: when two modules both declare users, the question is always which two, and a Set that did not record it could only report the name. Plan takes the Set by value, so a plan cannot be changed by a later Add.

The order is topological over foreign keys and deterministic: two runs over the same Set produce the same slice, so the output can be committed to a repository and diffed. A cycle is an error naming the tables in it; a self-reference is not a cycle, which is the case that catches a naive implementation.

CreateTable renders one table if you want to assemble the plan yourself.

Types

SQLType maps a schema.Type to one engine's spelling:

migrate.SQLType(postgres.Dialect{}, col)  // "TEXT", "BIGINT", "TIMESTAMPTZ", ...

An engine that has no spelling for a type returns ErrNoTypeMapping rather than guessing:

if _, err := migrate.SQLType(d, col); errors.Is(err, migrate.ErrNoTypeMapping) {
	// this engine cannot store that type; say so rather than emitting something plausible
}

That is the same fail-closed rule the engine packages follow. A DDL generator that silently substitutes a nearby type produces a table that accepts your writes and loses precision, which is the failure you find in production rather than at the gate.

Indexes

CreateIndexes returns the CREATE INDEX statements for one table, separately from its CREATE TABLE:

stmts := migrate.CreateIndexes(postgres.Dialect{}, t)

They are separate because they run separately in practice — an index on a large table is often built concurrently and long after the table exists — and because Plan needs every table to exist before any index that might reference one. It returns no error: an index declaration that cannot be rendered is a schema problem caught when the table is.

Engines

migrate.Engines()  // the engine names this package can render DDL for

Worth calling rather than assuming. This package renders DDL for fewer engines than sqlb renders queries for, and the list is the honest answer to which.

The table behind both SQLType and Engines is schema's — schema.EngineType and schema.EngineNames — not this package's. It moved there so that contrib/vet, which checks a Col[T] declaration against a live catalogue, reads the same spelling this renderer writes; two copies of what a bigint is would have drifted.

Why this is a separate module

contrib is for what does not belong in the core, and a third-party dependency is only the most common reason. This package has none: it is here because it targets specific engines and emits DDL, and the core stays free of both — free of DDL for the same reason it stays free of drivers.

schema describes and never does. This is the "does", and keeping it outside is what makes that claim checkable rather than a matter of taste.

Documentation

Overview

Package migrate turns a schema declaration into CREATE TABLE statements.

It is the first consumer of github.com/ubgo/sqlb/schema, and it exists as much to prove that layer carries its weight as to be used. A schema is only worth describing if something can act on the description without knowing who wrote it, and this package acts on a Set assembled from packages that never imported each other.

Deliberately narrow. It renders the DDL to create a schema that does not yet exist, in an order that satisfies foreign keys, and it stops there. It does not diff against a live database, because that needs introspection and introspection is engine-specific in a way DDL generation is not; it does not apply anything, because deciding when to run DDL is an operational question and not a library's. Both are reasonable things to build on top, and both would be worse hidden inside this.

It lives under contrib because it targets specific engines. The core stays free of DDL for the same reason it stays free of drivers.

Index

Examples

Constants

View Source 
const DefaultLedgerTable = "sqlb_migrations"

DefaultLedgerTable is the ledger's table unless NewLedger is told another.

View Source 
const DefaultLockWait = 60 * time.Second

DefaultLockWait is how long a lock waits when ctx has no deadline.

View Source 
const DefaultStaleAfter = 15 * time.Minute

DefaultStaleAfter is how old a TableLock row must be to be taken over.

Variables

View Source 
var (
	// ErrChecksumMismatch is a migration whose script changed after the
	// ledger recorded it: the database holds what the old script did, and
	// the code now describes something else.
	ErrChecksumMismatch = errors.New("migrate: a migration changed after it was applied")
	// ErrLocked is a lock another process holds past the context's deadline:
	// the migration lock, or on SQLite the database's own write lock.
	ErrLocked = errors.New("migrate: another process holds the migration lock")
	// ErrBadMigration is a migration with no owner, no name, no statements,
	// or a name used twice in one Apply.
	ErrBadMigration = errors.New("migrate: a migration needs an owner, a name and a script, and a name used once")
	// ErrNoLedgerDialect is a Ledger for a dialect it has no table for.
	ErrNoLedgerDialect = errors.New("migrate: the ledger supports postgres, mysql and sqlite")
	// ErrUnknownMigration is a ledger row Apply was not given, for an owner it
	// was given, from a Ledger made with RefuseUnknown: the database has been
	// migrated by newer code than the code now running, typically after a
	// rollback, and nothing is applied to a schema this code never knew.
	ErrUnknownMigration = errors.New("migrate: the database has a migration this code does not know")
)

The ledger's errors.

View Source 
var ErrCheckFailed = errors.New("migrate: a check statement returned rows")

ErrCheckFailed is returned by Execute when a MustReturnNoRows statement returned a row: the check found a problem, SQLite's foreign_key_check a broken reference.

View Source 
var ErrNilTable = errors.New("migrate: a contribution holds a nil table")

ErrNilTable is returned by Install for a nil entry in a Contribution's Tables, whether the interface itself is nil or holds a nil pointer: a (*pg.CreateTableReady)(nil) is not == nil as a Table, and calling Describe on it panicked.

View Source 
var ErrNoTypeMapping = errors.New("migrate: engine has no mapping for this column type")

ErrNoTypeMapping reports that an engine has no spelling for a portable type.

It is an error rather than a silent fallback to TEXT. A column that was meant to be a timestamp and silently became text is the kind of defect that is found months later in the data, not at migration time.

Functions

func Checksum 

func Checksum(s ddl.Script) string

Checksum is a script's identity for the ledger: SHA-256 over each statement's text, flags and SkipIf guard, so a change to any statement, or to whether it runs in a transaction, is a different migration.

func CreateIndexes 

func CreateIndexes(d render.Dialect, t schema.Table) []string

CreateIndexes renders one CREATE INDEX statement per declared index.

They are separate from CreateTable because every engine here accepts indexes only after the table exists, and because an index is the part of a schema a caller most often wants to create later or not at all.

Example

ExampleCreateIndexes shows indexes rendered separately, because every engine here accepts them only after the table exists. 

package main

import (
	"fmt"

	"github.com/ubgo/sqlb/contrib/migrate"
	"github.com/ubgo/sqlb/dialect/postgres"
	"github.com/ubgo/sqlb/schema"
)

func users() schema.Table {
	return schema.Table{
		Name:       "app_user",
		PrimaryKey: []string{"id"},
		Columns: []schema.Column{
			{Name: "id", Type: schema.TypeUUID},
			{Name: "email", Type: schema.TypeText},
			{Name: "age", Type: schema.TypeInt, Nullable: true},
			{Name: "created_at", Type: schema.TypeTimestamp, Default: "CURRENT_TIMESTAMP"},
		},
		Indexes: []schema.Index{{Name: "app_user_email_key", Columns: []string{"email"}, Unique: true}},
	}
}

func main() {
	for _, s := range migrate.CreateIndexes(postgres.Dialect{}, users()) {
		fmt.Println(s)
	}

}

Output:
CREATE UNIQUE INDEX "app_user_email_key" ON "app_user" ("email")

func CreateTable 

func CreateTable(d render.Dialect, t schema.Table) (string, error)

CreateTable renders one CREATE TABLE statement.

Foreign keys are rendered inline rather than as separate ALTER statements, which is why the creation order from Set.Sorted matters: an inline reference to a table that does not exist yet is rejected by every engine here.

Example

ExampleCreateTable shows DDL generated from a declaration. 

package main

import (
	"fmt"

	"github.com/ubgo/sqlb/contrib/migrate"
	"github.com/ubgo/sqlb/dialect/postgres"
	"github.com/ubgo/sqlb/schema"
)

func users() schema.Table {
	return schema.Table{
		Name:       "app_user",
		PrimaryKey: []string{"id"},
		Columns: []schema.Column{
			{Name: "id", Type: schema.TypeUUID},
			{Name: "email", Type: schema.TypeText},
			{Name: "age", Type: schema.TypeInt, Nullable: true},
			{Name: "created_at", Type: schema.TypeTimestamp, Default: "CURRENT_TIMESTAMP"},
		},
		Indexes: []schema.Index{{Name: "app_user_email_key", Columns: []string{"email"}, Unique: true}},
	}
}

func main() {
	ddl, err := migrate.CreateTable(postgres.Dialect{}, users())
	if err != nil {
		panic(err)
	}
	fmt.Println(ddl)

}

Output:
CREATE TABLE "app_user" (
	"id" UUID NOT NULL,
	"email" TEXT NOT NULL,
	"age" INTEGER,
	"created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY ("id")
)
Example (PerEngine)

ExampleCreateTable_perEngine shows one declaration serving two engines. The difference is the type mapping and the quoting, and nothing in the schema itself changes. 

package main

import (
	"fmt"

	"github.com/ubgo/sqlb/contrib/migrate"
	"github.com/ubgo/sqlb/dialect/sqlite"
	"github.com/ubgo/sqlb/schema"
)

func users() schema.Table {
	return schema.Table{
		Name:       "app_user",
		PrimaryKey: []string{"id"},
		Columns: []schema.Column{
			{Name: "id", Type: schema.TypeUUID},
			{Name: "email", Type: schema.TypeText},
			{Name: "age", Type: schema.TypeInt, Nullable: true},
			{Name: "created_at", Type: schema.TypeTimestamp, Default: "CURRENT_TIMESTAMP"},
		},
		Indexes: []schema.Index{{Name: "app_user_email_key", Columns: []string{"email"}, Unique: true}},
	}
}

func main() {
	ddl, err := migrate.CreateTable(sqlite.Dialect{}, users())
	if err != nil {
		panic(err)
	}
	fmt.Println(ddl)

}

Output:
CREATE TABLE "app_user" (
	"id" TEXT NOT NULL,
	"email" TEXT NOT NULL,
	"age" INTEGER,
	"created_at" TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY ("id")
)

func Engines 

func Engines() []string

Engines reports which engines this package can render DDL for, in a stable order, so a caller can check before it starts rather than after.

func Execute 

func Execute(ctx context.Context, conn *sql.Conn, s ddl.Script) error

Execute runs a Script on one connection, honouring the three flags a Statement carries, and is the reference runner for them:

  • OutsideTransaction statements run on their own, before any transaction is opened and after the one before them commits, because SQLite's PRAGMA foreign_keys is a silent no-op inside one;
  • statements that are not Transactional, Postgres's CREATE INDEX CONCURRENTLY among them, run alone and outside a transaction;
  • consecutive transactional statements run in one transaction, committed before the next statement that must stand alone, and rolled back on any failure. On Postgres and SQLite that undoes every statement of the transaction. MySQL commits each DDL statement implicitly, so there a failure leaves the statements before it applied. Running the script again recovers only if each statement can run twice: a CREATE TABLE written with IfNotExists can, a MySQL CREATE INDEX cannot, since MySQL has no IF NOT EXISTS for it (docs/ddl/not-built.md has the check);
  • a MustReturnNoRows statement is queried, and a row fails the run with ErrCheckFailed, because executing it and ignoring the result reports success whatever it found;
  • a statement with a SkipIf query is skipped when that query returns a row, which is how MySQL's CREATE INDEX IF NOT EXISTS is written.

It takes a *sql.Conn rather than a pool on purpose. A connection-scoped setting such as PRAGMA foreign_keys applies to one connection, and a pool is free to run the next statement on another, so a rebuild through a *sql.DB could turn enforcement off on one connection and copy the table on a different one that still enforces it. ExecuteDB pins a connection for a caller holding a pool.

It holds no ledger and takes no lock: which scripts run, once, and in what order across deploys is the application's decision, per ADR-0003.

func ExecuteDB 

func ExecuteDB(ctx context.Context, db *sql.DB, s ddl.Script) error

ExecuteDB pins one connection from db for the whole script and runs Execute on it.

func Install 

func Install(contribs ...Contribution) (ddl.Script, error)

Install orders every contributed CREATE TABLE so each table is created after the tables its foreign keys reference, across contributors that never imported each other, and returns the statements as one Script.

The ordering is schema.Set.Sorted over each statement's Describe, so it is the same deterministic, cycle-checked order Plan computes from a declared Set. A table two contributors both create is refused with schema.ErrDuplicateTable naming both owners, and a foreign key to a table no contribution creates with schema.ErrMissingTable: Install is for a whole database, installed together. A nil table is ErrNilTable rather than a panic, since a Tables slice built conditionally is where one comes from. No contributions give an empty Script.

func Plan 

func Plan(d render.Dialect, s schema.Set) ([]string, error)

Plan renders every statement needed to create a whole schema, in an order that can be executed top to bottom.

This is the function the schema layer was built for. The ordering comes from Set.Sorted, which resolves foreign keys across contributors, so a library and an application are migrated together by code that imported neither.

Example

ExamplePlan shows the whole thing: two packages that never imported each other, ordered and rendered by a third that imported neither. 

package main

import (
	"fmt"
	"strings"

	"github.com/ubgo/sqlb/contrib/migrate"
	"github.com/ubgo/sqlb/dialect/postgres"
	"github.com/ubgo/sqlb/schema"
)

func users() schema.Table {
	return schema.Table{
		Name:       "app_user",
		PrimaryKey: []string{"id"},
		Columns: []schema.Column{
			{Name: "id", Type: schema.TypeUUID},
			{Name: "email", Type: schema.TypeText},
			{Name: "age", Type: schema.TypeInt, Nullable: true},
			{Name: "created_at", Type: schema.TypeTimestamp, Default: "CURRENT_TIMESTAMP"},
		},
		Indexes: []schema.Index{{Name: "app_user_email_key", Columns: []string{"email"}, Unique: true}},
	}
}

func main() {
	set, err := schema.New("ubgo/auth", users())
	if err != nil {
		panic(err)
	}
	err = set.Add("the application", schema.Table{
		Name:       "invoice",
		PrimaryKey: []string{"id"},
		Columns: []schema.Column{
			{Name: "id", Type: schema.TypeUUID},
			{Name: "user_id", Type: schema.TypeUUID},
			{Name: "total", Type: schema.TypeDecimal},
		},
		ForeignKeys: []schema.ForeignKey{{
			Name: "invoice_user_fk", Columns: []string{"user_id"},
			RefTable: "app_user", RefColumns: []string{"id"}, OnDelete: schema.Cascade,
		}},
	})
	if err != nil {
		panic(err)
	}

	plan, err := migrate.Plan(postgres.Dialect{}, *set)
	if err != nil {
		panic(err)
	}
	for i, s := range plan {
		first, _, _ := strings.Cut(s, "\n")
		fmt.Printf("%d. %s\n", i+1, first)
	}

}

Output:
1. CREATE TABLE "app_user" (
2. CREATE TABLE "invoice" (
3. CREATE UNIQUE INDEX "app_user_email_key" ON "app_user" ("email")

func SQLType 

func SQLType(d render.Dialect, c schema.Column) (string, error)

SQLType returns the engine's spelling for a column.

A column with Raw set is returned verbatim, which is the documented escape hatch: the schema layer cannot express every engine's type, and pretending otherwise would be worse than a visible per-column override.

Example

ExampleSQLType shows the one genuinely engine-specific part of DDL. 

package main

import (
	"fmt"

	"github.com/ubgo/sqlb/contrib/migrate"
	"github.com/ubgo/sqlb/dialect/postgres"
	"github.com/ubgo/sqlb/dialect/sqlite"
	"github.com/ubgo/sqlb/schema"
)

func main() {
	c := schema.Column{Name: "at", Type: schema.TypeTimestamp}
	pgType, _ := migrate.SQLType(postgres.Dialect{}, c)
	liteType, _ := migrate.SQLType(sqlite.Dialect{}, c)
	fmt.Println("postgres:", pgType)
	fmt.Println("sqlite:  ", liteType)
	fmt.Println("engines: ", migrate.Engines())

}

Output:
postgres: TIMESTAMPTZ
sqlite:   TEXT
engines:  [clickhouse mysql postgres sqlite]

Types

type AppliedMigration 

type AppliedMigration struct {
	Owner, Name, Checksum string
	// AppliedAt is when Apply recorded the migration, in UTC, to the
	// nanosecond where the clock had it.
	AppliedAt time.Time
}

AppliedMigration is one row of the ledger: which migration ran, the checksum of the script that ran, and when.

type Contribution 

type Contribution struct {
	Owner  string
	Tables []Table
}

Contribution is one package's tables, under the owner name a duplicate is reported against.

type Ledger 

type Ledger struct {
	// contains filtered or unexported fields
}

Ledger applies migrations once each, recording them in a table of its own.

func NewLedger 

func NewLedger(d render.Dialect, table string, lock Locker) (Ledger, error)

NewLedger makes a ledger for d, keeping its records in table (empty means DefaultLedgerTable), serialized by lock. EngineLock(d) is the usual lock: Postgres's advisory lock, MySQL's named lock, a lock row on SQLite.

func (Ledger) Applied 

func (l Ledger) Applied(ctx context.Context, conn *sql.Conn) ([]AppliedMigration, error)

Applied returns every migration the ledger records, oldest first; two recorded at the same instant come in owner, then name, order, so the result is deterministic. A database with no ledger table has applied none, and that is an empty list rather than an error, because a database nobody has migrated yet is an ordinary thing to ask about. The table's existence is read from the engine's catalogue rather than inferred from an error, so a failure that is not a missing table is still returned.

It takes no lock: a row commits with its migration, so a concurrent Apply shows each migration as either applied or not, never half.

func (Ledger) AppliedDB 

func (l Ledger) AppliedDB(ctx context.Context, db *sql.DB) ([]AppliedMigration, error)

AppliedDB pins one connection from db and runs Applied on it.

func (Ledger) Apply 

func (l Ledger) Apply(ctx context.Context, conn *sql.Conn, ms ...Migration) (applied []string, err error)

Apply runs each migration the ledger has not recorded, in the order given, and records it; it returns the names it applied as "owner/name". It takes the lock first and holds it throughout, so a second process running Apply at the same time waits and then finds the work done.

A migration already recorded with the same checksum is skipped; one recorded with another fails with ErrChecksumMismatch and nothing after it runs. The ledger row is the last statement of the migration's script, so on Postgres and SQLite, where DDL is transactional, the row and the change commit together when the script's last statements are transactional; on MySQL, which commits every DDL statement implicitly, a failure part-way leaves the change without its row, and the next Apply runs the migration again, which is why a MySQL migration should be safe to repeat.

From a Ledger made with RefuseUnknown, a recorded migration it was not given, under an owner it was given, fails with ErrUnknownMigration before anything runs.

On SQLite every transaction begins IMMEDIATE, so two deploys on one file wait for each other instead of one failing with SQLITE_BUSY; see ledger_sqlite.go for the two refusals that used to escape.

func (Ledger) ApplyDB 

func (l Ledger) ApplyDB(ctx context.Context, db *sql.DB, ms ...Migration) ([]string, error)

ApplyDB pins one connection from db and runs Apply on it. On SQLite an open refused because another connection is converting the file to WAL is waited out, as Apply waits out the write lock.

func (Ledger) RefuseUnknown 

func (l Ledger) RefuseUnknown() Ledger

RefuseUnknown returns a copy of l whose Apply fails with ErrUnknownMigration, before applying anything, when the ledger records a migration it was not given under an owner it was given. It is how a library refuses to run against a database a newer release of itself migrated: after a rollback the ledger may hold 003 while the code knows only 001 and 002, and plain Apply would skip what it recognises and start the old code on a schema it was never written for. Owners Apply was not given are not judged, because one ledger table can serve several packages, each knowing only its own migrations. l itself is unchanged.

type Locker 

type Locker interface {
	Lock(ctx context.Context, conn *sql.Conn) error
	Unlock(ctx context.Context, conn *sql.Conn) error
}

Locker serializes migration runs across processes. Lock blocks until the lock is held or ctx ends, and Unlock releases it; both run on the pinned connection Apply is given, since a session lock belongs to one connection.

func EngineLock 

func EngineLock(d render.Dialect, key string) Locker

EngineLock is the lock each engine has: a session advisory lock on Postgres, a named lock on MySQL, and TableLock on SQLite, which has neither. key names the lock; two ledgers with the same key serialize, and ones with different keys do not.

type Migration 

type Migration struct {
	Owner  string
	Name   string
	Script ddl.Script
}

Migration is one named change, owned by a package: its Script runs once, and the ledger remembers it by Owner and Name. Name is the migration's identity for ever: renaming one makes it new, and editing its Script after it ran fails Apply with ErrChecksumMismatch rather than being silently skipped or silently re-run.

type MySQLNamedLock 

type MySQLNamedLock struct{ Name string }

MySQLNamedLock is GET_LOCK: a named lock the server releases if the session ends. MySQL takes its wait in whole seconds, so the wait is ctx's remaining time rounded up, or DefaultLockWait without a deadline.

func (MySQLNamedLock) Lock 

func (m MySQLNamedLock) Lock(ctx context.Context, conn *sql.Conn) error

Lock waits for the lock and fails with ErrLocked when the wait runs out.

func (MySQLNamedLock) Unlock 

func (m MySQLNamedLock) Unlock(ctx context.Context, conn *sql.Conn) error

Unlock releases it.

type PostgresAdvisoryLock 

type PostgresAdvisoryLock struct{ Key int64 }

PostgresAdvisoryLock is pg_advisory_lock: a session lock the server releases if the connection drops, so a crashed deploy never leaves it held.

func (PostgresAdvisoryLock) Lock 

func (p PostgresAdvisoryLock) Lock(ctx context.Context, conn *sql.Conn) error

Lock waits for the lock; a cancelled ctx cancels the wait.

func (PostgresAdvisoryLock) Unlock 

func (p PostgresAdvisoryLock) Unlock(ctx context.Context, conn *sql.Conn) error

Unlock releases it.

type Table 

type Table interface {
	Build() (ddl.Script, error)
	Describe() (schema.Table, error)
}

Table is a library's CREATE TABLE, as the ddl package and an engine package build it: pg.CreateTable(t)... satisfies it. It is what Install takes, so a table is declared once, in the DDL a library already writes, and its description for ordering is derived from that statement rather than written beside it.

type TableLock 

type TableLock struct {
	Dialect    render.Dialect
	Table      string
	StaleAfter time.Duration
	// Holder names this process in the row; empty uses a random value.
	Holder string
}

TableLock is a lock held as the one row of a table, for an engine with no session lock, SQLite. While another connection holds SQLite's write lock the row's INSERT is refused outright, SQLITE_BUSY rather than a duplicate key, and that is treated as the lock being held: the wait continues until ctx ends or, with no deadline, until StaleAfter has passed. A row older than StaleAfter is taken to be a crashed holder's and is replaced, which a session lock never needs, so pick StaleAfter longer than any migration takes. It works on any engine; the session locks are better where they exist.

func (TableLock) Lock 

func (t TableLock) Lock(ctx context.Context, conn *sql.Conn) error

Lock inserts the lock row, waiting while another holder's fresh row is there, and fails with ErrLocked when ctx ends first.

func (TableLock) Unlock 

func (t TableLock) Unlock(ctx context.Context, conn *sql.Conn) error

Unlock deletes the lock row. A waiting TableLock writes to the same table while it polls, and SQLite refuses a write while another connection's is in progress, so the delete is retried through that contention, for as long as StaleAfter at most; a failed first attempt was what the race detector showed, leaving the lock held until it went stale.

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.