Skip to content
Gisila

Migrations

What it is

Section titled “What it is”

A migration is a pair of SQL files: <id>.up.sql moves the database forward, <id>.down.sql moves it back. gisila_orm splits the job in two:

  • Writing them: dart run gisila_orm:generate diffs your schema against the last snapshot and writes the SQL. You can also write files by hand.
  • Running them: dart run gisila_orm:migrate up (or MigrationManager) applies files that haven’t run yet and records them in the gisila_migrations table.

Migrations are plain SQL, so you can read, review and edit them before they run.

When to use it

Section titled “When to use it”
  • Every schema change to a database that holds data you care about.
  • Building a fresh database (tests, new environments) by applying all migrations from the start.

When not to use it

Section titled “When not to use it”
  • Throwaway test databases can run the full-schema *.gisila.up.sql once instead, if you prefer; see Testing.
  • Data fixes and backfills that depend on application logic are often easier as a Dart script run once; keep pure SQL backfills in migrations.
  • Databases you don’t own.

How it works

Section titled “How it works”

Lifecycle

Section titled “Lifecycle”
edit YAML ─▶ generate ─▶ <millis>_auto_project_changes.up.sql ─▶ review ─▶ commit
                                                                         │
                         deploy: migrate up ◀────────────────────────────┘

Ids and order

Section titled “Ids and order”

A migration’s id is its file name without .up.sql. Migrations run in id order (string sort), so the generator prefixes names with a millisecond timestamp. Hand-written files should use the same style, for example 1730500000000_backfill_slugs.up.sql, so they interleave correctly.

Batches

Section titled “Batches”

Each migrate up applies all pending migrations as one batch with the next batch number. migrate down --steps N rolls back the last N batches, newest migration first. Rolling back a migration needs its .down.sql.

Safety rails

Section titled “Safety rails”
Rail What it prevents
One transaction per migration A failing migration leaves nothing half-applied, and earlier migrations in the batch stay committed.
Advisory lock during up / down Two deploys migrating at once run one after the other instead of racing.
Checksums up refuses to run if an applied migration file was edited. Changes go in a new migration, so every environment runs the same SQL.
Destructive-change refusal in generate Dropped tables, columns and enums, and narrowing type changes, aren’t written until you pass --allow-destructive.
Guessed-rename markers A drop plus an add of a compatible column becomes RENAME COLUMN, marked -- GUESSED RENAME for you to confirm.

Opting out of the transaction

Section titled “Opting out of the transaction”

Some statements can’t run in a transaction, notably CREATE INDEX CONCURRENTLY. A file containing the line -- gisila:no-transaction runs statement by statement. If one fails, earlier statements stay applied, so make them re-runnable with IF NOT EXISTS.

What the differ can’t know

Section titled “What the differ can’t know”

The differ sees only YAML. It can’t tell a renamed model from a dropped one and a new one (it treats it as drop plus create), it doesn’t backfill data, and it doesn’t know how long an ALTER will lock a big table. For those, edit the generated migration or write your own. See Schema changes in production.

Example

Section titled “Example”
lib/models/
├── blog.gisila.yaml
├── blog.gisila.g.dart
├── blog.gisila.up.sql            # full schema; skipped by migrate
├── blog.gisila.down.sql
└── migrations/
    ├── 1730000000000_initial_schema.up.sql
    ├── 1730000000000_initial_schema.down.sql
    ├── 1730460000000_auto_project_changes.up.sql
    └── 1730460000000_auto_project_changes.down.sql
Terminal window
$ dart run gisila_orm:migrate status
Discovered: 2, applied: 1
  [x] 1730000000000_initial_schema
  [ ] 1730460000000_auto_project_changes
Section titled “Related”