Examples · 01 — The schema this page uses

Real schemas. Real SQL. No magic on the wire.

Every example below uses the same .zanith schema. A typed call on the left, the parameterized SQL Zanith generates on the right. Read both — the gap between them is the engine.

First query docs Queries overview

compile2.4µs

query.ts

const users = await db.user.findMany({

where: { email: { contains: '@example.com' } },

orderBy: { createdAt: 'desc' },

take: 10,

});

compiled.sql

SELECT id, email, name, role, created_at

FROM users

WHERE email ILIKE $1

ORDER BY created_at DESC

LIMIT 10;

-- $1 = '%@example.com%'

parameterized ILIKE · ORDER BY · LIMIT

3 models · 1 enum · 2 relations.zanith DSLcompile 2.4µsparameterized · inspectable

02 — Two filter APIsobject syntax · field refs

Same SQL. Two ways to write it.

Object syntax in CRUD methods and field-reference syntax in the relational query builder — both compile to identical parameterized SQL.

Read filters & where →

compile2.4µs

query.ts

await db.user.findMany({

where: {

createdAt: { gte: new Date('2026-01-01') },

role: { not: 'USER' },

});

compiled.sql

SELECT id, email, name, role, created_at

FROM users

WHERE created_at >= $1

AND role <> $2;

CRUD methods — findMany, create, etc.

Shared .zanith schema+ expand

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  createdAt DateTime @default(now())
  posts     Post[]
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  body      String
  published Boolean  @default(false)
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
  comments  Comment[]
  createdAt DateTime @default(now())
}

model Comment {
  id      Int    @id @default(autoincrement())
  body    String
  postId  Int
  post    Post   @relation(fields: [postId], references: [id])
}

enum Role { USER ADMIN EDITOR }

.zanith DSL · Models & fields

03 — Filters14 operators

Every operator compiles. Nothing interpolated.

Comparison, string match, lists, boolean logic, null checks — each becomes a bound parameter. Sourced from engine/src/expression.

Read filters & where →

compile—

query.ts

