Fluent Queries and Pagination

import Fluent
import FluentSQLiteDriver
import Vapor

func configure(_ app: Application) async throws {
    app.databases.use(.sqlite(.memory), as: .sqlite)
    app.migrations.add(CreateBookshelf())
    app.migrations.add(SeedBookshelf())
    app.asyncCommands.use(DemoCommand(), as: "demo")
    app.asyncCommands.use(BenchCommand(), as: "bench")
    app.fluent.pagination.pageSizeLimit = 20
    try await app.autoMigrate()
    searchRoutes(app)
    paginationRoutes(app)
}

func basicQueries(on db: any Database) async throws {
    // SELECT ... FROM books
    let everything = try await Book.query(on: db).all()

    // WHERE status = 'published' AND published_year >= 1970 ORDER BY published_year DESC, title ASC
    let recent = try await Book.query(on: db)
        .filter(\.$status == .published)
        .filter(\.$publishedYear >= 1970)
        .sort(\.$publishedYear, .descending)
        .sort(\.$title)
        .all()

    // LIMIT 1: nil when nothing matches
    let dune = try await Book.query(on: db).filter(\.$isbn == "9780000000001").first()

    // find(_:on:) is the primary-key shortcut
    let byID = try await Book.find(seedID(2, 1), on: db)

    print("all:", everything.count, "recent:", recent.count, "first:", dune?.title ?? "nil", "find:", byID?.title ?? "nil")
    print(recent.prefix(3).map { "\($0.publishedYear) \($0.title)" })
}

all: 26 recent: 12 first: Dune find: Dune
["2017 The Stone Sky", "2016 The Obelisk Gate", "2015 The Fifth Season"]

SELECT <every books column> FROM "books"
SELECT <every books column> FROM "books" WHERE "books"."status" = 'published' AND "books"."published_year" >= ?1
    ORDER BY "books"."published_year" DESC, "books"."title" ASC          -- binds: [1970]
SELECT <every books column> FROM "books" WHERE "books"."isbn" = ?1 LIMIT 1
SELECT <every books column> FROM "books" WHERE "books"."id" = ?1 LIMIT 1

func operatorQueries(on db: any Database) async throws {
    let contains = try await Book.query(on: db).filter(\.$title ~~ "dune").all(\.$title)      // LIKE '%dune%'
    let prefix = try await Book.query(on: db).filter(\.$title =~ "The ").all(\.$title)        // LIKE 'The %'
    let suffix = try await Book.query(on: db).filter(\.$title ~= "Dune").all(\.$title)        // LIKE '%Dune'
    let notContains = try await Book.query(on: db).filter(\.$title !~ "Foundation").count()   // NOT LIKE '%Foundation%'
    let inList = try await Book.query(on: db).filter(\.$publishedYear ~~ [1965, 1969, 1976]).all(\.$title) // IN (...)
    let notIn = try await Book.query(on: db).filter(\.$status !~ [.published, .archived]).all(\.$title)    // NOT IN (...)
    let between = try await Book.query(on: db).filter(\.$priceCents >= 1000).filter(\.$priceCents < 1200).count()
    let notEqual = try await Book.query(on: db).filter(\.$status != .published).count()

    // LIKE wildcards in the operand are not escaped: "_une" matches "Dune"
    let wildcard = try await Book.query(on: db).filter(\.$title ~~ "_une").all(\.$title)
    print("wildcard", wildcard)
    print("contains", contains)
    print("prefix", prefix)
    print("suffix", suffix)
    print("notContains", notContains, "inList", inList)
    print("notIn", notIn, "between", between, "notEqual", notEqual)
}

wildcard ["Dune", "Dune Messiah", "Children of Dune", "God Emperor of Dune", "Heretics of Dune", "Chapterhouse: Dune"]
contains ["Dune", "Dune Messiah", "Children of Dune", "God Emperor of Dune", "Heretics of Dune", "Chapterhouse: Dune"]
prefix ["The Tombs of Atuan", "The Farthest Shore", ... 12 titles starting with "The "]
suffix ["Dune", "Children of Dune", "God Emperor of Dune", "Heretics of Dune", "Chapterhouse: Dune"]
notContains 22 inList ["Dune", "Dune Messiah", "Children of Dune", "The Left Hand of Darkness"]
notIn ["Pebble in the Sky", "The Killing Moon", "The City We Became"] between 6 notEqual 5

