SQift

SQift is a lightweight Swift wrapper for SQLite.

Features

• [X] On-Disk, In-Memory and Temporary Database Connections
• [X] SQL Statement Execution
• [X] Generic Parameter Binding and Value Extraction
• [X] Codable and Codable Collection Bindings
• [X] Simple Query APIs for Values, Rows, and Collections
• [X] Transactions and Savepoints
• [X] Tracing and Trace Event Support
• [X] Scalar and Aggregate Functions
• [X] Commit, Rollback, Update, Authorizer Hooks
• [X] WAL Checkpointing
• [X] ConnectionQueue for Serial Execution per Database Connection
• [X] ConnectionPool for Parallel Execution of Read-Only Connections
• [X] Top-Level Database to Simplify Thread-Safe Reads and Writes
• [X] Database Migration Support
• [X] Database Backups
• [x] Comprehensive Unit Test Coverage
• [x] Complete Documentation

Requirements

• iOS 10.0+, macOS 10.12+, tvOS 10.0+, watchOS 3.0+
• Xcode 10.2+
• Swift 5.0+

Migration Guides

• SQift 4.0 Migration Guide

Communication

• Need help? Open an issue.
• Have a feature request? Open an issue.
• Find a bug? Open an issue.
• Want to contribute? Fork the repo and submit a pull request.

---

Installation

Swift Package Manager

The Swift Package Manager is a tool for managing the distribution of Swift code. It’s integrated with the Swift build system to automate the process of downloading, compiling, and linking dependencies.

Xcode 11+ is required to provide SwiftPM support for iOS, watchOS, and tvOS platforms. In Xcode menu File -> Swift Packages -> Add Package Dependency... enter repository URL https://github.com/nike-inc/SQift.git.

Or, alternatively, in a Package.swift file:

