SQLKit and Raw SQL

// swift-tools-version:6.3
import PackageDescription

let package = Package(
    name: "App",
    platforms: [.macOS(.v13)],
    dependencies: [
        .package(url: "https://github.com/vapor/vapor.git", from: "4.122.2"),
        .package(url: "https://github.com/vapor/fluent.git", from: "4.13.0"),
        .package(url: "https://github.com/vapor/fluent-sqlite-driver.git", from: "4.9.0"),
        .package(url: "https://github.com/vapor/fluent-postgres-driver.git", from: "2.14.0"),
        .package(url: "https://github.com/vapor/sql-kit.git", from: "3.36.0"),
    ],
    targets: [
        .executableTarget(name: "App", dependencies: [
            .product(name: "Vapor", package: "vapor"),
            .product(name: "Fluent", package: "fluent"),
            .product(name: "FluentSQLiteDriver", package: "fluent-sqlite-driver"),
            .product(name: "FluentPostgresDriver", package: "fluent-postgres-driver"),
            .product(name: "SQLKit", package: "sql-kit"),
        ]),
    ]
)

import Fluent
import FluentSQL
import Vapor

struct AuthorName: Decodable {
    var name: String
}

func authorNames(_ req: Request) async throws -> [String] {
    if let sql = req.db as? any SQLDatabase {
        // The underlying database driver is SQL.
        let authors = try await sql.raw("SELECT name FROM authors ORDER BY name").all(decoding: AuthorName.self)
        return authors.map(\.name)
    } else {
        // The underlying database driver is _not_ SQL.
        return try await Author.query(on: req.db).sort(\.$name).all().map(\.name)
    }
}

import Fluent
import FluentSQL
import Vapor

extension Request {
    /// The request's database as a SQLKit database, or a 500 when the configured driver is not SQL.
    var sql: any SQLDatabase {
        get throws {
            guard let sql = self.db as? any SQLDatabase else {
                throw Abort(.internalServerError, reason: "The default database is not a SQL database")
            }
            return sql
        }
    }
}

/// A row of the reporting query: it is not a Fluent model, so it is a plain Codable struct.
struct BookRow: Content {
    var id: UUID
    var title: String
    var author: String
    var priceCents: Int
    var publishedYear: Int

    enum CodingKeys: String, CodingKey {
        case id, title, author
        case priceCents = "price_cents"
        case publishedYear = "published_year"
    }
}

struct AuthorStats: Content {
    var author: String
    var books: Int
    var reviews: Int
    var averageRating: Double?

    enum CodingKeys: String, CodingKey {
        case author, books, reviews
        case averageRating = "average_rating"
    }
}

struct PriceChange: Content {
    var id: UUID
    var priceCents: Int

    enum CodingKeys: String, CodingKey {
        case id
        case priceCents = "price_cents"
    }
}

import Fluent
import FluentSQL
import Vapor

func searchRoutes(_ app: Application) {
    let sql = app.grouped("sql")

    // Raw SQL with safe interpolation: \(bind:) turns the value into a bound parameter.
    sql.get("search") { req async throws -> [BookRow] in
        let q = try req.query.get(String.self, at: "q")
        return try await req.sql.raw("""
            SELECT b.id, b.title, a.name AS author, b.price_cents, b.published_year
            FROM books b JOIN authors a ON a.id = b.author_id
            WHERE b.title LIKE \(bind: "%" + q + "%")
            ORDER BY b.title
            """).all(decoding: BookRow.self)
    }
}

$ curl -s 'http://localhost:8091/sql/search?q=The'
[{"price_cents":1499,"title":"The Dispossessed","published_year":1974,"author":"Ursula K. Le Guin","id":"460CCD56-7622-47A8-9200-2A6C6918CA7D"},{"price_cents":1599,"title":"The Fellowship of the Ring","published_year":1954,"author":"J. R. R. Tolkien","id":"D404897B-6E7E-44D0-997E-8A78557F0183"},{"price_cents":1299,"title":"The Hobbit","published_year":1937,"author":"J. R. R. Tolkien","id":"F737BF67-7EEB-4932-A2F2-F14F09EF487A"}]

