Library / Modern Persistence / EP 323
EP 323 · Modern Persistence · May 5, 2025 ·Members

Video #323: Modern Persistence: Schemas

folder_open open_in_new
smart_display

Loading stream…

Video #323: Modern Persistence: Schemas

Episode: Video #323 Date: May 5, 2025 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep323-modern-persistence-schemas

Episode thumbnail

Description

What are the best, modern practices for persisting your application’s state? We explore the topic by rebuilding Apple’s Reminders app from scratch using SQLite, the most widely deployed database in all software. We will start by designing the schema that models our domain.

Video

Cloudflare Stream video ID: dc6db0efa8480ee55de23a9fc1d7bfc8 Local file: video_323_modern-persistence-schemas.mp4 *(download with --video 323)*

References

Transcript

— 0:05

We recently released a brand new version of our SharingGRDB library that provides a suite of tools that provide a viable alternative to using SwiftData. The tools are ergonomic, performant, and give you direct access to the SQLite database powering your app’s state without abstracting it from you. And these tools were made possible to another library that we just open sourced called StructuredQueries , which brings a type-safe and schema-safe Swift API to constructing SQL statements. Stephen

— 0:34

Now it’s time to put all of these tools to the test by building a real world, complex application from scratch that makes heavy use of all of the powers that SQL has to offer. Today we are beginning our new “Modern Persistence” series, and this is the 3rd time we’ve had a “modern” series. Previously we discussed “ Modern SwiftUI ”, where we showed by putting the upfront work in domain modeling and controlling dependencies allows one to accomplish a lot of powerful things in an app that would otherwise be very difficult. And then we did the same with UIKit in our “ Modern UIKit ” series. Brandon

— 1:08

This time, “Modern Persistence” refers to the best practices in bringing complex persistence and querying strategies into your app. We want to be able to have an external data source be the source of truth for user’s data, such as a SQLite database, but we also want the benefits of:

— 1:25

…holding that data in your features in a seamless manner so that you can perform nuanced logic around that data, and perform updates to the data that are then persisted to the data source. Stephen

— 1:36

And ideally there wouldn’t be unnecessary restrictions of where and how we hold onto this data in our features. Some people like to hold onto the data directly in their views or controllers, and others like to keep the data in a separate domain model. We should be free to choose where we want the data, and most likely in real world, complex apps we will probably have a mixture of both. Brandon

— 1:57

You also want to be able to listen for changes to the data source so that if someone else writes to the database your features are immediately updated. Stephen

— 2:07

And if all of that wasn’t complex enough, we further want to accomplish all of this in a super short, succinct syntax with as few of concepts as possible. Brandon

— 2:16

Oh, and one last thing that is important to us and many of our viewers: we want the whole thing to be very testable too.

— 2:23

So, we have some huge challenges ahead of us, and in order to explore these ideas we are going to build a new app from scratch. It will be a reminders app inspired by the default reminders app that comes with iOS. It seems like a simple app to build, but there are some very interesting and complex queries that one needs to be able to execute to implement the app’s features. And luckily for us, during our last series of episodes creating a query builder from scratch we already designed some of the basics types for this reminders app. But don’t worry if you didn’t follow along with those episodes, we will be doing everything from the beginning in this series. Stephen

— 2:57

And of course many may be wondering, why aren’t we using SwiftData for this? Isn’t SwiftData a “modern” persistence framework? Well, it definitely is, but there are some drawbacks to using SwiftData that we think we can fix:

— 3:10

First of all, SwiftData is a proprietary Apple technology, and any app built with it is necessarily tied to Apple’s platforms. That may not bother you, but at the end of the day SQLite is a cross platform library installed on billions of devices, and so we would hope it’s possible to create a portable persistence strategy. Brandon

— 3:28

SwiftData is also built on top of references types, but typically we prefer to model core domain types as value types since they bring a lot of guarantees and simplicity to the table. Stephen

— 3:41

Further, SwiftData can be difficult to test, not only because it is built on reference types, but also because most of its powerful tools only work when used directly in views, and that means you are forced to test with the UI testing framework, which is very slow and very flakey. Brandon

— 3:58

And finally, SwiftData so fully abstracts away the SQL from you that it can be very difficult to query for complex subsets of data that are a cinch in SQL. And it’s even possible to write queries in Swift data that crash at runtime but will be compile time errors in our libraries.

— 4:19

So, while SwiftData is a modern and powerful framework, we don’t think it’s for everyone, and our SharingGRDB library offers a lot of the same ergonomics without these caveats. And so that is why we are excited to embark on our “Modern Persistence” series.

— 4:34

Let’s get started! A tour of Reminders

— 4:36

Let’s start by giving a quick tour of the app we will be building. It is a reminders style app that is heavily inspired by the Reminders app that ships with iOS. I’ve got the iOS reminders app opened right now, and we see right out of the gate we see some top level stats of our reminders along with a list view displaying all of our reminders lists, of which there is only one right now called “Reminders”.

— 5:04

Let’s start by adding a few more lists just so that we have some variety. We will add a list for “Family” and a list for “Business”.

— 5:29

And we will rename “Reminders” to “Personal”.

— 5:37

It’s worth noting that we are able to select a primary color for the list, and upon adding a list we were automatically navigated to that list. And on the main list page we see the number of reminders in each list, which is currently 0 in all lists.

— 5:53

Next let’s add some reminders to our lists. To our personal reminders list we will add: Get haircut, high priority, due tomorrow Take a walk, low priority, due today Buy concert tickets, due yesterday