let package = Package(
    name: "MyPackage",
    dependencies: [
		.package(url: "https://github.com/nike-inc/SQift", from: "5.1"),
    ],
    products: [
        // ...
    ],
    targets: [
        .target(
            name: "YourTarget",
            dependencies: ["SQift"]
        ),
    // ...

CocoaPods

CocoaPods is a dependency manager for Cocoa projects. You can install it with the following command:

$ gem install cocoapods

CocoaPods 1.3+ is required to build SQift.

To integrate SQift into your Xcode project using CocoaPods, specify it in your Podfile:

platform :ios, '11.0'
use_frameworks!

target '<Your Target Name>' do
    pod 'SQift', '~> 4.0'
end

Then, run the following command:

$ pod install

Carthage

Carthage is a decentralized dependency manager that builds your dependencies and provides you with binary frameworks.

You can install Carthage with Homebrew using the following command:

$ brew update
$ brew install carthage

To integrate SQift into your Xcode project using Carthage, specify it in your Cartfile:

github "Nike-Inc/SQift" ~> 4.0

Run carthage update to build the framework and drag the built SQift.framework into your Xcode project.

---

Usage

SQift is designed to make it as easy as possible to work with SQLite from Swift. It does not, however, eliminate the need to understand how SQLite actually works. Before diving into SQift, it is recommended to first have a firm grasp on what SQLite is, how it works and how to use it.

• SQLite Docs
• SQLite Tutorial

SQift heavily leverages the new error handling model released with Swift 2.0. It was designed from the ground up to throw in all applicable cases. This makes it easy to wrap all your SQift calls in the do-catch paradigm.

Creating a Database Connection

Creating a database connection is simple.

let onDiskConnection = try Connection(storageLocation: .onDisk("path_to_db"))
let inMemoryConnection = try Connection(storageLocation: .inMemory)
let tempConnection = try Connection(storageLocation: .temporary)

There are also convenience parameters to make it easy to customize the flags when initializing the database connection:

let connection = try Connection(
    storageLocation: .onDisk("path_to_db"),
    readOnly: true,
    multiThreaded: false,
    sharedCache: false
)

In most cases, the default values are preferred. For more details about creating a database connection, please refer to the SQLite documentation.

Executing Statements

To execute a SQL statement on the Connection, you need to first create a Connection, then call execute.

let connection = try Connection(storageLocation: .onDisk("path_to_db"))

try connection.execute("PRAGMA foreign_keys = true")
try connection.execute("PRAGMA journal_mode = WAL")

try connection.execute("CREATE TABLE cars(id INTEGER PRIMARY KEY, name TEXT, price INTEGER)")

try connection.execute("INSERT INTO cars VALUES(1, 'Audi', 52642)")
try connection.execute("INSERT INTO cars VALUES(2, 'Mercedes', 57127)")

try connection.execute("UPDATE cars SET name = 'Honda' where id = 1")
try connection.execute("UPDATE cars SET price = 61_999 where name = 'Mercedes'")

try connection.execute("DELETE FROM cars where name = 'Mercedes'")
try connection.execute("DROP TABLE cars")

Bindings

Most Swift data types cannot be directly stored inside the database. They need to be converted to a data type supported by SQLite. In order to support moving Swift data types into the database and back out again, SQift leverage three powerful protocols: Bindable, Extractable, and Binding.

Bindable Protocol

The Bindable protocol handles converting Swift data types into a BindingValue enumeration type which can be stored in the database.

public protocol Bindable {
    var bindingValue: BindingValue { get }
}

Extractable Protocol

While the Bindable protocol helps move Swift data types into the database, the Extractable protocol allows SQift to extract the values from the database Connection and convert them back to the requested Swift data type.

public protocol Extractable {
    typealias BindingType
    typealias DataType = Self
    static func fromBindingValue(_ value: Any) -> DataType?
}

Binding Protocol

To extend Swift data types to be able to be inserted into the database and also be extracted safely, the Binding protocol forces the data type to conform to both the Bindable and Extractable protocols.

public protocol Binding: Bindable, Extractable {}

In order to make it as easy as possible to use SQift, SQift extends the following Swift data types to conform to the Binding protocol:

• NULL: NSNull
• INTEGER: Bool, Int8, Int16, Int32, Int64, Int, UInt8, UInt16, UInt32, UInt64, UInt
• REAL: Float, Double
• TEXT: String, URL, Date
• BLOB: Data

Additional Swift data types can easily add Bindable protocol conformance if necessary.

Binding Parameters to a Statement

Safely binding parameters to a Statement is easy thanks to the Binding protocol. First you need to prepare a Statement object, then bind the parameters and run it using method chaining.

let connection = try Connection(storageLocation: .onDisk("path_to_db"))

try connection.prepare("INSERT INTO cars VALUES(?, ?, ?)").bind(1, "Audi", 52_642).run()
try connection.prepare("INSERT INTO cars VALUES(:id, :name, :price)").bind([":id": 1, ":name": "Audi", ":price": 52_642]).run()

There are also convenience methods on the Connection for preparing a Statement, binding parameters and running it all in a single method named run.

let connection = try Connection(storageLocation: .onDisk("path_to_db"))

try connection.run("INSERT INTO cars VALUES(?, ?, ?)", 1, "Audi", 52_642)
try connection.run("INSERT INTO cars VALUES(:id, :name, :price)", parameters: [":id": 1, ":name": "Audi", ":price": 52_642])

It is very important to properly escape all parameter values provided in a SQL statement. When in doubt, always use the provided bind functionality.

Querying Data

Querying data from the database makes extensive use of the Binding protocol. It extracts the original values from the database, then uses the Binding protocol along with generics to convert the final Swift type.

Single Values

Extracting a single value from the database can be done using the query API.

let synchronous: Int? = try db.query("PRAGMA synchronous")
let minPrice: UInt? = try db.query("SELECT avg(price) FROM cars WHERE price > ?", 40_000)

Multiple Values

You can also use the query API to extract multiple values through the Row type.

if let row = try db.query("SELECT name, type, price FROM cars WHERE type = ? LIMIT 1", "Sedan") {
    let name: String = row[0]
    let type: String = row[1]
    let price: UInt = row[2]
}

The values can be accessed by index or by name.

if let row = try db.query("SELECT name, type, price FROM cars WHERE type = ? LIMIT 1", "Sedan") {
    let name: String = row["name"]
    let type: String = row["type"]
    let price: UInt = row["price"]
}

The Row type supports both optional and non-optional value extraction through index and name subscripts. The non-optional subscripts are certainly the saf