Migrations and Sync

Quark offers two ways to get a schema into a database, and most projects use both:

Path	Best for
client.Migrate / client.Sync	Development, tests, prototypes, additive evolution.
github.com/jcsvwinston/quark/migrate	Ordered, reviewable, reversible production migrations.

The first reads your models and creates or adjusts tables on the spot. The second runs ordered Up/Down functions that you wrote and reviewed. Start on the first while the schema is young, and move to the second once a table holds real data.

The quark CLI wraps the versioned path (quark migrate create / up / down / status). Everything on this page also works from your own Go command, from app startup, or from CI.

Create tables with Migrate

Migrate creates missing tables from your model metadata:

if err := client.Migrate(ctx, &User{}, &Order{}); err != nil {
    return err
}

It reads your tags and field types:

Metadata	DDL effect
db:"column" + Go type	A column of the dialect-specific type.
pk:"true"	Primary key (multiple tags → composite PK).
quark:"not_null" / nullable:"false"	NOT NULL.
quark:"unique"	UNIQUE.
default:"value"	DEFAULT value (bool defaults normalized per dialect).
rel:"many_to_many" m2m:"..."	A join table.

Field types map to each dialect's own column types:

Go type	SQL type
integer PK	auto-increment / identity primary key
string PK	VARCHAR(36) / NVARCHAR(36)
string	text / varchar
ints, floats	integer / real
bool	boolean / bit / numeric per dialect
time.Time	timestamp / datetime
pointer fields	unwrapped, nullable unless constrained

Migrate is idempotent wherever the engine supports CREATE TABLE IF NOT EXISTS, so re-running it is safe. It is also deliberately additive: it creates what is missing, and never alters or drops. To change an existing table, use Sync or a versioned migration.

For richer column types, see the rich types in Modeling, or create the column in a versioned migration and map it with a db tag.

Evolve a schema with Sync

Sync compares your model to the live table and applies the difference:

err := client.Sync(ctx, quark.SyncOptions{}, &User{})

Change	Behavior
Missing table	Calls Migrate (unless DryRun).
New db-tagged field	Adds the column.
quark:"rename:old_col"	Renames old_col to the current db name.
Removed field	Drops the column only when SafeMigrations is false.

Sync does not change column types or rewrite constraints on an existing column. Use a versioned migration for those.

Preview a sync before applying it with DryRun, and attach a logger to see the planned SQL:

err := client.Sync(ctx, quark.SyncOptions{DryRun: true}, &User{})

DryRun logs the add, rename, and drop statements without executing them. NoTransaction disables the transactional DDL wrapping. That wrapping applies where the dialect's SupportsTransactionalDDL() is true — PostgreSQL, SQL Server and SQLite; MySQL, MariaDB and Oracle commit each DDL statement implicitly, so Quark runs their sync steps without a transaction anyway. Oracle's dialect used to answer true, and Sync wrapped its steps in a transaction that could not roll them back.

Renaming a column

type User struct {
    ID       int64  `db:"id" pk:"true"`
    FullName string `db:"full_name" quark:"rename:name"`
}

err := client.Sync(ctx, quark.SyncOptions{}, &User{})

If the table has name but not full_name, Quark emits the dialect's rename DDL; if neither exists, it adds full_name.

Safe migrations

SafeMigrations defaults to true, which means Sync won't drop a column that's gone from the model. Opt into destructive drops explicitly:

limits := quark.DefaultLimits()
limits.SafeMigrations = false
client, _ := quark.New("postgres", dsn, quark.WithLimits(limits))

Prefer a versioned migration for destructive changes, so the review captures the data migration and rollback plan too.

Indexes and foreign keys

err := client.CreateIndex(ctx, "users", "idx_users_email", []string{"email"}, true)
// last arg = unique

err := client.AddForeignKey(ctx, "orders", "fk_orders_user",
    []string{"user_id"}, "users", []string{"id"}, "CASCADE", "")
// columns/refColumns match by position; onDelete/onUpdate as the engine takes them

