Author: Mika (VP Engineering) Date: 2026-02-20 Status: Draft v3 (review feedback addressed) Related: ID Generation, Entity-First Architecture
@vertz/db is currently Postgres-only. Every SQL generator, migration tool, and driver assumes PostgreSQL syntax. This blocks:
- Cloudflare D1 deployment (SQLite-based) — our primary deploy target
- Edge computing (SQLite is the only embedded DB option)
- Local development without Postgres (SQLite is zero-config)
- Cost-effective free tiers (D1 free tier is generous)
- Dialect abstraction — A
Dialectinterface encapsulating SQL syntax differences - SQLite dialect — Full SQLite support in query builders, schema, and migrations
- D1 compatibility — SQLite dialect works with Cloudflare D1's API
- Zero schema changes —
d.table()definitions stay the same regardless of dialect - Migration support — CREATE TABLE DDL for both dialects (ALTER deferred for SQLite)
- Database-specific optimizations (Postgres JSONB operators, SQLite FTS) — later
- Multi-database within one app
- SQLite ALTER TABLE migrations — v0.2 (SQLite's ALTER support is too limited)
- Local SQLite driver (better-sqlite3) — v0.2. D1 is the target for v0.1.
- Binary UUID storage in SQLite — TEXT for v0.1
ID generation is implemented in @vertz/db via .primary({ generate }). See plans/db-id-generation.md. This design does NOT duplicate ID generation.
// packages/db/src/dialect/types.ts
export type IdStrategy = 'cuid' | 'uuid' | 'nanoid'; // re-exported from id/
export interface Dialect {
readonly name: 'postgres' | 'sqlite';
/** Parameter placeholder: $1 (postgres) or ? (sqlite) */
param(index: number): string;
/** SQL function for current timestamp */
now(): string;
/** Map a vertz column sqlType to the dialect's SQL type */
mapColumnType(sqlType: string, meta?: ColumnTypeMeta): string;
/** Whether the dialect supports RETURNING clause */
readonly supportsReturning: boolean;
/** Whether the dialect supports array operators (@>, <@, &&) */
readonly supportsArrayOps: boolean;
/** Whether the dialect supports JSONB path operators (->>, ->) */
readonly supportsJsonbPath: boolean;
}
export interface ColumnTypeMeta {
enumName?: string;
enumValues?: readonly string[];
length?: number;
precision?: number;
scale?: number;
}Why the dialect is minimal: The dialect provides primitives (param format, type mapping, feature flags). The SQL builders use these primitives. The dialect does NOT own the full SQL generation — that stays in the existing builder files. This keeps the refactor surgical.
This is how Dialect flows from createDb() to SQL generation:
createDb({ dialect: 'sqlite', d1: env.DB })
→ stores dialect on DbInstance
→ crud.ts reads dialect from DbInstance
→ passes dialect to buildInsert(options, dialect)
→ buildInsert calls dialect.param(), dialect.now()
→ buildWhere(filter, offset, overrides, dialect)
→ WHERE builder calls dialect.param()
Concretely:
createDb()creates aDialectinstance and stores it on the db objectcrud.tsfunctions receivedialectvia a new field on the options or via the db instance passed throughqueryFn- SQL builders (
buildInsert,buildSelect,buildUpdate,buildDelete) acceptdialectas a parameter (defaults toPostgresDialectfor backward compat) buildWhere()acceptsdialectas a parameter — this is the deepest call site
Implementation: The cleanest path is extending the existing queryFn pattern. createDb() already passes queryFn through to CRUD functions. We add dialect as a sibling:
// In crud.ts, functions already receive queryFn and table.
// Add dialect as a parameter:
export async function create<T>(
queryFn: QueryFn,
table: TableDef<ColumnRecord>,
options: CreateArgs,
dialect: Dialect = defaultPostgresDialect,
): Promise<T> { ... }database.ts passes the dialect when calling CRUD functions internally.
Extracted from existing behavior — no functional change:
// packages/db/src/dialect/postgres.ts
export class PostgresDialect implements Dialect {
readonly name = 'postgres';
readonly supportsReturning = true;
readonly supportsArrayOps = true;
readonly supportsJsonbPath = true;
param(index: number): string {
return `$${index}`;
}
now(): string {
return 'NOW()';
}
mapColumnType(sqlType: string, meta?: ColumnTypeMeta): string {
switch (sqlType) {
case 'uuid': return 'UUID';
case 'text': return 'TEXT';
case 'integer': return 'INTEGER';
case 'serial': return 'SERIAL';
case 'boolean': return 'BOOLEAN';
case 'timestamp': return 'TIMESTAMPTZ';
case 'float': return 'DOUBLE PRECISION';
case 'json': return 'JSONB';
case 'decimal': return meta?.precision ? `NUMERIC(${meta.precision},${meta.scale ?? 0})` : 'NUMERIC';
case 'varchar': return meta?.length ? `VARCHAR(${meta.length})` : 'VARCHAR';
case 'enum': return meta?.enumName ?? 'TEXT';
default: return 'TEXT';
}
}
}
export const defaultPostgresDialect = new PostgresDialect();// packages/db/src/dialect/sqlite.ts
export class SqliteDialect implements Dialect {
readonly name = 'sqlite';
readonly supportsReturning = true; // SQLite 3.35+ (D1 supports this)
readonly supportsArrayOps = false;
readonly supportsJsonbPath = false; // SQLite uses json_extract() instead
param(_index: number): string {
return '?'; // SQLite uses positional ? params
}
now(): string {
return "datetime('now')";
}
mapColumnType(sqlType: string, _meta?: ColumnTypeMeta): string {
switch (sqlType) {
case 'uuid': return 'TEXT';
case 'text': return 'TEXT';
case 'integer': return 'INTEGER';
case 'serial': return 'INTEGER'; // INTEGER PRIMARY KEY auto-increments in SQLite
case 'boolean': return 'INTEGER'; // 0/1
case 'timestamp': return 'TEXT'; // ISO 8601
case 'float': return 'REAL';
case 'json': return 'TEXT';
case 'decimal': return 'REAL';
case 'varchar': return 'TEXT';
case 'enum': return 'TEXT'; // + CHECK constraint in DDL
default: return 'TEXT';
}
}
}When a query uses Postgres-specific operators on SQLite, the WHERE builder throws a clear error:
// In where.ts, when building array operators:
if (operators.arrayContains !== undefined) {
if (!dialect.supportsArrayOps) {
throw new Error(
'Array operators (arrayContains, arrayContainedBy, arrayOverlaps) are not supported on SQLite. ' +
'Use a different filter strategy or switch to Postgres.'
);
}
// ... existing Postgres code
}
// In where.ts, when resolving JSONB path:
if (key.includes('->')) {
if (!dialect.supportsJsonbPath) {
throw new Error(
'JSONB path operators (->>, ->) are not supported on SQLite. ' +
'Use json_extract() via raw SQL or switch to Postgres.'
);
}
// ... existing Postgres code
}This fails fast with a clear message instead of generating invalid SQL.
Each builder gets a dialect parameter (defaults to PostgresDialect):
Every $${idx + 1} becomes dialect.param(idx + 1). The function signature changes:
export function buildWhere(
filter: WhereFilter | undefined,
paramOffset = 0,
overrides?: CasingOverrides,
dialect: Dialect = defaultPostgresDialect,
): WhereResult { ... }And internally buildFilterClauses and buildOperatorCondition thread dialect through. Every $${idx + 1} → dialect.param(idx + 1).
Array operators (@>, <@, &&) and JSONB path (->, ->>) get feature-flag guards as shown above.
$${allParams.length}→dialect.param(allParams.length)'NOW()'→dialect.now()- ON CONFLICT: SQLite 3.24+ uses same
ON CONFLICT ... DO UPDATE SETsyntax. TheEXCLUDEDpseudo-table also works in SQLite. No change needed here.
$${allParams.length}→dialect.param(allParams.length)(viabuildWhere)COUNT(*) OVER()— SQLite supports window functions since 3.25. D1 supports them. No change needed.
$${allParams.length}→dialect.param(allParams.length)'NOW()'→dialect.now()
$${allParams.length}→dialect.param(allParams.length)(viabuildWhere)
All builder functions default to PostgresDialect. Existing callers (both internal and any external) produce identical SQL without changes. The new parameter is always last and optional.
SQLite stores booleans as 0/1 and timestamps as TEXT. The driver converts transparently.
How it knows which columns need conversion: The createDb() call already receives tables (the table registry). The SQLite driver uses the column metadata (_meta.sqlType) to know which columns are booleans and timestamps:
// packages/db/src/client/sqlite-value-converter.ts
export interface ValueConverter {
toDb(columnName: string, value: unknown): unknown;
fromDb(columnName: string, value: unknown): unknown;
}
export function createValueConverter(tables: TableRegistry): ValueConverter {
// Build a lookup: { tableName: { columnName: sqlType } }
// On write: boolean true → 1, false → 0; Date → ISO string
// On read: INTEGER for boolean columns → true/false; TEXT for timestamp columns → Date
return {
toDb(columnName, value) {
const sqlType = lookupType(columnName);
if (sqlType === 'boolean') return value ? 1 : 0;
if (sqlType === 'timestamp' && value instanceof Date) return value.toISOString();
return value;
},
fromDb(columnName, value) {
const sqlType = lookupType(columnName);
if (sqlType === 'boolean') return value === 1 || value === true;
if (sqlType === 'timestamp' && typeof value === 'string') return new Date(value);
return value;
},
};
}Table registry is already available — createDb() receives tables today. The SQLite driver just needs it passed through:
const db = createDb({
dialect: 'sqlite',
d1: env.DB,
tables, // ← already required
});// packages/db/src/client/sqlite-driver.ts
import type { DbDriver } from './driver';
import type { QueryFn } from '../query/executor';
import { createValueConverter } from './sqlite-value-converter';
export interface SqliteDriverOptions {
binding: D1Database;
tables: TableRegistry;
}
export function createSqliteDriver(options: SqliteDriverOptions): DbDriver {
const converter = createValueConverter(options.tables);
const queryFn: QueryFn = async (sql, params) => {
// Convert params (booleans, dates) before sending to D1
const convertedParams = params.map((p, i) => converter.toDb('param', p));
const isRead = sql.trimStart().toUpperCase().startsWith('SELECT');
const stmt = options.binding.prepare(sql).bind(...convertedParams);
if (isRead) {
const result = await stmt.all();
const rows = (result.results ?? []).map(row =>
convertRowFromDb(row, converter)
);
return { rows, rowCount: rows.length };
} else {
// INSERT/UPDATE/DELETE — use .run() for writes
const result = await stmt.run();
// D1 .run() returns { results, meta } — results has RETURNING data
const rows = (result.results ?? []).map(row =>
convertRowFromDb(row, converter)
);
return { rows, rowCount: result.meta?.changes ?? rows.length };
}
};
return {
queryFn,
async close() { /* D1 connections managed by Workers runtime */ },
async isHealthy() { return true; },
};
}Note on D1 API:
.all()for reads — returns{ results: Row[], success, meta }.run()for writes — returns{ results: Row[], success, meta: { changes, ... } }.first()for single-row reads — optimization, but.all()works fine for v0.1
export interface CreateDbOptions<TTables extends Record<string, TableEntry>> {
// Existing (unchanged):
readonly url?: string;
readonly tables: TTables;
readonly pool?: PoolConfig;
readonly casing?: 'snake_case' | 'camelCase';
readonly casingOverrides?: Record<string, string>;
readonly log?: (message: string) => void;
readonly _queryFn?: QueryFn;
// New:
readonly dialect?: 'postgres' | 'sqlite';
readonly d1?: D1Database;
}Validation:
dialect: 'sqlite'withoutd1→ throws:"SQLite dialect requires a D1 binding. Pass d1: env.DB to createDb()."dialect: 'sqlite'withurl→ throws:"SQLite dialect uses D1, not a connection URL. Remove url or use dialect: 'postgres'."- No
dialect+url→ Postgres (backward compatible) - No
dialect+ nourl+ nod1→ throws (existing behavior)
// packages/db/src/client/driver.ts
export interface DbDriver {
readonly queryFn: QueryFn;
close(): Promise<void>;
isHealthy(): Promise<boolean>;
}
// Backward compat alias:
/** @deprecated Use DbDriver */
export type PostgresDriver = DbDriver;SQLite's ALTER TABLE is severely limited (no DROP COLUMN before 3.35, no ALTER COLUMN type, no ADD CONSTRAINT). For v0.1:
- ✅ CREATE TABLE — full support with SQLite types
- ✅ CREATE INDEX — same syntax, works on both
- ✅ DROP TABLE — same syntax
- ❌ ALTER TABLE — deferred to v0.2 (would need table recreation strategy)
This is sufficient for the Cloudflare demo (fresh database). Migration diffs for existing SQLite databases come later.
// sql-generator.ts accepts dialect:
function generateCreateTable(table: TableSnapshot, dialect: Dialect): string {
const columns = Object.entries(table.columns).map(([name, col]) => {
const sqlType = dialect.mapColumnType(col.type, {
enumName: col.enumName,
enumValues: col.enumValues,
length: col.length,
precision: col.precision,
scale: col.scale,
});
const parts = [`"${camelToSnake(name)}" ${sqlType}`];
if (col.primary) parts.push('PRIMARY KEY');
if (!col.nullable) parts.push('NOT NULL');
if (col.unique) parts.push('UNIQUE');
// Enum CHECK constraint for SQLite
if (col.type === 'enum' && col.enumValues && dialect.name === 'sqlite') {
const values = col.enumValues.map(v => `'${v}'`).join(', ');
parts.push(`CHECK("${camelToSnake(name)}" IN (${values}))`);
}
return parts.join(' ');
});
return `CREATE TABLE "${table.name}" (\n ${columns.join(',\n ')}\n);`;
}For Postgres, enum DDL (CREATE TYPE ... AS ENUM) is generated separately as today.
No changes. The dialect is below the entity layer:
entity('todos', { model })
→ EntityDbAdapter.create(data)
→ CRUD pipeline (fillGeneratedIds adds ID)
→ buildInsert(options, dialect) generates SQL
→ Driver executes on Postgres or SQLite
packages/db/src/
dialect/
types.ts # Dialect interface
postgres.ts # PostgresDialect (+ defaultPostgresDialect)
sqlite.ts # SqliteDialect
index.ts # Barrel
client/
driver.ts # DbDriver interface (renamed)
database.ts # Updated createDb()
postgres-driver.ts # Existing (implements DbDriver)
sqlite-driver.ts # New D1 driver
sqlite-value-converter.ts # Boolean/timestamp conversion
sql/
insert.ts # Refactored: dialect param
select.ts # Refactored: dialect param
update.ts # Refactored: dialect param
delete.ts # Refactored: dialect param
where.ts # Refactored: dialect param + feature guards
migration/
sql-generator.ts # Refactored: dialect-aware DDL
id/
generators.ts # Already exists
index.ts # Already exists
- PostgresDialect.param(1) →
$1 - PostgresDialect.param(5) →
$5 - SqliteDialect.param(1) →
? - SqliteDialect.param(5) →
? - PostgresDialect.now() →
NOW() - SqliteDialect.now() →
datetime('now') - PostgresDialect.mapColumnType — all types
- SqliteDialect.mapColumnType — uuid→TEXT, boolean→INTEGER, timestamp→TEXT, json→TEXT
- PostgresDialect.supportsArrayOps → true
- SqliteDialect.supportsArrayOps → false
- PostgresDialect.supportsJsonbPath → true
- SqliteDialect.supportsJsonbPath → false
- PostgresDialect feature completeness (all fields defined)
- SqliteDialect feature completeness (all fields defined)
- buildInsert with PostgresDialect → same SQL as before
- buildSelect with PostgresDialect → same SQL as before
- buildUpdate with PostgresDialect → same SQL as before
- buildDelete with PostgresDialect → same SQL as before
- buildInsert with SqliteDialect → ? params, datetime('now')
- buildSelect with SqliteDialect → ? params
- buildUpdate with SqliteDialect → ? params, datetime('now')
- buildDelete with SqliteDialect → ? params
- buildWhere with SqliteDialect → ? params for all standard operators
- buildWhere arrayContains on SQLite → throws descriptive error
- buildWhere JSONB path on SQLite → throws descriptive error
- buildInsert ON CONFLICT with SqliteDialect → valid syntax
- toDb: boolean true → 1
- toDb: boolean false → 0
- toDb: Date → ISO string
- fromDb: 1 → true (boolean column)
- fromDb: 0 → false (boolean column)
- fromDb: ISO string → Date (timestamp column)
- SELECT via D1 mock → correct rows returned
- INSERT with RETURNING via D1 mock → uses .run()
- Empty result set handling
- Parameter binding with ? placeholders
- Boolean/timestamp conversion end-to-end
- Default (no dialect) → Postgres (backward compat)
dialect: 'sqlite'+d1→ SQLite driver createddialect: 'sqlite'withoutd1→ throws clear errordialect: 'sqlite'+url→ throws clear error
- CREATE TABLE with Postgres types (regression)
- CREATE TABLE with SQLite types (uuid→TEXT, boolean→INTEGER)
- Enum: CREATE TYPE for Postgres
- Enum: CHECK constraint for SQLite
- CREATE INDEX — same for both dialects
Total: 46 tests
- Create
Dialectinterface andPostgresDialect - Refactor all SQL builders to accept
dialectparam (default: PostgresDialect) - Refactor
where.tsto usedialect.param() - All existing tests must pass unchanged — pure refactor
- ~15 new tests (dialect unit + Postgres regression)
- Implement
SqliteDialect - Add feature guards in
where.tsfor array/JSONB operators - ~10 new tests
sqlite-driver.ts,sqlite-value-converter.ts- Update
createDb()withdialectoption and validation - Rename
PostgresDriver→DbDriver - ~12 new tests
- Make
sql-generator.tsdialect-aware (CREATE TABLE only for SQLite) - ~5 new tests
- Entity CRUD end-to-end on SQLite (via D1 mock)
- Regression on Postgres
- D1 type definitions: Need
@cloudflare/workers-typesfor D1 types. Add as devDependency. Users on Cloudflare already have it.
| Phase | Estimate |
|---|---|
| Phase 1: Dialect interface + Postgres extraction | 1.5 days |
| Phase 2: SqliteDialect | 0.5 days |
| Phase 3: D1 driver + createDb | 1.5 days |
| Phase 4: Migration generator | 0.5 days |
| Phase 5: Integration tests | 1 day |
| Total | ~5 days |
With parallel agent execution: 2-3 days.