Learn / Data

LESSON 56 OF 84

Data Core

Databases with @zudojs/database

Connect the Task API to PostgreSQL through @zudojs/database, with migrations, seeds, a repository, a query builder, pagination, transactions and health checks, all running against real PostgreSQL.

50 min to read and try
You need: The SQL lessons and the ZudoJS core part
You build: A PostgreSQL task store with migrations, seeds, a soft-deleting repository and signed pagination

Test yourself

What @zudojs/database does

In the SQL lessons you wrote every query by hand. That works, but every project ends up writing the same things again: connect and disconnect, run migrations once, turn database errors into HTTP errors, page through results, retry a failed transaction. @zudojs/database does those jobs for you.

It is built on Prisma, a popular database toolkit for TypeScript. Prisma talks to PostgreSQL. ZudoJS sits on top of it and adds:

a client that connects, disconnects and reports its status,
migrations and seeds that run once, even when two servers start at the same moment,
a repository base class with create, find, update, delete, soft delete and pagination,
a query builder, transactions with retries, and health checks.

It supports PostgreSQL only. Install it in your task-api folder, together with PGlite from the databases lesson:

Terminal on your computer

$ npm install @zudojs/database @electric-sql/pglite

added 7 packages, and audited 15 packages in 16s

found 0 vulnerabilities

Your numbers will be different, because your project already has other packages. npm also added @prisma/client: @zudojs/database lists it as a peer dependency, a package it expects your project to provide.

The client and its adapter

ZudoJS never talks to the database directly. It talks to a Prisma client, and it only needs a few methods from it: $connect, $disconnect, $queryRawUnsafe, $executeRawUnsafe and $transaction. The package calls this shape PrismaClientLike. Anything with those methods works. This is the adapter pattern: a small object that makes one thing look like the thing another piece of code expects.

In a production app, the Prisma client comes from Prisma itself. You describe your tables in a schema.prisma file, run npx prisma generate, and connect through a driver adapter, @prisma/adapter-pg:

production-db.tsNode.js only

import { PrismaClient } from "@prisma/client";
import { PrismaPg } from "@prisma/adapter-pg";
import { createDatabaseClient } from "@zudojs/database";

const connectionString = process.env["DATABASE_URL"];
if (!connectionString) {
  throw new Error("DATABASE_URL is not set");
}

const prisma = new PrismaClient({
  adapter: new PrismaPg({
    connectionString,
    max: 10,                        // at most 10 open connections
    idleTimeoutMillis: 30_000,      // close a connection unused for 30 s
    connectionTimeoutMillis: 5_000, // give up waiting for a free one after 5 s
  }),
});

export const client = createDatabaseClient({ prisma });

The client Prisma generates fits PrismaClientLike as it is, so no type cast is needed.

This setup needs a running PostgreSQL server and the Prisma tool chain, which the deployment lesson sets up. For learning, this lesson uses PGlite instead, the real PostgreSQL running inside Node.js. There is no ready-made Prisma adapter for PGlite, so you write a small one. It has two files.

The first turns a Prisma-style where object, such as { done: false, title: { contains: "milk" } }, into SQL. Look at two things. Every value becomes a $1, $2… parameter, never part of the SQL text. And every field name is checked against an allow-list before it is used, because a field name cannot be a parameter:

where.tsNode.js only

const COMPARE: Record<string, string> = {
  equals: "=", not: "<>", lt: "<", lte: "<=", gt: ">", gte: ">=",
};

/* "createdAt" becomes the column "created_at", but only for known fields. */
export function column(field: string, fields: readonly string[]): string {
  if (!fields.includes(field)) {
    throw new TypeError(`Unknown field "${field}"`);
  }
  return `"${field.replace(/[A-Z]/g, (c) => "_" + c.toLowerCase())}"`;
}

export function whereToSql(where: unknown, fields: readonly string[], params: unknown[]): string {
  if (where === undefined || where === null) return "TRUE";
  const bind = (value: unknown) => `$${params.push(value)}`;
  const parts: string[] = [];
  for (const [key, value] of Object.entries(where)) {
    if (key === "AND" || key === "OR") {
      const list = (value as unknown[]).map((w) => `(${whereToSql(w, fields, params)})`);
      parts.push(list.join(` ${key} `) || (key === "AND" ? "TRUE" : "FALSE"));
      continue;
    }
    if (key === "NOT") {
      parts.push(`NOT (${whereToSql(value, fields, params)})`);
      continue;
    }
    const col = column(key, fields);
    const hasOps = value !== null && typeof value === "object" && !(value instanceof Date);
    for (const [op, v] of hasOps ? Object.entries(value) : [["equals", value]]) {
      if (v === null && op === "equals") parts.push(`${col} IS NULL`);
      else if (v === null && op === "not") parts.push(`${col} IS NOT NULL`);
      else if (op in COMPARE) parts.push(`${col} ${COMPARE[op]} ${bind(v)}`);
      else if (op === "in") parts.push(`${col} = ANY(${bind(v)})`);
      else if (op === "contains") parts.push(`${col} LIKE ${bind(`%${String(v).replace(/[\\%_]/g, "\\$&")}%`)}`);
      else throw new TypeError(`Operator "${op}" is not supported`);
    }
  }
  return parts.join(" AND ") || "TRUE";
}

The second file is the adapter itself. modelDelegate gives one table the methods a Prisma model delegate has (findMany, create, update…), and createPglitePrisma builds the whole client. It also translates PostgreSQL's error codes into the Prisma codes ZudoJS understands: 23505 (unique value already used) becomes P2002, and a missing row becomes P2025. You don't need to follow every line; copy it into your project:

