Video #325: Modern Persistence: Reminders Lists, Part 2

Episode: Video #325 Date: May 19, 2025 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep325-modern-persistence-reminders-lists-part-2

Description

We flesh out the reminders lists feature using advanced queries that aggregate reminders counts and bundle results up into a custom type via the @Selection macro. And we show how “drafts”—a unique feature of StructuredQueries—allow us to create and update values using the same view, all without sacrificing the preciseness of our domain model.

Video

Cloudflare Stream video ID: 57b2b1b9d0cf0ace035f9e4c5f34743d Local file: video_325_modern-persistence-reminders-lists-part-2.mp4 *(download with --video 325)*

References

Transcript

— 0:05

We now have a bit of test coverage on our feature using our powerful inline snapshot testing tools. It’s forcing us to assert on how data is fetched from our database, including the manner it is sorted. We even get test coverage on how our feature deletes some records from the database directly, and then that causes the state in our model to update. And it’s pretty incredible to see how easy it is to write tests on complex data types using these tools, and we will see that it will continue to pay dividends over and over in this series.

— 0:33

OK, we now know how to query for data in our feature to render all of our reminders lists. And we know how to delete a list when the user taps a “Delete” button. And we’ve even got some test coverage on the feature. Brandon

— 0:45

Let’s now move on to a more complex aspect of this app. Right now we are just showing the names of the reminders lists in the UI, but the real Reminders app also shows the number of uncompleted reminders for each list. We need to somehow query for not only the list of reminders, sorted by title, as we currently are, but also compute the number of reminders in each list.

— 1:11

And this is the kinda of thing that SQL excels at, and sadly SwiftData is not currently capable of performing. In order to execute this kind of query on Apple platforms you have to dip into CoreData APIs, which are cumbersome and not safe at all to use.

— 1:32

So let’s see what it takes to update our query to be more powerful. Lists with counts of reminders

— 1:37

This is our current query for fetching all reminders lists, sorted by title: @ObservationIgnored @FetchAll(RemindersList.order(by: \.title)) var remindersLists

— 1:43

And we need to seriously beef it up in order to compute the number of uncompleted reminders for each list. We find that it can be helpful to first design a type that represents the data that we want to fetch from the database.

— 2:02

In this case, we want to fetch a reminders list, along with the count of reminders in the list: struct RemindersListRow { let incompleteRemindersCount: Int let remindersList: RemindersList }

— 2:25

And it’s now our job to design a query that specifically selects this data from the database. But to facilitate decoding the data from the database into this type we need to annotate it with the @Selection macro: @Selection struct RemindersListRow { let incompleteRemindersCount: Int let remindersList: RemindersList }

— 2:40

This generates code that is quite similar to what the @Table macro does, but it only needs to generate the init(decoder:) which is what allows us to decode the data from the database into this custom data type: extension RemindersListRow: StructuredQueries.QueryRepresentable { public struct Columns: StructuredQueries.QueryExpression { public typealias QueryValue = RemindersListRow public let queryFragment: StructuredQueries.QueryFragment public init( incompleteRemindersCount: some StructuredQueries.QueryExpression<Int>, remindersList: some StructuredQueries.QueryExpression<RemindersList> ) { self.queryFragment = """ \(incompleteRemindersCount.queryFragment) AS "remindersCount", \(remindersList.queryFragment) AS "remindersList" """ } } public init(decoder: inout some StructuredQueries.QueryDecoder) throws { let incompleteRemindersCount = try decoder.decode(Int.self) let remindersList = try decoder.decode(RemindersList.self) guard let incompleteRemindersCount else { throw QueryDecodingError.missingRequiredColumn } guard let remindersList else { throw QueryDecodingError.missingRequiredColumn } self.incompleteRemindersCount = incompleteRemindersCount self.remindersList = remindersList } }

— 3:10

Now that we have a data type defined we can work towards creating the query that selects this data. And this is actually a query that we wrote during our series of episodes where we designed the query building library from scratch.

— 3:25

In the end, we want to be able to compute an array of these RemindersListRows values from a @FetchAll query: @FetchAll var remindersListRows: [RemindersListRow] And we will update the view to use this type, instead. As we’ve seen already in this series, we are allowed to describe the query directly inside the @FetchAll constructor: @FetchAll( … ) var remindersListRows: [RemindersListRow]

— 4:30

In here we want to craft a query that selects all reminders lists, along with a count of the reminders in each list, and then selects that data into the RemindersListRows type. This can be done in standard SQL using joins and groups, and our query builder makes it very easy to construct this query in a type-safe and schema-safe manner.

— 4:40

We can start with the RemindersList table, ordered by title as we are currently doing: @FetchAll( RemindersList.order(by: \.title) ) var remindersListRows: [RemindersListRow]

— 4:46

