Happy 2024! In this series of posts I'm going to be running through the implementation of a lightweight SQLite wrapper, from scratch, in Swift.
So why do this at all? There are a bunch of high quality packages that wrap SQLite, however I've always found it useful to have a strong understanding of the lowest level of data flow in any app that I'm building. This pays dividends as soon as you hit some weird persistence issue, or unexpected performance slowdown at the persistence layer, and honestly, it's pretty straightforward (it's not like we're actually implementing a database here!).
Design goals are simple: we'll be dealing with a pure SQL interface (no ORM-style APIs, although you could certainly build those on top if you like). We will aim to support Swift Concurrency from the ground up, and we want to support parallel reads (along with a single writer) using SQLite WAL-mode. Let's get into it 🚀
Out first step will put all aspects of parallelism to the side, and focus on opening a single SQLite connection. We'll define a few typealiass to distinguish the various OpaquePointers that the SQLite library vends.
/// An opaque handle to a SQLite connection.
typealias ConnectionHandle = OpaquePointer
/// An opaque handle to a SQLite statement.
typealias StatementHandle = OpaquePointer
We can then go ahead and define an actor for our connection, which will serialize all executions against the connection. Our Connection actor takes a URL for where to open the connection, and exposes an API to allow queries to be executed against it.
import Dispatch
import Foundation
import SQLite3
public actor Connection {
public init(url: URL) async throws {
// Open the connection, retrieving a `ConnectionHandle`.
let queue = DispatchQueue(label: "Connection \(UUID().uuidString)")
let executor = Executor(queue: queue)
connectionHandle = try await Opener(executor: executor, url: url).open()
self.queue = queue
self.executor = executor
// Initialize the connection.
try execute("PRAGMA journal_mode = WAL")
try execute("PRAGMA synchronous = NORMAL")
try execute("PRAGMA foreign_keys = ON")
}
deinit {
sqlite3_close_v2(connectionHandle)
}
public nonisolated var unownedExecutor: UnownedSerialExecutor {
executor.asUnownedSerialExecutor()
}
public func execute(_ query: String) throws {
fatalError("TODO")
}
private let connectionHandle: ConnectionHandle
private let queue: DispatchQueue
private nonisolated let executor: Executor
}
In deinit, we ensure that we clean up after ourselves and close the connection using sqlite3_close_v2. Once we've opened the connection, we run a few PRAGMA statements to setup the database. PRAGMAs are extensions specific to SQLite used to configure the database (documented here), and the ones we set are:
journal_mode = WAL: Specifies the behavior of the rollback journal, which has implications on connection parallelization. I've used WAL mode previously in production.synchronous = NORMAL: Controls how SQLite synchronizes against the file system. synchronous = NORMAL is "a good choice for most applications running in WAL mode", according to documentation.foreign_keys = ON: Enables support for SQL foreign keys.There are also a few additional types here: Executor, and Opener. Both were previously covered in my previous post, Controlling Actors With Custom Executors, but to summarize:
Executor is a custom SerialExecutor which is used by the actor. This determines how the code running on the actor is executed. By default, this would be on the cooperative thread pool managed by the Swift runtime (with one thread per CPU core), however for potentially blocking disk access (such as when executing through a SQLite connection), it may be a good idea to shift this work onto a separate queue. Executor simply dispatches its jobs onto its DispatchQueue (which is created in Connection, with one per connection).extension Connection {
final class Executor {
init(queue: DispatchQueue) {
self.queue = queue
}
private let queue: DispatchQueue
}
}
extension Connection.Executor: SerialExecutor {
func enqueue(_ job: consuming ExecutorJob) {
let unownedJob = UnownedJob(job)
let unownedExecutor = asUnownedSerialExecutor()
queue.async {
unownedJob.runSynchronously(on: unownedExecutor)
}
}
func asUnownedSerialExecutor() -> UnownedSerialExecutor {
UnownedSerialExecutor(ordinary: self)
}
}
Opener is responsible for actually opening the SQLite connection. It also takes an Executor (and operates on it), and this functionality is split out to a separate type since the Connection actor is not ready to execute on its Executor until it's fully initialized (which it isn't, until its connectionHandle is set. 🐓 & 🥚!). For now, we're not handling errors.extension Connection {
actor Opener {
init(executor: Executor, url: URL) {
self.executor = executor
self.url = url
}
nonisolated var unownedExecutor: UnownedSerialExecutor {
executor.asUnownedSerialExecutor()
}
func open() throws -> ConnectionHandle {
var connectionHandle: ConnectionHandle?
let openResult = sqlite3_open_v2(
url.path,
&connectionHandle,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX,
nil
)
guard let connectionHandle else {
fatalError("TODO: Handle errors")
}
guard openResult == SQLITE_OK else {
sqlite3_close(connectionHandle)
fatalError("TODO: Handle errors")
}
return connectionHandle
}
private nonisolated let executor: Executor
private let url: URL
}
}
Time to fill in Connection.execute, so we can actually use our Connection. As before, we're not handling errors for now.
public actor Connection {
...
public func execute(_ query: String) throws {
// Prepare a statement for `query`, retrieving a `StatementHandle`.
var statementHandle: StatementHandle?
let prepareResult = sqlite3_prepare_v3(
connectionHandle,
query,
-1,
0,
&statementHandle,
nil
)
guard prepareResult == SQLITE_OK, let statementHandle else {
fatalError("TODO: Handle errors")
}
// Ensure the statement is finalized following execution (even if execution fails).
defer {
sqlite3_finalize(statementHandle)
}
// Execute the statement.
try execute(statementHandle: statementHandle)
}
private func execute(statementHandle: StatementHandle) throws {
// Continuously call `sqlite3_step` until execution is complete, or there's an error.
while true {
let stepResult = sqlite3_step(statementHandle)
switch stepResult {
case SQLITE_DONE:
return
case SQLITE_ROW:
continue
default:
fatalError("TODO: Handle errors")
}
}
}
}
Executing a statement first requires us to prepare it through a call to sqlite3_prepare_v3. Assuming that goes well, we can execute it by continuously calling sqlite3_step until it reports that it's done. This multi-step execution will make more sense once we start actually extracting return values from our queries, but for now, we just let it run to completion. Once done, we ensure we clean up resources with sqlite3_finalize.
Whilst this works to execute simple statements, I'll take a second here to outline why this is not a good implementation, and is only used as a stepping-stone:
SELECT * FROM users WHERE id = <something>) is to do Swift string interpolation (SELECT * FROM users WHERE id = \(myUserID)). This is definitely not a good idea, and will open you up to potential SQL injection attacks if you're building statements from untrusted user data. We'll cover binding parameters in the next post in the series.execute returns nothing. We'll also cover that in the next post.Whilst it's not much, we're already in a position to start writing some tests to verify that our Connection is operating as we'd expect. We'll first define our test fixture, and add some helpers that will give us a temporary database URL we can use throughout the test. This will be cleaned up in tearDown.
final class ConnectionTests: XCTestCase {
override func setUp() {
super.setUp()
temporaryDirectoryURL = URL(filePath: NSTemporaryDirectory(), directoryHint: .isDirectory)
.appending(path: UUID().uuidString, directoryHint: .isDirectory)
try! FileManager.default.createDirectory(
at: temporaryDirectoryURL,
withIntermediateDirectories: true
)
}
override func tearDown() {
try! FileManager.default.removeItem(at: temporaryDirectoryURL)
temporaryDirectoryURL = nil
super.tearDown()
}
private var temporaryDirectoryURL: URL!
private func temporaryDatabaseURL() -> URL {
temporaryDirectoryURL.appending(path: UUID().uuidString, directoryHint: .notDirectory)
}
}
We can then write a very basic test, which opens a connection and creates a table. Assuming it doesn't fatalError due to an error, our test should pass!
final class ConnectionTests: XCTestCase {
...
/// Verifies that a `Connection` can be opened, and used to execute statements.
func testConnection() async throws {
// Given:
let connection = try await Connection(url: temporaryDatabaseURL())
// Then:
try await connection.execute("CREATE TABLE test_table (id INTEGER NOT NULL)")
}
}
To finish up part 1, let's clean up our error handling so that we're not just spewing fatalErrors whenever something doesn't go to plan. We can define a new Error enum, alongside a helper function to extract some error information following a SQLite error.
enum Error: Swift.Error {
/// An error occurred when opening a connection at `url`.
case connectionOpen(url: URL, description: String)
/// An error occurred when executing `query`.
case execute(query: String, description: String)
// MARK: Internal
/// Returns a description of the last error that occurred whilst using `connectionHandle`.
static func errorDescription(connectionHandle: ConnectionHandle) -> String {
let errorCode = Int(sqlite3_errcode(connectionHandle))
let errorMessage = String(cString: sqlite3_errmsg(connectionHandle), encoding: .utf8) ??
"-"
return "SQLite error \(errorCode): \(errorMessage)"
}
}
Error.errorDescription uses sqlite3_errcode and sqlite3_errmsg to fetch an error code and description for the error that has just ocurred on connectionHandle, allowing us to give descriptive errors back to the client. This also includes detailing syntax errors in statements, meaning we have to do very little work to present pretty useful errors to the caller.
We can then update our code in Opener to use these new errors.
extension Connection {
actor Opener {
...
func open() throws -> ConnectionHandle {
var connectionHandle: ConnectionHandle?
let openResult = sqlite3_open_v2(
url.path,
&connectionHandle,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX,
nil
)
guard let connectionHandle else {
// Error now handled here.
throw Error.connectionOpen(url: url, description: "Cannot allocate memory for handle")
}
guard openResult == SQLITE_OK else {
// Error now handled here.
let errorDescription = Error.errorDescription(connectionHandle: connectionHandle)
sqlite3_close(connectionHandle)
throw Error.connectionOpen(url: url, description: errorDescription)
}
return connectionHandle
}
}
}
As well as the query execution code we just wrote in Connection.
public actor Connection {
...
public func execute(_ query: String) throws {
// Prepare a statement for `query`, retrieving a `StatementHandle`.
var statementHandle: StatementHandle?
let prepareResult = sqlite3_prepare_v3(
connectionHandle,
query,
-1,
0,
&statementHandle,
nil
)
guard prepareResult == SQLITE_OK, let statementHandle else {
// Error now handled here.
throw Error.execute(
query: query,
description: Error.errorDescription(connectionHandle: connectionHandle)
)
}
// Ensure the statement is finalized following execution (even if execution fails).
defer {
sqlite3_finalize(statementHandle)
}
// Execute the statement.
try execute(query: query, statementHandle: statementHandle)
}
private func execute(query: String, statementHandle: StatementHandle) throws {
// Continuously call `sqlite3_step` until execution is complete, or there's an error.
while true {
let stepResult = sqlite3_step(statementHandle)
switch stepResult {
case SQLITE_DONE:
return
case SQLITE_ROW:
continue
default:
// Error now handled here.
throw Error.execute(
query: query,
description: Error.errorDescription(connectionHandle: connectionHandle)
)
}
}
}
}
And since our code doesn't just crash on error now, we can write tests to verify that an error is thrown. I've added a custom assertThrows helper, since XCTAssertThrows doesn't support async expressions.
final class ConnectionTests: XCTestCase {
...
/// Verifies that an error is thrown when opening a `Connection` at an invalid `URL`.
func testConnectionOpenError() async throws {
await assertThrows(
try await Connection(url: URL(filePath: "")),
"No failure when opening connection"
)
}
/// Verifies than an error is thrown when executing a malformed query against a
/// `Connection`.
func testExecutionError() async throws {
// Given:
let connection = try await Connection(url: temporaryDatabaseURL())
// Then:
await assertThrows(
try await connection.execute("NOT_A_QUERY"),
"No failure when executing query"
)
}
/// Asserts that `body` throws an error, failing the current test with `message` if not.
private func assertThrows<R>(
_ body: @autoclosure () async throws -> R,
_ message: String
) async -> Void {
do {
_ = try await body()
XCTFail(message)
} catch {}
}
}
And with that, we can now spin up a SQLite connection and execute statements against it. The code up to this point is available on Github.
Although we can't actually read anything from our database yet, we'll get there before you know it (in the next part, in fact). See you there!