pglite-adapter.tsNode.js only

import type { PGlite, Transaction } from "@electric-sql/pglite";
import type { DatabaseTransactionContext, PrismaClientLike, RepositoryDelegate } from "@zudojs/database";
import { column, whereToSql } from "./where.js";

type Db = PGlite | Transaction;
type Row = Record<string, unknown>;
export interface Model {
  readonly table: string;
  readonly fields: readonly string[];
}
interface Args {
  where?: unknown;
  data?: Row;
  orderBy?: readonly Row[];
  skip?: number;
  take?: number;
}

/* PostgreSQL error codes, translated to the Prisma codes ZudoJS understands. */
const PRISMA_CODES: Record<string, string> = { "23505": "P2002", "23503": "P2003", "40001": "P2034" };

function prismaError(code: string, message: string): Error {
  return Object.assign(new Error(message), { code, clientVersion: "pglite" });
}

async function run(db: Db, sql: string, params: readonly unknown[]) {
  try {
    return await db.query<Row>(sql, [...params]);
  } catch (error) {
    const code = PRISMA_CODES[(error as { code?: string }).code ?? ""];
    throw code ? prismaError(code, (error as Error).message) : error;
  }
}

/* Rows come back as { created_at: … }; entities use { createdAt: … }. */
function toEntity(row: Row | undefined): Row {
  if (!row) throw prismaError("P2025", "Record not found");
  const entries = Object.entries(row).map(([key, value]) => [
    key.replace(/_(\w)/g, (_, c: string) => c.toUpperCase()),
    value,
  ]);
  return Object.fromEntries(entries);
}

export function modelDelegate<T>(db: Db, model: Model): RepositoryDelegate<T, number> {
  const table = `"${model.table}"`;
  const col = (field: string) => column(field, model.fields);
  const where = (args: Args | undefined, params: unknown[]) =>
    whereToSql(args?.where, model.fields, params);

  async function findMany(args: Args = {}) {
    const params: unknown[] = [];
    let sql = `SELECT * FROM ${table} WHERE ${where(args, params)}`;
    const order = (args.orderBy ?? []).flatMap((o) => Object.entries(o));
    if (order.length > 0) {
      sql += " ORDER BY " + order.map(([f, d]) => `${col(f)} ${d === "desc" ? "DESC" : "ASC"}`).join(", ");
    }
    if (args.take !== undefined) sql += ` LIMIT $${params.push(args.take)}`;
    if (args.skip !== undefined) sql += ` OFFSET $${params.push(args.skip)}`;
    return (await run(db, sql, params)).rows.map(toEntity);
  }

  const delegate = {
    findMany,
    findFirst: async (args: Args) => (await findMany({ ...args, take: 1 }))[0] ?? null,
    findUnique: async (args: Args) => (await findMany({ ...args, take: 1 }))[0] ?? null,
    async count(args?: Args) {
      const params: unknown[] = [];
      const sql = `SELECT count(*) AS n FROM ${table} WHERE ${where(args, params)}`;
      return Number((await run(db, sql, params)).rows[0]?.["n"]);
    },
    async create(args: Args) {
      const entries = Object.entries(args.data ?? {}).filter(([, value]) => value !== undefined);
      const columns = entries.map(([field]) => col(field)).join(", ");
      const marks = entries.map((_, i) => `$${i + 1}`).join(", ");
      const sql = `INSERT INTO ${table} (${columns}) VALUES (${marks}) RETURNING *`;
      return toEntity((await run(db, sql, entries.map(([, value]) => value))).rows[0]);
    },
    async update(args: Args) {
      const params: unknown[] = [];
      const sets = Object.entries(args.data ?? {})
        .filter(([, value]) => value !== undefined) // like Prisma: undefined means "leave it"
        .map(([f, v]) => `${col(f)} = $${params.push(v)}`);
      const sql = `UPDATE ${table} SET ${sets.join(", ")} WHERE ${where(args, params)} RETURNING *`;
      return toEntity((await run(db, sql, params)).rows[0]);
    },
    async delete(args: Args) {
      const params: unknown[] = [];
      const sql = `DELETE FROM ${table} WHERE ${where(args, params)} RETURNING *`;
      return toEntity((await run(db, sql, params)).rows[0]);
    },
  };
  return delegate as unknown as RepositoryDelegate<T, number>;
}

export function createPglitePrisma(pg: PGlite, models: Readonly<Record<string, Model>>): PrismaClientLike {
  const bind = (db: Db): DatabaseTransactionContext => {
    const client: Record<string, unknown> = {
      $queryRawUnsafe: async (sql: string, ...values: unknown[]) => (await run(db, sql, values)).rows,
      $executeRawUnsafe: async (sql: string, ...values: unknown[]) =>
        (await run(db, sql, values)).affectedRows ?? 0,
    };
    for (const [key, model] of Object.entries(models)) client[key] = modelDelegate(db, model);
    return client;
  };
  return {
    ...bind(pg),
    async $connect() {
      if (pg.closed) throw new Error("PGlite is closed");
      await pg.waitReady;
    },
    async $disconnect() {
      if (!pg.closed) await pg.close();
    },
    $transaction: <T>(callback: (tx: DatabaseTransactionContext) => Promise<T>) =>
      pg.transaction((tx) => callback(bind(tx))),
  };
}

