Database

Transactions

Run PostgreSQL, MySQL and SQLite queries atomically with AxilJS: driver.transaction() and Transaction.run() commit on success and roll back on any error.

3 min readUpdated DocumentationEdit this page
On this page6

A transaction runs a group of queries so that either all of them are committed or none are. @axiljs/orm gives you two ways to open one: the driver's transaction() with raw SQL, and the Transaction helper, which adds a query builder bound to the transaction.

Both commit when the callback resolves and roll back when it throws. The error is rethrown to the caller.

driver.transaction()

Every driver (PostgreSQL, MySQL, SQLite) implements transaction(work). work receives a context whose query(sql, params) runs on the transaction's connection.

TypeScript
TS
1import { createConnection } from '@axiljs/orm'
2
3const db = await createConnection({ driver: 'postgres', url: process.env.DATABASE_URL })
4
5await db.getDriver().transaction(async (trx) => {
6  await trx.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, 1])
7  await trx.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [100, 2])
8})

trx.query() returns { rows, affectedRows, insertId? } (insertId when the driver reports one). Use $1, $2 placeholders on PostgreSQL and ? on MySQL and SQLite.

On PostgreSQL and MySQL the driver checks out one pooled connection for the whole transaction and releases it afterwards.

Transaction.run()

Transaction wraps the same mechanism and hands you a TransactionScope with a query builder. Register the driver once at startup:

TypeScript
TS
1import { createConnection, Transaction } from '@axiljs/orm'
2
3const db = await createConnection({ driver: 'postgres', url: process.env.DATABASE_URL })
4Transaction.setDriver(db.getDriver(), 'postgres')

Then:

TypeScript
TS
1const order = await Transaction.run(async (trx) => {
2  const order = await trx.query('orders').insert({ user_id: 7, total: 500 })
3
4  await trx.query('order_items').insertMany([
5    { order_id: order.id, sku: 'A-1', qty: 2 },
6    { order_id: order.id, sku: 'B-4', qty: 1 }
7  ])
8
9  await trx.query('products').where('sku', '=', 'A-1').decrement('stock', 2)
10
11  return order
12})

Transaction.run() resolves with whatever the callback returns. Calling it before setDriver() throws [AxilORM] Transaction: no driver set.

TransactionScope

MethodReturnsDescription
query(table)QueryBuilderQuery builder on table that runs inside the transaction: where, insert, insertMany, update, delete, increment, get, first, and the rest
raw(sql, params?)Promise<T[]>Raw SQL inside the transaction; resolves with the rows
TypeScript
TS
1await Transaction.run(async (trx) => {
2  const [row] = await trx.raw<{ total: number }>(
3    'SELECT SUM(total) AS total FROM orders WHERE user_id = $1', [7]
4  )
5  await trx.query('users').where('id', '=', 7).update({ lifetime_value: row.total })
6})

What runs inside the transaction

Only queries issued through trx are part of the transaction. A model or repository call inside the callback, such as User.query() or userRepo.create(), uses the model's own driver and on PostgreSQL and MySQL runs on a different pooled connection, outside the transaction. Route every write that must be atomic through trx.query(...) or trx.raw(...).

Note

Nested transactions are not supported. Calling transaction() from inside a TransactionScope query builder throws Nested transactions not supported directly. Keep one Transaction.run() per unit of work and pass trx to helpers that need it.

Migrations

MigrationRunner runs each migration in its own transaction, so a failing migration leaves the schema as it was. See Migrations.

Was this page helpful?

Help improve the documentation

AxilJS is open source and documentation improvements are welcome.

AxilJS DocumentationMIT License · Built by SyntaxilitY