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.

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.

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.

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

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.