Database

One API for SQLite, Postgres, MySQL. Queries, relations, transactions.

Three SQL dialects supported, one API. SQLite ships with Bun (no install); Postgres and MySQL are opt-in via hopak use postgres / hopak use mysql. Drizzle under the hood, but you don’t write Drizzle — you write models.

The schema is created on hopak dev’s first boot via CREATE TABLE IF NOT EXISTS for every model. Run hopak sync to do the same thing explicitly without starting the server (useful in CI or on a fresh Postgres / MySQL database). This is idempotent naive replay — the same command is safe to run repeatedly, but it does not handle schema changes (ALTER TABLE / RENAME / DROP). For schema evolution during prototyping, drop the table and re-sync.

Dialect matrix

Querying inside a handler

defineRoute({
  handler: async (ctx) => {
    const post = await ctx.db?.model('post').findOne(1);
    return post;
  },
});

ctx.db is undefined when the server starts without models. In a normal app it’s always set — ctx.db!.model(...) narrows safely, or if (!ctx.db) throw new InternalError(...) for an explicit check.

Full ModelClient surface

Every db.model(name) returns a typed client with this surface:

// Read
client.findMany(options?);                           // → TRow[]
client.findMany({ select: ['id', 'title'] });        // → Pick<TRow, ...>[]
client.findOne(id, { lock?: 'forUpdate' | 'forShare' });  // → TRow | null
client.findOrFail(id, options?);                     // → TRow (throws NotFound)
client.count({ where? });                            // → number

// Write
client.create(data);                                 // → TRow
client.update(id, data);                             // → TRow (throws NotFound)
client.delete(id);                                   // → boolean
client.upsert({ where, create, update });            // → TRow

// Batch
client.createMany([row, row, row]);                  // → { count }
client.updateMany({ where, data });                  // → { count }
client.deleteMany({ where });                        // → { count }

// Aggregate
client.aggregate({ sum, avg, min, max, count });     // → AggregateResult
client.aggregate({ groupBy: ['col'], ... });         // → Array<AggregateResult + group keys>

findMany options reference

{
  where?: WhereClause,       // filter — see recipe 10
  include?: IncludeClause,   // eager-load relations — see recipe 11
  select?: ['id', 'title'],  // projection — see recipe 16
  distinct?: true | ['col'], // dedupe — see recipe 16
  orderBy?: [{ field, direction }],
  limit?: number,
  offset?: number,
  cursor?: { id: 42 },       // keyset pagination — see recipe 14
  lock?: 'forUpdate' | 'forShare',  // row locks — see recipe 15
}

Transactions

await ctx.db.transaction(async (tx) => {
  const user = await tx.model('user').create({...});
  await tx.model('post').create({ author: user.id, ... });
  // Throwing here (or from any tx query) rolls everything back.
});

See Recipe 15 for FOR UPDATE locking patterns.

Raw SQL with db.sql

For the ~5% of queries the typed client doesn’t cover — window functions, recursive CTEs, vendor-specific JSON / FTS, EXPLAIN ANALYZE, DDL — reach for db.sql, a tagged template that parameterises interpolations automatically:

// Typed aggregate with group-by on a derived column
const daily = await ctx.db!.sql<{ day: string; n: number }>`
  SELECT DATE(created_at) AS day, COUNT(*) AS n
  FROM posts
  WHERE published = true
  GROUP BY day
  ORDER BY day DESC
  LIMIT 30
`;

// Point lookup — ${id} becomes a bound parameter, never inlined into SQL
const post = await ctx.db!.sql<Post>`SELECT * FROM posts WHERE id = ${id}`;

// DDL + writes work too; writes return an empty array
await ctx.db!.sql`ALTER TABLE posts ADD COLUMN views INTEGER NOT NULL DEFAULT 0`;
await ctx.db!.sql`UPDATE posts SET published = ${true} WHERE author_id = ${userId}`;

Interpolations become driver-native placeholders (? on SQLite and MySQL, $N on Postgres). Values are never concatenated into the SQL text, so injection through ${...} is fundamentally impossible — this is safe even when one of the interpolated values comes from user input.

Table names in raw SQL

Added in 1.0

The physical table is the pluralized model name: model post → table posts, category → categories. Pluralization is suffix-based, so person becomes persons, not people.

Read it off the model instead of guessing — this is also what you want when composing DDL in a migration:

import post from '../models/post';

post.tableName;  // 'posts'

A tagged template can’t interpolate an identifier — sql`SELECT * FROM ${post.tableName}` would bind the name as a value, not a table. Write the table name literally in the template, and use tableName when you’re building statement text by hand (ctx.execute in migrations).

Postgres returns BIGINT as a string

COUNT(*), SUM(...) and other bigint results come back as strings from the Postgres driver in raw SQL — that’s postgres.js preserving precision, not Hopak transforming anything. The typed client (client.count(), client.aggregate()) already normalizes to numbers; in raw SQL, coerce yourself:

const [row] = await ctx.db!.sql<{ n: string }>`SELECT COUNT(*) AS n FROM posts`;
const total = Number(row?.n ?? 0);

Reads return a typed readonly T[]. Writes (INSERT / UPDATE / DELETE / DDL) return an empty array; use the return value only for SELECTs.

db.sql works inside a db.transaction(fn) too — inside the callback, call tx.sql the same way you’d call db.sql outside; the statement binds to the enclosing transaction.

Drizzle escape hatch (db.builder)

For the <1% where even db.sql isn’t enough (custom query-builder composition, Drizzle plugins, advanced features), drop to Drizzle directly:

import { sql } from 'drizzle-orm';

const drizzle = ctx.db!.builder();
const top = await drizzle.execute(sql`
  SELECT author, ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rank
  FROM posts WHERE published = true GROUP BY author LIMIT 10
`);

builder() returns the dialect’s native Drizzle instance — the full Drizzle API is available. SAVEPOINTs and custom isolation levels live here.

Escape hierarchy

model()           ← 90% of queries (typed + safe by default)
  ↓
db.sql`...`       ← raw SQL with types (≈5% escape)
  ↓
db.builder()      ← Drizzle query builder (<1% escape)

Removed in 1.0

Note on db.execute. Earlier releases exposed db.execute(sql, params?) as the raw-SQL write path. It was deprecated in 0.5 and removed from the public Database interface in 1.0 — use db.sql. Inside migrations, ctx.execute remains available for dynamic DDL text that the tagged template deliberately refuses.