Database

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

public let sqliteConnection: SQLiteConnection

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? { get set }

The database configuration

public let configuration: Configuration

See Configuration.label

public let description: String

public var debugDescription: String { get }

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 { get }

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 { get }

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 { get }

True if the database connection is currently in a transaction.

public var isInsideTransaction: Bool { get }

The last error code

public var lastErrorCode: ResultCode { get }

The last error message

public var lastErrorMessage: String? { get }

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.

public var maximumStatementArgumentCount: Int { get }

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

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(sql: "CREATE TABLE files (name TEXT COLLATE localized_standard")

public func add(collation: DatabaseCollation)

Remove a collation.

public func remove(collation: DatabaseCollation)

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.

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

Runs a WAL checkpoint.

See https://www.sqlite.org/wal.html and https://www.sqlite.org/c3ref/wal_checkpoint_v2.html for more information.

@discardableResult
public func checkpoint(_ kind: Database.CheckpointMode = .passive, on dbName: String? = "main") throws
    -> (walFrameCount: Int, checkpointedFrameCount: Int)

When this notification is posted, databases which were opened with the Configuration.observesSuspensionNotifications flag are suspended.

Experimental

public static let suspendNotification: Notification.Name

When this notification is posted, databases which were opened with the Configuration.observesSuspensionNotifications flag are resumed.

Experimental

public static let resumeNotification: Notification.Name

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.

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

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

Begins a database transaction.

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

Rollbacks a database transaction.

public func rollback() throws

Commits a database transaction.

public func commit() throws

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

public func isSQLiteInternalTable(_ tableName: String) -> Bool

Returns whether a table is an internal GRDB table.

Those are tables whose name begins with “grdb_”.

public func isGRDBInternalTable(_ tableName: String) -> Bool

Returns whether a view exists.

public func viewExists(_ name: String) throws -> Bool

Returns whether a trigger exists.

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.

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.

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

The foreign keys defined on table named tableName.

public func foreignKeys(on tableName: String) throws -> [ForeignKeyInfo]

The columns in the table named tableName

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

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])!

public func makeSelectStatement(sql: String) throws -> 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.

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

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

public func makeUpdateStatement(sql: String) throws -> 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.

public func cachedUpdateStatement(sql: String) throws -> 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.

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

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.

public func execute(literal sqlLiteral: SQLLiteral) throws

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:

// Wait 1 second before failing with SQLITE_BUSY
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 SQL column type.

try db.create(table: "player") { t in
    t.autoIncrementedPrimaryKey("id")
    t.column("title", .text)
}

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

See more

public struct ColumnType : RawRepresentable, Hashable

An SQLite conflict resolution.

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

See more

public enum ConflictResolution : String

A foreign key action.

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

See more

public enum ForeignKeyAction : String

An error log function that takes an error code and message.

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

An option for Database.trace(options:_:)

See more

public struct TracingOptions : OptionSet

An event reported by Database.trace(options:_:)

See more

public enum TraceEvent : CustomStringConvertible

Confirms or cancels the changes performed by a transaction or savepoint.

See more

public enum TransactionCompletion

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

See more

public enum TransactionKind : String

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

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

Remove a transaction observer.

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.

public func afterNextTransactionCommit(_ closure: @escaping (Database) -> Void)

The extent of a transaction observation

See Database.add(transactionObserver:extent:)

See more

public enum TransactionObservationExtent

Deletes the synchronization triggers for a synchronized FTS4 table

public func dropFTS4SynchronizationTriggers(forTable tableName: String) throws

Creates a database table.

try db.create(table: "place") { t in
    t.autoIncrementedPrimaryKey("id")
    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 = 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: "player") { 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

Creates an index.

try db.create(index: "playerByEmail", on: "player", 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(sql: "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,
    condition: SQLExpressible? = nil)
    throws

Deletes a database index.

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

public func drop(index name: String) throws

Delete and recreate from scratch all indices that use this collation.

This method is useful when the definition of a collation sequence has changed.

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

public func reindex(collation: Database.CollationName) throws

Delete and recreate from scratch all indices that use this collation.

This method is useful when the definition of a collation sequence has changed.

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

public func reindex(collation: DatabaseCollation) 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: "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

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