# SQL

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

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

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

```ts
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()` must be called from the server. If browser code calls it without going
through an `@rpc`, you get a compile error pointing at the call site.

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.

```ts
let users = sql<User>(`select id, name, email from users`);
for (let u of users) { /* ... */ }
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.

```ts
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 explicit promise control, import `sqlAsync` or `txAsync` directly
and use `async`/`await` as normal. This is the right tool for concurrent reads
with `Promise.all`, or for interleaving SQL with awaited third-party calls. The
build leaves your manual `async`/`await` alone.

```ts
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.

The conversion belongs to `sql()`, so it does not apply in a psql shell.
`elements db -sql "select * from todos order by createdAt"` fails with
`column "createdat" does not exist`: there, write the real column name,
`created_at`.

```ts
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:

```ts
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:

```ts
sql(`select * from items where kind = ${kind}`);      // 'myKind' stays 'myKind'
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.

```ts
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`.