But this returns RemindersList s, not RemindersListRow s, and so to bundle things up into a row we will use select . We can do so by constructing a RemindersListRows.Columns value, which is a generated type from the @Selection macro, and providing the reminders list, as well as a stub count: @FetchAll( RemindersList .order(by: \.title) .select { RemindersListRows.Columns( incompleteRemindersCount: 0, remindersList: $0 ) } ) var remindersListRows: [RemindersListRow]

— 5:45

And now we can even omit the type information: @FetchAll( RemindersList .order(by: \.title) .select { RemindersListRows.Columns( incompleteRemindersCount: 0, remindersList: $0 ) } ) var remindersListRows

— 6:04

And things are already building, but of course nothing has changed in the view because we still have the count hardcoded to zero.

— 6:22

Next we can join this table to the Reminder table on the constraint that the list’s ID should equal the reminder’s foreign key: @FetchAll( RemindersList .order(by: \.title) .join(Reminder.all) { $0.id.eq($1.remindersListID) } .select { RemindersListRows.Columns( incompleteRemindersCount: 0, remindersList: $0 ) } ) var remindersListRows

— 7:51

With this join in place, the select now has access to both schemas, so we can get the count by calling the aggregate function against the reminders table: @FetchAll( RemindersList .order(by: \.title) .join(Reminder.all) { $0.id.eq($1.remindersListID) } .select { RemindersListRows.Columns( incompleteRemindersCount: $1.count(), remindersList: $0 ) } ) var remindersListRows

— 8:17

But now our preview reloads without any lists. This is because a regular join will only select lists that have at least one reminder, but we want to make sure to select all lists. The tool for this is left joins: @FetchAll( RemindersList .order(by: \.title) .leftJoin(Reminder.all) { $0.id.eq($1.remindersListID) } .select { RemindersListRows.Columns( incompleteRemindersCount: $1.count(), remindersList: $0 ) } ) var remindersListRows

— 8:41

And now the preview loads, but only one of the three lists appear.

— 8:44

We need to group the results by the ID of the reminders list so that count the reminders on a per-list basis rather than just the total number of reminders in the database. Typically groupings go at the end of a SQL query: @FetchAll( RemindersList .group(by: \.id) .order(by: \.title) .leftJoin(Reminder.all) { $0.id.eq($1.remindersListID) } .select { RemindersListRows.Columns( incompleteRemindersCount: $1.count(), remindersList: $0 ) } ) var remindersListRows

— 9:00

And now we’re back to seeing all 3 lists in our preview.

— 9:12

This is an incredibly powerful query! In one single query we are computing an aggregate across the joining of two tables, and we aren’t needing to load all the data into memory and decode the data into data types just to compute it manually. We are letting SQL do the heavy lifting because this is where SQL excels.

— 9:38

And you may be put off by having such a complex query directly in an observable model like this, and you are of course free to extract it out to a helper, but we personally think this is pretty great. The query that is powering our feature’s state is sitting right next to the declaration of the state. We don’t have to search around in this file, or worse somewhere else in the codebase, just to figure out the logic that loads this data. So we are going to stick with this style for the rest of this series, but rest assured this query can be put into a helper and stored elsewhere if desired.

— 10:16

If we run the app we will see, well, the same thing we’ve always been seeing. All the lists with a bunch of 0s next to each list. Well, this is just because we don’t have any reminders in our database. But also, we don’t have a way to add reminders yet. This sounds like the perfect job for our seed function.

— 10:31

After creating our 3 lists we can create a variety of reminders assigned to these lists. I will just copy-and-paste the reminders here: private func seed(database db: Database) throws { try db.seed { … Reminder( id: 1, notes: "Milk\nEggs\nApples\nOatmeal\nSpinach", remindersListID: 1, title: "Groceries" ) Reminder( id: 2, dueDate: Date().addingTimeInterval(-60 * 60 * 24 * 2), isFlagged: true, remindersListID: 1, title: "Haircut" ) Reminder( id: 3, dueDate: Date(), notes: "Ask about diet", priority: .high, remindersListID: 1, title: "Doctor appointment" ) Reminder( id: 4, dueDate: Date().addingTimeInterval(-60 * 60 * 24 * 190), isCompleted: true, remindersListID: 1, title: "Take a walk" ) Reminder( id: 5, dueDate: Date(), remindersListID: 1, title: "Buy concert tickets" ) Reminder( id: 6, dueDate: Date().addingTimeInterval(60 * 60 * 24 * 2), isFlagged: true, priority: .high, remindersListID: 2, title: "Pick up kids from school" ) Reminder( id: 7, dueDate: Date().addingTimeInterval(-60 * 60 * 24 * 2), isCompleted: true, priority: .low, remindersListID: 2, title: "Get laundry" ) Reminder( id: 8, dueDate: Date().addingTimeInterval(60 * 60 * 24 * 4), isCompleted: false, priority: .high, remindersListID: 2, title: "Take out trash" ) Reminder( id: 9, dueDate: Date().addingTimeInterval(60 * 60 * 24 * 2), notes: """ Status of tax return Expenses for next year Changing payroll company """, remindersListID: 3, title: "Call accountant" ) Reminder( id: 10, dueDate: Date().addingTimeInterval(-60 * 60 * 24 * 2), isCompleted: true, priority: .medium, remindersListID: 3, title: "Send weekly emails" ) } }