db.user.findMany({

where: {

createdAt: { gte: new Date('2026-01-01') },

role: { not: 'USER' },

});

compiled.sql

SELECT id, email, name, role, created_at

FROM users

WHERE created_at >= $1

AND role <> $2;

gt, gte, lt, lte, equals, not

Operator reference · 14 total

op	shape	sql
equals	{ field: T }	field = $1
not	{ not: T }	field <> $1
gt · gte	{ gt: T } · { gte: T }	field > $1 · field >= $1
lt · lte	{ lt: T } · { lte: T }	field < $1 · field <= $1
in	{ in: T[] }	field IN ($1, $2, …)
notIn	{ notIn: T[] }	field NOT IN ($1, …)
contains	{ contains: string }	field ILIKE $1
startsWith	{ startsWith: string }	field ILIKE $1
endsWith	{ endsWith: string }	field ILIKE $1
AND	{ AND: WhereInput[] }	(a AND b)
OR	{ OR: WhereInput[] }	(a OR b)
NOT	{ NOT: WhereInput }	NOT (…)
null	{ field: null }	field IS NULL
not null	{ not: null }	field IS NOT NULL

04 — RelationsJOIN · automatic

Joins that compile to a single statement.

Declare what you want — Zanith builds the JOINs from your schema. Filters on related models flatten into EXISTS subqueries.

Read relations →Schema relations →

compile17.2µs

query.ts

const posts = await db.post.findMany({

where: { author: { role: 'ADMIN' } },

include: { author: true, comments: true },

});

compiled.sql

SELECT

p.id, p.title, p.body, p.published,

u.id AS author_id, u.email, u.name, u.role,

c.id AS comment_id, c.body AS comment_body

FROM posts p

LEFT JOIN users u ON u.id = p.author_id

LEFT JOIN comments c ON c.post_id = p.id

WHERE u.role = $1;

single statement · no N+1

include

one query, automatic JOINs

nested where

EXISTS — no N+1

.with()

query-builder projections

05 — Aggregates & windowsGROUP BY · OVER

Counting and ranking, first-class — not bolted on.

groupBy, having, and window functions compile to standard SQL — no client-side aggregation.

Read aggregates →Window functions →

compile5.5µs

query.ts

db.post.groupBy({

by: ['authorId'],

_count: { _all: true },

having: { _count: { _all: { gt: 5 } } },

});

compiled.sql

SELECT author_id, COUNT(*) AS count_all

FROM posts

GROUP BY author_id

HAVING COUNT(*) > $1;

posts per author

groupBy + having

posts per author above threshold

aggregate + filter

join + COUNT/AVG in one pass

ROW_NUMBER

top N per group via subquery

06 — WritesINSERT · UPSERT · BEGIN

Writes return what they wrote. Transactions roll back on throw.

Every write uses RETURNING so the row comes back typed. Bulk inserts batch into a single roundtrip; transactions wrap BEGIN/COMMIT atomically.

Read insert & upsert →Transactions →

compile2.8µs

query.ts

const user = await db.user.create({

data: {

email: '[email protected]',

name: 'Alice',

role: 'ADMIN',

});

compiled.sql

INSERT INTO users (email, name, role)

VALUES ($1, $2, $3)

RETURNING id, email, name, role, created_at;

typed result row

create

INSERT + RETURNING *

compile 2.1µs

upsert

ON CONFLICT DO UPDATE

compile 3.4µs

createMany

bulk VALUES list

compile 7.6µs

transaction

BEGIN · throw → ROLLBACK

07 — Raw SQL escape hatchdb.raw · safe

When you need raw SQL, the values are still bound.

db.raw`…` tagged templates refuse to interpolate values — every ${x} becomes a bound parameter.

Read raw SQL →

compile—

query.ts

const someDate = new Date('2026-01-01');

const minScore = 0.5;

const result = await db.raw<{

count: number;

avg_score: number;

}>`

SELECT COUNT(*) AS count,

AVG(score) AS avg_score

FROM comments

WHERE created_at > ${someDate}

AND score > ${minScore}

compiled.sql

SELECT COUNT(*) AS count,

AVG(score) AS avg_score

FROM comments

WHERE created_at > $1

AND score > $2;

-- $1 = '2026-01-01T00:00:00.000Z'

-- $2 = 0.5

values bound · never interpolated

Safety contract

· Values cannot be string-concatenated into the template — every ${x} becomes a bound parameter.
· Identifiers (table/column names) must come from a fixed allowlist, not user input.
· Result type is unknown unless you specify a generic — no schema-graph inference for raw queries.

08 — Compile cost

Microseconds on the engine. Network latency is the bill.

Figures below are the engine's compile overhead — not total query time. Sourced from engine/test/benchmark/execution.test.ts (VOICE.md §7).

Simple filter findMany

2.4µs

execution.test.ts

Join + projection + where

17.2µs

execution.test.ts

Single-row insert

2.8µs

execution.test.ts

Upsert ON CONFLICT

2.7µs

execution.test.ts

Bulk insert · 10 rows

7.6µs

execution.test.ts

GROUP BY + COUNT + SUM

5.5µs

execution.test.ts

Full benchmark receipts → /proof

09 — Go deeper

elsewhere

Queries overview

The shape of a typed query and how it compiles.

How it works

Four layers from parser to adapter.

Proof suite

Benchmark receipts for every compile cost above.

Real schemas. Real SQL. No magic on the wire.

Same SQL. Two ways to write it.

Every operator compiles. Nothing interpolated.

Joins that compile to a single statement.

Counting and ranking, first-class — not bolted on.

Writes return what they wrote. Transactions roll back on throw.

When you need raw SQL, the values are still bound.

Microseconds on the engine. Network latency is the bill.

Every operator documented. Every option typed.

Filters & where

Relations

Aggregates

Insert & upsert

Transactions

Raw SQL

Queries overview

How it works

Proof suite