Schema changes in production

You’ll apply common schema changes to a database that is serving traffic, where old and new versions of your app run side by side for a while during a deploy.

Prerequisites

• Migrations run as a deploy step before the new app version starts (Run migrations).
• The .gisila/ snapshot and all migrations committed.

The rule: expand, then contract

During a rolling deploy, the old code runs against the new schema. So every migration must keep the previous version of the app working:

1. Expand: add the new thing (column, table, index) in a way old code ignores.
2. Deploy code that writes both, or the new one.
3. Backfill existing rows.
4. Contract: in a later release, remove what’s no longer used.

generate writes the SQL for each step from your YAML; your job is to split the change across releases and add backfills.

Steps

1. Add a column

Safe when the column is nullable or has a constant default:

    slug: { type: varchar, max_length: 220 }          # nullable: safe
    view_count: { type: integer, is_null: false, default: 0 }   # constant default: safe

Adding is_null: false without a default fails on a non-empty table, because existing rows have no value. Use the next step instead.

2. Make a column required

Release 1, expand. Add it nullable and start writing it from the new code:

    slug: { type: varchar, max_length: 220 }

Then add a backfill to the generated migration, or as a separate hand-written one:

UPDATE "posts" SET "slug" = lower(regexp_replace("title", '[^a-zA-Z0-9]+', '-', 'g'))
WHERE "slug" IS NULL;

Release 2, contract. Once every row has a value and all running code writes it:

    slug: { type: varchar, max_length: 220, is_null: false, is_unique: true }

generate writes ALTER COLUMN "slug" SET NOT NULL. It doesn’t add a UNIQUE constraint to an existing column (see the note below), so add it to that migration yourself:

ALTER TABLE "posts" ADD CONSTRAINT "posts_slug_key" UNIQUE ("slug");

What the differ doesn’t migrate on existing columns

Turning is_unique or is_index on or off for a column that already exists produces no constraint or index change in the generated migration (only vector index settings are handled). New columns and new tables get them. For existing columns, add the ALTER TABLE ... ADD CONSTRAINT ... UNIQUE or CREATE INDEX yourself, or declare the index in the model’s indexes: block, which the differ does track.

3. Rename a column

A direct rename breaks the old code still running during the deploy. And when you remove a column and add one of the same type in one edit, generate guesses it was a rename and writes RENAME COLUMN with a -- GUESSED RENAME comment. That’s right only if it’s what you meant, and it still breaks old code.

The safe path takes three releases:

1. Add the new column (nullable); write both columns from the code; backfill with UPDATE ... SET new = old.
2. Switch reads to the new column; stop writing the old one.
3. Remove the old column from YAML and run generate --allow-destructive.

To rename a table, keep the model name and change db_table; the differ writes ALTER TABLE ... RENAME TO. Renaming the model itself is a drop plus a create, which loses data.

4. Change a column type

Widenings (integer → bigint, a longer max_length, varchar → text) are not flagged as destructive, but ALTER COLUMN ... TYPE can still rewrite and lock a large table. Anything else is refused until --allow-destructive, and the cast may fail on existing values.

For large tables, use expand and contract with a new column instead, and backfill in batches:

-- gisila:no-transaction
UPDATE "posts" SET "view_count_big" = "view_count" WHERE "id" BETWEEN 1 AND 100000;
UPDATE "posts" SET "view_count_big" = "view_count" WHERE "id" BETWEEN 100001 AND 200000;

With -- gisila:no-transaction each statement commits on its own, so locks are short. Make such migrations safe to re-run.

5. Add an index without blocking writes

Declare the index in the model’s indexes: block; the differ generates CREATE INDEX for new entries there (it ignores is_index changes on existing columns). A plain CREATE INDEX blocks writes to the table while it builds, so on a large table edit the generated migration before it’s applied anywhere:

-- gisila:no-transaction
CREATE INDEX CONCURRENTLY IF NOT EXISTS "idx_posts_author_published"
  ON "posts" ("author_id", "published_at");

Keep the same index name as in the YAML, so the snapshot and the database agree.

6. Drop a column or table

1. Deploy code that no longer reads or writes it.
2. Remove it from YAML and run generate. It refuses and lists what would be lost.
3. Back up the data if you might need it, then run generate --allow-destructive.
4. Review the -- DESTRUCTIVE steps and deploy.

7. Add enum values

Adding a value to a schema enum generates ALTER TYPE ... ADD VALUE IF NOT EXISTS. Deploy the migration before any code that writes the new value. Removing or renaming enum values isn’t generated; write that migration by hand.

8. Check before you deploy

dart run gisila_orm:generate      # must produce no new files in CI
dart run gisila_orm:migrate status

• Read every pending migration for -- DESTRUCTIVE and -- GUESSED RENAME.
• Never edit a migration after it ran anywhere; migrate up refuses changed files. Put fixes in a new migration.
• Rehearse on a copy of production data when a migration touches large tables.

Verify it works

After deploying, migrate status shows every migration applied, and both the previous and the new app version pass their health checks during the rollout.

Next steps

• Migrations concept
• Migrations API
• CLI commands