# Handlers

Taking over a read or a write with your own SQL, and where authorization goes.

## Mutators

Each of `insert`, `update`, and `delete` is one function. It receives the row
and returns the row it wrote. `session` is in context, so authorization is the
first line of the handler, the same as in an `@rpc`.

```ts
import { LiveTable, session, sql } from "@elements/app";

export let comments: LiveTable<Comment> = new LiveTable<Comment>({
  insert: (item) => {
    session.isLoggedInOrThrow();
    return comments.insert(item);
  },

  update: (item) => {
    if (item.author !== session.get("userName")) {
      throw new ForbiddenError();
    }

    return comments.update(item);
  },

  delete: (item) => {
    session.isLoggedInOrThrow();
    comments.delete(item);
  },
});
```

`comments.insert(item)` on the declaration is the raw auto-SQL: one `insert
... returning` against the table, no handler, no broadcast. It is what runs
when you declare no handler, and what a handler calls when it only wants to
gate the default. `comments.update` and `comments.delete` are the same for the
other two. Because the handler names the variable it is assigned to, the
declaration needs its type written out, `let comments: LiveTable<Comment>`.

When a handler is omitted, the mutator is open and uses auto-SQL. A handler
that should never be reached throws:

```ts
delete: () => { throw new ForbiddenError(); },
```

Thrown errors reach the browser as the same class: `AuthError`,
`ForbiddenError`, and `ValidationError` are safe and carry their message, and
anything else is wrapped in `ServerError`. See `rpc` for catching them.

## Custom SQL

Auto-SQL covers most cases. Write the SQL yourself when the write derives a
column from `session`, touches several rows, or targets a join.

```ts
export let comments: LiveTable<Comment> = new LiveTable<Comment>({
  insert: (item) => {
    session.isLoggedInOrThrow();

    return sql<Comment>(`
      insert into comments (id, text, author, postId, createdAt)
      values (${item.id}, ${item.text}, ${session.getOrThrow("userName")}, ${item.postId}, now())
      returning *
    `).firstOrThrow();
  },
});
```

`item` is `Partial<Comment>` for insert and `Comment` for update and delete;
you do not annotate it.

Two things a custom insert must do. It must preserve `item.id`: the browser
minted it, the optimistic row carries it, and if the stored row comes back
with a different id the broadcast cannot find the row it belongs to and the
list shows two. And it must return the row: the return value is what the server
broadcasts, and it has to carry the partition columns and, on a windowed view,
the `orderBy` columns, so the browser can place it. `returning *` satisfies
both.

## Select

`select` is the initial query. It receives the partition the view was opened
with and the window, and returns the rows.

```ts
select: ({ postId }) => sql<Comment>(`
  select c.id, c.text, c.author, c.createdAt, c.postId, u.avatarUrl
  from comments c
  join users u on u.id = c.userId
  where c.postId = ${postId}
`)
```

Use it for joins, projections, and computed columns. Every row it returns is
checked against the partition, so a select that ignores the object it was
given is refused rather than served. A windowed view also hands the select its
order, limit and cursor, which the query must honor; see `windows` for the
three fragments that make that one line each.

A select may filter rows further than the partition, `where archived = false`
say, to keep the first render small. Know what that buys and does not: the
stream is defined by the channel, so a row broadcast on the partition still
reaches the browser whether or not the select would have returned it. The
template's `filter()` is the rule; the select's WHERE is an optimization of
the snapshot. See `partitions`.

## Composite tables

`table` names the database table the automatic handlers use. To back a
LiveTable with a view, a join, or any custom SQL, override `select` and the
mutators as needed. The rows still need an `id`, and mutations still return the
row they wrote.