One line needs a word: delegate as unknown as RepositoryDelegate<T, number>. The adapter works with plain rows, but a repository wants rows of type T, such as Task. Prisma generates exact types for each table; this small adapter cannot, so it asserts the type once, in one place. The table definition below is what keeps it honest.

NOTE

This adapter covers what the lesson needs: equality, contains, comparisons, AND/OR/NOT, sorting and paging. It ignores transaction options such as the isolation level. In production, Prisma does all of this for you.

Configuration and connecting

Now describe the tasks table once and open a client. The client is created with createDatabaseClient. Two options are worth knowing on day one:

logger: by default the client logs every connect and disconnect as JSON through @zudojs/logger. noopDatabaseLogger keeps the examples quiet. In your app, keep the default or pass your own logger.
connectionTimeoutMs: how long connect() may take before it fails (the default is 10 seconds).

Where the data lives is configuration, so it comes from the environment, as in the config lesson. With no PGLITE_DATA_DIR, PGlite keeps everything in memory:

database.tsNode.js only

import { PGlite } from "@electric-sql/pglite";
import { createDatabaseClient, noopDatabaseLogger } from "@zudojs/database";
import { createPglitePrisma } from "./pglite-adapter.js";
import type { Model } from "./pglite-adapter.js";

export interface Task {
  readonly id: number;
  readonly title: string;
  readonly done: boolean;
  readonly priority: "low" | "normal" | "high";
  readonly createdAt: Date;
  readonly deletedAt: Date | null;
}

export const TASK_MODEL: Model = {
  table: "tasks",
  fields: ["id", "title", "done", "priority", "createdAt", "deletedAt"],
};

export async function openDatabase() {
  const pg = new PGlite(process.env["PGLITE_DATA_DIR"]);
  const prisma = createPglitePrisma(pg, { task: TASK_MODEL });
  const client = createDatabaseClient({
    prisma,
    logger: noopDatabaseLogger,
    connectionTimeoutMs: 5_000,
  });
  await client.connect();
  return { pg, client };
}

The key task in { task: TASK_MODEL } matters: it is the name Prisma would give the Task model, and the repository uses it to find its table inside a transaction.

A real PostgreSQL server needs a connection string such as postgres://user:password@host:5432/tasks. It contains a password, so it is a secret: it comes from the environment, never from your code, and you never print it. Check that it is there when the app starts, so a missing setting fails loudly at once instead of at the first request:

env-check.tsNode.js only

import { ConfigurationError } from "@zudojs/errors";

export function requireDatabaseUrl(): string {
  const url = process.env["DATABASE_URL"];
  if (!url) {
    throw new ConfigurationError("DATABASE_URL is not set. Add it to your environment or .env file.");
  }
  if (!/^postgres(ql)?:\/\//.test(url)) {
    throw new ConfigurationError("DATABASE_URL must start with postgres://");
  }
  return url;
}

try {
  requireDatabaseUrl();
} catch (error) {
  if (error instanceof ConfigurationError) {
    console.log(error.name, "-", error.message);
  }
}

Output of npx tsx env-check.ts

ConfigurationError - DATABASE_URL is not set. Add it to your environment or .env file.

The message names the setting but never shows a value. Error messages end up in logs, and logs are read by more people than your secrets should be.

Migrations

You met migrations in the databases lesson: numbered steps that change the database's structure, applied in order and recorded so each runs only once. createMigrationRunner does that, with two extras. It takes a PostgreSQL advisory lock while it works, so two copies of your server starting together cannot apply the same migration twice. And each migration can have a down step that undoes it:

migrations.tsNode.js only

import type { Migration } from "@zudojs/database";

export const migrations: Migration[] = [
  {
    version: 1,
    name: "create-tasks",
    up: async (tx) => {
      await tx.$executeRawUnsafe(`
        CREATE TABLE tasks (
          id         serial PRIMARY KEY,
          title      text NOT NULL UNIQUE,
          done       boolean NOT NULL DEFAULT false,
          priority   text NOT NULL DEFAULT 'normal',
          created_at timestamptz NOT NULL DEFAULT now(),
          deleted_at timestamptz
        )`);
    },
    down: async (tx) => {
      await tx.$executeRawUnsafe("DROP TABLE tasks");
    },
  },
  {
    version: 2,
    name: "index-tasks-priority",
    up: async (tx) => {
      await tx.$executeRawUnsafe("CREATE INDEX tasks_priority_idx ON tasks (priority)");
    },
    down: async (tx) => {
      await tx.$executeRawUnsafe("DROP INDEX tasks_priority_idx");
    },
  },
];

Run them, run them again, then undo the last one:

migrate.tsNode.js only

import { createMigrationRunner } from "@zudojs/database";
import { openDatabase } from "./database.js";
import { migrations } from "./migrations.js";

const { client } = await openDatabase();
const runner = createMigrationRunner(client, migrations);

const first = await runner.migrate();
console.log("applied:", first.applied.map((m) => `${m.version} ${m.name}`));

const again = await runner.migrate();
console.log("applied:", again.applied.length, "skipped:", again.skipped.length);

const undone = await runner.rollback();
console.log("rolled back:", undone?.name);

const status = await runner.status();
console.log("version", status.currentVersion, "of", status.latestVersion);
console.log("pending:", status.pending.map((m) => m.name));

await client.disconnect();

Output of npx tsx migrate.ts

applied: [ '1 create-tasks', '2 index-tasks-priority' ]
applied: 0 skipped: 2
rolled back: index-tasks-priority
version 1 of 2
pending: [ 'index-tasks-priority' ]