func groupedQueries(on db: any Database) async throws {
    // WHERE status = 'published' AND (title LIKE '%foundation%' OR title LIKE '%dune%')
    let titles = try await Book.query(on: db)
        .filter(\.$status == .published)
        .group(.or) { group in
            group.filter(\.$title ~~ "foundation")
            group.filter(\.$title ~~ "dune")
        }
        .sort(\.$title)
        .all(\.$title)
    print(titles.joined(separator: ", "))

    // Without group the second filter would be ANDed to everything: this matches nothing.
    let none = try await Book.query(on: db).filter(\.$title ~~ "foundation").filter(\.$title ~~ "dune").count()
    print("AND of both:", none)
}

Children of Dune, Dune, Dune Messiah, Foundation, Foundation and Empire, Foundation's Edge, God Emperor of Dune, Heretics of Dune, Second Foundation
AND of both: 0

WHERE "books"."status" = 'published' AND ("books"."title" LIKE ?1 OR "books"."title" LIKE ?2)

func windowQueries(on db: any Database) async throws {
    let base = { Book.query(on: db).sort(\.$publishedYear).sort(\.$id) }
    let limited = try await base().limit(3).all(\.$title)
    let paged = try await base().offset(3).limit(3).all(\.$title)
    let ranged = try await base().range(3..<6).all(\.$title)          // same rows: OFFSET 3 LIMIT 3
    print(limited, paged, ranged == paged)

    // range(23...) is OFFSET 23 with no LIMIT: PostgreSQL accepts it, SQLite and MySQL reject it
    do {
        _ = try await base().range(23...).all(\.$title)
    } catch {
        print("open-ended range failed:", error)
    }
    let tail = try await base().offset(23).limit(Int.max / 2).all(\.$title)   // portable form
    print(tail)
}

["I, Robot", "Pebble in the Sky", "Foundation"] ["Foundation and Empire", "Second Foundation", "The Caves of Steel"] true
open-ended range failed: error: near "OFFSET": syntax error
["The Obelisk Gate", "The Stone Sky", "The City We Became"]

func fieldQueries(on db: any Database) async throws {
    // Only the title column is read: SELECT books.title FROM books
    let titles: [String] = try await Book.query(on: db).filter(\.$status == .draft).sort(\.$title).all(\.$title)

    // SELECT DISTINCT published_year FROM books
    let years: [Int] = try await Book.query(on: db).unique().sort(\.$publishedYear).all(\.$publishedYear)

    // field(_:) builds a partial model: only the listed properties may be read
    let partial = try await Book.query(on: db).field(\.$title).field(\.$priceCents).sort(\.$title).limit(2).all()
    for book in partial { print(book.title, book.priceCents) }

    print(titles)
    print(years.count, years.first ?? 0, years.last ?? 0)
}

A Wizard of Earthsea 799
Chapterhouse: Dune 1149
["Pebble in the Sky", "The City We Became", "The Killing Moon"]
24 1950 2020

SELECT "books"."title" FROM "books" WHERE "books"."status" = 'draft' ORDER BY "books"."title" ASC
SELECT DISTINCT "books"."published_year" FROM "books" ORDER BY "books"."published_year" ASC
SELECT "books"."title", "books"."price_cents" FROM "books" ORDER BY "books"."title" ASC LIMIT 2

func chunkedQueries(on db: any Database) async throws {
    nonisolated(unsafe) var batches: [Int] = []
    try await Book.query(on: db).sort(\.$title).chunk(max: 10) { results in
        batches.append(results.count)
    }
    print("async chunk batch sizes:", batches)

    // Legacy bridge: the EventLoopFuture overload of chunk, awaited with .get(). It is used here only because
    // the async overload drops the last batch (see below); new code otherwise uses the async API.
    nonisolated(unsafe) var legacy: [Int] = []
    try await Book.query(on: db).sort(\.$title).chunk(max: 10) { results in
        legacy.append(results.count)
    }.get()
    print("future chunk batch sizes:", legacy)
}

async chunk batch sizes: [10, 10]
future chunk batch sizes: [10, 10, 6]

