Database

public final class Database : CustomStringConvertible, CustomDebugStringConvertible

A Database connection.

You don’t create a database directly. Instead, you use a DatabaseQueue, or a DatabasePool:

let dbQueue = DatabaseQueue(...)

// The Database is the `db` in the closure:
try dbQueue.write { db in
    try Player(...).insert(db)
}

SQLite C API

Configuration

Database Information

Limits

  • The maximum number of arguments accepted by an SQLite statement.

    For example, requests such as the one below must make sure the ids array does not contain more than maximumStatementArgumentCount elements:

    let ids: [Int] = ...
    try dbQueue.write { db in
        try Player.deleteAll(db, keys: ids)
    }
    

    See https://www.sqlite.org/limits.html and SQLITE_LIMIT_VARIABLE_NUMBER.

    Declaration

    Swift

    public var maximumStatementArgumentCount: Int { get }

Functions

  • Add or redefine an SQL function.

    let fn = DatabaseFunction("succ", argumentCount: 1) { dbValues in
        guard let int = Int.fromDatabaseValue(dbValues[0]) else {
            return nil
        }
        return int + 1
    }
    db.add(function: fn)
    try Int.fetchOne(db, sql: "SELECT succ(1)")! // 2
    

    Declaration

    Swift

    public func add(function: DatabaseFunction)
  • Remove an SQL function.

    Declaration

    Swift

    public func remove(function: DatabaseFunction)

Collations

  • Add or redefine a collation.

    let collation = DatabaseCollation("localized_standard") { (string1, string2) in
        return (string1 as NSString).localizedStandardCompare(string2)
    }
    db.add(collation: collation)
    try db.execute(sql: "CREATE TABLE files (name TEXT COLLATE localized_standard")
    

    Declaration

    Swift

    public func add(collation: DatabaseCollation)
  • Remove a collation.

    Declaration

    Swift

    public func remove(collation: DatabaseCollation)

Trace

  • Registers a tracing function.

    For example:

    // Trace all SQL statements executed by the database
    var configuration = Configuration()
    configuration.prepareDatabase { db in
        db.trace(options: .statement) { event in
            print("SQL: \(event)")
        }
    }
    let dbQueue = try DatabaseQueue(path: "...", configuration: configuration)
    

    Pass an empty options set in order to stop database tracing:

    // Stop tracing
    db.trace(options: [])
    

    See https://www.sqlite.org/c3ref/trace_v2.html for more information.

    Declaration

    Swift

    public func trace(options: TracingOptions = .statement, _ trace: ((TraceEvent) -> Void)? = nil)

    Parameters

    options

    The set of desired event kinds. Defaults to .statement, which notifies all executed database statements.

    trace

    the tracing function.

WAL Checkpoints

Database Suspension

Transactions & Savepoint

  • Executes a block inside a database transaction.

    try dbQueue.inDatabase do {
        try db.inTransaction {
            try db.execute(sql: "INSERT ...")
            return .commit
        }
    }
    

    If the block throws an error, the transaction is rollbacked and the error is rethrown.

    This method is not reentrant: you can’t nest transactions.

    Throws

    The error thrown by the block.

    Declaration

    Swift

    public func inTransaction(_ kind: TransactionKind? = nil, _ block: () throws -> TransactionCompletion) throws

    Parameters

    kind

    The transaction type (default nil). If nil, the transaction type is configuration.defaultTransactionKind, which itself defaults to .deferred. See https://www.sqlite.org/lang_transaction.html for more information.

    block

    A block that executes SQL statements and return either .commit or .rollback.

  • Executes a block inside a savepoint.

    try dbQueue.inDatabase do {
        try db.inSavepoint {
            try db.execute(sql: "INSERT ...")
            return .commit
        }
    }
    

    If the block throws an error, the savepoint is rollbacked and the error is rethrown.

    This method is reentrant: you can nest savepoints.

    Throws

    The error thrown by the block.

    Declaration

    Swift

    public func inSavepoint(_ block: () throws -> TransactionCompletion) throws

    Parameters

    block

    A block that executes SQL statements and return either .commit or .rollback.

  • Begins a database transaction.

    Throws

    The error thrown by the block.

    Declaration

    Swift

    public func beginTransaction(_ kind: TransactionKind? = nil) throws

    Parameters

    kind

    The transaction type (default nil). If nil, the transaction type is configuration.defaultTransactionKind, which itself defaults to .deferred. See https://www.sqlite.org/lang_transaction.html for more information.

  • Rollbacks a database transaction.

    Declaration

    Swift

    public func rollback() throws
  • Commits a database transaction.

    Declaration

    Swift

    public func commit() throws

