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.
db.exec("BEGIN"); try { // several writes here db.exec("COMMIT"); } catch (error) { db.exec("ROLLBACK"); throw error; }
Try it
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.
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). |
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
awaitinside the transaction function.node:sqliteis synchronous, and the connection is shared by the whole process. If you yield to the event loop betweenBEGINandCOMMIT, other code can run on the same connection and its writes become part of your transaction. - Batch writes. Without a transaction each
INSERTis 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 TABLEandALTER TABLEare transactional in SQLite, which makes migrations safe: a failure leaves the schema untouched. - Use
BEGIN IMMEDIATEfor 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 (thetimeoutconstructor option orPRAGMA 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.isTransactionbefore issuingROLLBACKin cleanup code, so a rollback after a failedBEGINdoes 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.