func aggregateQueries(on db: any Database) async throws {
    let total = try await Book.query(on: db).count()
    let published = try await Book.query(on: db).filter(\.$status == .published).count()
    let sum = try await Book.query(on: db).filter(\.$status == .published).sum(\.$priceCents)
    let cheapest = try await Book.query(on: db).min(\.$priceCents)
    let dearest = try await Book.query(on: db).max(\.$priceCents)
    let oldest = try await Book.query(on: db).min(\.$publishedYear)
    // average(_:) is typed as the column (Int here); ask for a Double explicitly
    let average = try await Book.query(on: db).filter(\.$status == .published)
        .aggregate(.average, \.$priceCents, as: Double.self)
    let none = try await Book.query(on: db).filter(\.$publishedYear > 3000).sum(\.$priceCents)

    print("count", total, "published", published, "sum", sum ?? -1, "min", cheapest ?? -1, "max", dearest ?? -1)
    print("oldest", oldest ?? -1, "average", average, "sum over no rows", String(describing: none))
}

count 26 published 21 sum 21479 min 799 max 1499
oldest 1950 average 1022.8095238095239 sum over no rows nil

SELECT COUNT("books"."id") AS "aggregate" FROM "books" WHERE "books"."status" = 'published'
SELECT SUM("books"."price_cents") AS "aggregate" FROM "books" WHERE "books"."status" = 'published'
SELECT AVG("books"."price_cents") AS "aggregate" FROM "books" WHERE "books"."status" = 'published'

func joinQueries(on db: any Database) async throws {
    // Join to filter and sort on the author without decoding it
    let herbert = try await Book.query(on: db)
        .join(parent: \.$author)
        .filter(Author.self, \.$name == "Frank Herbert")
        .sort(\.$publishedYear)
        .all(\.$title)
    print(herbert)

    // Join and decode: the joined model is read with joined(_:)
    let rows = try await Book.query(on: db)
        .join(Author.self, on: \Book.$author.$id == \Author.$id)
        .filter(\.$publishedYear < 1960)
        .sort(Author.self, \.$name)
        .sort(\.$title)
        .all()
    for book in rows {
        let author = try book.joined(Author.self)
        print("\(book.title) by \(author.name)")
    }

    // Many-to-many through the pivot: books tagged "fantasy"
    let fantasy = try await Book.query(on: db)
        .join(siblings: \.$tags)
        .filter(Tag.self, \.$name == "fantasy")
        .sort(\.$title)
        .all(\.$title)
    print(fantasy.count, fantasy.first ?? "")

    // Left join: all books, with the review when there is one
    let withReviews = try await Book.query(on: db)
        .join(Review.self, on: \Book.$id == \Review.$book.$id, method: .left)
        .filter(\.$publishedYear < 1955)
        .sort(\.$title)
        .all()
    for book in withReviews {
        let rating = (try? book.joined(Review.self))?.rating
        print(book.title, rating.map(String.init) ?? "no review")
    }
}

["Dune", "Dune Messiah", "Children of Dune", "God Emperor of Dune", "Heretics of Dune", "Chapterhouse: Dune"]
Foundation by Isaac Asimov
Foundation and Empire by Isaac Asimov
I, Robot by Isaac Asimov
Pebble in the Sky by Isaac Asimov
Second Foundation by Isaac Asimov
The Caves of Steel by Isaac Asimov
The Naked Sun by Isaac Asimov
12 A Wizard of Earthsea
Foundation 4
Foundation 3
Foundation and Empire no review
I, Robot no review
Pebble in the Sky no review
Second Foundation no review
The Caves of Steel no review

SELECT "books"."title" FROM "books" INNER JOIN "authors" ON "books"."author_id" = "authors"."id" WHERE "authors"."name" = ?1 ORDER BY "books"."published_year" ASC

final class Reviewer: ModelAlias {
    static let name = "reviewers"
    let model = User()
}

final class Borrower: ModelAlias {
    static let name = "borrowers"
    let model = User()
}