— 6:24

It’s worth mentioning in the actual Reminders app on iOS devices there is an additional option to flag a reminder and add tags to a reminder. These are things we will want to recreate in our app, but we can’t show you what that looks like right now.

— 6:50

Now that we have a few reminders in this list we can explore other things that can be done in this list. For example, we can sort by due date, creation date, priority, title, and do so in an ascending or descending fashion. We can also show completed reminders because by default all completed reminders are hidden from this view.

— 7:27

Let’s go ahead and complete a few reminders to show that they disappear…

— 7:39

It’s interesting to note that when you complete a reminder it does not go away immediately. It sticks around for a few seconds and then goes away. Ostensibly this is so that if you accidentally complete a reminder that you did not mean to you have a few moments to uncheck it.

— 7:56

And then if we show completed reminders we will see them come back. And further completing or un-completing any reminders causes them to automatically animate to their final position in the list.

— 8:12

Let’s go back to the main list of lists, and go into the “Family” list to add a few reminders. We will add: Get groceries, medium priority, next week Buy birthday present, high priority, no due date Wash car, last week, completed

— 8:55

And finally let’s go to the “Business” list and add a few reminders: Call accountant, high priority, today, completed Send episode emails, next Monday Pay quarterly taxes, high priority, June 1st

— 9:50

You may not have noticed, but it turns out that the sorting options and the “Show completed” option is set on a per-list basis. So if I go into the “Personal” list, sort by title, and show completed, and then go into the “Family” list, we will see it is sorted by due date and does not show completed lists. So this is an interesting detail that we will want to make sure to capture when we recreate this.

— 10:22

And now that we have a wide variety of reminders added we can see how the root screen behaves. The top-level stats show how many reminders are due today, how many are scheduled (which means they have a due date), how many are uncompleted, as well as a list of all completed reminders. We can tap on any of these stats to see all their reminders. And further each list is now showing only the number of incomplete reminders in each list.

— 10:52

The official Reminders app on iOS has a few other things that are interesting on this page. It has a top-level stat for the number of flagged reminders, and at the bottom of the lists is a section for all tags used on reminders. Tapping on any tag takes you to a list of reminders with that tag assigned.

— 11:13

The final feature of this app that we want to show off is the search functionality. We can search for any part of the reminder, including its title, notes, and even tags. Further, while searching the UI tells us how many reminders have been completed that match the search, and we can even clear out those reminders. We can also choose to show and hide the completed reminders from the search results. Schema

— 12:06

And that is the app we will be building. It is honestly pretty complex, and it is going to good amount of work to properly design a database schema that can efficiently handle thousands, if not tens of thousands of reminders. And we are going to have to craft some complex queries to sift through all that data and display the various lists and stats in our features. Stephen

— 12:27

Let’s get started building our port of this complex application. We will begin with the schema of our database. We will design a collection of Swift data types that represent the data we want to store in a SQLite database. We are starting with Swift data types because they are our primary interface to this data. 99% of the time we will be working with these data types to implement our features, and so we would like them to be as pristine as possible. 13:0-11 And then we will create a database and migrate it to add the actual tables that represent our Swift data types, as well as the relationships between the tables. 13:0-2 Let’s dig in.

— 13:01

I’ve got a fresh project ready to go, and I am going to create a file called Schema.swift that will hold all of the data types that represent our database tables…

— 13:12

And the first thing I am going to do is import our latest SharingGRDB library: import SharingGRDB

— 13:20

This gives us access to a suite of tools that allow us to model our database tables as simple Swift data types and unlock powerful query building that is type-safe and schema-safe.

— 13:22

But first we need to add this library to our project… 14:0-23 And now everything is compiling. 14:0-7 Let’s start with a very simple data type that does not have many fields, such as the reminders list. For us, a reminders list only consists of a color, represented as an integer, a title, and we will want a unique identifier too, which will be an integer: struct RemindersList { let id: Int var color = 0x4a99ef_ff var title = "" }

— 14:30

