DatabasePool

public final class DatabasePool : DatabaseWriter
extension DatabasePool: DatabaseReader

A DatabasePool grants concurrent accesses to an SQLite database.

Database Information

Initializer

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

    Throws

    A DatabaseError whenever an SQLite error occurs.

    Declaration

    Swift

    public init(path: String, configuration: Configuration = Configuration()) throws

    Parameters

    path

    The path to the database file.

    configuration

    A configuration.

Memory management

  • Free as much memory as possible.

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

    Declaration

    Swift

    public func releaseMemory()

Interrupting Database Operations

Reading from Database

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

    Throws

    The error thrown by the block, or any DatabaseError that would happen while establishing the read access to the database.

    Declaration

    Swift

    public func read<T>(_ block: (Database) throws -> T) throws -> T

    Parameters

    block

    A block that accesses the database.

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

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

    Attempts to write in the database from this method throw a DatabaseError of resultCode SQLITE_READONLY.

    Declaration

    Swift

    public func asyncRead(_ block: @escaping (Result<Database, Error>) -> Void)

    Parameters

    block

    A block that accesses the database.

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

    Throws

    The error thrown by the block, or any DatabaseError that would happen while establishing the read access to the database.

    Declaration

    Swift

    public func unsafeRead<T>(_ block: (Database) throws -> T) throws -> T

    Parameters

    block

    A block that accesses the database.

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

    Throws

    The error thrown by the block, or any DatabaseError that would happen while establishing the read access to the database.

    Declaration

    Swift

    public func unsafeReentrantRead<T>(_ block: (Database) throws -> T) throws -> T

    Parameters

    block

    A block that accesses the database.

  • Declaration

    Swift

    public func concurrentRead<T>(_ block: @escaping (Database) throws -> T) -> DatabaseFuture<T>
  • 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 { dbResult in
            do {
                let db = try dbResult.get()
                // Guaranteed to be zero
                let count = try Player.fetchCount(db)
            } catch {
                // Handle error
            }
        }
    
        // Insert a player
        try Player(...).insert(db)
    }
    

    Declaration

    Swift

    public func asyncConcurrentRead(_ block: @escaping (Result<Database, Error>) -> Void)

    Parameters

    block

    A block that accesses the database.

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

    Declaration

    Swift

    public func invalidateReadOnlyConnections()

Writing in Database

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

    Throws

    The error thrown by the updates.

    Declaration

    Swift

    public func writeWithoutTransaction<T>(_ updates: (Database) throws -> T) rethrows -> T

    Parameters

    updates

    The updates to the database.

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

    Updates are guaranteed an exclusive access to the database. They wait until all pending writes and reads are completed. They postpone all other writes and reads until they are completed.

    This method is not reentrant.

    Important

    Reads executed by concurrent database snapshots are not considered: they can run concurrently with the barrier updates.

    Throws

    The error thrown by the updates.

    Declaration

    Swift

    public func barrierWriteWithoutTransaction<T>(_ updates: (Database) throws -> T) rethrows -> T

    Parameters

    updates

    The updates to the database.

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

    Updates are guaranteed an exclusive access to the database. They wait until all pending writes and reads are completed. They postpone all other writes and reads until they are completed.

    Important

    Reads executed by concurrent database snapshots are not considered: they can run concurrently with the barrier updates.

    Declaration

    Swift

    public func asyncBarrierWriteWithoutTransaction(_ updates: @escaping (Database) -> Void)

    Parameters

    updates

    The updates to the database.

  • 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
    }
    

    Throws

    The error thrown by the updates, or by the wrapping transaction.

    Declaration

    Swift

    public func writeInTransaction(
        _ kind: Database.TransactionKind? = nil,
        _ updates: (Database) throws -> Database.TransactionCompletion)
    throws

    Parameters

    kind

    The transaction type (default nil). If nil, the transaction type is configuration.defaultTransactionKind, which itself defaults to .deferred. See https://www.sqlite.org/lang_transaction.html for more information.

    updates

    The updates to the database.

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

    Declaration

    Swift

    public func unsafeReentrantWrite<T>(_ updates: (Database) throws -> T) rethrows -> T
  • 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.

    Declaration

    Swift

    public func asyncWriteWithoutTransaction(_ updates: @escaping (Database) -> Void)

Snapshots

  • 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

    Declaration

    Swift

    public func makeSnapshot() throws -> DatabaseSnapshot