Database

The raw SQLite connection, suitable for the SQLite C API. It is constant, until close() sets it to nil.

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.

The database configuration

See Configuration.label

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

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

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

True if the database connection is currently in a transaction.

The last error code

The last error message

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.

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

Remove an SQL function.

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

Remove a collation.

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.

Runs a WAL checkpoint.

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

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

Experimental

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

Experimental

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.

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.

Begins a database transaction.

If nil, and the database connection is read-only, the transaction kind is .deferred.

If nil, and the database connection is not read-only, the transaction kind is configuration.defaultTransactionKind.

See https://www.sqlite.org/lang_transaction.html for more information.

Rollbacks a database transaction.

Commits a database transaction.

Undocumented

Copies the database contents into another database.

The backup method blocks the current thread until the destination database contains the same contents as the source database.

Usage:

let source: DatabaseQueue = ...
let destination: DatabaseQueue = ...
try source.write { sourceDb in
    try destination.barrierWriteWithoutTransaction { destDb in
        try sourceDb.backup(to: destDb)
    }
}

When you’re after progress reporting during backup, you’ll want to perform the backup in several steps. Each step copies the number of database pages you specify. See https://www.sqlite.org/c3ref/backup_finish.html for more information:

// Backup with progress reporting
try sourceDb.backup(
    to: destDb,
    pagesPerStep: ...)
    { backupProgress in
       print("Database backup progress:", backupProgress)
    }

The progress callback will be called at least once—when backupProgress.isCompleted == true. If the callback throws when backupProgress.isCompleted == false, the backup is aborted and the error is rethrown. If the callback throws when backupProgress.isCompleted == true, backup completion is unaffected and the error is silently ignored.

See also DatabaseReader.backup().

Clears the database schema cache.

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

Returns whether a table exists in the main or temp schema.

Returns whether a table is an internal SQLite table.

Those are tables whose name begins with sqlite_ and pragma_.

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

Returns whether a table is an internal SQLite table.

Those are tables whose name begins with sqlite_ and pragma_.

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

Returns whether a table is an internal GRDB table.

Those are tables whose name begins with “grdb_”.

Returns whether a table is an internal GRDB table.

Those are tables whose name begins with “grdb_”.

Returns whether a view exists in the main or temp schema.

Returns whether a trigger exists in the main or temp schema.

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.

The indexes on table named tableName.

Only indexes on columns are returned. Indexes on expressions are not returned.

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

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

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.

The foreign keys defined on table named tableName.

Returns a cursor over foreign key violations in the database.

Returns a cursor over foreign key violations in the table.

Throws a DatabaseError of extended code SQLITE_CONSTRAINT_FOREIGNKEY if there exists a foreign key violation in the database.

Throws a DatabaseError of extended code SQLITE_CONSTRAINT_FOREIGNKEY if there exists a foreign key violation in the table.

The columns in the table, or view, named tableName.

Returns a new prepared statement that can be reused.

let statement = try db.makeStatement(sql: "SELECT * FROM player WHERE id = ?")
let player1 = try Player.fetchOne(statement, arguments: [1])!
let player2 = try Player.fetchOne(statement, arguments: [2])!

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

Returns a new prepared statement that can be reused.

let statement = try db.makeStatement(literal: "SELECT * FROM player WHERE id = ?")
let player1 = try Player.fetchOne(statement, arguments: [1])!
let player2 = try Player.fetchOne(statement, arguments: [2])!

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

// OK
try makeStatement(literal: """
    SELECT COUNT(*) FROM player WHERE score > ?
    """)
try makeStatement(literal: """
    SELECT COUNT(*) FROM player WHERE score > \(1000)
    """)

// NOT OK
try makeStatement(literal: """
    SELECT COUNT(*) FROM player
    WHERE color = ? AND score > \(1000)
    """)

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

Returns a new prepared statement that can be reused.

// OK
try makeSelectStatement(literal: """
    SELECT COUNT(*) FROM player WHERE score > ?
    """)
try makeSelectStatement(literal: """
    SELECT COUNT(*) FROM player WHERE score > \(1000)
    """)

// NOT OK
try makeSelectStatement(literal: """
    SELECT COUNT(*) FROM player
    WHERE color = ? AND score > \(1000)
    """)

Returns a prepared statement that can be reused.

let statement = try db.cachedStatement(sql: "SELECT * FROM player WHERE id = ?")
let player1 = try Player.fetchOne(statement, arguments: [1])!
let player2 = try Player.fetchOne(statement, arguments: [2])!

