Examples · 01 — The schema this page uses

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

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

ZANITHschema.zanith

28 lines


1model User {
2  id        Int      @id @default(autoincrement())
3  email     String   @unique
4  name      String?
5  role      Role     @default(USER)
6  createdAt DateTime @default(now())
7  posts     Post[]
8}
9 
10model Post {
11  id        Int      @id @default(autoincrement())
12  title     String
13  body      String
14  published Boolean  @default(false)
15  authorId  Int
16  author    User     @relation(fields: [authorId], references: [id])
17  comments  Comment[]
18  createdAt DateTime @default(now())
19}
20 
21model Comment {
22  id      Int    @id @default(autoincrement())
23  body    String
24  postId  Int
25  post    Post   @relation(fields: [postId], references: [id])
26}
27 
28enum Role { USER ADMIN EDITOR }

referenced throughout this pageready

Schema graph · sample

3 models · 1 enum · 2 relations

User
1:N → Post
6 fields
Post
N:1 → User · 1:N → Comment
8 fields
Comment
N:1 → Post
4 fields
Role
USER · ADMIN · EDITOR
3 variants

parsed in <1msgraph ready

First example · canonical findMany

Match recent users by email, paginate by ten.

TSexample.ts

5 lines


1const users = await db.user.findMany({
2  where: { email: { contains: '@example.com' } },
3  orderBy: { createdAt: 'desc' },
4  take: 10,
5});

compiles to~2.4µs

SQLcompiled.sql

6 lines


1SELECT id, email, name, role, created_at
2FROM users
3WHERE email ILIKE $1
4ORDER BY created_at DESC
5LIMIT 10;
6-- $1 = '%@example.com%'

02 — Filterswhere clauses

Every filter shape, parameterized.

User input never reaches the SQL string. Comparison, string matching, IN lists, AND/OR/NOT — all bound as parameters by the driver, never interpolated.

Operator reference · 14 entriestype-checked against the schema graph · all parameterized

operatorfilter shape (TypeScript)compiled SQLnote

comparison· 4 operatorsscalar equality + ordering

equals·

ts shape
{ field: T }

compiled sql
field = $1

note
shorthand for `{ equals: T }`

not·

ts shape
{ not: T }

compiled sql
field <> $1

note
negates equality; combine for inequality

gt · gte·

ts shape
{ gt: T } · { gte: T }

compiled sql
field > $1 · field >= $1

note
numbers, dates, BigInt

lt · lte·

ts shape
{ lt: T } · { lte: T }

compiled sql
field < $1 · field <= $1

note
numbers, dates, BigInt

list· 2 operatorsmembership against an array

in·

ts shape
{ in: T[] }

compiled sql
field IN ($1, $2, …)

note
each value is its own bound parameter

notIn·

ts shape
{ notIn: T[] }

compiled sql
field NOT IN ($1, $2, …)

note
empty array compiles to `FALSE`

string· 3 operatorssubstring search · ILIKE

contains·

ts shape
{ contains: string }

compiled sql
field ILIKE $1

note
param bound as `'%X%'` · case-insensitive

startsWith·

ts shape
{ startsWith: string }

compiled sql
field ILIKE $1

note
param bound as `'X%'`

endsWith·

ts shape
{ endsWith: string }

compiled sql
field ILIKE $1

note
param bound as `'%X'`

logic· 3 operatorsboolean composition

AND·

ts shape
{ AND: WhereInput[] }

compiled sql
(a AND b AND c)

note
implicit when multiple keys at the same level

OR·

ts shape
{ OR: WhereInput[] }

compiled sql
(a OR b OR c)

note
explicit; nests with AND

NOT·

ts shape
{ NOT: WhereInput }

compiled sql
NOT (…)

note
negates the entire subtree

nullable· 2 operatorsnull-aware predicates

null·

ts shape
{ field: null } · { equals: null }

compiled sql
field IS NULL

note
shorthand allowed for the bare value

not null·

ts shape
{ not: null }

compiled sql
field IS NOT NULL

note
auto-promoted from `<> NULL`

5 groups · 14 operators · all parameterizedsource · engine/src/expression

