Video #302: SQLite: GRDB

Episode: Video #302 Date: Nov 11, 2024 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep302-sqlite-grdb

Description

Interfacing with SQLite’s C library from Swift is possible, but clunky. Luckily there are friendlier, “Swiftier” interfaces the community has built, so let’s take a look at the most popular: GRDB. We’ll explore how it can help us avoid pitfalls and boilerplate required to use the C library, and how its typed SQL helpers can even help us avoid runtime issues at compile time.

Video

Cloudflare Stream video ID: d73451d177efbed5aef00b459635fa05 Local file: video_302_sqlite-grdb.mp4 *(download with --video 302)*

References

Transcript

— 0:05

And so that is the basics of interacting with the SQLite C library from Swift. We now know how to create a database connection, create tables, insert data into tables, and query tables for data. And really this was also a lesson in how one interacts with C libraries from Swift, in general. Long story short: it ain’t pretty. But it is also amazing that it is possible at all. Brandon

— 0:27

And because of the pain we witnessed when trying to interact with a C library, it’s no surprise that many people have tried to put a friendlier and “Swiftier” interface over SQLite. There are a number of libraries out there, but no doubt the most popular is

GRDB 0:58

So, let’s import GRDB into our project and see what it takes to create database connections, create tables, insert data into tables, and query tables. And we will also show off a couple of advanced features too, such as subscriptions and relations.

GRDB 1:16

Let’s start by trying to import the GRDB library: import GRDB No such module ‘GRDB’

GRDB 1:20

This of course causes a compilation error because we do not have this dependency added to our project. But we can do so easily using Xcode…

GRDB 1:30

At the time of recording this episode the most current major version is 6.0 , but there is a beta for 7.0 that brings compatibility with the Swift 6 language mode. In order to be as future forward as possible we will depend on the latest beta, which is v7.0.0-beta.6 :

GRDB 2:24

And while we’re here, let’s go ahead and put our project in Swift 6 language mode too so that we can catch concurrency problems as early as possible. Creating a database

GRDB 2:58

Next let’s try to accomplish everything we did with the raw SQLite C library, but using the GRDB library instead. It provides a nice Swift interface to basically everything SQLite has to offer, and even a bit extra.

GRDB 3:18

First let’s move all of our direct SQLite work down to a function so that we can keep that code around but not execute it: func sqlite3() { … }

GRDB 3:36

Now let’s see what it takes to connect to an existing SQLite database, or create one if one does not already exist. We will do this in the init of the entry point of the app: init() { let databasePath = URL.documentsDirectory .appending(path: "db.sqlite") .path() let databaseQueue = DatabaseQueue(path: databasePath) } Call can throw, but it is not marked with ‘try’ and the error is not handled

GRDB 4:13

Right off the bat we see a few things in stark contrast with the C library. First we are creating a first class object to represent the database connection, and it is using argument labels to describe what is required to create it. And second it is leveraging Swift’s error throwing capabilities instead of forcing us to check the status returned from every sqlite3_ function.

GRDB 5:08

So, let’s wrap everything in a do { } catch { } , and for now we will just fatalError any errors thrown: do { let databasePath = URL .documentsDirectory .appending(path: "db.sqlite").path() let databaseQueue = try DatabaseQueue(path: databasePath) } catch { fatalError(error.localizedDescription) }

GRDB 5:28

A DatabaseQueue is an abstraction over the opaque pointer we were juggling earlier when we were dealing with the C library. In fact we can prove that to our selves by command-clicking on the various types to go deeper and deeper into the library.

GRDB 5:48

For example, if we command-click on DatabaseQueue we are taken to its file where we see it holds onto something called a SerializedDatabase : private let writer: SerializedDatabase

GRDB 5:54

And if we command-click on that we will be taken to a file where we see that it holds onto something called a Database : private let db: Database

GRDB 5:58

And if we further command-click on that we are taken to a file to see that it holds onto something called a SQLiteConnection : public private(set) var sqliteConnection: SQLiteConnection?

GRDB 6:02

And finally if we command-click on that type we will see that it is merely a type alias of an OpaquePointer : public typealias SQLiteConnection = OpaquePointer

GRDB 6:06

…which is exactly what we used for our database connection when interfacing with the C library directly.

