decide: whether generated Drizzle migrations replace node-pg-migrate #219

New Issue

2026-08-28T15:17:42-05:00

bermudalamb commented

2026-08-28 15:17:42 -05:00

Third step of the Drizzle adoption from #216, and deliberately a separate decision. Adopting Drizzle for queries does not force adopting it for migrations, and the spike surfaced reasons to take them apart.

What the spike established

drizzle-kit generate works and diffs correctly. But the first generated migration after drizzle-kit pull also emitted drops and recreations of the three expression indexes (tags_name_uniq, categories_child_name_uniq, categories_root_name_uniq) alongside the one column actually added. Re-running with no schema change reports "nothing to migrate", so it settles rather than recurring — but that first migration needs hand-editing before it goes near production, and on a large table those recreations take real locks.

The argument against, which is not technical

Our migrations are mostly prose. 1787400000000_split-customer-name.js is about twenty lines explaining that splitting on the first space is right for "Thom Lamb" and wrong for "Mary Jane Smith", why both columns are nullable although registration requires them, and why leaving them empty was rejected. drizzle-kit generate emits bare SQL.

So either we commit to hand-editing every generated migration — keeping the writing and automating only the SQL, which is fine but is not the "smoother" that motivated this — or the repo quietly loses the habit that makes its history worth reading.

And data migrations cannot be generated at all. The name-splitting backfill is not a schema diff. Anything touching existing rows stays hand-written whatever the tooling, so the automation covers a smaller share of our migrations than the headline suggests.

Options

Keep node-pg-migrate, use Drizzle for queries only. Schema stays hand-written and commented; schema.ts is refreshed by drizzle-kit pull after each migration and is a read-only mirror. Lowest risk, keeps the prose, gives up the generation.
Move to drizzle-kit generate, with a rule that every generated migration is hand-annotated before merge. Gets the automation, keeps the reasoning, costs discipline nobody can enforce automatically.
Move fully and accept bare migrations. Cheapest per change, and the thing most likely to be regretted in a year.

Leaning towards 1 until #218 reports, because nothing about converting queries depends on this and option 1 is the only one that cannot make the history worse.

Depends on

#217 and #218 landing first. This should not be decided before there is real experience of keeping schema.ts in step with hand-written migrations.

Refs #216

Third step of the Drizzle adoption from #216, and deliberately a separate decision. Adopting Drizzle for **queries** does not force adopting it for **migrations**, and the spike surfaced reasons to take them apart. ## What the spike established `drizzle-kit generate` works and diffs correctly. But the first generated migration after `drizzle-kit pull` also emitted drops and recreations of the three expression indexes (`tags_name_uniq`, `categories_child_name_uniq`, `categories_root_name_uniq`) alongside the one column actually added. Re-running with no schema change reports "nothing to migrate", so it settles rather than recurring — but that first migration needs hand-editing before it goes near production, and on a large table those recreations take real locks. ## The argument against, which is not technical Our migrations are mostly prose. `1787400000000_split-customer-name.js` is about twenty lines explaining that splitting on the first space is right for "Thom Lamb" and wrong for "Mary Jane Smith", why both columns are nullable although registration requires them, and why leaving them empty was rejected. `drizzle-kit generate` emits bare SQL. So either we commit to hand-editing every generated migration — keeping the writing and automating only the SQL, which is fine but is not the "smoother" that motivated this — or the repo quietly loses the habit that makes its history worth reading. And data migrations cannot be generated at all. The name-splitting backfill is not a schema diff. Anything touching existing rows stays hand-written whatever the tooling, so the automation covers a smaller share of our migrations than the headline suggests. ## Options 1. **Keep `node-pg-migrate`, use Drizzle for queries only.** Schema stays hand-written and commented; `schema.ts` is refreshed by `drizzle-kit pull` after each migration and is a read-only mirror. Lowest risk, keeps the prose, gives up the generation. 2. **Move to `drizzle-kit generate`, with a rule that every generated migration is hand-annotated before merge.** Gets the automation, keeps the reasoning, costs discipline nobody can enforce automatically. 3. **Move fully and accept bare migrations.** Cheapest per change, and the thing most likely to be regretted in a year. Leaning towards 1 until #218 reports, because nothing about converting queries depends on this and option 1 is the only one that cannot make the history worse. ## Depends on #217 and #218 landing first. This should not be decided before there is real experience of keeping `schema.ts` in step with hand-written migrations. Refs #216

