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.

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
})

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.

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 every tx.executeAsync() before the callback returns or before manually finalizing. The wrapper does not track unawaited transaction promises. A thrown error after an explicit commit or rollback cannot undo that completed transaction.

For a fixed list of statements and parameter sets, batch operations need less callback code. See the transaction API for signatures.