Transactions

transaction(fn) commits when fn resolves and rolls back when it throws. Inside it, the plain client joins the transaction too, so services and repositories take part without being handed a transaction object.

Basics

transaction(fn) passes fn a client bound to the transaction. It commits on success and rolls back on throw:

await this.db.transaction(async (tx) => {
  const user = await tx
    .insertInto('users')
    .values({ email })
    .returningAll()
    .executeTakeFirstOrThrow()

  await tx.insertInto('profiles').values({ userId: user.id }).execute()
})

Set an isolation level with the options form:

await this.db.transaction({ isolation: 'serializable' }, async (tx) => {
  // ...
})

readOnly: true starts it read-only (Postgres, MySQL): every read sees one consistent view, and a write fails. It suits reports and exports that must not change anything. SQLite has no read-only transactions, so it's refused there.

const report = await this.db.transaction({ readOnly: true, isolation: 'repeatable read' }, () =>
  buildReport(this.db),
)

Savepoints

Inside a transaction, savepoint(fn) creates a nested rollback boundary — a throw inside rolls back only the savepoint:

await this.db.transaction(async (tx) => {
  await tx.insertInto('users').values({ email }).execute()

  await tx.savepoint(async (sp) => {
    await sp.insertInto('audit').values({ action: 'create' }).execute()
    // a throw here rolls back only the audit insert
  })
})

Transactions follow the call chain

Inside transaction(fn), the plain client joins the transaction too — a repository or service that only holds the injected db writes inside the transaction its caller opened, without being handed tx:

await this.db.transaction(async () => {
  const user = await this.users.create({ email }) // uses this.db internally
  await this.profiles.create({ userId: user.id }) // same transaction
})

db.inTransaction says whether the current call chain is inside one. Concurrent requests each get their own — the transaction follows the async call chain, not the client.

Calling transaction() while one is already open runs according to nested:

nested	Behaviour
'reuse' (default)	runs inside the open transaction; the outer one commits or rolls back
'savepoint'	runs behind a savepoint, so a throw undoes only this part
'separate'	opens an independent transaction on another connection — commits even if the outer rolls back (not on SQLite)

await this.db.transaction({ nested: 'separate' }, async () => {
  await this.audit.record('login attempt') // kept even if the caller's transaction fails
})

Durable: commit means committed

Because transactions follow the call chain, a transaction() called from code that is already inside one joins it. Its work commits only when the outermost transaction does, and is lost if that one rolls back. Usually that's what you want. Sometimes it isn't: a payment must be recorded before the confirmation email goes out, whatever the caller does next.

durable: true makes that a rule. The transaction must be the outermost one, and it throws if it is called inside an open transaction:

async recordPayment(payment: NewPayment) {
  await this.db.transaction({ durable: true }, async () => {
    await this.db.insertInto('payments').values(payment).execute()
  })
  // Here the payment is committed — safe to send the receipt.
}

Called from inside another transaction, recordPayment fails straight away instead of quietly joining it, which would hide the bug until a rollback lost a payment. A durable transaction can still open savepoints and nested transactions of its own. On Postgres, { durable: true, nested: 'separate' } runs on its own connection and commits independently, even inside another transaction.

A transaction you finish yourself

transaction(fn) can't leave a transaction open, so prefer it. When one has to outlive a callback — a test that rolls back everything it wrote, a transaction handed across layers — startTransaction() returns a client for it, with commit() and rollback():

const tx = await this.db.startTransaction({ isolation: 'serializable' })
try {
  await tx.insertInto('orders').values(order).execute()
  await tx.afterCommit(() => this.mailer.sendReceipt(order))
  await tx.commit()
} catch (err) {
  await tx.rollback()
  throw err
}

It takes transaction()'s options except retry, fires the same events, runs its afterCommit hooks on commit, and takes savepoint() and nested transaction() calls. Inside a callback transaction it's refused — nest with savepoint() there. A second commit() or rollback() throws.

After commit

afterCommit(fn) runs fn once the transaction commits — send the email, publish the event — and drops it if the transaction (or the savepoint it was registered in) rolls back. Outside a transaction it runs right away. A hook that throws is reported to the error observers; the transaction stays committed.

await this.db.transaction(async () => {
  const order = await this.orders.place(cart)
  await this.db.afterCommit(() => this.mailer.sendReceipt(order))
})

Retrying

Under serializable (and sometimes repeatable read) a transaction can fail because a concurrent one conflicted with it, and deadlocks cancel one side. Both are safe to run again — pass retry:

await this.db.transaction({ isolation: 'serializable', retry: true }, async () => {
  // runs again, from the start, on SerializationFailureError or DeadlockError
})

retry: true is three attempts; retry: 5 sets the count, and { attempts, baseDelayMs, maxDelayMs } the backoff (exponential with jitter, 20 ms up to 1 s by default). Only errors with retryable: true are retried; anything else throws at once. The callback runs once per attempt, so keep side effects outside the database in afterCommit. retry and isolation apply when a transaction starts — a nested 'reuse' runs inside the outer one, and its failure retries the outer transaction if that one has retry. Each retry fires a transactionRetry event with the attempt, error and delay.

Work that outlives the transaction

A promise started inside transaction() but not awaited can still be running after the commit. From then on it's no longer in the transaction: db.inTransaction is false, and a query it runs throws TransactionFinishedError. await every query inside the callback, and put work meant for after the commit in afterCommit.

Related

• Errors — SerializationFailureError, DeadlockError and other retryable failures
• Testing with kick/db — a transaction per test
• Multi-tenancy → Row-level security — a transaction per request