[ DEBUG ] Executing query [binds: [%Hobbit%], database-id: sqlite, request-id: 0D879DF8-..., sql: SELECT b.id, b.title, a.name AS author, b.price_cents, b.published_year ...

import Fluent
import FluentSQL
import Vapor

func modelRoutes(_ app: Application) {
    let sql = app.grouped("sql")

    // SQL rows decoded into a Fluent model: column names must be the model's field keys.
    sql.get("published-models") { req async throws -> [String] in
        let rows = try await req.sql.raw("""
            SELECT * FROM books WHERE status = \(bind: BookStatus.published) ORDER BY title
            """).all()
        return try rows.map { try $0.decode(fluentModel: Book.self).title }
    }
}

import Fluent
import FluentSQL
import Vapor

struct BookQuery: Content {
    var minYear: Int?
    var maxPriceCents: Int?
    var sort: String?
    var descending: Bool?
}

func bookListRoutes(_ app: Application) {
    let sql = app.grouped("sql")

    // Builder: dynamic filters, an allow-listed sort column and a join.
    sql.get("books") { req async throws -> [BookRow] in
        let query = try req.query.decode(BookQuery.self)
        let sortable: Set<String> = ["title", "price_cents", "published_year"]
        let sortColumn = query.sort ?? "title"
        guard sortable.contains(sortColumn) else {
            throw Abort(.badRequest, reason: "Cannot sort by \(sortColumn)")
        }
        var select = try req.sql.select()
            .column(SQLColumn("id", table: "books"))
            .column(SQLColumn("title", table: "books"))
            .column(SQLColumn("name", table: "authors"), as: "author")
            .column(SQLColumn("price_cents", table: "books"))
            .column(SQLColumn("published_year", table: "books"))
            .from("books")
            .join("authors", on: SQLColumn("author_id", table: "books"), .equal, SQLColumn("id", table: "authors"))
        if let minYear = query.minYear {
            select = select.where(SQLColumn("published_year", table: "books"), .greaterThanOrEqual, SQLBind(minYear))
        }
        if let maxPrice = query.maxPriceCents {
            select = select.where(SQLColumn("price_cents", table: "books"), .lessThanOrEqual, SQLBind(maxPrice))
        }
        let direction: SQLDirection = (query.descending ?? false) ? .descending : .ascending
        return try await select
            .orderBy(SQLColumn(sortColumn, table: "books"), direction)
            .all(decoding: BookRow.self)
    }
}

$ curl -s 'http://localhost:8091/sql/books?minYear=1950&maxPriceCents=1500'
[{"id":"AEB16D3C-A724-4CAF-80A9-CA10C121FBBD","author":"Ursula K. Le Guin","price_cents":1199,"published_year":1968,"title":"A Wizard of Earthsea"},{"id":"EE1CDF1E-F183-4AD5-AB4B-A4BB276CDE5A","author":"Ursula K. Le Guin","price_cents":1499,"published_year":1974,"title":"The Dispossessed"}]

$ curl -s -w ' [%{http_code}]\n' 'http://localhost:8091/sql/books?sort=title;DROP'
{"error":true,"reason":"Cannot sort by title;DROP"} [400]

binds: [1950, 1500], sql: SELECT "books"."id", "books"."title", "authors"."name" AS "author", "books"."price_cents", "books"."published_year" FROM "books" INNER JOIN "authors" ON "books"."author_id" = "authors"."id" WHERE "books"."published_year" >= ?1 AND "books"."price_cents" <= ?2 ORDER BY "books"."title" ASC

import Fluent
import FluentSQL
import Vapor

func reportRoutes(_ app: Application) {
    let sql = app.grouped("sql")

    // Aggregation that the Fluent query builder cannot express in one statement.
    sql.get("authors", "stats") { req async throws -> [AuthorStats] in
        try await req.sql.raw("""
            SELECT a.name AS author,
                   COUNT(DISTINCT b.id) AS books,
                   COUNT(r.id) AS reviews,
                   AVG(r.rating) AS average_rating
            FROM authors a
            LEFT JOIN books b ON b.author_id = a.id
            LEFT JOIN reviews r ON r.book_id = b.id
            GROUP BY a.id, a.name
            HAVING COUNT(DISTINCT b.id) >= \(bind: 1)
            ORDER BY average_rating DESC, a.name
            """).all(decoding: AuthorStats.self)
    }
}

$ curl -s http://localhost:8091/sql/authors/stats
[{"reviews":3,"average_rating":4,"author":"J. R. R. Tolkien","books":2},{"reviews":2,"average_rating":4,"author":"Ursula K. Le Guin","books":2}]

import Fluent
import FluentSQL
import Vapor

func discountRoutes(_ app: Application) {
    let sql = app.grouped("sql")

    // UPDATE ... RETURNING through the builder, with an expression on the right-hand side.
    sql.post("books", ":bookID", "discount") { req async throws -> PriceChange in
        let bookID = try req.parameters.require("bookID", as: UUID.self)
        let percent = try req.query.get(Int.self, at: "percent")
        guard (1...90).contains(percent) else { throw Abort(.badRequest, reason: "percent must be 1...90") }
        let changed = try await req.sql.update("books")
            .set(SQLColumn("price_cents"), to: SQLQueryString("price_cents * \(literal: 100 - percent) / 100"))
            .where("id", .equal, bookID)
            .returning("id", "price_cents")
            .first(decoding: PriceChange.self)
        guard let changed else { throw Abort(.notFound) }
        return changed
    }
}

$ curl -s -X POST 'http://localhost:8091/sql/books/D30B9415-DD3E-4132-8905-4ED8672E1663/discount?percent=10'
{"id":"D30B9415-DD3E-4132-8905-4ED8672E1663","price_cents":1169}

binds: [D30B9415-DD3E-4132-8905-4ED8672E1663], sql: UPDATE "books" SET "price_cents" = price_cents * 90 / 100 WHERE "id" = ?1 RETURNING "id", "price_cents"

import Fluent
import FluentSQL
import Vapor

/// An Encodable value (not a Fluent model) whose property names are the column names.
struct NewTag: Encodable {
    var id: UUID
    var name: String
}

struct TagID: Content {
    var id: UUID
}

func tagRoutes(_ app: Application) {
    let sql = app.grouped("sql")

    // Upsert: INSERT ... ON CONFLICT DO UPDATE ... RETURNING, with an Encodable value as the row.
    sql.put("tags") { req async throws -> TagID in
        let name = try req.query.get(String.self, at: "name")
        let row = try await req.sql.insert(into: "tags")
            .model(NewTag(id: UUID(), name: name))
            .onConflict(with: "name") { $0.set(excludedValueOf: "name") }
            .returning("id")
            .first(decoding: TagID.self)
        guard let row else { throw Abort(.internalServerError) }
        return row
    }

    // A plain INSERT that violates the unique index: the driver error says it is a constraint failure.
    sql.post("tags") { req async throws -> HTTPStatus in
        let name = try req.query.get(String.self, at: "name")
        do {
            try await req.sql.insert(into: "tags").model(NewTag(id: UUID(), name: name)).run()
        } catch let error as any DatabaseError where error.isConstraintFailure {
            throw Abort(.conflict, reason: "Tag \(name) already exists")
        }
        return .created
    }
}

$ for m in PUT PUT POST POST; do curl -s -w ' [%{http_code}]\n' -X $m 'http://localhost:8091/sql/tags?name=fantasy'; done
{"id":"40465C01-2F7F-4F70-9C5D-5F84B4DEB9D3"} [200]
{"id":"40465C01-2F7F-4F70-9C5D-5F84B4DEB9D3"} [200]
{"error":true,"reason":"Tag fantasy already exists"} [409]
{"error":true,"reason":"Tag fantasy already exists"} [409]

import Fluent
import FluentSQL
import Vapor

func statusRoutes(_ app: Application) {
    let sql = app.grouped("sql")

    // IN (...) with one bound parameter per value.
    sql.get("by-status") { req async throws -> [String] in
        let statuses = try req.query.get([String].self, at: "status")
        guard !statuses.isEmpty else { throw Abort(.badRequest) }
        struct Row: Decodable { var title: String }
        let rows = try await req.sql.raw("SELECT title FROM books WHERE status IN (\(binds: statuses)) ORDER BY title")
            .all(decoding: Row.self)
        return rows.map(\.title)
    }
}

$ curl -s 'http://localhost:8091/sql/by-status?status=draft&status=archived'
["The Dispossessed"]

import Fluent
import FluentSQL
import Vapor

struct LoanClosed: Content {
    var closed: Int
}

func loanRoutes(_ app: Application) {
    let sql = app.grouped("sql")

    // A transaction: the closure receives a Fluent Database that is also a SQLDatabase.
    sql.post("loans", "close-overdue") { req async throws -> LoanClosed in
        let now = Date()
        let closed = try await req.db.transaction { tx async throws -> Int in
            guard let sql = tx as? any SQLDatabase else { throw Abort(.internalServerError) }
            let due = try await sql.raw("SELECT id FROM loans WHERE returned_at IS NULL AND due_at < \(bind: now)").all()
            for row in due {
                let id = try row.decode(column: "id", as: UUID.self)
                try await sql.update("loans").set("returned_at", to: now).where("id", .equal, id).run()
            }
            return due.count
        }
        return LoanClosed(closed: closed)
    }
}

$ curl -s -w ' [%{http_code}]\n' -X POST http://localhost:8091/sql/loans/close-overdue
{"closed":1} [200]
$ curl -s -w ' [%{http_code}]\n' -X POST http://localhost:8091/sql/loans/close-overdue
{"closed":0} [200]

import SQLKit
import Vapor

struct Counts: Content {
    var authors: Int
    var books: Int
}

func countsOnOneConnection(_ db: any SQLDatabase) async throws -> Counts {
    try await db.withSession { session in
        let authors = try await session.raw("SELECT COUNT(*) AS n FROM authors")
            .first(decodingColumn: "n", as: Int.self) ?? 0
        let books = try await session.raw("SELECT COUNT(*) AS n FROM books")
            .first(decodingColumn: "n", as: Int.self) ?? 0
        return Counts(authors: authors, books: books)
    }
}

$ curl -s http://localhost:8091/sql/counts
{"books":4,"authors":3}

import Fluent
import FluentSQL
import Vapor

func mixedRoutes(_ app: Application) {
    let sql = app.grouped("sql")

    // A raw predicate inside a Fluent query: the rest of the query stays portable and typed.
    sql.get("fluent-mixed") { req async throws -> [String] in
        let needle = try req.query.get(String.self, at: "q").lowercased()
        let books = try await Book.query(on: req.db)
            .filter(.sql(embed: "LOWER(\(ident: "title")) LIKE \(bind: "%" + needle + "%")"))
            .sort(\.$title)
            .all()
        return books.map(\.title)
    }
}

$ curl -s 'http://localhost:8091/sql/fluent-mixed?q=THE%20h'
["The Hobbit"]

import Fluent
import FluentSQL

/// Page-local migration: an index that the Fluent schema builder cannot express portably is created with SQLKit.
struct AddBookReportingIndex: AsyncMigration {
    func prepare(on database: any Database) async throws {
        guard let sql = database as? any SQLDatabase else { return }
        try await sql.create(index: "idx_books_author_year")
            .on("books")
            .column("author_id")
            .column("published_year")
            .run()
    }

    func revert(on database: any Database) async throws {
        guard let sql = database as? any SQLDatabase else { return }
        try await sql.drop(index: "idx_books_author_year").run()
    }
}

import Fluent
import FluentSQL
import Vapor

func dialectRoutes(_ app: Application) {
    let sql = app.grouped("sql")

    // The dialect and the final SQL text: serialize() shows what the driver sends.
    sql.get("explain") { req async throws -> [String: String] in
        let db = try req.sql
        let query = db.select().column("title").from("books")
            .where("price_cents", .lessThan, 1500).where("status", .equal, "published").query
        let (text, binds) = db.serialize(query)
        return ["dialect": db.dialect.name, "sql": text, "binds": "\(binds)"]
    }
}

$ curl -s http://localhost:8091/sql/explain
{"dialect":"sqlite","sql":"SELECT \"title\" FROM \"books\" WHERE \"price_cents\" < ?1 AND \"status\" = ?2","binds":"[1500, \"published\"]"}

import Fluent
import FluentSQL
import Vapor

func unsafeRoutes(_ app: Application) {
    let sql = app.grouped("sql")

    // The anti-pattern, kept to show what it does: the client's text becomes part of the statement.
    sql.get("unsafe-search") { req async throws -> [String] in
        let q = try req.query.get(String.self, at: "q")
        struct Row: Decodable { var title: String }
        let rows = try await req.sql.raw("SELECT title FROM books WHERE title LIKE '%\(unsafeRaw: q)%' AND status = 'published'").all(decoding: Row.self)
        return rows.map(\.title)
    }
}

$ curl -s -G http://localhost:8091/sql/unsafe-search --data-urlencode "q=Hobbit"
["The Hobbit"]
$ curl -s -G http://localhost:8091/sql/unsafe-search --data-urlencode "q=zzz' OR status = 'draft' OR title LIKE '"
["The Hobbit","The Fellowship of the Ring","A Wizard of Earthsea","The Dispossessed"]
$ curl -s -G http://localhost:8091/sql/unsafe-search --data-urlencode "q=' UNION SELECT email FROM users --"
["A Wizard of Earthsea","The Dispossessed","The Fellowship of the Ring","The Hobbit","reader@example.com"]

$ curl -s -G http://localhost:8091/sql/search --data-urlencode "q=zzz' OR status = 'draft' OR title LIKE '"
[]

import Fluent
import FluentPostgresDriver
import FluentSQL
import PostgresNIO
import Vapor

/// Compile-only: needs a live PostgreSQL server.
struct PostgresDemo {
    /// Cast to the driver-specific protocol and run a simple (text protocol) query.
    static func version(_ req: Request) async throws -> String {
        guard let postgres = req.db as? any PostgresDatabase else {
            throw Abort(.internalServerError, reason: "Not PostgreSQL")
        }
        // Legacy bridge: simpleQuery returns EventLoopFuture and has no async overload on PostgresDatabase.
        let rows = try await postgres.simpleQuery("SELECT version()").get()
        return try rows.first?.decode(String.self) ?? "unknown"
    }

    /// JSONB: store a document and query it with the containment operator.
    static func tagged(_ req: Request, key: String, value: String) async throws -> [String] {
        let sql = try req.sql
        let filter = "{\"\(key)\": \"\(value)\"}"
        struct Row: Decodable { var title: String }
        return try await sql.raw("""
            SELECT title FROM books WHERE metadata @> \(bind: filter)::jsonb ORDER BY title
            """).all(decoding: Row.self).map(\.title)
    }

    /// Publishing a notification is plain SQL on any pooled connection.
    static func notify(_ req: Request, channel: String, payload: String) async throws {
        try await req.sql.raw("SELECT pg_notify(\(bind: channel), \(bind: payload))").run()
    }

    /// Listening needs one dedicated connection that lives as long as the listener.
    static func listen(on channel: String, app: Application) async throws {
        struct MissingDatabasePassword: Error {}
        // Production must provide the password; other environments may fall back to the local placeholder.
        let password: String
        if let fromEnvironment = Environment.get("DATABASE_PASSWORD") {
            password = fromEnvironment
        } else if app.environment == .production {
            throw MissingDatabasePassword()
        } else {
            password = "placeholder"
        }
        let configuration = PostgresConnection.Configuration(
            host: Environment.get("DATABASE_HOST") ?? "localhost",
            port: 5432,
            username: Environment.get("DATABASE_USERNAME") ?? "vapor_username",
            password: password,
            database: Environment.get("DATABASE_NAME") ?? "vapor_database",
            tls: .disable
        )
        let connection = try await PostgresConnection.connect(
            on: app.eventLoopGroup.next(), configuration: configuration, id: 1, logger: app.logger
        )
        do {
            try await connection.listen(on: channel) { notifications in
                for try await notification in notifications {
                    app.logger.info("notification", metadata: ["channel": "\(channel)", "payload": "\(notification.payload)"])
                }
            }
        } catch {
            try? await connection.close()
            throw error
        }
        try await connection.close()
    }
}