Queries - Vertz

The database client provides typed CRUD operations derived from your schema. Every query is type-checked — column names, filter values, and return types are all inferred from the model definition.

Creating the client

import { createDb } from '@vertz/db';

const db = createDb({
url: process.env.DATABASE_URL!,
models: { users: usersModel, tasks: tasksModel },
});

import { createDb } from '@vertz/db';

const db = createDb({
  dialect: 'sqlite',
  path: '.vertz/data/app.db', // or ':memory:'
  models: { users: usersModel, tasks: tasksModel },
  migrations: { autoApply: true },
});

Each model becomes a property on db with typed query methods. The query API is identical across all dialects — only the createDb() options differ.

Read operations

Get a single record

const result = await db.users.get({
  where: { id: userId },
});

if (result.ok) {
  console.log(result.data.name); // typed
}

List with filtering

const result = await db.tasks.list({
  where: { status: 'todo', assigneeId: userId },
  orderBy: { createdAt: 'desc' },
  limit: 20,
});

if (result.ok) {
  console.log(result.data.items); // Task[]
  console.log(result.data.total); // total count
  console.log(result.data.hasNextPage);
}

Filter operators

where: {
  // Equality
  status: 'active',

  // Comparison
  age: { gt: 18, lte: 65 },

  // Set membership
  role: { in: ['admin', 'editor'] },
  status: { notIn: ['deleted'] },

  // String matching
  name: { contains: 'Smith' },
  email: { startsWith: 'admin' },
  email: { endsWith: '@example.com' },

  // Null checks
  deletedAt: { isNull: true },
}

Select specific fields

const result = await db.users.list({
  select: { id: true, name: true, email: true },
});
// result.data.items is { id: string; name: string; email: string }[]

Include relations

Load related data in a single query instead of making separate API calls:

const result = await db.posts.list({
  include: { author: true, comments: true },
});
// result.data.items[0].author — the related user
// result.data.items[0].comments — array of related comments

Narrow included relations to specific fields:

include: {
  author: { select: { id: true, name: true } },
}

Filter, sort, and limit included relations:

include: {
  comments: {
    where: { status: 'approved' },
    orderBy: { createdAt: 'desc' },
    limit: 10,
  },
}

Nest includes up to 3 levels deep:

include: {
  comments: {
    orderBy: { createdAt: 'desc' },
    include: {
      author: { select: { id: true, name: true } },
    },
  },
}
// result.data.items[0].comments[0].author.name

Many-to-many relations work through join tables — define a .through() relation in your model and include it like any other:

// Schema
const postsModel = d.model(postsTable, {
  tags: d.ref.many(() => tagsTable).through(() => postTagsTable, 'postId', 'tagId'),
});

// Query — join table is traversed automatically
const result = await db.posts.list({
  include: { tags: true },
});
// result.data.items[0].tags — Tag[] resolved through the join table

Count

const count = await db.users.count({
  where: { isActive: true },
});

Aggregation

const stats = await db.orders.aggregate({
  where: { status: 'completed' },
  _count: true,
  _sum: { price: true },
  _avg: { amount: true },
});

Group by

const grouped = await db.orders.groupBy({
  by: ['status'],
  _count: true,
  _sum: { total: true },
});

Write operations

Create

const result = await db.users.create({
  data: { email: 'alice@example.com', name: 'Alice' },
});

if (result.ok) {
  console.log(result.data.id); // auto-generated
}

Update

const result = await db.users.update({
  where: { id: userId },
  data: { name: 'Bob' },
});

Update expressions

Use d.increment(), d.decrement(), or d.expr() to perform atomic column operations directly in the database — no read-modify-write cycle needed.

import { d, sql } from '@vertz/db';

// Increment a counter atomically
await db.urls.update({
  where: { slug: 'my-link' },
  data: { clickCount: d.increment(1) },
});
// SQL: UPDATE "urls" SET "click_count" = "click_count" + $1 WHERE ...

// Decrement
await db.inventory.update({
  where: { sku: 'WIDGET-01' },
  data: { stock: d.decrement(5) },
});

// Arbitrary SQL expression
await db.urls.update({
  where: { slug: 'My-Link' },
  data: { slug: d.expr((col) => sql`LOWER(${col})`) },
});
// SQL: UPDATE "urls" SET "slug" = LOWER("slug") WHERE ...

Expressions work in update, updateMany, and the update path of upsert. They compose with the sql tagged template — the col parameter in d.expr() is the column reference, automatically converted to snake_case.

Expressions override autoUpdate() columns. If you pass d.increment() on an autoUpdate() column like updatedAt, your expression is used instead of the automatic NOW().

Upsert

Create if not found, update if exists:

