Database

Every Environment gets its own Postgres, and the Database service is how your backend talks to it. There is no ctx object and no client to construct: you import Database from @palbase/backend and call it. Two things about it are load-bearing. Database.public.<name> is typed against what db/ declares, with no import and no generic, and every statement runs inside a transaction bound to the calling user's identity, so Row-Level Security filters what your SQL can even see. The one deliberate exception is Database.$asService(), which opens a genuinely separate service-role transaction — explicit, greppable, and never the default.

Quick example

// modules/todos/dto/todo.ts
import { z } from "@palbase/backend";

export const Todo = z.object({
  id: z.string(),
  user_id: z.string(),
  title: z.string(),
  done: z.boolean(),
  created_at: z.string(),
});
export type Todo = z.infer<typeof Todo>;

export const CreateTodo = z.object({ title: z.string().min(1) });
export type CreateTodo = z.infer<typeof CreateTodo>;

// modules/todos/todo.service.ts
// The layer that touches Database.
import { Database, Injectable, NotFound } from "@palbase/backend";
import { Todo } from "./dto/todo";

@Injectable()
export class TodoService {
  open(): Promise<Todo[]> {
    // No user_id filter — RLS scopes this to the caller's own rows.
    return Database.public.todos.findMany({ where: { done: false } });
  }

  create(userId: string, title: string): Promise<Todo> {
    return Database.public.todos.insert({ title, user_id: userId });
  }

  async get(id: string): Promise<Todo> {
    const todo = await Database.public.todos.findById(id);
    if (!todo) throw new NotFound("todo not found");
    return todo;
  }
}

// modules/todos/todos.controller.ts
import { Body, Controller, Get, Param, Post, User } from "@palbase/backend";
import type { UserT } from "@palbase/backend";
import { CreateTodo, Todo } from "./dto/todo";
import { TodoService } from "./todo.service";

@Controller("/todos")
export class TodosController {
  constructor(private readonly todos: TodoService) {}

  @Get("")
  list(): Promise<Todo[]> {
    return this.todos.open();
  }

  @Post("")
  create(@Body(CreateTodo) body: CreateTodo, @User() user: UserT): Promise<Todo> {
    return this.todos.create(user.id, body.title);
  }

  @Get("/{id}")
  get(@Param("id") id: string): Promise<Todo> {
    return this.todos.get(id);
  }
}

Three house rules are visible there. The response schema is read from the method's return type, so it must name a zod schema that exists as a value in scope — an interface or an inline Promise<{ … }> is rejected by the deploy. Database is imported by the service, never by the controller — the decisions about which rows, whose and in what order are the part worth testing, and a controller that reaches for a table has moved them into the layer that is hardest to test. And both classes are named in modules/todos/todos.module.ts — the controller under controllers, the service under providers — because a class no module lists is refused at build, by name, and never reaches the route table or the OpenAPI document.

The full surface

Database carries your schemas by name — Database.public.<table>, and Database.<schema>.<table> for any other schema db/ declares — plus a set of system operations that all begin with $.

The $ is the whole disambiguation rule, and it is why it exists: a table or a schema name can never start with $ (the schema validator refuses it), so a table called transaction is Database.transaction and the operation is Database.$transaction. No name you can declare can collide with one of these.

The table above is the set you will reach for; it is not the whole list. Every typed table method has a string-keyed twin under the same rule — $insertMany, $updateMany, $deleteMany, $aggregate, $search, $similar, $recommend, $facets, $claim, $supersede — for the cases where the table name is a variable rather than something you can write down.

Two of them are not twins — they are the only surface for what they do, and reaching for raw SQL instead of them is the most common reason $query shows up in a codebase:

For the rows you were about to select anyway, select(where, { lock: "update" }) inside a $transaction takes a real FOR UPDATE.

The low-level txPlan operation is deliberately not re-exposed here — $transaction is the surface, and a hand-built plan would skip the guard machinery that makes one safe to write.

Note: Every value that leaves Database has passed through a wire conversion, and one case is worth knowing: a timestamp column comes back from the driver as a JavaScript Date and is converted to an ISO-8601 string before you see it. Without that, a handler returning a row straight from Database.public.* failed its own declared return type — a live POST /todos answered 500 output_invalid with "expected string, received date" for created_at, from code that had done nothing wrong.

Typed tables

Database.public.<name> carries one accessor per table in your schema, with row and insert types derived from your column definitions.

Every read and every bulk write takes one object. where is a field on it, not a positional argument, so orderBy, limit, select and the rest have somewhere obvious to sit and there is nothing to remember about which argument holds what.

