DatabasePool

The path to the database.

Opens the SQLite database at path path.

let dbPool = try DatabasePool(path: "/path/to/database.sqlite")

Database connections get closed when the database pool gets deallocated.

Runs a WAL checkpoint

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

Free as much memory as possible.

This method blocks the current thread until all database accesses are completed.

See also setupMemoryManagement(application:)

Listens to UIApplicationDidEnterBackgroundNotification and UIApplicationDidReceiveMemoryWarningNotification in order to release as much memory as possible.

• param application: The UIApplication that will start a background task to let the database pool release its memory when the application enters background.

Synchronously executes a read-only block in a protected dispatch queue, and returns its result. The block is wrapped in a deferred transaction.

let players = try dbPool.read { db in
    try Player.fetchAll(...)
}

The block is completely isolated. Eventual concurrent database updates are not visible inside the block:

try dbPool.read { db in
    // Those two values are guaranteed to be equal, even if the
    // `wine` table is modified between the two requests:
    let count1 = try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM wine")!
    let count2 = try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM wine")!
}

try dbPool.read { db in
    // Now this value may be different:
    let count = try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM wine")!
}

This method is not reentrant.

Asynchronously executes a read-only block in a protected dispatch queue.

let players = try dbQueue.asyncRead { result in
    do {
        let db = try result.get()
        let count = try Player.fetchCount(db)
    } catch {
        // Handle error
    }
}

Starting SQLite 3.8.0 (iOS 8.2+, OSX 10.10+, custom SQLite builds and SQLCipher), attempts to write in the database from this method throw a DatabaseError of resultCode SQLITE_READONLY.

Synchronously executes a read-only block in a protected dispatch queue, and returns its result.

The block argument is not isolated: eventual concurrent database updates are visible inside the block:

try dbPool.unsafeRead { db in
    // Those two values may be different because some other thread
    // may have inserted or deleted a wine between the two requests:
    let count1 = try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM wine")!
    let count2 = try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM wine")!
}

Cursor iteration is safe, though:

try dbPool.unsafeRead { db in
    // No concurrent update can mess with this iteration:
    let rows = try Row.fetchCursor(db, sql: "SELECT ...")
    while let row = try rows.next() { ... }
}

This method is not reentrant.

Synchronously executes a read-only block in a protected dispatch queue, and returns its result.

The block argument is not isolated: eventual concurrent database updates are visible inside the block:

try dbPool.unsafeReentrantRead { db in
    // Those two values may be different because some other thread
    // may have inserted or deleted a wine between the two requests:
    let count1 = try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM wine")!
    let count2 = try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM wine")!
}

Cursor iteration is safe, though:

try dbPool.unsafeReentrantRead { db in
    // No concurrent update can mess with this iteration:
    let rows = try Row.fetchCursor(db, sql: "SELECT ...")
    while let row = try rows.next() { ... }
}

This method is reentrant. It is unsafe because it fosters dangerous concurrency practices.

Asynchronously executes a read-only block in a protected dispatch queue.

This method must be called from a writing dispatch queue, outside of any transaction. You’ll get a fatal error otherwise.

The block argument is guaranteed to see the database in the last committed state at the moment this method is called. Eventual concurrent database updates are not visible inside the block.

This method returns as soon as the isolation guarantees described above are established.

In the example below, the number of players is fetched concurrently with the player insertion. Yet the future is guaranteed to return zero:

try writer.asyncWriteWithoutTransaction { db in
    // Delete all players
    try Player.deleteAll()

    // Count players concurrently
    writer.asyncConcurrentRead { result in
        do {
            let db = try result.get()
            // Guaranteed to be zero
            let count = try Player.fetchCount(db)
        } catch {
            // Handle error
        }
    }

    // Insert a player
    try Player(...).insert(db)
}

Invalidates open read-only SQLite connections.

After this method is called, read-only database access methods will use new SQLite connections.

Eventual concurrent read-only accesses are not invalidated: they will proceed until completion.

Synchronously executes database updates in a protected dispatch queue, outside of any transaction, and returns the result.

Eventual concurrent database updates are postponed until the updates are completed.

Eventual concurrent reads may see partial updates unless you wrap them in a transaction.

This method is not reentrant.

A barrier write ensures that no database access is executed until all previous accesses have completed, and the specified updates have been executed.

This method is not reentrant.

Synchronously executes database updates in a protected dispatch queue, wrapped inside a transaction, and returns the result.

If the updates throws an error, the transaction is rollbacked and the error is rethrown. If the updates return .rollback, the transaction is also rollbacked, but no error is thrown.

Eventual concurrent database updates are postponed until the transaction has completed.

Eventual concurrent reads are guaranteed to not see any partial updates of the database until the transaction has completed.

This method is not reentrant.

try dbPool.writeInTransaction { db in
    db.execute(...)
    return .commit
}

Synchronously executes database updates in a protected dispatch queue, outside of any transaction, and returns the result.

Eventual concurrent database updates are postponed until the updates are completed.

Eventual concurrent reads may see partial updates unless you wrap them in a transaction.

This method is reentrant. It should be avoided because it fosters dangerous concurrency practices.

Asynchronously executes database updates in a protected dispatch queue, outside of any transaction.

Eventual concurrent reads may see partial updates unless you wrap them in a transaction.

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
}
dbPool.add(function: fn)
try dbPool.read { db in
    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)
}
dbPool.add(collation: collation)
try dbPool.write { db in
    try db.execute(sql: "CREATE TABLE file (name TEXT COLLATE LOCALIZED_STANDARD")
}

Remove a collation.

Add a custom FTS5 tokenizer.

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

Creates a database snapshot.

The snapshot sees an unchanging database content, as it existed at the moment it was created.

When you want to control the latest committed changes seen by a snapshot, create it from the pool’s writer protected dispatch queue:

let snapshot1 = try dbPool.write { db -> DatabaseSnapshot in
    try Player.deleteAll()
    return try dbPool.makeSnapshot()
}
// <- Other threads may modify the database here
let snapshot2 = try dbPool.makeSnapshot()

try snapshot1.read { db in
    // Guaranteed to be zero
    try Player.fetchCount(db)
}

try snapshot2.read { db in
    // Could be anything
    try Player.fetchCount(db)
}

It is forbidden to create a snapshot from the writer protected dispatch queue when a transaction is opened, though, because it is likely a programmer error:

try dbPool.write { db in
    try db.inTransaction {
        try Player.deleteAll()
        // fatal error: makeSnapshot() must not be called from inside a transaction
        let snapshot = try dbPool.makeSnapshot()
        return .commit
    }
}

To avoid this fatal error, create the snapshot before or after the transaction:

try dbPool.writeWithoutTransaction { db in
    // OK
    let snapshot = try dbPool.makeSnapshot()

    try db.inTransaction {
        try Player.deleteAll()
        return .commit
    }

    // OK
    let snapshot = try dbPool.makeSnapshot()
}

You can create as many snapshots as you need, regardless of the maximum number of reader connections in the pool.

For more information, read about snapshot isolation at https://sqlite.org/isolation.html