Example · 01

Comparison

gt, gte, lt, lte, equals, not

TSquery.ts

6 lines


db.user.findMany({
  where: {
    createdAt: { gte: new Date('2026-01-01') },
    role: { not: 'USER' },
  },
});

SQLcompiled.sql

4 lines


SELECT id, email, name, role, created_at
FROM users
WHERE created_at >= $1
  AND role <> $2;

Example · 02

String matching

contains, startsWith, endsWith — case-insensitive

TSquery.ts

6 lines


db.user.findMany({
  where: {
    email: { startsWith: 'admin' },
    name: { contains: 'corp', mode: 'insensitive' },
  },
});

SQLcompiled.sql

6 lines


SELECT id, email, name, role, created_at
FROM users
WHERE email ILIKE $1
  AND name ILIKE $2;
-- $1 = 'admin%'
-- $2 = '%corp%'

Example · 03

AND / OR / NOT, IN list

explicit logic operators, parameterized lists

TSquery.ts

11 lines


db.user.findMany({
  where: {
    OR: [
      { role: { in: ['ADMIN', 'EDITOR'] } },
      { AND: [
        { name: { not: null } },
        { email: { contains: '@fybyte' } },
      ] },
    ],
  },
});

SQLcompiled.sql

6 lines


SELECT id, email, name, role, created_at
FROM users
WHERE (
  role IN ($1, $2)
  OR (name IS NOT NULL AND email ILIKE $3)
);

03 — RelationsJOIN · automatic

Joins that compile to a single statement. No N+1.

include does what you want it to: produces one SQL query with the necessary LEFT JOINs. Filters on related models flatten into EXISTS subqueries. The compiler picks the cheaper plan; the engine doesn't roundtrip.

Example · 01

Include — eager fetch

single SQL with LEFT JOINs, no N+1

TSquery.ts

4 lines


db.post.findMany({
  where: { published: true },
  include: { author: true, comments: true },
});

SQLcompiled.sql

8 lines


SELECT
  p.id, p.title, p.body, p.published, p.created_at,
  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 p.published = $1;

Example · 02

Filter through a relation

where on the joined model · automatic JOIN

TSquery.ts

6 lines


db.post.findMany({
  where: {
    author: { role: 'ADMIN' },
    comments: { some: { body: { contains: 'spam' } } },
  },
});

SQLcompiled.sql

9 lines


SELECT p.id, p.title, p.published, p.created_at
FROM posts p
INNER JOIN users u ON u.id = p.author_id
WHERE u.role = $1
  AND EXISTS (
    SELECT 1 FROM comments c
    WHERE c.post_id = p.id
      AND c.body ILIKE $2
  );

Example · 03

Multi-hop · 3 tables

User → Post → Comment in one statement

TSquery.ts

9 lines


db.user.findMany({
  where: { role: 'EDITOR' },
  include: {
    posts: {
      where: { published: true },
      include: { comments: true },
    },
  },
});

SQLcompiled.sql

8 lines


SELECT
  u.id, u.email, u.name, u.role,
  p.id AS post_id, p.title,
  c.id AS comment_id, c.body
FROM users u
LEFT JOIN posts    p ON p.author_id = u.id AND p.published = $1
LEFT JOIN comments c ON c.post_id = p.id
WHERE u.role = $2;

04 — Aggregates & windowsGROUP BY · OVER

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

Aggregate calls compile to standard SQL: GROUP BY, HAVING, window functions in subqueries. No client-side aggregation. No ORM running a per-row callback for what should have been a single statement.

Example · 01

count · groupBy

posts per author

TSquery.ts

5 lines


db.post.groupBy({
  by: ['authorId'],
  _count: { _all: true },
  having: { _count: { _all: { gt: 5 } } },
});

SQLcompiled.sql

4 lines


SELECT author_id, COUNT(*) AS count_all
FROM posts
GROUP BY author_id
HAVING COUNT(*) > $1;

Example · 02

sum · avg with where

engagement metrics on a slice

TSquery.ts

6 lines


db.comment.aggregate({
  where: { post: { published: true } },
  _count: { _all: true },
  _avg: { score: true },
  _max: { createdAt: true },
});