Database Schema

  • Clears the database schema cache.

    You may need to clear the cache manually if the database schema is modified by another connection.

    Declaration

    Swift

    public func clearSchemaCache()
  • Returns whether a table exists in the main or temp schema.

    Declaration

    Swift

    public func tableExists(_ name: String) throws -> Bool
  • Returns whether a table is an internal SQLite table.

    Those are tables whose name begins with sqlite_ and pragma_.

    For more information, see https://www.sqlite.org/fileformat2.html

    Declaration

    Swift

    public static func isSQLiteInternalTable(_ tableName: String) -> Bool
  • Returns whether a table is an internal SQLite table.

    Those are tables whose name begins with sqlite_ and pragma_.

    For more information, see https://www.sqlite.org/fileformat2.html

    Declaration

    Swift

    @available(*, deprecated, message: "Use Database.isSQLiteInternalTable(_:﹚ static method instead.")
    public func isSQLiteInternalTable(_ tableName: String) -> Bool
  • Returns whether a table is an internal GRDB table.

    Those are tables whose name begins with “grdb_”.

    Declaration

    Swift

    public static func isGRDBInternalTable(_ tableName: String) -> Bool
  • Returns whether a table is an internal GRDB table.

    Those are tables whose name begins with “grdb_”.

    Declaration

    Swift

    @available(*, deprecated, message: "Use Database.isGRDBInternalTable(_:﹚ static method instead.")
    public func isGRDBInternalTable(_ tableName: String) -> Bool
  • Returns whether a view exists in the main or temp schema.

    Declaration

    Swift

    public func viewExists(_ name: String) throws -> Bool
  • Returns whether a trigger exists in the main or temp schema.

    Declaration

    Swift

    public func triggerExists(_ name: String) throws -> Bool
  • The primary key for table named tableName, in the main or temp schema.

    All tables have a primary key, even when it is not explicit. When a table has no explicit primary key, the result is the hidden “rowid” column.

    Throws

    A DatabaseError if table does not exist.

    Declaration

    Swift

    public func primaryKey(_ tableName: String) throws -> PrimaryKeyInfo
  • The indexes on table named tableName, in the main or temp schema.

    Only indexes on columns are returned. Indexes on expressions are not returned.

    SQLite does not define any index for INTEGER PRIMARY KEY columns: this method does not return any index that represents the primary key.

    If you want to know if a set of columns uniquely identify a row, prefer table(_:hasUniqueKey:) instead.

    Throws

    A DatabaseError if table does not exist.

    Declaration

    Swift

    public func indexes(on tableName: String) throws -> [IndexInfo]
  • True if a sequence of columns uniquely identifies a row, that is to say if the columns are the primary key, or if there is a unique index on them.

    Declaration

    Swift

    public func table<T: Sequence>(
        _ tableName: String,
        hasUniqueKey columns: T)
    throws -> Bool
    where T.Iterator.Element == String
  • The foreign keys defined on table named tableName, in the main or temp schema.

    Throws

    A DatabaseError if table does not exist.

    Declaration

    Swift

    public func foreignKeys(on tableName: String) throws -> [ForeignKeyInfo]
  • The columns in the table named tableName, in the main or temp schema.

    Throws

    A DatabaseError if table does not exist.

    Declaration

    Swift

    public func columns(in tableName: String) throws -> [ColumnInfo]