— 11:00

But there’s a variety of completed and uncompleted reminders, as well as high priority, low priority and no priority, and a variety of notes and lists.

— 11:14

And with that we can run the app to see that now we see some counts appearing!

— 11:28

And let’s see how to update our tests for this new behavior. First, tests are not compiling, but that’s only because we renamed the remindersLists property to remindersListRows . So let’s update those real quick…

— 12:01

Now when we run tests we see they fail, but that’s because new snapshots were recorded since the data in our feature has changed, not only in the content of the data, but even the shape of the data. The data we are snapshotting is now a list along with the count of completed reminders…

— 12:47

And this diff makes it very clear that the only thing that was added to the diff was the new counts. All of the information about the lists being displayed as stayed the same.

— 14:00

However, now that I look at this data, there is one thing I think is wrong. We can see that the lists have reminders counts of 5, 2 and 3, which add up to 10. And 10 is the total number of reminders we are seeding the database with. But the query should only compute the number of uncompleted reminders, and some of the reminders we seeded are definitely completed.

— 14:35

There are a few ways we can accomplish, and we showed off some of those ways during our SQL Building series, but now we want to show a simpler way. We can put the condition of only selecting the incomplete reminders directly into the join clause: .leftJoin(Reminder.all) { $0.id.eq($1.remindersListID) && !$1.isCompleted }

— 15:04

This guarantees that all lists will be selected, thanks to the leftJoin , even if the list contains only completed reminders or not reminders at all. Whereas if we put the condition outside of the join constraint: .leftJoin(Reminder.all) { $0.id.eq($1.remindersListID) } .where { !$1.isCompleted }

— 15:17

This will first join the lists table to the reminders table, and making sure to take all lists since we are using a leftJoin . But then the where clause will discard any lists that do not have any incomplete reminders. And we do not want that.

— 15:34

Now when we re-run tests we see that they fail because new snapshots were recorded. But in the diff of the failures we can clearly see that the counts went down a little bit since we are now counting only the incomplete reminders.

— 16:08

And so our final beast of a query is the following: @FetchAll( RemindersList .group(by: \.id) .order(by: \.title) .leftJoin(Reminder.all) { $0.id.eq($1.remindersListID) && !$1.isCompleted } .select { RemindersListRows.Columns( remindersCount: $1.id.count(), remindersList: $0 ) } ) var remindersListRows It seems like a lot, but it’s also very powerful, and we have complete test coverage over it. Creating reminders lists

— 16:26

We think this is pretty incredible. We are executing quite a complex query to populate the reminders lists in our view, along with the count of completed reminders in each list. And we are able to specify this query right next to the state itself. This is something that unfortunately SwiftData cannot do, in multiple senses:

— 16:43

For one, complex queries can rarely be declared inline with the @Query macro. Only the simplest kind of queries can be, and all others must be defined elsewhere.

— 16:53

And second, SwiftData is not currently capable of performing joins with aggregations on its models, and so this forces you to either load all data into memory and aggregate yourself, or dip into the older CoreData APIs and learn its special, proprietary querying language. Stephen

— 17:11

OK, now that we have lists showing, with their reminders counts, let’s work on allowing the user to actually create lists from scratch, because we still can’t do that. We are only seeing the lists that we seeded when the app first launched.

— 17:23

This will give us an opportunity to explore a wonderful feature of our StructuredQueries library, and in particular how it deals with tables that have a primary key.

— 17:31

Let’s begin!

— 17:34

I’m going to start by creating a new file to hold the feature for creating a new list:

— 17:44

