Transactions, Model Middleware & Soft Delete

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

This page is written against Vapor 4.122.2 on Swift 6.4 (the package builds in the Swift 6 language mode with tools version 6.3, as the official template does), with fluent 4.13.0, fluent-kit 1.57.0, fluent-sqlite-driver 4.9.0 and sql-kit 3.36.0, checked on 2026-10-17. Every Swift listing on this page was compiled in the Swift 6 language mode with zero warnings in a scratch package, and the routes were run against an in-memory SQLite database and exercised with curl; the outputs shown are captured from those runs. Nothing here was run against a live PostgreSQL or MySQL server: where behaviour on those drivers is described, it comes from reading the driver sources, and the page says so. Vapor 5 is at 5.0.0-beta.4 and is not released yet; Vapor 5 and its Fluent were not exercised for this page; check What’s New, Upgrading and Vapor 5 before porting.

The page extends the Bookshelf models of Fluent Models and the earlier pages of this section. It adds two columns to books and one table. These are page-local additions; the canonical models are not renamed:

  • books.deleted_at (datetime, nullable), mapped as @Timestamp(key: "deleted_at", on: .delete) var deletedAt: Date?;

  • books.version (int, not null, default 1), mapped as @Field(key: "version") var version: Int, initialised to 1 by the model’s init;

  • an audit_entries table (entity, entity_id, action, detail, created_at) written by the model middleware.

Swift itself is covered by the Swift Reference; closures that cross isolation domains and Sendable by Actors, Isolation and Sendable. The same topics in Rust are in Migrations & Transactions.

Where these features sit

None of the three is part of the HTTP pipeline. They live in the Fluent layer, below the handler:

  • a transaction is a database connection held for the duration of a closure; everything run on the Database value the closure receives belongs to one BEGIN …​ COMMIT/ROLLBACK;

  • model middleware wraps the persistence operations of a model instance (create, update, delete, restore). Each call such as book.save(on:) builds a chain from the middleware registered on the database and ends at the built-in implementation that runs the query;

  • soft delete is not a separate mechanism: it is a @Timestamp with trigger .delete. Fluent turns delete into an UPDATE of that column and adds deleted_at IS NULL to queries.

In a request the order is: the global middleware chain of the application, the router, the handler, and from the handler the Fluent calls, each passing through the model middleware to the driver.

Transactions

The API

Database has two transaction methods in fluent-kit 1.57.0 (Database.swift and Concurrency/Database+Concurrency.swift):

// FluentKit 1.57.0, abridged
extension Database {
    func transaction<T: Sendable>(
        _ closure: @escaping @Sendable (any Database) async throws -> T
    ) async throws -> T
}
// the requirement every driver implements (legacy, EventLoopFuture based):
// func transaction<T>(_ closure: @escaping @Sendable (any Database) -> EventLoopFuture<T>) -> EventLoopFuture<T>

Source: FluentKit 1.57.0: Database+Concurrency.swift and Database.swift

Semantics, from the SQLite and PostgreSQL driver sources:

  1. the driver checks out one connection from the pool and issues BEGIN;

  2. it calls your closure with a new Database value, db, bound to that connection;

  3. if the closure returns, the driver issues COMMIT and returns your value; if it throws, the driver issues ROLLBACK and rethrows the same error.

The consequence that matters most: only queries run on db are inside the transaction.

Rollback on throw

The first example creates an author and all of their books atomically. The second book repeats an ISBN, the unique constraint fails, and the author row and the first book vanish with the rollback:

import Fluent
import Vapor

struct NewBook: Content, Sendable {
    var title: String
    var isbn: String
    var publishedYear: Int
    var priceCents: Int
}

struct NewAuthorWithBooks: Content, Sendable {
    var name: String
    var books: [NewBook]
}

struct CreatedAuthor: Content, Sendable {
    var id: UUID
}

