Database

See BusyMode and https://www.sqlite.org/c3ref/busy_handler.html

public typealias BusyCallback = (_ numberOfTries: Int) -> Bool

When there are several connections to a database, a connection may try to access the database while it is locked by another connection.

The BusyMode enum describes the behavior of GRDB when such a situation occurs:

• .immediateError: The SQLITE_BUSY error is immediately returned to the connection that tries to access the locked database.

• .timeout: The SQLITE_BUSY error will be returned only if the database remains locked for more than the specified duration.

• .callback: Perform your custom lock handling.

To set the busy mode of a database, use Configuration:

let configuration = Configuration(busyMode: .timeout(1))
let dbQueue = DatabaseQueue(path: "...", configuration: configuration)

Relevant SQLite documentation:

• https://www.sqlite.org/c3ref/busy_timeout.html
• https://www.sqlite.org/c3ref/busy_handler.html
• https://www.sqlite.org/lang_transaction.html
• https://www.sqlite.org/wal.html

See more

public enum BusyMode

The available checkpoint modes.

See more

public enum CheckpointMode: Int32

A built-in SQLite collation.

See https://www.sqlite.org/datatype3.html#collation

See more

public struct CollationName : RawRepresentable, Hashable

An SQLite conflict resolution.

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

See more

public enum ConflictResolution : String

An SQL column type.

try db.create(table: "persons") { t in
    t.column("id", .integer).primaryKey()
    t.column("title", .text)
}

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

See more

public struct ColumnType : RawRepresentable, Hashable

A foreign key action.

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

See more

public enum ForeignKeyAction : String

An SQLite transaction kind. See https://www.sqlite.org/lang_transaction.html

See more

public enum TransactionKind

The end of a transaction: Commit, or Rollback

See more

public enum TransactionCompletion

log function that takes an error message.

public typealias LogErrorFunction = (_ resultCode: ResultCode, _ message: String) -> Void

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.

public static var logError: LogErrorFunction? = nil

The database configuration

public let configuration: Configuration

The raw SQLite connection, suitable for the SQLite C API.

public let sqliteConnection: SQLiteConnection

The rowID of the most recently inserted row.

If no row has ever been inserted using this database connection, returns zero.

For more detailed information, see https://www.sqlite.org/c3ref/last_insert_rowid.html

public var lastInsertedRowID: Int64

The number of rows modified, inserted or deleted by the most recent successful INSERT, UPDATE or DELETE statement.

For more detailed information, see https://www.sqlite.org/c3ref/changes.html

public var changesCount: Int

The total number of rows modified, inserted or deleted by all successful INSERT, UPDATE or DELETE statements since the database connection was opened.

For more detailed information, see https://www.sqlite.org/c3ref/total_changes.html

public var totalChangesCount: Int

True if the database connection is currently in a transaction.

public var isInsideTransaction: Bool

Returns a new prepared statement that can be reused.

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

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

Returns a prepared statement that can be reused.

let statement = try db.cachedSelectStatement("SELECT COUNT(*) FROM persons WHERE age > ?")
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.

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

Returns a new prepared statement that can be reused.

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

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

Returns a prepared statement that can be reused.

let statement = try db.cachedUpdateStatement("INSERT INTO persons (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.

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

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

try db.execute(
    "INSERT INTO persons (name) VALUES (:name)",
    arguments: ["name": "Arthur"])

try db.execute(
    "INSERT INTO persons (name) VALUES (?);" +
    "INSERT INTO persons (name) VALUES (?);" +
    "INSERT INTO persons (name) VALUES (?);",
    arguments; ['Arthur', 'Barbara', 'Craig'])

This method may throw a DatabaseError.

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

Add or redefine an SQL function.

let fn = DatabaseFunction("succ", argumentCount: 1) { databaseValues in
    let dbv = databaseValues.first!
    guard let int = dbv.value() as Int? else {
        return nil
    }
    return int + 1
}
db.add(function: fn)
try Int.fetchOne(db, "SELECT succ(1)")! // 2

public func add(function: DatabaseFunction)

Remove an SQL function.

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")

public func add(collation: DatabaseCollation)

Remove a collation.

public func remove(collation: DatabaseCollation)

Clears the database schema cache.

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

public func clearSchemaCache()

Returns whether a table exists.

public func tableExists(_ tableName: String) throws -> Bool

The primary key for table named tableName; nil if table has no primary key.

public func primaryKey(_ tableName: String) throws -> PrimaryKeyInfo?

The number of columns in the table named tableName.

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.

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.

public func table<T: Sequence>(_ tableName: String, hasUniqueKey columns: T) throws -> Bool where T.Iterator.Element == String

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.

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

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.

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

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

The transaction observer is weakly referenced: it is not retained, and stops getting notifications after it is deallocated.

public func add(transactionObserver: TransactionObserver)

Remove a transaction observer.

public func remove(transactionObserver: TransactionObserver)

Undocumented

public final class Database

Creates a database table.

try db.create(table: "pointOfInterests") { t in
    t.column("id", .integer).primaryKey()
    t.column("title", .text)
    t.column("favorite", .boolean).notNull().default(false)
    t.column("longitude", .double).notNull()
    t.column("latitude", .double).notNull()
}

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

public func create(table name: String, temporary: Bool = false, ifNotExists: Bool = false, withoutRowID: Bool, body: (TableDefinition) -> Void) throws

Creates a database table.

try db.create(table: "pointOfInterests") { t in
    t.column("id", .integer).primaryKey()
    t.column("title", .text)
    t.column("favorite", .boolean).notNull().default(false)
    t.column("longitude", .double).notNull()
    t.column("latitude", .double).notNull()
}

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

public func create(table name: String, temporary: Bool = false, ifNotExists: Bool = false, body: (TableDefinition) -> Void) throws

Renames a database table.

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

public func rename(table name: String, to newName: String) throws

Modifies a database table.

try db.alter(table: "persons") { t in
    t.add(column: "url", .text)
}

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

public func alter(table name: String, body: (TableAlteration) -> Void) throws

Deletes a database table.

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

public func drop(table name: String) throws

Undocumented

public final class Database

Creates an index.

try db.create(index: "personByEmail", on: "person", columns: ["email"])

SQLite can also index expressions (https://www.sqlite.org/expridx.html) and use specific collations. To create such an index, use a raw SQL query.

try db.execute("CREATE INDEX ...")

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

public func create(index name: String, on table: String, columns: [String], unique: Bool = false, ifNotExists: Bool = false) throws

Creates a partial index.

try db.create(index: "personByEmail", on: "person", columns: ["email"], condition: Column("email") != nil)

See https://www.sqlite.org/lang_createindex.html, and https://www.sqlite.org/partialindex.html

public func create(index name: String, on table: String, columns: [String], unique: Bool = false, ifNotExists: Bool = false, condition: SQLExpressible) throws

Deletes a database index.

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

public func drop(index name: String) throws

Creates a virtual database table.

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

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

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

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

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