DatabasePool

public final class DatabasePool: DatabaseWriter

A DatabasePool grants concurrent accesses to an SQLite 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.

    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.

  • Free as much memory as possible.

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

    See also setupMemoryManagement(application:)

    Declaration

    Swift

    public func releaseMemory()
  • 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.

    Declaration

    Swift

    public func setupMemoryManagement(in application: UIApplication)
  • 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, "SELECT COUNT(*) FROM wine")!
        let count2 = try Int.fetchOne(db, "SELECT COUNT(*) FROM wine")!
    }
    
    try dbPool.read { db in
        // Now this value may be different:
        let count = try Int.fetchOne(db, "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.

  • 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, "SELECT COUNT(*) FROM wine")!
        let count2 = try Int.fetchOne(db, "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, "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, "SELECT COUNT(*) FROM wine")!
        let count2 = try Int.fetchOne(db, "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, "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.

  • Asynchronously executes a read-only block in a protected dispatch queue, wrapped in a deferred transaction.

    This method must be called from the writing dispatch queue, outside of a 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.

    try dbPool.write { db in
        try db.execute("DELETE FROM player")
        try dbPool.readFromCurrentState { db in
            // Guaranteed to be zero
            try Int.fetchOne(db, "SELECT COUNT(*) FROM player")!
        }
        try db.execute("INSERT INTO player ...")
    }
    

    This method blocks the current thread until the isolation guarantee has been established, and before the block argument has run.

    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 readFromCurrentState(_ block: @escaping (Database) -> Void) throws

    Parameters

    block

    A block that accesses the database.

  • Synchronously executes an update block in a protected dispatch queue, wrapped inside a transaction, and returns the result of the block.

    Eventual concurrent database updates are postponed until the block has executed.

    try dbPool.write { db in
        try db.execute(...)
    }
    

    Eventual concurrent reads are guaranteed not to see any changes performed in the block until they are all saved in the database.

    This method is not reentrant.

    Throws

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

    Declaration

    Swift

    public func write<T>(_ block: (Database) throws -> T) throws -> T
  • Synchronously executes a block that takes a database connection, and returns its result.

    Eventual concurrent database updates are postponed until the block has executed.

    Eventual concurrent reads may see changes performed in the block before the block completes.

    The block is guaranteed to be executed outside of a transaction.

    This method is not reentrant.

    Throws

    The error thrown by the block.

    Declaration

    Swift

    public func writeWithoutTransaction<T>(_ block: (Database) throws -> T) rethrows -> T
  • Synchronously executes a block in a protected dispatch queue, wrapped inside a transaction.

    Eventual concurrent database updates are postponed until the block has executed.

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

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

    Eventual concurrent reads are guaranteed not to see any changes performed in the block until they are all saved in the database.

    This method is not reentrant.

    Throws

    The error thrown by the block, or any error establishing the transaction.

    Declaration

    Swift

    public func writeInTransaction(_ kind: Database.TransactionKind? = nil, _ block: (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.

    block

    A block that executes SQL statements and return either .commit or .rollback.

  • Synchronously executes an update block in a protected dispatch queue, and returns its result.

    Eventual concurrent database updates are postponed until the block has executed.

    try dbPool.unsafeReentrantWrite { db in
        try db.execute(...)
    }
    

    Eventual concurrent reads may see changes performed in the block before the block completes.

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

    Declaration

    Swift

    public func unsafeReentrantWrite<T>(_ block: (Database) throws -> T) rethrows -> T
  • 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, "SELECT succ(1)") // 2
    }
    

    Declaration

    Swift

    public func add(function: DatabaseFunction)
  • Remove an SQL function.

    Declaration

    Swift

    public func remove(function: DatabaseFunction)
  • 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("CREATE TABLE file (name TEXT COLLATE LOCALIZED_STANDARD")
    }
    

    Declaration

    Swift

    public func add(collation: DatabaseCollation)
  • Remove a collation.

    Declaration

    Swift

    public func remove(collation: DatabaseCollation)
  • 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
  • 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.