GRDB 6:15

Backing up to our app entry point, we now have an object that can be used to interact with the database, and it is called DatabaseQueue because it gives serialized access to the database. This means that reads and writes to the database will be blocking and will not be allowed to be interleave with each other.

GRDB 6:35

GRDB provides another abstraction for when you want to parallelize multiple reads from the database while still serializing writes, and it’s called a DatabasePool : let databasePool = try DatabasePool(path: databasePath)

GRDB 6:50

Both of these objects are unified by DatabaseReader and DatabaseWriter protocols, and so technically we can deal with these things abstractly rather than concretely. But we don’t need any of that power right now so let’s go back to just a queue: let databaseQueue = try DatabaseQueue(path: databasePath)

GRDB 7:22

Alright, so that one single line takes care of creating the database if necessary and connecting to it, compared to this when interacting with the C library directly: var db: OpaquePointer? guard sqlite3_open_v2( databasePath, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nil ) == SQLITE_OK else { fatalError("Could not open database at \(databasePath).") }

GRDB 7:41

Not bad! Creating a table

GRDB 7:42

So already we are seeing big improvements to using GRDB over the raw C library for interacting with SQLite. Stephen

GRDB 7:50

But things get better. Let’s see what it takes to create a table in the database with GRDB. Not only do we get a nicer Swift API to this functionality, with all the benefits of static types and autocomplete, but there is also a tool known as “migrations” that allow you to incrementally apply changes to your database schema when your app first starts up.

GRDB 8:12

Let’s create a table for our players. We’ll start by deleting the one that was already created in our database: $ rm …/Containers/Data/Application/…/Documents/db.sqlite

GRDB 8:20

