Skip to content
Tutorials Center
My learning

Topics

Node.js Reference ยท node:sqlite and Runtime Services

Transactions in node:sqlite

How to use transactions with node:sqlite: BEGIN, COMMIT and ROLLBACK, a reusable helper, savepoints for nesting, BEGIN IMMEDIATE and isTransaction.

A transaction groups several SQL statements into one unit that either happens completely or not at all. Without one, every statement is committed on its own: a failure halfway through a multi-step change leaves the database half-updated, and each separate write pays for its own disk sync.

node:sqlite has no transaction() helper. You start a transaction by running BEGIN with database.exec(), finish it with COMMIT (keep the changes) or ROLLBACK (discard them), and use database.isTransaction to check whether one is open. The usual pattern is a small wrapper function that always ends the transaction one way or the other.

NODE
db.exec("BEGIN");
try {
  // several writes here
  db.exec("COMMIT");
} catch (error) {
  db.exec("ROLLBACK");
  throw error;
}

Try it

Node.js A transaction helper that rolls back on error

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Node.js Savepoints, nesting and a failed COMMIT

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Syntax

Statement Effect
BEGIN or BEGIN DEFERRED Starts a transaction. The lock is taken at the first read or write.
BEGIN IMMEDIATE Starts a transaction and takes the write lock now, so a competing writer fails (or waits) at the start, not in the middle.
BEGIN EXCLUSIVE Like IMMEDIATE, and in rollback-journal mode also blocks readers.
COMMIT (or END) Makes all changes permanent.
ROLLBACK Discards every change made since BEGIN.
SAVEPOINT name Opens a named point. Starts a transaction when none is open.
ROLLBACK TO name Undoes changes back to the savepoint and keeps the savepoint open.
RELEASE name Folds the savepoint into the enclosing transaction (or commits it when it started one).
NODE
database.isTransaction // boolean

Errors. BEGIN inside a transaction throws ERR_SQLITE_ERROR with "cannot start a transaction within a transaction". COMMIT or ROLLBACK with no open transaction throws "cannot commit - no transaction is active" (or "cannot rollback ..."). A failed statement inside a transaction does not close it, so your code must call ROLLBACK. A COMMIT can fail, for instance on a deferred foreign key, and then the transaction stays open until you roll back.

Usage notes

  • Never await inside the transaction function. node:sqlite is synchronous, and the connection is shared by the whole process. If you yield to the event loop between BEGIN and COMMIT, other code can run on the same connection and its writes become part of your transaction.
  • Batch writes. Without a transaction each INSERT is its own transaction and its own disk sync. Wrapping thousands of inserts in one transaction is typically many times faster.
  • Schema changes roll back too. CREATE TABLE and ALTER TABLE are transactional in SQLite, which makes migrations safe: a failure leaves the schema untouched.
  • Use BEGIN IMMEDIATE for transactions that will write. A deferred transaction that reads first and writes later can fail with "database is locked" when it tries to upgrade its lock. Set a busy timeout (the timeout constructor option or PRAGMA busy_timeout) to let SQLite wait briefly.
  • With a single connection to a single in-memory database there is no concurrency to worry about, only the rule about not yielding in the middle.
  • Check db.isTransaction before issuing ROLLBACK in cleanup code, so a rollback after a failed BEGIN does not hide the real error.
  • Closing a database with an open transaction discards that transaction.
  • Always keep the transaction short: it holds a lock that blocks other writers.