The second migrate() applied nothing: the runner keeps its history in a table called _migrations and skipped both. After the rollback, version 2 is pending again, so the next migrate() would re-apply it. Each migration runs in its own transaction: if one fails halfway, its changes are undone and the ones before it stay.

NEVER EDIT A MIGRATION THAT HAS RUN

Once a migration has run on any shared database, treat it as frozen. To change the table again, add migration 3. The runner only looks at version numbers, so an edited migration 1 would never run again anywhere it already ran.

Seeds

A seed puts starting data into the database: demo tasks for development, or the list of roles every install needs. The seed runner works like the migration runner: named steps, run once, recorded in a _seeds table. A small setup function runs both, so every later example starts from the same data:

seeds.tsNode.js only

import type { Seed } from "@zudojs/database";

export const seeds: Seed[] = [
  {
    name: "demo-tasks",
    run: async (tx) => {
      const titles = ["Buy milk", "Write report", "Call the bank", "Read a book", "Plan the trip"];
      for (const title of titles) {
        await tx.$executeRawUnsafe("INSERT INTO tasks (title) VALUES ($1)", title);
      }
      await tx.$executeRawUnsafe("UPDATE tasks SET priority = 'high' WHERE title = $1", "Call the bank");
    },
    rollback: async (tx) => {
      await tx.$executeRawUnsafe("DELETE FROM tasks");
    },
  },
];

setup.tsNode.js only

import { createMigrationRunner, createSeedRunner } from "@zudojs/database";
import { openDatabase } from "./database.js";
import { migrations } from "./migrations.js";
import { seeds } from "./seeds.js";

export async function setup() {
  const db = await openDatabase();
  await createMigrationRunner(db.client, migrations).migrate();
  await createSeedRunner(db.client, seeds).run();
  return db;
}

seed.tsNode.js only

import { createSeedRunner } from "@zudojs/database";
import { seeds } from "./seeds.js";
import { setup } from "./setup.js";

const { client } = await setup();

const again = await createSeedRunner(client, seeds).run();
console.log("second run skipped:", again.skipped.map((s) => s.name));

const rows = await client.queryRawUnsafe<{ id: number; title: string; priority: string }[]>(
  "SELECT id, title, priority FROM tasks ORDER BY id",
);
console.table(rows);
await client.disconnect();

Output of npx tsx seed.ts

second run skipped: [ 'demo-tasks' ]
┌─────────┬────┬─────────────────┬──────────┐
│ (index) │ id │ title           │ priority │
├─────────┼────┼─────────────────┼──────────┤
│ 0       │ 1  │ 'Buy milk'      │ 'normal' │
│ 1       │ 2  │ 'Write report'  │ 'normal' │
│ 2       │ 3  │ 'Call the bank' │ 'high'   │
│ 3       │ 4  │ 'Read a book'   │ 'normal' │
│ 4       │ 5  │ 'Plan the trip' │ 'normal' │
└─────────┴────┴─────────────────┴──────────┘

setup() ran the seed once; running it a second time skipped it. Seeds are for data your app needs to start. Test data for one test belongs in that test, which the testing lesson covers.

Raw SQL, safely

You just used client.queryRawUnsafe(sql, values). Sometimes a hand-written query is the clearest way to get an answer. The word Unsafe in the name is a warning: the method sends your SQL text exactly as you wrote it. It is safe only when every outside value goes in the values array. Here is the mistake from the SQL basics lesson once more, then the fix:

raw.tsNode.js only

import { setup } from "./setup.js";

const { client } = await setup();
const search = "nothing' OR '1'='1"; // what an attacker types into the search box

// WRONG: the input becomes part of the SQL
const leaked = await client.queryRawUnsafe<unknown[]>(
  `SELECT id FROM tasks WHERE title = '${search}'`,
);
console.log("string-built query returned", leaked.length, "rows");

// RIGHT: the input is a parameter, never SQL
const safe = await client.queryRawUnsafe<unknown[]>(
  "SELECT id FROM tasks WHERE title = $1",
  [search],
);
console.log("parameterized query returned", safe.length, "rows");
await client.disconnect();

Output of npx tsx raw.ts

string-built query returned 5 rows
parameterized query returned 0 rows

The string-built query returned every task, because the attacker's quote closed the string and OR '1'='1' is always true. With $1, PostgreSQL treats the whole input as one plain value and finds no task with that odd title. Raw SQL through ZudoJS always uses $1, $2… with a values array.

A repository

A repository is one class that owns all access to one table. The rest of your app asks it for tasks and never writes SQL. You wrote a small generic repository in the generics lesson; BaseRepository is the full version. Extend it, pass the model delegate, and add methods for your own questions:

task.repository.tsNode.js only

import { BaseRepository } from "@zudojs/database";
import type { BaseRepositoryOptions, RepositoryDelegate } from "@zudojs/database";
import type { Task } from "./database.js";

export type NewTask = Pick<Task, "title"> & Partial<Pick<Task, "done" | "priority">>;

export class TaskRepository extends BaseRepository<Task, number, NewTask> {
  constructor(delegate: RepositoryDelegate<Task, number, NewTask>, options: BaseRepositoryOptions = {}) {
    super(delegate, { modelName: "Task", softDelete: true, ...options });
  }

  findByTitle(title: string): Promise<Task | null> {
    return this.findOne({ title });
  }
}

The type parameters are the entity (Task), its id type (number) and what create accepts (NewTask: a title, and optionally done and priority). softDelete: true is explained in the next section, and options lets a caller add more settings, which the pagination section uses. Now use it, including two things that go wrong on purpose:

repository.tsNode.js only

import { isConflictError, isNotFoundError } from "@zudojs/database";
import { BaseError } from "@zudojs/errors";
import { TASK_MODEL } from "./database.js";
import type { Task } from "./database.js";
import { modelDelegate } from "./pglite-adapter.js";
import { setup } from "./setup.js";
import { TaskRepository } from "./task.repository.js";

const { pg, client } = await setup();
const tasks = new TaskRepository(modelDelegate<Task>(pg, TASK_MODEL));

const created = await tasks.create({ title: "Learn ZudoJS", priority: "high" });
console.log(created);

const updated = await tasks.update(created.id, { done: true });
console.log("done:", updated.done);
console.log("Buy milk has id", (await tasks.findByTitle("Buy milk"))?.id);
console.log("high priority:", await tasks.count({ priority: "high" }));

const mistakes = [
  () => tasks.create({ title: "Buy milk" }),
  () => tasks.update(999, { done: true }),
];
for (const attempt of mistakes) {
  try {
    await attempt();
  } catch (error) {
    if (error instanceof BaseError) {
      console.log(error.statusCode, error.code, error.message);
      console.log("  conflict?", isConflictError(error), "not found?", isNotFoundError(error));
    }
  }
}
await client.disconnect();

Output of npx tsx repository.ts

{
  id: 6,
  title: 'Learn ZudoJS',
  done: false,
  priority: 'high',
  createdAt: 2026-09-23T13:40:42.567Z,
  deletedAt: null
}
done: true
Buy milk has id 1
high priority: 2
409 ERR_CONFLICT Task already exists.
  conflict? true not found? false
404 ERR_NOT_FOUND Task was not found.
  conflict? false not found? true

Read the output:

create returned the whole new row, with the id, defaults and timestamp that PostgreSQL filled in.
A second "Buy milk" broke the UNIQUE rule on title. The repository turned PostgreSQL's error into a DatabaseError with status 409 Conflict.
Updating task 999 found nothing to update, which became 404 Not Found.

These are the same status codes your API should send, so an error handler like the one in the errors lesson can pass them on as they are. The messages are generic on purpose: they never include the constraint name or the SQL, which would tell an attacker about your schema.

Soft delete and the query builder

Soft delete means "mark as deleted instead of removing". softDelete(id) sets deleted_at to the current time, and from then on every read of the repository skips that row. Users can get a deleted task back, and you keep a history. findDeleted() lists the deleted ones, and withDeleted() gives you a copy of the repository that sees everything.

For searches with several conditions, createQueryBuilder builds the filter step by step. Its type parameter lists the fields you allow, so a typo is a compile error:

query.tsNode.js only

import { createQueryBuilder } from "@zudojs/database";
import { TASK_MODEL } from "./database.js";
import type { Task } from "./database.js";
import { modelDelegate } from "./pglite-adapter.js";
import { setup } from "./setup.js";
import { TaskRepository } from "./task.repository.js";

const { pg, client } = await setup();
const tasks = new TaskRepository(modelDelegate<Task>(pg, TASK_MODEL));

await tasks.softDelete(1);
const titles = (list: readonly Task[]) =>
  [...list].sort((a, b) => a.id - b.id).map((t) => `${t.id} ${t.title}`);
console.log("visible:", titles(await tasks.findMany()));
console.log("deleted:", titles(await tasks.findDeleted()));
console.log("everything:", (await tasks.withDeleted().count()));

const query = createQueryBuilder<keyof Task>()
  .where("done", false)
  .whereContains("title", "the")
  .orderByDesc("id")
  .limit(2);
console.log(JSON.stringify(query.toPrismaArgs()));
const found = await tasks.findByQuery(query);
console.log("found:", found.map((t) => `${t.id} ${t.title}`));
await client.disconnect();

Output of npx tsx query.ts

visible: [
  '2 Write report',
  '3 Call the bank',
  '4 Read a book',
  '5 Plan the trip'
]
deleted: [ '1 Buy milk' ]
everything: 5
{"where":{"done":{"equals":false},"title":{"contains":"the"}},"orderBy":[{"id":"desc"}],"skip":0,"take":2}
found: [ '5 Plan the trip', '3 Call the bank' ]

The query builder produces a plain Prisma-style object (the JSON line), which the repository hands to the delegate, and your adapter turned into SQL with parameters. whereContains is case-sensitive: "the" matches "Call the bank" and "Plan the trip", not "The…".

Sorting by what the client asks for

Clients often choose the sort order: GET /tasks?sort=title. A field name cannot be a SQL parameter, so it must be checked. ZudoJS checks sort fields before they reach the database, and your adapter's allow-list catches any field the table does not have:

sort-attack.tsNode.js only

import { TASK_MODEL } from "./database.js";
import type { Task } from "./database.js";
import { modelDelegate } from "./pglite-adapter.js";
import { setup } from "./setup.js";
import { TaskRepository } from "./task.repository.js";

const { pg, client } = await setup();
const tasks = new TaskRepository(modelDelegate<Task>(pg, TASK_MODEL));

for (const field of ["title", "title; DROP TABLE tasks", "password"]) {
  try {
    const page = await tasks.findPaginated(undefined, {
      pagination: { page: 1, limit: 2 },
      sort: [{ field, direction: "asc" }],
    });
    console.log(field, "->", page.data.map((t) => t.title));
  } catch (error) {
    console.log(field, "->", (error as Error).name, (error as Error).message);
  }
}
console.log("tasks left:", await tasks.count());
await client.disconnect();