SQLcompiled.sql

7 lines


SELECT
  COUNT(*)        AS count_all,
  AVG(c.score)    AS avg_score,
  MAX(c.created_at) AS max_created_at
FROM comments c
INNER JOIN posts p ON p.id = c.post_id
WHERE p.published = $1;

Example · 03

Window · ROW_NUMBER

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

Every write uses RETURNING so the inserted, upserted, or updated row comes back typed. db.transaction wraps a function in BEGIN/COMMIT; any throw inside it triggers ROLLBACK.

Example · 01

Single insert · RETURNING *

the inserted row comes back as a typed result

TSquery.ts

7 lines


const user = await db.user.create({
  data: {
    email: '[email protected]',
    name: 'Alice',
    role: 'ADMIN',
  },
});

SQLcompiled.sql

3 lines


INSERT INTO users (email, name, role)
VALUES ($1, $2, $3)
RETURNING id, email, name, role, created_at;

Example · 02

Upsert · ON CONFLICT DO UPDATE

deduplicated by the unique field

TSquery.ts

5 lines


await db.user.upsert({
  where: { email: '[email protected]' },
  create: { email: '[email protected]', name: 'Alice' },
  update: { name: 'Alice Updated' },
});

SQLcompiled.sql

5 lines


INSERT INTO users (email, name)
VALUES ($1, $2)
ON CONFLICT (email) DO UPDATE
  SET name = EXCLUDED.name
RETURNING id, email, name, role, created_at;

Example · 03

Transaction · all or nothing

rolled back on any throw

TSquery.ts

9 lines


await db.transaction(async (tx) => {
  const user = await tx.user.create({
    data: { email: '[email protected]', name: 'Bob' },
  });
  await tx.post.create({
    data: { title: 'First', body: '…', authorId: user.id },
  });
  // throws → both rows roll back
});

SQLcompiled.sql

7 lines


BEGIN;
  INSERT INTO users (email, name) VALUES ($1, $2)
    RETURNING id, email, name, role, created_at;
  INSERT INTO posts (title, body, author_id) VALUES ($3, $4, $5)
    RETURNING id, title, body, published, author_id, created_at;
COMMIT;
-- on throw: ROLLBACK;

06 — Raw SQL escape hatchtagged template · safe

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

Sometimes the query is too database-specific to express through the model API. The db.raw`…` tagged template handles that — and refuses to interpolate values into the SQL string. Every ${x} becomes a bound parameter.

TSquery.ts

13 lines


1const someDate = new Date('2026-01-01');
2const minScore = 0.5;
3 
4const result = await db.raw<{
5  count: number;
6  avg_score: number;
7}>`
8  SELECT COUNT(*) AS count,
9         AVG(score) AS avg_score
10  FROM comments
11  WHERE created_at > ${someDate}
12    AND score > ${minScore}
13`;

tagged template literalready

SQLexecuted.sql

8 lines


1SELECT COUNT(*) AS count,
2       AVG(score) AS avg_score
3FROM comments
4WHERE created_at > $1
5  AND score > $2;
6 
7-- $1 = '2026-01-01T00:00:00.000Z'
8-- $2 = 0.5

parameters bound · $1 · $2ready

The safety contract · what raw cannot do

· You cannot string-concatenate values into the SQL template. The tag function rejects non-template usage at the type level.
· Identifiers (table names, column names) must come from a fixed allowlist — they cannot be interpolated as user input.
· The result type is unknown unless you specify a generic. There's no automatic schema-graph inference for raw queries.

by construction

07 — Where to go from here

door · 01

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

3 models · 1 enum · 2 relations

Match recent users by email, paginate by ten.

Every filter shape, parameterized.

Comparison

String matching

AND / OR / NOT, IN list

Joins that compile to a single statement. No N+1.

Include — eager fetch

Filter through a relation

Multi-hop · 3 tables

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

count · groupBy

sum · avg with where

Window · ROW_NUMBER

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

Single insert · RETURNING *

Upsert · ON CONFLICT DO UPDATE

Transaction · all or nothing

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

Read the docs

How it works

See the receipts