func transactionRoutes(_ app: Application) {
    app.post("authors") { req async throws -> Response in
        let input = try req.content.decode(NewAuthorWithBooks.self)
        do {
            let authorID = try await req.db.transaction { db in
                let author = Author(name: input.name)
                try await author.save(on: db)
                let id = try author.requireID()
                for b in input.books {
                    try await Book(title: b.title, isbn: b.isbn, publishedYear: b.publishedYear,
                                   priceCents: b.priceCents, authorID: id).save(on: db)
                }
                return id
            }
            return try await CreatedAuthor(id: authorID).encodeResponse(status: .created, for: req)
        } catch let error as any DatabaseError where error.isConstraintFailure {
            throw Abort(.conflict, reason: "A book with that ISBN already exists")
        }
    }
}

Source: Vapor docs: Fluent, Transaction

Run against SQLite with two books that normalise to the same ISBN, then with two different ISBNs:

curl -s -i -X POST http://127.0.0.1:8137/authors -H 'Content-Type: application/json' -d '{"name":"Ada","books":[
  {"title":"A1","isbn":"978-0-306-40615-7","publishedYear":2001,"priceCents":1000},
  {"title":"A2","isbn":"9780306406157","publishedYear":2002,"priceCents":1200}]}'
# HTTP/1.1 409 Conflict
# {"error":true,"reason":"A book with that ISBN already exists"}
curl -s http://127.0.0.1:8137/authors/count      # 0   - the author row was rolled back
curl -s http://127.0.0.1:8137/books              # []  - so was the first book

With the second request using a different ISBN, the response is 201 with the author id, the author count is 1 and /books lists both books.

Points about the listing:

  • db is the closure parameter; req.db is not used inside the closure (see the next section).

  • The closure is @Sendable, so it may capture only Sendable values. input is a Sendable Content struct. Capturing req compiles, because Request is Sendable in Vapor 4.122.2, but is best avoided; copy out what the closure needs. The result type must be Sendable: returning the UUID is fine, returning a Fluent model needs the model to be Sendable (the Bookshelf models are declared @unchecked Sendable).

  • A unique violation surfaces as an error whose DatabaseError.isConstraintFailure is true (an any DatabaseError, cast with as?). Mapping it to 409 there keeps the driver’s message, which names tables and columns, out of the response. Without the catch the same request answers 500 with constraintUniqueFailed: UNIQUE constraint failed: books.isbn in the body when the application runs in a development environment; production builds hide the reason of non-Abort errors.

Use the closure’s db, never the outer database

The transaction owns a connection. Work done on any other Database value, including the req.db the transaction was started from, runs outside it. Two failure shapes follow, and both were reproduced:

  • Writes escape the transaction. On a driver with a pool of several connections the outer write runs on another connection, commits on its own and is not rolled back when the closure throws.

  • Deadlock on a one-connection pool. The SQLite driver’s pool holds one connection per event loop by default (maxConnectionsPerEventLoop: 1), so a write on the outer database inside the closure waits for the connection the transaction holds. Against SQLite this produced, after the 10 second default connectionPoolTimeout:

    [ ERROR ] Connection request timed out. This might indicate a connection deadlock in your application.
          If you have long-running requests, consider increasing your connection timeout. [database-id: sqlite, ...]

The experiment was:

app.post("x", "outer-db") { req async throws -> Int in
    let outer = req.db
    do {
        try await outer.transaction { _ in
            try await Author(name: "written with the outer db").save(on: outer)   // wrong: outer, not the closure's db
            throw Abort(.badRequest)
        }
    } catch {}
    return try await Author.query(on: outer).count()   // 0: the write never got a connection
}

Source: Vapor docs: Fluent, Transaction

The same holds for helper functions: a service called from inside the closure must take the Database as a parameter instead of reading req.db itself.

Nested transactions

Calling transaction on a Database that is already in a transaction does not start a new one. The SQLite and PostgreSQL drivers check inTransaction and call the closure directly on the same db; there is no savepoint. (The MySQL driver was not read for this page.) So an inner failure that the outer code catches does not undo the inner writes:

app.post("x", "nested") { req async throws -> Int in
    try await req.db.transaction { db in
        try await Author(name: "outer").save(on: db)
        do {
            try await db.transaction { inner in
                try await Author(name: "inner").save(on: inner)
                throw Abort(.badRequest)
            }
        } catch {}                                  // swallowed: the outer transaction still commits
    }
    return try await Author.query(on: req.db).count()
}
// POST /x/nested  ->  2   (the "inner" author was committed although the inner closure threw)

