FeistyDB

A powerful and performant Swift interface to SQLite featuring:

• Type-safe and type-agnostic database values.
• Thread-safe synchronous and asynchronous database access.
• Full support for transactions and savepoints.
• Custom SQL functions, including aggregate and window functions.
• Custom collating sequences.
• Custom commit, rollback, update, and busy handler hooks.
• Custom virtual tables.
• Custom FTS5 tokenizers.
• Optional support for pre-update hooks and sessions

FeistyDB allows fast, easy database access with robust error handling. It is not a general-purpose object-relational mapper.

Installation

Swift Package Manager

Add a package dependency to https://github.com/feistydog/FeistyDB in Xcode.

Manual or Custom Build

1. Clone the FeistyDB repository.
2. swift build.

CSQLite and SQLite Build Options

FeistyDB is built atop CSQLite, a Swift package of the SQLite amalgamation with the carray, decimal, ieee754, series, sha3, and uuid extensions added.

Unfortunately there is no way using Swift Package Manager to expose package features or build options, in this case the SQLite pre-update hook and the session extension. For this reason SQLite build options must be customized by changing to a local CSQLite package dependency and editing CSQLite/Package.swift.

Quick Start

// Initialize FeistyDB
try FeistyDB.initialize()

// Create an in-memory database
let db = try Database()

// Create a table
try db.execute(sql: "CREATE TABLE t1(a,b);")

// Insert a row
try db.execute(sql: "INSERT INTO t1(a,b) VALUES (?,?);", 
               parameterValues: [33, "lulu"])

// Retrieve the values
try db.execute(sql: "SELECT a,b FROM t1;") { row in
    let a: Int = try row.value(at: 0)
    let b: String = try row.value(at: 1)
}

Segue to Thread Safety

FeistyDB uses SQLite with thread safety disabled for improved performance. While this increases performance, it also means a Database instance may only be accessed from a single thread or dispatch queue at a time.

Most applications should not create a Database directly but instead should use a thread-safe DatabaseQueue.

// Create a queue serializing access to an in-memory database
let dbQueue = try DatabaseQueue("myapp.database-isolation-queue")

This creates a queue which may be used from multiple threads or dispatch queues safely. The queue serializes access to the database ensuring only a single operation occurs at a time. Database operations may be performed synchronously or asynchronously.

// Perform a synchronous database access
try dbQueue.sync { db in
    // Do something with `db`
}

// Perform an asynchronous database access
dbQueue.async { db in
    // Do something with `db`
} completion: { result in
    // Handle any errors that occurred
}

For databases using Write-Ahead Logging concurrent reading and writing is supported. Multiple read operations may be performed simultaneously using more than one DatabaseReadQueue instance. Write operations must always be confined to a single DatabaseQueue. A typical usage pattern is one global DatabaseQueue instance used for writing located in the application's delegate, with DatabaseReadQueue instances located in individual view or window controllers. When used with long-running read transactions each DatabaseReadQueue maintains a separate, consistent snapshot of the database that may be updated in response to database changes.

Design

The core of FeistyDB is the types Database, Statement, and Row.

• Database is an SQLite database.

• Statement is a compiled SQL statement.

• Row is a single result row.

The fundamental type for native database values is DatabaseValue.

• DatabaseValue contains an integer, floating-point, textual, or blob value.

Type-safe access to database values is provided by classes implementing the ColumnConvertible protocol.

• ColumnConvertible is a type that can be initialized from a column in a result row.

SQL parameter binding is provided by classes implementing the ParameterBindable protocol.

• ParameterBindable is a type that can bind its value to an SQL parameter.

General object storage is provided by classes implementing the DatabaseSerializable protocol.

• DatabaseSerializable is a type that can be serialized to and deserialized from a database column.

Thread-safe access to a database is provided by DatabaseQueue.

• DatabaseQueue serializes work items on a database.
• DatabaseReadQueue serializes read operations on a database.

Examples

Create an In-Memory Database

let db = try Database()

This creates a database for use on a single thread or dispatch queue only. Most applications should not create a Database directly but instead should use a thread-safe DatabaseQueue.

Create a Table

try db.execute(sql: "CREATE TABLE t1(a,b);")

The created table t1 has two columns, a and b.

Insert Data

for i in 0..<5 {
    try db.execute(sql: "INSERT INTO t1(a,b) VALUES (?,?);",
                   parameterValues: [2*i, 2*i+1])
}

SQL parameters are passed as a sequence or series of values. Named parameters are also supported.

try db.execute(sql: "INSERT INTO t1(a,b) VALUES (:a,:b);",
               parameters: [":a": 100, ":b": 404])

Insert Data Efficiently

Rather than parsing SQL each time a statement is executed, it is more efficient to prepare a statement and reuse it.

let s = try db.prepare(sql: "INSERT INTO t1(a,b) VALUES (?,?);")
for i in 0..<5 {
    try s.bind(parameterValues: [2*i, 2*i+1])
    try s.execute()
    try s.reset()
    try s.clearBindings()
}

Fetch Data

The closure passed to execute() will be called with each result row.

try db.execute(sql: "SELECT * FROM t1;") { row in
    let x: Int = try row.value(at: 0)
    let y: Int? = try row.value(at: 1)
}

row is a Row instance.

Perform a Transaction

let result = try db.transaction { db in
    // Do something with `db`
    return .commit
}
// Result is either `.commit`. or `.rollback`

Database transactions may also be performed asynchronously using DatabaseQueue.

dbQueue.asyncTransaction { db in
    // Do something with `db`
    return .commit
} completion: { result in
    // Handle any errors that occurred
}

Custom SQL Functions

let rot13Mapping: [Character: Character] = [
    "A": "N", "B": "O", "C": "P", "D": "Q", "E": "R", "F": "S", "G": "T", "H": "U", "I": "V", "J": "W", "K": "X", "L": "Y", "M": "Z",
    "N": "A", "O": "B", "P": "C", "Q": "D", "R": "E", "S": "F", "T": "G", "U": "H", "V": "I", "W": "J", "X": "K", "Y": "L", "Z": "M",
    "a": "n", "b": "o", "c": "p", "d": "q", "e": "r", "f": "s", "g": "t", "h": "u", "i": "v", "j": "w", "k": "x", "l": "y", "m": "z",
    "n": "a", "o": "b", "p": "c", "q": "d", "r": "e", "s": "f", "t": "g", "u": "h", "v": "i", "w": "j", "x": "k", "y": "l", "z": "m"]

try db.addFunction("rot13", arity: 1) { values in
    let value = values.first.unsafelyUnwrapped
    switch value {
        case .text(let s):
            return .text(String(s.map { rot13Mapping[$0] ?? $0 }))
        default:
            return value
    }
}

rot13 can now be used just like any other SQL function.

let s = try db.prepare(sql: "INSERT INTO t1(a) VALUES (rot13(?));")

Custom Collating Sequences

try db.addCollation("localized_compare", { (lhs, rhs) -> ComparisonResult in
    return lhs.localizedCompare(rhs)
})

localized_compare is now available as a collating sequence.

let s = try db.prepare(sql: "SELECT * FROM t1 ORDER BY a COLLATE localized_compare;")

License

FeistyDB is released under the MIT License.