refactor(db): swap the query builder from Drizzle to Kysely #305

New Issue

2026-09-04T14:52:33-05:00

bermudalamb commented

2026-09-04 14:52:33 -05:00

Carries out the decision recorded in #297. Nothing here reopens #219 — migrations stay hand-written in node-pg-migrate, which is what they already are.

Why

#216 was right that a type-safe builder should replace hand-assembled SQL, and #297 confirms the safety property is real rather than relocated: a value interpolated into a sql template becomes a bind parameter in either library, so #202's invariant becomes a type-system property and #180's S2077 hotspots retire either way.

What #216 could not know is that src/db-drizzle/CONVENTIONS.md would end up being mostly a list of ways to be quietly wrong. Three of its four hazards are properties of Drizzle rather than of type-safe query building, and #297 verified each against emitted SQL:

An array interpolates as a placeholder list, producing invalid Postgres, unless every site remembers sql.param(). Kysely emits one parameter.
A column reference inside a sql template is rendered without its table, so a correlated subquery silently correlates with itself — valid SQL, wrong data, and the reason #218 got a count of 1 where 2 was correct. Kysely emits the text as written.
The generated mirror is camelCase while these APIs answer snake_case, so every converted select needs an explicit column map or it silently changes the JSON contract. Kysely's generated types carry the database's own names.

The cost of doing this is bounded by how little is built: one converted file, three calls, against 238 raw query sites. The cost of not doing it is paid 237 more times, in a codebase where the failure mode is a review that passes.

Scope

One change, so main is never half-converted between two builders:

Add kysely and kysely-codegen; remove drizzle-orm, drizzle-kit, and drizzle.config.ts.
Replace src/db-drizzle/ with the Kysely equivalent — generated types, conventions, and the buildItemFilterSql worked example from #297's spike.
Repoint drizzleSchema.integration.test.ts at the new mirror. The drift it guards against is library-independent and already happened once, so the test survives the swap rather than being rewritten from scratch.
Reconvert src/routes/adminCategories.ts, keeping isUniqueViolation's tolerance of both error shapes until it is known which shape Kysely surfaces.
Both builders must not coexist at any commit, and Kysely takes the existing pg Pool rather than opening its own — a transaction on one pool is invisible to the other, and the configured limits would silently double.

Out of scope

Converting anything beyond adminCategories.ts. That stays file by file with raw pg alongside, and gets its own issue.

Migrations. #219 stands: node-pg-migrate, hand-written, prose intact.

Done when

drizzle appears nowhere in backend/, the drift test fails when the mirror and the database disagree, adminCategories.ts answers the same JSON it answers today, and the full backend suite is green.

Carries out the decision recorded in #297. Nothing here reopens #219 — migrations stay hand-written in `node-pg-migrate`, which is what they already are. ## Why #216 was right that a type-safe builder should replace hand-assembled SQL, and #297 confirms the safety property is real rather than relocated: a value interpolated into a `sql` template becomes a bind parameter in either library, so #202's invariant becomes a type-system property and #180's S2077 hotspots retire either way. What #216 could not know is that `src/db-drizzle/CONVENTIONS.md` would end up being mostly a list of ways to be quietly wrong. Three of its four hazards are properties of Drizzle rather than of type-safe query building, and #297 verified each against emitted SQL: - An array interpolates as a placeholder list, producing invalid Postgres, unless every site remembers `sql.param()`. Kysely emits one parameter. - A column reference inside a `sql` template is rendered without its table, so a correlated subquery silently correlates with itself — valid SQL, wrong data, and the reason #218 got a count of 1 where 2 was correct. Kysely emits the text as written. - The generated mirror is camelCase while these APIs answer snake_case, so every converted select needs an explicit column map or it silently changes the JSON contract. Kysely's generated types carry the database's own names. The cost of doing this is bounded by how little is built: one converted file, three calls, against 238 raw query sites. The cost of not doing it is paid 237 more times, in a codebase where the failure mode is a review that passes. ## Scope One change, so `main` is never half-converted between two builders: - Add `kysely` and `kysely-codegen`; remove `drizzle-orm`, `drizzle-kit`, and `drizzle.config.ts`. - Replace `src/db-drizzle/` with the Kysely equivalent — generated types, conventions, and the `buildItemFilterSql` worked example from #297's spike. - Repoint `drizzleSchema.integration.test.ts` at the new mirror. The drift it guards against is library-independent and already happened once, so the test survives the swap rather than being rewritten from scratch. - Reconvert `src/routes/adminCategories.ts`, keeping `isUniqueViolation`'s tolerance of both error shapes until it is known which shape Kysely surfaces. - Both builders must not coexist at any commit, and Kysely takes the existing `pg` Pool rather than opening its own — a transaction on one pool is invisible to the other, and the configured limits would silently double. ## Out of scope Converting anything beyond `adminCategories.ts`. That stays file by file with raw `pg` alongside, and gets its own issue. Migrations. #219 stands: `node-pg-migrate`, hand-written, prose intact. ## Done when `drizzle` appears nowhere in `backend/`, the drift test fails when the mirror and the database disagree, `adminCategories.ts` answers the same JSON it answers today, and the full backend suite is green.

bermudalamb referenced this issue from a commit

2026-09-04 16:06:26 -05:00

docs(db): design the Kysely swap (#305)

bermudalamb referenced this issue from a commit

2026-09-04 16:06:26 -05:00

docs(db): plan the Kysely swap (#305)

bermudalamb referenced this issue from a commit

2026-09-04 16:06:26 -05:00

refactor(db): swap the query builder from Drizzle to Kysely (#305)

bermudalamb referenced this issue from a commit

2026-09-04 16:06:26 -05:00

docs(db): correct the drift test's own stale references (#305)