bermudalamb referenced this issue

2026-08-28 15:17:48 -05:00

spike: evaluate Drizzle for type-safe queries and generated migrations #216

bermudalamb commented

2026-09-02 09:04:36 -05:00

#217 and #218 have landed, so this can be decided on experience rather than on the spike's guess.

Decision: option 1 — keep `node-pg-migrate`, use Drizzle for queries only

schema.ts stays a generated, read-only mirror, refreshed by drizzle-kit pull after each hand-written migration. backend/migrations remains the schema's owner.

What changed my confidence, both from actually doing it

The mirror drifts silently, and now something catches it. #217 found schema.ts had been missing item_drafts and upload_links since #222 landed — not because anyone re-pulled, but because migrations moved on and the hand-copied mirror did not. Nothing noticed for a week. That is the strongest argument against option 1 and it is now answered: drizzleSchema.integration.test.ts fails when the mirror and the database disagree on tables or columns, and out points at the source directory so there is no copy step to forget. Verified by removing a table from the mirror and confirming the test fails naming it.

Without that guard I would not have recommended option 1, because a stale mirror is worse than no mirror — Drizzle infers row types from it, so a converted query type-checks against a schema the database does not have.

The index churn is real, and I have the artefact. The spike's generated migration was still on main in backend/drizzle/0001_add_condition_note.sql — which is separately notable, since #216's closing comment said that experimental column must never reach main and it did. Adding one nullable column emitted:

DROP INDEX "tags_name_uniq";
DROP INDEX "categories_child_name_uniq";
DROP INDEX "categories_root_name_uniq";
ALTER TABLE "items" ADD COLUMN "condition_note" text;
CREATE UNIQUE INDEX "tags_name_uniq" ON "tags" USING btree (lower(name));
CREATE UNIQUE INDEX "categories_child_name_uniq" ON "categories" USING btree (parent_id,lower(name)) WHERE (parent_id IS NOT NULL);
...

Six statements for one column, because drizzle-kit cannot diff expression indexes and recreates them instead. Nothing ran it — node-pg-migrate only executes backend/migrations — so it was inert. But it is exactly what option 3 ships: a generated migration nobody reads, taking three index rebuilds and their locks on the way past. On items at production size that is not free.

And #218 showed the type system does not catch what matters. Two defects were introduced converting one file — a correlated subquery that silently returned the wrong number, and a SQLSTATE check that silently stopped matching. Neither produced a type error. If the tooling cannot be trusted to make queries obviously correct, extending it to unreviewed schema changes is the wrong direction of travel.

Why not the other two

Option 2 — generate, then hand-annotate every migration — asks for discipline nothing can enforce. The value of our migrations is the prose: 1787400000000_split-customer-name.js is twenty lines explaining that splitting on the first space is right for "Thom Lamb" and wrong for "Mary Jane Smith". A rule that every generated migration must be annotated before merge is a rule that will hold for three migrations and then quietly stop, and the failure is invisible — the migration still works, it just stops explaining itself.

Option 3 is the same thing without pretending otherwise.

And the automation covers less than it appears: data migrations cannot be generated at all. The name-splitting backfill is not a schema diff, so anything touching existing rows stays hand-written regardless.

What this means in practice

Write migrations by hand in backend/migrations, as now, with the reasoning in them.
Run drizzle-kit pull afterwards to refresh the mirror. drizzleSchema.integration.test.ts fails if you forget.
0000_*.sql and meta/ from a pull are gitignored — this project has one migration history and it is not that one.
Revisit if drizzle-kit learns to diff expression indexes, or if the mirror-refresh step proves to be a nuisance in practice rather than in theory.

Recorded in backend/src/db-drizzle/CONVENTIONS.md. Nothing about converting the remaining ~178 query sites depends on this.

Closes #219