Output of npx tsx sort-attack.ts

title -> [ 'Buy milk', 'Call the bank' ]
title; DROP TABLE tasks -> TypeError Invalid query field name "title; DROP TABLE tasks".
password -> DatabaseError Unknown field "password"
tasks left: 5

The injection attempt never reached PostgreSQL: ZudoJS refused the name because it is not a plain identifier. password is a valid name but not a field of tasks, so the adapter refused it. In a real route, check the sort field against a short list of allowed values with a schema first, and answer 400 for anything else, as the validation lesson showed.

Pagination

Never return a whole table: it grows forever. Pagination returns one page at a time. There are two kinds.

Offset pagination says "page 2, 2 per page". It is easy and lets users jump to any page. findPaginated also counts the rows, so it can tell you how many pages there are. Keyset (cursor) pagination says "the 2 tasks after the last one I saw". It stays fast on huge tables and does not skip or repeat rows when new ones are added between requests. The "last one I saw" is an opaque string called a cursor, which the client sends back to get the next page.

A cursor travels through the client, so the client can change it. Give the repository a cursorSecret and every cursor is signed: a changed cursor is rejected. The secret comes from the environment:

paginate.tsNode.js only

import { randomBytes } from "node:crypto";
import { ValidationError } from "@zudojs/errors";
import { TASK_MODEL } from "./database.js";
import type { Task } from "./database.js";
import { modelDelegate } from "./pglite-adapter.js";
import { setup } from "./setup.js";
import { TaskRepository } from "./task.repository.js";

// Demo only: a random secret so the example runs anywhere. Your app sets CURSOR_SECRET.
process.env["CURSOR_SECRET"] ??= randomBytes(32).toString("base64url");

const cursorSecret = process.env["CURSOR_SECRET"];
if (cursorSecret.length < 32) {
  throw new Error("CURSOR_SECRET must be at least 32 characters");
}

const { pg, client } = await setup();
const tasks = new TaskRepository(modelDelegate<Task>(pg, TASK_MODEL), { cursorSecret });
const sort = [{ field: "id", direction: "asc" }] as const;

const page2 = await tasks.findPaginated(undefined, { pagination: { page: 2, limit: 2 }, sort });
console.log(page2.data.map((t) => t.id), page2.meta);

const first = await tasks.paginateCursor(undefined, { limit: 2, sort });
console.log(first.data.map((t) => t.id), first.meta.nextCursor);
const next = await tasks.paginateCursor(undefined, { limit: 2, sort, cursor: first.meta.nextCursor });
console.log(next.data.map((t) => t.id), "has next?", next.meta.hasNextPage);
const back = await tasks.paginateCursor(undefined, { limit: 2, sort, cursor: next.meta.previousCursor });
console.log(back.data.map((t) => t.id), "has previous?", back.meta.hasPreviousPage);

const [, signature] = (first.meta.nextCursor ?? "").split(".");
const forged = Buffer.from('{"id":0}').toString("base64url") + "." + signature;
try {
  await tasks.paginateCursor(undefined, { limit: 2, sort, cursor: forged });
} catch (error) {
  if (error instanceof ValidationError) {
    console.log(error.statusCode, error.name, error.message, error.issues[0]?.code);
  }
}
await client.disconnect();

Output of npx tsx paginate.ts

[ 3, 4 ] {
  page: 2,
  limit: 2,
  total: 5,
  totalPages: 3,
  hasNextPage: true,
  hasPreviousPage: true,
  hasNext: true,
  hasPrev: true
}
[ 1, 2 ] eyJpZCI6Mn0.nySv_IOv5J-S0Pqx2wp7NtmJoZLgIR3kP0ZwbiFLgRY
[ 3, 4 ] has next? true
[ 1, 2 ] has previous? false
400 ValidationError Invalid pagination cursor signature. cursor_signature

Page 2 of the offset pagination holds tasks 3 and 4, and meta has what a client needs to draw page links: the total, the number of pages, and whether there is a next or previous page.

The cursor has two parts separated by a dot. The first is the position, in Base64: eyJpZCI6Mn0 is {"id":2}, the last task of the first page. The second is the signature, which changes with every secret (so yours will differ).

Cursors go both ways. A page fetched with a cursor also has a meta.previousCursor; sending it back returns the page before, here tasks 1 and 2 again, in the same order. That page is the first one, so hasPreviousPage is false.

The forged cursor claimed to start at id 0 and reused a real signature, and it was refused with a ValidationError: status 400 Bad Request, and one issue whose code says why (cursor_signature). A cursor is outside input, so a bad one is the client's mistake, not a server error. Your error handler can send the 400 as it is, and the message never repeats the cursor's contents.

Use cursor pagination for "load more" buttons and infinite scrolling, and offset pagination when users need page numbers.

Transactions

A transaction makes several changes all-or-nothing, as you saw in the databases lesson. withTransaction(client, callback) opens one, passes a transaction client tx to your callback, commits if the callback returns and rolls back if it throws. Repositories join the transaction with withTransaction(tx), which returns a copy bound to it:

transaction.tsNode.js only

import { withTransaction } from "@zudojs/database";
import { BaseError } from "@zudojs/errors";
import { TASK_MODEL } from "./database.js";
import type { Task } from "./database.js";
import { modelDelegate } from "./pglite-adapter.js";
import { setup } from "./setup.js";
import { TaskRepository } from "./task.repository.js";

const { pg, client } = await setup();
const tasks = new TaskRepository(modelDelegate<Task>(pg, TASK_MODEL));

