Part 4 · 1 chapters · ~8 min

Drizzle

The SQL-first thesis, schemas in TypeScript with types inferred without code generation, the query builder's compile-time SQL construction, the relational query API and what SQL it emits, drizzle-kit migrations and introspection, prepared statements and edge runtimes, and where Drizzle's types break down.

SQL-first in TypeScript

code

// schema.ts: the schema is TypeScript; types are inferred, no generate step
export const accounts = pgTable('accounts', {
  id: text('id').primaryKey(), ownerId: text('owner_id').notNull(),
  currency: varchar('currency', { length: 3 }).notNull(), balanceKobo: bigint('balance_kobo', { mode: 'bigint' }).notNull().default(0n),
}, t => [index('accounts_owner_idx').on(t.ownerId)]);

// core builder: reads like SQL, one query
const rows = await db.select({ id: accounts.id, bal: accounts.balanceKobo }).from(accounts)
  .where(and(eq(accounts.ownerId, userId), gt(accounts.balanceKobo, 0n))).orderBy(desc(accounts.balanceKobo));

// row locking and atomic updates are first-class
await db.transaction(async tx => {
  await tx.select().from(accounts).where(inArray(accounts.id, [a, b])).orderBy(accounts.id).for('update');
  await tx.update(accounts).set({ balanceKobo: sql`${accounts.balanceKobo} - ${amt}` }).where(eq(accounts.id, a));
});

// prepared statements: parsed once, reused (also suits edge runtimes with HTTP drivers)
const byOwner = db.select().from(accounts).where(eq(accounts.ownerId, sql.placeholder('owner'))).prepare('by_owner');
await byOwner.execute({ owner: userId });

Trade-offs: Drizzle produces predictable SQL and fits edge runtimes, with no engine and small bundles. Its relational API (db.query.accounts.findMany({ with: { postings: true } })) builds a single query with JSON aggregation; very complex dynamic queries and some type inference cases (deep conditional selects) can produce slow type-checking or confusing errors.