search is the odd one out: it is present only when the table declares a search block or carries a vector column, so calling it anywhere else is a compile error rather than a runtime surprise. Full-text, vector or both, one call, RLS applied — see Search.

import { Database } from "@palbase/backend";

declare const user: { id: string };

// Fully typed: insert requires title + user_id; id/done/created_at have defaults.
const todo = await Database.public.todos.insert({ title: "ship docs", user_id: user.id });

// Partial update — only what `set` names changes.
const updated = await Database.public.todos.update({ where: { id: todo.id }, set: { done: true } });

// Equality filter: keys inside `where` are ANDed together.
const open = await Database.public.todos.findMany({ where: { done: false } });
const all = await Database.public.todos.findMany(); // no filter — everything visible to you

update, delete and findById all address a row by its id column; a table whose primary key is called something else is reachable through findMany, updateMany, deleteMany and $query.

Filtering, ordering, paging

Inside where, a field takes a plain value for equality or an operator object; fields are ANDed. orderBy, limit, offset and select sit beside where in the same object.

import { Database } from "@palbase/backend";

declare const accountIds: string[];
declare const since: string;

const recent = await Database.public.entries.findMany({
  where: { account_id: { in: accountIds }, created_at: { gt: since } },
  orderBy: { column: "created_at", direction: "desc" },
  limit: 50,
});

orderBy.column is checked against the schema before it can reach SQL. Related rows come back through with, aggregates through aggregate, and anything neither covers belongs in $query.

Note: array parameters in $query are encoded as Postgres array literals, so = ANY($1::uuid[]) takes a plain JavaScript array — Database.$query("… WHERE id = ANY($1::uuid[])", [ids]). A vector value goes the same way and is cast on arrival: $1::float8[]::vector.

put() — write, or overwrite what it collided with

Read-then-write loses a race, and it cannot be rescued by catching the unique violation: a request is ONE transaction, so the failed insert aborts it and every later statement answers current transaction is aborted. put is one statement instead.

import { Database } from "@palbase/backend";

declare const user: { id: string };
declare const interestId: string;

await Database.public.user_interests.put({
  data: { user_id: user.id, interest_id: interestId },
  onConflict: ["user_id", "interest_id"],
});

The conflict columns must carry a unique constraint or index — that is what Postgres matches on — and they are excluded from the update, since they are what matched. The same operation exists inside $transaction(fn), under the same name.

Note: put replaced upsert, and the rename is the point. upsert carried two different intentions under one name — "write it, and overwrite if it is already there" (this) and "write it once, idempotently" — and DO UPDATE is silently wrong for the second: the second caller's data overwrites the first's. For that one, use claim(unique, extra?), which answers { inserted, row } and hands back the first call's row when inserted is false.

$attempt(): a failure that does not poison the request

When the recovery is not a put, put the write that might fail inside $attempt. It takes a real SAVEPOINT, so only that write rolls back and the request keeps going.

import { Database } from "@palbase/backend";

declare const user: { id: string };
declare const interestId: string;

const claimed = await Database.$attempt(async (tx) => {
  await tx.insert("user_interests", { user_id: user.id, interest_id: interestId });
  return true;
}).catch(() => false);

The handle is a parameter, not the ambient Database: only what tx writes is inside the boundary. An ambient version would capture writes a concurrent branch of the same request made outside it, and roll those back too.

insert is the one typed method that throws rather than returning null. It writes with RETURNING *, and a write that comes back with no row was filtered away — almost always by an RLS WITH CHECK policy — so it raises instead of handing you undefined:

insert into todos returned no row — the write was rejected (an RLS policy, most likely).

Where the types come from

