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

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.