Statements

  • Returns a new prepared statement that can be reused.

    let statement = try db.makeStatement(sql: "SELECT * FROM player WHERE id = ?")
    let player1 = try Player.fetchOne(statement, arguments: [1])!
    let player2 = try Player.fetchOne(statement, arguments: [2])!
    
    let statement = try db.makeStatement(sql: "INSERT INTO player (name) VALUES (?)")
    try statement.execute(arguments: ["Arthur"])
    try statement.execute(arguments: ["Barbara"])
    

    Throws

    A DatabaseError whenever SQLite could not parse the sql query.

    Declaration

    Swift

    public func makeStatement(sql: String) throws -> Statement

    Parameters

    sql

    An SQL query.

    Return Value

    A Statement.

  • Returns a new prepared statement that can be reused.

    let statement = try db.makeStatement(literal: "SELECT * FROM player WHERE id = ?")
    let player1 = try Player.fetchOne(statement, arguments: [1])!
    let player2 = try Player.fetchOne(statement, arguments: [2])!
    
    let statement = try db.makeStatement(literal: "INSERT INTO player (name) VALUES (?)")
    try statement.execute(arguments: ["Arthur"])
    try statement.execute(arguments: ["Barbara"])
    

    Throws

    A DatabaseError whenever SQLite could not parse the sql query.

    Precondition

    No argument must be set, or all arguments must be set. An error is raised otherwise.

    // OK
    try makeStatement(literal: """
        SELECT COUNT(*) FROM player WHERE score > ?
        """)
    try makeStatement(literal: """
        SELECT COUNT(*) FROM player WHERE score > \(1000)
        """)
    
    // NOT OK
    try makeStatement(literal: """
        SELECT COUNT(*) FROM player
        WHERE color = ? AND score > \(1000)
        """)
    

    Declaration

    Swift

    public func makeStatement(literal sqlLiteral: SQL) throws -> Statement

    Parameters

    sqlLiteral

    An SQL literal.

    Return Value

    A Statement.

  • Returns a new prepared statement that can be reused.

    let statement = try db.makeSelectStatement(sql: "SELECT * FROM player WHERE id = ?")
    let player1 = try Player.fetchOne(statement, arguments: [1])!
    let player2 = try Player.fetchOne(statement, arguments: [2])!
    

    Throws

    A DatabaseError whenever SQLite could not parse the sql query.

    Declaration

    Swift

    @available(*, deprecated, renamed: "makeStatement(sql:﹚")
    public func makeSelectStatement(sql: String) throws -> Statement

    Parameters

    sql

    An SQL query.

    Return Value

    A Statement.

  • Returns a new prepared statement that can be reused.

    Throws

    A DatabaseError whenever SQLite could not parse the sql query.

    Precondition

    No argument must be set, or all arguments must be set. An error is raised otherwise.

    // OK
    try makeSelectStatement(literal: """
        SELECT COUNT(*) FROM player WHERE score > ?
        """)
    try makeSelectStatement(literal: """
        SELECT COUNT(*) FROM player WHERE score > \(1000)
        """)
    
    // NOT OK
    try makeSelectStatement(literal: """
        SELECT COUNT(*) FROM player
        WHERE color = ? AND score > \(1000)
        """)
    

    Declaration

    Swift

    @available(*, deprecated, renamed: "makeStatement(literal:﹚")
    public func makeSelectStatement(literal sqlLiteral: SQL) throws -> Statement

    Parameters

    sqlLiteral

    An SQL literal.

    Return Value

    A Statement.

  • Returns a prepared statement that can be reused.

    let statement = try db.cachedStatement(sql: "SELECT * FROM player WHERE id = ?")
    let player1 = try Player.fetchOne(statement, arguments: [1])!
    let player2 = try Player.fetchOne(statement, arguments: [2])!
    
    let statement = try db.cachedStatement(sql: "INSERT INTO player (name) VALUES (?)")
    try statement.execute(arguments: ["Arthur"])
    try statement.execute(arguments: ["Barbara"])
    

    The returned statement may have already been used: it may or may not contain values for its eventual arguments.

    Throws

    A DatabaseError whenever SQLite could not parse the sql query.

    Declaration

    Swift

    public func cachedStatement(sql: String) throws -> Statement

    Parameters

    sql

    An SQL query.

    Return Value

    A Statement.

  • Returns a prepared statement that can be reused.

    let statement = try db.cachedStatement(literal: "SELECT * FROM player WHERE id = ?")
    let player1 = try Player.fetchOne(statement, arguments: [1])!
    let player2 = try Player.fetchOne(statement, arguments: [2])!
    
    let statement = try db.cachedStatement(literal: "INSERT INTO player (name) VALUES (?)")
    try statement.execute(arguments: ["Arthur"])
    try statement.execute(arguments: ["Barbara"])
    

    Throws

    A DatabaseError whenever SQLite could not parse the sql query.

    Precondition

    No argument must be set, or all arguments must be set. An error is raised otherwise.

    // OK
    try cachedStatement(literal: """
        SELECT COUNT(*) FROM player WHERE score > ?
        """)
    try cachedStatement(literal: """
        SELECT COUNT(*) FROM player WHERE score > \(1000)
        """)
    
    // NOT OK
    try cachedStatement(literal: """
        SELECT COUNT(*) FROM player
        WHERE color = ? AND score > \(1000)
        """)
    

    Declaration

    Swift

    public func cachedStatement(literal sqlLiteral: SQL) throws -> Statement

    Parameters

    sqlLiteral

    An SQL literal.

    Return Value

    A Statement.

  • Returns a prepared statement that can be reused.

    let statement = try db.cachedSelectStatement(sql: "SELECT COUNT(*) FROM player WHERE score > ?")
    let moreThanTwentyCount = try Int.fetchOne(statement, arguments: [20])!
    let moreThanThirtyCount = try Int.fetchOne(statement, arguments: [30])!
    

    The returned statement may have already been used: it may or may not contain values for its eventual arguments.

    Throws

    A DatabaseError whenever SQLite could not parse the sql query.

    Declaration

    Swift

    @available(*, deprecated, renamed: "cachedStatement(sql:﹚")
    public func cachedSelectStatement(sql: String) throws -> Statement

    Parameters

    sql

    An SQL query.

    Return Value

    A Statement.

  • Returns a prepared statement that can be reused.

    let statement = try db.cachedSelectStatement(literal: "SELECT COUNT(*) FROM player WHERE score > \(20)")
    let moreThanTwentyCount = try Int.fetchOne(statement)!
    

    Throws

    A DatabaseError whenever SQLite could not parse the sql query.

    Precondition

    No argument must be set, or all arguments must be set. An error is raised otherwise.

    // OK
    try cachedSelectStatement(literal: """
        SELECT COUNT(*) FROM player WHERE score > ?
        """)
    try cachedSelectStatement(literal: """
        SELECT COUNT(*) FROM player WHERE score > \(1000)
        """)
    
    // NOT OK
    try cachedSelectStatement(literal: """
        SELECT COUNT(*) FROM player
        WHERE color = ? AND score > \(1000)
        """)
    

    Declaration

    Swift

    @available(*, deprecated, renamed: "cachedStatement(literal:﹚")
    public func cachedSelectStatement(literal sqlLiteral: SQL) throws -> Statement

    Parameters

    sqlLiteral

    An SQL literal.

    Return Value

    A Statement.

  • Returns a new prepared statement that can be reused.

    let statement = try db.makeUpdateStatement(sql: "INSERT INTO player (name) VALUES (?)")
    try statement.execute(arguments: ["Arthur"])
    try statement.execute(arguments: ["Barbara"])
    

    Throws

    A DatabaseError whenever SQLite could not parse the sql query.

    Declaration

    Swift

    @available(*, deprecated, renamed: "makeStatement(sql:﹚")
    public func makeUpdateStatement(sql: String) throws -> Statement

    Parameters

    sql

    An SQL query.

    Return Value

    An UpdateStatement.

  • Returns a new prepared statement that can be reused.

    Throws

    A DatabaseError whenever SQLite could not parse the sql query.

    Precondition

    No argument must be set, or all arguments must be set. An error is raised otherwise.

    // OK
    try makeUpdateStatement(literal: """
        UPDATE player SET name = ?
        """)
    try makeUpdateStatement(literal: """
        UPDATE player SET name = \("O'Brien")
        """)
    
    // NOT OK
    try makeUpdateStatement(literal: """
        UPDATE player SET name = ?, score = \(10)
        """)
    

    Declaration

    Swift

    @available(*, deprecated, renamed: "makeStatement(literal:﹚")
    public func makeUpdateStatement(literal sqlLiteral: SQL) throws -> Statement

    Parameters

    sqlLiteral

    An SQL literal.

    Return Value

    A Statement.

  • Returns a prepared statement that can be reused.

    let statement = try db.cachedUpdateStatement(sql: "INSERT INTO player (name) VALUES (?)")
    try statement.execute(arguments: ["Arthur"])
    try statement.execute(arguments: ["Barbara"])
    

    The returned statement may have already been used: it may or may not contain values for its eventual arguments.

    Throws

    A DatabaseError whenever SQLite could not parse the sql query.

    Declaration

    Swift

    @available(*, deprecated, renamed: "cachedStatement(sql:﹚")
    public func cachedUpdateStatement(sql: String) throws -> Statement

    Parameters

    sql

    An SQL query.

    Return Value

    A Statement.

  • Returns a new prepared statement that can be reused.

    Throws

    A DatabaseError whenever SQLite could not parse the sql query.

    Precondition

    No argument must be set, or all arguments must be set. An error is raised otherwise.

    // OK
    try cachedUpdateStatement(literal: """
        UPDATE player SET name = ?
        """)
    try cachedUpdateStatement(literal: """
        UPDATE player SET name = \("O'Brien")
        """)
    
    // NOT OK
    try cachedUpdateStatement(literal: """
        UPDATE player SET name = ?, score = \(10)
        """)
    

    Declaration

    Swift

    @available(*, deprecated, renamed: "cachedStatement(sql:﹚")
    public func cachedUpdateStatement(literal sqlLiteral: SQL) throws -> Statement

    Parameters

    sqlLiteral

    An SQL literal.

    Return Value

    A Statement.

  • Returns a cursor of all SQL statements separated by semi-colons.

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

    Throws

    A DatabaseError whenever an SQLite error occurs.

    Precondition

    Arguments must be nil, or all arguments must be set. The returned cursor will throw an error otherwise.

    // OK
    try allStatements(sql: """
        SELECT COUNT(*) FROM player WHERE score < ?;
        SELECT COUNT(*) FROM player WHERE score > ?;
        """)
    try allStatements(sql: """
        SELECT COUNT(*) FROM player WHERE score < ?;
        SELECT COUNT(*) FROM player WHERE score > ?;
        """, arguments: [1000, 1000])
    
    // NOT OK
    try allStatements(sql: """
        SELECT COUNT(*) FROM player WHERE score < ?;
        SELECT COUNT(*) FROM player WHERE score > ?;
        """, arguments: [1000])
    

    Declaration

    Swift

    public func allStatements(sql: String, arguments: StatementArguments? = nil)
    throws -> SQLStatementCursor

    Parameters

    sql

    An SQL query.

    arguments

    Statement arguments.

    Return Value

    A cursor of Statement

  • Returns a cursor of all SQL statements separated by semi-colons.

    Literals allow you to safely embed raw values in your SQL, without any risk of syntax errors or SQL injection:

    let statements = try db.allStatements(literal: """
        INSERT INTO player (name) VALUES (\("Arthur"));
        INSERT INTO player (name) VALUES (\("Barbara"));
        INSERT INTO player (name) VALUES (\("O'Brien"));
        """)
    while let statement = try statements.next() {
        try statement.execute()
    }
    

    Throws

    A DatabaseError whenever an SQLite error occurs.

    Precondition

    No argument must be set, or all arguments must be set. The returned cursor will throw an error otherwise.

    // OK
    try allStatements(literal: """
        SELECT COUNT(*) FROM player WHERE score < ?;
        SELECT COUNT(*) FROM player WHERE score > ?;
        """)
    try allStatements(literal: """
        SELECT COUNT(*) FROM player WHERE score < \(1000);
        SELECT COUNT(*) FROM player WHERE score > \(1000);
        """)
    
    // NOT OK
    try allStatements(literal: """
        SELECT COUNT(*) FROM player WHERE score < \(1000);
        SELECT COUNT(*) FROM player WHERE score > ?;
        """)
    

    Declaration

    Swift

    public func allStatements(literal sqlLiteral: SQL) throws -> SQLStatementCursor

    Parameters

    sqlLiteral

    An SQL literal.

    Return Value

    A cursor of Statement

  • Executes one or several SQL statements, separated by semi-colons.

    try db.execute(
        sql: "INSERT INTO player (name) VALUES (:name)",
        arguments: ["name": "Arthur"])
    
    try db.execute(sql: """
        INSERT INTO player (name) VALUES (?);
        INSERT INTO player (name) VALUES (?);
        INSERT INTO player (name) VALUES (?);
        """, arguments: ["Arthur", "Barbara", "O'Brien"])
    

    This method may throw a DatabaseError.

    Throws

    A DatabaseError whenever an SQLite error occurs.

    Declaration

    Swift

    public func execute(sql: String, arguments: StatementArguments = StatementArguments()) throws

    Parameters

    sql

    An SQL query.

    arguments

    Statement arguments.

  • Executes one or several SQL statements, separated by semi-colons.

    Literals allow you to safely embed raw values in your SQL, without any risk of syntax errors or SQL injection:

    try db.execute(literal: """
        INSERT INTO player (name) VALUES (\("Arthur"))
        """)
    
    try db.execute(literal: """
        INSERT INTO player (name) VALUES (\("Arthur"));
        INSERT INTO player (name) VALUES (\("Barbara"));
        INSERT INTO player (name) VALUES (\("O'Brien"));
        """)
    

    This method may throw a DatabaseError.

    Throws

    A DatabaseError whenever an SQLite error occurs.

    Declaration

    Swift

    public func execute(literal sqlLiteral: SQL) throws

    Parameters

    sqlLiteral

    An SQL literal.