An action the engine does not have is refused before anything is sent: Oracle has ON DELETE CASCADE and ON DELETE SET NULL and no ON UPDATE clause, and SQL Server has no RESTRICT. NO ACTION, the default everywhere, is left out on Oracle, which has no clause for it. The dialects reference has the table per engine.

AddForeignKey needs an engine that supports ALTER TABLE ADD CONSTRAINT. SQLite does not, so declare the foreign key at table-create time there instead — a Table in a desired Schema carries its ForeignKeys, and ApplyPlan emits them inline with the CREATE TABLE on every engine (see below). For partial or expression indexes, included columns, or engine-specific options, write a versioned migration with explicit SQL.

A model can declare a single-column index in its tags, and both Migrate and PlanMigration read the declaration:

type User struct {
    ID    int64  `db:"id" pk:"true"`
    Email string `db:"email" quark:"unique"`          // a UNIQUE column constraint
    Slug  string `db:"slug" quark:"index"`            // idx_users_slug
    Org   string `db:"org" quark:"index=ix_users_org"` // a name of your choosing
}

Migrate creates the index after the table; PlanMigration proposes it when it is missing and treats a live index of the same shape under another name — the one the engine gives a unique column — as already there.

Versioned Go migrations

Use the migrate package when changes must be ordered, reviewed, and reversible. Register each migration with an Up and a Down:

package migrations

func init() {
    migrate.Register(&migrate.Migration{
        ID:   "202605050001_add_users_email_index",
        Name: "add users email index",
        Up: func(ctx context.Context, client *quark.Client) error {
            return client.CreateIndex(ctx, "users", "idx_users_email", []string{"email"}, true)
        },
        Down: func(ctx context.Context, client *quark.Client) error {
            return client.Exec(ctx, `DROP INDEX idx_users_email`)
        },
    })
}

IDs sort lexicographically, so use timestamp-like prefixes to keep the order stable.

Drive the migrations from a command that imports your migration package for its side effects. The migrator runs raw SQL internally, so that command's client needs AllowRawQueries: true:

limits := quark.DefaultLimits()
limits.AllowRawQueries = true
client, _ := quark.New("postgres", dsn, quark.WithLimits(limits))

migrator := migrate.NewMigrator(client)
_ = migrator.UpDryRun(ctx, 0) // preview pending
err := migrator.Up(ctx, 0)    // apply all pending
// ...
err = migrator.Down(ctx, 1)   // revert the last one

Up and Down take the cluster-wide migration lock before touching the schema — pg_advisory_lock on PostgreSQL, GET_LOCK on MySQL and MariaDB, sp_getapplock on SQL Server, DBMS_LOCK on Oracle — so two replicas running migrate up at the same time apply each migration once. SQLite has no distributed lock and a single writer, so nothing is taken there. Options:

migrator := migrate.NewMigrator(client,
    migrate.WithLockTimeout(time.Minute),   // default 30s
    migrate.WithLockName("myapp:schema"),   // default "quark:schema"
    migrate.WithLogger(logger),             // default: the client's logger
)
migrator = migrate.NewMigrator(client, migrate.WithoutLock()) // single process, no lock

Progress goes through the logger, not stdout:

level=INFO msg="migrate: applying" id=202605050001_add_users_email_index name="add users email index"
level=INFO msg="migrate: applied" count=1

A migration can be written in its transactional form, UpTx / DownTx, which receive a *sql.Tx. On engines that roll DDL back (PostgreSQL, SQLite, SQL Server) the migration and its ledger row commit together, so a migration that fails halfway leaves neither behind. On MySQL, MariaDB and Oracle DDL commits itself: only the ledger row is atomic with the last statement, and the migrator says so at debug level.

migrate.Register(&migrate.Migration{
    ID: "202605050002_add_orders", Name: "add orders",
    UpTx: func(ctx context.Context, tx *sql.Tx) error {
        _, err := tx.ExecContext(ctx, `CREATE TABLE orders (id BIGSERIAL PRIMARY KEY)`)
        return err
    },
    DownTx: func(ctx context.Context, tx *sql.Tx) error {
        _, err := tx.ExecContext(ctx, `DROP TABLE orders`)
        return err
    },
})

