# Transactions

Running work in a transaction, how nesting behaves, and how the connection
pool is sized.

## Formatting

Write every insert with one column per line, `) values (`, then one value per
line in the same order, so the nth value sits under the nth column whatever the
number of columns. Other queries are laid out by clause; see
`elements man start/conventions`.

```ts One per line
sql(`
  insert into signups (
    name,
    email,
    fileName,
    contentType,
    size
  ) values (
    ${name},
    ${email},
    ${file?.name},
    ${file?.contentType},
    ${file?.size}
  )
`);
```

Now the mapping reads straight down, and adding a column is a one-line diff on
each side instead of a reflow of both.

## Transactions

`tx(fn)` runs `fn` inside a Postgres transaction. The transaction commits when
the callback returns and rolls back if the callback throws.

```ts Transaction
import { tx, sql } from "@elements/app";

tx(() => {
  let user = sql<User>(`
    insert into users (
      name
    ) values (
      ${name}
    )
    returning *
  `).firstOrThrow();

  sql(`
    insert into auditLog (
      event,
      userId
    ) values (
      'user_created',
      ${user.id}
    )
  `);
});
```

The connection that opens the transaction is held for the duration of the
callback. Every `sql()` call inside `tx()` runs on that same connection
automatically, so you do not have to pass a transaction object through your
functions or thread a context.

Nesting `tx()` is fine. A `tx()` called inside another `tx()` joins the
transaction already in progress: no new transaction opens, and the outermost
`tx()` owns the single commit or rollback. A helper that uses `tx()` composes
into a larger `tx()` without change.

```ts Nested tx
tx(() => {
  let user = createUser(name);
  sql(`
    insert into orgs (
      ownerId
    ) values (
      ${user.id}
    )
  `);
});

function createUser(name: string) {
  return tx(() => {
    return sql<User>(`
      insert into users (
        name
      ) values (
        ${name}
      )
      returning *
    `).firstOrThrow();
  });
}
```

Because there is one transaction, an unhandled error anywhere inside rolls the
whole thing back. Everything inside succeeds together or not at all.

`txAsync(fn)` is the explicit async variant. Use it only when you need explicit
promise control inside the callback.

### A Dedicated Connection

`tx()` and `sql()` run on the app's ambient connection. When you need a
connection of your own for work that must run or persist outside the surrounding
transaction, such as audit logging, check one out with `checkoutDb()`.

```ts Own connection
import { checkoutDb } from "@elements/app";

using db = checkoutDb();

db.tx(() => {
  db.sql(`
    insert into audit (
      event
    ) values (
      'exported'
    )
  `);
});
```

The handle has `db.sql()` for queries and `db.tx()` for transactions. Savepoints
and other advanced control are raw SQL you write on it, for example
`db.sql("savepoint sp1")`. Declare it with `using` so the connection returns to
the pool when the block ends.

## Connection Pool

The app server maintains a Postgres connection pool. The pool eagerly opens its
first connection at app startup so a connectivity problem fails fast instead of
surfacing on the first request.

Every `sql()` call checks out a connection from the pool, runs the query, and
returns the connection. A `tx()` callback checks out a single connection at the
start of the block and holds it for the entire callback, so every `sql()` inside
reuses that one connection. The connection returns to the pool when the
transaction commits or rolls back.

Keep transactions to the database writes that need to be atomic. Do long-running
work like network calls or file processing outside the `tx()` block so the
connection returns to the pool quickly.