Database-Related Types

Database Observation

  • Add a transaction observer, so that it gets notified of database changes.

    Declaration

    Swift

    public func add(
        transactionObserver: TransactionObserver,
        extent: TransactionObservationExtent = .observerLifetime)

    Parameters

    transactionObserver

    A transaction observer.

    extent

    The duration of the observation. The default is the observer lifetime (observation lasts until observer is deallocated).

  • Remove a transaction observer.

    Declaration

    Swift

    public func remove(transactionObserver: TransactionObserver)
  • Registers a closure to be executed after the next or current transaction completion.

    try dbQueue.write { db in
        db.afterNextTransactionCommit { _ in
            print("success")
        }
        ...
    } // prints "success"
    

    If the transaction is rollbacked, the closure is not executed.

    If the transaction is committed, the closure is executed in a protected dispatch queue, serialized will all database updates.

    Declaration

    Swift

    public func afterNextTransactionCommit(_ closure: @escaping (Database) -> Void)
  • The extent of a transaction observation

    See Database.add(transactionObserver:extent:)

    See more

    Declaration

    Swift

    public enum TransactionObservationExtent
  • Deletes the synchronization triggers for a synchronized FTS4 table

    Declaration

    Swift

    public func dropFTS4SynchronizationTriggers(forTable tableName: String) throws

Database Schema