This is a pristine Swift type that describes exactly the kind of values we want to use in our application. We are thinking of Swift and our app feature code first, and we will get to the database layer in a moment. 15:0-18 And in order to unlock all of our query building tools for this type we must apply the @Table macro to this type: @Table struct RemindersList { … } 15:0-11 This generates a whole bunch of code under the hood: struct RemindersList { @Column("id", primaryKey: true) let id: Int @Column("color") var color: Int @Column("title") var title = "" } extension RemindersList: StructuredQueries.Table, StructuredQueries.PrimaryKeyedTable { public struct TableColumns: StructuredQueries.TableDefinition, StructuredQueries.PrimaryKeyedTableDefinition { public typealias QueryValue = RemindersList public let id = StructuredQueries.TableColumn<QueryValue, Int>("id", keyPath: \QueryValue.id) public let color = StructuredQueries.TableColumn<QueryValue, Int>("color", keyPath: \QueryValue.color) public let title = StructuredQueries.TableColumn<QueryValue, Swift.String>("title", keyPath: \QueryValue.title, default: "") public var primaryKey: StructuredQueries.TableColumn<QueryValue, Int> { self.id } public static var allColumns: [any StructuredQueries.TableColumnExpression] { [QueryValue.columns.id, QueryValue.columns.color, QueryValue.columns.title] } } public struct Draft: StructuredQueries.TableDraft { public typealias PrimaryTable = RemindersList @Column(primaryKey: false) let id: Int? var color: Int var title = "" public struct TableColumns: StructuredQueries.TableDefinition { public typealias QueryValue = RemindersList.Draft public let id = StructuredQueries.TableColumn<QueryValue, Int?>("id", keyPath: \QueryValue.id) public let color = StructuredQueries.TableColumn<QueryValue, Swift.Int>("color", keyPath: \QueryValue.color, default: 0x4a99ef_ff) public let title = StructuredQueries.TableColumn<QueryValue, Swift.String>("title", keyPath: \QueryValue.title, default: "") public static var allColumns: [any StructuredQueries.TableColumnExpression] { [QueryValue.columns.id, QueryValue.columns.color, QueryValue.columns.title] } } public static let columns = TableColumns() public static let tableName = RemindersList.tableName public init(decoder: inout some StructuredQueries.QueryDecoder) throws { self.id = try decoder.decode(Int.self) self.color = try decoder.decode(Int.self) ?? 0x4a99ef_ff self.title = try decoder.decode(Swift.String.self) ?? "" } public init(_ other: RemindersList) { self.id = other.id self.color = other.color self.title = other.title } public init( id: Int? = nil, color: Int = 0x4a99ef_ff, title: Swift.String = "" ) { self.id = id self.color = color self.title = title } } public static let columns = TableColumns() public static let tableName = "remindersLists" public init(decoder: inout some StructuredQueries.QueryDecoder) throws { let id = try decoder.decode(Int.self) self.color = try decoder.decode(Int.self) ?? 0x4a99ef_ff self.title = try decoder.decode(Swift.String.self) ?? "" guard let id else { throw QueryDecodingError.missingRequiredColumn } self.id = id } } 15:0-5 This is over 70 lines of generated code from just the 4 lines that define our struct. It’s not necessary to understand how all of this code works, but suffice it to say it allows us to build type-safe queries based on the fields that are specified in the struct.

— 15:10

The next simplest table to model as a Swift data type is that of a tag. We mentioned that the version of reminders installed on simulators does not have the tagging feature, but the full version on iOS does and we do want that functionality.

— 15:22

To us a tag will just be an integer ID and a title string: @Table struct Tag { let id: Int var title = "" } 16:0-26 The next table we will model is the reminders table, and it will be a bit more complicated. To begin with, our reminders will have a unique ID, an isCompleted boolean, an isFlagged boolean, a notes field, and a title: @Table struct Reminder { let id: Int var isCompleted = false var isFlagged = false var notes = "" var title = "" }

— 16:01

That part is easy, but there is of course a lot more a reminder needs to hold onto. Let’s start with the due date, which should be an optional date since not every reminder is required to have a date: import Foundation struct Reminder { … var dueDate: Date? }

— 16:14

However, this causes a compilation error: ‘Date’ column requires a query representation

— 16:22

This is happening because SQLite does not have a native type for dates. Instead you can choose to store a date as either text, or an integer, or a double, and in each of those cases SQLite interprets the format of the date differently: 17:0-26 If you store a date as a string in a column then SQLite interprets the date as an ISO-8601-formatted string. 17:0-21 If you store a date as an integer then SQLite interprets that to be the number of seconds since the Unix epoch. 17:0-16 And if you store a date as a double then SQLite interprets that to be the number of days since November 24, 4713 B.C., also known as a Julian day number. 17:0-5 And so we need to tell our library how we want to encode the date into a SQLite-friendly field, and the library can even help us with this. The @Table macro is what is generating this error for us, and it provides us with multiple fix-its that it can apply to fix the problem.

— 17:07

We will choose to represent dates as text formatted in the ISO-8601 standard, and so we will choose that fix-it: struct Reminder { … @Column(as: Date.ISO8601Representation?.self) var dueDate: Date? }

— 17:15

And just like that we are in compiling order. This correctly tells our library that while we want to store this field in our app code as a Foundation Date , the type actually held in the database is a string and is represented by an ISO-8601-formatted string.

— 17:31

Let’s move onto the next field we want to add to this model that is a little less straightforward. Remember that each reminder is able to have a priority associated with it, and it can either be low, medium, or high priority, or not have a priority at all. 18:0-16 We could of course represent this as an optional integer: struct Reminder { … var priority: Int? } 18:0-11 This compiles just fine, but we can do better. When using an optional integer we have to mentally map the full range of integers onto the concept of “high”, “medium” and “low”. We could consider the number 3 to be high, 2 to be medium, and 1 to be low. But then we have numbers 4 through Int.max to contend with. What should those numbers represent? And worse, there are negative numbers!

— 18:09

This is exactly the kind of problem that Swift enums were created to solve. We have only a finite number choices we want to represent, so why are we choosing a type that has more than 18 quintillion values in it?

— 18:19

So, let’s do this the right way. Let’s define an enum that enumerates low, medium and high priority, and hold that in our Reminder data type: @Table struct Reminder { … var priority: Priority? enum Priority { case low case medium case high } } 19:0-26 This would be great if it worked, but unfortunately we do get a compiler error: Type ‘Reminder.Priority’ does not conform to protocol ‘QueryBindable’ 19:0-8 Just as we could not naively hold onto a Date in our model because SQLite does not know about dates, we also cannot hold onto a Priority in our model. However, because this enum is so simple we can support it in our model more more easily.

— 19:08

First we can make the enum RawRepresentable by an integer: enum Priority: Int { case low case medium case high }

— 19:14