Source: fluent-sqlite-driver 4.9.0: FluentSQLiteDatabase.swift and fluent-postgres-driver 2.14.0: FluentPostgresDatabase.swift

Only an error that leaves the outermost closure rolls back. If you need partial rollback, use raw SAVEPOINT statements through SQLKit, which Fluent does not wrap.

More rules

  • Keep it short. A transaction pins a pooled connection and, in most databases, holds row locks until it ends. Do not call other services, read request bodies or sleep inside it.

  • No external side effects. Sending an e-mail or enqueueing a job inside the closure and then rolling back leaves the side effect behind. Do it after the transaction returns.

  • Retries. The closure may run again if you retry on a serialization failure; make it free of side effects.

  • Isolation level. Fluent has no API for it. Run SET TRANSACTION ISOLATION LEVEL ... through SQLKit as the first statement on the closure’s db if you need something other than the database default (not exercised here).

  • Hooks run inside. Model middleware registered on the database also runs for models saved on the transaction’s db, and its writes (the audit entries below) roll back with everything else.

Model middleware

The protocol

Model middleware lets you run code around the create/update/delete/restore operations of a model. In fluent-kit 1.57.0 the async form is:

// FluentKit 1.57.0, Concurrency/AsyncModelMiddleware.swift
public protocol AsyncModelMiddleware: AnyModelMiddleware {
    associatedtype Model: FluentKit.Model
    func create(model: Model, on db: any Database, next: any AnyAsyncModelResponder) async throws
    func update(model: Model, on db: any Database, next: any AnyAsyncModelResponder) async throws
    func delete(model: Model, force: Bool, on db: any Database, next: any AnyAsyncModelResponder) async throws
    func softDelete(model: Model, on db: any Database, next: any AnyAsyncModelResponder) async throws
    func restore(model: Model, on db: any Database, next: any AnyAsyncModelResponder) async throws
}

Source: FluentKit 1.57.0: AsyncModelMiddleware.swift

All five have default implementations that call straight through to next, so a middleware implements only the hooks it needs. The legacy ModelMiddleware (futures) has the same shape; new code uses the async one. A middleware is Sendable (it is shared by every request), so it must be a struct with immutable Sendable state or an actor.

A hook receives the model instance, the Database to use, and next, the rest of the chain. Calling next.create(model, on: db) (or next.update, next.delete(_:force:on:), next.softDelete, next.restore) performs the operation. Code before the call is a "before" hook, code after it an "after" hook, and not calling next (throwing instead) vetoes the operation.

Which method triggers which hook

Call Hook(s) Notes

model.create(on:), or save(on:) on a model that has no row yet

create

Timestamps with .create and .update are set by the built-in step after your "before" code.

model.update(on:), or save(on:) on an existing model

update

Skipped silently by the built-in step if no field has changed.

model.delete(on:) on a model with a .delete timestamp

softDelete

The timestamp is set before the chain runs; the row stays.

model.delete(on:) on a model without one

delete with force: false

The row is removed.

model.delete(force: true, on:)

delete with force: true

The row is removed even if the model has a .delete timestamp.

model.restore(on:)

restore

Clears the .delete timestamp.

[Model].create(on:), [Model].delete(force:on:)

create / delete once per element

The array is then written with one batch query.

Model.query(on:)...update(), .delete(), .set(...)

none

Query-builder operations never run model middleware.

The last row is the pitfall that makes middleware unsuitable as the only place for an invariant. Reproduced on the running service:

app.delete("x", "batch") { req async throws -> HTTPStatus in
    try await Book.query(on: req.db).filter(\.$publishedYear > 0).delete()   // soft-deletes every book
    return .noContent
}
// GET /books        -> []
// GET /books/trash  -> [{"title":"T", "deletedAt":"2026-10-17T15:31:09Z", ...}]
// GET /audit        -> ["create 9780306406157"]            no "softDelete" entry was written

Source: FluentKit 1.57.0: QueryBuilder+Concurrency.swift and Vapor docs: Fluent, Model, Lifecycle