func aliasQueries(on db: any Database) async throws {
    // Books reviewed by one user and loaned to a different one: the users table is joined twice
    let books = try await Book.query(on: db)
        .join(Review.self, on: \Book.$id == \Review.$book.$id)
        .join(Reviewer.self, on: \Review.$user.$id == \Reviewer.$id)
        .join(Loan.self, on: \Book.$id == \Loan.$book.$id)
        .join(Borrower.self, on: \Loan.$user.$id == \Borrower.$id)
        .filter(\Reviewer.$email != \Borrower.$email)
        .sort(\.$title)
        .all()
    for book in books {
        let reviewer = try book.joined(Reviewer.self)
        let borrower = try book.joined(Borrower.self)
        print("\(book.title): reviewed by \(reviewer.email), loaned to \(borrower.email)")
    }
}

Dune: reviewed by reader@example.com, loaned to librarian@example.com
Dune: reviewed by librarian@example.com, loaned to reader@example.com
Dune: reviewed by admin@example.com, loaned to reader@example.com
Dune: reviewed by admin@example.com, loaned to librarian@example.com
Foundation: reviewed by admin@example.com, loaned to reader@example.com
The Fifth Season: reviewed by librarian@example.com, loaned to admin@example.com
The Fifth Season: reviewed by reader@example.com, loaned to admin@example.com

func batchQueries(on db: any Database) async throws {
    let before = try await Book.find(seedID(2, 19), on: db)   // a draft

    // UPDATE books SET status = 'published' WHERE status = 'draft' AND published_year < 1960
    try await Book.query(on: db)
        .filter(\.$status == .draft)
        .filter(\.$publishedYear < 1960)
        .set(\.$status, to: .published)
        .update()

    let after = try await Book.find(seedID(2, 19), on: db)
    print("status:", before?.status.rawValue ?? "-", "->", after?.status.rawValue ?? "-")
    print("updated_at bumped by the query update:", before?.updatedAt != after?.updatedAt)

    // several columns at once: a clearance price for every archived book, with updated_at set explicitly
    try await Book.query(on: db)
        .filter(\.$status == .archived)
        .set(\.$priceCents, to: 499)
        .set(\.$updatedAt, to: Date())
        .update()

    // DELETE FROM books WHERE status = 'draft'
    let drafts = try await Book.query(on: db).filter(\.$status == .draft).count()
    try await Book.query(on: db).filter(\.$status == .draft).delete()
    let remaining = try await Book.query(on: db).count()
    print("deleted drafts:", drafts, "remaining:", remaining)
}

status: draft -> published
updated_at bumped by the query update: true
deleted drafts: 2 remaining: 24

UPDATE "books" SET "status" = 'published', "updated_at" = ?1 WHERE "books"."status" = 'draft' AND "books"."published_year" < ?2
UPDATE "books" SET "price_cents" = ?1, "updated_at" = ?2 WHERE "books"."status" = 'archived'
DELETE FROM "books" WHERE "books"."status" = 'draft'

func offsetPages(on db: any Database) async throws {
    let first = try await Book.query(on: db)
        .filter(\.$status == .published)
        .sort(\.$title)
        .paginate(PageRequest(page: 1, per: 5))
    print(first.metadata.page, first.metadata.per, first.metadata.total, first.metadata.pageCount)
    print(first.items.map(\.title))

    // Out of range: still a page, with no items
    let beyond = try await Book.query(on: db)
        .filter(\.$status == .published)
        .sort(\.$title)
        .paginate(PageRequest(page: 99, per: 5))
    print(beyond.items.count, beyond.metadata.total)

    // Map to a DTO without losing the metadata
    let slim = first.map { BookSummary(id: $0.id, title: $0.title, year: $0.publishedYear) }
    print(slim.items.count)
}

1 5 21 5
["A Wizard of Earthsea", "Children of Dune", "Dune", "Dune Messiah", "Foundation"]
0 21
5

SELECT COUNT("books"."id") AS "aggregate" FROM "books" WHERE "books"."status" = 'published'
SELECT <every books column> FROM "books" WHERE "books"."status" = 'published' ORDER BY "books"."title" ASC LIMIT 5 OFFSET 0
SELECT <every books column> FROM "books" WHERE "books"."status" = 'published' ORDER BY "books"."title" ASC LIMIT 5 OFFSET 490

struct BookCursor: Codable, Sendable {
    var year: Int
    var id: UUID

    var token: String {
        let data = try! JSONEncoder().encode(self)
        return data.base64EncodedString()
            .replacingOccurrences(of: "+", with: "-").replacingOccurrences(of: "/", with: "_")
            .replacingOccurrences(of: "=", with: "")
    }

