zanith

Relational Queries

The core differentiator — JOINs are built from your schema relations automatically. You declare what data you want, Zanith figures out the SQL.

Why this matters

Getting data from two related tables usually means writing manual SQL JOINs or making multiple queries and stitching results. With Zanith, you say "include the author" and the engine builds the correct LEFT JOIN from your schema — including the right table name, alias, and ON clause.

.with({ author: true })
Schema lookup

finds relation

LEFT JOIN

auto-generated

Typed result

Basic join

Use .with() to include a related model. Zanith looks up the relation definition, finds the foreign key, and builds the JOIN.

TSyour code
const posts = await db.post.query()
  .with({ author: true })              // include the author relation
  .select(({ post, author }) => ({     // pick specific fields
    title: post.title,
    authorEmail: author.email,
    authorRole: author.role,
  }))
  .where(({ post }) => post.published.eq(true))
  .orderBy(({ post }) => post.createdAt.desc())
  .limit(20)
  .execute();

What Zanith generates

TSgenerated SQL
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
ORDER BY "posts"."created_at" DESC
LIMIT $1

Notice: all identifiers are quoted, all values are parameterized ($1), and the JOIN ON clause uses the correct snake_case column names from your schema.

Multi-hop traversal

Need data from three tables? Nest the .with() calls. Zanith chains the JOINs through the correct parent — not always the root table.

TS2-hop: Contract → Organization → Owner
const contracts = await db.contract.query()
  .with({
    organization: {       // first JOIN: contracts → organizations
      owner: true,        // second JOIN: organizations → users
    },
  })
  .select(({ contract, organization, owner }) => ({
    title: contract.title,
    orgName: organization.name,
    ownerEmail: owner.email,
  }))
  .where(({ contract, owner }) =>
    and(
      contract.value.gte(10000),
      owner.email.endsWith('@corp.com')
    )
  )
  .execute();

The second JOIN chains through organization, not contract:

TSgenerated SQL
LEFT JOIN "organizations" AS "organization"
  ON "contracts"."organization_id" = "organization"."id"
LEFT JOIN "users" AS "owner"
  ON "organization"."owner_id" = "owner"."id"

JOIN types

By default, .with()uses LEFT JOIN — which means the main record is returned even if the related record doesn't exist (joined fields will be null). Use 'inner' for INNER JOIN when you only want records with matching relations.

TS
.with({ author: true })       // LEFT JOIN (default)
.with({ author: 'inner' })  // INNER JOIN — excludes posts without an author
.with({ author: 'left' })   // explicit LEFT JOIN

Cross-model filtering

You can filter on fields from ANY joined model in the .where()clause — not just the root table. This is one of Zanith's most powerful features.

TS
// Find published posts by admin authors
.where(({ post, author }) =>
  and(
    post.published.eq(true),       // filter on root model
    author.role.eq('ADMIN'),       // filter on joined model
  )
)
 
// Complex: posts with "urgent" in title OR published by admins
.where(({ post, author }) =>
  or(
    post.title.contains('urgent'),
    and(
      post.published.eq(true),
      author.role.in(['ADMIN', 'MODERATOR'])
    )
  )
)

Explicit projection vs SELECT *

Without .select(), Zanith returns all fields from the root model (SELECT posts.*). With .select(), you pick exactly which fields you want — including fields from joined models with custom aliases.

Without .select()

  • ·Returns all root model fields
  • ·No joined model fields
  • ·SELECT posts.*

With .select()

  • ·Returns only chosen fields
  • ·Can include joined model fields
  • ·Aliased projections (authorEmail)

Next steps