SQL

Writing queries with the sql tag, the sync-style async transform, and camelCase column mapping.

Signature
sql<T>(text: string, args?: unknown[]): SqlResult<T>

Write SQL as a template string and interpolate any runtime value with ${value}.

Example
let user = sql<User>(`
  select *
  from users
  where id = ${id}
`).first();
let active = sql<User>(`
  select *
  from users
  where
    active = ${true}
    and orgId = ${orgId}
`);

The build extracts each ${value} into a positional parameter at compile time. The runtime call becomes text + args and Postgres parameterization happens at the protocol layer, so interpolations are always safe from injection.

sql() runs on the server, and the compiler enforces it. Browser code, including event handlers and template expressions, reaches the database only by calling an @rpc function; a sql() call anywhere else in browser code is a compile error at the call site. The code that calls sql() exists only on the server and is never sent to the browser, so neither are your queries or your schema.

The return value is a SqlResult<T>: an iterable wrapper over the rows. There is no model layer, no class instances, and no lazy loading. The generic renames the result type for the type checker and does not change runtime behavior.

Iterating results
let users = sql<User>(`
  select
    id,
    name,
    email
  from users
`);
for (let u of users) {
  console.log(u.name);
}
users.all().map(u => u.name);

The result exposes:

• .first(): the first row, or undefined.
• .last(): the last row, or undefined.
• .all(): every row as a plain array.
• .length / .size: the row count.
• .empty(): true when there are no rows.
• .firstOrThrow(error?) / .lastOrThrow(error?) / .allOrThrow(error?): same as the above, but throws NotFoundError (a safe 404) if empty. A string is the message for the NotFoundError; an Error instance is thrown as given.

Result methods
sql<User>(`
  select *
  from users
  where id = ${id}
`).first();
sql<Event>(`
  select *
  from events
  order by createdAt desc
`).last();
sql<User>(`select * from users`).all();

// throws NotFoundError to the client if the row is missing
let user = sql<User>(`
  select *
  from users
  where id = ${id}
`).firstOrThrow();

// your own message, or your own error when a 404 is the wrong answer
sql<Job>(`
  select *
  from jobs
  where id = ${id}
`).firstOrThrow("No such job.");
sql<Invite>(`
  select *
  from invites
  where
    token = ${token}
    and expiresAt > now()
`).firstOrThrow(new ValidationError("That invite has expired."));

Sync-Style and the Async Transform

sql() is written sync-style. At build time the compiler rewrites each call to await sqlAsync() and propagates async up the call stack. Every function that transitively calls sql() becomes async without you typing anything. The code reads as a sequence of statements and runs async underneath.

When you need the promise itself, import sqlAsync or txAsync and await them yourself. This is the tool for running queries at the same time with Promise.all. Awaiting another library's promise does not need it: mark the function async, await that call, and keep writing sql() as usual.

Concurrent reads
import { sqlAsync } from "@elements/app";

let [users, posts] = await Promise.all([
  sqlAsync<User>(`select * from users`),
  sqlAsync<Post>(`select * from posts`),
]);

camelCase

By convention, write camelCase identifiers in your SQL. Elements converts to snake_case at the database boundary and back to camelCase on results, so the SQL reads like the TypeScript that surrounds it. You write one casing throughout the codebase.

In the interactive psql shell, write the stored snake_case names (created_at). What you type there reaches psql as typed.

camelCase
let user = sql<User>(`
  insert into users (
    firstName,
    createdAt
  ) values (
    ${name},
    ${now}
  )
  returning *
`).firstOrThrow();

user.firstName;
user.createdAt;

// On the wire to Postgres:
// INSERT INTO users (first_name, created_at) VALUES ($1, $2) RETURNING *

The same convention applies in LiveTable config, in migrations, and anywhere SQL meets TypeScript. Elements handles the boundary.

elements db -sql and a sql file piped into elements db are converted too. The one place it does not apply is the interactive psql shell, where you write the snake_case names stored in the database. See elements man database/cli.

Names passed as strings

A few functions take a relation or column name as a string rather than as an identifier. Those strings are names, and they convert like names, so one spelling works everywhere:

Names as strings
sql(`create sequence orderNumberSeq start with 1`);   // stored as order_number_seq

sql(`select nextval('orderNumberSeq') as n`);         // 1
sql(`select nextval('order_number_seq') as n`);       // 2, the stored spelling also works
sql(`select 'orderNumberSeq'::regclass`);             // resolves

This applies to nextval, currval, setval, to_regclass, to_regtype, pg_get_serial_sequence, and a ::regclass or ::regtype cast. Only the name argument of those calls converts, and only at that call's own nesting level.

Every other string is a value and is passed through exactly as you wrote it:

Values
// 'myKind' stays 'myKind'
sql(`
  select *
  from items
  where kind = ${kind}
`);

sql(`select concat('aB', 'cD')`); // untouched

Raw SQL

Every ${} in a sql() template becomes a parameter, which is what keeps a query safe. It also means a query cannot take an ORDER BY column, an optional WHERE clause, or a table name from a variable: Postgres will not accept an identifier as a parameter. sql.raw is the one way to say "this is query text". Its text is spliced into the surrounding query, and the ${} inside it are still parameters.

sql.raw
let newerThan = cursor ? sql.raw(`and createdAt < ${cursor}`) : sql.raw(``);
let order = sql.raw(`createdAt desc, id desc`);

sql<Post>(`
  select *
  from posts
  where authorId = ${authorId} ${newerThan}
  order by ${order}
`);

The rule is one sentence: a value is a parameter, a sql.raw is SQL. Only text written in the sql.raw literal is ever spliced, so a string that came from a request can no more reach the query text here than it can through sql(). Fragments nest, and camelCase inside them is converted like the rest of the query. A windowed LiveTable hands its custom select three of these; see livetable/windows.