    init(year: Int, id: UUID) { self.year = year; self.id = id }

    init(token: String) throws {
        var base64 = token.replacingOccurrences(of: "-", with: "+").replacingOccurrences(of: "_", with: "/")
        while base64.count % 4 != 0 { base64 += "=" }
        guard let data = Data(base64Encoded: base64), let cursor = try? JSONDecoder().decode(BookCursor.self, from: data) else {
            throw Abort(.badRequest, reason: "Malformed cursor.")
        }
        self = cursor
    }
}

struct KeysetPage: Content {
    var items: [BookSummary]
    var next: String?
}

func keysetPage(on db: any Database, after cursor: BookCursor?, limit: Int, status: BookStatus = .published) async throws -> KeysetPage {
    let limit = min(max(limit, 1), 100)
    let query = Book.query(on: db).filter(\.$status == status)
    if let cursor {
        // (published_year, id) > (cursor.year, cursor.id). The redundant ">=" lets the planner seek into
        // an index on (published_year, id); the OR group alone is not sargable in SQLite.
        query.filter(\.$publishedYear >= cursor.year)
        query.group(.or) { rows in
            rows.filter(\.$publishedYear > cursor.year)
            rows.group(.and) { tie in
                tie.filter(\.$publishedYear == cursor.year)
                tie.filter(\.$id > cursor.id)
            }
        }
    }
    // Fetch one row more than asked for to learn whether another page exists, without a COUNT.
    let rows = try await query.sort(\.$publishedYear).sort(\.$id).limit(limit + 1).all()
    let page = rows.prefix(limit)
    let next = rows.count > limit ? page.last.flatMap { last in last.id.map { BookCursor(year: last.publishedYear, id: $0).token } } : nil
    return KeysetPage(items: page.map { BookSummary(id: $0.id, title: $0.title, year: $0.publishedYear) }, next: next)
}

1950 I, Robot | 1951 Foundation | 1952 Foundation and Empire | 1953 Second Foundation | 1954 The Caves of Steel | 1965 Dune | 1968 A Wizard of Earthsea | 1969 Dune Messiah
1969 The Left Hand of Darkness | 1971 The Tombs of Atuan | 1972 The Farthest Shore | 1974 The Dispossessed | 1976 Children of Dune | 1981 God Emperor of Dune | 1982 Foundation's Edge | 1984 Heretics of Dune
1990 Tehanu | 2010 The Hundred Thousand Kingdoms | 2015 The Fifth Season | 2016 The Obelisk Gate | 2017 The Stone Sky

SELECT <every books column> FROM "books"
WHERE "books"."status" = 'published'
  AND "books"."published_year" >= ?1
  AND ("books"."published_year" > ?2 OR ("books"."published_year" = ?3 AND "books"."id" > ?4))
ORDER BY "books"."published_year" ASC, "books"."id" ASC
LIMIT 9

/// Query parameters of GET /books, e.g. /books?q=dune&status=published&minYear=1960&sort=year&dir=desc&page=1&per=5
struct BookSearch: Content {
    var q: String?
    var status: BookStatus?
    var author: String?
    var minYear: Int?
    var maxYear: Int?
    var sort: Sort?
    var dir: Direction?
    var page: Int?
    var per: Int?

    enum Sort: String, Codable { case title, year, price }
    enum Direction: String, Codable { case asc, desc }
}

extension BookSearch {
    /// Builds the query. Every parameter narrows the result; none can widen it.
    func query(on db: any Database) -> QueryBuilder<Book> {
        let query = Book.query(on: db)
        if let q, !q.isEmpty {
            // Escape LIKE wildcards: Fluent binds the text but does not escape % and _ inside it.
            let escaped = q.replacingOccurrences(of: "%", with: "").replacingOccurrences(of: "_", with: "")
            if !escaped.isEmpty { query.filter(\.$title ~~ escaped) }
        }
        if let status { query.filter(\.$status == status) }
        if let minYear { query.filter(\.$publishedYear >= minYear) }
        if let maxYear { query.filter(\.$publishedYear <= maxYear) }
        if let author {
            query.join(parent: \.$author).filter(Author.self, \.$name == author)
        }
        let direction: DatabaseQuery.Sort.Direction = dir == .desc ? .descending : .ascending
        switch sort ?? .title {
        case .title: query.sort(\.$title, direction)
        case .year: query.sort(\.$publishedYear, direction)
        case .price: query.sort(\.$priceCents, direction)
        }
        // A unique tiebreaker keeps page boundaries stable when the sort column has duplicates.
        return query.sort(\.$id)
    }
}

