Postgres queries

Import Bun's shared SQL client and query the application's schema directly:

import { sql } from "bun";

Cloud does not add an ORM. Keep SQL in the service that owns the domain operation.

Identify application connections

defineApp() adds application_name=cloud:<app-id> to the configured Postgres connection URL when no application name is supplied. This identifies the app in Postgres connection diagnostics. An existing application_name or options URL parameter remains operator-owned, and TLS connection settings are preserved.

Call defineApp() before using Bun's default SQL pool, including reading sql.options. Perform startup queries in the application's lifecycle hooks. Bun initializes the pool configuration on first use, so a pool initialized before defineApp() keeps its existing name. Cloud does not replace the pool or rename an individual active connection. Separately constructed new SQL() clients with explicit URLs or options retain their own configuration.

The standalone Gateway router does not use defineApp(), so its connection name must be supplied in its Postgres URL if attribution is needed.

Map database rows

Keep database column names out of public contracts:

type DbInventoryItem = {
  id: string;
  name: string;
  quantity: number;
  created_at: Date | string;
};

type InventoryItem = {
  id: string;
  name: string;
  quantity: number;
  createdAt: string;
};

const mapInventoryItem = (
  row: DbInventoryItem,
): InventoryItem => ({
  id: row.id,
  name: row.name,
  quantity: row.quantity,
  createdAt: new Date(row.created_at).toISOString(),
});

Select the columns the mapper needs:

const rows = await sql<DbInventoryItem[]>`
  SELECT id, name, quantity, created_at
  FROM inventory.items
  WHERE id = ${itemId}::uuid
`;

const item = rows[0] ? mapInventoryItem(rows[0]) : null;

The tagged template sends interpolated values as parameters. Do not assemble SQL with string concatenation.

Insert and update rows

Use RETURNING when the service needs the stored row:

const [row] = await sql<DbInventoryItem[]>`
  INSERT INTO inventory.items (name, quantity)
  VALUES (${input.name}, ${input.quantity})
  RETURNING id, name, quantity, created_at
`;

Check the returned row before mapping it.

Turn expected constraint failures into a service result:

import { isUniqueViolation } from "@k2b/cloud/services";
import { err, fail } from "@k2b/cloud/server";

try {
  // insert
} catch (error) {
  if (isUniqueViolation(error)) {
    return fail(err.conflict("Inventory item name"));
  }
  throw error;
}

Do not return raw database errors to the client.

Build text filters

Escape user text before adding wildcard characters:

import { escapeLikePattern } from "@k2b/cloud/services";

const search = input.search?.trim().toLowerCase();
const pattern = search
  ? `%${escapeLikePattern(search)}%`
  : null;

const rows = await sql<DbInventoryItem[]>`
  SELECT id, name, quantity, created_at
  FROM inventory.items
  WHERE (
    ${pattern}::text IS NULL
    OR LOWER(name) LIKE ${pattern} ESCAPE '\'
  )
`;

escapeLikePattern() escapes %, _, and \. It does not add the wildcard characters.

Pass arrays

Bun SQL does not serialize empty JavaScript arrays for every Postgres array operation. Use the Cloud helpers:

import {
  toPgIntArray,
  toPgTextArray,
  toPgUuidArray,
} from "@k2b/cloud/services";

const ids = toPgUuidArray(input.ids);
const labels = toPgTextArray(input.labels);

const rows = await sql<DbInventoryItem[]>`
  SELECT id, name, quantity, created_at
  FROM inventory.items
  WHERE (
    ${input.ids.length} = 0
    OR id = ANY(${ids}::uuid[])
  )
    AND (
      ${input.labels.length} = 0
      OR labels && ${labels}::text[]
    )
`;

The helpers return a valid empty Postgres array when the input is empty.

Control ordering

Values can be parameters. Column names and keywords cannot.

Select SQL fragments from a closed set:

const orderBy =
  input.sort === "quantity"
    ? sql`quantity`
    : sql`LOWER(name)`;

const direction =
  input.direction === "desc"
    ? sql`DESC`
    : sql`ASC`;

const rows = await sql<DbInventoryItem[]>`
  SELECT id, name, quantity, created_at
  FROM inventory.items
  ORDER BY ${orderBy} ${direction}, id
  LIMIT ${input.limit}
  OFFSET ${input.offset}
`;

Validate sort and direction before the service receives them. See Typed HTTP APIs.

For keyset pagination on a timestamptz column, select the cursor timestamp as SQL text with full precision, for example to_char(created_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'). Preserve that string in the opaque cursor and cast it back with ::timestamptz for the next page's comparison. Include a unique tie-breaker such as id in both the ordering and the cursor. JavaScript Date keeps only milliseconds; using it for a cursor can skip or repeat rows stored within the same millisecond. Public display timestamps can still use the row mapper above.

Load relations in batches

Do not query one relation for every result row.

Collect the IDs, load all related rows with ANY(...), and group them in memory:

const itemIds = items.map((item) => item.id);
const ids = toPgUuidArray(itemIds);

const labels = await sql<{
  item_id: string;
  label: string;
}[]>`
  SELECT item_id, label
  FROM inventory.item_labels
  WHERE item_id = ANY(${ids}::uuid[])
`;

An empty result page should skip the relation query.

Keep access checks in the query

List and search queries must apply resource access before sorting and pagination.

Use buildAccessPrincipalCondition() instead of loading every row and checking it in JavaScript. Reject a resource-bound credential or restrict the query to its exact resource. See Resource authorization.

Use Pagination and filtering for the HTTP pagination contract.

The complete Inventory data example is checked by TypeScript.