This is the second post in a series about powering modern apps with SQLite. The other posts in this series are:

• Powering modern apps with SQLite
• Encoding and decoding SQLite in Swift
• Reacting to changes to data in the SQLite database

When it’s time to start implementing a real SQLite-based persistence layer for a Swift iOS or macOS app, one of the first things we’ll need to do is write a Swift wrapper around SQLite. As I mentioned in my previous article, there are already plenty of high-quality, open-source SQLite wrappers around, including GRDB and SQLite.swift. However, I prefer to write my own. I like to limit the dependencies I add to my apps and I’m very opinionated. Plus, writing a wrapper around SQLite is pretty easy and gives us experience interacting with SQLite at a low level.

Hiding OpaquePointer

SQLite’s C interface is exposed to Swift as a bunch of OpaquePointers and Int32 values. Hiding these opaque types is my primary goal of wrapping SQLite in a Swift interface. The best place to start is opening a database with SQLite’s open function. In C, it looks like int sqlite3_open(const char *filename, sqlite3 **ppDb), which translates to Swift as func sqlite3_open(_ filename: UnsafePointer<Int8>!, _ ppDb: UnsafeMutablePointer<OpaquePointer?>!) -> Int32. Not pretty. Thankfully, Swift’s interoperability with C is pretty great. We can pass a simple Swift.String file path directly to the filename parameter in sqlite3_open(). There’s not much we can do about the OpaquePointer, but we’ll keep it hidden inside of a SQLite.Database class.

class func open(at path: String) throws -> OpaquePointer {
	var optionalConnection: OpaquePointer?
	let result = sqlite3_open(path, &optionalConnection)

	guard SQLITE_OK == result else {
		SQLite.Database.close(optionalConnection)
		let error = SQLite.Error.onOpen(result, path)
		assertionFailure(error.description)
		throw error
	}

	guard let connection = optionalConnection else {
		let error = SQLite.Error.onOpen(SQLITE_INTERNAL, path)
		assertionFailure(error.description)
		throw error
	}

	return connection
}

There’s a lot of code here, but it’s relatively straightforward. We start by creating a variable that will eventually hold the database connection: var optionalConnection: OpaquePointer?. We pass this variable to SQLite’s open function as an in-out variable. If sqlite3_open() succeeds, optionalConnection will hold a valid SQLite connection. If sqlite3_open() fails, we don’t know what the variable will hold, but SQLite’s documentation tells us to pass it to sqlite3_close() (which we’ve wrapped in SQLite.Database.close()).

So, to check if sqlite3_open() succeeds, we compare its return value against the named integer constant SQLITE_OK, which is one of the Int32 constants that SQLite defines for the return values of its C functions. If the return value of sqlite3_open() isn’t equal to SQLITE_OK, we create a custom error from it and throw. We do the same thing if optionalConnection is still nil, even though sqlite3_open() reported that it succeeded. This should never happen, which means that we could probably safely use ! to unwrap optionalConnection, but I don’t ever use ! in production code. Finally, we return the safely-unwrapped connection so that it can be used throughout SQLite.Database for updating or fetching values from the database.

Performing queries

Opening a SQLite database is relatively straightforward. Executing SQL statements is a bit more complicated. In SQLite.Database, I model all database queries as either a “read” or a “write”. However, the steps for both the read and write operations are the same:

1. Create (or reuse) a SQLite statement
2. Bind the arguments to the statement
3. Evaluate the statement

The only real difference between read and write operations is the return value expected. In simple terms, a write either succeeds or fails. So, we can expect to receive a return value signifying success (SQLITE_DONE) or one that signifies failure (e.g., SQLITE_MISUSE, SQLITE_RANGE, etc.) A read, on the other hand, expects to receive some number of results that match the query. This means that, in addition to supporting the same success and failure return values as write queries, reads also have to handle the SQLITE_ROW result code, which indicates that there is at least one row that matches the query.

Writing to the database

The implementation of SQLite.Database.write() is pretty simple and follows the steps listed above.

