# Applying a hand-written schema patch outside kywi migrate

URL: https://kywi-docs-production.up.railway.app/operating/applying-a-hand-written-schema-patch-outside-kywi-migrate
Type: docPage

Hand-applied SQL is sometimes the only correct path; the cost is that the drizzle journal never records it and every database must be patched the same way.

There is one situation that requires this, and it is narrower than it looks: a project with **no migration journal yet**, on an additive upgrade. All `kywi migrate` can generate there is a from-nothing baseline — a file of `CREATE TABLE IF NOT EXISTS` that applies cleanly against your populated database and adds no columns to tables that already exist. It would report success and change nothing. If `kywi-generated/migrations/meta/_journal.json` exists, you are not in this situation and [the ordinary path](/operating/running-migrations-against-a-live-database/) applies.

## The patch has to be replayable

You are writing DDL that will be applied to at least two databases, possibly on different days, possibly twice. Every statement must survive a re-run — usually `IF NOT EXISTS`, sometimes just a statement whose second application is a no-op, and for constraints the wrapper drizzle-kit itself uses:

BEGIN; ALTER TABLE kywi_component_instances ADD COLUMN IF NOT EXISTS holder_kind varchar(20) NOT NULL DEFAULT 'content'; ALTER TABLE kywi_component_instances ADD COLUMN IF NOT EXISTS blueprint_id uuid; ALTER TABLE kywi_component_instances ALTER COLUMN content_id DROP NOT NULL; DO $$ BEGIN ALTER TABLE kywi_component_instances ADD CONSTRAINT kywi_component_instances_one_holder CHECK ( (holder_kind = 'content' AND content_id IS NOT NULL AND blueprint_id IS NULL) OR (holder_kind = 'blueprint' AND blueprint_id IS NOT NULL AND content_id IS NULL) ); EXCEPTION WHEN duplicate_object THEN NULL; END $$; COMMIT;

## kywi-schema-upgrade-0.15.0.sql

The `DO $$` block is not decoration. drizzle-kit wraps its `ADD CONSTRAINT … FOREIGN KEY` statements that way but emits `ADD CONSTRAINT … CHECK` bare, so a replayed CHECK fails with `constraint … already exists`. Either wrap it as above or drop it first with `DROP CONSTRAINT IF EXISTS`. Apply the whole file as one transaction, so a statement that fails leaves nothing behind:

psql "$PROXY_URL" -v ON_ERROR_STOP=1 -f kywi-schema-upgrade-0.15.0.sql

Most patches are much smaller than that file — 0.14.0's was a single `ALTER TABLE kywi_users ADD COLUMN IF NOT EXISTS password_reset_required …`, run against local and production in the same sitting. Both databases, always, in the same sitting: that is the part that goes wrong.

## Verify by diffing, not by reading

Build a scratch database from the target version's schema with `drizzle-kit push`, then compare `pg_dump --schema-only` against your patched one. Names matter as much as shapes: Postgres truncates identifiers at 63 characters, and the 0.15.0 pass found its foreign key is called `kywi_component_instances_blueprint_id_kywi_section_templates_id` only by diffing. A constraint you named yourself is a difference the next `drizzle-kit generate` will try to reconcile.

## The journal never learns about this

A hand-applied patch is invisible to `meta/_journal.json`, so the next `drizzle-kit generate` diffs against a snapshot that never saw it — the same desync `--push` causes. It can then generate a migration for a change your database already has, or skip one it thinks is already there. Nothing warns you at the time.

## Getting back onto the journal

The way back in is to let the tooling generate its migration, then make that migration's statements idempotent before applying it — exactly what this deployment did at 0.18.0. Run `kywi migrate` against a local copy, open the generated `000N_*.sql`, and rewrite the statements your databases have already had by hand into `ADD COLUMN IF NOT EXISTS` form. Editing that not-yet-applied file is safe; [Running migrations against a live database](/operating/running-migrations-against-a-live-database/) has why. From then on the project is journal-managed and this page stops applying.

## The data steps do not run

`psql` applies DDL and nothing else, so every post-migration data step `kywi migrate` would have run is skipped — the dedupes, the config-site reconcile, the component-instance reindex, the layout search-text rebuild. The 0.15.0 pass ran the three that existed then through a one-off script in the site repo. Check [Post-migration data steps and why they can silently not run](/operating/post-migration-data-steps-and-why-they-can-silently-not-run/) for the current list, and run them deliberately; every one is idempotent, so the cost of running them is nothing and the cost of forgetting is a site whose search returns nothing.
