Building and Executing Requests

A request is a description of a database query. Requests are built using Swift methods, then executed to fetch data from the database.

Creating Base Requests

All requests start from a record type that conforms to TableRecord:

struct Player: TableRecord {
    static let databaseTableName = "player"
    var id: Int64
    var name: String
    var score: Int
}

// Request all players
let request = Player.all()

// Empty request (fetches nothing)
let empty = Player.none()

Player.all() returns a QueryInterfaceRequest<Player> - a request that fetches Player records.

Filtering Records

Use the filter method to add WHERE clauses:

Basic Filters

// Single condition
Player.filter { $0.score > 1000 }
// SELECT * FROM player WHERE score > 1000

// Multiple conditions with AND
Player.filter { $0.score > 1000 && $0.name != nil }
// SELECT * FROM player WHERE (score > 1000) AND (name IS NOT NULL)

// Multiple conditions with OR
Player.filter { $0.score > 1000 || $0.score < 0 }
// SELECT * FROM player WHERE (score > 1000) OR (score < 0)

Filter by Primary Key

// Single ID
Player.filter(id: 42)
// SELECT * FROM player WHERE id = 42

// Multiple IDs
Player.filter(ids: [1, 2, 3])
// SELECT * FROM player WHERE id IN (1, 2, 3)

// Compound primary key
CountryInfo.filter(key: ["code": "FR"])

Filter by Column Value

// Using Column directly
Player.filter(Column("name") == "Arthur")

// Using KeyPath
Player.filter(\.$name == "Arthur")

// Using coding keys
Player.filter(Player.Columns.name == "Arthur")

Comparison Operators

// Equality
Player.filter { $0.name == "Arthur" }

// Inequality
Player.filter { $0.name != "Arthur" }

// Comparisons
Player.filter { $0.score > 1000 }
Player.filter { $0.score >= 1000 }
Player.filter { $0.score < 1000 }
Player.filter { $0.score <= 1000 }

// Ranges
Player.filter((100...1000).contains($0.score))
// SELECT * FROM player WHERE score BETWEEN 100 AND 1000

// NULL checks
Player.filter { $0.email == nil }
// SELECT * FROM player WHERE email IS NULL

Player.filter { $0.email != nil }
// SELECT * FROM player WHERE email IS NOT NULL

String Matching

// LIKE operator
Player.filter(Column("name").like("Art%"))
// SELECT * FROM player WHERE name LIKE 'Art%'

// Case-insensitive search
Player.filter(Column("email").collating(.nocase) == "[email protected]")

// Pattern matching
Player.filter(Column("name").glob("Art*"))

IN Operator

// IN with array
let names = ["Arthur", "Barbara"]
Player.filter(names.contains($0.name))
// SELECT * FROM player WHERE name IN ('Arthur', 'Barbara')

// NOT IN
Player.filter(!names.contains($0.name))
// SELECT * FROM player WHERE name NOT IN ('Arthur', 'Barbara')

// IN with subquery
let subquery = Team.select(Column("name"))
Player.filter(subquery.contains($0.teamName))

Chaining Filters

Filters can be chained together (they combine with AND):

let request = Player.all()
    .filter { $0.score > 1000 }
    .filter { $0.name != nil }
    .filter { $0.createdAt > Date().addingTimeInterval(-86400) }
// SELECT * FROM player 
// WHERE (score > 1000) 
//   AND (name IS NOT NULL) 
//   AND (createdAt > ?)

Ordering Results

Use the order method to add ORDER BY clauses:

// Single column, ascending
Player.order(\.$score)
// SELECT * FROM player ORDER BY score

// Single column, descending
Player.order(\.$score.desc)
// SELECT * FROM player ORDER BY score DESC

// Multiple columns
Player.order(\.$score.desc, \.$name)
// SELECT * FROM player ORDER BY score DESC, name

Collations

Specify how strings are compared:

// Case-insensitive
Player.order(\.$name.collating(.nocase))

