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

[vivek7405]

Resolution status (see the "Resolutions to the 5 open questions" comment for detail): Q1 (column surface + escape hatch), Q2 (portable insertOne), Q3 (scaffold --db flag), Q4 (str length / text) are RESOLVED and verified on rc.3. Q5 (ship the abstraction by default vs SQLite-default + opt-in) is the one OPEN decision, pending the owner. Recommendation on record: SQLite-default with minimal helpers (pk/createdAt/index) as the default, the full portable abstraction as a documented opt-in.

Question

Can webjs offer a single, portable Drizzle DX (schema + queries + mutations + inferred types) across SQLite, Postgres, and MySQL, via a thin abstraction, while keeping the buildless thesis? If so, finalize that DX and apply it to the final Drizzle implementation (#551).

Motivation: Prisma's schema is largely DB-agnostic (switch provider + URL). Drizzle's column builders are dialect-specific, so a naive Drizzle adoption loses that portability. This record investigates whether a thin abstraction recovers Prisma-grade portability on Drizzle's buildless base.

Status: OPEN, research in progress. Pinned to drizzle-orm + drizzle-kit 1.0.0-rc.3 (the newest clean prerelease; relations v2 defineRelations only exists on the 1.0 line). All findings below verified by real tsc --noEmit (strict + erasableSyntaxOnly) and, where noted, drizzle-kit generate and a better-sqlite3 round-trip.

Verified findings (drizzle-orm 1.0.0-rc.3)

1. Schema portability via per-dialect column modules: FEASIBLE

A unified column API (table, pk, uuidPk, str, int, bool, createdAt, updatedAt, index) implemented once per dialect (columns.sqlite, columns.pg, columns.mysql), each mapping to that dialect's builders. The SAME schema.server.ts body, written against the unified API, compiled clean against ALL THREE dialect modules.

2. Inferred types are identical across dialects

A bidirectional type-equality assertion on $inferSelect passed on all three: User resolves to exactly { id: number; email: string; name: string | null; active: boolean; createdAt: Date } regardless of dialect. So active (sqlite integer boolean-mode vs pg/mysql native boolean) and createdAt (sqlite integer timestamp vs pg/mysql native timestamp) all infer the same boolean / Date. App code that consumes these types is portable.

3. Reads, updates, deletes: portable as-is

The same code compiled against the three real db types (better-sqlite3, node-postgres, mysql2): db.select().from().where(eq()).orderBy(desc()), db.update().set().where(), db.delete().where(), and the relational db.query.* surface. No per-dialect change.

4. The ONE break: MySQL has no .returning()

db.insert(t).values(...).returning() typechecks on SQLite and Postgres but FAILS on MySQL (Property 'returning' does not exist on type 'MySqlInsertBase...'. Did you mean '$returningId'?). MySQL only exposes $returningId(). So the create-then-return-the-row pattern is the single non-portable mutation. A portable insertOne(db, table, values) helper closes the gap (.returning() on sqlite/pg, $returningId() + re-read on mysql).

5. Primary key: int OR string, both infer correctly

Verified tsc exact-equality: integer PK infers id: number, uuid/text PK infers id: string. Per dialect: int = integer().primaryKey({autoIncrement}) / serial().primaryKey() / int().autoincrement().primaryKey(); string = text().primaryKey().$defaultFn(() => crypto.randomUUID()) (sqlite/mysql) / uuid().primaryKey().defaultRandom() (pg). Expose two helpers (pk() int, uuidPk() string) rather than one overloaded fn (a single fn returning a union wrecks inference).

6. rc.3 DX specifics (apply to all dialects)

• Casing lives on the table creator: sqliteTableCreator((n) => n, 'snake_case') (and pg/mysql equivalents). The connection-config casing was removed in rc.3.
• many relations need explicit { from, to } to typecheck; bare r.many.x() errors.
• index() needs a name to typecheck (runtime auto-names). A column-deriving index(...cols) helper recovers the anonymous call site and emits drizzle-kit's own table-qualified names.
• createdAt default: integer({ mode: 'timestamp_ms' }).notNull().defaultNow(). Footgun: defaultNow() emits milliseconds, so it MUST pair with timestamp_ms, never timestamp (seconds), or dates land in year 58429.
• updatedAt: defaultNow().$onUpdate(() => new Date()) (sqlite/pg, app-level) vs MySQL native defaultNow().onUpdateNow() (DB-enforced). The helper hides the difference.

Proposed DX (to finalize)

db/
  columns.server.ts     # re-exports ONE dialect's helpers (scaffold writes per chosen DB)
  columns.sqlite.ts     # { table, pk, uuidPk, str, int, bool, createdAt, updatedAt, index }
  columns.pg.ts
  columns.mysql.ts
  write.server.ts       # insertOne() portable create-and-return helper
  schema.server.ts      # written ONCE against columns.server.ts, identical for any DB
  connection.server.ts  # per-dialect driver (bun:sqlite / better-sqlite3 / pg / mysql2)

Schema, relations, queries, actions, and inferred types stay portable. Switch DB = swap the active columns module + the connection driver, regenerate migrations.

Open questions to finalize

1. Unified API surface: which column types are in scope (text/varchar length handling, numeric, boolean, json, blob)? What is the escape-hatch convention for dialect-only types (pg arrays/jsonb ops, mysql fulltext)?
2. insertOne ergonomics + typing across the three db types (generics vs per-dialect wrapper).
3. How the scaffold selects a dialect (a --db flag) and what it writes (which columns.* becomes columns.server.ts, which connection, which drizzle.config).
4. str() length: pg/mysql varchar wants a length, sqlite text does not. Default length, or str({ length })?
5. Is the maintenance cost (owning 3 column modules + a write helper + 3-dialect tests) worth the portability for webjs's audience, or is SQLite-only the right default with the abstraction as opt-in?

Relationship to #551

#551 is scoped to "default ORM = Drizzle, SQLite default." This cross-DB abstraction is a larger, separate design. The plan: finalize the DX in THIS record, then implement. The final Drizzle implementation (#551 and/or a dedicated feature issue filed from this record) adopts the finalized DX. Keep #551 lean unless we decide to fold the abstraction in.

[vivek7405]

Deep-dive: verified artifacts

All checked on drizzle-orm@1.0.0-rc.3 + drizzle-kit@1.0.0-rc.3, typescript@5.9, tsc --noEmit with strict + erasableSyntaxOnly + skipLibCheck.

Unified column module (per dialect, same export names)

// columns.sqlite.ts
export const table = sqliteTableCreator((n) => n, 'snake_case');
export const pk = () => integer().primaryKey({ autoIncrement: true });
export const uuidPk = () => text().primaryKey().$defaultFn(() => crypto.randomUUID());
export const str = () => text();
export const int = () => integer();
export const bool = () => integer({ mode: 'boolean' });
export const createdAt = () => integer({ mode: 'timestamp_ms' }).notNull().defaultNow();
export const updatedAt = () => integer({ mode: 'timestamp_ms' }).notNull().defaultNow().$onUpdate(() => new Date());
// columns.pg.ts: pgTableCreator, serial()/uuid().defaultRandom(), varchar({length:255}), boolean(), timestamp({withTimezone}).defaultNow()
// columns.mysql.ts: mysqlTableCreator, int().autoincrement(), varchar({length:255}), boolean(), timestamp().defaultNow().onUpdateNow()

One schema body, compiled against all three

import { table, pk, str, int, bool, createdAt, index } from './cols_active.ts';
import { defineRelations } from 'drizzle-orm';
export const users = table('users', { id: pk(), email: str().notNull().unique(), name: str(), active: bool().notNull().default(true), createdAt: createdAt() });
export const posts = table('posts', { id: pk(), title: str().notNull(), authorId: int().notNull().references(() => users.id, { onDelete: 'cascade' }), createdAt: createdAt() }, (t) => [index(t.authorId), index(t.createdAt)]);
export const relations = defineRelations({ users, posts }, (r) => ({
  users: { posts: r.many.posts({ from: r.users.id, to: r.posts.authorId }) },
  posts: { author: r.one.users({ from: r.posts.authorId, to: r.users.id }) },
}));
export type User = typeof users.$inferSelect;

Result: tsc exit 0 against sqlite, pg, AND mysql column modules. A $inferSelect bidirectional equality assertion (User = { id: number; email: string; name: string | null; active: boolean; createdAt: Date }) also passed on all three.

The MySQL .returning() break

await sdb.insert(sPosts).values({ title: 'x' }).returning(); // sqlite OK
await pdb.insert(pPosts).values({ title: 'x' }).returning(); // postgres OK
await mdb.insert(mPosts).values({ title: 'x' }).returning(); // mysql -> TS2551 'returning' does not exist, "Did you mean '$returningId'?"

Reads (select().from().where().orderBy()), update().set().where(), and delete().where() compiled clean on all three.

PK int vs string inference

integer().primaryKey({ autoIncrement: true })        // id: number  (verified)
text().primaryKey().$defaultFn(() => crypto.randomUUID()) // id: string (verified)
uuid().primaryKey().defaultRandom()                  // id: string  (pg, verified)

index() helper (anonymous call site, table-qualified auto name)

export const index = (...cols: SQLiteColumn[]) =>
  _index(`${getTableName((cols[0] as unknown as { table: Table }).table)}_${cols.map((c) => c.name).join('_')}_idx`).on(...(cols as [SQLiteColumn, ...SQLiteColumn[]]));

drizzle-kit generate emitted collision-free posts_author_id_idx, comments_post_id_created_at_idx, etc. (table-qualified, so author_id on two tables does not clash). The plain index(undefined as any) shortcut was REJECTED: drizzle-kit named it literally undefined and two collided.

createdAt default footgun

defaultNow() emits a milliseconds expression, so it is correct only with mode: 'timestamp_ms'. Paired with mode: 'timestamp' (seconds) it round-tripped to year +058429. .default(sql\(unixepoch())`)+mode: 'timestamp'` is the second-precision alternative.

[vivek7405]

Resolutions to the 5 open questions

All code claims verified on drizzle-orm@1.0.0-rc.3 via tsc --noEmit (strict + erasableSyntaxOnly) and, where noted, runtime.

Q1. Unified column surface + escape hatch (RESOLVED)

Surface (each maps to a dialect builder, all verified to infer the IDENTICAL row shape across sqlite/pg/mysql):
table, pk (int), uuidPk (string), str(max = 255) (varchar, or sqlite text), text() (unbounded), int, num (real / double / doublePrecision), bool, json<T>() (typed), createdAt, updatedAt, index.
Proof: a schema using str(), str(80), text(), num(), bool().default(false), json<Meta>(), createdAt() inferred exactly { id: number; title: string; summary: string | null; body: string; rating: number | null; published: boolean; meta: Meta | null; createdAt: Date } on ALL THREE dialects (bidirectional equality assertion passed).
Escape hatch: for a dialect-only type (pg jsonb operators / arrays, mysql fulltext, partial indexes), import the raw builder from that dialect core directly for that one column. That column becomes dialect-specific by choice, and the rest of the schema stays portable. This is the documented boundary of the abstraction.

Q2. Portable insertOne (RESOLVED)

A per-dialect write.server.ts (the scaffold writes the matching one), typed generically with drizzle's InferInsertModel<T> / InferSelectModel<T> (NOT T['$inferInsert'], which is not index-accessible on a generic constraint):

// sqlite/pg
export async function insertOne<T extends SQLiteTable>(t: T, values: InferInsertModel<T>): Promise<InferSelectModel<T>> {
  const [row] = await db.insert(t).values(values as any).returning();
  return row as InferSelectModel<T>;
}
// mysql (no RETURNING)
export async function insertOne<T extends MySqlTable & { id: any }>(t: T, values: InferInsertModel<T>): Promise<InferSelectModel<T>> {
  const res = await db.insert(t).values(values as any).$returningId();
  const [row] = await db.select().from(t).where(eq(t.id, res[0].id)).limit(1);
  return row as InferSelectModel<T>;
}

Call site is identical and fully typed: const row = await insertOne(posts, { title }) (values checked, return is the row). Verified compile (both dialects) + runtime (sqlite returns the full row incl. the defaulted createdAt). Caveat: the mysql re-read keys on a single id pk. An app-generated (uuid) pk is already in values, so re-read by that.

Q3. Scaffold dialect selection (DECIDED)

webjs create <app> --db sqlite|postgres|mysql (default sqlite). The flag materializes the dialect-specific files only: columns.server.ts (= chosen dialect helpers), connection.server.ts (driver: bun:sqlite/better-sqlite3, pg, or mysql2), write.server.ts (the dialect insertOne), drizzle.config.ts (dialect + url), the driver dep, and .env DATABASE_URL. schema.server.ts, queries, and actions are the SAME template regardless of dialect. One dialect ships per app (not all three modules).

Q4. str length / text (RESOLVED)

str(max = 255) maps to varchar({ length: max }) on pg/mysql and text({ length: max }) on sqlite (accepted, ignored at the DB). text() maps to unbounded text on all three. Both infer string. Verified.

Q5. Default vs opt-in (OPEN, owner decision)

Two distinct, separable things:

• A. The --db flag generating a dialect-appropriate schema (can be close-to-raw drizzle for that dialect, minimal helpers). Low maintenance, covers "I want Postgres."
• B. The full unified abstraction (one schema body portable across ALL dialects, swap the dialect modules). Delivers Prisma-grade portability, but webjs OWNS the column/write abstraction across drizzle version churn, and it adds indirection over raw drizzle (tension with the stated "stay close to the drizzle API" preference).
  Most apps pick one DB and never switch, so B's portability is rarely exercised while its indirection is always felt. Recommendation: ship A as the default (close to drizzle, low cost) with the minimal helper set (pk, createdAt, index), and offer B as a documented opt-in pattern for apps that genuinely want DB portability, not the default. Pending the owner's call.

[vivek7405]

Q5 decided: B (ship the unified abstraction)

Motivation locked it: the dev-on-SQLite, prod-on-Postgres workflow (same code, switch config) is a first-class goal, and only the full unified abstraction (B) delivers it. So the finalized DX is the portable abstraction: one schema.server.ts + queries + actions, with per-dialect columns / write / connection modules the scaffold materializes per --db.

Caveat carried into the implementation (a docs + CI concern, not a blocker): the abstraction makes the CODE portable, NOT the database behavior. Migrations are generated per-dialect (drizzle-kit emits different SQL), and runtime semantics differ (case-sensitivity, coercion, constraints, concurrency). So a dev-sqlite / prod-postgres app MUST run its test suite against the prod engine in CI. The switch removes code-rewrite friction; it does not remove the need to validate against the real engine. The scaffold should document this and wire a CI matrix (or at least a prod-engine test job) when --db is not sqlite.

This concludes the research. Finalized DX = the per-dialect-module abstraction across the Q1 to Q4 resolutions, with Q5 = B. Implementation tracked as a separate issue.

[vivek7405]

Final plan: scoped to SQLite + Postgres, minimal abstraction

Refinement of the Q5=B conclusion after working the DX end to end. Decision: support SQLite + Postgres only (drop MySQL). That removes the single biggest abstraction, the insertOne write helper, because both SQLite and Postgres have .returning(). Mutations go back to raw drizzle (db.insert(t).values(...).returning()). The remaining abstraction is ONE file (columns.server.ts, one variant per dialect), which re-exports the common raw builders and adds a thin helper only for the columns that genuinely differ between the two engines. Goal honored: least abstraction, stay close to the drizzle API.

All verified on drizzle-orm@1.0.0-rc.3 (tsc strict + erasableSyntaxOnly, plus runtime on sqlite).

What stays vs goes (vs the 3-dialect design)

• GONE: the write.server.ts / insertOne helper (raw .returning() works on both).
• GONE: the MySQL module, $returningId re-read path, str(max)/varchar variance, num, the mode config.
• STAYS: one columns.server.ts (per dialect) = re-exported raw builders + helpers ONLY for table, pk, uuidPk, uuid, bool, createdAt, updatedAt, index.
• updatedAt is now uniform: both dialects use app-level $onUpdate(() => new Date()) (neither has native ON UPDATE without a trigger; the one place MySQL was better). Fires on db.update() through drizzle, which is every webjs write.

The two complete columns files

db/columns.sqlite.ts

import { sqliteTableCreator, integer, index as _index } from 'drizzle-orm/sqlite-core';
import type { SQLiteColumn } from 'drizzle-orm/sqlite-core';
import { getTableName, type Table } from 'drizzle-orm';

// raw drizzle builders, re-exported (identical call sites on both dialects)
export { text, integer, real, blob } from 'drizzle-orm/sqlite-core';

// the genuinely-divergent bits, thin helpers:
export const table = sqliteTableCreator((name) => name, 'snake_case');
export const pk = () => integer().primaryKey({ autoIncrement: true });
export const uuidPk = () => text().primaryKey().$defaultFn(() => crypto.randomUUID());
export const uuid = () => text();
export const bool = () => integer({ mode: 'boolean' });
export const createdAt = () => integer({ mode: 'timestamp_ms' }).notNull().defaultNow();
export const updatedAt = () => integer({ mode: 'timestamp_ms' }).notNull().defaultNow().$onUpdate(() => new Date());
export const index = (...c: SQLiteColumn[]) =>
  _index(`${getTableName((c[0] as unknown as { table: Table }).table)}_${c.map((x) => x.name).join('_')}_idx`).on(...(c as [SQLiteColumn, ...SQLiteColumn[]]));

db/columns.pg.ts

import { pgTableCreator, serial, uuid as pgUuid, boolean, timestamp, index as _index } from 'drizzle-orm/pg-core';
import type { PgColumn } from 'drizzle-orm/pg-core';
import { getTableName, type Table } from 'drizzle-orm';

export { text, integer, real } from 'drizzle-orm/pg-core';

export const table = pgTableCreator((name) => name, 'snake_case');
export const pk = () => serial().primaryKey();
export const uuidPk = () => pgUuid().primaryKey().defaultRandom();
export const uuid = () => pgUuid();
export const bool = () => boolean();
export const createdAt = () => timestamp({ withTimezone: true }).notNull().defaultNow();
export const updatedAt = () => timestamp({ withTimezone: true }).notNull().defaultNow().$onUpdate(() => new Date());
export const index = (...c: PgColumn[]) =>
  _index(`${getTableName((c[0] as unknown as { table: Table }).table)}_${c.map((x) => x.name).join('_')}_idx`).on(...(c as [PgColumn, ...PgColumn[]]));

The scaffold writes ONE of these as db/columns.server.ts per --db (default sqlite). The schema, queries, and actions import from ./columns.server.ts and are identical regardless. Mutations use raw .returning() (no helper). PK is pk() (int -> number) or uuidPk() (string), with uuid() for FKs to a uuid PK.

Implementation proceeds in #563, applied within the #551 Prisma-to-Drizzle PR.