Transactions

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

Formatting

Short statements read fine on one line. Past three or four columns they don't. a wrapped line of columns followed by a wrapped line of values leaves you counting positions to see which value belongs to which column.

Put one per line and let the two lists line up:

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.

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.

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().

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.