// Localized comparison
Player.order(\.$name.collating(.localizedCaseInsensitiveCompare))

// Custom collation
Player.order(\.$name.collating(.custom("UNICODE")))

Reversing Order

// Reverse entire ordering
let ascending = Player.order(\.$score)
let descending = ascending.reversed()
// SELECT * FROM player ORDER BY score DESC

Limiting Results

Control how many records are returned:

// First 10 records
Player.limit(10)
// SELECT * FROM player LIMIT 10

// Pagination with offset
Player.limit(10, offset: 20)
// SELECT * FROM player LIMIT 10 OFFSET 20

// Remove limit
let unlimited = Player.limit(10).unordered()

Selecting Columns

Choose which columns to retrieve:

Select All Columns

// Default: all columns
Player.all()
// SELECT * FROM player

Select Specific Columns

// Multiple columns
Player.select(\.$name, \.$score)
// SELECT name, score FROM player

// Using Column
Player.select(Column("name"), Column("score"))

// Mix columns and expressions
Player.select(\.$name, (\.$score * 2).forKey("doubleScore"))

Select Single Column

Fetch a simple type instead of a record:

let names = try Player
    .select(\.$name, as: String.self)
    .fetchAll(db) // [String]

Computed Columns

// Arithmetic
Player.select((\.$score + \.$bonus).forKey("total"))

// String concatenation
Player.select(
    (Column("firstName") + " " + Column("lastName")).forKey("fullName")
)

// Conditional expressions
Player.select(
    (\.$score > 1000).forKey("isExpert")
)

Aggregate Functions

// COUNT
let count = try Player.fetchCount(db)

// MAX
Player.select(max(\.$score), as: Int.self)

// MIN
Player.select(min(\.$score), as: Int.self)

// AVG
Player.select(average(\.$score), as: Double.self)

// SUM
Player.select(sum(\.$score), as: Int.self)

Grouping Results

Group records with GROUP BY:

// Group by single column
Player.group(\.$teamId)
// SELECT * FROM player GROUP BY teamId

// Group by multiple columns
Player.group(\.$teamId, \.$country)
// SELECT * FROM player GROUP BY teamId, country

// With aggregate
Player
    .select(\.$teamId, count(Column("*")).forKey("count"))
    .group(\.$teamId)
// SELECT teamId, COUNT(*) AS count FROM player GROUP BY teamId

Having Clause

Filter grouped results:

Player
    .select(\.$teamId, count(Column("*")).forKey("count"))
    .group(\.$teamId)
    .having(count(Column("*")) > 5)
// SELECT teamId, COUNT(*) AS count 
// FROM player 
// GROUP BY teamId 
// HAVING COUNT(*) > 5

Distinct Results

Remove duplicate rows:

// Distinct rows
Player.select(\.$country).distinct()
// SELECT DISTINCT country FROM player

// Distinct on expression
Player.select((\.$firstName + " " + \.$lastName).forKey("name")).distinct()

Fetching Results

Execute requests to fetch data from the database:

Fetch All Records

try dbQueue.read { db in
    let players = try Player
        .filter { $0.score > 1000 }
        .fetchAll(db) // [Player]
}

Fetch One Record

try dbQueue.read { db in
    // Returns first match or nil
    let player = try Player
        .filter(id: 42)
        .fetchOne(db) // Player?
}

Fetch Set

try dbQueue.read { db in
    let players = try Player
        .filter { $0.score > 1000 }
        .fetchSet(db) // Set<Player>
}

Fetch Cursor

Memory-efficient iteration over large result sets:

try dbQueue.read { db in
    let cursor = try Player
        .order(\.$score.desc)
        .fetchCursor(db)
    
    while let player = try cursor.next() {
        print("\(player.name): \(player.score)")
    }
}

Cursors must be consumed within the database access block. They become invalid once the block exits.

Fetch Count

