Database

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

It is nil after the database has been successfully closed with close().

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.

For example:

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.

Executes a block inside a savepoint.

For example:

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.

Rollbacks a database transaction.

Commits a database transaction.

Free as much memory as possible.

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, or in an attached database.

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 view exists, in the main or temp schema, or in an attached database.

Returns whether a trigger exists, in the main or temp schema, or in an attached database.

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 identifies a row, because the columns contain the primary key or a unique index, use table(_:hasUniqueKey:).

True if a sequence of columns uniquely identifies a row, that is to say if the columns contain the primary key, or a unique index.

For example:

// CREATE TABLE t(id INTEGER PRIMARY KEY, a, b, c);
// CREATE UNIQUE INDEX i ON t(a, b);
try db.table("t", hasUniqueKey: ["id"])                // true
try db.table("t", hasUniqueKey: ["a", "b"])            // true
try db.table("t", hasUniqueKey: ["c"])                 // false
try db.table("t", hasUniqueKey: ["id", "a"])           // true
try db.table("t", hasUniqueKey: ["id", "a", "b", "c"]) // true

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.

For example:

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.

For example:

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 prepared statement that can be reused.

For example:

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.

For example:

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 cursor of all SQL statements separated by semi-colons.

For example:

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.

For example:

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

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

Sets the passphrase used to crypt and decrypt an SQLCipher database.

Call this method from Configuration.prepareDatabase, as in the example below:

var config = Configuration()
config.prepareDatabase { db in
    try db.usePassphrase("secret")
}

Changes the passphrase used by an SQLCipher encrypted database.

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 = try 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.

This method has no effect on read-only database connections.

Remove a transaction observer.

Registers closures to be executed after the next or current transaction completion.

try dbQueue.write { db in
    db.afterNextTransaction { _ in
        print("success")
    }
    ...
} // prints "commit"

Closure are executed in a protected dispatch queue, serialized will all database updates.

The extent of a transaction observation.

See add(transactionObserver:extent:).

Deletes the synchronization triggers for a synchronized FTS4 table

Deletes the synchronization triggers for a synchronized FTS5 table

Add a custom FTS5 tokenizer.

class MyTokenizer : FTS5CustomTokenizer { ... }
db.add(tokenizer: MyTokenizer.self)

Creates a pattern from a raw pattern string; throws DatabaseError on invalid syntax.

The pattern syntax is documented at https://www.sqlite.org/fts5.html#full_text_query_syntax

try db.makeFTS5Pattern(rawPattern: "and", forTable: "document") // OK
try db.makeFTS5Pattern(rawPattern: "AND", forTable: "document") // malformed MATCH expression: [AND]

Creates an FTS5 tokenizer, given its descriptor.

let unicode61 = try db.makeTokenizer(.unicode61())

It is a programmer error to use the tokenizer outside of a protected database queue, or after the database has been closed.

Use this method when you implement a custom wrapper tokenizer:

final class MyTokenizer : FTS5WrapperTokenizer {
    var wrappedTokenizer: FTS5Tokenizer

    init(db: Database, arguments: [String]) throws {
        wrappedTokenizer = try db.makeTokenizer(.unicode61())
    }
}

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

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