func searchRoutes(_ routes: some RoutesBuilder) {
    routes.get("books") { req async throws -> Page<BookSummary> in
        let search = try req.query.decode(BookSearch.self)
        let request = PageRequest(page: search.page ?? 1, per: search.per ?? 10)
        let page = try await search.query(on: req.db).paginate(request)
        return page.map { BookSummary(id: $0.id, title: $0.title, year: $0.publishedYear) }
    }
}

func paginationRoutes(_ routes: some RoutesBuilder) {
    // paginate(for:) decodes ?page=&per= from the query string itself (defaults: page 1, per 10)
    routes.get("books", "simple") { req async throws -> Page<Book> in
        try await Book.query(on: req.db).sort(\.$title).paginate(for: req)
    }

    routes.get("books", "keyset") { req async throws -> KeysetPage in
        let after = try req.query.get(String?.self, at: "after").map { try BookCursor(token: $0) }
        let limit = try req.query.get(Int?.self, at: "limit") ?? 5
        return try await keysetPage(on: req.db, after: after, limit: limit)
    }

    routes.get("stats") { req async throws -> Stats in
        let published = Book.query(on: req.db).filter(\.$status == .published)
        return Stats(
            books: try await published.copy().count(),
            cheapest: try await published.copy().min(\.$priceCents),
            dearest: try await published.copy().max(\.$priceCents),
            averageCents: try await published.copy().aggregate(.average, \.$priceCents, as: Double?.self)
        )
    }
}

struct Stats: Content {
    var books: Int
    var cheapest: Int?
    var dearest: Int?
    var averageCents: Double?
}

$ swift run App serve --hostname 127.0.0.1 --port 8089

$ curl -s 'http://127.0.0.1:8089/books?q=dune&sort=year&dir=desc&per=3'
{"items":[{"id":"00000002-0000-0000-0000-000000000006","title":"Chapterhouse: Dune","year":1985},
          {"id":"00000002-0000-0000-0000-000000000005","title":"Heretics of Dune","year":1984},
          {"id":"00000002-0000-0000-0000-000000000004","title":"God Emperor of Dune","year":1981}],
 "metadata":{"page":1,"per":3,"total":6}}

$ curl -s 'http://127.0.0.1:8089/books?status=published&author=Ursula%20K.%20Le%20Guin&minYear=1970&sort=price&dir=desc&per=2&page=2'
{"items":[{"id":"00000002-0000-0000-0000-000000000008","title":"The Tombs of Atuan","year":1971},
          {"id":"00000002-0000-0000-0000-000000000009","title":"The Farthest Shore","year":1972}],
 "metadata":{"page":2,"per":2,"total":4}}

$ curl -s 'http://127.0.0.1:8089/stats'
{"cheapest":799,"averageCents":1022.8095238095239,"books":21,"dearest":1399}

$ curl -s 'http://127.0.0.1:8089/books/keyset?limit=3'
{"items":[{"id":"00000002-0000-0000-0000-000000000010","title":"I, Robot","year":1950},
          {"id":"00000002-0000-0000-0000-00000000000D","title":"Foundation","year":1951},
          {"id":"00000002-0000-0000-0000-00000000000E","title":"Foundation and Empire","year":1952}],
 "next":"eyJ5ZWFyIjoxOTUyLCJpZCI6IjAwMDAwMDAyLTAwMDAtMDAwMC0wMDAwLTAwMDAwMDAwMDAwRSJ9"}

$ curl -si 'http://127.0.0.1:8089/books/keyset?after=garbage'
HTTP/1.1 400 Bad Request
{"error":true,"reason":"Malformed cursor."}

$ curl -si 'http://127.0.0.1:8089/books?minYear=abc'
HTTP/1.1 400 Bad Request
{"error":true,"reason":"Value was not of type 'Int' at path 'minYear'. Data found at 'minYear' was not Int."}

$ curl -s 'http://127.0.0.1:8089/books?per=1000&page=0'      # metadata: {"page":1,"per":20,"total":26}, 20 items