Classgrdb 7.3.0GRDB

    Statement

    A prepared statement.

    Statement.swift:37Statement.md
    final class Statement
    Mangled symbol
    s4GRDB9StatementC What are these?
    FNV24 hash
    1DZYE

    Overview

    Prepared statements let you execute an SQL query several times, with different arguments if needed.

    Reusing prepared statements is a performance optimization technique because SQLite parses and analyses the SQL query only once, when the prepared statement is created.

    Building Prepared Statements

    Build a prepared statement with the makeStatement(sql:) method:

    try dbQueue.write { db in
    let insertStatement = try db.makeStatement(sql: """
        INSERT INTO player (name, score) VALUES (:name, :score)
        """)
    
    let selectStatement = try db.makeStatement(sql: """
        SELECT * FROM player WHERE name = ?
        """)
}

    The ? and colon-prefixed keys like :name in the SQL query are the statement arguments. Set the values for those arguments with arrays or dictionaries of database values, or StatementArguments instances:

    insertStatement.arguments = ["name": "Arthur", "score": 1000]
selectStatement.arguments = ["Arthur"]

    Alternatively, the makeStatement(literal:) method creates prepared statements with support for SQL Interpolation:

    let insertStatement = try db.makeStatement(literal: "INSERT ...")
let selectStatement = try db.makeStatement(literal: "SELECT ...")
//                                         ~~~~~~~

    The makeStatement methods throw an error of code SQLITE_MISUSE (21) if the SQL query contains multiple statements joined with a semicolon. See Parsing Multiple Prepared Statements from a Single SQL String below.

    Executing Prepared Statements and Fetching Values

    Prepared statements can be executed:

    try insertStatement.execute()

    To fetch rows and values from a prepared statement, use a fetching method of Row, DatabaseValueConvertible, or FetchableRecord:

    let players = try Player.fetchCursor(selectStatement) // A Cursor of Player
let players = try Player.fetchAll(selectStatement)    // [Player]
let players = try Player.fetchSet(selectStatement)    // Set<Player>
let player =  try Player.fetchOne(selectStatement)     // Player?
//                ~~~~~~ or Row, Int, String, Date, etc.

    Arguments can be set at the moment of the statement execution:

    try insertStatement.execute(arguments: ["name": "Arthur", "score": 1000])
let player = try Player.fetchOne(selectStatement, arguments: ["Arthur"])

    Caching Prepared Statements

    When the same query will be used several times in the lifetime of an application, one may feel a natural desire to cache prepared statements.

    Don’t cache statements yourself.

    Instead, use the cachedStatement(sql:) method. GRDB does all the hard caching and memory management:

    let statement = try db.cachedStatement(sql: "INSERT ...")

    The variant cachedStatement(literal:) supports SQL Interpolation:

    let statement = try db.cachedStatement(literal: "INSERT ...")

    Should a cached prepared statement throw an error, don’t reuse it. Instead, reload one from the cache.

    Parsing Multiple Prepared Statements from a Single SQL String

    To build multiple statements joined with a semicolon, use allStatements(sql:arguments:):

    let statements = try db.allStatements(sql: """
    INSERT INTO player (name, score) VALUES (?, ?);
    INSERT INTO player (name, score) VALUES (?, ?);
    """, arguments: ["Arthur", 100, "O'Brien", 1000])
while let statement = try statements.next() {
    try statement.execute()
}

    The variant allStatements(literal:) supports SQL Interpolation:

    let statements = try db.allStatements(literal: """
    INSERT INTO player (name, score) VALUES (\("Arthur"), \(100));
    INSERT INTO player (name, score) VALUES (\("O'Brien"), \(1000));
    """)
// An alternative way to iterate all statements
try statements.forEach { statement in
    try statement.execute()
}

    The results of multiple SELECT statements can be joined into a single Cursor. This is the GRDB version of the sqlite3_exec() function:

    let statements = try db.allStatements(sql: """
    SELECT ...; 
    SELECT ...; 
    """)
let players = try statements.flatMap { statement in
    try Player.fetchCursor(statement)
}
for let player = try players.next() { 
    print(player.name)
}

    The SQLStatementCursor returned from allStatements can be turned into a regular Swift array, but in this case make sure all individual statements can compile even if the previous ones were not executed:

    // OK: Array of statements
let statements = try Array(db.allStatements(sql: """
    INSERT ...; 
    UPDATE ...; 
    """))

// FAILURE: Can't build an array of statements since the INSERT won't
// compile until CREATE TABLE is executed.
let statements = try Array(db.allStatements(sql: """
    CREATE TABLE player ...; 
    INSERT INTO player ...;
    """))

    Executing a Prepared Statement

    Arguments

    Statement Informations