Your checkout holds a generated palbase/palbase-env.d.ts, produced from db/*.ts. It augments the SDK's Tables interface, which is why Database.public.* is typed everywhere with no import and no generic. palbase build writes it:

palbase build
# ✓ palbase/palbase-env.d.ts

It lives inside palbase/, the one visible directory the CLI owns, and it is committed like everything else there — at the checkout root it was a generated file that had to be ignored forever, and an ignored file is one nobody can see drift in. palbase build is the only command that writes it — palbase push does not, so after editing a schema file run a build if you want your editor to catch up. Never edit it by hand. When you want a row type explicitly, for a service signature, import it:

import type { Tables } from "@palbase/backend/env";

type Todo = Tables["todos"]["row"];
type TodoInsert = Tables["todos"]["insert"];

Note: If Database.public.todos is a compile error saying the property does not exist, the type file has not been generated for that table yet. Run palbase build. Before any schema exists at all, Tables is empty and every Database.public.* access is a compile error — that is the correct behaviour, not a missing feature.

null means "no row" — including rows RLS hides

findById and update resolve to null when no row matched. That covers two cases you cannot tell apart from inside the handler:

• the id genuinely does not exist, or
• the row exists and an RLS policy hides it from this caller.

Not telling them apart is the point — a distinguishable answer is an oracle for rows the caller may not read. It is an idempotent outcome, not an error: the runtime never throws for a missing row. Map it yourself when a missing row should be a client error:

import { Database, NotFound } from "@palbase/backend";

declare const id: string;

const todo = await Database.public.todos.findById(id);
if (!todo) throw new NotFound("todo not found");

const updated = await Database.public.todos.update({ where: { id }, set: { done: true } });
if (!updated) throw new NotFound("todo not found");

Warning: Do not skip the null check after update. A handler that ignores it returns null where its declared return type promises a row, and the engine answers 500 output_invalid — a validation failure of your own contract, on the response path, where it is hardest to read.

Raw SQL with $query()

$query(sql, params?) runs parameterised SQL and returns rows as plain records. Use it for selects, joins and aggregates the typed helpers do not cover.

Warning: $query is not sandboxed. It runs on the very same transaction insert, update and delete write through — nothing marks that transaction READ ONLY — so a write you pass to it executes and commits with the request, whether it is a plain UPDATE, a write hidden in a CTE, or a function that writes. The only thing standing between a statement and your data is RLS and the role the request runs as, exactly as for every other operation on this page. Write through the typed tables, where the schema, the wire conversion and the null checks are on your side.

import { Database } from "@palbase/backend";

const rows = await Database.$query(
  "SELECT id, title FROM todos WHERE done = $1 ORDER BY created_at DESC LIMIT $2",
  [false, 20],
);
const typed = rows as { id: string; title: string }[];

Two things to keep in mind:

• Use $1, $2, … with the params array. Never interpolate a value into the SQL string.
• $query takes no TypeScript generic. Writing $query<MyRow>(...) is a compile error ("Expected 0 type arguments"); cast the returned Record<string, unknown>[] instead.

RLS applies to $query exactly as it does to everything else: the statement runs as the calling user, so policies filter what the SQL can see.

Transactions are a plan, not a session

Database.$transaction(fn) does not open an interactive session. The callback describes a program: nothing has run when it returns. The whole description travels in one request, and the broker runs it inside a single transaction — committing when it finishes, rolling back on any failure.

import { Conflict, Database } from "@palbase/backend";

declare const userId: string;
declare const skus: string[];

const { orderId } = await Database.$transaction((tx) => {
  const order = tx.public.orders
    .insert({ user_id: userId, amount: "0", status: "reviewing" })
    .expectOne(new Conflict("order insert failed"));

  tx.public.order_items.insertMany(skus.map((sku) => ({ order_id: order.id, sku })));

  return { orderId: order.id };
});

Three rules follow from that model, and the first two are enforced by the compiler:

1. The callback is synchronous. async on it and await inside it are compile errors. There is nothing to await — no statement has run.
2. insert() hands back a handle, not a row. Reading a field requires .expectOne(err) first, which turns "what if the row is not there" into an argument you cannot route around.
3. A field you read is a Ref — a promise of a value the statement will produce when the plan runs. It can be written into a later operation and returned from the callback, but it cannot be branched on.

The handle the callback receives carries the schemas and nothing else: no $query, no findById, no findMany, no nested $transaction, no $asService. A read whose value the plan does not write belongs outside the transaction, where it costs one round trip and is an ordinary value you can branch on.

The table surface inside a plan

tx.public.<name> — and tx.<schema>.<name> — is a different surface from Database.public.<name>, and the difference is deliberate: there is no update({ where: { id }, … }) addressing a single row and no findById here.

Note: tx.tables.<name> still resolves, as an alias for tx.public.<name>, and it is deprecated. Write tx.public — Database.tables is gone from the direct surface, and using the alias here trains a spelling that does not compile on the other one.

The filter comes first in updateWhere because it is the dangerous half: an update whose where you got wrong rewrites rows you never looked at. lock: "update" takes a real FOR UPDATE row lock for the rest of the transaction.

Two expressions are available inside a plan — now(), and inc(by) / dec(by), which read the column's current value and are therefore valid only in an updateWhere set:

import { Database, NotFound, inc, now } from "@palbase/backend";

declare const sessionId: string;

await Database.$transaction((tx) => {
  tx.public.sessions.updateWhere({ id: sessionId }, { hits: inc(1), last_seen: now() }).expectOne(
    new NotFound("session not found"),
  );
});

Guards

Every operation returns a TxRows handle, and the guards on it are how a plan expresses a condition it checks as it runs:

A failed guard throws the error you supplied and rolls the whole plan back. That is the replacement for reading a value and branching on it: updateWhere({ id, accepted_at: null }, { … }).expectOne(new Conflict("already accepted")) is a race-free compare-and-set, evaluated where the rows are.

A Ref cannot be branched on

Coercing a Ref (String(ref), +ref, JSON.stringify(ref)), awaiting it, or letting one be stored as data all throw TxRefError at build time. Bare truthiness is the one hole that cannot be closed. JavaScript does not let a proxy trap it, so if (ref) takes the true branch for every Ref, always, and TypeScript is silent because a Ref is a perfectly good object:

const account = tx.public.accounts.select({ id }, { limit: 1 }).expectOne(e);
if (!account.balance) { /* ← ALWAYS false. Silently wrong data. */ }

