Skip to content

Running migrations against a live database

Run kywi migrate from your own machine against the production database before you push the deploy, and take a pg_dump first.

Dump, migrate, then deploy — in that order, and the migrate runs from your machine against the live database, not from the release. Nothing in a deploy hook should touch the schema; see Why drizzle-kit push is banned before deploy for the deployment this rule was written from.

The connection string is not the one in your service variables

A managed Postgres publishes an internal host to the services beside it and a TCP proxy to everything else. The DATABASE_URL your web service reads is the internal one and resolves to nothing from a laptop, so the migrate needs the proxy host and port assembled from the same variable set:

bash
railway variables --service Postgres --json   # PGUSER, PGPASSWORD, PGDATABASE, RAILWAY_TCP_PROXY_DOMAIN, RAILWAY_TCP_PROXY_PORT

PROXY="postgresql://$PGUSER:$PGPASSWORD@$RAILWAY_TCP_PROXY_DOMAIN:$RAILWAY_TCP_PROXY_PORT/$PGDATABASE"
DATABASE_URL="$PROXY" pnpm exec kywi migrate

Run it from the release branch, with the new @kywi-software/* versions already installed, before the branch merges — so the containers that come up on the new code find a schema that already matches it.

What a good run prints

The schema is only the first half. After drizzle-kit exits zero, kywi migrate runs data steps that finish the upgrade, and these are the lines that say they happened:

text
sites: <n> created, <n> updated
  Created site row(s) for <ids> — each with a published home page so its "/" renders (#275).
Indexed <n> linked component instance(s) across <n> document(s) and <n> saved layout(s).
Rebuilt layout search text for <n> of <n> laid-out page(s) — copy composed in the layout editor is now findable (#231).

The last two print only when the count is non-zero, so a second run that says nothing is a healthy no-op rather than a skipped step. This deployment's 0.23.0 pass reported sites: 2 created, 0 updated and rebuilt layout search text for 12 of 24 pages. A run that prints the schema result and none of these did not do half its job — Post-migration data steps and why they can silently not run is that failure.

The refusal that stops a production run cold

Before spawning drizzle-kit at all, kywi migrate checks that every live kywi_ct_* table is covered by the schema just generated. If any is not:

text
✖ Refusing to run drizzle-kit: 2 admin-created content-type table(s) exist in the database but are missing from the generated schema:
    kywi_ct_doc_page
    kywi_ct_form
  Running the migration now would offer to DROP these tables and their rows (#38).
  Re-run 'kywi migrate' with a DATABASE_URL that can read kywi_content_types, or
  report this as a bug if the problem persists.

Re-run the same command; nothing has been applied. Changing the content model on a database that has data has the cause and the guard's full mechanism.

Read the generated migration before it goes anywhere else

The generated SQL is a diff against drizzle's snapshot, not against your database, and on a long-lived deployment the two can disagree. The 0.18.0 upgrade of this site generated an ADD COLUMN for kywi_audiences.is_public that both databases already had. Editing a not-yet-applied file is safe — the hash recorded in the journal is computed from the file's content when it is applied — so the fix was one word:

kywi-generated/migrations/0001_….sqlsql
ALTER TABLE "kywi_audiences" ADD COLUMN IF NOT EXISTS "is_public" boolean DEFAULT false NOT NULL;

So migrate a local copy first, read the file, then run the proxy command. Editing a migration that has already been applied somewhere is a different and much worse situation.

Prove the two databases agree

Local and production must end up holding the same journal, in the same order:

sql
SELECT hash, created_at FROM drizzle.__drizzle_migrations ORDER BY created_at;

Identical hash lists on both means the same files were applied to both. Then commit kywi-generated/migrations/ with its snapshot — that directory is the project's schema history, and a snapshot only you have is a migration everyone else will generate again.