A lifecycle middleware for Book

The next listing normalises and validates the ISBN before the insert, and writes an audit row after each operation. The audit row is saved with the db the hook received, so it is part of the caller’s transaction when there is one.

import Fluent
import Vapor

final class AuditEntry: Model, @unchecked Sendable {
    static let schema = "audit_entries"
    @ID(key: .id) var id: UUID?
    @Field(key: "entity") var entity: String
    @OptionalField(key: "entity_id") var entityID: UUID?
    @Field(key: "action") var action: String
    @Field(key: "detail") var detail: String
    @Timestamp(key: "created_at", on: .create) var createdAt: Date?
    init() {}
    init(entity: String, entityID: UUID?, action: String, detail: String = "") {
        self.entity = entity; self.entityID = entityID; self.action = action; self.detail = detail
    }
}

struct BookLifecycle: AsyncModelMiddleware {
    typealias Model = Book

    func create(model: Book, on db: any Database, next: any AnyAsyncModelResponder) async throws {
        model.isbn = model.isbn.filter { $0.isNumber || $0 == "X" }
        guard model.isbn.count == 10 || model.isbn.count == 13 else {
            throw Abort(.unprocessableEntity, reason: "ISBN must have 10 or 13 characters")
        }
        try await next.create(model, on: db)
        try await AuditEntry(entity: "book", entityID: model.id, action: "create", detail: model.isbn).save(on: db)
    }

    func update(model: Book, on db: any Database, next: any AnyAsyncModelResponder) async throws {
        try await next.update(model, on: db)
        try await AuditEntry(entity: "book", entityID: model.id, action: "update", detail: model.title).save(on: db)
    }

    func softDelete(model: Book, on db: any Database, next: any AnyAsyncModelResponder) async throws {
        try await next.softDelete(model, on: db)
        try await AuditEntry(entity: "book", entityID: model.id, action: "softDelete").save(on: db)
    }

    func restore(model: Book, on db: any Database, next: any AnyAsyncModelResponder) async throws {
        try await next.restore(model, on: db)
        try await AuditEntry(entity: "book", entityID: model.id, action: "restore").save(on: db)
    }

    func delete(model: Book, force: Bool, on db: any Database, next: any AnyAsyncModelResponder) async throws {
        try await next.delete(model, force: force, on: db)
        try await AuditEntry(entity: "book", entityID: model.id, action: "delete", detail: "force=\(force)").save(on: db)
    }
}

Source: Vapor docs: Fluent, Model, Lifecycle

The migration that adds the page-local columns and table (an AsyncMigration; see Migrations & Schema):

import Fluent
import FluentSQL

struct AddSoftDeleteVersionAndAudit: AsyncMigration {
    func prepare(on db: any Database) async throws {
        try await db.schema("books").field("deleted_at", .datetime).update()
        try await db.schema("books").field("version", .int, .required, .sql(.default(1))).update()
        try await db.schema("audit_entries").id()
            .field("entity", .string, .required).field("entity_id", .uuid)
            .field("action", .string, .required).field("detail", .string, .required, .sql(.default("")))
            .field("created_at", .datetime).create()
    }
    func revert(on db: any Database) async throws {
        try await db.schema("audit_entries").delete()
        try await db.schema("books").deleteField("version").update()
        try await db.schema("books").deleteField("deleted_at").update()
    }
}

Source: Vapor docs: Fluent, Migrations and Vapor docs: Fluent, Schema, Field

Two separate update() calls add the two columns because SQLite’s ALTER TABLE adds one column at a time. The .sql(.default(1)) constraint is what allows adding a NOT NULL column to a table that already has rows. It needs import FluentSQL; with MemberImportVisibility enabled (as in the template) omitting the import is a compile error (static method 'sql' is not available due to missing import of defining module 'FluentSQL').

Registering middleware and ordering

Middleware is registered per database in configure, with app.databases.middleware.use(_:on:):

import Fluent
import FluentSQLiteDriver
import Vapor