If a decision depends on a real value, read it before the transaction and branch there, or express it as a guard.

The limits a plan must fit in

A plan is not shipped anywhere: it is built in your process and then executed on the request's own transaction, inside one SAVEPOINT. The bounds below are enforced by the builder, as you write it, so a plan that would not run is named at the line that assembles it rather than halfway through:

• a reference points only backwards, and only at an operation statically known to yield at most one row — which is why .expectOne() is always what produces a readable row;
• update and delete require a where; insert refuses one;
• every row of an insertMany must set the same columns;
• ≤ 1000 operations, and ≤ 5000 rows per insertMany.

Failures surface as TxRefError (a Ref used somewhere it cannot be) and TxPlanError (a plan the builder refuses to assemble). Both are exported from @palbase/backend.

$atomic() — a transaction of your own, with retries

$transaction is a savepoint inside the request's transaction. A savepoint keeps its parent's snapshot and cannot retry its parent's COMMIT, so $transaction(fn, { retry: n }) with a non-zero n is refused where you write it. When the work genuinely needs its own transaction boundary — a balance transfer, an idempotent claim, anything that has to survive a serialization failure — that is $atomic:

// modules/ledger/ledger.service.ts
import { Conflict, Database, Injectable, NotFound, decrement } from "@palbase/backend";

@Injectable()
export class LedgerService {
  withdraw(accountId: string, amount: string): Promise<{ id: string }> {
    return Database.$atomic(
      async (tx) => {
        // Ordinary async reads and writes — this callback is NOT a plan.
        const account = await tx.public.accounts.findById(accountId);
        if (!account) throw new NotFound("account not found");

        // The compare-and-set is still a plan, so the check happens where the rows are.
        return tx.$transaction((plan) =>
          plan.public.accounts
            .updateWhere({ id: accountId, balance: { gte: amount } }, { balance: decrement(amount) })
            .expectOne(new Conflict("insufficient balance")),
        );
      },
      { isolation: "serializable", retry: 3, lockTimeoutMs: 1000, statementTimeoutMs: 5000 },
    );
  }
}

Four things about that boundary:

• The callback is async — unlike a $transaction plan. Its promise resolves after COMMIT succeeds.
• retry defaults to 0 and is capped at 10. A silent default would run a non-idempotent operation twice without the caller knowing. Only serialization and deadlock failures are retried; a connection lost during COMMIT has an unknown outcome and is never replayed on its own — reconcile that with an idempotency key.
• Every retry reruns the whole callback, decision reads included, so keep external effects out of it. Write an outbox row inside and deliver after commit.
• It commits independently. A later handler error does not undo it, and it is not atomic with the request's own transaction — so do not write the same rows on both sides. For service-role work, choose the identity before entering: Database.$asService().$atomic(…).

AtomicOptions also carries readOnly, timeoutMs (a whole-scope deadline, queueing and retries included) and a signal.

withRetry — the same retry policy without a transaction

withRetry(fn, { retry }) reruns fn on the same narrow set of failures, for work that is not one transaction:

import { Database, withRetry } from "@palbase/backend";

const rows = await withRetry(() => Database.public.todos.findMany({ where: { done: false } }), {
  retry: 3,
});

isRetryable(e) is the predicate both use, and it is deliberately narrow: a unique violation is not in it, because retrying one produces the same answer forever.

Unique violations are typed

A write that duplicates a row raises UniqueViolation — a Conflict, so HTTP 409 — carrying the constraint Postgres named:

// modules/users/register.service.ts
import { Conflict, Database, Injectable, UniqueViolation } from "@palbase/backend";

@Injectable()
export class RegisterService {
  async register(email: string): Promise<{ id: string }> {
    try {
      return await Database.public.users.insert({ email });
    } catch (e) {
      if (UniqueViolation.is(e) && e.constraint === "users_email_key") {
        throw new Conflict("that email is taken", "email_taken");
      }
      throw e;
    }
  }
}