This makes it so that low is associated with the integer 0, medium with 1 and high with 2. Or we can override the default starting integer: enum Priority: Int { case low = 1 case medium // = 2 case high // = 3 }

— 19:26

And further, an initializer is automatically synthesized that allows you to initialize a Priority from an integer: let p = Priority(rawValue: <#Int#>) 20:0-24 And this is a failable initializer where if you provide any integer besides 1, 2, or 3 it will initialize to nil . 20:0-17 So already we are seeing that this small change makes the Priority enum behave similar to an integer, which is a native type that SQLite does understand. Next we need to further conform Priority to the QueryBindable protocol from StructuredQueries: enum Priority: Int, QueryBindable { case low case medium case high } 20:0-3 That is all we have to do and the project is back to compiling order. The reason no other work is needed is that StructuredQueries provides a default implementation of QueryBindable for RawRepresentable types. So the simple act of conforming to both RawRepresentable and QueryBindable at once satisfies all of the requirements and we are good to go.

— 20:21

There is one final property we want to add to the Reminder data type. Remember that every reminder belongs to exactly one list, and we would like to represent this fact in the Swift data type. Now we can’t naively model this as the Reminder type literally holding onto a RemindersList value: @Table struct Reminder { … var remindersList: RemindersList } 21:0-20 This is not correct for a few reasons. First of all, the properties of these data types should correspond to exactly what is held in the database. So, if the table in the database has 3 fields of an integer, text and boolean, then our Swift data type should just hold onto 3 properties of an integer, string and boolean. And once we get to actually creating the database for our schema, it is not going to be the case that the “reminders” table literally has a column to hold all of the information for its associated reminders list. Instead we will have a separate “remindersLists” table that has the lists, and we will create a foreign key relationship between the tables.

— 21:13

And second, by trying to hold an entire list in the Reminder type we are preventing ourselves from taking advantage of a huge potential optimization when building queries, which is being very selective with what data we actually pull from the database. There will be times we want to select all reminders along with just the title of its associated list. Or just with the color of its associated list. Doing these kinds of hyper-specific selection strategies is a great way to boost the performance of queries, but we would not be able to do this if we forced the entire list to be represented in each reminder. 22:0-18 So, this is not what we want. Instead, we want a foreign key in the Reminder type that points to its associated RemindersList . We can do this by holding onto the integer of the ID of the list that the reminder is associated with: @Table struct Reminder { … var remindersListID: Int } 22:0-6 This does the job, but we can make it even better. Let’s make the RemindersList identifiable since it has an ID field: @Table struct RemindersList: Identifiable { … }

— 22:07

And in fact, let’s go ahead and make all of our data types Identifiable since they trivially conform: @Table struct Reminder: Identifiable { … } @Table struct Tag: Identifiable { … }

— 22:14

And now we can make it much clearer that the Reminder type has a pointer to associated it with a particular list: @Table struct Reminder { … var remindersListID: RemindersList.ID }

— 22:21

And this right here represents a 1-to-many relationship. One single list can have many reminders, and because the foreign key is non-optional it also forces that every reminder belong to exactly one list.

— 22:31

So, that’s how to model 1-to-many relationships, but we also have a many-to-many relationship lurking in our schema. Reminders can be tagged with any number of tags, and a tag can be associated with any number of reminders. 23:0-16 The way one represents such relationships in SQLite is to have a dedicated table that holds an ID from each of the tables being associated with each other. So, as far our Swift is concerned, that means a new data type, decorated with the @Table macro, and holding onto a reminder ID as well as a tag ID: @Table struct ReminderTag { let reminderID: Reminder.ID let tagID: Tag.ID }

— 23:09

Then we will create rows of this table to represent an association between a reminder and a tag, and that allows there to be any number of reminders to be associated with any number of tags, and vice-versa.

— 23:19

That’s all it takes! Creating the database

— 23:22

We have now seen just how easy it is to model our database schema as first class Swift data types. We’ve now modeled 4 tables: a “reminders” table, a “remindersLists” table, a “tags” table, and a many-to-many join table called “reminderTags”. Further, we’ve seen how to use custom data types in our models that SQLite does not have knowledge of, such as dates and our Priority enum. And we saw how to model foreign key relationships, including 1-to-many and many-to-many relationships. 24:0-10 This is basically all you need to know to design your database tables as first class Swift data types. There’s a few more advanced tricks you could learn, but for the most part what we have covered are the most important parts. Brandon

— 24:02

But, we still have not created an actual database connection to the SQLite database that will hold all of our data. We are going to use the popular GRDB library to handle all of the low-level interactions with SQLite, and we will use its migration tools to provide a single place to create our tables.

— 24:20

Let’s get started.

— 24:22

First we want to establish a connection to our local SQLite database. The way we like to do this is as a module scoped function that returns a type erased DatabaseWriter : func appDatabase() throws -> any DatabaseWriter { } 25:0-18 The DatabaseWriter protocol comes from GRDB and represents a connection to a database that is capable of executing read and write queries. 25:0-10 Inside this function we will create a database connection, but it will take a few steps to do so. We can start by forward declaring the variable for the connection: let database: any DatabaseWriter return database

— 25:06

We are going to provide some configuration to our database connection, and so we will create a mutable bit of state for that: var configuration = Configuration()

— 25:09

There are a number of things we can configuration in our database connection, but the most important setting is foreign key checks: configuration.foreignKeysEnabled = true

— 25:18

This makes SQLite strict when it comes to foreign keys. If we say that one table references another table with a non-

NULL 26:09

But we can do better than just print everything to standard output using the standard library print function: we can introduce an OSLog logger, which logs in a fashion that makes it easier to isolate and filter things: import OSLog … logger.debug("\($0.expandedDescription)") … private let logger = Logger( subsystem: "Reminders", category: "Database" )

NULL 27:03

Now that we have our configuration prepared we can create the connection to the database. We will define a variable for the URL on the file system the database is stored: let path = URL.documentsDirectory .appending(component: "db.sqlite") .path()

NULL 27:21

And we like to log the URL to the console when the database connection is opened because it makes it easy to open the database from terminal or in a dedicated app: logger.info("open \(path)") 28:0-17 And then we will create a connection to the database via the DatabasePool object: database = try DatabasePool(path: path, configuration: configuration) 28:0-7 The DatabasePool class conforms to DatabaseWriter , and so that is why we are allowed to assign it to database . It represents a connection to the database that allows for multiple concurrent reads, but only one write can be performed at a time.

NULL 28:22

We now have a connection to our database, and the next important thing to do is migrate the database to create the tables that represent our Swift data types. However, there is something we can do to improve this code already. 29:0-25 It is not always the case that we want to open up a connection to the actual live database file sitting on disk. Many times we will want to open up a connection to an in-memory database. This is the case when running tests so that we can run multiple tests in parallel without them interfering with each other. It is also the case when running Xcode previews so that you can make changes in one preview without it affecting another one. 29:0-4 To support this we will detect if the code is executing in a preview or tests, and if so, provision an in-memory database, instead. Now there are cryptic incantations we could write out that check various process info to do this, but we have a better option. SharingGRDB leverages our popular Dependencies library, which has a dependency value that does just that, called context .

NULL 29:22

We can get access to it using the @Dependency property wrapper: @Dependency(\.context) var context

NULL 29:29

And switch over this value to handle each case. For “live” contexts, meaning running the app in the simulator or on device, we will create a connection to a persisted database on disk, and in previews and tests we will create an in-memory database connection: switch context { case .live: let path = URL.documentsDirectory .appending(component: "db.sqlite") .path() print("open", path) database = try DatabasePool(path: path, configuration: configuration) case .preview, .test: database = try DatabaseQueue(configuration: configuration) }

NULL 30:21

This will make it much easier to test and preview code that interacts with our database.

NULL 30:25

Now we can finally add tables to our database that represents the Swift data types we created just a moment ago. We want to create these tables only a single time when the database connection is first made upon first launch of the app. We don’t need to create the tables again when the user launches the app again. 31:0-15 The way one does this is to have some kind of migration mechanism in place that allows you run little bits of code when the app launches, and then a record is made somewhere so that you know which migrations have been run so that you don’t have to run them again. And GRDB comes with such a mechanism.

NULL 31:03

We start by creating a migrator: var migrator = DatabaseMigrator()

NULL 31:07

And then we register a migration by using the registerMigration method: migrator.registerMigration(<#String#>, migrate: <#(Database) throws -> Void#>)

NULL 31:14

This method takes a string to uniquely identify the migration, and a trailing closure that is handed a database value that can be used to make writes to the database: migrator.registerMigration("Create tables") { db in }

NULL 31:28

The work performed inside this trailing closure will only be executed a single time. When GRDB runs this migration it records the identifier in a private table and uses that information to know when it needs to run the migration or when it can be skipped. 32:0-17 To create our tables we will execute SQL strings using another macro that comes with our StructuredQueries library, and hence SharingGRDB. It’s called #sql , and you can feed it a SQL string to execute: #sql( """ """ )

NULL 32:01

And once the statement is constructed you can invoke the execute method on it to actually execute the statement with GRDB: try #sql( """ """ ) .execute(db)

NULL 32:10

This allows us to execute a SQL string directly, but , most importantly, it does not allow us to execute unsafe SQL. By default, if you interpolate a value into this string it will interpret that as a binding and creating an appropriate prepared statement that is safe from SQL injections: try #sql( """ \(42) """ ) .execute(db) 33:0-8 You can also interpret parts of the table schema into this string, which is also a completely safe thing to do and something we will show of later: try #sql( """ \(Reminder.id), \(Reminder.title) """ ) .execute(db)

NULL 33:20

And finally, it is possible to interpolate raw SQL into this string, which is unsafe, but you are required to make it explicit you know you are using a raw, unsafe API: try #sql( """ \(raw: "un-escaped, raw string") """ ) .execute(db)

NULL 34:06

This makes the #sql macro completely safe to use, and it can be a very powerful tool to use when certain complex queries are difficult to build in our query builder API.

NULL 34:18

So, we are ready to create our table, and if you followed our “ SQL Building ” series you know exactly what we need to do in here. SQL has a special statement called a CREATE TABLE statement that allows you to specify the name of the table as well as the columns for the table: try #sql( """ CREATE TABLE "remindersLists" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "color" INTEGER NOT NULL DEFAULT \(raw: 0x4a99ef_ff), "title" TEXT NOT NULL DEFAULT '' ) """ ) .execute(db) 36:0-16 We are going to further add one option to this CREATE TABLE statement. As of version 3.37.0 of SQLite, which iOS comes with and was released in 2021, it is possible to declare tables as being “strict”: try #sql( """ CREATE TABLE "remindersLists" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "color" INTEGER NOT NULL DEFAULT \(raw: 0x4a99ef_ff), "title" TEXT NOT NULL DEFAULT '' ) STRICT """ ) .execute(db)

NULL 36:01

This causes SQLite to be more strict with what types of value are held in these columns. SQLite is for the most part an untyped database, and by default it allows you to store any kind of data in these columns. You can put a string in the “color” column, or data bytes in the “title” column.

NULL 36:23

That flexibility can be powerful, but since we are working in a statically typed language it can be nice for the database to have some stricter typing too. So by applying the

STRICT 37:06

That is basically all it takes. We can even invoke the appDatabase method in the entry point of the app: @main struct ModernPersistenceApp: App { init() { let _ = try! appDatabase() } … } 38:0-24 …and then run the app to see the following logs printed to the console: open …/Documents/db.sqlite 0.000s SELECT * FROM sqlite_master LIMIT 1 0.000s PRAGMA journal_mode = WAL 0.000s PRAGMA synchronous = NORMAL 0.001s BEGIN IMMEDIATE TRANSACTION 0.000s CREATE TABLE grdb_issue_102 (id INTEGER PRIMARY KEY) 0.000s DROP TABLE grdb_issue_102 0.001s COMMIT TRANSACTION 0.000s CREATE TABLE IF NOT EXISTS grdb_migrations (identifier TEXT NOT NULL PRIMARY KEY) 0.000s SELECT identifier FROM grdb_migrations 0.000s PRAGMA foreign_keys 0.000s PRAGMA foreign_keys = OFF 0.000s BEGIN IMMEDIATE TRANSACTION 0.000s CREATE TABLE "remindersLists" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "color" INTEGER NOT NULL, "title" TEXT NOT NULL DEFAULT '' ) STRICT 0.000s INSERT INTO grdb_migrations (identifier) VALUES ('Create tables') 0.000s PRAGMA foreign_key_check 0.000s COMMIT TRANSACTION 0.000s PRAGMA foreign_keys = ON 38:0-8 We can see the URL of where the database is being stored, along with a bunch of queries that have been executed in order to check which migrations have been run and then to actually create our table. And if we open our database we will see that indeed there is a “remindersLists” table with an “id”, “color”, and “title” column. 39:0-19 So, that’s all it takes to create our first database table. But you may be wondering why we are resorting to raw SQL strings: try #sql( """ CREATE TABLE "remindersLists" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "color" INTEGER NOT NULL DEFAULT \(raw: 0x4a99ef_ff), "title" TEXT NOT NULL DEFAULT '' ) STRICT """ ) .execute(db) 39:0-11 …when it seems like a big point of our StructuredQueries library is to write safer SQL via Swift APIs. This doesn’t seem very modern. 39:0-3 Wouldn’t it be better if we used the statically defined names of the table and columns in this query? For example, instead of hard coding the string “remindersLists” we could instead interpolate the table name directly from the data type: """ CREATE TABLE "\(raw: RemindersList.tableName)" ( … ) """

STRICT 39:30

In fact, we can do better than using the raw string interpolation API. We can interpolate the type of the table directly: """ CREATE TABLE "\(RemindersList.self)" ( … ) """ 40:0-22 …and this does the same thing, but is a bit safer since it uses statically known values. 40:0-9 We can even access the static description of the columns of the table that the macro provides us and interpolate those values directly into the query: """ \(RemindersList.id) INTEGER PRIMARY KEY AUTOINCREMENT, \(RemindersList.color) INTEGER NOT NULL DEFAULT \(raw: 0x4a99ef_ff), \(RemindersList.title) TEXT NOT NULL DEFAULT '' """

STRICT 40:10

And again this is completely safe because this is all statically known information at the time of compiling. We are not interpolating any dynamic or user provided data.

STRICT 40:23

And this certainly seems a little better because we are leaning more on static information that is known at compile time, rather than using plain strings which is susceptible to typos. 41:0-28 However, we do not feel this is the right way to go. While a goal of the library is to be able to write more expressive and safer SQL queries, it is not a goal of the library to fully replace the need to write SQL queries as strings. It is not always appropriate to use the static tools of the library, and this is one of those situations. 41:0-8 It’s important to know that the Swift data type description of our tables live in a completely different world from our migrations. The Swift data type represents the idealized form of our tables at the present moment. It’s the version of table once it has been fully migrated to its most up-to-date version.

STRICT 41:14

Whereas each step of the migration represents the state of our database at a particular snapshot in time. As the application evolves we are going to create dozens of more migrations, perhaps even hundreds. We are going to add new tables, add or remove columns from existing tables, create indices, and whole bunch more. The state of those migrations are almost always going to be incompatible. 42:0-19 Once a migration is shipped it should never be edited and it should not be dynamically constructed. The easiest way to see this is to suppose that some day in the future we decide to drop the “color” column from our “remindersLists” table. Since the RemindersList type represents the state of the table in its most up-to-date form, we can just remove that property: // var color = 0x4a99ef_ff

STRICT 42:03

The moment we do that we get a compilation error in our migration that originally created the “remindersLists” table: migrator.registerMigration("Create tables") { db in try #sql( """ CREATE TABLE "\(RemindersList.self)" ( "\(RemindersList.id)" INTEGER PRIMARY KEY AUTOINCREMENT, "\(RemindersList.color)" INTEGER NOT NULL, "\(RemindersList.title)" TEXT NOT NULL DEFAULT '' ) STRICT """ ) .execute(db) } Value of type ‘RemindersList.TableColumns’ has no member ’color’

STRICT 42:12

This may lead us to believe that perhaps we are supposed to just remove this column from the migration: migrator.registerMigration("Create tables") { db in try db.execute( literal: """ CREATE TABLE "\(RemindersList.self)" ( "\(RemindersList.id)" INTEGER PRIMARY KEY AUTOINCREMENT, - "\(RemindersList.color)" INTEGER NOT NULL, "\(RemindersList.title)" TEXT NOT NULL DEFAULT '' ) STRICT """ ) }

STRICT 42:22

But this is not correct because migrations are a historical record, and we should not edit history. As we said a moment ago, once a migration is shipped it should never be edited. And so in our opinion, the modern way to perform migrations is just as simple SQL strings: migrator.registerMigration("Create tables") { db in try #sql( """ CREATE TABLE "remindersLists" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "color" INTEGER NOT NULL, "title" TEXT NOT NULL DEFAULT '' ) STRICT """ ) .execute(db) } 43:0-7 …even though that doesn’t seem so modern. Creating more tables

STRICT 46:01

OK, we are now making some headway on our journey to a modern app with persistence built on SQLite. We know how to connect to a database, and we can do so either as a file on disk or in-memory depending on the situation. Then we saw how to set up a migration system so that we can create the tables in the database that represent the data types in our app. And we even got a bit of exposure to the powerful #sql macro, but it has a lot more cool tricks up its sleeve that we won’t be able to see until later. Stephen

STRICT 46:27

Let’s now create the rest of our tables. We need a table for the tags, the reminders, as well as the join table that allows us to associate any number of tags to any number of reminders. 47:0-21 Let’s dig in. 47:0-19 The next table we will create is the one that will hold tags. We can even do this work inside the existing migration we have going, and we will of course turn on “STRICT” mode: migrator.registerMigration("Create tables") { db in … try #sql( """ CREATE TABLE "tags" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "title" TEXT NOT NULL DEFAULT '' ) STRICT """ ) .execute(db) }

STRICT 47:06

As we mentioned a moment ago, once a migration has shipped, meaning once it has been released in a version of your app that is installed on a user’s device, you should consider that migration frozen forever. But that is not that the case for the migration we have written so far, so there’s no harm in adding more work in its trailing closure.

STRICT 47:24

Well, that is all it takes to create a “tags” table because it is a quite simple table. Let’s move onto the next table, which is the most complicated one: “reminders”. We can start with an “id” column that, like the other tables, will be an integer, primary key, and auto-incrementing, and it also has a “title” column: try #sql( """ CREATE TABLE "reminders" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "title" TEXT NOT NULL DEFAULT '' ) STRICT """ ) .execute(db) 48:0-17 Next we will add a column for the dueDate , but remember that although we want to store the value as a foundation Date in our Swift data type, SQLite has no custom data type for dates. Instead we need to store it as a text column that will hold an ISO-8601-formatted dates: "dueDate" TEXT

STRICT 48:00

We also are not going to mark this column as NOT NULL because we do want it to be nullable to represents reminders with no dates.

STRICT 48:07

Next we will add columns for isCompleted , isFlagged , and notes , which is pretty straightforward: "isCompleted" INTEGER NOT NULL DEFAULT 0, "isFlagged" INTEGER NOT NULL DEFAULT 0, "notes" TEXT NOT NULL DEFAULT '', 49:0-22 Next we will add a column for the priority. Again we are using a custom data type to represent the priority in our Swift data type, but we cannot use that in SQLite. Instead we will store the priority as an integer: "priority" INTEGER, 49:0-3 …and the @Table macro will be responsible for converting between this integer and the Priority enum.

STRICT 49:02

And finally we have the remindersListID column that points to the list that the reminder belongs to. This relationship is required, and so it can be represented as a non-

NULL 49:16

But we can further tell SQLite that this integer is specifically referencing a column in another table, in particular the “id” column of the “remindersLists” table: "remindersListID" INTEGER NOT NULL REFERENCES "remindersLists"("id") 50:0-28 And further we can tell SQLite that if anyone ever deletes a list, then all associated reminders should also be deleted: "remindersListID" INTEGER NOT NULL REFERENCES "remindersLists"("id") ON DELETE CASCADE 50:0-15 And that’s all it takes to create the “reminders” table. 50:0-9 The final table we need to create is the many-to-many relationship table that says which reminders are associated with which tags. This can be done like so: try #sql( """ CREATE TABLE "reminderTags" ( "reminderID" INTEGER NOT NULL REFERENCES "reminders"("id") ON DELETE CASCADE, "tagID" INTEGER NOT NULL REFERENCES "tags"("id") ON DELETE CASCADE ) STRICT """ ) .execute(db) 51:0-10 That is all it takes to migrate our database and create all 4 tables that we currently need for our app.

NULL 51:05

However, if we run the app so that a database connection is created, and this migration code is run again…and then open the database by copying and pasting the command printed to the console into terminal…we will see that our 3 new tables are not actually in the database. Why is that?

NULL 51:27

Well, as we mentioned before, when GRDB detects that a migration has not yet been run it runs it, and then records that migration identifier in a private table. We can even see what data is stored in that table: sqlite> SELECT * FROM grdb_migrations; ┌───────────────┐ │ identifier │ ├───────────────┤ │ Create tables │ └───────────────┘ 52:0-11 So, as far as GRDB knows, we’ve already run the “Create tables” migration, and therefore it’s not necessary to run again. But what GRDB doesn’t know is that we actually updated the migration to create more tables. In this case, which is very common when actively developing an app, we would like GRDB to be able to detect that the migration has changed and re-run it.

NULL 52:09

And luckily GRDB has an option to do just that. We can enable the eraseDatabaseOnSchemaChange option on the migrator: migrator.eraseDatabaseOnSchemaChange = true

NULL 52:19

…and if GRDB detects that the current schema of the database does not match what is generated by running all of the migrations, it will blow everything away and start from scratch. This is great for development so that you can quickly iterate on schema changes without having to worry about the data that is held in the database, but it would be pretty disastrous to do this on a user’s device. 53:0-23 We’ve already said a few times that you should never modify a migration that has been released out into the wild, and so if you follow that advice strictly you should never run into this problem. However, mistakes can happen, and if you ever do accidentally edit a migration after its been shipped you run the risk of accidentally blowing away their entire database next time they launch your app. So, to protect against that it is recommended to wrap this in an #if DEBUG check: #if DEBUG migrator.eraseDatabaseOnSchemaChange = true #endif

NULL 53:04

Now when we run the app again we will see that our migration runs, and we see all of the CREATE TABLE statements appear in the logs, and re-opening the database shows that all tables have been created.

NULL 53:25

It’s important to note that our usage of camel-case for table names and column names: try #sql( """ CREATE TABLE "reminderTags" ( "reminderID" INTEGER NOT NULL REFERENCES "reminders"("id") ON DELETE CASCADE, "tagID" INTEGER NOT NULL REFERENCES "tags"("id") ON DELETE CASCADE ) STRICT """ ) .execute(db)

NULL 53:31

…may not be what you are used to. Many people prefer to use snake case for database schemas: try #sql( """ CREATE TABLE "reminder_tags" ( "reminder_id" INTEGER NOT NULL REFERENCES "reminders"("id") ON DELETE CASCADE, "tag_id" INTEGER NOT NULL REFERENCES "tags"("id") ON DELETE CASCADE ) STRICT """ ) .execute(db) 54:0-14 It is absolutely OK if you want to use snake-case, or really any naming strategy. The @Table macro allows you to customize the naming to whatever you want. In order to override the name of the table used by StructuredQueries you can pass a string to the @Table macro: @Table("reminder_tags") struct ReminderTag { var reminderID: Reminder.ID var tagID: Tag.ID }

NULL 54:10

Now all queries generated by the library will use the table name “reminder_tags” instead of “reminderTags”.

NULL 54:18

And to customize the name of the columns you can use the @Column macro and supply a string of the name you want to use: @Table("reminder_tags") struct ReminderTag { @Column("reminder_id") var reminderID: Reminder.ID @Column("tag_id") var tagID: Tag.ID } 55:0-21 Now all queries generated by the library will use reminder_id and tag_id for the column names. 55:0-11 This should make it possible to use the tools from StructuredQueries in any application with a pre-existing database schema in place. It is not necessary to create a database from scratch when using our library. You can simply customize the @Table and @Column macros to match your exact schema.

NULL 55:05

But since we are starting fresh, let’s undo those changes and embrace the default table and column names generated by the library: struct ReminderTag { var reminderID: Reminder.ID var tagID: Tag.ID } Next time: Reminders lists feature

NULL 55:14

We have now finished a big, foundational step to creating a modern app with its persistence based on SQLite. It may seem a little weird that we took any entire episode to just set up some tables and a database, but we have already learned a bunch of valuable lessons:

NULL 55:27

We have seen that we like to first and foremost design our database tables with our Swift data types in mind. This is because they are the interface to our data that we are going to be dealing with 99% of the time, and it allows us to take full advantage of everything Swift has to offer, such as enums for the finite enumeration of options. Brandon 56:0-14 Then we saw how to create a connection to a database, and how sometimes we may want to do that to a live file stored on disk, but other times, such as in tests and previews, we may want to do that in-memory. That way multiple tests and previews do not trample on each other by writing to the same file on disk. Stephen

NULL 56:06

And finally, we saw how to create the tables that represent our Swift data types, including how to set up a foreign key relationship that allows each reminder to belong to exactly one reminders list, and another relationship for allowing any number of tags to belong to any number of reminders.

NULL 56:21

These are all good concepts to be familiar with when building modern persistence into your app, we are going to leverage these concepts more and more as we progress through the app. Brandon

NULL 56:31

But we still haven’t actually gotten anything to display on the screen! And now it is time. We are going to start building out the views that can display the data in our database. We will show that our libraries come with a suite of tools that make it incredibly easy to query for complex subsets of data, and immediately display them in views. And further, any changes made to the database will cause the view to automatically update. And best of all? These tools are not relegated to only the view layer. They of course work in the view, but they also work in so many more places.

NULL 57:07

Let’s get to it…next time! References SQLiteData A fast, lightweight replacement for SwiftData, powered by SQL. https://github.com/pointfreeco/sqlite-data StructuredQueries A library for building SQL in a safe, expressive, and composable manner. https://github.com/pointfreeco/swift-structured-queries Downloads Sample code 0323-modern-persistence-pt1 Point-Free A hub for advanced Swift programming. Brought to you by Brandon Williams and Stephen Celis . Content Become a member The Point-Free Way Beta previews Gifts Videos Collections Free clips Blog More About Us Community Slack Mastodon Twitter BlueSky GitHub Contact Us Privacy Policy © 2026 Point-Free, Inc. All rights are reserved for the videos and transcripts on this site. All other content is licensed under CC BY-NC-SA 4.0 , and the underlying source code to run this site is licensed under the MIT License .