func configure(_ app: Application) async throws {
    app.databases.use(.sqlite(.memory), as: .sqlite)
    app.databases.middleware.use(BookLifecycle(), on: .sqlite)
    app.migrations.add(CreateBookshelf())
    app.migrations.add(AddSoftDeleteVersionAndAudit())
    try await app.autoMigrate()
    try routes(app)
}

Source: Vapor docs: Fluent, Model, Lifecycle

Registered middleware sees every model saved on that database; the generated handle method passes models of other types straight through, so BookLifecycle (whose Model is Book) ignores AuditEntry. That is also why the audit write inside the hook does not recurse.

Order: the first registered middleware is the outermost. fluent-kit builds the chain by wrapping the built-in step with the registered middleware in reverse order. To see it, a logging middleware was registered before and after BookLifecycle:

struct TraceMiddleware: AsyncModelMiddleware {
    typealias Model = Book
    let name: String
    func create(model: Book, on db: any Database, next: any AnyAsyncModelResponder) async throws {
        db.logger.info("\(name) before create")
        try await next.create(model, on: db)
        db.logger.info("\(name) after create")
    }
}
// app.databases.middleware.use(TraceMiddleware(name: "outer"), on: .sqlite)
// app.databases.middleware.use(BookLifecycle(),                on: .sqlite)
// app.databases.middleware.use(TraceMiddleware(name: "inner"), on: .sqlite)

Source: FluentKit 1.57.0: ModelMiddleware.swift and Vapor docs: Fluent, Model, Lifecycle

[ INFO ] outer before create [database-id: sqlite, request-id: DEA65298-...]
[ INFO ] inner before create [database-id: sqlite, request-id: DEA65298-...]
[ INFO ] inner after create  [database-id: sqlite, request-id: DEA65298-...]
[ INFO ] outer after create  [database-id: sqlite, request-id: DEA65298-...]

The outer middleware sees the model before BookLifecycle normalises the ISBN and the inner one sees it after. Put validation middleware first, and middleware that reacts to a successful write last (inner).

app.databases.middleware.clear(on:) removes all registered middleware for a database. Middleware is per database ID: a model saved on a second database (for example a read replica registered as another ID) does not run it unless you register it there too.

Rules and pitfalls of hooks

  • Throwing before next vetoes the write; throwing after next is too late for the row, but inside a transaction it rolls the row back. Outside a transaction a failing "after" hook leaves the write committed, so audit writes that must not be lost belong in a transaction.

  • The update hook runs for save on an existing model even when no field changed; the built-in step then does nothing. Compare against model.hasChanges if the hook should only fire on real changes.

  • A hook that saves or deletes the same model calls the hooks again; guard against it or write through the query builder.

  • Do not do slow I/O in a hook; it runs on the request’s connection path.

  • An Abort thrown from a hook propagates to the error middleware, so 422 for a bad ISBN needs no extra code (listing POST /authors/:authorID/books answers 422 {"reason":"ISBN must have 10 or 13 characters","error":true}).

  • Hooks are code, not constraints. Keep real invariants (unique ISBN, foreign keys) in the schema; the hook is for normalisation, auditing and notifications.

Soft delete

What it is and how Fluent implements it

Soft delete marks a row as deleted instead of removing it, so it can be audited and restored. Fluent has no separate property wrapper: a model opts in by having a @Timestamp with trigger .delete:

// in Book (page-local addition to the canonical model)
@Timestamp(key: "deleted_at", on: .delete) var deletedAt: Date?

Source: Vapor docs: Fluent, Model, Soft Delete

With that property present, fluent-kit 1.57.0 changes behaviour in four places (Concurrency/Model+Concurrency.swift and QueryBuilder+Concurrency.swift):

  1. model.delete(on:) sets deletedAt to now and issues an UPDATE books SET deleted_at = ... WHERE id = ?; the hook is softDelete. delete(force: true, on:) issues a real DELETE, with hook delete(force: true).

  2. Every query on the model adds deleted_at IS NULL (or a later date), so all(), first(), find, aggregates, joins and eager loads skip soft-deleted rows.

  3. withDeleted() on a query builder turns that filter off. Book.query(on:).withDeleted() returns both live and deleted rows.

  4. model.restore(on:) sets the timestamp back to nil (querying with withDeleted() internally to find the row) and reloads the model’s state; the hook is restore.