try {
  await withTransaction(client, async (tx) => {
    const repo = tasks.withTransaction(tx);
    await repo.create({ title: "Pack bags" });
    await repo.create({ title: "Buy milk" }); // already exists
  });
} catch (error) {
  if (error instanceof BaseError) {
    console.log("transaction failed:", error.statusCode, error.message);
  }
}
console.log("Pack bags saved?", (await tasks.findByTitle("Pack bags")) !== null);

const moved = await withTransaction(client, async (tx) => {
  const repo = tasks.withTransaction(tx);
  await repo.update(2, { priority: "high" });
  await repo.update(3, { priority: "normal" });
  return repo.count({ priority: "high" });
});
console.log("committed, high priority tasks:", moved);
await client.disconnect();

Output of npx tsx transaction.ts

transaction failed: 409 Task already exists.
Pack bags saved? false
committed, high priority tasks: 1

The second insert failed, so the first one was rolled back too: "Pack bags" was never saved. The second transaction committed both updates together. Use the tx-bound repository for every query inside the callback. A query through the ordinary tasks repository would run outside the transaction.

Your own errors inside a transaction

Often the reason to stop a transaction is your own rule, not the database: "project 7 does not exist", "this task is already done". Throw the matching error from the errors lesson. The transaction still rolls back, and the error reaches your caller unchanged, with its own status code:

transaction-errors.tsNode.js only

import { withTransaction } from "@zudojs/database";
import { BaseError, NotFoundError } from "@zudojs/errors";
import { TASK_MODEL } from "./database.js";
import type { Task } from "./database.js";
import { modelDelegate } from "./pglite-adapter.js";
import { setup } from "./setup.js";
import { TaskRepository } from "./task.repository.js";

const { pg, client } = await setup();
const tasks = new TaskRepository(modelDelegate<Task>(pg, TASK_MODEL));

try {
  await withTransaction(client, async (tx) => {
    await tasks.withTransaction(tx).create({ title: "Draw the plan" });
    throw new NotFoundError("Project 7 not found");
  });
} catch (error) {
  if (error instanceof BaseError) {
    console.log(error.name, error.statusCode, error.message);
  }
}
console.log("Draw the plan saved?", (await tasks.findByTitle("Draw the plan")) !== null);
await client.disconnect();

Output of npx tsx transaction-errors.ts

NotFoundError 404 Project 7 not found
Draw the plan saved? false

The insert was undone, and your API can answer 404 as it should. Any @zudojs/errors error keeps its class and status this way (since @zudojs/database 1.3.0). Failures that really come from the database, such as the 409 above, still arrive as a DatabaseError.

withTransactionRetry(client, callback, { retries: 3 }) runs the whole callback again when PostgreSQL reports a serialization failure or a deadlock, two errors that mean "try again". The transactions lesson goes deeper: nesting, savepoints, hooks that run after a commit, and timeouts.

Health checks and connection pools

Your /health route from creating the project says "ok" even when the database is down. A load balancer then keeps sending traffic to a server that cannot answer. checkDatabaseHealth runs a tiny SELECT 1 with a timeout and tells you the truth:

health.tsNode.js only

import { checkDatabaseHealth, checkDatabaseReadiness } from "@zudojs/database";
import { setup } from "./setup.js";

const { pg, client } = await setup();

async function healthRoute() {
  const health = await checkDatabaseHealth(client, { timeoutMs: 2_000 });
  return { status: health.healthy ? 200 : 503, body: { database: health.status } };
}

console.log(await healthRoute());
console.log("ready:", (await checkDatabaseReadiness(client)).ready);

await pg.close(); // the database goes away
console.log(await healthRoute());
console.log("ready:", (await checkDatabaseReadiness(client)).ready);
await client.disconnect();

Output of npx tsx health.ts

{ status: 200, body: { database: 'healthy' } }
ready: true
{ status: 503, body: { database: 'unhealthy' } }
ready: false

Once the database was gone, the check reported unhealthy and the route answered 503 Service Unavailable. The body names only the status, never the error's text or the host name: a health endpoint is public.

Health (also called liveness) asks "is this process working?".
Readiness asks "can it take traffic right now?". During start-up, before migrations are done, a server is alive but not ready.

Connection pools

Opening a connection to a PostgreSQL server is slow: a network round trip, a login, some memory on the server. A connection pool opens a few connections once and lends them out, one per query or transaction, taking them back afterwards. When all are busy, the next query waits for a free one.

With Prisma, the pool belongs to the driver adapter, not to @zudojs/database. That is why the production example at the top of this lesson passed max, idleTimeoutMillis and connectionTimeoutMillis to PrismaPg. A pool that is too big overloads the database; too small, and requests queue. Start with about 10 per server process, and remember that PostgreSQL has a total limit (often 100) shared by all your servers. PGlite is one connection inside your process, so it has no pool.

For long-running servers, createConnectionManager({ client, healthCheckIntervalMs, reconnect }) checks the connection on a timer and reconnects with growing pauses between tries when it drops. While it waits between tries it keeps the process running, and disconnect() stops the retries.

Practice

TRY IT YOURSELF

Add a due date

Write migration 3, add-task-due-date, that adds a nullable due_date date column to tasks, with a down that removes it. Run all migrations and print the column names of tasks from information_schema.columns.

Show a solution

due-date.tsNode.js only

import { createMigrationRunner } from "@zudojs/database";
import type { Migration } from "@zudojs/database";
import { openDatabase } from "./database.js";
import { migrations } from "./migrations.js";

