# 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(msg?)` / `.lastOrThrow(msg?)` / `.allOrThrow(msg?)`: same as
  the above, but throws `NotFoundError` (a safe 404) if empty.

```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();
```

## 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.

The one place this conversion does not apply is `psql`. `elements db` and its
`-sql` flag send text straight to Postgres, so there you write the snake_case
names stored in the database. See **elements db → Shell** below.