The timestamp is optional (Date?): nil means live. Calling restore on a model without a .delete timestamp traps (fatalError("no delete timestamp on this model") in the source), so guard with deletedAt != nil and a model that has the property.

Routes: delete, trash, restore, force delete

import Fluent
import Vapor

struct BookDTO: Content, Sendable {
    var id: UUID?
    var title: String
    var isbn: String
    var version: Int
    var deletedAt: Date?
    init(_ b: Book) { id = b.id; title = b.title; isbn = b.isbn; version = b.version; deletedAt = b.deletedAt }
}

func softDeleteRoutes(_ app: Application) {
    app.get("books") { req async throws -> [BookDTO] in
        try await Book.query(on: req.db).sort(\.$title).all().map(BookDTO.init)
    }
    app.get("books", "trash") { req async throws -> [BookDTO] in
        try await Book.query(on: req.db).withDeleted().filter(\.$deletedAt != nil).all().map(BookDTO.init)
    }
    app.delete("books", ":bookID") { req async throws -> HTTPStatus in
        let force = (try? req.query.get(Bool.self, at: "force")) ?? false
        guard let book = try await Book.query(on: req.db).withDeleted()
            .filter(\.$id == (req.parameters.get("bookID", as: UUID.self) ?? UUID())).first()
        else { throw Abort(.notFound) }
        try await book.delete(force: force, on: req.db)
        return .noContent
    }
    app.post("books", ":bookID", "restore") { req async throws -> BookDTO in
        guard let book = try await Book.query(on: req.db).withDeleted()
            .filter(\.$id == (req.parameters.get("bookID", as: UUID.self) ?? UUID())).first(),
              book.deletedAt != nil
        else { throw Abort(.notFound) }
        try await book.restore(on: req.db)
        return BookDTO(book)
    }
}

Source: Vapor docs: Fluent, Model, soft delete

The lookup uses withDeleted() because a soft-deleted row is invisible to Book.find and to a plain query; without it the second DELETE of a book (to force-delete something already in the trash) and every restore would answer 404. In a real service these routes sit behind an authorisation check: restoring and force-deleting are librarian or admin operations (see Authorization: Roles, Ownership and Policies).

Run on SQLite, with the two books created earlier (B1 is "A1"):

curl -s -i -X DELETE $U/books/$B1 | head -1             # HTTP/1.1 204 No Content
curl -s $U/books                                        # only A2
curl -s $U/books/trash
# [{"isbn":"9780306406157","id":"0811...","version":1,"deletedAt":"2026-10-17T15:28:29Z","title":"A1"}]
curl -s -X POST $U/books/$B1/restore                    # A1 back, no deletedAt field in the JSON
curl -s -i -X POST $U/books/$B1/restore | head -1       # HTTP/1.1 404 Not Found (it is not deleted any more)
curl -s -i -X DELETE "$U/books/$B2?force=true" | head -1  # HTTP/1.1 204 No Content, the row is gone for good
curl -s $U/audit
# ["create 9780306406157","create 9781402894626","softDelete ","restore ","delete force=true"]

The audit trail shows which hook ran for each call: a plain delete fired only softDelete, not delete, and the forced one fired delete with force=true.

