transactions

Transactions are useful if you want to tie the success of one query to another, enabling you to guarantee a valid state for your database in the event that anything might go wrong. If the callback throws, the entire transaction rolls back.

Start a transaction by calling transaction on your app's ApplicationModel:

await ApplicationModel.transaction(async txn => {
  const user = await User.txn(txn).create({ ... })
  await UserSettings.txn(txn).create({ user })
  await user.txn(txn).createAssociation('profile', {})
})

The pattern for this can seem strange at first, but you will get the hang of it in short order. The callback you pass to transaction receives the transaction, named txn by convention. With the txn variable on hand, you proceed to pass it to each subsequent query that might need it, as demonstrated above, using the txn method (named the same as the variable), which model classes and model instances both provide.

When to open a transaction

Open one only when a set of writes must land together or not at all — a Place and the Rooms created with it, a Booking and the row recording its payment. A single write never needs one, and neither does a set whose partial application the application can recover from.

Every operation inside a transaction must be explicitly bound

Every model operation inside a transaction — creates, updates, destroys, queries, association operations — must be explicitly bound via .txn(txn). If you forget it, the operation runs outside the transaction and won't roll back if the callback later throws:

await ApplicationModel.transaction(async txn => {
  const user = await User.txn(txn).create({ email: 'test@test.com' })
  const post = await Post.txn(txn).create({ user, title: 'Test' })
  await user.txn(txn).createAssociation('profile', {})

  // Queries also need .txn(txn) to see uncommitted data
  const found = await User.txn(txn).findBy({ email: 'test@test.com' })
})

On a @deco.Sortable model, forgetting .txn(txn) is worse than a lost rollback. A sortable write computes its position under a lock on its sort scope, so an unbound room.destroy() inside a transaction that has already created, moved, or destroyed another Room of the same Place opens a second transaction on another connection and waits on a lock the enclosing transaction is holding. Only one side is waiting, so Postgres's deadlock detector never sees it — the call hangs until sortableScopeLockTimeout expires, then throws SortableScopeLockWaitTimedOut. That error advises retrying; a retry cannot help here, since the fix is adding the missing .txn(txn).

instance.txn(txn).update(attrs) mutates that same in-memory instance — it assigns the attributes to the instance before saving, exactly like non-transactional instance.update(). So the caller's reference is current after the call and can be handed onward (to a service, a serializer) without re-querying. The catch: only that instance is updated. If a helper loaded a different instance of the same row (e.g. Place.txn(txn).findBy(...)) and updated it, the caller's original instance is now stale — await original.txn(txn).reload() before reading from it. An unbound reload() reads on another connection and cannot see the transaction's uncommitted write.

Sortable writes hold their scope locks until commit

Binding every write correctly leaves a second cost on a @deco.Sortable model. A sortable write holds the lock on its sort scope until the transaction commits, and nothing releases those locks along the way, so a transaction that writes Rooms across many Places holds one lock per place at once. These are Postgres advisory locks, which come from a lock table the whole Postgres cluster shares; exhausting it fails other connections' transactions with out of shared memory, far from the code that caused it.

Keep the transaction to the writes that must land together. A loop whose sortable writes span many Place scopes is usually not one of them, and narrowing the transaction to each iteration costs atomicity across the loop: if a later iteration fails, the earlier ones have already committed, so a re-run has to skip what already landed.

Narrowing applies only to transactions your own code opens. Inside a transaction your code did not open — a hook's txn, or one handed to a service — every write still binds to that txn; a sortable write broken out of it hangs as described above.

Optional transaction participation with .txn(null)

.txn() accepts null as well as a transaction. When null is passed, the operation runs without a transaction — it's a no-op. This lets a method that only optionally participates in a transaction use a single code path instead of branching:

async function doWork(user: User, txn: DreamTransaction<ApplicationModel> | null = null) {
  // No if/else needed — .txn(null) is a no-op
  await user.txn(txn).update({ status: 'done' })
  await user.txn(txn).createAssociation('posts', { title: 'New' })
}

// Call with a transaction
await ApplicationModel.transaction(async txn => {
  await doWork(user, txn)
})

// Call without — works the same, just no transaction wrapping
await doWork(user)

Both Model.txn(null) (class-level) and instance.txn(null) (instance-level) work the same way.

Restrictions inside transactions

Once in a transaction builder (done by calling the txn method), you will be prohibited from using methods that rely on unique-constraint violations to function, like createOrFindBy and createOrUpdateBy, since they will not function properly in a transaction. Use their transaction-safe counterparts — findOrCreateBy and updateOrCreateBy — instead.

Keep background work and I/O out of the transaction

Do not enqueue background work from inside an open transaction. When a model lifecycle hook queues a backgrounded service, a backgrounded model method, or any helper that queues work, use a commit hook variant such as @deco.AfterCreateCommit, @deco.AfterUpdateCommit, or @deco.AfterSaveCommit so the worker only runs after the transaction commits. Regular hooks run before an enclosing transaction commits, so otherwise the worker can race the commit and either miss a newly-created row or read stale data after an update. This applies equally to an imperative background() call made from inside ApplicationModel.transaction(...).

More broadly, what belongs inside ApplicationModel.transaction(...) is database writes only. HTTP fetches, S3/object-storage uploads, sending email, calling third-party APIs, file I/O, sleeps, and any other non-DB work belong outside the transaction. Holding an open transaction across long-running I/O has three failure modes:

1. Connection pool starvation. A long-running transaction pins a Postgres connection, so other requests block waiting for a free one.
2. Vacuum interference. Open transactions hold the horizon for vacuum, preventing dead-tuple cleanup on hot tables until the transaction closes.
3. Background-job race exposure. If work inside the transaction enqueues background jobs, workers dequeue immediately and race the commit.

Do the external I/O before the transaction, then record the result in a tight transaction at the end. If you invert this — commit first, then do the I/O — and the I/O fails, the database will claim work happened that actually didn't, requiring reconciliation code to find and recover those records:

// WRONG — long-running I/O inside the transaction
await ApplicationModel.transaction(async txn => {
  for (const record of records) {
    const place = await Place.txn(txn).create({ ... })
    await fetch(record.photoUrl) // network I/O
    await s3.send(new PutObjectCommand({ ... })) // network I/O
  }
})

// ALSO WRONG — DB says the work happened, but if the I/O fails, external state is missing
const created: Place[] = []
await ApplicationModel.transaction(async txn => {
  for (const record of records) {
    created.push(await Place.txn(txn).create({ ... }))
  }
})
for (const place of created) {
  await s3.send(new PutObjectCommand({ ... })) // if this fails, the DB row is orphaned
}

// RIGHT — do the I/O first, then record the result in a single fast transaction
const prepared: Array<{ record: Record; bucketPath: string }> = []
for (const record of records) {
  const buffer = await (await fetch(record.photoUrl)).arrayBuffer()
  const bucketPath = PlacePhotoMediaService.objectKey(host, record)
  await s3.send(new PutObjectCommand({ Bucket, Key: bucketPath, Body: Buffer.from(buffer) }))
  prepared.push({ record, bucketPath })
}

await ApplicationModel.transaction(async txn => {
  for (const { record, bucketPath } of prepared) {
    await Place.txn(txn).create({ ...record, bucketPath })
  }
})

If the I/O phase fails partway, nothing is in the database and the caller can safely retry. The transaction at the end is short, predictable, and never held open across network round-trips. If a later failure requires cleanup, orphaned S3 objects are cheaper to reconcile than orphaned DB rows pointing at nothing.