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 post
  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 post WHERE id = ${id}`;

// DDL + writes work too; writes return an empty array
await ctx.db!.sql`ALTER TABLE post ADD COLUMN views INTEGER NOT NULL DEFAULT 0`;
await ctx.db!.sql`UPDATE post 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.

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)

Note on db.execute. Earlier releases exposed db.execute(sql, params?) as the raw-SQL write path. It is @deprecated in 0.5 and forwards to db.sql — existing migration files keep compiling. New code should use db.sql (or ctx.sql inside migrations).