Pass steps > 0 to limit how many to apply/revert; 0 means all. Applied IDs are tracked in a quark_migrations table.

Coordinating concurrent deploys

When several processes can migrate the same database, take a cluster-wide advisory lock first so they don't race. The first caller wins; the rest wait up to timeout and then get ErrLockTimeout:

lock, err := client.AcquireMigrationLock(ctx, "schema-migrations", 30*time.Second)
if err != nil {
    return err
}
defer lock.Release(ctx)

if err := client.Migrate(ctx, &User{}, &Order{}); err != nil {
    return err
}

The lock is opt-in: Migrate never takes one on its own. Each engine uses its native primitive — pg_advisory_lock, GET_LOCK, sp_getapplock, and Oracle's DBMS_LOCK, which needs GRANT EXECUTE ON DBMS_LOCK. SQLite is single-writer and returns ErrUnsupportedFeature; use BEGIN IMMEDIATE in-process there.

Diffing models against the database

PlanMigration builds the schema your models describe, introspects the live database, diffs the two, and returns an inert Plan — nothing runs until you apply it. That makes it the right tool for a CI drift gate or a controlled apply. Introspection and diffing work on all six engines, and a plan against an in-sync schema comes back empty:

plan, err := client.PlanMigration(ctx, &User{}, &Order{})
if err != nil {
    return err
}
fmt.Println(plan.IsEmpty(), len(plan.Hash()))
// => true 64   (no drift; Hash is a stable SHA-256 of the ops)

if !plan.IsEmpty() {
    fmt.Println(plan.String()) // human-readable pending operations
    if err := client.ApplyPlan(ctx, plan); err != nil {
        return err
    }
}

The desired schema includes the many-to-many join tables your models declare and the indexes they declare with quark:"index". Live indexes, foreign keys and check constraints the tags say nothing about are carried over — so a plan never proposes dropping an object your models simply don't describe. A table created by ApplyPlan is interchangeable with one from Migrate: same primary key, same auto-increment shape.

A Schema you build by hand, or read from a document, can carry more than the tags can: Indexes, ForeignKeys and Checks on each Table. ApplyPlan creates the whole table — foreign keys and checks inline with the CREATE TABLE, which is the one form SQLite accepts, and each index as its own CREATE INDEX — and Diff orders new tables so a referenced table exists before the table that references it. Re-diffing the desired schema against the result proposes nothing: the loop converges. A foreign key is matched by its columns and target, not by its name (SQLite keeps none), and an unspecified ON DELETE / ON UPDATE is the engine default, NO ACTION.

How ApplyPlan recovers from a mid-plan failure depends on the engine, as the dialect's SupportsTransactionalDDL() reports it:

• PostgreSQL, SQL Server, SQLite — the apply is transactional, so a failure rolls the whole plan back.
• MySQL, MariaDB, Oracle — DDL auto-commits, so the apply is resumable instead: re-running the same plan picks up from the first operation that hasn't been applied. Each applied operation is recorded in a checkpoint table, quark_migration_state, under the plan's Hash().

The checkpoint is only valid for the schema it was recorded against. Before skipping recorded operations, ApplyPlan reads the schema. It may find that a recorded operation is not there: the table it created is gone, the column it dropped is back, or the column it altered still has its old definition. Then ApplyPlan discards that plan's checkpoint and applies the plan from the first operation. So a plan applied again after its tables were dropped and recreated runs again; applied again over a schema that already holds it, it changes nothing. Only a schema that shows an operation missing counts. An operation the catalog cannot settle either way is taken as applied, so a plan that failed half-way and was fixed by hand still resumes where it stopped.

A dialect of your own takes the path its SupportsTransactionalDDL() answers, and the statements of each operation are its answers to the optional interfaces listed in the dialect API reference.

A few changes are refused on purpose, so that drift stays visible instead of being silently mis-applied:

• Renaming a column through OpAlterColumn returns ErrUnsupportedFeature: a rename is Sync's quark:"rename:old" tag or a versioned migration.
• On SQLite, a table whose CREATE TABLE carries a CHECK in a shape the rebuild cannot read back (anything but CONSTRAINT name CHECK (expr) on its own line) returns ErrUnsupportedFeature rather than rebuilding without it.