func write(_ sql: SQL, arguments: SQLiteArguments) throws {
	guard _isOpen else { assertionFailure("Database is closed"); return }

	let statement = try self.statement(for: sql)

	defer {
		sqlite3_reset(statement)
		sqlite3_clear_bindings(statement)
	}

	try bind(arguments: arguments, to: statement)

	let result = sqlite3_step(statement)
	if result != SQLITE_DONE && result != SQLITE_ROW && result != SQLITE_INTERRUPT {
		throw SQLite.Error.onStep(result, sql)
	}
}

After verifying that the database is, in fact, open, we fetch a cached statement for the SQL query or we create a new one.

func statement(for sql: SQL) throws -> OpaquePointer {
	if let cached = _cachedStatements[sql] {
		return cached
	} else {
		let prepared = try prepare(sql)
		_cachedStatements[sql] = prepared
		return prepared
	}
}

If we don’t have a cached statement already, we need to create one. Creating a new statement involves calling the ugly-looking prepare statement SQLite function:

sqlite3_prepare_v2(OpaquePointer!, UnsafePointer<Int8>!, Int32, UnsafeMutablePointer<OpaquePointer?>!, UnsafeMutablePointer<UnsafePointer<Int8>?>!) -> Int32

Although the prepare function has more arguments, it’s conceptually similar to the open function. We need to pass our database connection (the return value of our SQLite.Database.open() function), as the first argument. The second argument is SQL statement that we want to prepare. Thankfully, even though the type is UnsafePointer<Int8>! (which is the way Swift translates the const char * argument from the original C SQLite interface), we can pass a Swift string directly to this argument—no conversion necessary! The third argument is the maximum length of the SQL statement we passed as the second argument. We can pass -1 here to tell SQLite to read the string until the first zero terminator, which Swift automatically appends to the end of the string when passing it to a C function. The fourth argument is an in-out variable that will hold the prepared SQLite statement if this function is successful. The fifth argument doesn’t apply to us. So, we can pass nil. In the end, the implementation of SQLite.Database.prepare() is pretty simple:

func prepare(_ sql: SQL) throws -> OpaquePointer {
	var optionalStatement: OpaquePointer?
	let result = sqlite3_prepare_v2(_connection, sql, -1, &optionalStatement, nil)
	guard SQLITE_OK == result, let statement = optionalStatement else {
		sqlite3_finalize(optionalStatement)
		let error = SQLite.Error.onPrepareStatement(result, sql)
		assertionFailure(error.description)
		throw error
	}
	return statement
}

After fetching or creating a prepared statement, we bind the arguments to the statement. Binding arguments to a statement is separate from creating the prepared statement because this allows the statements to be reused, which is significantly more efficient than creating a new prepared statement every time a query is run. SQLiteArguments is a typealias of Dictionary<String, SQLite.Value>, the key of which is the column name of the argument. In turn, SQLite.Value is an enum that maps to all of the primitive types that SQLite supports:

enum Value {
	case data(Data)
	case double(Double)
	case integer(Int64)
	case null
	case text(String)
}

The bind function is very simple. We iterate over each of the key-value pairs in SQLiteArguments. For each column name (the key in SQLiteArguments), we prepend “:”, which is one of the ways that SQLite identifies named columns. Then, we get the index of the parameter in the prepared statement by calling sqlite3_bind_parameter_index(). Once we have the index, we use that to bind the value to prepared statement according to the value’s type.

func bind(value: SQLite.Value, to index: Int32, in statement: OpaquePointer) throws {
	let result: Int32
	switch value {
	case .data(let data):
		result = data.withUnsafeBytes { (bytes: UnsafePointer) -> Int32 in
			return sqlite3_bind_blob(statement, index, bytes, Int32(data.count), SQLITE_TRANSIENT)
		}
	case .double(let double):
		result = sqlite3_bind_double(statement, index, double)
	case .integer(let int):
		result = sqlite3_bind_int64(statement, index, int)
	case .null:
		result = sqlite3_bind_null(statement, index)
	case .text(let text):
		result = sqlite3_bind_text(statement, index, text, -1, SQLITE_TRANSIENT)
	}

	if SQLITE_OK != result {
		throw SQLite.Error.onBindParameter(result, index, value)
	}
}

