In part 1: "Getting started with GRDB", we covered how to setup a local SQLite database for your iOS app, how to write migrations, how to adopt GRDB's protocols within a struct that can then be saved to the database, and lastly we went over some basic querying.
In part 2, we'll have a look at how to observe and react to changes in the database, how to define relationships between tables, and how to save a custom types.
Defining associations
To show you how associations work in GRDB, we'll create a tasks table and setup a "has many" relationship between the project and task tables. Tasks, in turn, will have a "belongs to" relationship with projects.
DatabaseManager.swift
class DatabaseManager {
static var migrator: DatabaseMigrator {
var migrator = DatabaseMigrator()
migrator.eraseDatabaseOnSchemaChange = true
migrator.registerMigration("createProject") { db in
try db.create(table: "project") { t in
t.autoIncrementedPrimaryKey("id")
t.column("name", .text).notNull()
t.column("description", .text)
t.column("due", .date)
t.column("isDraft", .boolean).notNull().defaults(to: true)
}
}
migrator.registerMigration("createTask") { db in
try db.create(table: "task") { t in
t.autoIncrementedPrimaryKey("id")
t.column("projectId", .integer).notNull().indexed().references("project", onDelete: .cascade)
t.column("name", .text).notNull()
t.column("isDone", .boolean).notNull().defaults(to: false)
}
}
return migrator
}
}
We went through what most of the above does in part 1; the interesting bit is line 21, where we create a column named projectId
. The column is defined as an integer (because the primary key of the project table is an integer), then constrained to be notNull
because we want SQLite to guarantee that all tasks have a project. indexed
tells SQLite that we want the column to be indexed, which'll increase performance once we start querying for tasks that belong to a project (and thus matching against the value of this column). Lastly, references
creates a foreign key constraint which tells SQLite that for each row in the tasks table, there exists a project it belongs to. onDelete: .cascade
upholds that constraint by automatically deleting all tasks that belong a project when that project is deleted from the database.
Now that the required migrations have been done, create a Task
struct for the newly created table (go back and reference part 1 if you're unsure how to do so). Once you've done that, we can move on to updating our Project
struct.
Models/Project.swift
extension Project: TableRecord, EncodableRecord {
static let tasks = hasMany(Task.self)
private enum Columns {
static let id = Column(CodingKeys.id)
static let name = Column(CodingKeys.name)
static let description = Column(CodingKeys.description)
static let due = Column(CodingKeys.due)
static let isDraft = Column(CodingKeys.isDraft)
}
var tasks: QueryInterfaceRequest<Task> {
return request(for: Project.tasks)
}
static func drafts() -> QueryInterfaceRequest<Project> {
return Project.filter(Columns.isDraft == true)
}
}
In the above snippet, the extension
that housed our queries in part 1 has been updated. First, on line 1, TableRecord
and EncodableRecord
have been adopted, which gives us access to GRDB's belongsTo
function. On line 3, that function is then used to tell GRDB of the foreign key.
Lines 13 through 15 provide us with a nice way to query all the tasks that belong to a project simply by doing:
let tasks = try project.tasks.fetchAll(db)
Similarly, we can update the Task
struct to provide us with a way to fetch the project it belongs to:
Models/Project.swift
extension Task: TableRecord, EncodableRecord {
static let project = belongsTo(Project.self)
var project: QueryInterfaceRequest<Project> {
return request(for: Task.project)
}
}
To then fetch the parent project, all that's needed is:
let project = try task.project.fetchOne(db)
Note that filters can still be applied when querying a hasMany
relationship. The following, for example, is valid:
let doneTasks = try project
.tasks
.filter(Column("isDone") == true)
.fetchAll(db)
Observing and reacting to changes
GRDB leverages SQLite's data change notifications to provide us with an efficient ValueObservation
tool that will, in turn, call either the onChange
or onError
callback it is passed.
For this example, we'll implement an observer into a simple TaskListViewController
whose view we want to update as and when the tasks that belong to a certain project update.
To start, import GRDB
and define an optional TransactionObserver
property in your controller; this is where our observer will be kept in memory. TransactionObserver
is the type returned when start
is called on a ValueObservation
.
TaskListViewController.swift
class TaskListViewController: UIViewController {
private var tasksObserver: TransactionObserver?
}
tasksObserver
now needs a value. We'll break its configuration out into its own function; remember to call configureTasksObserver
in viewDidLoad
, or wherever you're handling view setup.
TaskListViewController.swift
class TaskListViewController: UIViewController {
private func configureTasksObserver() {
let project: Project = ...
let observation = ValueObservation.tracking { db in
try project.tasks.fetchAll(db)
}
tasksObserver = observation.start(
in: dbQueue,
onError: onTasksObserverError(_:),
onChange: onTasksObserverChange(_:)
)
}
}
When start
is first called on a ValueObservation
type, the query in its definition will run once before any changes are made. This is nice because it means we don't have to duplicate the query elsewhere in our setup code to fetch the data required for the first initialization of the controller.
You can see that 2 callbacks are passed into start
on lines 12 and 13. We still need to define those:
TaskListViewController.swift
class TaskListViewController: UIViewController {
private func onTasksObserverChange(_ tasks: [Task]) {
// Update your UI, etc...
}
private func onTasksObserverError(_ error: Error) {
// Update your UI, etc...
}
}
The only thing left to do is clean-up when the controller closes:
TaskListViewController.swift
class TaskListViewController: UIViewController {
override func viewWillDisappear(_ animated: Bool) {
super.viewWillDisappear(animated)
tasksObserver = nil
}
}
That's all there is to it; GRDB does a great job of making observation straightforward.
Tip: A frequest use case for database observation is keeping the data in a UICollectionView up to date. The DeepDiff package makes a great accompaniment to what we've just covered if that's what you're implementing.
Reading and writing custom data types
GRDB supports strings, integers, dates, booleans, and enums (that adopt DatabaseValueConvertible
) out of the box. For this example, we'll contrive a requirement that projects can have a user-defined accent colour and banner image that must be modelled into a ProjectBrand
struct.
DatabaseManager.swift
class DatabaseManager {
static var migrator: DatabaseMigrator {
var migrator = DatabaseMigrator()
migrator.eraseDatabaseOnSchemaChange = true
migrator.registerMigration("createProject") { db in
try db.create(table: "project") { t in
t.autoIncrementedPrimaryKey("id")
t.column("name", .text).notNull()
t.column("description", .text)
t.column("due", .date)
t.column("isDraft", .boolean).notNull().defaults(to: true)
t.column("accentColor", .text)
t.column("bannerImage", .text)
}
}
return migrator
}
}
You can see on lines 15 and 16 that the two new fields still have to adhere to the basic types when they're in the database, and that they are stored in their own columns.
GRDB will come into play post-read, transforming the two fields into one ProjectBrand
, and pre-write, splitting the ProjectBrand
back down to two fields in preparation for SQLite. Let's implement that.
Models/Project.swift
struct Project {
var id: Int64?
var name: String
var description: String?
var due: Date?
var isDraft: Bool
var brand: ProjectBrand
}
struct ProjectBrand {
var accentColor: String?
var bannerImage: String?
}
extension Project: TableRecord, FetchableRecord, MutablePersistableRecord {
enum Columns: String, ColumnExpression {
case id, name, description, due, isDraft, accentColor, bannerImage
}
init(row: Row) {
id = row[Columns.id]
name = row[Columns.name]
description = row[Columns.description]
due = row[Columns.due]
isDraft = row[Columns.isDraft]
brand = ProjectBrand(
accentColor: row[Columns.accentColor],
bannerImage: row[Columns.bannerImage]
)
super.init(row: row)
}
func encode(to container: inout PersistenceContainer) {
container[Columns.id] = id
container[Columns.name] = name
container[Columns.description] = description
container[Columns.due] = due
container[Columns.isDraft] = isDraft
container[Columns.accentColor] = brand.accentColor
container[Columns.bannerImage] = brand.bannerImage
}
mutating func didInsert(with rowID: Int64, for column: String?) {
id = rowID
}
}
You'll see on line 7 that instead of accentColor
and bannerImage
keys in the Project
struct, we have a brand
key of type ProjectBrand
. Your struct should represent your data as you want to use it in your app, now how it is structured in the database.
The two main differences when compared to the Project
struct we wrote in part 1 are the init
and encode
functions. The former is responsible for modelling a database row into a Project
when the database is read; you can see that lines 27 through 30 transform the accentColor
and bannerImage
rows into a ProjectBrand
. The latter is responsible for splitting the Project
back down into database rows โ lines 41 and 42 break down the ProjectBrand
.
With all that in place, here's an example of what a Project
could look like in your app:
try dbQueue.write { db in
var projectBrand = ProjectBrand(
accentColor: "#005eff",
bannerImage: nil
)
var project = Project(
name: "Advanced GRDB",
description: "A blog post",
due: Date().addingTimeInterval(24 * 60 * 60),
isDraft: true,
brand: projectBrand
)
try! project.insert(db)
}
You can see that we can now call insert
directly on a Project
type even though it has a key that uses a custom data type. As we're writing to the database in this case, the afore-defined encode
function will run as part of the write process and breakdown the ProjectBrand
. Similarly, we could read a Project
and the ProjectBrand
would be pre-assembled for us by the init
function.
try dbQueue.read { db in
let project = try Project.fetchOne(db, key: 1)
print(project)
// => Project(id: nil, name: "Advanced GRDB", description: Optional("A blog post"), due: Optional(2019-12-11 14:16:15 +0000), isDraft: true, brand: ProjectBrand(accentColor: "#005eff", bannerImage: nil))
}
Wrapping up
I hope this 2 part series โ the first series I've ever published on elliotekj.com โ has helped you get to grips with SQLite on iOS, and shown you its power and ease of use when paired with GRDB.
If you have any questions, feel free to ask.
Thanks for reading.
Top comments (0)