Migrations

Run migrations outside the request path. Two common choices:

a console command (dygo g command migrate, using the console package) that runs golang-migrate or goose, started as a separate step in your deploy;
an OnApplicationBootstrap hook on a Migrator provider, for small services with one instance.

Database

dygo db sets up MySQL or Postgres and runs migrations. Migrations are the source of truth for the schema: there is no schema sync from structs.

dygo new shop -db postgres           # a new project with a database (-db mysql too)
dygo db init                         # or add one to an existing project (asks for the settings)
docker compose up -d                 # dygo new -db and dygo db init -compose write compose.yaml
dygo db create                       # CREATE DATABASE shop
dygo g migration                     # migrations/0001_update_schema.up.sql and .down.sql,
                                     # with the SQL for every entity that changed
dygo db migrate                      # apply what is pending
dygo db status                       # applied and pending migrations

Command	What it does
`dygo new shop -db postgres\|mysql`	`dygo new` plus everything `dygo db init -compose` does
`dygo db init [-driver postgres\|mysql] [-url URL \| -host -port -user -password -name] [-compose] [-force]`	writes `DATABASE_URL` to `.env` and `.env.example` (with the password `change-me`), imports `postgres.Module()` or `mysql.Module()` (in a modular project it replaces the `src/database` placeholder), adds `migrations/embed.go`, and wires the `db:*` commands into `main`. It asks in a terminal for what the flags leave out, and refuses to replace a different `DATABASE_URL` without `-force`.
`dygo g migration [name]` (`mig`)	the next numbered `.up.sql` and `.down.sql` pair, filled for every entity whose table differs (see below); without a name it is called `update_schema`, and nothing is written when nothing changed
`dygo db create [--if-not-exists]`	creates the database named in `DATABASE_URL`
`dygo db drop --force [--if-exists]`	drops it
`dygo db migrate [--to N] [--dry-run] [--out-of-order]`	applies pending migrations; `--dry-run` prints their SQL
`dygo db rollback [--steps N \| --to N]`	runs the down files, newest first
`dygo db status [--check]`	lists migrations; `--check` exits 1 when some are pending (CI, deploy scripts)
`dygo db schema create`	applies every migration to an empty database
`dygo db schema drop --force [--all]`	rolls back every migration; `--all` drops every table instead

drop, rollback and schema drop refuse to run when APP_ENV=production unless --allow-production is given.

Migrations from an entity

dygo g migration compares every entity with its table and fills both files with the difference, whatever the migration is called (the name is only a label). Without a name it is called update_schema, and when every table already matches it writes nothing and says “No changes”. For each entity:

If no earlier migration creates the table, CREATE TABLE with a column for each field and DROP TABLE in the down file.
Otherwise ALTER TABLE ... ADD COLUMN for each new field (with a default, so existing rows get a value) and DROP COLUMN for each column that is no longer a field (with a comment, since it deletes data); the down file reverses them.

Column names follow sql.Repository: the db tag or the snake_case field name, with id as the primary key unless a field has db:",pk". The table is the one given to sql.ForFeature[Product]("..."), or the plural snake_case entity name. Types come from the Go types (string is TEXT on Postgres and VARCHAR(255) on MySQL, float64 is DOUBLE PRECISION or DOUBLE, time.Time is TIMESTAMPTZ or DATETIME(6), pointers and sql.Null* are nullable), for the database in DATABASE_URL.

Renamed fields. A renamed field looks like a dropped column and a new one. In a terminal, dygo g migration asks for each dropped column that has a new column of the same kind (“Did you rename products.note to description (TEXT)? [y/N]”); a yes writes RENAME COLUMN, which keeps the data. Outside a terminal, or to skip the question, name them: dygo g migration rename_products_note -rename note:description (comma separated for several; field names such as PublishedAt work too). A drop that looks like a rename gets a comment with the -rename to use.

Changed types. A column changes when its field moves to another kind of type (text, integer, float, bool, time, bytes, json): ALTER COLUMN ... TYPE ... USING col::type on Postgres, MODIFY COLUMN on MySQL. Changes inside a kind (INT to BIGINT, TEXT to VARCHAR(255)) are left alone, so hand-written types stay. A field that becomes a pointer or sql.Null* drops NOT NULL; one that stops being nullable gets NOT NULL, after an UPDATE that fills the NULLs with the zero value. The down file reverses each change.

