Querying with SQL

sql from platform:stdlib is the single entry point for reading and writing data. It talks to real PostgreSQL — no query builder, no ORM dialect.

import { sql, tableRef } from "platform:stdlib";
import { Issue } from "platform:entities";

const i = tableRef(Issue);

const { rows } = await sql.query<Issue>(`
  SELECT ${i.issue_id}, ${i.title}
  FROM   ${i}
  WHERE  ${i.status} = 'new'
`);

tableRef

tableRef(Entity) returns a proxy that renders as the table name, and whose properties render as fully-qualified column references.

const i = tableRef(Issue);

`${i}`           // the table
`${i.title}`     // the title column, table-qualified

The value is what makes the SQL schema-checked: i.titel is a TypeScript error, not a runtime failure in production. Rename a field and every query referencing it stops compiling.

Tip

Alias the ref to something short (i, u, co). It appears in every line of the query, and a two-character name keeps the SQL readable.

Interpolating values

Never interpolate user input directly into the template string. Use bound parameters, passed as the second argument:

const { rows } = await sql.query<Issue>(
  `SELECT * FROM ${i} WHERE ${i.status} = $1`,
  [status],
);

For an IN list, sql.delimit escapes each value and joins with commas:

const { rows } = await sql.query<Issue>(`
  SELECT * FROM ${i}
  WHERE  ${i.status} IN (${sql.delimit(["new", "assigned"])})
`);

Injection

${i.title} is safe because it is a column reference the type system produced. ${userInput} is not. Anything that came from a request body, a form, or a record field belongs in $1 or sql.delimit, never inline.

Writing data

DML methods take arrays of entity instances. Each returns a DmlResult[] parallel to the input.

Method	Behavior
sql.insert(records)	Insert; returns per-record results
sql.update(records)	Update by record_id
sql.upsert(records)	Insert or update on primary key
sql.delete(recordsOrIds)	Delete by record or by id string

const issue = new Issue();
issue.title = "Disk nearly full";
issue.component_id = componentId;

await sql.insert([issue]);

Passing an empty array is a no-op that returns [] without a round trip, so you do not need to guard the call.

Failing loudly

The plain methods report failure in their results rather than throwing — you must inspect them. Each has an OrThrow variant that raises DmlBatchError instead:

await sql.insertOrThrow([issue]);
await sql.updateOrThrow(records);
await sql.upsertOrThrow(records);
await sql.deleteOrThrow(ids);

Prefer OrThrow unless you intend to handle partial failure. To check manually:

import { assertDmlSuccess } from "platform:stdlib";

const results = await sql.insert(records);
assertDmlSuccess(results); // throws DmlBatchError if any failed

System mode

sql.systemMode bypasses PostgreSQL row-level security. Everything else is identical — same methods, same signatures.

const all = await sql.systemMode.query<Issue>(`SELECT * FROM ${i}`);
await sql.systemMode.upsertOrThrow(records);

Use it when code must act on records the triggering user cannot see: a nightly sweep, a cross-tenant rollup, an escalation that touches another team’s rows.

System mode is the sharp edge

Regular sql is filtered by sharing rules, so a bug leaks nothing. systemMode sees every row. Reach for it only when the operation genuinely has no user context, and keep its blast radius small.

Other helpers

await sql.validate(query);  // parse and check without executing
await sql.schema();         // live table and column metadata

sql is also callable directly as shorthand for sql.query:

const { rows } = await sql(`SELECT 1`);

Limits

query takes an options object for per-call caps:

await sql.query<Issue>(text, [], { rowLimit: 500, timeout: 5000 });

Defaults come from server configuration — 1,000 rows and a 30-second timeout, with a 10,000-row hard ceiling.