How to build an iOS application with SQLite and GRDB.swift

Or how Swift protocols and immutability impact database accesses.

GRDB.swift is a Swift application toolkit that provides access to SQLite databases. This article gives general information and best practices, so that you can turn GRDB into your best friend.

Records

GRDB grants you with raw access to the SQLite database, with SQL queries, database rows and columns. But you can also use Records, that help manipulating database rows as regular objects:

if let poi = PointOfInterest.fetchOne(db, key: 1) {
poi.isFavorite = true
try poi.update(db)
}

Any Swift struct or class can become a Record:

struct PointOfInterest {
var id: Int64?
var title: String
var isFavorite: Bool
var coordinate: CLLocationCoordinate2D
}

extension PointOfInterest : RowConvertible { ... }
extension PointOfInterest : Persistable { ... }

let pois = PointOfInterest.fetchAll(db)

Records are not uniqued, and do not auto-update.

This means that two subsequent fetches will return different and independent record instances, and that records won’t automatically update their properties when the database changes.

Why is that so?

  • GRDB is protocol-oriented, and fetching methods are proper to the RowConvertible protocol. Custom structs can be fetched as well, and structs cannot be uniqued by definition.
  • Records are immutable (at least GRDB won’t mutate them on your back), and this makes them trivial to share between various threads.

This makes GRDB quite unlike other Swift ORMs that use class inheritance and make heavy use of objects mutability, like Realm and Core Data. Both of them provide uniquing and auto-updating records.

This architecture has served them very well. Particularly, Core Data is really well suited for macOS development, which makes heavy use of bindings and key-value coding/observing.

But Core Data is difficult to use properly in a multi-threaded application, and Realm suffers from the same uneasy crossing of thread boundaries. Both generally lack a single source of truth. And who likes those pesky changes notifications that come at the wrong time?

Well, iOS has no bindings, Swift doesn’t like KVC/O, and immutability and protocol-oriented programming were missing their database toolkit.

It can help thinking of GRDB as a server-inspired tool for applications. Since records are not uniqued, and won’t mutate behind your back, you can architecture your application around task-oriented and independent data flows. Have them use short-lived memory objects. Use a single source of truth: the database.

Have your controllers fetch data when they need fresh information, and write to the database whatever has to be shared with other controllers.

Synchronization points are eventually unavoidable: but your application knows better than GRDB what should be synchronized, when, and how.

This task does not have to be more difficult that your ordinary juggling with Grand Central Dispatch. And you can use handy database observation tools like FetchedRecordsController and transaction observers.

Let Controllers Control

In the “fat controller” vs. “fat model” debate, GRDB is on the side of fat controllers.

We have seen above that models are not uniqued, and do not auto-update. This means that models encapsulate a partial view of the database, both in space and time. They are not nodes in a consistent graph of objects. They don’t have the slightest idea of the application as a whole.

Only controllers have the big picture. And particularly: only controllers know about the best scope for database transactions (the basic unit for changes that should be stored on disk as a whole), their content, and timing.

To foster this architecture, GRDB provides two database access classes, DatabaseQueue and DatabasePool (pools use SQLite WAL mode and provide better performances).

Instantiate a single database queue or pool for each database file, and make it available to controllers.

Since most iOS apps access a single database, you can store the singleton queue/pool in a global initialized when application starts. Even if you do not like globals, make sure to instantiate a single queue or pool. For sample code, see Database.swift in the GRDBDemo app.

Queues and pools grant controllers with connections to the database, but only inside thread-safe and isolated closures:

dbQueue.inDatabase { db in
// Safely use the database connection
if let poi = PointOfInterest.fetchOne(db, key: 1) {
...
}
}

Have model methods take a database connection argument.

This is how you avoid binding your models to any specific database queue, pool, or connection, and make sure they can easily be shared across your application threads.

This is also how you give your controllers the opportunity to do their controlling job, namely group related database tasks into coherent units, protected from concurrent threads:

class MyController {
func myJob() throws {
try dbQueue.inDatabase { db in
let model1 = SomeModel.fetchOne(db, ...)
try model2.doSomething(db, with: model1, and: 42)
}
}
}

Models can still provide critical database sections:

class MoneyTransfer {
func perform(db: Database) throws {
try db.inSavepoint {
// Should an error happen in this closure,
// the whole transfer will be rollbacked.
try sourceAccount.debit(db, amount)
try destinationAccount.credit(db, amount)
}
}
}

An illustrated example

In the following schema, a controller performs its blue database task, which involves three models: m1, m2, m3. Meanwhile, some concurrent threads spawn an orange, and a green task. You can see how the database queue postpones the parallel tasks after the blue one has been completed: the controller is guaranteed to have an isolated and consistent access to the database.

// Good
class MyController {
func blue() throws {
try dbQueue.inDatabase { db in
try m1.insert(db)
try m2.doThis(db)
try m3.andThat(db)
}
}
}

