Transactions

Inside a single database session you can use ordinary PostgreSQL transactions. Different sessions mean separate transactions.

What is supported

const s = await env.db.session();
try {
  await s.exec("begin");
  await s.exec("update accounts set balance = balance - $1 where id = $2", [100, 1]);
  await s.exec("update accounts set balance = balance + $1 where id = $2", [100, 2]);
  await s.exec("commit");
} catch (e) {
  await s.exec("rollback");
  throw e;
} finally {
  await s.close();
}

BEGIN, COMMIT, ROLLBACK
SAVEPOINT and savepoint rollback
SET TRANSACTION ISOLATION LEVEL
The transaction APIs of all six ORMs
SET, temporary tables, and PREPARE within a session

Caution: splitting calls splits the transaction

A transaction does not survive between two env.db.exec calls. Each call uses a new session, so BEGIN does not apply to the next call. The same is true of pool.query() in @runlot/pg. Use pool.connect() for transactions.

Time functions

now() returns the time the transaction started. Calling it several times in the same transaction gives the same value. If you need the current time, use clock_timestamp().

Do not hold transactions open

Database requests are handled one at a time per project. A long transaction makes the project's other requests wait. Avoid calling external APIs inside a transaction.

After 30 seconds without a SQL statement, the session is reclaimed and any open transaction is rolled back. We recommend opening and closing the session within a single request.

Branching on error codes

const r = await s.tryExec("insert into users (email) values ($1)", [email]);
if (!r.ok && r.error.code === "23505") {
  await s.exec("rollback");
  return new Response("That email is already registered", { status: 409 });
}

Only Prisma translates unique constraint violations into P2002. The other ORMs return SQLSTATE 23505 as-is.