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

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