Data loss warnings. Every type change says what it does to the rows already in the table, as a comment above the statement and as a WARNING: line in the output:

-- price changes from DOUBLE PRECISION to BIGINT.
-- DATA LOSS: decimals are rounded (2.7 becomes 3); the down migration cannot bring them back. Back up the table first.
-- MAY FAIL on numbers too big for BIGINT. Postgres then rolls the migration back.
ALTER TABLE products ALTER COLUMN price TYPE BIGINT USING price::bigint;

DATA LOSS marks values that change for good: decimals rounded (float to integer), large integers rounded (integer to REAL or DOUBLE), numbers merged into true (integer to BOOLEAN on Postgres), text cut to a VARCHAR(n) length (Postgres), bytes turned into hex text (BYTEA to TEXT). MAY FAIL marks values that do not convert, such as text that is not a number or a date. Postgres rolls a failed migration back; MySQL cannot roll back a schema change, so check the data first there. The down file gets the same comment for the reverse change. A change where every value converts (integer to text, BOOLEAN to integer) says so.

Safe mode for rolling deploys (-safe). A plain RENAME COLUMN or DROP COLUMN breaks the instances still running the old code during a deploy. -safe splits the change into expand and contract migrations:

dygo g migration rename_note_in_products -safe -rename note:description
dygo db migrate             # 1. expand: adds description, copies note into it, syncs both
                            # 2. deploy the new code everywhere
dygo db migrate --contract  # 3. contract: stops the sync, drops note

The expand migration adds the new column, copies the data, and installs a trigger that copies every write between the old and the new column (a PL/pgSQL function on Postgres, two one-statement BEFORE triggers on MySQL). Old code writing note and new code writing description then see the same data. New fields and NULL changes go here too.
The contract migration is the next version. It starts with -- dygo:after-deploy, so dygo db migrate (and migrate.OnBoot) stop before it and print what is waiting, and status shows it as “pending, after deploy”. status --check does not fail on it. Run it with dygo db migrate --contract (./app db:migrate --contract in production) once no old code runs. It drops the trigger, the old column and the columns that are no longer fields. dygo db schema create applies it directly, since an empty database has no old code.
A type change under the same column name is refused, since old and new code would read one column as two types. Give the field a new column (db:"price_cents") and run with -rename price:price_cents: the trigger casts between the two types.
Both down files work: rolling back the contract brings the old column and the trigger back, and rolling back the expand removes them.

On MySQL with binary logging, creating triggers needs the TRIGGER privilege and, for users without SUPER, log_bin_trust_function_creators=1.

This compares the entity with what the earlier migration files build (their CREATE, ALTER, RENAME and DROP TABLE statements, including ALTER COLUMN and MODIFY), not with a live database, so it needs no connection and works the same in CI. It is a starting point to review. dygo g entity prints the command to run after it adds an entity or fields, once the project has migrations.

How it runs. The CLI has no database driver (it uses only the standard library), so the app runs the commands itself. dygo db init adds this to main, before the app is created:

if code, ok := migrate.Main(ctx, postgres.Dialect(), migrations.FS, os.Args[1:]); ok {
  os.Exit(code)
}

dygo db migrate builds the main package and runs it as app db:migrate in the project root, so the app’s driver, migrations and .env are used. In production run the binary itself, for example as a deploy step: ./shop db:migrate. migrate.Main reads DATABASE_URL on its own and opens one connection, without creating the app, so it works before the database exists and does not need the app’s other services.

Migration files. NNNN_name.up.sql and NNNN_name.down.sql in migrations/, embedded in the binary by migrations/embed.go. A dygo_migrations table records what ran, and a database lock (pg_advisory_lock, MySQL GET_LOCK) makes a second instance wait. Each file runs in a transaction with its dygo_migrations row; Postgres rolls back a failed migration completely, while MySQL commits each DDL statement on its own, so keep MySQL migrations to one change each. Start a file with -- dygo:no-transaction for statements that refuse a transaction, such as CREATE INDEX CONCURRENTLY. A pending migration older than the newest applied one (two branches each added one) stops migrate until you renumber it or pass --out-of-order. For a small single instance service, migrate.OnBoot(migrations.FS) applies migrations while the app boots instead.

Not built yet: dygo db seed, db reset, db validate, db sql, db shell, dygo g crud --db (waits for the sqltest helpers), and a schema diff (db diff, db schema update), which is planned for a later release.