Atomic Multi-Write Operations in AdonisJS with Lucid Transactions

The problem: partial writes after a mid-request failure

Consider an order checkout that inserts an order row, inserts its line items, and decrements inventory. If the inventory update fails after the order row was inserted, you now have an order with no stock backing it — or worse, an order whose items were never written. The fix is a database transaction: every write in the unit of work commits together, or none of them do. This guide shows how to structure that in an AdonisJS service using Lucid, how to keep HTTP concerns out of the transaction, and how to prove rollback actually happens.

Note: Lucid's transaction helper names and options have changed across AdonisJS major versions. Confirm the exact API against the version installed in your project before copying this code; the structure (validate, transact, respond) is stable even when method names differ.

Prerequisites

• An AdonisJS application with Lucid configured and migrations applied for the tables involved (here: orders, order_items, products).
• A test database you can freely write to, ideally the same engine (e.g. PostgreSQL or MySQL) you run in production.
• A service class or controller method where the checkout use case can be exercised end to end.

Keep HTTP outside the transaction

A transaction holds database locks while it is open. Everything slow or failure-prone that does not need the database — request validation, authorization, external API calls — belongs outside it. The pattern is:

1. Validate and normalize input.
2. Open the transaction and run only model/query operations inside it.
3. Commit (implicitly, on success) and map the result or error to an HTTP response afterwards.

Validation errors should be raised before the transaction starts. Business-rule failures discovered during writes (for example, insufficient stock) are raised inside the transaction so they trigger a rollback.

The service: one client for every query

The critical rule is that every query in the unit of work must use the same transaction client. Mixing the default connection with the transactional connection silently breaks atomicity — the non-transactional writes commit immediately even if the transaction later rolls back.

// app/services/order_service.ts
import db from '@adonisjs/lucid/services/db'
import Order from '#models/order'
import OrderItem from '#models/order_item'
import Product from '#models/product'

export class InsufficientStockError extends Error {}

interface CheckoutItem {
  productId: number
  quantity: number
}

export default class OrderService {
  async checkout(userId: number, items: CheckoutItem[]) {
    // useTransaction commits if the callback resolves,
    // and rolls back if it throws.
    return db.transaction(async (trx) => {
      const order = new Order()
      order.useTransaction(trx)
      await order.fill({ userId, status: 'pending' }).save()

      for (const item of items) {
        const product = await Product.query({ client: trx })
          .where('id', item.productId)
          .forUpdate() // row lock: serializes concurrent stock changes
          .firstOrFail()

        if (product.stock < item.quantity) {
          // Domain error thrown inside the callback => ROLLBACK
          throw new InsufficientStockError(
            `Product ${product.id} has ${product.stock} in stock`
          )
        }

        product.stock -= item.quantity
        await product.save()

        const orderItem = new OrderItem()
        orderItem.useTransaction(trx)
        await orderItem
          .fill({
            orderId: order.id,
            productId: product.id,
            quantity: item.quantity,
            unitPrice: product.price,
          })
          .save()
      }

      return order
    })
  }
}

Two details matter here:

• Passing the client everywhere. Models get it via useTransaction(trx); query-builder calls get it via query({ client: trx }). If you add a raw db.from(...) query later, it must also receive the client.
• Locking the stock row. forUpdate() takes a row lock so two concurrent checkouts cannot both read stock = 5 and both succeed. Without it, the transaction is atomic but not isolated against this race.

Mapping errors at the boundary

The controller validates first, then calls the service, then translates domain errors into responses — after the transaction has already ended:

// app/controllers/orders_controller.ts (sketch)
async store({ request, response, auth }: HttpContext) {
  const payload = await request.validateUsing(checkoutValidator)

  try {
    const order = await orderService.checkout(auth.user!.id, payload.items)
    return response.created({ orderId: order.id })
  } catch (error) {
    if (error instanceof InsufficientStockError) {
      return response.unprocessableEntity({ message: error.message })
    }
    throw error // unexpected errors surface as 500 via the handler
  }
}

By the time this catch runs, the transaction has already rolled back. Do not attempt to "clean up" rows in the controller — that is the rollback's job.

Proving rollback with a test

Logs and manual inspection are weak evidence. Write a test that forces the last write to fail and asserts the earlier rows are gone:

test('rolls back order and items when stock is insufficient', async ({ client, assert }) => {
  const product = await ProductFactory.create() // stock: 1

  const response = await client.post('/orders').json({
    items: [
      { productId: product.id, quantity: 1 },
      { productId: product.id, quantity: 5 }, // exceeds stock
    ],
  })

  response.assertStatus(422)

  // Nothing from the aborted checkout may exist.
  assert.equal(await Order.query().count('* as total'), '0' /* or 0, driver-dependent */)
  assert.equal(await OrderItem.query().count('* as total'), '0')

  await product.refresh()
  assert.equal(product.stock, 1) // decrement was rolled back too
})

Run the happy path as well and assert the order, its items, and the reduced stock all exist with consistent foreign keys. For deeper confirmation, enable query logging during the test and check that the statements share one transaction ending in ROLLBACK (failure case) or COMMIT (success case).

Limitations and things to check

• Keep transactions short. Open transactions hold locks; never make HTTP calls or send emails inside the callback. Queue those side effects after commit.
• Nested transactions differ by driver. Savepoint behavior is not identical across PostgreSQL, MySQL, and SQLite. Test against the engine you deploy on, not only an in-memory substitute.
• DDL is not transactional everywhere. Schema changes inside a transaction may auto-commit depending on the engine; keep migrations out of runtime transactions.
• Verify the API for your version. Check your installed AdonisJS/Lucid major version's documentation for db.transaction, useTransaction, and forUpdate before relying on the exact signatures above.

The decision rule to take away: validate before the transaction, put only database work inside it, throw domain errors to trigger rollback, and prove atomicity with a test that forces the final write to fail.