Warning: Use UniqueViolation.is(e), never e instanceof UniqueViolation. A controller bundle inlines its own copy of @palbase/backend and the engine that raises this error is the runtime's copy — two copies, two class identities, so instanceof is false in the one place you would write it. is() matches on shape and crosses that boundary. isRetryable, SerializationFailure and DeadlockDetected are exported beside it and follow the same rule.

The constraint name sits on e.constraint, for you. It is deliberately not in the message that reaches the client — an uncaught error becomes an HTTP response, and a constraint name there tells an end user how your schema is built.

The role every statement runs as

A request's transaction opens with a bind statement, and that statement is the whole of your backend's database identity:

select set_config('role', $1, true),
       set_config('search_path', 'public', true),
       set_config('request.jwt.claims', $2, true)

The values are bound parameters — user identity is never interpolated into SQL — and is_local => true scopes them to the transaction exactly like SET LOCAL. The role is backend_authenticated, and it is that role on every request, signed in or not; anonymous callers differ by their claims, not by their role. Policies you write must therefore name a role your backend actually runs as, which is what the Row-Level Security page is about.

$asService() — the RLS bypass

Database.$asService() returns a sibling with the same typed surface, running as backend_service_role, which carries BYPASSRLS. It is for the code that legitimately needs cross-user reach: an admin endpoint, a fan-out job, a cleanup sweep.

import { Database } from "@palbase/backend";

const everyones = await Database.$asService().public.todos.findMany({ where: { done: false } });

Four facts about it are worth carrying:

• It is a second transaction, on a second connection. The role reaches Postgres once, in the BEGIN's bind statement, and it is transaction-scoped — a sibling riding the request's own transaction would still run as backend_authenticated and $asService() would silently mean nothing. The separation is physical so that no statement of either surface can change what the other runs as.
• It carries the same request.jwt.claims. $asService() changes what the caller may touch, not who they are, so auth.uid() still resolves inside a trigger or a column default.
• One transaction per request, not per call. It opens lazily on first use — $asService() called and never used opens nothing — and every later call returns the same surface, so a loop cannot leak connections. Both transactions settle with the request, but they settle as two: they are not atomic with each other.
• There is no double bypass. The returned client does not re-expose $asService().

Warning: Do not write the same row from both surfaces in one request. Database.* holds a lock the service transaction cannot get until the request commits, and the request cannot commit until the handler returns. The service side gives up after 5 seconds with an error that names the cause, rather than hanging and holding two pool connections; the other direction has no such bound.

Inside $asService() your own code is the only authorisation layer left, so check the caller's right to the operation before reaching for it. Call it inline at the point of use rather than stashing the client in module scope — the whole value of the escape hatch is that a reviewer can grep for it.

Scoped CRUD — defineRepository

A repository class is usually the same two lines per method: add the owning column to the predicate, then unwrap the result. Writing that by hand is what makes it forgettable, and one forgotten predicate shows one household's row to another. defineRepository produces a base class that writes the predicate once.

import { Database, defineRepository } from "@palbase/backend";

export class EntryRepository extends defineRepository(
  Database.public.entries,
  { tenant: "household_id" },
) {}

The option names the column every row is scoped by — household_id above, org_id, workspace_id, whatever your schema calls it. The subclass gets six typed methods, each taking that scope value first: list, find, insert, update, updateScoped and delete. Add your own methods in the subclass body as usual.

insert does not take the scope column in its payload — the repository writes it. update throws NotFound when nothing matches, so the caller needs no null branch; updateScoped runs the same predicate and returns null instead, for when absence is a value. The base constructor takes no arguments, so the container resolves the subclass by its own name.

The row key defaults to id but is not fixed — defineTable does not require an id column, so name it when the primary key sits elsewhere:

import { Database, defineRepository } from "@palbase/backend";

export class DocRepository extends defineRepository(
  Database.public.docs,
  { tenant: "org_id", key: "slug" },
) {}

The key parameter carries that column's own type, so a numeric key is typed number rather than assumed to be a string.

Related

• Schema — defineSchema, the column builders, and where Database.public.* gets its types
• Row-Level Security — why RLS is on by default, and what a policy-less table returns
• Schema changes — how a change to db/public.ts reaches a database
• Controllers & Routing — the classes these examples live in
• Errors — NotFound, Conflict, and the wire envelope a thrown error produces
• Database — palbase db plan, db apply and db query against the stack on your machine
• Documents — the schemaless alternative for document-shaped data