Switch default ORM from Prisma to Drizzle across scaffolds, webjs db, docs, blog

[vivek7405]

Problem

Make Drizzle the default ORM in all three scaffolds, replacing Prisma. Decision and full rationale are in the closed research record #546 (research issue #532). In short: Drizzle fits webjs's buildless + Node-or-Bun thesis better (no module-graph codegen, native bun:sqlite, decorator-free erasable TS, edge-ready), and its schema/query/mutation DX was brought close enough to Prisma to win on the merits. Supersedes the closed Prisma-hardening umbrella #547 (moot under a no-codegen ORM).

Depends on #550 (unify webjs dev/start/db with the npm-script behavior). The Drizzle command story assumes the unified model.

Decision summary (locked DX)

Imports: plain drizzle-orm, NOT an alias. Deep research (recorded on #546) confirmed a "drizzle": "npm:drizzle-orm" alias is unsafe: an unrelated real npm package drizzle exists (Truffle web3), and aliasing duplicates drizzle-orm (drizzle-kit resolves it by real name), triggering the dual-package hazard (identity/instanceof failures, "Please install latest version of drizzle-orm"). A re-export barrel was rejected too, because agents reach for drizzle-orm from muscle memory and the direct import still works, so the barrel silently drifts. Going with the grain (plain drizzle-orm) is the AI-first choice.

CLI: webjs db <sub> wraps drizzle-kit (generate / migrate / push / studio); npm scripts call webjs db; users run npm run db:*. The verbose drizzle-kit name stays hidden. Both webjs db and npm run db:* work once #550 lands.

Schema DX (single file, agreed shape in #546): db/schema.ts with lib/db/columns.ts helpers (pk(), createdAt()), casing: 'snake_case' on the db instance (no per-column name strings), anonymous indexes (index().on(...), auto-named; explicit name only for sql-expression indexes), per-model relation callbacks co-located under each table, assembled with relations v2 defineRelations at the bottom.

Query DX: relational db.query.<table>.findMany/findFirst({ where, orderBy, with, columns }), top-level operator style (where: eq(posts.slug, slug), orderBy: desc(posts.createdAt)), importing operators + table consts.

Mutation DX: builder writes (db.insert/update/delete().values/set().returning()); for create-then-return-with-relations, (a) splice known data when the related row is in hand, (c) inline db.query re-read by id when it isn't (no helper); db.transaction() for multi-table; onConflictDoUpdate for upsert.

Plumbing: lib/db.server.ts singleton (drizzle(new Database('dev.db'), { schema, relations: schema.relations, casing: 'snake_case' }), globalThis-cached like the Prisma one). Types are inferred by default, declare none; where a name is needed at a boundary, derive it (typeof posts.$inferSelect, ReturnType<typeof formatPost>), never hand-write.

Runtime: native bun:sqlite on Bun, better-sqlite3 (or node:sqlite) on Node, selected in lib/db.server.ts. No engine binary, no generate step.

Implementation notes (for the implementing agent)

• Scaffold generator: packages/cli/lib/create.js (replace the prisma/schema.prisma template, lib/prisma.server.ts singleton, the @prisma/client/prisma deps, the predev: prisma generate + prestart: prisma migrate deploy hooks, the db:* scripts, the DATABASE_URL env, and the gitignore lines, with the Drizzle equivalents: db/schema.ts, lib/db/columns.ts, lib/db.server.ts, drizzle.config.ts, drizzle-orm + drizzle-kit deps, a SQLite driver per runtime). Drop the predev generate hook entirely (no codegen).
• SaaS template: packages/cli/lib/saas-template.js (port the User model + the signup/current-user actions to Drizzle).
• CLI webjs db: packages/cli/bin/webjs.js case 'db' (~L147) repoint generate/migrate/studio + add push to drizzle-kit.
• Delete: packages/cli/lib/prisma-preflight.js + packages/cli/test/prisma-preflight/ (no generated client to detect).
• Docs: rewrite docs/app/docs/database/page.ts; update Prisma mentions in architecture, authentication, deployment, getting-started, backend-only, troubleshooting.
• Agent guidance: root AGENTS.md ("Default to a real database (Prisma + SQLite)" -> Drizzle; the webjs db line; the lib/*.server infra mention) and the scaffold templates' AGENTS.md + CONVENTIONS.md ("Persist data with Prisma + SQLite" -> Drizzle).
• Example app: examples/blog (port prisma/schema.prisma to db/schema.ts, the singleton, and every query/action module; the real files were mapped in Research: Drizzle vs Prisma as the default ORM, leaning Drizzle (#532) #546; reads port near 1:1).
• MCP: check packages/mcp for any Prisma-specific projection/wording.
• Tests: test/scaffolds/scaffold-integration.test.js (assert the Drizzle files, not the Prisma ones); add a Bun cross-runtime assert that the scaffolded DB round-trips on bun:sqlite; the blog e2e on BOTH Node and Bun.
• Landmines: the casing: 'snake_case' known edge (unique-constraint names not always converted, verify migrations); relations v2 (defineRelations/defineRelationsPart) is on the 1.0 beta line, pin a known-good version; db.query with keys are type-checked against the defined relations (a wrong relation name is a compile error, good). Do NOT alias drizzle-orm (dual-package hazard, see Research: Drizzle vs Prisma as the default ORM, leaning Drizzle (#532) #546).
• Invariants: server-only DB code stays in .server.{js,ts} (invariant 1); erasable TS only, no TS enum in a model (use a union or pgEnum/sqlite text .$type), invariant 10.

Acceptance criteria

• All three scaffolds generate a Drizzle app (single-file schema + helpers + casing + anonymous indexes + co-located relations) that boots on Node AND Bun with no generate step.
• webjs db generate/migrate/push/studio work and npm run db:* are equivalent (per Unify webjs dev/start/db with npm-script behavior via a declarative tasks config #550).
• examples/blog fully ported; blog e2e passes on Node AND Bun.
• All Prisma artifacts removed (deps, schema.prisma, prisma.server.ts, prisma-preflight, predev/prestart prisma hooks).
• Imports are plain drizzle-orm (no alias, no barrel).
• Docs + AGENTS.md + scaffold templates + (if needed) MCP updated to Drizzle.
• A counterfactual proves the scaffold-integration test fails if the Drizzle files are not written.
• Tests at every layer touched (unit, scaffold, Bun cross-runtime DB round-trip, blog e2e).

Closes #532 follow-through. Record: #546.

---

File structure (converged from the design discussion)

All database code lives in ONE db/ folder; only drizzle.config.ts sits at the root (drizzle-kit requires that exact filename). Every TypeScript file in db/ carries .server.ts, because in webjs a plain .ts is servable to the client if it is reachable from a browser entry (or requested by URL), and the .server.ts extension is the path-level guarantee that the file is never served and a browser import throws at load. The DB layer is server-only by nature, so the whole folder is .server.ts.

db/
  schema.server.ts       # models + relations (single file: helpers + casing + anon indexes + defineRelations)
  columns.server.ts      # pk(), createdAt() helpers
  connection.server.ts   # opens the SQLite connection, exports `db`  (the only file importing the driver)
  seed.server.ts         # seed script (run-directly via `webjs db seed`; .server.ts still executes fine)
  migrations/            # drizzle-kit output (generated SQL + meta), committed
drizzle.config.ts        # root; schema: './db/schema.server.ts', out: './db/migrations', dbCredentials.url from process.env

Naming + boundary rules:

• The connection file is connection.server.ts (not client.server.ts, "client" is a Prisma-ism; not index.server.ts, which buys nothing because webjs is native-ESM with explicit extensions and no directory-index resolution, so import from '../db' does not work, you would write ../db/index.server.ts anyway).
• connection.server.ts exports db = drizzle(new Database(...), { schema, relations: schema.relations, casing: 'snake_case' }), globalThis-cached like the old Prisma singleton.
• db is used ONLY inside server-only files: modules/*/queries/*.server.ts, modules/*/actions/*.server.ts, app/**/route.ts, middleware.ts, and seed.server.ts. It is NEVER imported into a page, layout, or component, those call the query/action functions (RPC stubs on the client), exactly as they called Prisma-backed actions before.
• drizzle.config.ts is the one constrained exception (cannot be .server.ts, drizzle-kit needs the literal name); keep secrets out of it by sourcing the DB URL from process.env.

Imports: plain drizzle-orm (decided, no alias, no barrel, see the dual-package research on #546). Deep relative paths today (../../../db/connection.server.ts). A short #db / @/db form (e.g. import { db } from '#db') is a SEPARATE, deferred concern tracked in #549 (path-alias research); when it lands it maps the alias to db/connection.server.ts without renaming the file, and the .server.ts boundary must survive the alias.

Before (Prisma) -> After (Drizzle)

• REMOVED: prisma/ (schema.prisma, seed, db file), lib/prisma.server.ts, @prisma/client + prisma deps, the predev: prisma generate / prestart: prisma migrate deploy hooks, and (framework-side) packages/cli/lib/prisma-preflight.js + its tests.
• ADDED: the db/ folder above, drizzle.config.ts, drizzle-orm + drizzle-kit deps + a SQLite driver (bun:sqlite on Bun, better-sqlite3/node:sqlite on Node).
• CHANGED: every *.server.ts query/action swaps import { prisma } for import { db } from '.../db/connection.server.ts' + the table(s) from db/schema.server.ts + operators from drizzle-orm; prisma.post.findMany({ include }) -> db.query.posts.findMany({ with }); writes -> db.insert().values().returning(). The predev/prestart orchestration moves into the "webjs" tasks config per Unify webjs dev/start/db with npm-script behavior via a declarative tasks config #550.

Depends on #550 (so webjs dev/start/db and npm run dev/start/db:* behave identically). Path-alias short imports tracked in #549.