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

Gwendal Roué
6 min readJul 27, 2016

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 place = try Place.fetchOne(db, key: 1) {
place.isFavorite = true
try place.update(db)
}

Any Swift struct or class can become a Record:

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

extension Place : FetchableRecord { ... }
extension Place : PersistableRecord { ... }

let places = try Place.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 FetchableRecord 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 such as ValueObservation.

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 the GRDB demo applications.

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

try dbQueue.read { db in
// Safely use the database connection
if let place = try Place.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.write { db in
let model1 = try 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)
return .commit
}
}
}

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.write { 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.write { 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
// )
struct Player {
var username: String // matches the username column
var creationDate: Date // matches the creationDate column
init(row: Row) {
username = row["username"]
creationDate = row["creationDate"]
}
}

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 = try PointOfInterest.order(...).fetchAll(db)
let count = try PointOfInterest.filter(...).fetchCount(db)
let poisController = FetchedRecordsController<PointOfInterest>(
dbQueue, request: ...)

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

let pois = try PointOfInterest.fetchAll(db, "SELECT ...")
let count = try 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.

Conclusion

Started in 2015, 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.

--

--