Server: Drizzle ORM
Drizzle is a thin, SQL-first query builder, which makes it a good fit for Twill's focused SQL surface: the queries it generates are close to the SQL you'd write by hand. Point it at engine-server through a standard Postgres driver.
Prerequisites
A running server, and npm i drizzle-orm postgres (the postgres-js driver; pg works too via drizzle-orm/node-postgres).
Step 1 — the client
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
const client = postgres("postgresql://[email protected]:5433/main?sslmode=disable", {
ssl: false,
prepare: true, // use the extended (prepared) protocol — supported by the server
});
export const db = drizzle(client);Breakdown: ssl: false matches the cleartext listener; prepare: true uses Parse/Bind/Execute, which the server implements. drizzle(client) wraps the driver in the query builder.
Step 2 — the schema
import { pgTable, integer, text } from "drizzle-orm/pg-core";
export const notes = pgTable("notes", {
id: integer("id").primaryKey(),
body: text("body").notNull(),
ts: text("ts").notNull(),
});Breakdown: declare tables with column types the engine supports (integer/text here). This is the type-safe handle Drizzle uses to build queries — it does not by itself create the table (see the caveat below).
Step 3 — queries & transactions
import { eq, desc } from "drizzle-orm";
// select
const recent = await db.select().from(notes).orderBy(desc(notes.id)).limit(50);
// insert
await db.insert(notes).values({ id: 1, body: "hello", ts: new Date().toISOString() });
// transaction — the engine runs it for real, COMMIT blocks until durable
await db.transaction(async (tx) => {
await tx.update(notes).set({ body: "edited" }).where(eq(notes.id, 1));
});Breakdown: Drizzle compiles these to parameterized SQL within the supported subset. Keep an eye on advanced constructs (window functions, exotic joins) — if a built query exceeds the surface, the engine returns a clean ENGINE_ERR_SQL rather than mis-running it; check it against the SQL reference.
The caveat: schema management
Author DDL yourself; be careful with drizzle-kit
Drizzle's runtime query builder maps cleanly onto the engine. But drizzle-kit introspect / push leans on full Postgres catalog behaviour that exceeds the current surface. Prefer creating schema with plain CREATE TABLE you control — via a migration you author, or db.execute(sql\`...\`) — and validate generated DDL against the SQL reference. Track surface growth on the roadmap (Phase 6).
import { sql } from "drizzle-orm";
await db.execute(sql`CREATE TABLE IF NOT EXISTS notes (
id INTEGER PRIMARY KEY, body TEXT NOT NULL, ts TEXT NOT NULL
)`);