Encoding and decoding SQLite in Swift
This is the third post in a series about powering modern apps with SQLite. The other posts in this series are:
- Powering modern apps with SQLite
- Building a lightweight SQLite wrapper in Swift
- Reacting to changes to data in the SQLite database
One of the best changes to Swift so far was the addition of the Codable protocol to the Swift standard library. This protocol, which combines the underlying Encodable and Decodable protocols, defines a standardized approach to encoding and decoding data. Explaining the ins and outs of Swift’s Encodable and Decodable protocols is out of the scope of this post, but Apple’s documentation is quite good, and there are plenty of in-depth guides on the Web.
The ultimate benefit of Swift’s Codable implementation is that we developers can write a model type made up of simple types, declare its conformance to Codable, and the compiler will generate all of the code needed to support decoding it to and encoding it from Data.
struct Task: Codable, Equatable {
var title: String
var dueDate: Date?
var isCompleted: Bool
}
Then, all we need to do to encode it to JSON and decode it back to a Task is to instantiate and use instances of JSONEncoder and JSONDecoder.
let jsonEncoder = JSONEncoder()
let jsonDecoder = JSONDecoder()
let id = UUID().uuidString
let tomorrow = Date(timeIntervalSinceNow: 86400)
let task = Task(id: id, title: "Buy milk", dueDate: tomorrow, isCompleted: false)
// wrap these calls in do-catch blocks in real apps
let json = try! jsonEncoder.encode(task)
let taskFromJSON = try! jsonDecoder.decode(Task.self, from: json)
The Swift standard library includes support for encoding data to and decoding data from JSON and property lists. As this blog series is all about powering modern apps with SQLite, it would be great if we could replace the JSONEcoder and JSONDecoder in the previous example with a SQLite encoder and decoder.
Encoding to and decoding from SQLite
There are many potential ways to encode model types to and decode them from SQLite. Perhaps the easiest approach would be to use a SQLite table with a single BLOB column to store encoded JSON. Taking this approach would sacrifice most of the power of SQLite, however, because, among other reasons, we’d lose the ability to create indexes on specific columns to increase the speed of specific queries. (We could regain some of the advantages of using SQLite if, instead of saving the JSON as ‘Data’, we saved it as text and then used SQLite’s JSON extension to access and modify it.)
Alternately, we could write an encoder/decoder that uses introspection to create tables based on properties of our specific model types (i.e., for the example above, the encoder would create a table called “Task” that includes three columns: title, dueDate, and isCompleted with the correct types). The convenience of this approach, in my opinion, is outweighed by the incredible amount of work it would require, especially if we wanted to support anything besides a straightforward translation from a simple model type to a database table. If this sort of thing sounds appealing, consider using Core Data or Realm.
I prefer to take a middle approach in which the developer is responsible for creating the tables for the model types and for writing the SQL to update and fetch the model types from the database, but the SQLite encoder is responsible for
converting the model types to a form that SQLite understands. The SQLite decoder is then responsible for taking the SQLite data types and converting them into our model types. In practice, this approach requires us to write a bit more code than is required to just add conformance to the Codable protocol, but the difference is very small.
struct Task: Codable, Equatable {
var title: String
var dueDate: Date?
var isCompleted: Bool
}
extension Task {
static var createTable: SQL {
return "CREATE TABLE tasks (title TEXT NOT NULL, dueDate TEXT, isCompleted INTEGER NOT NULL);"
}
static var upsert: SQL {
return "INSERT OR REPLACE INTO tasks VALUES (:title, :dueDate, :isCompleted);"
}
static var fetchAll: SQL {
return "SELECT title, dueDate, isCompleted FROM tasks;"
}
static var fetchByID: SQL {
return "SELECT id, title, dueDate, isCompleted FROM tasks WHERE id=:id;"
}
}
In the previous example, ‘SQL’ is just a typealias for ‘String’
Then, in the approach I’ve chosen, using SQLite.Encoder and SQLite.Decoder with our custom model type is extremely easy.
// wrap these calls in do-catch blocks in real apps
let database = try! SQLite.Database(path: ":memory:")
try! database.execute(raw: Task.createTable)
let sqliteEncoder = SQLite.Encoder(database)
let sqliteDecoder = SQLite.Decoder(database)
// wrap these calls in do-catch blocks in real apps
try! sqliteEncoder.encode(task, using: Task.upsert)
let allTasks = try! sqliteDecoder.decode(Array<Task>.self, using: Task.fetchAll)
let taskFromSQLite = try! sqliteDecoder.decode(Task.self, using: Task.fetchByID, arguments: ["id": .text(id)])
SQLite.Encoder and SQLite.Decoder are modeled closely on JSONEncoder and JSONDecoder, the implementations for which can be found here. The major differences between the SQLite and JSON versions are 1) the SQLite varieties need to be initialized with an instance of SQLite.Database and 2) encoding and decoding require a SQL statement and, sometimes, some arguments for the SQL statement.
SQLite.Encoder
The best way to understand how SQLite.Encoder works is to first review how encoding works in Swift. Our Task type is very simple, which means the Swift compiler is able to automatically synthesize its implementation of encode(to:), but, in order to understand how it works, let’s implement it ourselves.
extension Task: Encodable {
func encode(to encoder: Encoder) throws {
var container = encoder.container(keyedBy: Task.CodingKeys.self)
try container.encode(id, forKey: .id)
try container.encode(title, forKey: .title)
try container.encode(dueDate, forKey: .dueDate)
try container.encode(isCompleted, forKey: .isCompleted)
}
}
To add support for encoding our Task, all we need to do is declare conformance to Encodable and implement func encode(to encoder: Encoder) throws. Inside of this method, we first ask the encoder for a key-value container to hold our model’s properties. The encoder is something that conforms to the Encoder protocol. The key-value container is a more complicated type. Swift’s documentation gives its type as KeyedEncodingContainer<Key> where Key : CodingKey.
KeyedEncodingContainer is an actual struct; it’s not a protocol. Its initializer has the signature init<Container>(_ container: Container) where K == Container.Key, Container : KeyedEncodingContainerProtocol, which means the KeyedEncodingContainer is a wrapper around something that is generic on the Task.CodingKeys Key type and conforms to the KeyedEncodingContainerProtocol protocol. After we have gotten the key-value container from the encoder, we tell the container to encode each one of our model’s properties and save it using its key. In the case of our SQLite encoder, the keys each correspond to a column in the tasks table.
Proceeding from the above, it’s clear that, at the very least, our SQLite.Encoder will need to implement an encoder that conforms to the Encoder protocol and a container that conforms to KeyedEncodingContainerProtocol. Interestingly, the encoder and container are both private types hidden behind the public SQLite.Encoder interface. Let’s implement the encoder.
private class _SQLiteEncoder: Encoder {
var codingPath: Array<CodingKey> = []
var userInfo: [CodingUserInfoKey : Any] = [:]
var encodedArguments: SQLiteArguments { return _storage.arguments }
private let _storage = _KeyedStorage()
func container<Key>(keyedBy type: Key.Type) -> KeyedEncodingContainer<Key> where Key: CodingKey {
_storage.reset()
return KeyedEncodingContainer(_KeyedContainer<Key>(_storage))
}
func unkeyedContainer() -> UnkeyedEncodingContainer {
fatalError("_SQLiteEncoder doesn't support unkeyed encoding")
}
func singleValueContainer() -> SingleValueEncodingContainer {
fatalError("_SQLiteEncoder doesn't support single value encoding")
}
}
Most of this code is boilerplate that we had to add to conform to the Encoder protocol. One thing to note is that our _SQLiteEncoder is not as full-featured as the ones that power JSONEncoder or PropertyListEncoder because ours only supports keyed encoding and does not support nesting. That being said, our _SQLiteEncoder is very similar to—and, in fact, modeled after—Swift’s _JSONEncoder. When asked for a key-value container, _SQLiteEncoder creates and returns a new KeyedEncodingContainer that wraps our private _KeyedContainer, which itself holds a reference to our private _KeyedStorage. Let’s first take a look at _KeyedStorage.
private class _KeyedStorage {
private var _elements = SQLiteArguments()
var arguments: SQLiteArguments { return _elements }
func reset() {
_elements.removeAll(keepingCapacity: true)
}
subscript(key: String) -> SQLite.Value? {
get {
return _elements[key]
}
set {
_elements[key] = newValue
}
}
}
_KeyedStorage is a simple, reference-typed wrapper around SQLiteArguments, which is just a typealias for Dictionary<String, SQLite.Value>. The purpose of _KeyedStorage is to hold the SQLite values we want to encode for a single entity. This corresponds to a single row of values in a SQLite table. For example, if we were to encode Task(title: "Buy milk", dueDate: nil, isCompleted: false), _KeyedStorage would end up holding a dictionary like ["title": .text("Buy milk"), "dueDate": .null, "isCompleted": .integer(false)]. It needs to be a reference type because _SQLiteEncoder needs to be able to return the encoded values via its arguments calculated variable. As we can see in the code for _SQLiteEncoder above, every time a new key-value container is returned from container(keyedBy:), the keyed storage is reset, removing the previously-encoded entity’s properties from _KeyedStorage._elements.
So, that leaves _KeyedContainer, which is, by far, the largest type on our SQLite encoder team, but also one of the simplest.
private struct _KeyedContainer<K: CodingKey>: KeyedEncodingContainerProtocol {
typealias Key = K
let codingPath: Array<CodingKey> = []
private var _storage: _KeyedStorage
init(_ storage: _KeyedStorage) {
_storage = storage
}
mutating func encodeNil(forKey key: K) throws {
_storage[key.stringValue] = .null
}
mutating func encode(_ value: Bool, forKey key: K) throws {
_storage[key.stringValue] = .integer(value ? 1 : 0)
}
mutating func encode(_ value: Int, forKey key: K) throws {
_storage[key.stringValue] = .integer(Int64(value))
}
/**
...a bunch of other variations of Int and Double, such as UInt, Int8, Float, etc....
*/
mutating func encode(_ value: Double, forKey key: K) throws {
_storage[key.stringValue] = .double(value)
}
mutating func encode(_ value: String, forKey key: K) throws {
_storage[key.stringValue] = .text(value)
}
mutating func encode(_ value: Data, forKey key: K) throws {
_storage[key.stringValue] = .data(value)
}
mutating func encode(_ value: Date, forKey key: K) throws {
let string = SQLite.DateFormatter.string(from: value)
_storage[key.stringValue] = .text(string)
}
mutating func encode(_ value: URL, forKey key: K) throws {
_storage[key.stringValue] = .text(value.absoluteString)
}
mutating func encode(_ value: UUID, forKey key: K) throws {
_storage[key.stringValue] = .text(value.uuidString)
}
mutating func encode<T>(_ value: T, forKey key: K) throws where T : Encodable {
if let data = value as? Data {
try encode(data, forKey: key)
} else if let date = value as? Date {
try encode(date, forKey: key)
} else if let url = value as? URL {
try encode(url, forKey: key)
} else if let uuid = value as? UUID {
try encode(uuid, forKey: key)
} else {
let jsonData = try jsonEncoder.encode(value)
guard let jsonText = String(data: jsonData, encoding: .utf8) else {
throw SQLite.Encoder.Error.invalidJSON(jsonData)
}
_storage[key.stringValue] = .text(jsonText)
}
}
mutating func nestedContainer<NestedKey>(keyedBy keyType: NestedKey.Type, forKey key: K) -> KeyedEncodingContainer<NestedKey> where NestedKey : CodingKey {
fatalError("_KeyedContainer does not support nested containers.")
}
mutating func nestedUnkeyedContainer(forKey key: K) -> UnkeyedEncodingContainer {
fatalError("_KeyedContainer does not support nested containers.")
}
mutating func superEncoder() -> Encoder {
fatalError("_KeyedContainer does not support nested containers.")
}
mutating func superEncoder(forKey key: K) -> Encoder {
fatalError("_KeyedContainer does not support nested containers.")
}
}
As we can see, the purpose of _KeyedContainer is to take the raw, encodable Swift types and convert them into our SQLite.Value type, which is the type that SQLite.Database understands. The majority of the Swift types are direct conversions. For example, all of the variations of Swift’s Int are converted directly into SQLite.Value.integer. We have to do some smarter conversions for more advanced types like Date, URL, or UUID, but even those conversions are pretty straightforward. The most complicated conversion we have to do is when we encounter a type for which we haven’t created an explicit conversion. This could be something like a Dictionary<String, String> or a completely custom type (e.g., an array of Subtask). In this case, we use Swift’s JSONEncoder to encode the type into Data, and then we store the UTF-8 textual representation of that JSON. The reason we store JSON text instead of JSON data is that this allows us to use SQLite’s JSON extension to access and modify it, if we wanted.
Now that we’ve looked at all of the private types that power our SQLite encoder, let’s take a look at the public SQLite.Encoder itself.
public final class Encoder {
public enum Error: Swift.Error {
case invalidType(Any)
case invalidValue(Any)
case invalidJSON(Data)
case transactionFailed
}
private let _database: SQLite.Database
public init(_ database: SQLite.Database) {
_database = database
}
public func encode<T: Encodable>(_ value: T, using sql: SQL) throws {
let encoder = _SQLiteEncoder()
if let array = value as? Array<Encodable> {
let success = try _database.inTransaction {
try array.forEach { (element: Encodable) in
try element.encode(to: encoder)
try _database.write(sql, arguments: encoder.encodedArguments)
}
}
if success == false {
throw SQLite.Encoder.Error.transactionFailed
}
} else if let dictionary = value as? Dictionary<AnyHashable, Encodable> {
throw SQLite.Encoder.Error.invalidType(dictionary)
} else {
try value.encode(to: encoder)
try _database.write(sql, arguments: encoder.encodedArguments)
}
}
}
The public class on our SQLite encoder team is very small and simple. It really doesn’t have much to do. Its main responsibility is creating an instance of _SQLiteEncoder, passing that instance to the values to be encoded, and then writing the encoded values to the database using the provided SQL statement. In order to be as flexible as possible, SQLite.Encoder.encode(_:using:) accepts single entities and an array of entities. So, our encoder is also responsible for deciding whether the value passed into it is a single Encodable entity or an array of entities. If the value is an array, then the encode method iterates over the array and passes each one to the instance of _SQLiteEncoder it created. It rethrows any errors that happen during any of the steps of the encoding process.
And, that’s it. Although writing the SQLite encoder required writing a couple hundred lines of code, the majority of the code was very simple. The result is a flexible encoder that can work with nearly any custom type.
SQLite.Decoder
Writing a decoder is very similar to writing an encoder. If anything, it’s easier to implement a decoder than an encoder. As with the encoder, the best way to understand how SQLite.Decoder works is to first review how decoding works in Swift. Again, the Swift compiler is able to automatically synthesize an implementation of init(from decoder: Decoder) for our simple Task, but let’s implement it ourselves.
extension Task: Decodable {
init(from decoder: Decoder) throws {
let container = try decoder.container(keyedBy: Task.CodingKeys.self)
self.id = try container.decode(String.self, forKey: .id)
self.title = try container.decode(String.self, forKey: .title)
self.dueDate = try container.decodeIfPresent(Date.self, forKey: .dueDate)
self.isCompleted = try container.decode(Bool.self, forKey: .isCompleted)
}
}
As is clear from the above, implementing the Decodable protocol is very similar to implementing the Encodable protocol. We first need to declare conformance to Decodable and implement init(from decoder: Decoder) throws. Inside of this method, we first ask the decoder for a container that holds the encoded values of the properties of our Task. The decoder is something that conforms to the Decoder protocol. The key-value container is very similar to the one that powers the encoder. Swift’s documentation gives its type as KeyedDecodingContainer<Key> where Key : CodingKey. Again, similar to the encoding stuff, the KeyedDecodingContainer is an actual struct, not a protocol. Its initializer has the signature init<Container>(_ container: Container) where K == Container.Key, Container : KeyedDecodingContainerProtocol, which means the KeyedDecodingContainer is a wrapper around something that is generic on the Task.CodingKeys Key type and conforms to the KeyedDecodingContainerProtocol protocol. After we get the key-value container from the decoder, we ask it to decode a value for each one of the properties of our Task. We specify the type we need the values to be decoded to. We immediately set those decoded values to the properties of our task.
So, again similar to the encoder, it’s clear that our SQLite.Decoder will need to implement a decoder that conforms to the Decoder protocol and a container that conforms to KeyedDecodingContainerProtocol. The decoder and container are both private types hidden behind the public SQLite.Decoder interface. Let’s start with the decoder.
private class _SQLiteDecoder: Decoder {
var codingPath: Array<CodingKey> = []
var userInfo: Dictionary<CodingUserInfoKey, Any> = [:]
var row: SQLiteRow?
init(_ row: SQLiteRow? = nil) {
self.row = row
}
func container<Key>(keyedBy type: Key.Type) throws -> KeyedDecodingContainer<Key> where Key : CodingKey {
guard let row = self.row else { fatalError() }
return KeyedDecodingContainer(_KeyedContainer<Key>(row))
}
func unkeyedContainer() throws -> UnkeyedDecodingContainer {
fatalError("SQLiteDecoder doesn't support unkeyed decoding")
}
func singleValueContainer() throws -> SingleValueDecodingContainer {
fatalError("SQLiteDecoder doesn't support single value decoding")
}
}
Similar to _SQLiteEncoder, _SQLiteDecoder is mostly boilerplate code. It’s modeled after Swift’s _JSONDecoder, but it’s more limited than the JSON version because it only supports keyed decoding. When asked for a key-value container, _SQLiteDecoder creates and returns a new KeyedDecodingContainer that wraps our private _KeyedContainer, which takes a copy of the row-to-be-decoded. SQLiteRow is just a typealias for Dictionary<String, SQLite.Value>. It contains the column names and SQLite values for a single row fetched from the SQLite database. The decoder’s _KeyedContainer is very similar to the encoder’s _KeyedContainer.
private class _KeyedContainer<K: CodingKey>: KeyedDecodingContainerProtocol {
typealias Key = K
let codingPath: Array<CodingKey> = []
var allKeys: Array<K> { return _row.keys.compactMap { K(stringValue: $0) } }
private var _row: SQLiteRow
init(_ row: SQLiteRow) {
_row = row
}
func contains(_ key: K) -> Bool {
return _row[key.stringValue] != nil
}
func decodeNil(forKey key: K) throws -> Bool {
guard let value = _row[key.stringValue] else {
throw SQLite.Decoder.Error.missingValueForKey(key.stringValue)
}
if case .null = value {
return true
} else {
return false
}
}
func decode(_ type: Bool.Type, forKey key: K) throws -> Bool {
guard let value = _row[key.stringValue]?.boolValue else {
throw SQLite.Decoder.Error.missingValueForKey(key.stringValue)
}
return value
}
func decode(_ type: Int.Type, forKey key: K) throws -> Int {
guard let value = _row[key.stringValue]?.intValue else {
throw SQLite.Decoder.Error.missingValueForKey(key.stringValue)
}
return value
}
/**
...a bunch of other variations of Int and Double, such as UInt, Int8, Float, etc....
*/
func decode(_ type: Double.Type, forKey key: K) throws -> Double {
guard let value = _row[key.stringValue]?.doubleValue else {
throw SQLite.Decoder.Error.missingValueForKey(key.stringValue)
}
return value
}
func decode(_ type: String.Type, forKey key: K) throws -> String {
guard let value = _row[key.stringValue]?.stringValue else {
throw SQLite.Decoder.Error.missingValueForKey(key.stringValue)
}
return value
}
func decode(_ type: Data.Type, forKey key: K) throws -> Data {
guard let value = _row[key.stringValue]?.dataValue else {
throw SQLite.Decoder.Error.missingValueForKey(key.stringValue)
}
return value
}
func decode(_ type: Date.Type, forKey key: K) throws -> Date {
let string = try decode(String.self, forKey: key)
if let date = SQLite.DateFormatter.date(from: string) {
return date
} else {
throw SQLite.Decoder.Error.invalidDate(string)
}
}
func decode(_ type: URL.Type, forKey key: K) throws -> URL {
let string = try decode(String.self, forKey: key)
if let url = URL(string: string) {
return url
} else {
throw SQLite.Decoder.Error.invalidURL(string)
}
}
func decode(_ type: UUID.Type, forKey key: K) throws -> UUID {
let string = try decode(String.self, forKey: key)
if let uuid = UUID(uuidString: string) {
return uuid
} else {
throw SQLite.Decoder.Error.invalidUUID(string)
}
}
func decode<T>(_ type: T.Type, forKey key: K) throws -> T where T: Decodable {
if Data.self == T.self {
return try decode(Data.self, forKey: key) as! T
} else if Date.self == T.self {
return try decode(Date.self, forKey: key) as! T
} else if URL.self == T.self {
return try decode(URL.self, forKey: key) as! T
} else if UUID.self == T.self {
return try decode(UUID.self, forKey: key) as! T
} else {
let jsonText = try decode(String.self, forKey: key)
guard let jsonData = jsonText.data(using: .utf8) else {
throw SQLite.Decoder.Error.invalidJSON(jsonText)
}
return try jsonDecoder.decode(T.self, from: jsonData)
}
}
func nestedContainer<NestedKey>(keyedBy type: NestedKey.Type, forKey key: K) throws -> KeyedDecodingContainer<NestedKey> where NestedKey : CodingKey {
fatalError("_KeyedContainer does not support nested containers.")
}
func nestedUnkeyedContainer(forKey key: K) throws -> UnkeyedDecodingContainer {
fatalError("_KeyedContainer does not support nested containers.")
}
func superDecoder() throws -> Decoder {
fatalError("_KeyedContainer does not support nested containers.")
}
func superDecoder(forKey key: K) throws -> Decoder {
fatalError("_KeyedContainer does not support nested containers.")
}
}
The purpose of the decoder’s _KeyedContainer is to take the raw SQLite types fetched from the database and convert them into native Swift types, which we will then use to initialize our Task. Just like with the encoder’s keyed container, most of the conversions are direct—for example, from SQLite.Value.integer to Int. Again, mirroring the encoder’s keyed container, we have to do some smarter conversions for more advanced types like Date, URL, or UUID, but they are still relatively straightforward. Remembering back to the encoder, completely custom types (e.g., Subtask) are encoded using Swift’s JSON encoder before being saved to the database. Therefore, here, we need to decode them using Swift’s JSON decoder. Overall, the decoder’s _KeyedContainer is a simple, albeit very long, class.
All that’s left is to look at the public SQLite.Decoder itself.
public final class Decoder {
public enum Error: Swift.Error {
case incorrectNumberOfResults(Int)
case missingValueForKey(String)
case invalidDate(String)
case invalidURL(String)
case invalidUUID(String)
case invalidJSON(String)
}
private let _database: SQLite.Database
public init(_ database: SQLite.Database) {
_database = database
}
public func decode<T: Decodable>(_ type: T.Type, using sql: SQL,
arguments: SQLiteArguments = [:]) throws -> T? {
let results: Array<T> = try self.decode(Array<T>.self, using: sql, arguments: arguments)
guard results.count == 0 || results.count == 1 else {
throw SQLite.Decoder.Error.incorrectNumberOfResults(results.count)
}
return results.first
}
public func decode<T: Decodable>(_ type: Array<T>.Type, using sql: SQL,
arguments: SQLiteArguments = [:]) throws -> Array<T> {
let results: Array<SQLiteRow> = try _database.read(sql, arguments: arguments)
let decoder = _SQLiteDecoder()
return try results.map { (row: SQLiteRow) in
decoder.row = row
return try T.init(from: decoder)
}
}
}
The only public SQLite decoder class is quite straightforward. We expose two methods (in addition to the initializer) publicly. One is for decoding a single instance of one of our types. This is meant to be used when selecting a single item from the database. Our Task.fetchByID SQL statement does exactly this. The other method is for decoding multiple instances of one of our types. Our Task.fetchAll SQL statement is a good example of this. Regardless of which method is called, the same code path is followed. First, we fetch from the database using the passed-in SQL statement. Then, we initialize an instance of _SQLiteDecoder. Finally, for each of the database results, we set the decoder’s row to the current database result and then call the Decodable initializer init(from decoder: Decoder), passing in our decoder. The result is an instance of our custom type, which we then collect in an array and pass back to the caller. The first of the two decode methods checks to make sure that there is either zero or one item in the array and then passes that item (or nil) back to the caller.
Going forward
Adding SQLite.Encoder and SQLite.Decoder to our apps allows us to benefit from the hard work the Swift language team has put into building Codable. Importantly, this dramatically cuts down the amount of error-prone boilerplate persistence code we need to write. Additionally, this makes our code more flexible because it allows us to easily swap out our SQLite persistence engine for another technology that also supports Swift’s Codable, if the need arises. Check out the code on Github. I’ve also created a Swift Playgound that includes all of the code in this post. Download it from Github.