try dbQueue.read { db in
    let count = try Player
        .filter { $0.score > 1000 }
        .fetchCount(db) // Int
}

Check Existence

try dbQueue.read { db in
    let exists = try Player
        .filter(id: 42)
        .isEmpty(db) // Bool
}

Advanced Techniques

Reusable Request Methods

Define requests as static methods:

extension Player {
    static func topScorers(limit: Int = 10) -> QueryInterfaceRequest<Player> {
        Player
            .filter { $0.score > 0 }
            .order(\.$score.desc)
            .limit(limit)
    }
    
    static func inTeam(_ teamId: Int64) -> QueryInterfaceRequest<Player> {
        Player.filter { $0.teamId == teamId }
    }
}

// Usage
let topPlayers = try Player.topScorers().fetchAll(db)
let teamPlayers = try Player.inTeam(5).fetchAll(db)

// Compose requests
let topTeamPlayers = try Player
    .inTeam(5)
    .order(\.$score.desc)
    .limit(10)
    .fetchAll(db)

Request Composition

func applyDefaultFilters(_ request: QueryInterfaceRequest<Player>) 
    -> QueryInterfaceRequest<Player> 
{
    request
        .filter { $0.isActive == true }
        .filter { $0.deletedAt == nil }
}

let activeTopScorers = applyDefaultFilters(Player.all())
    .order(\.$score.desc)
    .limit(10)

Conditional Queries

func searchPlayers(name: String?, minScore: Int?) -> QueryInterfaceRequest<Player> {
    var request = Player.all()
    
    if let name = name {
        request = request.filter { $0.name.like("%\(name)%") }
    }
    
    if let minScore = minScore {
        request = request.filter { $0.score >= minScore }
    }
    
    return request.order(\.$score.desc)
}

// Usage
try searchPlayers(name: "Art", minScore: 1000).fetchAll(db)
try searchPlayers(name: nil, minScore: 500).fetchAll(db)

Batch Operations

Update All

try dbQueue.write { db in
    try Player
        .filter { $0.score < 0 }
        .updateAll(db, \.$score.set(to: 0))
    // UPDATE player SET score = 0 WHERE score < 0
}

Delete All

try dbQueue.write { db in
    try Player
        .filter { $0.deletedAt != nil }
        .deleteAll(db)
    // DELETE FROM player WHERE deletedAt IS NOT NULL
}

Getting Started

Core Concepts

Records

Query Interface

Advanced Features

Guides

​Creating Base Requests

​Filtering Records

​Basic Filters

​Filter by Primary Key

​Filter by Column Value

​Comparison Operators

​String Matching

​IN Operator

​Chaining Filters

​Ordering Results

​Collations

​Reversing Order

​Limiting Results

​Selecting Columns

​Select All Columns

​Select Specific Columns

​Select Single Column

​Computed Columns

​Aggregate Functions

​Grouping Results

​Having Clause

​Distinct Results

​Fetching Results

​Fetch All Records

​Fetch One Record

​Fetch Set

​Fetch Cursor

​Fetch Count

​Check Existence

​Advanced Techniques

​Reusable Request Methods

​Request Composition

​Conditional Queries

​Batch Operations

​Update All

​Delete All

​Next Steps

Associations

SQL Interpolation

Build docs developers (and LLMs) love

Creating Base Requests

Filtering Records

Basic Filters

Filter by Primary Key

Filter by Column Value

Comparison Operators

String Matching

IN Operator

Chaining Filters

Ordering Results

Collations

Reversing Order

Limiting Results

Selecting Columns

Select All Columns

Select Specific Columns

Select Single Column

Computed Columns

Aggregate Functions

Grouping Results

Having Clause

Distinct Results

Fetching Results

Fetch All Records

Fetch One Record

Fetch Set

Fetch Cursor

Fetch Count

Check Existence

Advanced Techniques

Reusable Request Methods

Request Composition

Conditional Queries

Batch Operations

Update All

Delete All

Next Steps