OpAlterColumn covers the four facets a column can change — type, nullable, default and primary key — on every engine. PostgreSQL alters each facet on its own; MySQL and MariaDB restate the column with one MODIFY; SQL Server alters the column and manages its named default and key constraints; Oracle emits one MODIFY with exactly what changed. SQLite has no ALTER COLUMN and no ADD or DROP CONSTRAINT at all, so there every column, foreign-key and check change goes through the table rebuild its manual documents — create the changed table under a temporary name, copy the rows, drop, rename, recreate the indexes and triggers — inside the plan's transaction, so a failure (a NOT NULL over rows that hold NULL, say) leaves the table as it was.

A CI drift gate with quarkmigrate

Go has no runtime model registry, so a schema-diff command has to import your models directly. The quarkmigrate package keeps that command down to one call:

os.Exit(quarkmigrate.Run(context.Background(), action, client,
    &models.User{}, &models.Order{}))

go run ./migrations plan      # informational, exit 0
go run ./migrations verify    # exit 1 if the schema has drifted (CI gate)
go run ./migrations apply     # apply the plan

Exit code	Meaning
0	plan/verify clean, or apply succeeded
1	verify found drift (CI gate signal)
2	operational error

Rolling a plan back

Plan.Down() returns the plan that undoes one: every operation inverted, in reverse order.

plan, _ := client.PlanMigration(ctx, &User{})

down, err := plan.Down()   // capture it BEFORE applying
if err != nil {
    return err
}
if err := client.ApplyPlan(ctx, plan); err != nil {
    return err
}

// …later, to roll back:
err = client.ApplyPlan(ctx, down)

Reverse order matters as much as the inversion: a plan that creates a table and then indexes it has to drop the index first, or the drop refers to something that is already gone.

Capture Down before you apply the plan. Nothing stops you calling it afterwards — a Plan is a value — but a plan built from a schema you have already changed describes a different starting point.

Some operations cannot be reversed from a plan

DROP TABLE records the table's name, not its columns, keys and indexes. DROP COLUMN records a name, not a type, nullability or default. There is nothing in the plan to rebuild them from, so Down returns ErrIrreversibleOperation naming the operation instead of emitting a partial rollback.

That refusal is the point: a rollback that silently restores a table without its indexes reports success and leaves the schema subtly different. When you need to reverse a drop, write the up/down pair as a versioned migration, where both directions are yours to state.

Backfilling data

When a migration adds a column you then have to populate, Backfill handles the primary-key pagination, the batching, and the resume token. You write only the per-batch work:

err := client.Backfill(ctx, quark.BackfillSpec{
    Name:      "fill_user_email_hash", // resume key
    Table:     "users",
    PKColumn:  "id",   // default "id"
    BatchSize: 1000,   // default 1000
    Process: func(ctx context.Context, batchPKs []int64) error {
        // run your UPDATE for this batch of PKs
        return nil
    },
})

Each successful batch records its highest primary key in quark_backfill_state, so a crash or a retry resumes exactly where it stopped: no earlier batch is reprocessed, and re-running a finished backfill does nothing. Integer primary keys only.

Register models once

Instead of passing your model list to every call, register them on the client:

client.RegisterModel(&User{}, &Order{}, &Invoice{})
client.MigrateRegistered(ctx)
plan, _ := client.PlanMigrationRegistered(ctx)

RegisterModel validates each model up front and is safe for concurrent use. The registry belongs to one client, so it never leaks across tenant clients.

Recommended production flow

1. Use Migrate freely in tests and local prototypes.
2. Use Sync for additive changes while the schema is young.
3. Use quark:"rename:old_col" for non-destructive renames.
4. Move production DDL into versioned migrations once the table holds real data.
5. Keep SafeMigrations on for application clients.
6. Use a separate migration client with AllowRawQueries: true.
7. Make destructive changes explicit and reversible, with a tested Down.
8. Wrap multi-process migrations in AcquireMigrationLock so deploys don't race.