And I’m going to paste in some scaffolding for a form that can allow one to edit the details of a RemindersList : import SwiftUI struct RemindersListForm: View { var body: some View { Form { Section { VStack { TextField("List Name", text: <#.constant("")#>) .font(.system(.title2, design: .rounded, weight: .bold)) .foregroundStyle(<#Color.blue#>) .multilineTextAlignment(.center) .padding() .textFieldStyle(.plain) } .background(Color(.secondarySystemBackground)) .clipShape(.buttonBorder) } ColorPicker("Color", selection: <#.constant(Color.blue)#>) } .navigationBarTitleDisplayMode(.inline) .toolbar { ToolbarItem { Button("Save") { <#Save action#> } } ToolbarItem(placement: .cancellationAction) { Button("Cancel") { <#Cancel action#> } } } } } #Preview { Form { } .sheet(isPresented: .constant(true)) { NavigationStack { RemindersListForm () } .presentationDetents([.medium]) } }

— 17:58

And there’s a preview set up so that we can see how this looks so far.

— 18:07

We are also going to go above and beyond with this view. It is not only going to service us creating a new reminders list from scratch, but it is also going to allow the user to edit an existing list. We want to get 2 for the price of 1 with this view.

— 18:20

So, where do we begin? Well, we certainly need some state for the user to edit, and so maybe we can use @State : struct RemindersListForm: View { @State <#???#> }

— 18:32

But what kind of state should we hold onto? We could of course hold onto a full RemindersList value: @State var remindersList: RemindersList

— 18:35

That would allow us to start deriving bindings to this state so that the user can edit it in the form. For example, the title of list: TextField("List Name", text: $remindersList.title)

— 18:52

We can also update the color of the text field based on the selected color of the reminders list using the Color.init(hex:) helper we defined in another file: .foregroundStyle(Color(hex: remindersList.color))

— 19:09

Next we’d like to be able bind the reminders list’s color to the color picker: ColorPicker("Color", selection: $remindersList.color)

— 19:19

But this doesn’t work because currently we are storing the color as a simple integer, which is the hex representation of the color, for example: remindersList: RemindersList( id: 1, color: 0x4a99ef_ff, title: "Personal" )

— 19:26

However, the ColorPicker view in SwiftUI takes a binding to an actual SwiftUI Color type. This means we need a way to convert our integer color into a SwiftUI Color , and if we bake this transformation into a computed property with a get and set , we will be able to leverage dynamic member lookup to automatically derive a binding for the color: ColorPicker("Color", selection: $remindersList.color.swiftUIColor)

— 19:32

We will paste the implementation of this computed property because while kinda interesting, it isn’t really important for the work we are doing right now: extension Int { var swiftUIColor: Color { get { Color(hex: self) } set { guard let components = UIColor(newValue).cgColor.components else { return } let r = Int(components[0] * 0xFF) << 24 let g = Int(components[1] * 0xFF) << 16 let b = Int(components[2] * 0xFF) << 8 let a = Int( (components.indices.contains(3) ? components[3] : 1) * 0xFF ) self = r | g | b | a } } }

— 20:00

With that done things are compiling. It is also worth mentioning that it would be possible to employ tricks like we did for holding dates in our tables. Remember that SQLite does not support a native date type, and so the way we are allowed to hold onto a date in our table is by annotating the field with a query representation: @Column(as: Date.ISO8601Representation?.self) var dueDate: Date?

— 20:30

This tells our library that we want to hold an actual Swift Date in our Swift data type, but secretly behind the scenes that date will be encoded to a string that is actually stored in the database. And conversely the string will be decoded into a Swift Date when fetching data from the database.

— 20:44

This machinery is extensible outside of the library too. You can create your own QueryRepresentable types to convert back and forth between native Swift data types and native SQLite types. For example, it would be possible to create a kind of HexRepresentation that allows us to store a native SwiftUI Color in our model, yet secretly under the hood it is stored as an integer in the database: @Column(as: Color.HexRepresentation.self) var color: Color

— 21:08

That would simplify our view even more, and we wouldn’t need this computed property. In fact, the work being done in the computed property is basically what we need to do to implement this HexRepresentation . But we are going to dive into those topics later and not get bogged down by them for right now.

— 21:39

OK, we now have all parts of the RemindersList type bound to this form so that the user can make edits to the title and the color. Next we need to implement the logic of this feature. Really the only logic is that the user can tap the “Save” button to commit the changes they have made to the list. We already have a button in place, we just need to implement its action closure: Button("Save") { }

— 22:01

In this action closure we want to make a write to the database, and to do that we need access to the database. Previously, in the RemindersListModel , we got access to the database by using the @Dependency property wrapper. That property works just fine in a view too, so let’s add it: struct RemindersListForm: View { @Dependency(\.defaultDatabase) var database … }

— 22:27

Then we can use that database to start a write transaction by invoking GRDB’s write method: database.write { db in }

— 22:36

Then inside this trailing closure we can construct an insert SQL statement to execute by using our StructuredQueries library. This is the first time we have come across this, but to start there is an insert static method on our table: RemindersList.insert

— 22:50

And it acts as an entry point into a SQL

INSERT 23:05

But it’s also possible to pass along a fully formed table record and the full

INSERT 23:18

Finally we will use GRDB to actually execute this query: Button("Save") { try database.write { db in try RemindersList.insert(remindersList) .execute(db) } }

INSERT 23:27

And we are not currently in a failing context, so we can’t throw an error here. But also at this moment we don’t expect there to be any errors thrown that the user can act upon. Instead, any error thrown would just be a programmer error, and something we should take care of instead of notifying the user.

INSERT 23:33

And so for this reason we will wrap the whole thing in withErrorReporting in order to notify us, the developers, of any errors: Button("Save") { withErrorReporting { try database.write { db in try RemindersList.insert(remindersList) .execute(db) } } }

INSERT 23:43

And let’s quickly go above and beyond here. We know that after saving the list we will want to dismiss the sheet holding this view, so let’s add the dismiss environment value: struct RemindersListForm: View { @Environment(\.dismiss) var dismiss … }

INSERT 23:56

And we can invoke it after the work in the “Save” button: Button("Save") { … dismiss() }

INSERT 24:01

As well as in the “Cancel” button: Button("Cancel") { dismiss() }

INSERT 24:03

OK, let’s now try running the preview to see if everything works. I’ll edit the details of the list, and then hit “Save”, and well: StructuredQueriesGRDBCore/DefaultDatabase.swift:42: A blank, in-memory database is being used. To set the database that is used by ‘SharingGRDB’ in a preview, use a tool like the ‘dependency’ trait: #Preview( trait: .dependency(\.defaultDatabase, try DatabaseQueue(/* ... */)) ) { // ... }

INSERT 24:16

We again forgot to prepare the database, but luckily there is a very visible and clear message letting us know what went wrong. So, let’s prepare the database: #Preview { let _ = prepareDependencies { $0.defaultDatabase = try! appDatabase() } … }

INSERT 24:45

Now when we try to save the list we again run into a problem, but this time it’s different: 0.000s INSERT INTO "remindersLists" ("id", "color", "title") VALUES (1, 83934719, 'Personal') 0.000s ROLLBACK TRANSACTION ModernPersistence/RemindersListFormFeature.swift:29: Caught error: SQLite error 19: UNIQUE constraint failed: remindersLists.id - while executing INSERT INTO "remindersLists" ("id", "color", "title") VALUES (?, ?, ?)

INSERT 24:57

This is letting us know that when trying to insert our list into the database we are getting a unique constraint failure. This is an example of one of those programmer errors I alluded to a moment ago. We are getting this because we are doing something wrong, not the user, and so this is exactly the kind of thing we should not be notifying the user about.

INSERT 25:13

The problem is that are starting the preview with a reminder that has ID 1: RemindersListForm( remindersList: RemindersList( id: 1, color: 0x4a99ef_ff, title: "Personal" ) )

INSERT 25:17

But also, before the preview starts we are seeding the database with a bunch of data, and there is already a list created with ID 1. So, when we try to insert another list with ID 1 SQLite has no choice but to throw an error since we told SQLite the ID column of the table was unique.

INSERT 25:27

I guess one way to guarantee that our preview reminder doesn’t match anything in the seeds would be to just set a super large ID: RemindersListForm( remindersList: RemindersList( id: 3984729836, color: 0x4a99ef_ff, title: "Personal" ) )

INSERT 25:34

Now when we run the preview it does work correctly. We can make some edits, hit “Save”, and we see that no errors are thrown and we can even look in the logs to see exactly what query was created by the StructuredQueries library: 0.000s BEGIN IMMEDIATE TRANSACTION 0.000s INSERT INTO "remindersLists" ("id", "color", "title") VALUES (3984729836, 1251602431, 'Personal') 0.000s COMMIT TRANSACTION

INSERT 25:39

And this now executes just fine because the ID we are inserting doesn’t already exist in the table. Integrating into parent

INSERT 25:44

OK, we now have the basics of a working RemindersListForm . We are able to edit the properties of a locally held piece of RemindersList state by binding to its properties directly. And when the “Save” button is tapped we will execute a SQL query to insert that list into the database. Brandon

INSERT 26:00

Now let’s integrate this form into the parent feature so that we can actually navigate to it in a sheet, enter the details of a new list, and then save it.

INSERT 26:09

We will start with some domain modeling in the observable model we previously created.

INSERT 26:13

Let’s take a look.

INSERT 26:15

We need to add some state to our feature in order to represent when SwiftUI should present and dismiss a sheet for the form. And because we have an @Observable model already, we will add the state there. We could use a boolean, but because the RemindersListForm view requires us to pass a fully formed RemindersList to it, we should use an optional to represent when to present the sheet: @Observable class RemindersListsModel { var remindersListForm: RemindersList? … }

INSERT 27:11

That way when this value because non- nil we can construct a RemindersListForm with the value and present the sheet: .sheet(item: $model.remindersListForm) { remindersList in NavigationStack { RemindersListForm(remindersList: remindersList) .navigationTitle("New List") } .presentationDetents([.medium]) }

INSERT 27:41

But, to be able to derive a binding to the remindersListForm property on the model we have to mark the model as @Bindable : struct RemindersListsView: View { @Bindable var model: RemindersListsModel … }

INSERT 28:12

So, that takes care of state-driven navigation in our feature. Now we just have to actually populate the state in order to make the sheet appear. We will keep as much of the logic of our feature in the @Observable model as possible, and so when the “Add List” button is tapped we will just invoke a method on the model: Button { model.addListButtonTapped() } label: { Text("Add List") .font(.title3) }

INSERT 28:39

And add a new method to the model class: func addListButtonTapped() { }

INSERT 28:48

And here we will want to populate the remindersListForm state in order to communicate to SwiftUI that it is time to present a sheet: func addListButtonTapped() { remindersListForm = RemindersList( id: <#Int#> ) }

INSERT 28:55

But to construct a RemindersList we have to provide an ID. It’s actually the only required property because all other properties have defaults. But what are we supposed to use: func addListButtonTapped() { remindersListForm = RemindersList( id: <#???#> ) }

INSERT 29:09

This is similar to the problem we ran into in the preview of the form. We were forced to provide an ID, and when we provided a value of 1 it caused a constraint failure with existing data, and so we were forced to pick some random large number. But clearly we don’t want to do that here.

INSERT 29:33

Ideally we would be allowed to create a RemindersList with no ID: func addListButtonTapped() { remindersListForm = RemindersList() }

INSERT 29:37

And then when this value is inserted into the database SQLite would be able to figure out what it’s ID is supposed to be by virtue of the fact that the “id” column is an AUTOINCREMENT PRIMARY KEY .

INSERT 29:54

However, this forces us into a situation where we have to make the ID in our table optional: @Table struct RemindersList: Equatable, Identifiable { let id: Int? … } That is the only way to be able to construct this value without specify the ID and letting it be determined by SQLite.

INSERT 30:02

But even worse, we also need to make the field a var if we want to be able to leave the field off when initializing a list: @Table struct RemindersList: Equatable, Identifiable { var id: Int? … }

INSERT 30:21

But now this is problematic for many reasons. First of all, needing to make the property a var is unfortunate because the ID of a record really should not be immutable. It should be fully determined by the database, and we should not be allowed to change it in our app code. Doing so can lead to some really surprising behavior and bugs that are hard to track down.

INSERT 30:46

And second, optional IDs leak complexity in our app. Anytime we need to use the ID of a record we have to unwrap its value and consider what it means for it to be nil . And optional IDs make Identifiable conformances really tricky too. This makes it so that all values with a nil ID are considered to have the same identity, even if the values really do not represent the same row in the database.

INSERT 31:35

And so it would be really unfortunate if we made the id property optional.

INSERT 31:39

And remember, one of our stated goals of this “Modern Persistence” series is to allow our Swift data types be as pristine as possible, and then figure out how to best represent it in the database. We did this for dates by using the ISO-8601 representation that allowed us to hold a date in our Swift data type, but in the database it is stored as a string. And we also alluded to doing this with colors by allowing us to store SwiftUI colors in our Swift data types, but secretly the color was stored as an integer in the database.

INSERT 32:11

And let’s not make id optional or mutable: @Table struct RemindersList: Equatable, Identifiable { let id: Int

INSERT 32:22

And this is a long standing problem in databases when it comes to modeling your tables with Swift data types, and we feel that we have a really nice solution. When the @Table macro sees that we have an id property in our data type, it interprets that property to be the “primary key” of the table. That means that property is unique amongst all rows in the table.

INSERT 32:50

Such tables are enhanced with extra super powers by our library. Secretly, under the hood, the macro generated a whole new data type nested inside RemindersList called Draft . It holds onto all of the same property as RemindersList , but , crucially, its id property is marked as optional. We can even expand the @Table macro again to see the Draft typing sitting inside: 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, Int>("color", keyPath: \QueryValue.color) 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) let color = try decoder.decode(Int.self) self.title = try decoder.decode(Swift.String.self) ?? "" guard let color else { throw QueryDecodingError.missingRequiredColumn } self.color = color } public init(_ other: RemindersList) { self.id = other.id self.color = other.color self.title = other.title } public init( id: Int? = nil, color: Int, title: Swift.String = "" ) { self.id = id self.color = color self.title = title } }

INSERT 33:16

And we can see the id right in there: let id: Int?

INSERT 33:24

And it provides an initializing that allows us to create the draft without the ID: public init( id: Int? = nil, color: Int, title: Swift.String = "" ) { … }

INSERT 33:39

This means we can instantiate a draft just like how we wanted to do for the RemindersList by omitting the id : func newReminderButtonTapped() { remindersListForm = RemindersList.Draft() }

INSERT 33:48

This is looking promising, but of course it does not work yet. We have to update the state in our observable model to allow us to drive navigation by an optional RemindersList.Draft instead of a just a plain RemindersList : @Observable class RemindersListsModel { var remindersListForm: RemindersList.Draft? … }

INSERT 34:06

And then in order for a sheet to be driven off of this optional state, we have to make it Identifiable : extension RemindersList.Draft: Identifiable {}

INSERT 34:33

Now I know what you are thinking right now. Just a moment ago we went off on why optional IDs should never define an Identifiable conformance, and here we are doing it for drafts.

INSERT 34:45

Well, we feel this is a bit different. The Draft type is a macro generated type that represents a piece of scratch state that has not yet been saved to the database, and its primary purpose is just to allow inserting and editing records. This type is not meant to be used all across the code base. And so for this reason we feel that it is OK to add an Identifiable conformance, and it will serve us well here.

INSERT 35:20

Next let’s hop over to the RemindersListForm and update it to hold onto a draft instead of a full RemindersList : struct RemindersListForm: View { @State var remindersList: RemindersList.Draft … }

INSERT 35:53

And now this means we can drop the ID from the preview value, which is what gave us trouble earlier: RemindersListForm( remindersList: RemindersList.Draft( // id: 3984729836, color: 0x4a99ef_ff, title: "Personal" ) )

INSERT 36:06

However, and this is really annoying, we can no longer use the #Preview macro. Unfortunately macro generated code cannot see other macro generated code. And that means that the code inside the #Preview macro does not know that our @Table macro has generated a Draft type, and hence we are getting a compiler error.

INSERT 36:28

This is something that affects all macros and the #Preview macro. Basically if you need to interact with macro generated code to construct your preview, there is a good chance you will not be able to use the #Preview macro.

INSERT 36:45

But luckily we can go back to using the older style PreviewProvider protocol, which works basically the same: struct RemindersListFormPreviews: PreviewProvider { static var previews: some View { let _ = prepareDependencies { $0.defaultDatabase = try! appDatabase() } Form { } .sheet(isPresented: .constant(true)) { NavigationStack { RemindersListForm( remindersList: RemindersList.Draft( color: 0x4a99ef_ff, title: "Personal" ) ) } .presentationDetents([.medium]) } } }

INSERT 37:14

Now when we go back to the list feature, run it, tap “Add List”, fill in some details and hit “Save”, we see something a bit more promising. A list was actually inserted into the list in the background. And if we look at the logs we see an insert statement was executed: 0.000s BEGIN IMMEDIATE TRANSACTION 0.000s INSERT INTO "remindersLists" ("id", "color", "title") VALUES (NULL, 1251602431, 'Secret Project') 0.000s COMMIT TRANSACTION …but crucially it has

NULL 38:04

Further, immediately after this INSERT in the logs we see a SELECT statement: 0.000s BEGIN DEFERRED TRANSACTION 0.000s SELECT count("reminders"."id") AS "remindersCount", "remindersLists"."id", "remindersLists"."color", "remindersLists"."title" AS "remindersList" FROM "remindersLists" LEFT JOIN "reminders" ON (("remindersLists"."id" = "reminders"."remindersListID") AND NOT ("reminders"."isCompleted")) GROUP BY "remindersLists"."id" ORDER BY "remindersLists"."title" 0.000s COMMIT TRANSACTION This reloads all of the lists and the counts of reminders in each list. This is happening because our @FetchAll property wrapper observes changes to the tables so that it can re-execute its query when data is inserted, deleted or modified. This is what makes sure that our views are kept in sync with the state of our database.

NULL 38:17

And it’s worth reflecting on the fact that our @FetchAll property wrapper lives in an @Observable model: @Observable class RemindersListsModel { … @ObservationIgnored @FetchAll( RemindersList .group(by: \.id) .order(by: \.title) .leftJoin(Reminder.all) { $0.id.eq($1.remindersListID) && !$1.isCompleted } .select { RemindersListRows.Columns( remindersCount: $1.id.count(), remindersList: $0 ) } ) var remindersListRows: [RemindersListRows] … } …and yet it still behaves the way we would hope. This is not true of SwiftData. The property wrappers and macros in SwiftData only work when installed directly in a view. You have no choice but to keep all state in your view, whether that is appropriate or not for your application. Whereas our tools work in both observable models and views, which is pretty great. Editing reminders lists

NULL 38:47

Things are starting to look really nice. We now have the ability to present a sheet for entering the details of a new list, we can hit “Save”, the sheet dismisses, a write is made directly to the database, and the view magically observes those changes to update its UI to show the new list. It’s all just working seamlessly even though we are using the data fetching tools in an observable model instead of a view. And the tools work just fine if used in a view too. It’s really up to you! Stephen

NULL 39:17

OK, we can now create new lists from scratch, but we don’t yet have a way to edit an existing list. This is something the native Apple Reminders app can do, so let’s see what it takes. This will give us a chance to show off a whole new super power of the StructuredQueries library called “drafts”.

NULL 39:35

Let’s take a look.

NULL 39:37

The way we are going to enable editing a list’s details is the same as how the official Apple Reminders app does things. You can swipe on the row of a list to expose an info button and a delete button. The way one does this in SwiftUI is via the swipeActions view modifier, which can be invoked directly on the row in the ForEach : .swipeActions { }

NULL 39:58

And we will add two buttons, a delete button and an info button, from which we will let the model know when they are tapped: Button { model.deleteButtonTapped(remindersList: state.remindersList) } label: { Image(systemName: "trash") } .tint(.red) Button { model.infoButtonTapped(remindersList: state.remindersList) } label: { Image(systemName: "info.circle") }

NULL 40:40

We just need to define these endpoints. First we can update the deleteButtonTapped method to take a list instead of an index set: func deleteButtonTapped(remindersList: RemindersList) { withErrorReporting { try database.write { db in try RemindersList.delete(remindersList).execute(db) } } }

NULL 41:09

And we will add a method on the model that can be called when the info button is tapped: func editButtonTapped(remindersList: RemindersList) { remindersListForm = remindersList } However, this doesn’t work: Cannot convert value of type ‘RemindersList’ to expected argument type ’RemindersList.Draft’ …because the RemindersListForm takes a draft, not an honest RemindersList .

NULL 41:19

However, this is a completely natural thing to happen. The Draft type comes with an initializer for constructing a draft from an honest value: RemindersListForm(remindersList: RemindersList.Draft(remindersList)) And we feel this is the right way to go. We model the most appropriate data in each of our domains, using a RemindersList for the remindersListForm state while using a Draft for the RemindersListForm , and then at the touch points we convert from one type to another when appropriate.

NULL 41:56

Everything is now compiling, and so let’s give it a spin. If we tap the info button for one of the lists, make a small edit, and then tap “Save”, well… it did not work. The change we made is not reflected in the view, and if we look in the logs we will see why: 0.000s BEGIN IMMEDIATE TRANSACTION 0.000s INSERT INTO "remindersLists" ("id", "color", "title") VALUES (1, 1251602431, 'Family') 0.000s ROLLBACK TRANSACTION ModernPersistence/RemindersListFormFeature.swift:30: Caught error: SQLite error 19: UNIQUE constraint failed: remindersLists.id - while executing INSERT INTO "remindersLists" ("id", "color", "title") VALUES (?, ?, ?)

NULL 42:46

This is happening because we are trying to insert a whole new row with all of the information of the existing list but with the title tweaked: 0.000s INSERT INTO "remindersLists" ("id", "color", "title") VALUES (1, 1251602431, 'Family')

NULL 43:00

This is causing a unique constrained failure because we are trying to insert a list with ID 1 even though such a list already exists. And really we shouldn’t be executing an

UPDATE 43:13

Again this is a programmer error. We should not be notifying our user of this problem because there is nothing they can do to fix it. It is entirely on us to fix it.

UPDATE 43:21

And to fix it I guess one thing we could try is to check if the id of the remindersList is nil , and if so we interpret that to mean we are inserting a new list. And otherwise we interpret it to mean that we are updating an existing list, which we can do with the update static method on RemindersList : try database.write { db in if remindersList.id == nil { try RemindersList.insert(remindersList) .execute(db) } else { try RemindersList.update(remindersList) .execute(db) } }

UPDATE 43:45

However, StructuredQueries comes with a special tool that takes care of this logic for us, and it’s called upsert , which employs SQLite’s “upsert” clause to update a row in the database when it encounters a conflict. try RemindersList.upsert(remindersList) .execute(db)

UPDATE 44:04

That right there is all it takes to execute a single SQL statement that will try inserting the list into the database, and if there is a conflict it will then update the existing record with the data we are trying to insert.

UPDATE 44:12

Now when we run the preview, edit an existing list, and hit “Save”, we see that the root list of lists does update correctly, and the following query is logged to the console: 0.000s BEGIN IMMEDIATE TRANSACTION 0.000s INSERT INTO "remindersLists" ("id", "color", "title") VALUES (1, 1251602431, 'Family') ON CONFLICT (”id”) DO UPDATE SET "color" = "excluded"."color", "title" = "excluded"."title" 0.000s COMMIT TRANSACTION …which shows that when there is a conflict we will update the existing row with the columns from the row that was prevented from being inserted. And again, right after this query is executed we see our

SELECT 45:03

Which proves that @FetchAll is observing the database correctly and making sure that the view is kept up-to-date with what is in the database. However, the update was a bit jarring, it’d be nicer if there were a smooth animation during these updates, and it’s quite easy to do. You can provide a SwiftUI animation directly to the @FetchAll property wrapper: @ObservationIgnored @FetchAll( RemindersList .group(by: \.id) .order(by: \.title) .leftJoin(Reminder.all) { $0.id.eq($1.remindersListID) && !$1.isCompleted } .select { RemindersListRows.Columns( remindersCount: $1.id.count(), remindersList: $0 ) }, animationL: .default ) var remindersListRows: [RemindersListRows] And when we re-run things, updates and deletions smoothly animate! Next time: reminders

SELECT 45:44

Our little reminders app is starting to really take shape. We now have the ability to display the reminders lists along with a count of the number of incomplete reminders lists. And we did that in a single, simple SQL query which unfortunately is not possible to do in SwiftData. And we have the ability to create new lists of reminders, edit existing lists of reminders, all the while the view is observing changes to the database and animating changes.

SELECT 46:03

And if that wasn’t cool enough, we are able to use all the tools of our libraries in an @Observable model, which means everything we’ve done so far is 100% testable, and sadly this is not the case for SwiftData. Brandon

SELECT 46:22

So, we truly are starting to see what it means to have a “modern persistence” system in an iOS application. We get to leverage all of the powers of SQL, and let it shine, while also being able to leverage the powers of SwiftUI, and let it shine.

SELECT 46:37

But we currently do not have a way to see the reminders that are in each list, nor do we have a way to create new reminders or edit existing ones. And in the process of implementing these features we are going to come across all types of advanced topics in crafting complex SQL queries, so let’s dig in…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 0325-modern-persistence-pt3 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 .