zanith

Query System

Zanith provides two query APIs: a simple Model API for CRUD operations, and a Relational Query Builder for complex joins, aggregates, and projections.

Model API (CRUD)

Every model gets these methods automatically. All are fully typed.

findMany

TS
const users = await db.user.findMany({
  where: { role: 'ADMIN' },
  orderBy: { createdAt: 'desc' },
  take: 10,
  skip: 0,
});
// users: Array<{ id: string, email: string, name: string | null, ... }>

findFirst / findUnique

TS
const user = await db.user.findFirst({
  where: { email: { contains: 'admin' } },
});
// user: { id: string, email: string, ... } | null
 
const exact = await db.user.findUnique({
  where: { id: '123' },
});

create

TS
const user = await db.user.create({
  data: { email: '[email protected]', name: 'Alice', role: 'ADMIN' },
});
// Returns the created record with all fields

update

TS
const user = await db.user.update({
  where: { id: '123' },
  data: { name: 'Alice Updated' },
});
// Returns the updated record

delete

TS
const user = await db.user.delete({
  where: { id: '123' },
});
// Returns the deleted record

count

TS
const total = await db.user.count({
  where: { role: 'ADMIN' },
});
// total: number

Where filters

The where input supports direct values, operator objects, and logical combinators.

TS
// Direct value (equals)
{ email: '[email protected]' }
 
// Operator object
{ age: { gt: 18 } }
{ age: { gte: 18, lt: 65 } }
{ email: { contains: 'example' } }
{ email: { startsWith: 'admin' } }
{ name: { not: null } }
{ id: { in: ['a', 'b', 'c'] } }
 
// Null checks
{ name: null }                  // IS NULL
{ name: { equals: null } }     // IS NULL
{ name: { not: null } }        // IS NOT NULL
 
// Logical combinators
{ AND: [{ age: { gte: 18 } }, { age: { lt: 65 } }] }
{ OR: [{ role: 'ADMIN' }, { role: 'MODERATOR' }] }
{ NOT: { role: 'USER' } }
OperatorSQLTypes
equals= $1All
not!= $1All
gt, gte, lt, lte>, >=, <, <=number, string, Date
containsILIKE '%value%'string
startsWithILIKE 'value%'string
endsWithILIKE '%value'string
inIN ($1, $2, ...)All
notInNOT IN (...)All

Relational queries

The relational query builder constructs JOINs from your schema relations automatically. No manual SQL, no string-based joins.

TSOne-hop join
const posts = await db.post.query()
  .with({ author: true })                        // LEFT JOIN users
  .select(({ post, author }) => ({
    title:       post.title,
    authorEmail: author.email,
    authorRole:  author.role,
  }))
  .where(({ post, author }) =>
    and(post.published.eq(true), author.role.eq('ADMIN'))
  )
  .orderBy(({ post }) => post.createdAt.desc())
  .limit(20)
  .execute();

Generated SQL:

TXTSQL output
SELECT "posts"."title", "author"."email" AS "authorEmail", "author"."role" AS "authorRole"
FROM "posts" AS "posts"
LEFT JOIN "users" AS "author"
  ON "posts"."author_id" = "author"."id"
WHERE ("posts"."published" = TRUE AND "author"."role" = $1)
ORDER BY "posts"."created_at" DESC
LIMIT $2

Multi-hop traversal

TS
// Contract → Organization → Owner (2 hops)
const contracts = await db.contract.query()
  .with({
    organization: {       // first hop
      owner: true,        // second hop
    },
  })
  .select(({ contract, organization, owner }) => ({
    title:      contract.title,
    orgName:    organization.name,
    ownerEmail: owner.email,
  }))
  .execute();

JOIN types

TS
.with({ author: true })       // LEFT JOIN (default)
.with({ author: 'inner' })  // INNER JOIN
.with({ author: 'left' })   // explicit LEFT JOIN

Aggregates

TS
import { count, sum, avg, max } from 'zanith';
 
const stats = await db.post.query()
  .with({ author: true })
  .select(({ post, author }) => ({
    authorEmail: author.email,
    postCount:   count(),
    totalViews:  sum(post.viewCount),
    avgViews:    avg(post.viewCount),
    bestPost:    max(post.viewCount),
  }))
  .groupBy(({ post, author }) => [post.authorId, author.email])
  .execute();

Insert & upsert

TS
// Single insert
await db.user.insert({ email: '[email protected]', name: 'Alice' }).execute();
 
// Bulk insert
await db.user.insert([
  { email: '[email protected]', name: 'A' },
  { email: '[email protected]', name: 'B' },
]).executeMany();
 
// Upsert (ON CONFLICT)
await db.user.insert({ email: '[email protected]', name: 'Alice' })
  .onConflict({
    columns: ['email'],
    action: 'update',
    set: { name: 'Alice Updated' },
  })
  .execute();
 
// Shaped RETURNING
await db.user.insert({ email: '[email protected]' })
  .returning(['id', 'email'])
  .execute();

Window functions

TS
import { rowNumber, rank, sumOver } from 'zanith';
 
const ranked = await db.post.query()
  .select(({ post }) => ({
    title: post.title,
    viewRank: rank().orderBy(post.viewCount.desc()),
    runningViews: sumOver(post.viewCount)
      .partitionBy(post.authorId)
      .orderBy(post.createdAt.asc()),
  }))
  .execute();

Subqueries

TS
import { exists } from 'zanith';
 
// WHERE EXISTS
const usersWithPosts = await db.user.query()
  .where(({ user }) =>
    exists(
      db.post.query().where(({ post }) => post.authorId.eq(user.id))
    )
  )
  .execute();
 
// FROM subquery
const results = await db.post.query()
  .fromSubquery('SELECT * FROM posts WHERE published = true', [], 'pub')
  .where(({ post }) => post.title.contains('guide'))
  .execute();

PostgreSQL-native features

DISTINCT ON

TS
// Latest post per author
const latest = await db.post.query()
  .with({ author: true })
  .distinctOn(({ post }) => [post.authorId])
  .select(({ post, author }) => ({
    authorEmail: author.email,
    latestTitle: post.title,
  }))
  .orderBy(({ post }) => [post.authorId.asc(), post.createdAt.desc()])
  .execute();

CTEs (WITH clause)

TS
const results = await db.post.query()
  .withCTE('recent', "SELECT * FROM posts WHERE created_at > NOW() - INTERVAL '7 days'")
  .fromCTE('recent')
  .where(({ post }) => post.published.eq(true))
  .execute();

JSONB

TS
// Text extraction: field->>'key'
.where(({ post }) => post.metadata.jsonText('type').eq('article'))
 
// Containment: field @> '{"key": value}'
.where(({ post }) => post.metadata.jsonContains({ featured: true }))
 
// Key existence: field ? 'key'
.where(({ post }) => post.metadata.jsonHasKey('tags'))

Transactions

TS
await db.transaction(async (tx) => {
  const user = await tx.user.create({
    data: { email: '[email protected]', name: 'New User' },
  });
 
  await tx.post.create({
    data: { title: 'First Post', authorId: user.id },
  });
 
  // If anything throws, the entire transaction rolls back
});

Raw SQL

TS
// Tagged template — values are parameterized automatically
const result = await db.raw<{ count: number }>`
  SELECT COUNT(*) as count FROM users WHERE created_at > ${date}
`;
// result: Array<{ count: number }>