Would models directly use the database queue, the controller could not isolate its blue task from concurrent threads:

// Bad
class MyController {
func blue() throws {
try dbQueue.inDatabase { db in
try m1.insert(db)
}
// <- here concurrent threads can mess with our task
try m2.doThis()
// <- here as well
try m3.andThat()
}
}

Maybe we will display funny values on the screen. Or face a relational constraint error. Worse, we can have a silent application model corruption.

In short: let controllers control, and don’t use database queues and pools from models, ever.

Trust SQLite and SQL

Trust has advantages: there is no point writing paranoid code. For example, the following snippet does not provide any error handling, and there is no Swift optional in sight, because the database provides the guarantees we need:

// CREATE TABLE users (
// username TEXT NOT NULL,
// creationDate DATETIME NOT NULL
// )
for row in Row.fetch(db, "SELECT * FROM users") {
let username = row.value(named: "username") as String
let date = row.value(named: "creationDate") as NSDate
}

Accessing untrusted databases and values is also possible: see the error handling documentation.

But consider tightening your database control, and leverage SQLite abilities (tables, indexes, foreign keys and relational constraints, collations, etc.) Your local database is not some JSON loaded from a remote server.

Next, even when you don’t see it, SQL is never far:

let pois = PointOfInterest.order(...).fetchAll(db)
let count = PointOfInterest.filter(...).fetchCount(db)
let poisController = FetchedRecordsController<PointOfInterest>(
dbQueue, request: ...)

SQL is indeed so close that you can always write your own:

let pois = PointOfInterest.fetchAll(db, "SELECT ...")
let count = Int.fetchOne(db, "SELECT COUNT...")!
let poisController = FetchedRecordsController<PointOfInterest>(
dbQueue, sql: "SELECT ...")

This bottom-up principle is a core value of GRDB: showing your SQL muscles never requires heavy refactoring.

Do use SQL when it is the best tool for the job: it’s free.

Associations

Associations is the term of art for ownership relations between models. For example, a book belongs to its author, and an author has many books.

At the database level, associations are implemented as foreign keys and relational constraints:

CREATE TABLE authors (
id INTEGER PRIMARY KEY,
name TEXT
)
CREATE TABLE books (
id INTEGER PRIMARY KEY,
title TEXT,
authorId INTEGER NOT NULL REFERENCES authors(id) ON DELETE CASCADE
)

Most ORMs out there provide you with model methods that map those relations:

book.author  // Author
author.books // some collection

In its current state, GRDB does not provide any high-level methods that support associations.

Regardless of eventual future support for them, the design principles we have seen above provide some guidelines:

  • Model methods should take a database connection argument. That means that lazy Book.author and Author.books properties are an anti-pattern. The keyword here is lazy: you can of course define explicit author and books properties in your models, and store values into them.
  • Records do not auto-update (and you are free to define totally immutable record structs). A consequence is that GRDB is unwilling to address the synchronization between the author.books collection, and individual books objects. The hypothesis is that this will yield much less StackOverflow questions about the weird idiosyncrasies that plague most ORMs that attempt to deal with this hard problem. If you have already tried to address a non-trivial task that involves magical collections, you know what I’m talking about (there is today 1747 SO questions about Realm’s List, 2285 about Django ForeignKey, 4792 about Core Data relationships, 24884 about ActiveRecord has_many). Well, my goal is not to make you feel incompetent or fooled.

Let me propose below an implementation of the author to books relationship in the current state of GRDB (and come discuss it if you feel like it needs improvements):

Let’s load an author, and all her books:

Let’s load an author, and all her books, but sorted by title in a localized case insensitive way:

Now let’s load all authors, sorted by name, along with their number of books:

The last example deserves a few comments.

First, it uses raw SQL. That’s because the query interface does not yet generate this kind of query. Yet, whenever this query eventually gets generated, a single line of code will change: the line that performs the request.

Second, it uses an ad hoc type, AuthorWithBookCount. Feel free to criticize the name of this class, or the fact that it uses inheritance (both are fixable), but the sample code is shorter this way. It uses an ad hoc type because, after all, Author itself does not have to provide support for what is much likely the specific need of a unique controller in your application (like feeding table view cells).

Conclusion

After one year of development, the new GRDB.swift library has evolved into a capable application toolkit that helps developing robust applications around the rock-solid SQLite.

Its focus on Swift protocols and support for immutable types make it quite unlike traditional inheritance-based database libraries like Core Data and Realm. Swift protocols let you define precise and to-the-point model types. Immutability saves GRDB from trying to emulate a graph of objects that map the database, with all the hard-to-reproduce synchronization troubles that even experienced developers struggle with.

It fosters applications based on simple models and empowered controllers that are given the opportunity and tools to perform their controlling task.