Pitfalls of soft delete

  • Unique constraints keep seeing deleted rows. books.isbn is unique in the schema, and the soft-deleted row still holds it. Creating a book with the ISBN of a trashed one failed on SQLite with constraintUniqueFailed: UNIQUE constraint failed: books.isbn (an HTTP 500 until the route maps it). Options: restore the old row instead of creating a new one, force-delete before re-creating, or use a partial unique index (CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL, PostgreSQL and SQLite; raw SQL through SQLKit, since Fluent’s unique(on:) cannot express it).

  • Foreign keys and cascades. onDelete: .cascade fires only for a real DELETE. Soft-deleting an author does not delete or hide its books; do that explicitly (in a transaction), or filter with a join. Conversely, a force delete cascades to reviews and loans.

  • Eager loading. with(\.$author) does not load a soft-deleted parent; the property then fails to unwrap. The eager loaders in fluent-kit’s Parent, OptionalParent and Children have a withDeleted flag internally; the Fluent Relations page covers relations and eager loading. It was not exercised here.

  • Batch operations. As shown above, query.delete() soft-deletes but skips middleware; query.delete(force: true) removes rows. restore has no batch form: update deleted_at with a query that uses withDeleted().

  • Counting and exports. Reports that must include deleted rows need withDeleted() too; forgetting it in one place is the usual bug, so give the "include deleted" queries a named helper.

  • Privacy. A soft-deleted row still holds personal data. Regulations that require erasure need a force delete or a scrubbing job, not only a timestamp.

  • Both timestamps in one model. A model can have several timestamps (.create, .update, .delete); only the one with .delete drives soft delete, and touchTimestamps sets .update as well when a soft-delete query is run through the builder (visible as a changed updated_at).

Optimistic concurrency with a version column

The problem

Two librarians open the same book, change different fields and save. With a plain book.save(on:) the second write silently overwrites the first (a lost update). Locking the row while a person edits is not an option over HTTP; the answer is optimistic concurrency: the client sends the version it read, and the server applies the change only if the row still has that version.

Fluent does not provide this; the model gets a plain version column (the page-local books.version, default 1) and the update is a single compare-and-set statement:

UPDATE books SET title = ?, version = ? WHERE id = ? AND version = ? RETURNING id

If another writer got there first, the WHERE matches no row and nothing is returned. A separate SELECT followed by an UPDATE would leave a window between them; a single statement does not.

The route

The statement is built with SQLKit (see SQLKit and Raw SQL), reached from Fluent with req.db as? any SQLDatabase:

import Fluent
import SQLKit
import Vapor

struct BookPatch: Content, Sendable {
    var title: String
    var version: Int
}

func concurrencyRoutes(_ app: Application) {
    app.patch("books", ":bookID") { req async throws -> BookDTO in
        let patch = try req.content.decode(BookPatch.self)
        guard let id = req.parameters.get("bookID", as: UUID.self) else { throw Abort(.badRequest) }
        guard let sql = req.db as? any SQLDatabase else { throw Abort(.internalServerError) }
        let rows = try await sql.update("books")
            .set("title", to: patch.title)
            .set("version", to: patch.version + 1)
            .where("id", .equal, id)
            .where("version", .equal, patch.version)
            .returning("id")
            .all()
        if rows.isEmpty {
            guard try await Book.find(id, on: req.db) != nil else { throw Abort(.notFound) }
            throw Abort(.conflict, reason: "The book was changed by someone else; reload and retry")
        }
        guard let book = try await Book.find(id, on: req.db) else { throw Abort(.notFound) }
        return BookDTO(book)
    }
}

Source: Vapor docs: Fluent, advanced, SQL

Run on SQLite:

curl -s -X PATCH $U/books/$B1 -H 'Content-Type: application/json' -d '{"title":"A1 second ed.","version":1}'
# {"isbn":"9780306406157","id":"0811...","version":2,"title":"A1 second ed."}
curl -s -i -X PATCH $U/books/$B1 -H 'Content-Type: application/json' -d '{"title":"stale write","version":1}'
# HTTP/1.1 409 Conflict
# {"reason":"The book was changed by someone else; reload and retry","error":true}
curl -s -X PATCH $U/books/$B1 -H 'Content-Type: application/json' -d '{"title":"A1 third ed.","version":2}'
# {"isbn":"9780306406157","id":"0811...","version":3,"title":"A1 third ed."}

The stale request carried the version it had read (1), the row was already at 2, so no row matched and the route answered 409. The distinction between 404 and 409 comes from the follow-up find.