const result = await db.users.upsert({
  where: { email: 'alice@example.com' },
  create: { email: 'alice@example.com', name: 'Alice' },
  update: { name: 'Alice Updated' },
});

Delete

const result = await db.users.delete({
  where: { id: userId },
});

Bulk operations

await db.users.createMany({
  data: [
    { email: 'alice@example.com', name: 'Alice' },
    { email: 'bob@example.com', name: 'Bob' },
  ],
});

await db.users.updateMany({
  where: { isActive: false },
  data: { isActive: true },
});

await db.users.deleteMany({
  where: { deletedAt: { isNull: false } },
});

Transactions

Wrap multiple operations in db.transaction() to make them atomic — either all succeed or all roll back:

const result = await db.transaction(async (tx) => {
  const user = await tx.users.create({
    data: { email: 'alice@example.com', name: 'Alice' },
  });
  if (!user.ok) throw new Error('Failed to create user');

  await tx.tasks.create({
    data: { title: 'Onboarding', assigneeId: user.data.id },
  });

  return user.data;
});

The transaction client (tx) has the same model delegates as db — tx.users.create(), tx.tasks.list(), and tx.query() all work identically. The difference is that all operations share a single database transaction.

How it works

Auto-commit: If the callback returns normally, the transaction commits.
Auto-rollback: If the callback throws, the transaction rolls back and the error propagates to the caller.
Return values: The callback’s return value is returned from db.transaction().

Raw SQL in transactions

You can mix model delegates with raw SQL in the same transaction:

await db.transaction(async (tx) => {
  await tx.users.create({ data: { email: 'bob@example.com', name: 'Bob' } });
  await tx.query(sql`UPDATE counters SET value = value + 1 WHERE name = ${'user_count'}`);
});

Limitations

No nesting: Calling db.transaction() inside a transaction callback throws "Nested transactions are not supported.".
No D1 support: Cloudflare D1 does not support interactive transactions. Use D1Database.batch() for atomic operations on D1.

Error handling

All operations return Result<T, Error> — they never throw. Error types map to specific database conditions:

Error	HTTP equivalent	Cause
`NotFoundError`	404	Record not found
`UniqueConstraintError`	409	Duplicate unique value
`ForeignKeyError`	409	Referenced record doesn’t exist
`NotNullError`	422	Required field missing
`CheckConstraintError`	422	CHECK constraint violated
`ConnectionError`	503	Database unreachable

Handle errors with pattern matching:

const result = await db.users.create({ data: { email: 'alice@example.com', name: 'Alice' } });

if (!result.ok) {
  switch (result.error.code) {
    case 'UNIQUE_VIOLATION':
      console.log('Email already exists');
      break;
    case 'NOT_NULL_VIOLATION':
      console.log('Missing required field');
      break;
    default:
      console.error('Unexpected error:', result.error);
  }
}

Raw SQL

For queries that the builder can’t express, use tagged template SQL:

import { sql } from '@vertz/db';

const result = await db.query(sql`SELECT * FROM users WHERE email = ${email} AND active = ${true}`);

Parameters are automatically escaped — no SQL injection risk.

Prepared statements

All queries to PostgreSQL use prepared statements automatically — no configuration needed. The database driver sends each query with prepare: true, which enables PostgreSQL to cache the query plan and reuse it on subsequent executions of the same query shape. This applies to all query paths: typed client operations (db.users.list()), raw SQL (db.query()), and queries inside transactions. There is no opt-out — prepared statements are always enabled for PostgreSQL connections.

SQLite and Cloudflare D1 handle statement caching internally at the driver level, so this optimization is specific to PostgreSQL.

Database support

All databases use the same import and createDb() API:

Database	Dialect	Connection option	Notes
PostgreSQL	`'postgres'` (default)	`url: string`	Full feature support
SQLite	`'sqlite'`	`path: string`	Development, embedded, and local use
Cloudflare D1	`'sqlite'`	`d1: D1Database`	Edge deployment

import { createDb } from '@vertz/db';

See the overview for setup examples for each dialect.

​Creating the client

​Read operations

​Get a single record

​List with filtering

​Filter operators

​Select specific fields

​Include relations

​Count

​Aggregation

​Group by

​Write operations

​Create

​Update

​Update expressions

​Upsert

​Delete

​Bulk operations

​Transactions

​How it works

​Raw SQL in transactions

​Limitations

​Error handling

​Raw SQL

​Prepared statements

​Database support

Creating the client

Read operations

Get a single record

List with filtering

Filter operators

Select specific fields

Include relations

Count

Aggregation

Group by

Write operations

Create

Update

Update expressions

Upsert

Delete

Bulk operations

Transactions

How it works

Raw SQL in transactions

Limitations

Error handling

Raw SQL

Prepared statements

Database support