const addDueDate: Migration = {
  version: 3,
  name: "add-task-due-date",
  up: async (tx) => {
    await tx.$executeRawUnsafe("ALTER TABLE tasks ADD COLUMN due_date date");
  },
  down: async (tx) => {
    await tx.$executeRawUnsafe("ALTER TABLE tasks DROP COLUMN due_date");
  },
};

const { client } = await openDatabase();
const result = await createMigrationRunner(client, [...migrations, addDueDate]).migrate();
console.log(result.applied.map((m) => m.version));

const columns = await client.queryRawUnsafe<{ column_name: string }[]>(
  "SELECT column_name FROM information_schema.columns WHERE table_name = $1 ORDER BY ordinal_position",
  ["tasks"],
);
console.log(columns.map((c) => c.column_name));
await client.disconnect();

Output of npx tsx due-date.ts

[ 1, 2, 3 ]
[
  'id',
  'title',
  'done',
  'priority',
  'created_at',
  'deleted_at',
  'due_date'
]

Remember to add "dueDate" to TASK_MODEL.fields and dueDate: string | null to Task, or the adapter's allow-list will refuse the new field.

TRY IT YOURSELF

Open tasks by priority

Add a method openByPriority(priority) to TaskRepository that returns the tasks that are not done and have that priority, newest first, using the query builder. Mark task 4 done and try it.

Show a solution

open-by-priority.tsNode.js only

import { createQueryBuilder } from "@zudojs/database";
import { TASK_MODEL } from "./database.js";
import type { Task } from "./database.js";
import { modelDelegate } from "./pglite-adapter.js";
import { setup } from "./setup.js";
import { TaskRepository } from "./task.repository.js";

class Tasks extends TaskRepository {
  openByPriority(priority: Task["priority"]): Promise<readonly Task[]> {
    const query = createQueryBuilder<keyof Task>()
      .where("done", false)
      .where("priority", priority)
      .orderByDesc("createdAt")
      .orderByDesc("id");
    return this.findByQuery(query);
  }
}

const { pg, client } = await setup();
const tasks = new Tasks(modelDelegate<Task>(pg, TASK_MODEL));
await tasks.update(4, { done: true });
console.log((await tasks.openByPriority("normal")).map((t) => t.id));
console.log((await tasks.openByPriority("high")).map((t) => t.id));
await client.disconnect();

Output of npx tsx open-by-priority.ts

[ 5, 2, 1 ]
[ 3 ]

Sorting by createdAt alone is not enough here: the seed created all five tasks in one transaction, so they share a timestamp. Adding id as a second sort field makes the order stable.

TRY IT YOURSELF

A safe page request

Clients send page and limit as strings in the query string. Write listTasks(query) that validates them with @zudojs/schema (whole numbers, page at least 1, limit 1 to 50, both optional) and returns a page of tasks sorted by id. Try it with { page: "2", limit: "2" } and with { limit: "5000" }.

Show a solution

list-tasks.tsNode.js only

import { SchemaError } from "@zudojs/errors";
import { schema } from "@zudojs/schema";
import { TASK_MODEL } from "./database.js";
import type { Task } from "./database.js";
import { modelDelegate } from "./pglite-adapter.js";
import { setup } from "./setup.js";
import { TaskRepository } from "./task.repository.js";

const PageQuery = schema.object({
  page: schema.coerce.number().int().min(1).default(1),
  limit: schema.coerce.number().int().min(1).max(50).default(10),
});

const { pg, client } = await setup();
const tasks = new TaskRepository(modelDelegate<Task>(pg, TASK_MODEL));

async function listTasks(query: unknown) {
  const { page, limit } = PageQuery.parse(query);
  const result = await tasks.findPaginated(undefined, {
    pagination: { page, limit },
    sort: [{ field: "id", direction: "asc" }],
  });
  return { tasks: result.data.map((t) => t.title), total: result.meta.total, pages: result.meta.totalPages };
}

console.log(await listTasks({ page: "2", limit: "2" }));
try {
  await listTasks({ limit: "5000" });
} catch (error) {
  if (error instanceof SchemaError) console.log(error.statusCode, error.message);
}
await client.disconnect();

Output of npx tsx list-tasks.ts

{ tasks: [ 'Call the bank', 'Read a book' ], total: 5, pages: 3 }
400 Validation failed

Recap

@zudojs/database is a PostgreSQL layer on top of Prisma. It needs a PrismaClientLike: in production a generated PrismaClient with @prisma/adapter-pg, here a small adapter over PGlite.
Connection strings are secrets: read them from the environment, check them at start-up, never print them.
createMigrationRunner and createSeedRunner apply numbered migrations and named seeds once, under a lock, and can roll them back.
queryRawUnsafe is safe only with $1 parameters. Field names are checked against an allow-list.
BaseRepository gives you CRUD, soft delete, findByQuery, offset and signed cursor pagination (forward and back; a forged cursor is a 400), and maps database errors to 409 and 404.
withTransaction makes several writes all-or-nothing; bind repositories with withTransaction(tx). Your own errors thrown inside roll it back and keep their status code.
checkDatabaseHealth makes /health honest. Pools live in the driver adapter.

Next, storage abstractions show a second, driver-independent way to store data, including files.

Test yourself

Five questions, picked at random from this lesson's question bank. Some ask you to choose an answer, some to predict what code prints, and some to write code and run it in the terminal. Get 4 of 5 right to pass. If you don't, read the explanations and try again: you get 5 different questions.