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.inDatabase { db in
    try db.execute(...)
}
  • 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: LogErrorFunction? = nil
  • 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(_ tableName: String) throws -> Bool
  • Returns whether a view exists.

    Declaration

    Swift

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

    Declaration

    Swift

    public func triggerExists(_ triggerName: 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 number of columns in the table named tableName.

    Throws

    A DatabaseError if table does not exist.

    Declaration

    Swift

    public func columnCount(in tableName: String) throws -> Int
  • 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]
  • Returns a new prepared statement that can be reused.

    let statement = try db.makeSelectStatement("SELECT COUNT(*) FROM players 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("SELECT COUNT(*) FROM players 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("INSERT INTO players (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("INSERT INTO players (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(
        "INSERT INTO players (name) VALUES (:name)",
        arguments: ["name": "Arthur"])
    
    try db.execute("""
        INSERT INTO players (name) VALUES (?);
        INSERT INTO players (name) VALUES (?);
        INSERT INTO players (name) VALUES (?);
        """, arguments; ['Arthur', 'Barbara', 'Craig'])
    

    This method may throw a DatabaseError.

    Throws

    A DatabaseError whenever an SQLite error occurs.

    Declaration

    Swift

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

    Parameters

    sql

    An SQL query.

    arguments

    Optional statement arguments.

  • 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, "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("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("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 .immediate. 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("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.

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

    dbQueue.inTransaction { db in
        db.afterNextTransactionCommit { _ in
            print("commit did succeed")
        }
        ...
        return .commit // prints "commit did succeed"
    }
    

    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) -> ())
  • 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, no error is thrown if table already exists.

    module

    The name of an SQLite virtual table module.

  • Creates a virtual database table.

    let module = ...
    try db.create(virtualTable: "pointOfInterests", 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: "books", 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, no error is thrown if table already exists.

    module

    a VirtualTableModule

    body

    An optional closure that defines the virtual table.