Database

public final class Database

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)
}
  • The error logging function.

    Quoting https://www.sqlite.org/errlog.html:

    SQLite can be configured to invoke a callback function containing an error code and a terse error message whenever anomalies occur. This mechanism is very helpful in tracking obscure problems that occur rarely and in the field. Application developers are encouraged to take advantage of the error logging facility of SQLite in their products, as it is very low CPU and memory cost but can be a huge aid for debugging.

    Declaration

    Swift

    public static var logError: Database.LogErrorFunction? { get set }
  • The database configuration

    Declaration

    Swift

    public let configuration: Configuration
  • 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.

    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_.

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

    Declaration

    Swift

    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 func isGRDBInternalTable(_ tableName: String) -> Bool
  • Returns whether a view exists.

    Declaration

    Swift

    public func viewExists(_ name: String) throws -> Bool
  • Returns whether a trigger exists.

    Declaration

    Swift

    public func triggerExists(_ name: String) throws -> Bool
  • The primary key for table named tableName.

    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; returns the empty array if the table does not exist.

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

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

    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.

    Declaration

    Swift

    public func foreignKeys(on tableName: String) throws -> [ForeignKeyInfo]
  • The columns in the table named tableName

    Throws

    A DatabaseError if table does not exist.

    Declaration

    Swift

    public func columns(in tableName: String) throws -> [ColumnInfo]
  • Returns a new prepared statement that can be reused.

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

    Throws

    A DatabaseError whenever SQLite could not parse the sql query.

    Declaration

    Swift

    public func makeSelectStatement(sql: String) throws -> SelectStatement

    Parameters

    sql

    An SQL query.

    Return Value

    A SelectStatement.

  • 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

    public func cachedSelectStatement(sql: String) throws -> SelectStatement

    Parameters

    sql

    An SQL query.

    Return Value

    An UpdateStatement.

  • 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

    public func makeUpdateStatement(sql: String) throws -> UpdateStatement

    Parameters

    sql

    An SQL query.

    Return Value

    An UpdateStatement.

  • 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

    public func cachedUpdateStatement(sql: String) throws -> UpdateStatement

    Parameters

    sql

    An SQL query.

    Return Value

    An UpdateStatement.

  • 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.

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

    With Swift 5, you can safely embed raw values in your SQL queries, without any risk of syntax errors or SQL injection:

    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: SQLLiteral) throws

    Parameters

    sqlLiteral

    An SQLLiteral.

  • 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)
  • 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)
  • 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
  • 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
  • Creates a virtual database table.

    try db.create(virtualTable: "vocabulary", using: "spellfix1")
    

    See https://www.sqlite.org/lang_createtable.html

    Throws

    A DatabaseError whenever an SQLite error occurs.

    Declaration

    Swift

    public func create(virtualTable name: String, ifNotExists: Bool = false, using module: String) throws

    Parameters

    name

    The table name.

    ifNotExists

    If false (the default), an error is thrown if the table already exists. Otherwise, the table is created unless it already exists.

    module

    The name of an SQLite virtual table module.

  • Creates a virtual database table.

    let module = ...
    try db.create(virtualTable: "book", using: module) { t in
        ...
    }
    

    The type of the closure argument t depends on the type of the module argument: refer to this module’s documentation.

    Use this method to create full-text tables using the FTS3, FTS4, or FTS5 modules:

    try db.create(virtualTable: "book", using: FTS4()) { t in
        t.column("title")
        t.column("author")
        t.column("body")
    }
    

    See https://www.sqlite.org/lang_createtable.html

    Throws

    A DatabaseError whenever an SQLite error occurs.

    Declaration

    Swift

    public func create<Module: VirtualTableModule>(
        virtualTable tableName: String,
        ifNotExists: Bool = false,
        using module: Module,
        _ body: ((Module.TableDefinition) -> Void)? = nil)
        throws

    Parameters

    name

    The table name.

    ifNotExists

    If false (the default), an error is thrown if the table already exists. Otherwise, the table is created unless it already exists.

    module

    a VirtualTableModule

    body

    An optional closure that defines the virtual table.