After binding all of the parameters to the prepared statement, we execute it by calling sqlite3_step(). This returns a result code, which will be SQLITE_DONE, if the statement was executed correctly. If the result is something other than SQLITE_DONE, then something has gone wrong, and we would throw an error which the caller should handle. After executing the prepared statement, the last step is to reset the statement and clear the bound parameters. We do that by calling sqlite3_reset() and sqlite3_clear_bindings(). This allows us to reuse the statement—without having to create a new one—the next time we run the same SQL.

Reading from the database

As I said above, reading from the database is very similar to writing to it. The only difference comes when evaluating the prepared statement. As is the case when writing, in SQLite.Database.read(_:arguments:), after preparing the statement and binding the parameters to it, we call sqlite3_step(). If the query returns some number of matches, then the return value of sqlite3_step() will be SQLITE_ROW. In this case, we need to figure out what values the statement returned and which table columns those values correspond to.

func row(for statement: OpaquePointer) throws -> SQLiteRow {
	let columnCount = sqlite3_column_count(statement)
	guard columnCount > 0 else { return [:] }

	var output = SQLiteRow()
	for column in (0..<columnCount) {
		let name = String(cString: sqlite3_column_name(statement, column))
		let value = try self.value(for: statement, at: column)
		output[name] = value
	}
	return output
}

Figuring out the column names and values returned is straightforward and similar to the process we used to bind parameters to a statement. First, we ask SQLite for the number of columns returned by the statement. In the case of running the SQL SELECT name, age FROM user WHERE id=:id;, two columns would be returned: “name” and “age”. We retrieve the names of the columns, in the order they are in the SQL statement, by calling sqlite3_column_name(). After collecting the column names, we then fetch each of the returned values.

func value(for statement: OpaquePointer, at column: Int32) throws -> SQLite.Value {
	let type = sqlite3_column_type(statement, column)

	switch type {
	case SQLITE_BLOB:
		guard let bytes = sqlite3_column_blob(statement, column) else { return .null }
		let count = sqlite3_column_bytes(statement, column)
		if count > 0 {
			return .data(Data(bytes: bytes, count: Int(count)))
		} else {
			return .null // Does it make sense to return null if the data is zero bytes?
		}
	case SQLITE_FLOAT:
		return .double(sqlite3_column_double(statement, column))
	case SQLITE_INTEGER:
		return .integer(sqlite3_column_int64(statement, column))
	case SQLITE_NULL:
		return .null
	case SQLITE_TEXT:
		guard let cString = sqlite3_column_text(statement, column) else { return .null }
		return .text(String(cString: cString))
	default:
		throw SQLite.Error.onGetColumnType(type)
	}
}

Fetching the values is relatively simple. We first ask SQLite for the type of the data returned for a particular column. Then, we fetch the raw C value, convert it into a native Swift type, and put it inside of SQLite.Value. Then, we put the column names and SQLite.Values together inside of a SQLiteRow, which is another typealias for Dictionary<String, SQLite.Value>. This SQLiteRow is collected in an array of rows that will be returned to the caller of SQLite.Database.read(_:arguments:).

var result = sqlite3_step(statement)
while result == SQLITE_ROW {
	try output.append(row(for: statement))
	result = sqlite3_step(statement)
}

As long as sqlite3_step() returns SQLITE_ROW, we continue appending new rows to the result array. Finally, when sqlite3_step() returns SQLITE_DONE, then we return the array of fetched rows to the caller. If anything besides SQLITE_ROW OR SQLITE_DONE is returned from sqlite3_step(), then we throw an error.

Thread safety

Be aware that, although SQLite is, by default, thread-safe, SQLite.Database is not. This was a conscious choice because 1) it’s easier to reason about single-threaded code and 2) SQLite is fast enough to be accessed serially in all of the apps I’m working on. However, to enforce serial access of SQLite.Database, I always write a wrapper class around SQLite.Database that runs all database queries on a serial dispatch queue. I’ll include an example of this in a future blog post that shows how SQLite.Database is used to power a simple Slack client.

Going forward

Although SQLite is an incredibly powerful and feature-rich tool, many app developers only need to know how to safely write data to the database and quickly read data from the database. In order to learn how to take advantage of some of SQLite’s more powerful features, be sure to read the other posts in this series. Otherwise, check out the code for ‘SQLite.Database’ on Github.