To make any changes to a database you will first open up a “write” closure like so: try databaseQueue.write { db in <#code#> }

GRDB 8:37

This provides a new lexical scope to execute any writes you want to the database, and a transaction is acquired for the duration of the lexical scope. Transactions are a way of grouping multiple SQL statements into a single statement that is executed at once, atomically. All statements executed by SQLite are done so in a transaction, whether done so explicitly by the user of the database, or implicitly by SQLite if no transaction exists.

GRDB 9:01

Transactions are important, but the way they are designed in SQLite, and pretty much every database out there, can be quite dangerous. Using the bare bones tools that SQLite gives us for transactions, one would typically start and commit a transaction like so: try db.beginTransaction() // Execute reads and writes try db.commit()

GRDB 9:28

However, this leaves us open to the possibility of forgetting to commit the transaction at the end of our work. Of course Swift does have a nice tool to help us not forget, which is the defer : try db.beginTransaction() defer { try db.commit() } // Execute reads and writes

GRDB 9:43

But sadly this does not work because commit is throwing. So it really is on us to remember to call commit at the end of our work.

GRDB 9:50

An alternative style of API is one that embraces structured programming so that we are given an explicit lexical scope to perform work, and we are not allowed to perform the work outside that scope. This is what the write method enforces. It automatically wraps the execution of the trailing closure in beginTransaction and commit so that we do not have to.

GRDB 10:11

Swift is increasingly moving towards this style of API, as is apparent with SwiftUI’s withAnimation : withAnimation { … }

GRDB 10:18

…the new Mutex type’s withLock : let count = Mutex(0) count.withLock { print($0) }

GRDB 10:36

…and TaskLocal ’s withValue : enum Locals { @TaskLocal static var count = 0 } Locals.$count.withValue(1) { print(Locals.count) }

GRDB 11:00

This is a far safer style of API when dealing with changes to state in a local and structured way, and there are even more tools coming to Swift that will be able to make even stronger guarantees of how state is allowed to be changed in lexical scopes.

GRDB 11:13

While in the lexical scope of write you have the ability to execute SQL statements that make changes to the database: databaseQueue.write { db in db.execute(sql: <#T##SQL#>) }

GRDB 11:34

We can even just execute the CREATE TABLE statement that we crafted earlier: try databaseQueue.write { db in try db.execute( sql: """ CREATE TABLE IF NOT EXISTS players ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, name TEXT NOT NULL, createdAt DATETIME NOT NULL ) """ ) }

GRDB 11:46

That right there will work just fine, but GRDB gives us a few tools to improve this.

GRDB 11:51

First, the db value handed to the write trailing closure isn’t a regular DatabaseQueue . It’s a special type that has a family of methods that aid in executing statements in the database. For example, to create a table, if it doesn’t already exist, we can simply do: try db.create( table: "players", options: .ifNotExists ) { table in }

GRDB 12:13

This is nice because we can use autocomplete in Xcode to browse all the types of operations that can be performed on the database.

GRDB 12:25

And then this new trailing closure has a table value that exposes a family of methods that is capable of creating columns on the table: table.autoIncrementedPrimaryKey("id") table.column("name", .text).notNull() table.column("createdAt", .datetime).notNull()

GRDB 13:03

On the one hand it’s nice to have a Swift interface to some of this basic functionality of SQL. We get the benefits of auto-complete and static type types so that we don’t accidentally have a typo, like if we accidentally thought there was an

IS 13:18

But on the other hand, this is a whole new syntax one most learn and it is specific to GRDB. So, even though this syntax can be convenient, we still highly recommend that you be very familiar with how SQL syntax works in general. It will only help you wield the GRDB library more effectively, and if there ever comes a time that some SQL statement is not expressible with the tools that GRDB gives you, then you will always be able to fallback to a literal SQL query.

IS 13:40

There is another tool that can improve upon what we have done here, and that is to leverage GRDB’s migration API. This allows you to describe statements that you want to execute only a single time, and GRDB will do the work of tracking which migrations have been run and which have not.

IS 13:53

For example, right in the app entry point we can create a migrator: var migrator = DatabaseMigrator()

IS 14:01

We can then register a new migration, and we can use any label we want: migrator.registerMigration("Create 'players' table") { db in }

IS 14:17

And then in this trailing closure we can execute any statements we want on the database. But this time we can even drop the .ifNotExists constraint because we now know this statement will only be executed a single time: try database.create(table: "players") { table in table.autoIncrementedPrimaryKey("id") table.column("name", .text).notNull() table.column("createdAt", .datetime).notNull() }

IS 14:28

And then finally we tell the migrator to migrate the database: try migrator.migrate(databaseQueue)

IS 14:39

And run the app again. We will see that not only was the players table recreated, but a new grdb_migrations table was also created with a single row: "Create 'players' table"

IS 15:07

That is how GRDB keeps track of which migrations have run so that it knows when to skip a migration or when to run it.

IS 15:13

Then, if later we decide to add more tables or make change to an existing table, we can just register a new migration. For example, say we want to add an isInjured boolean to the players table: migrator.registerMigration( "Add 'isInjured' to 'players'" ) { database in try database.alter(table: "players") { table in table.add(column: "isInured", .boolean) .defaults(to: false) } }

IS 15:48

Now when we run the app we will see the migrations table has two rows: "Add 'isInjured' to 'players'" "Create 'players' table"

IS 16:02

…and the players table has an isInured column. I actually have a typo in my property. And so I would like to fix it: table.add(column: "isInjured", .boolean).defaults(to: false)

IS 16:15

…but because this migration has already been run it is not going to be run if I launch the app again. I would need to go manually update the table in the database, or I could delete the whole database and start fresh.

IS 16:24

But, there is a nice feature of GRDB that detects changes like this to the migration and will automatically blow away the entire database so that you can start fresh: #if DEBUG migrator.eraseDatabaseOnSchemaChange = true #endif

IS 16:46

And now when we run the app we will see the table has the correctly named column. And so that is really nice. Inserting data into a table Stephen

IS 17:42

So now we see that creating a database schema is quite a bit easier in GRDB thanks to a host of helpers for creating tables and columns, and the migration system so that you can incrementally make schema changes when your app first boots up. Brandon

IS 17:53

Let’s move onto the next bit of functionality we explored with SQLite, which is inserting data into the table. This too is much, much easier with GRDB.

IS 18:04

Just as we saw when creating a table with GRDB, writes to the database begin with the write method on DatabaseQueue : try databaseQueue.write { db in }

IS 18:29

…which is handed Database object that can execute actual statements.

IS 18:34

In particular, there is an execute method that takes a SQL statement as well as arguments that are inserted into the prepared SQL statement: db.execute(sql: <#String#>, arguments: <#StatementArguments#>)

IS 19:01

This allows you to do something like this: try db.execute( sql: """ INSERT INTO "players" ("name", "createdAt")

VALUES 21:03

But things get even better. GRDB also supports custom string interpolation that allows one to write their SQL statements in the easy style that seems to be dangerous: let name = "Blob" let createdAt = Date() try db.execute( literal: """ INSERT INTO "players" ("name", "createdAt")

VALUES 21:40

…yet this actually does the right thing. Because string interpolation in Swift is a customizable and overridable thing, GRDB gets to bind these values instead of naively inserting the raw value directly into the statement.

VALUES 21:54

That means we can even do something that should create an invalid SQL statement: let name = "Blob;"

VALUES 22:25

…and this will still work properly. The semicolon will be handled properly by the prepared statement.

VALUES 22:57

So that seems pretty great. We can now go back to writing our SQL statements in the style that seemed very natural at first when interfacing directly with the C library, but now everything is safe.

VALUES 23:05

But things get even better. GRDB also provides the tools to allow to create first class Swift data types that represent the rows in a table. And then it gives us all types of functions for free that allow one to insert, update or delete rows from a value of that data type.

VALUES 23:23

So far we have yet to define an actual data type that represents the player data we are storing in the database. It would have been incredibly cumbersome to map the properties of a Player struct to the columns in a table, but with GRDB it is nearly as easy as defining the struct just as we normally would: import Foundation struct Player { var id: Int64 var name = "" var createdAt: Date var isInjured = false }

VALUES 24:12

There is just a little more work we need to do. There are a few protocols in GRDB that express various kinds of “record” types, which endow a type with helpers for associating a Swift data type with tables in a SQLite database.

VALUES 24:25

The most fundamental one is TableRecord : import GRDB struct Player: TableRecord { … }

VALUES 24:35

Technically this already compiles because all of the requirements of the protocol have default implementations.

VALUES 24:41

However, there is one requirement that we want to implement explicitly, and that is the databaseTableName requirement: struct Player: TableRecord { static let databaseTableName = "" … } This a string of the table name that represents this data type, and by default it essentially lower cases the name of the type: static let databaseTableName = "player"

VALUES 24:58

However, we decided to name our table “players”. Now that is just a stylistic choice we made. Over many years of developing with various database systems, we have grown accustomed to pluralized table names, and its hard to teach an old dog new tricks.

VALUES 25:16

But our choice isn’t the universal correct one, and you may prefer singular table names in which case you do not need to supply this static property. But we will need to do that: static let databaseTableName = "players"

VALUES 25:46

So, we have no conformed to this protocol, but it doesn’t actual unlock much functionality for us. In order to be able to turn a value of the Player type into a SQL query for inserting and deleting rows in a table, we need it to further conform to the EncodableRecord protocol: struct Player: EncodableRecord, TableRecord { … }

VALUES 26:04

And in order to satisfy this protocol we need our type to be Encodable : struct Player: Encodable, EncodableRecord, TableRecord { … }

VALUES 26:11

Now that compiles, but it still doesn’t give us much functionality that is useful right this moment. Under the hood it is very useful, but we still just want to be able to insert and delete rows from having a value of the Player type.

VALUES 26:25

So we further need to conform to the MutablePersistableRecord : struct Player: Encodable, EncodableRecord, MutablePersistableRecord, TableRecord { … }

VALUES 26:32

And that already compiles, and it does give us some really useful tools right away. We can construct a Player value and insert it right into a database with very little work.

VALUES 26:37

We start by creating a value: let player = Player(id: 42, name: "Blob", createdAt: Date())

VALUES 27:07

And because Player conforms to MutablePersistableRecord we get an inserted method: try player.inserted(db)

VALUES 27:11

This inserts the data for the player into the “players” table and then returns the newly inserted player: print(try player.inserted(db))

VALUES 27:24

If we run the app we will that the player is inserted into the database and prints to the console: Player(id: 42, name: "Blob", createdAt: 2024-10-29 12:39:51 +0000, isInjured: false)

VALUES 27:47

GRDB has written and executed a query for us that we previously had to write from scratch in SQL. To gain insight into the query that it wrote, let’s configure our database connection with some logging. var config = Configuration() config.prepareDatabase { $0.trace { print($0) } } let databaseQueue = try DatabaseQueue( path: databasePath, configuration: config )

VALUES 28:41

And if we run things, we see a whole bunch of SQL statements printed to the console, but finally at the end we see the insert: INSERT INTO "players" ("id", "name", "createdAt", "isInjured") VALUES (?,?,?,?)

VALUES 29:40

But, we’ve also hit a crash. The problem is the API we are using currently forces us to provide an explicit ID for the player, and if we accidentally reuse an ID, like we just did, then SQLite will emit an error, cause GRDB to throw a Swift error, and currently we are doing a fatalError when that happens: Fatal error: SQLite error 19: UNIQUE constraint failed: players.id - while executing INSERT INTO “players” (“id”, “name”, “createdAt”, “isInjured”) VALUES (?,?,?,?) This is happening because we tried to insert a row with an id of 42 but we already previously did that. And when we described our “players” table we told SQLite that the id column was unique, and so it prohibits inserting this new record.

VALUES 30:06

But also, when we described the “id” column we also said to use an “auto-incrementing” primary key: table.autoIncrementedPrimaryKey("id")

VALUES 30:14

This means if we leave off the “id” column entirely from the INSERT INTO statement then SQLite will look up the next available ID for the table and use that one. But how can we represent that with our data type?

VALUES 30:27

The easiest way would be to make the id optional in the Player : var id: Int64?

VALUES 30:32

And that allows us to leave off the ID if it’s an un-persisted value: Player(name: "Blob", createdAt: Date())

VALUES 30:43

And now when we run the app we will see that no error is thrown, and we can check the database to see that a new player was inserted into the table with an ID of 43. That means it did the work for us to find the last inserted ID, and used 1 more than that ID.

VALUES 31:08

So this is promising, but there is still something strange here. The player we print to the console has a nil ID: Player(id: nil, name: "Blob", createdAt: 2024-10-29 13:17:39 +0000, isInjured: false)

VALUES 31:19

We would hope that after you insert the player into the database, the player value we get back would have its new ID assigned. It would be really handy to have this because you may want to do something with that player immediately after inserting it. You may want to track some analytics and use the ID as a property on the event, or you may want to create other database rows that reference the ID of the player.

VALUES 31:45

In order to make sure the inserted player value has its new ID assigned we need to make use of a feature of MutablePersistableRecord known as “callbacks”. These are methods on the record type that are invoked at the very stages of inserting or updating data in the database.

VALUES 31:55

In particular, there is a method called didInsert : struct Player: Encodable, EncodableRecord, MutablePersistableRecord, TableRecord { … mutating func didInsert(_ inserted: InsertionSuccess) { } } …that is called right after the row is inserted. Its a mutating method so that we can make further changes to the player after insertion, and it is handed an InsertionSuccess value, which holds some information about the insert. In particular, it holds what is called the “row ID”, which is the integer primary key for the table.

VALUES 32:30

So, we will use this method and the rowID to immediate update our player value upon insertion: mutating func didInsert(_ inserted: InsertionSuccess) { id = inserted.rowID }

VALUES 32:37

And with those few changes, when we run the app we will now see the correct thing printed to the console: Player(id: Optional(46), name: "Blob", createdAt: 2024-10-29 13:25:37 +0000, isInjured: false)

VALUES 32:53

And while we’re here, let’s also show how easy it is to update a player in the database. We can simply make a mutation to the player value we have locally, and then invoke the update method: var insertedPlayer = try player.inserted(db) insertedPlayer.name += " Jr." try insertedPlayer.update(db)

VALUES 33:22

And that right there will update the newly inserted player by turning its name into “Blob Jr.”. Next time: Fetching data

VALUES 34:38

We have now nearly recreated all the functionality we had when interacting directly with the SQLite C library, but this time using GRDB. We can create database connections, create tables, and insert data into the tables. And each step of the way we saw how GRDB provided a nice interface to this functionality, and allowed us to use many of Swift’s powerful features, such as value types and Swift’s Encodable protocol for automatically inserting a first class Swift data type into the database. Stephen

VALUES 35:08

The only functionality that we haven’t recreated yet is querying the database to get players. This was quite cumbersome when calling the C library directly, so let’s see what GRDB gives us for this, and along the way we will see what it takes to start showing data from our database in a SwiftUI view…next time! References