Design notes

  • Where the version travels. In the body, as here, or in the HTTP ETag/If-Match pair. With If-Match the version is sent as a header and a mismatch is 412 Precondition Failed (RFC 9110, section 13.1.1); the PATCH above chose 409 because the version is part of the payload.

  • Who bumps it. Every writer must increment it, including the other routes and background jobs. If any code path updates the row without touching version, the check no longer protects against it. Putting the increment into the update model-middleware hook does not help for this statement because raw SQL (and query-builder updates) skip the hooks; this route is also why the audit log has no update entry for it.

  • Timestamps are bypassed too. Raw SQL does not touch updated_at; set it in the statement if you need it.

  • Portability. RETURNING works on SQLite 3.35+ and PostgreSQL. MySQL has no RETURNING on UPDATE; there, run the update through Fluent’s query.update() and verify the outcome by re-reading the row with a unique token column (for example a UUID revision set to a fresh value on every successful update): a re-read that returns your token proves that you won, which a bare incremented integer cannot (a concurrent winner may have produced the same number). The MySQL variant was not run.

  • A pessimistic alternative. Fluent’s QueryBuilder has no locking clause. Inside a transaction, a row lock goes through SQLKit on the closure’s db, whose select builder has .for(.update) (and .for(.share)):

    let version = try await req.db.transaction { db -> Int in
    guard let sql = db as? any SQLDatabase else { throw Abort(.internalServerError) }
    guard let row = try await sql.select().column("version").from("books")
        .where("id", .equal, id)
        .for(.update)                                   // SELECT ... FOR UPDATE
        .first()
    else { throw Abort(.notFound) }
    // the row stays locked until the closure returns; update it on `db` or `sql` here
    return try row.decode(column: "version", as: Int.self)
}

    Source: sql-kit 3.36.0: SQLSubqueryClauseBuilder.swift

    PostgreSQL and MySQL render FOR UPDATE; SQLite has no row locks, and SQLKit silently drops the clause on a dialect that does not support locking reads. Use it for short critical sections such as lending a copy; use the version for user-driven edits.

  • Client handling. On 409 the client reloads the resource, shows the diff or merges, and resends with the new version. Do not auto-retry the same body.

Common errors

Symptom Cause and fix

Compile error static method 'sql' is not available due to missing import of defining module 'FluentSQL'

.sql(.default(...)) in a schema builder needs import FluentSQL when MemberImportVisibility is enabled.

Compile error referencing instance method 'encodeResponse(status:headers:for:)' on 'Dictionary' requires that 'UUID' conform to 'Content'

A dictionary with non-Content values is not a response. Return a small Content struct (CreatedAuthor).

Connection request timed out. This might indicate a connection deadlock

Work on the outer database inside transaction { db in ... } on a one-connection pool, or a transaction held open by a slow closure. Use db; shorten the transaction.

Rollback does not undo some writes

They ran on req.db (another connection) instead of the closure’s db, or an inner catch swallowed a failure inside a single transaction (no savepoints).

constraintUniqueFailed after "deleting" a record and re-creating it

The soft-deleted row still holds the unique value. Restore, force delete, or use a partial unique index.

404 when restoring or force-deleting a trashed row

The lookup omitted withDeleted().

Fatal error no delete timestamp on this model on restore

The model has no @Timestamp(... on: .delete).

Hook did not run

The change was made with a query builder (update(), delete()), or the middleware was registered on a different database ID.

Hook ran twice for one request

The middleware is registered twice, or the hook saves the same model again.

409 on every save

The client sends a stale or missing version; read it from the last response, not from a cache.

Where the books are outdated

  • Condon et al. (Server-Side Swift with Vapor, Kodeco), chapter 30, teaches soft delete and restore with a deletedAt timestamp and withDeleted(). That design is unchanged in 4.122: the property is still @Timestamp(key:on: .delete), restore and delete(force:) exist as described. What changed is the call style (the book’s flatMap chains are now try await) and that model middleware has an async protocol (AsyncModelMiddleware), so hooks are written as async throws methods that call try await next.create(model, on: db) instead of returning a future.

  • The book’s transactions, where covered, use database.transaction { database in ... } returning an EventLoopFuture. In 4.122 the same method has an async overload; the rule about using the closure’s database instead of the request’s is unchanged.

  • Yeung (Packt) and Patel (Packt) predate Fluent 4 (they use Vapor 2 and 3 Model protocol conformances and .softDeletable style traits); their ideas of lifecycle callbacks map to AsyncModelMiddleware, and soft delete to the timestamp trigger, but none of their code compiles.

References