Transactions

A SQLite transaction groups related statements into one unit of work. If the transaction commits, SQLite keeps the changes. If it rolls back, SQLite discards them. For example, a transfer between accounts should not leave only one balance updated.

Run a transaction

In NitroSQLite, use db.transaction() when several statements must succeed or fail together. It starts a SQLite transaction and passes a transaction object to an async callback. The callback's return value becomes the value of the transaction promise.

const transferId = await db.transaction(async (tx) => {
  tx.execute('UPDATE accounts SET balance = balance - ? WHERE id = ?', [25, 1])
  tx.execute('UPDATE accounts SET balance = balance + ? WHERE id = ?', [25, 2])

  const { insertId } = await tx.executeAsync(
    'INSERT INTO transfers (amount) VALUES (?)',
    [25],
  )

  return insertId
})

Commit and rollback behavior

When the callback resolves, the wrapper commits unless you already called tx.commit() or tx.rollback(). When it rejects or throws, the wrapper rolls back unless the transaction has already been finalized. An explicit tx.rollback() does not itself reject the callback: it ends the transaction, and the callback can still resolve. Calls to tx.execute, tx.executeAsync, tx.commit, or tx.rollback after finalization throw.

A failed COMMIT can leave SQLite's transaction active, for example when a deferred foreign-key constraint fails. NitroSQLite marks a commit complete only after SQLite accepts it. A failed manual or automatic commit triggers rollback and rejects with the commit error. This also happens if the callback catches a failed tx.commit() and returns without explicitly rolling back. After the failure, only tx.rollback() is allowed on that transaction handle.

If rollback also fails, the rejection preserves the original message and appends the rollback error. Its cause is an AggregateError containing the normalized primary and rollback errors, in that order. A callback that catches a failed manual rollback still causes the transaction promise to reject. A failed BEGIN does not roll back a transaction that the wrapper did not start.

Keep operations inside the callback

The connection queue is occupied for the whole callback. Use tx.execute or tx.executeAsync for every statement on this database during that callback, including statements in helpers.

Awaiting db.executeAsync(), db.executeBatchAsync(), or another db.transaction() for the same database inside the callback deadlocks: those calls wait for the current transaction to leave the queue. A synchronous connection call fails with a busy error instead.

await db.transaction(async (tx) => {
  await tx.executeAsync('INSERT INTO audit_log (message) VALUES (?)', [
    'started',
  ])
  // Use tx in helpers too: await writeMoreAuditEntries(tx)
})

Await pending statements

Await every tx.executeAsync() before the callback returns or before manually finalizing. While an async statement is pending, tx.execute(), tx.commit(), and tx.rollback() throw instead of running out of order. If the callback throws, the wrapper waits for pending async statements before rolling back. A thrown error after an explicit commit or rollback cannot undo that completed transaction.

tx.commit() and tx.rollback() are synchronous. Their declared return type exposes the native query result fields. The JavaScript helper also adds rows at runtime, but that field is not in their declared type. Use them to finalize the transaction, not to retrieve rows. The transaction starts with BEGIN TRANSACTION; the global NitroSQLite.transaction helper also accepts isExclusive to use BEGIN EXCLUSIVE TRANSACTION.

For a fixed list of statements and parameter sets, batch operations need less callback code. See the generated Transaction reference for exact method types.