let statement = try db.cachedStatement(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.

Returns a prepared statement that can be reused.

let statement = try db.cachedStatement(literal: "SELECT * FROM player WHERE id = ?")
let player1 = try Player.fetchOne(statement, arguments: [1])!
let player2 = try Player.fetchOne(statement, arguments: [2])!

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

// OK
try cachedStatement(literal: """
    SELECT COUNT(*) FROM player WHERE score > ?
    """)
try cachedStatement(literal: """
    SELECT COUNT(*) FROM player WHERE score > \(1000)
    """)

// NOT OK
try cachedStatement(literal: """
    SELECT COUNT(*) FROM player
    WHERE color = ? AND score > \(1000)
    """)

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.

Returns a prepared statement that can be reused.

let statement = try db.cachedSelectStatement(literal: "SELECT COUNT(*) FROM player WHERE score > \(20)")
let moreThanTwentyCount = try Int.fetchOne(statement)!

// OK
try cachedSelectStatement(literal: """
    SELECT COUNT(*) FROM player WHERE score > ?
    """)
try cachedSelectStatement(literal: """
    SELECT COUNT(*) FROM player WHERE score > \(1000)
    """)

// NOT OK
try cachedSelectStatement(literal: """
    SELECT COUNT(*) FROM player
    WHERE color = ? AND score > \(1000)
    """)

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

Returns a new prepared statement that can be reused.

// OK
try makeUpdateStatement(literal: """
    UPDATE player SET name = ?
    """)
try makeUpdateStatement(literal: """
    UPDATE player SET name = \("O'Brien")
    """)

// NOT OK
try makeUpdateStatement(literal: """
    UPDATE player SET name = ?, score = \(10)
    """)

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.

Returns a new prepared statement that can be reused.

// OK
try cachedUpdateStatement(literal: """
    UPDATE player SET name = ?
    """)
try cachedUpdateStatement(literal: """
    UPDATE player SET name = \("O'Brien")
    """)

// NOT OK
try cachedUpdateStatement(literal: """
    UPDATE player SET name = ?, score = \(10)
    """)

Returns a cursor of all SQL statements separated by semi-colons.

let statements = try db.allStatements(sql: """
    INSERT INTO player (name) VALUES (?);
    INSERT INTO player (name) VALUES (?);
    INSERT INTO player (name) VALUES (?);
    """, arguments: ["Arthur", "Barbara", "O'Brien"])
while let statement = try statements.next() {
    try statement.execute()
}

// OK
try allStatements(sql: """
    SELECT COUNT(*) FROM player WHERE score < ?;
    SELECT COUNT(*) FROM player WHERE score > ?;
    """)
try allStatements(sql: """
    SELECT COUNT(*) FROM player WHERE score < ?;
    SELECT COUNT(*) FROM player WHERE score > ?;
    """, arguments: [1000, 1000])

// NOT OK
try allStatements(sql: """
    SELECT COUNT(*) FROM player WHERE score < ?;
    SELECT COUNT(*) FROM player WHERE score > ?;
    """, arguments: [1000])

Returns a cursor of all SQL statements separated by semi-colons.

Literals allow you to safely embed raw values in your SQL, without any risk of syntax errors or SQL injection:

let statements = try db.allStatements(literal: """
    INSERT INTO player (name) VALUES (\("Arthur"));
    INSERT INTO player (name) VALUES (\("Barbara"));
    INSERT INTO player (name) VALUES (\("O'Brien"));
    """)
while let statement = try statements.next() {
    try statement.execute()
}

// OK
try allStatements(literal: """
    SELECT COUNT(*) FROM player WHERE score < ?;
    SELECT COUNT(*) FROM player WHERE score > ?;
    """)
try allStatements(literal: """
    SELECT COUNT(*) FROM player WHERE score < \(1000);
    SELECT COUNT(*) FROM player WHERE score > \(1000);
    """)

// NOT OK
try allStatements(literal: """
    SELECT COUNT(*) FROM player WHERE score < \(1000);
    SELECT COUNT(*) FROM player WHERE score > ?;
    """)

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.

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

Literals allow you to safely embed raw values in your SQL, without any risk of syntax errors or SQL injection:

try db.execute(literal: """
    INSERT INTO player (name) VALUES (\("Arthur"))
    """)

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.

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

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

The available checkpoint modes.

A built-in SQLite collation.

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

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

An SQLite conflict resolution.

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

A foreign key action.

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

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

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

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

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

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

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

Remove a transaction observer.

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.

The extent of a transaction observation

See Database.add(transactionObserver:extent:)

Deletes the synchronization triggers for a synchronized FTS4 table

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

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

Renames a database table.

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

Modifies a database table.

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

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

Deletes a database table.

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

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

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

Deletes a database index.

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

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

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

Creates a virtual database table.

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

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

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