#217 and #218 have landed, so this can be decided on experience rather than on the spike's guess. ## Decision: option 1 — keep `node-pg-migrate`, use Drizzle for queries only `schema.ts` stays a generated, read-only mirror, refreshed by `drizzle-kit pull` after each hand-written migration. `backend/migrations` remains the schema's owner. ## What changed my confidence, both from actually doing it **The mirror drifts silently, and now something catches it.** #217 found `schema.ts` had been missing `item_drafts` and `upload_links` since #222 landed — not because anyone re-pulled, but because migrations moved on and the hand-copied mirror did not. Nothing noticed for a week. That is the strongest argument *against* option 1 and it is now answered: `drizzleSchema.integration.test.ts` fails when the mirror and the database disagree on tables or columns, and `out` points at the source directory so there is no copy step to forget. Verified by removing a table from the mirror and confirming the test fails naming it. Without that guard I would not have recommended option 1, because a stale mirror is worse than no mirror — Drizzle infers row types from it, so a converted query type-checks against a schema the database does not have. **The index churn is real, and I have the artefact.** The spike's generated migration was still on `main` in `backend/drizzle/0001_add_condition_note.sql` — which is separately notable, since #216's closing comment said that experimental column must never reach `main` and it did. Adding one nullable column emitted: ```sql DROP INDEX "tags_name_uniq"; DROP INDEX "categories_child_name_uniq"; DROP INDEX "categories_root_name_uniq"; ALTER TABLE "items" ADD COLUMN "condition_note" text; CREATE UNIQUE INDEX "tags_name_uniq" ON "tags" USING btree (lower(name)); CREATE UNIQUE INDEX "categories_child_name_uniq" ON "categories" USING btree (parent_id,lower(name)) WHERE (parent_id IS NOT NULL); ... ``` Six statements for one column, because drizzle-kit cannot diff expression indexes and recreates them instead. Nothing ran it — `node-pg-migrate` only executes `backend/migrations` — so it was inert. But it is exactly what option 3 ships: a generated migration nobody reads, taking three index rebuilds and their locks on the way past. On `items` at production size that is not free. **And #218 showed the type system does not catch what matters.** Two defects were introduced converting one file — a correlated subquery that silently returned the wrong number, and a SQLSTATE check that silently stopped matching. Neither produced a type error. If the tooling cannot be trusted to make *queries* obviously correct, extending it to unreviewed schema changes is the wrong direction of travel. ## Why not the other two **Option 2** — generate, then hand-annotate every migration — asks for discipline nothing can enforce. The value of our migrations is the prose: `1787400000000_split-customer-name.js` is twenty lines explaining that splitting on the first space is right for "Thom Lamb" and wrong for "Mary Jane Smith". A rule that every generated migration must be annotated before merge is a rule that will hold for three migrations and then quietly stop, and the failure is invisible — the migration still works, it just stops explaining itself. **Option 3** is the same thing without pretending otherwise. And the automation covers less than it appears: data migrations cannot be generated at all. The name-splitting backfill is not a schema diff, so anything touching existing rows stays hand-written regardless. ## What this means in practice - Write migrations by hand in `backend/migrations`, as now, with the reasoning in them. - Run `drizzle-kit pull` afterwards to refresh the mirror. `drizzleSchema.integration.test.ts` fails if you forget. - `0000_*.sql` and `meta/` from a pull are gitignored — this project has one migration history and it is not that one. - Revisit if drizzle-kit learns to diff expression indexes, or if the mirror-refresh step proves to be a nuisance in practice rather than in theory. Recorded in `backend/src/db-drizzle/CONVENTIONS.md`. Nothing about converting the remaining ~178 query sites depends on this. Closes #219

bermudalamb closed this issue

2026-09-02 09:04:58 -05:00

bermudalamb referenced this issue from a commit

2026-09-02 09:06:47 -05:00

feat(db): land the Drizzle schema, config and conventions (#217)

bermudalamb referenced this issue from a commit

2026-09-02 09:06:47 -05:00

chore(db): remove the spike's drizzle output directory (#217)

bermudalamb referenced this issue from a commit

2026-09-02 09:06:47 -05:00

docs(db): record the migrations decision (#219)

bermudalamb referenced this issue from a commit