feat: cross-database Drizzle abstraction (sqlite/postgres/mysql)

[vivek7405]

Problem

webjs is adopting Drizzle as the default ORM (#551). Research #562 finalized a cross-database DX so one schema.server.ts plus all queries and actions are portable across SQLite, Postgres, and MySQL, and an app can develop on SQLite and deploy on Postgres by switching a config (the key motivating workflow). This issue implements that finalized DX. It is the schema/DB-layer design the #551 migration adopts.

Feasibility is fully verified on drizzle-orm@1.0.0-rc.3 (see #562 and its deep-dive comments): the same schema body compiles and infers an identical row shape across all three dialects, reads/updates/deletes are portable as-is, and the one break (MySQL has no .returning()) is closed by a portable insertOne helper.

Design / approach

A thin abstraction, materialized per dialect by the scaffold:

db/
  columns.server.ts      # re-exports the chosen dialect's helpers
  columns.sqlite.ts      # unified API via sqlite-core
  columns.pg.ts          # unified API via pg-core
  columns.mysql.ts       # unified API via mysql-core
  write.server.ts        # insertOne() (per-dialect: returning vs $returningId+re-read)
  connection.server.ts   # per-dialect driver
  schema.server.ts       # written ONCE against columns.server.ts, portable
  seed.server.ts
  migrations/
drizzle.config.ts        # dialect + url from process.env

Unified column API (all verified to infer the identical row shape across dialects): table, pk (int -> number), uuidPk (string), str(max = 255) (varchar / sqlite text), text() (unbounded), int, num (real / double / doublePrecision), bool, json<T>() (typed), createdAt, updatedAt, index(...cols) (anonymous-style, table-qualified auto name). Relations use defineRelations (dialect-agnostic). Reads use the relational object-filter API (where: {...}, orderBy: {...}). Writes use the builder; create-and-return uses insertOne for portability.

webjs create <app> --db sqlite|postgres|mysql (default sqlite) materializes only the dialect-specific files (columns/connection/write/config/driver dep/.env); the schema, queries, and actions are the same template regardless.

Implementation notes (for the implementing agent)

• Read research: cross-database Drizzle DX abstraction (sqlite/postgres/mysql) #562 first (closed research issue): it has the finalized DX, every verified snippet, and the landmines below with evidence.
• Where to edit:
  • packages/cli/lib/create.js: replace the Prisma scaffold (the prisma dir at ~L267, the schema.prisma + lib/prisma.server.ts templates ~L489-520, the @prisma/client/prisma deps ~L302-308, the db:* scripts ~L297-299, the webjs dev/start tasks ~L342-345, the DATABASE_URL env ~L522-533, the .gitignore prisma lines ~L537-543) with the db/ folder generation above, dialect-selected.
  • packages/cli/bin/webjs.js: add --db flag parsing for create (the TEMPLATES/create dispatch). The webjs db command itself already points at drizzle-kit (landed in this PR's branch / Switch default ORM from Prisma to Drizzle across scaffolds, webjs db, docs, blog #551).
  • packages/cli/lib/saas-template.js: port the User model + auth (signup/current-user/authorize) to the Drizzle abstraction.
  • examples/blog: the Switch default ORM from Prisma to Drizzle across scaffolds, webjs db, docs, blog #551 SQLite port adopts columns.sqlite + the unified schema.
• Landmines (all verified on rc.3 in research: cross-database Drizzle DX abstraction (sqlite/postgres/mysql) #562):
  • Pin drizzle-orm AND drizzle-kit to 1.0.0-rc.3 (relations v2 defineRelations only exists on the 1.0 line; the rc dist-tag is rc.3, rc.4 is only a commit snapshot).
  • Connection is drizzle({ client, relations }), NOT positional drizzle(client, {...}) (positional silently opens a fresh empty DB).
  • Casing lives on the table creator sqliteTableCreator((n) => n, 'snake_case'), NOT the connection (rc.3 removed connection casing; it is a silent no-op there).
  • many relations need explicit { from, to } to typecheck; bare r.many.x() errors.
  • index() needs a name to typecheck (runtime auto-names). Use the index(...cols) helper that derives a table-qualified name via getTableName(col.table) + column names (verified collision-free; index(undefined as any) is REJECTED, drizzle-kit names it literally undefined).
  • createdAt: integer({ mode: 'timestamp_ms' }).notNull().defaultNow(). defaultNow() emits milliseconds, so it MUST pair with timestamp_ms, never timestamp (seconds) or dates land in year 58429.
  • updatedAt: sqlite/pg defaultNow().$onUpdate(() => new Date()) (app-level); mysql native defaultNow().onUpdateNow().
  • MySQL has NO .returning() (only $returningId()); insertOne must branch (returning on sqlite/pg, $returningId() + re-read on mysql).
  • Generic write helper types use InferInsertModel<T> / InferSelectModel<T> from drizzle-orm, NOT T['$inferInsert'] (not index-accessible on a generic constraint).
  • Migrations are generated per-dialect and runtime behavior differs, so a dev-sqlite / prod-postgres app MUST run its test suite against the prod engine in CI (the abstraction makes code portable, not DB behavior).
• Invariants: all db/*.ts carry .server.ts (invariant 1, never served to the browser); erasable TS only (invariant 10, the abstraction uses import type + as casts only); the index helper's one runtime-.table cast is as unknown as { table: Table } (erasable).

Acceptance criteria

• A single schema.server.ts written against the unified column API compiles AND infers identical row types under webjs typecheck for all three dialect column modules.
• webjs create <app> --db sqlite|postgres|mysql scaffolds a booting app for each; default (no flag) is sqlite.
• insertOne(table, values) returns the full inserted row, typed, on sqlite/pg (returning) and mysql ($returningId + re-read).
• Reads / updates / deletes and the relational db.query.* surface are byte-identical across dialects (one query/action template).
• CI runs the scaffold/blog test suite against Postgres (and ideally MySQL) via a service container, not just SQLite.
• webjs db generate/migrate/push work per dialect; npm run db:* equivalent.
• A counterfactual proves the scaffold-integration test fails if the db/ files are not written.
• Tests at every layer touched (unit, scaffold-integration, Bun cross-runtime DB round-trip, blog e2e on Node AND Bun).
• Docs + AGENTS.md + scaffold templates updated (the db/ layout, the --db flag, the dev-sqlite/prod-postgres testing caveat).

Finalized DX: #562. Base Prisma->Drizzle migration: #551.

[vivek7405]

Scope refined: SQLite + Postgres only, minimal abstraction

Per the finalized plan in #562: drop MySQL, support SQLite + Postgres. This removes the insertOne/write.server.ts helper entirely (both dialects have .returning(), so mutations are raw drizzle). The abstraction is now ONE file (db/columns.server.ts, one variant per dialect) that re-exports the common raw builders and adds thin helpers only for table, pk, uuidPk, uuid, bool, createdAt, updatedAt, index. The two complete column files are in #562's final-plan comment.

Updated deliverables:

• db/ layer: columns.server.ts (sqlite default), connection.server.ts, schema.server.ts, seed.server.ts, drizzle.config.ts. No write.server.ts.
• Scaffold --db sqlite|postgres (default sqlite) writes the matching columns.server.ts + connection.server.ts + driver dep (better-sqlite3 / pg) + drizzle.config + .env.
• Mutations use raw db.insert().values().returning(); reads use db.query.* object filters; relations via per-model fns + defineRelations.
• CI: run the suite against Postgres (service container) in addition to SQLite, since migrations + runtime behavior are per-dialect.

Being implemented within the #551 Prisma-to-Drizzle PR (#558), which carries Closes #563.