Video #328: Modern Persistence: Advanced Aggregations

Episode: Video #328 Date: Jun 16, 2025 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep328-modern-persistence-advanced-aggregations

Description

We conclude our series on “modern persistence” with advanced queries that leverage reusable SQL builders, “safe” SQL strings, and complex aggregations, including JSON arrays and a query that selects many stats in a single query.

Video

Cloudflare Stream video ID: 720d8bc4b017396f1d8696f1c0b2f566 Local file: video_328_modern-persistence-advanced-aggregations.mp4 *(download with --video 328)*

References

Transcript

— 0:05

Alright, our remake of Apple’s reminder app is really starting to take shape. We now have a root collection of all the reminders lists in our app. We can create a new list or delete any of our lists if we want. Then we can tap a list to drill down into that list and see all the reminders associated with it. And in that detail we are able to sort and filter the lists, and those settings are even saved on a per list basis. And finally, we are able to create new reminders, update existing reminders, as well as delete reminders. Brandon

— 0:35

It’s all looking great, but there are just a few more final touches we want to put on the app before ending this series. There are still some placeholders left throughout our views representing functionality that we haven’t yet figured out. There are some complex computations we haven’t dealt with, such as calculating all of the top-level stats at the root of the app. There are also some complex interactions with tags that aren’t implemented. That will give us our first exposure to many-to-many relationships, which is something that can be quite difficult to get right in SwiftData.

— 1:05

And we will get to all of that in due time, but we are going to start with something a bit easier. Right now we have a placeholder in our view for calculating the “past due” state for our reminders. You might typically think that is something that should be computed in app code, but we want to show that it is actually really great for us to leave that computation to SQLite. And this will give us our first exposure into building little reusable SQL helpers that can be pieced together in any query.

— 1:32

Let’s dig in. Query reuse: past-due

— 1:35

Let’s head over to the RemindersDetailView where we still have a few placeholders in the code: ReminderRow( color: model.detailType.color, isPastDue: <#false#>, reminder: reminder, tags: <#["weekend", "fun"]#> ) { … }

— 1:44

But before we fill them in, we are going to bump our application’s version of StructuredQueries. We have had a couple releases since we first embarked on this series of episodes, and will find some of the new features helpful in our journey.

— 2:17

One other thing that has changed since we started this series is that Xcode 16.4 was released, and it contains a new experimental feature that allows macros to be built using a precompiled version of SwiftSyntax, which can save tens of seconds to minutes depending on your build configuration. We can enable it for Xcode on the command line by writing a default: $ defaults write com.apple.dt.Xcode IDEPackageEnablePrebuilts YES

— 3:01

And we will already see that our application builds a bit faster.

— 3:11

We will also find ourselves with a single deprecation warning: @Column(as: Date?.ISO8601Representation.self) var dueDate: Date? 'ISO8601Representation' is deprecated: ISO-8601 text is now the default representation The ergonomics of the library have improved a bit in which the more common date representation is now the default, and so we can eliminate a bit of boilerplate // @Column(as: Date?.ISO8601Representation.self) var dueDate: Date?

— 3:55

With those updates out of the way, there are two arguments that need to be passed to ReminderRow that we have not yet computed. The isPastDue state needs to perform the date computation to check if the dueDate of the reminder is before the current date. And to provide the tags argument we need to somehow compute the array of tag titles when fetching the reminders.

— 4:24

It is possible to query for all of this data all at once. However, currently our model is holding onto only a simple array of reminders: @FetchAll var reminders: [Reminder]

— 4:37

When need to beef this up so that we hold onto more state. We like to design this type in isolation so that we can focus solely on what it is this feature needs to do its job.

— 4:50

So, let’s define a new type: struct Row { }

— 4:54

And this type will have properties for everything the feature needs. Certainly it will need a reminder: struct Row { let reminder: Reminder }

— 5:00

And it will need a boolean for the isPastDue state: struct Row { let isPastDue: Bool let reminder: Reminder }

— 5:05

And we want to have an array of strings for the tags: struct Row { let isPastDue: Bool let reminder: Reminder let tags: [String] }

— 5:08

And are making the decision to hold onto tags as a simple array of strings instead of an array of actual Tag values: let tags: [Tag]

— 5:16

We are doing this because we really only need the title of the tags and nothing else. And that means if in the future we add more data to tags, such as a color, description, or maybe even an image, none of that data will need to be loaded to compute this array. That will be more efficient, but if sometime later we do want that data we will just update this Row type.

— 5:53

And then finally we need to mark this type with the @Selection macro, which allows our library to be able to decode a SQL data set into the type: @Selection struct Row { let isPastDue: Bool let reminder: Reminder let tags: [String] }

— 6:03

However this does cause an error in the expanded macro code: let tags = try decoder.decode([String].self) No exact matches in call to instance method 'decode'

— 6:13

The problem here is that SQLite has a very limited set of types it supports, such as integers, text and data blogs, and an array of strings is not one of those types. Therefore our decoding infrastructure has no idea how to decode data from SQLite into this array.

— 6:31

But, we have run into this problem before. In the Reminder type we hold onto a Date , but that is a type that SQLite does not support. And so we need to either tell our library that when it encounters a date it should encode it in a particular way, or we can fall back to the default of an ISO-8601 formatted string: // @Column(as: Date.ISO8601Representation?.self) var dueDate: Date?

— 6:59

We can do the same thing for this array of strings. We will tell our library to decode this data from JSON into our array of strings: @Column(as: [String].JSONRepresentation.self) let tags: [String]

— 7:29

And this allows us to use JSON as a simple interchange format between SQLite and our data types. When constructing the query we will serialize the tag titles into a JSON array, which SQLite has lots of handy functions for accomplishing, and then our library will decode that JSON directly into this Swift array of strings.

— 7:54

Now that we have this type we want to use it everywhere in this feature. Rather than fetching an array of plain reminders, we will select an array of reminder rows: @FetchAll var rows: [Row]

— 8:23

And we will update the initializer to power this from a query: _rows = FetchAll(query)

— 8:37

And this means we need to update the query: var query: some SelectStatementOf<Row> { … }

— 8:45

But this doesn’t work because Row is not a table: Type 'RemindersDetailModel.Row' does not conform to protocol 'Table'

— 8:53

And I guess we could theoretically apply the @Table macro to it to force it into a table, but that doesn’t seem right. The Row type only exists as a convenience for bundling up multiple pieces of data from a single query. It does not represent a real table. It’s kind of like a “virtual” table, or a specialized “view” into our database.

— 9:15

So, what we can do is weaken this to just be some Statement rather than some SelectStatement : var remindersQuery: some Statement<Row> { … }

— 9:22

But then unfortunately Statement is a type that exists in both GRDB and our StructuredQueries library, and so we have to disambiguate it: var remindersQuery: some StructuredQueries.Statement<Row> { … }

— 9:30

Now we just have to update the query to actually select all of this data. But, before doing that we can at least get things compiling by stubbing out this data. To do this we can tack on a select at the end of the query: var remindersQuery: some StructuredQueries.Statement<Row> { Reminder … .select { reminder in } } In this trailing closure we have access to the schema of Reminder and we need to ultimately return a schema value for Row : var remindersQuery: some StructuredQueries.Statement<Row> { Reminder … .select { reminder in Row.Columns( isPastDue: <#isPastDue#>, reminder: <#reminder#>, tags: <#tags#> ) } }

— 10:03

For the isPastDue column we can just stub in false : Row.Columns( isPastDue: false, reminder: <#reminder#>, tags: <#tags#> )

— 10:10

For the reminder we can pass along what was given to us in the trailing closure: Row.Columns( isPastDue: false, reminder: reminder, tags: <#tags#> )

— 10:13

And for the tags we might hope we could just hard code an array of some temporary tags: Row.Columns( isPastDue: false, reminder: $0, tags: ["weekend", "fun"] )

— 10:28

However, this is not correct. The value we need to provide here is not a concrete array of tags. After all, we are not construct a Row value but rather a Row.Columns value, which is a query representation of the data that will ultimately run through SQLite.

— 10:48

And arrays of strings are not bindable or decodable on their own. But we can use the #bind macro to wrap up the value into something that is query representable, and inferable from the initializer argument: Row.Columns( isPastDue: false, reminder: $0, tags: #bind(["weekend", "fun"]) )

— 11:54

Next we need to update the updateQuery method for our new row type and query: func updateQuery() async { await withErrorReporting { try await $rows.load(query, animation: .default) } }

— 12:03

As well as the ForEach , and now we have all the data available to us to pass to ReminderRow : ForEach(model.rows) { row in ReminderRow( color: model.detailType.color, isPastDue: row.isPastDue, reminder: row.reminder, tags: row.tags ) { model.reminderDetailsButtonTapped(reminder: row.reminder) } }

— 12:35

However, the Row type is not Identifiable . It would be quite easy to do so because we could just use the underlying reminder ID as the row’s ID. But we can also just specify the ID right in the ForEach : ForEach(model.rows, id: \.reminder.id) { row in … }

— 12:54

OK, everything is now compiling, but all we’ve really done is move the stub data we had in the view to be stub data in the query. But at least we are now in a position to compute the actual value of these columns directly in our SQLite query.

— 13:09

Let’s start with the isPastDue value that is currently hard coded to false: isPastDue: false,

— 13:13

There will be two things that go into computing this. First, the reminder must be incomplete because a completed reminder has no concept of being “past due”: isPastDue: !$0.isCompleted,

— 13:39

And next we want to check that the due date of the reminder comes before the current date: isPastDue: !$0.isCompleted && $0.dueDate < Date(),

— 13:56

However this does not compile: Operator function '<' requires the types 'TableColumn<Reminder.TableColumns.QueryValue, Date?>.QueryValue' (aka 'Optional<Date>' ) and 'Date' be equivalent This is happening because we are trying to compare an optional date to an honest date. The dueDate we hold in our database is optional because we want to allow a user to not specify a due date at all, but that does complicate logic like this. We have decided not to extend the < operator to work with optional values because three-valued in SQL can be very tricky, and so we actually think this error is a good one to have.

— 14:25

And this is also how Swift works. You cannot compare an optional date with a non-optional one: var dueDate: Date? dueDate < Date() Binary operator '<' cannot be applied to operands of type ‘Date?’ and ’Date’

— 14:51

And this is for god reason, as it can be nuanced to decide how you want nil to be compared: should nil s be less than everything else? Greater than? It’s a tricky question, and SQLite has taken a stance on this as it allows for

NULL 15:49

But our Swift operator should return an honest boolean, not an optional one. StructuredQueries’ query builder embraces the Swift type system over SQLite’s. And there is a solution here in Swift, which is to coalesce things to a non-optional before doing the comparison: // 'nil' is less than everything: (dueDate ?? .distancePast) < Date() // 'nil' is greater than everything: (dueDate ?? .distanceFuture) > Date()

NULL 16:28

And we can employ this exact same trick in our builder: isPastDue: !$0.isCompleted && ($0.dueDate ?? Date.distantFuture) < Date(),

NULL 16:53

That right there is compiling, and we can run the preview to see that well, looks like every reminder is past due. This is just due to a lack of variety in our seeded reminders, so let’s fix that.

NULL 17:16

Let’s make it so that the “Doctor appointment” reminder isn’t due for 12 hours: Reminder( id: 3, dueDate: now.addingTimeInterval(60 * 60 * 12), notes: "Ask about diet", priority: .high remindersListID: 1, title: "Doctor appointment" )

NULL 17:43

Now if we re-run the preview we will see that “Buy concert tickets” is no longer red because it is due in the future.

NULL 18:05

And this isPastDue computation is probably going to be used quite a bit through our app, so wouldn’t it be nice if we could put it somewhere central so that it could be reused? Well, luckily we can!

NULL 18:23

Any type annotated with the @Table macro has an inner type constructed for it called TableColumns : extension Reminder.TableColumns { }

NULL 18:39

And this type is what constitutes the static description of the schema of the table. We can add helpers to this type so that they can be reused from elsewhere: extension Reminder.TableColumns { var isPastDue: }

NULL 18:56

But the type of the property isn’t just a plain boolean. It is going to be a whole query expression of its own that evaluates to a boolean when run in SQL: extension Reminder.TableColumns { var isPastDue: some QueryExpression<Bool> { } }

NULL 19:14

And now we can basically copy-and-paste what we are doing over in the detail query over here: extension Reminder.TableColumns { var isPastDue: some QueryExpression<Bool> { !isCompleted && (dueDate ?? Date.distantFuture) < Date() } }

NULL 19:24

And now in our query we can do something as simple as this: isPastDue: $0.isPastDue, This isPastDue helper is available to us on $0 because it was defined on Reminder.TableColumns . We can run the preview and everything works exactly as it did before.

NULL 19:43

And it’s worth mentioning that this kind of trick did not work over in SwiftData when we tried it. If you add a computed property to a model and use it in a predicate or sort descriptor, it will crash at runtime.

NULL 19:59

The difference in our case is that we are not adding this helper directly to the Reminder type. If we did that then the helper would not be accessible to us at all from the query builder. We are instead adding it to the TableColumns type, and that is accessible to us from the query builder, and it simply inserts this little SQL fragment into a larger SQL statement. Query reuse: tag aggregation

NULL 20:31

We are now performing complex date computations right in SQL so that we can fetch the most pristine version of our data to hand off to our views. And we were further able to extract out this complex computation into its own little helper so that it can be used from multiple places. And while we aren’t going to write any tests for this logic right now, it is 100% testable since our tests exercise the full stack of database and client code. Stephen

NULL 20:56

This is all looking great, but we still have a placeholder for showing tags for each reminder. In our database schema, tags are represented as a many-to-many relationship. That is, there’s a table for all of our tags, and we have our table for all reminders, and then there’s a third “join” table that simply points to the tag and reminder that are associated. Our query needs to be seriously beefed up so that we can join these 3 tables together to get all of the tags associated with a reminder, and then further aggregate the titles of those tags into a collection.

NULL 21:28

Sounds complex, but this is the kind of thing that SQL excels at. Let’s dig in.

NULL 21:35

There is still one stub we have in place when constructing the Row.Columns type, and that is the tags: Row.Columns( isPastDue: !$0.isPastDue, reminder: $0, tags: #bind(["weekend", "fun"]) )

NULL 21:42

We would prefer to dynamically load up all of the tags associated with the reminder we are fetching and extract out their titles into a JSON array. How can we do that?

NULL 21:51

Well, first of all we need access to the tags in our query to do anything with them. And remember that tags are associated with reminders through a many-to-many join table that we called ReminderTag . It’s a table that simply has two foreign keys, one pointing to a reminder and one pointing to a tag. We have to join through this table, but because not every reminder is going to have tags we should use a left join: .leftJoin(ReminderTag.all) { $0.id.eq($1.reminderID) } That will guarantee that we always get all reminders even if there are no associated tags.

NULL 22:36

And then we further join into tags, again as a left join: .leftJoin(ReminderTag.all) { $0.id.eq($1.reminderID) } .leftJoin(Tag.all) { $1.tagID.eq($2.id) }

NULL 22:55

And once that is done we now have access to both the reminders table and tags table in our select , where $0 is the reminder and $2 is the tag, from which we can pluck off its title: .select { Row.Columns( isPastDue: $0.isPastDue, reminder: $0, tags: $2.title ) }

NULL 23:08

Now the question is: how do we gather up all of the tags’ titles into a JSON array?

NULL 23:12

Well, there is a SQL aggregate function that does just this and it’s called jsonGroupArray : tags: tag.title.jsonGroupArray()

NULL 23:18

This will combine all rows together and during that process it will take the title from each tag and add it to a JSON array. The only problem is that technically tag isn’t just a regular Tag.TableColumns like we had with reminders, but rather it’s an optional TableColumns : tag: Tag?.TableColumns

NULL 23:34

This is happening because we are left joining the tags table, which means there is the very real possibility that we are querying for a row that has reminder data but no tag data.

NULL 23:46

So that means that the JSON array we are getting from this expression is not an array of strings, but rather an array of optional strings. And our Row type demands that we get an array of honest strings, not optional strings.

NULL 23:57

This is OK. We can use the same trick we used for dealing with with the optional date. We will just coalesce any nil tag titles to be an empty string: tags: (tag.title ?? "").jsonGroupArray()

NULL 24:07

Now this compiles, and the preview runs, but weirdly we only have a single reminder. This is happening because we haven’t specified a GROUP BY clause in our query, which means when we use an aggregate function, such as jsonGroupArray , it has no choice but to squash all rows into a single row.

NULL 24:25

That’s clearly not right. What we want instead is to group together only the reminders that have the same ID: Reminder .group(by: \.id) … This is correct because in the full join of reminders and tags we are going to have a bunch of rows with duplicate reminder data since a single reminder can have multiple tags. And so we will squash all of those duplicates so that we are left with just a single reminder, and then further all tags belonging to that reminder will be aggregated together into the JSON array.

NULL 24:37

With that one change our preview is back to showing all of the reminders, but weirdly we are only seeing tags with no text. There are just isolated “#” symbols floating at the end of each reminder.

NULL 24:46

This is happening for two reasons. The first (and simplest to fix) reason is that we haven’t actually seeded any tags in our database. Remember that our previews are powered by a database that is created, migrated and seeded, but we never actually took the time to create tags during that process.

NULL 25:01

So, let’s hop over to Schema.swift, and at the bottom of the seed trailing closure we will add the following: try db.seed { … Tag(id: 1, title: "weekend") Tag(id: 2, title: "fun") Tag(id: 3, title: "easy-win") ReminderTag(reminderID: 1, tagID: 1) ReminderTag(reminderID: 2, tagID: 1) ReminderTag(reminderID: 4, tagID: 1) ReminderTag(reminderID: 4, tagID: 2) ReminderTag(reminderID: 5, tagID: 2) ReminderTag(reminderID: 2, tagID: 3) ReminderTag(reminderID: 6, tagID: 3) ReminderTag(reminderID: 7, tagID: 3) ReminderTag(reminderID: 8, tagID: 3) }

NULL 25:22

Now when we run the preview things are looking better. We can clearly see that “Haircut” is tagged “#easy-win”, “Buy concert tickets” is tagged “#fun”, and “Groceries” is tagged “#weekend”.

NULL 25:31

However, “Doctor appointment” still has a floating, empty tag at the end. This is happening because that reminder doesn’t have any tags, and so when we do this in the query: tags: (tag.title ?? "").jsonGroupArray()

NULL 25:42

…we are building up an array of a single element that is an empty string. We would like to filter out this element, and luckily it’s quite easy to do in SQL.

NULL 25:50

Aggregate functions like json_group_array have an option for filtering out certain values from the aggregation: tags: ($2.title ?? "") .jsonGroupArray(filter: <#(some QueryExpression<Bool>)?#>)

NULL 25:56

And so we can just say that we only want to group the tags for which their id is not NULL since every tag should have an ID: tags: ($2.title ?? "") .jsonGroupArray(filter: $2.id.isNot(nil))

NULL 26:12

And just like that the preview is now behaving correctly. Each reminder has its tags listed, and the one reminder that has no tags does not show anything.

NULL 26:22

And it’s pretty incredible to see the query that is powering this view: 0.000s SELECT NOT ((NOT ("reminders"."isCompleted") AND (coalesce("reminders"."dueDate", '4001-01-01 00:00:00.000') < '2025-06-04 22:41:04.450'))) AS "isPastDue", "reminders"."id", "reminders"."dueDate", "reminders"."isCompleted", "reminders"."isFlagged", "reminders"."notes", "reminders"."priority", "reminders"."remindersListID", "reminders"."title" AS "reminder", json_group_array(coalesce("tags"."title", '')) FILTER (WHERE ("tags"."id" IS NOT NULL)) AS "tags" FROM "reminders" LEFT JOIN "reminderTags" ON ("reminders"."id" = "reminderTags"."reminderID") LEFT JOIN "tags" ON ("reminderTags"."tagID" = "tags"."id") WHERE NOT ("reminders"."isCompleted") AND ("reminders"."remindersListID" = 1) GROUP BY "reminders"."id" ORDER BY "reminders"."isCompleted", "reminders"."dueDate" ASC NULLS LAST

NULL 26:32

This is a massive SQL query that we are able to write in an expressive, type-safe and schema-safe API.

NULL 26:38

And if we felt that this little query fragment may be useful in other places, there’s nothing stopping us from defining a reusable helper just as we did with isPastDue . Except this time we will define it on the optional Tag?.TableColumns : extension Tag?.TableColumns { var jsonTitles: some QueryExpression<[String].JSONRepresentation> { (self.title ?? "").jsonGroupArray(filter: self.id.isNot(nil)) } } That means this helper is only useful when applied to tags as a left join table, but that is probably the most common use case and so we think that’s a fair tradeoff.

NULL 27:33

And now we can use this helper in our query to make it super short and succinct: Row.Columns( isPastDue: $0.isPastDue, reminder: $0, tags: $2.jsonTitles ) And at this point, our Swift constructing of this query 30% less characters than the actual SQL generated by our library. That is pretty incredible! Query reuse: stats

NULL 27:56

Honestly, we were able to accomplish that quite fast. We are now joining two tables to the reminders table in order to grab all the tabs associated with a reminder. Then we accumulate the titles of all of those tags into a JSON array, which is then shuffled over to Swift and decoded by our library into a Swift array of strings. And that allows us to display a preview of the tags associated with each reminder in a list, and it’s all being powered by one single query.

NULL 28:21

We don’t need to execute multiple queries to gather all of this data and then perform complex work to munge the data together into something that can be used by SwiftUI. Instead, we precisely craft our query so that it fetches the most pristine of our data, and then let SwiftUI do the rest. And sadly, this is just yet another thing that SwiftData is not capable of, and even if you are willing to drop down to CoreData it can be very difficult to accomplish. Brandon

NULL 28:49

OK, we are getting close to the end of this series, but there’s one last feature we want to implement. In the official Reminders app from Apple, the root screen of the app shows some top level stats about your reminders, such as how many are due today, how many are flagged, and so on. You can even tap on any of those stats in order to drill down to a detail screen that shows you just the reminders for today, or flagged, and so on.

NULL 29:15

Let’s see what it takes takes to compute these stats, and add more navigation to our app.

NULL 29:21

Recall that we currently have a placeholder for a bunch of stats in our root view: Section { <#Top-level stats#> }

NULL 29:31

There’s currently nothing in this section, so let’s get some more placeholders into place to understand what kind of extra data we need to be fetching from the database.

NULL 29:38

Let’s start by pasting in a little reusable view that represents a grid item that houses a state about our reminders: private struct ReminderGridCell: View { let color: Color let count: Int? let iconName: String let title: String let action: () -> Void var body: some View { Button(action: action) { HStack(alignment: .firstTextBaseline) { VStack(alignment: .leading, spacing: 8) { Image(systemName: iconName) .font(.largeTitle) .bold() .foregroundStyle(color) .background( Color.white.clipShape(Circle()).padding(4) ) Text(title) .font(.headline) .foregroundStyle(.gray) .bold() .padding(.leading, 4) } Spacer() if let count { Text("\(count)") .font(.largeTitle) .fontDesign(.rounded) .bold() .foregroundStyle(Color(.label)) } } .padding( EdgeInsets(top: 8, leading: 12, bottom: 8, trailing: 12) ) .background(Color(.secondarySystemGroupedBackground)) .cornerRadius(10) } } }

NULL 29:51

And then we will paste in a grid of these stats with some data stubbed in: Section { Grid( alignment: .leading, horizontalSpacing: 16, verticalSpacing: 16 ) { GridRow { ReminderGridCell( color: .blue, count: <#todayCount#>, iconName: "calendar.circle.fill", title: "Today" ) { <#Action#> } ReminderGridCell( color: .red, count: <#scheduledCount#>, iconName: "calendar.circle.fill", title: "Scheduled" ) { <#Action#> } } GridRow { ReminderGridCell( color: .gray, count: <#allCount#>, iconName: "tray.circle.fill", title: "All" ) { <#Action#> } ReminderGridCell( color: .orange, count: <#flaggedCount#>, iconName: "flag.circle.fill", title: "Flagged" ) { <#Action#> } } GridRow { ReminderGridCell( color: .gray, count: nil, iconName: "checkmark.circle.fill", title: "Completed" ) { <#Action#> } } } .buttonStyle(.plain) .listRowBackground(Color.clear) .padding([.leading, .trailing], -20) }

NULL 30:04

And already this is showing something quite nice in the preview! But of course all of these stats are hard coded. Let’s start executing some queries that compute these stats. We’ll start with the easiest, which is the “All” stat, which just computes the number of incomplete reminders in the entire database.

NULL 30:27

We will add this to our existing observable model because that will mean it’s even testable in the future. We can start with a @FetchOne since we only want to fetch one single result from this query rather than a whole collection of results: @FetchOne

NULL 30:43

We will provide a query that counts all incomplete reminders: @FetchOne(Reminder.where { !$0.isCompleted }.count())

NULL 31:01

We will give this variable a name and a default so that it can be non-optional: @FetchOne(Reminder.where { !$0.isCompleted }.count()) var allCount = 0

NULL 31:05

And of course we need to ignore it from the @Observable macro since macros don’t play nicely with property wrappers: @ObservationIgnored @FetchOne(Reminder.where { !$0.isCompleted }.count()) var allCount = 0

NULL 31:12

But don’t worry, @FetchOne handles all of its own observation under the hood.

NULL 31:23

With that one tiny change we can update the grid cell for this state to use this data: ReminderGridCell( color: .gray, count: model.allCount, iconName: "tray.circle.fill", title: "All" ) { <#Action#> }

NULL 31:42

And now when we run the preview the state changes from 8, which I hard coded, to 7, which is the actual number of incomplete reminders. And to really prove this to ourselves lets delete the “Business” list.

NULL 32:01

Now the count went down to 6 because there was 1 reminder in that list.

NULL 32:09

And if we delete “Family” it goes down to 4 because it had 2 reminders. And finally if we delete “Personal” it goes down to 0 because it had 4 reminders. So all stats and data being displayed in the view is being kept up-to-date with exactly what is in the database. It’s pretty incredible how quickly we got this working.

NULL 32:22

One stat down, 3 more to go. The next easiest to attack is the “Flagged” stat. That should count all reminders that are flagged, and of course we only want to count the incomplete ones: @ObservationIgnored @FetchOne(Reminder.where { !$0.isCompleted && $0.isFlagged }.count()) var flaggedCount = 0

NULL 32:40

And we can now use this state in the view: ReminderGridCell( color: .orange, count: model.flaggedCount, iconName: "flag.circle.fill", title: "Flagged" ) { <#Action#> }

NULL 32:50

And we immediately see the preview update to now show 2. And we can prove to ourselves that it will definitely live update as we mutate the database by deleting the “Family” list because that has one flagged reminder. And indeed, we instantly see the “Flagged” count go down to 1.

NULL 33:26

Let’s move on to the next easiest query, which is the “Scheduled” query. This should count all incomplete reminders that have a due date assigned, regardless of when that date is. We can construct this query by just checking if the dueDate column is not nil : @ObservationIgnored @FetchOne( Reminder .where { !$0.isCompleted && $0.dueDate.isNot(nil) } .count() ) var scheduledCount = 0

NULL 33:48

And we can start using that in the view: ReminderGridCell( color: .red, count: model.scheduledCount, iconName: "calendar.circle.fill", title: "Scheduled" ) { <#Action#> }

NULL 33:55

And instantly the view updates to show that there are 6 scheduled reminders. And if we delete the “Family” list we will see the count go do to 2.

NULL 34:15

We have just one query left and that is to count the number of reminders that are due today. This one is a bit more difficult because it involve date-based math. We need to somehow take an existing date, compute the “day” portion of it by dropping the time, and then see if that matches today. This can typically be a pain, but SQL provides some wonderful date functions that make this a breeze. And this will also give us a chance to explore a situation in which our query builder does not have everything SQL has, but that’s ok because there are escape hatches to access the underlying SQL directly.

NULL 34:57

So let’s start with the basics of at least just counting the incomplete reminders: @ObservationIgnored @FetchOne( Reminder.where { !$0.isCompleted } .count() ) var todayCount = 0

NULL 35:12

We want to add onto this where clause the condition that the dueDate ’s day is equal to today. The way one gets the “today” of a date is using the date function: // date("dueDate")

NULL 35:40

In this expression, if dueDate is a column that holds both the day and time of a date, then date will lop of the time and just leave the day.

NULL 35:45

Further, date() with no arguments returns the date of today, and so it sounds like we want to check the following: // date("dueDate") = date()

NULL 36:05

But here’s the thing. Our query builder doesn’t come with this date function. And that’s OK. Our query builder does not need to expose 100% of everything inside SQL, and in fact that may not really even be possible.

NULL 36:18

But we do provide a convenient escape hatch that lets you execute any SQL you want, but you do have to take a bit of responsibility. The escape hatch is the #sql macro: !$0.isCompleted && #sql("")

NULL 36:34

We can put any SQL fragment we want in there and it will be inserted into the larger SQL statement. However, importantly, this escape hatch does not open you up to potential SQL injection attacks. Any values interpolated into this string will still be properly prepared by the database and made safe, unless you go out of your way to use an explicitly unsafe API.

NULL 37:01

And so we copy in the literal SQL fragment we sketched out a moment ago: !$0.isCompleted && #sql("date(dueDate) = date()")

NULL 37:12

Truly that’s all it takes. If we update the view to use this state: ReminderGridCell( color: .blue, count: model.todayCount, iconName: "calendar.circle.fill", title: "Today" ) { <#Action#> }

NULL 37:20

…we will instantly see that there is exactly one reminder in our database that is due today. We can even drill into each list to see which reminder it is. It is the “Buy concert tickets” in the “Personal” list.

NULL 38:06

But, even though we are using an escape hatch into SQL string world, we can retain a bit of safety in this expression. There’s no need to hardcode the “dueDate” directly in the when we have a static symbol that will do. We can instead interpolate $0.dueDate and get the benefits of some static safety: !$0.isCompleted && #sql("date(\($0.dueDate) = date()")

NULL 38:40

And here we get to seen amazing feature of the #sql macro: Cannot find ')' to match opening '(' in SQL string, producing incomplete fragment; did you mean to make this explicit? (from macro 'sql' )

NULL 39:02

It performs a light amount of parsing of the string to make sure that all parentheses and quotes are properly matched, and it seems I forgot to close a paren: !$0.isCompleted && #sql("date(\($0.dueDate)) = date()")

NULL 39:13

So even though we are writing a SQL string, the macro has our back with respect to the schema of our tables and some light syntax checking.

NULL 39:22

There’s one more improvement we can make to our stats feature. We currently have 4 queries computing various stats about reminders, which constitute 4 different database transactions, and 4 different database observations. However, a lot of the time these values change in overlapping ways: adding or deleting a flagged reminder will affect the “all” count as well as the “flagged” count, and could affect the “today” count and “scheduled” count if scheduled for today.

NULL 39:51

So it would be a little more efficient if we could bundle up all these stats into a single query, which would be performed in a single database transaction and notify the view in a single observation.

NULL 40:03

And it turns out it is incredibly easy to do this. We can start by defining a struct that holds onto all the data we want the query to fetch as a selection: @Selection struct Stats { var allCount = 0 var flaggedCount = 0 var scheduledCount = 0 var todayCount = 0 }

NULL 40:32

And once that’s done, we can replace all our individual queries with a single @FetchOne that selects a count for each column: @ObservationIgnored @FetchOne( Reminder.select { Stats.Columns( allCount: $0.count( filter: !$0.isCompleted ), flaggedCount: $0.count( filter: !$0.isCompleted && $0.isFlagged ), scheduledCount: $0.count( filter: !$0.isCompleted && $0.date.isNot(nil) ), todayCount: $0.count( filter: !$0.isCompleted && #sql("date(\($0.dueDate)) = date()") ) ) } ) var stats = Stats()

NULL 42:14

It may seem too good to be true, but that is all it takes to refactor things! And if we update our views to go through stats and run the preview, everything works exactly as it did before. More detail screens

NULL 43:26

It’s just really incredible to see how powerful SQLite is. We are computing complex aggregations across our database in just a few lines, and those computations live update as the database changes. Anytime a reminder is added or deleted, the stats instantly update, and even when we delete a list, which cascades to deleting all of its contained reminders, the numbers again live update. And we could of course write a full test suite that proves these stats are exactly what we expect them to be, but we won’t take the time to do that right now. It’s hard to overstate just how powerful it can be to not worry about data synchronization. We get to write complex queries to build our features, and we never have to be worried about our views accidentally showing old data. Stephen

NULL 43:58

Let’s now finish things off by supporting a way to navigate to these new detail screens by tapping on the stats button in the root view.

NULL 44:07

We currently have a DetailType enum whose purpose is to enumerate all of the types of reminders we can show in the detail screen. But currently it only has one value: enum DetailType { case remindersList(RemindersList) } …which allows us to view all the reminders for a specific list.

NULL 44:21

We need to greatly expand this enum to allow for the many other types of reminders we want to see. We want to be able to see all reminders, just the completed ones, or just the flagged ones, the ones that are scheduled, and the ones that are due today: enum DetailType { case all case completed case flagged case remindersList(RemindersList) case scheduled case today }

NULL 44:43

Then we need to update some of our helpers that compute relevant information from the detail type, such as the navigation title of the view: var navigationTitle: String { switch self { case .remindersList(let remindersList): remindersList.title case .all: "All" case .completed: "Completed" case .flagged: "Flagged" case .scheduled: "Scheduled" case .today: "Today" } } And the theme color of the detail: var color: Color { switch self { case .remindersList(let remindersList): remindersList.color.swiftUIColor case .all: .black case .completed: .gray case .flagged: .orange case .scheduled: .red case .today: .blue } } As well as suffix used for the app storage key where we store bits of information about this detail view: var appStorageKeySuffix: String { switch self { case .remindersList(let remindersList): "remindersList_\(remindersList.id)" case .all: "all" case .completed: "completed" case .flagged: "flagged" case .scheduled: "scheduled" case .today: "today" } }

NULL 45:22

This means we will be allowed have our settings for showing completed reminders and sorting for each of these new detail types.

NULL 45:29

Next we need to update our query to take into consideration all of these new detail types: .where { switch detailType { case .remindersList(let remindersList): $0.remindersListID.eq(remindersList.id) case .all: case .completed: case .flagged: case .scheduled: case .today: } }

NULL 45:41

And we can just go through these on a case-by-case basis to figure out how we should augment the query. For example, in the .all case we don’t want to filter out any reminders, so we can just use true to mean take all reminders: case .all: true

NULL 45:50

For the .completed case we can filter for just the ones that are completed: case .completed: $0.isCompleted

NULL 45:54

Similarly for the .flagged case: case .flagged: $0.isFlagged

NULL 45:59

For the .scheduled case we will take all reminders that have a non- nil due date: case .scheduled: $0.dueDate.isNot(nil)

NULL 46:05

And then lastly we have the .today case. Here we need to repeat some of the logic we did for computing the count of all “today” reminders: case .today: #sql("date(\($0.dueDate)) = date()")

NULL 46:22

But, it is a bummer to have to repeat some of this nuanced logic. Let’s put this some dedicated helpers defined on Reminder.TableColumns : extension Reminder.TableColumns { … var isScheduled: some QueryExpression<Bool> { dueDate.isNot(nil) } var isToday: some QueryExpression<Bool> { #sql("date(\(self.dueDate)) = date()") } } …so that we can reuse them from multiple places. We can start right in this query: case .scheduled: $0.isScheduled case .today: $0.isToday

NULL 47:07

And we can update the count query in the root view to now use this helper: scheduledCount: $0.count( filter: !$0.isCompleted && $0.isScheduled ), todayCount: $0.count( filter: !$0.isCompleted && $0.isToday )

NULL 47:20

We’ve almost got everything compiling, but we are switching on the detailType when the “New reminder” button is tapped. We can quickly fill in the new cases: func newReminderButtonTapped() { switch detailType { case .remindersList(let remindersList): reminderForm = Reminder.Draft(remindersListID: remindersList.id) case .all: break case .completed: break case .flagged: break case .scheduled: break case .today: break } }

NULL 47:31

In reality, we should actually take the time to hide the “New reminder” button when viewing certain detail types. For example, it doesn’t really make sense to add a new reminder when viewing all completed reminders. But we will leave those little details to our viewers to fill in.

NULL 47:50

Now everything is compiling, and that is the basics of getting this to work. We will find that there are a few more tweaks we need to make, but in order to see that we need to first be able to navigate to these new detail types.

NULL 48:00

In the root view we already have a piece of optional state that drives navigation to the detail view: var remindersDetail: RemindersDetailModel?

NULL 48:05

When this state becomes non- nil the app will navigate to the detail screen.

NULL 48:09

So, we just need to populate this state when the top-level stats are tapped. Let’s create a dedicated method that represents the user tapping on a detail, whether that be a stat or a list: func detailTapped(detailType: DetailType) { remindersDetail = RemindersDetailModel(detailType: detailType) }

NULL 48:44

Then we can update the list row to invoke this method instead of the previous one we had: Button { model.detailTapped(detailType: .remindersList(row.remindersList)) } label: { … } And we can update each stats button to invoke this method with the appropriate detail type: model.detailTapped(detailType: .today) … model.detailTapped(detailType: .scheduled) … model.detailTapped(detailType: .all) … model.detailTapped(detailType: .flagged) … model.detailTapped(detailType: .completed)

NULL 49:15

OK, everything is compiling, and running the preview we will see that everything seems to mostly work. We can navigate to the “Today” detail to see that there is indeed 1 reminder due to day.

NULL 49:25

We can then navigate to the “Scheduled” detail to see that there are 6 reminders with due dates. We can also show the completed reminders in this list to see all the ones with due dates that were completed in the past. And this “Show completed” setting is localized to just this view since we incorporated the detail type into the app storage key.

NULL 49:29

Next we can go to the “All” detail and we do see all 7 incomplete reminders. And we are not showing the completed ones here even though we specifically turned on “Show completed” over in the “Scheduled” view.

NULL 49:32

Next we can go to “Flagged”, and indeed there are 2 flagged reminders. And finally we can go to the “Completed” list, and well, there are no reminders. This is happening because this detail type has the ability to show and hide completed reminders, just like all the others, and by default we hide the completed ones.

NULL 49:49

It sounds like just shouldn’t use this setting when the detailType is .completed . We can update our query to skip this setting when we are on the completed screen: .where { if detailType != .completed && !showCompleted { !$0.isCompleted } }

NULL 50:18

But we need to make the DetailType enum equatable, which is easy enough to do: enum DetailType: Equatable { … }

NULL 50:30

And further we will just hide the setting from the menu when viewing only completed reminders: if model.detailType != .completed { Button { Task { await model.toggleShowCompletedButtonTapped() } } label: { Label { Text(model.showCompleted ? "Hide Completed" : "Show Completed") } icon: { Image(systemName: model.showCompleted ? "eye.slash.fill" : "eye") } } }

NULL 50:43

And now everything behaves how we expect. We can drill down to the “Completed” reminders, and all 3 are instantly visible. And further, the menu in the top-right does not even show us the “Show completed” option.

NULL 51:07

There are a few more places that we need to apply surgical logic like this. For example, in the “Scheduled” view we should default to sorting by due date, and we probably shouldn’t even allow the user to change the sort. The whole point of that detail screen is to just see reminders sorted by their due date. But we will leave those details to the view. Conclusion

NULL 51:24

And just like that we are at the end of our “Modern Persistence” series. We have rebuilt many parts of Apple’s Reminders app, and along the way we did not shy away from attacking the really difficult parts of the app. Brandon

NULL 51:33

We have a real-world, complex schema for the app. We have multiple tables with foreign keys connecting the tables and cascading actions so that when you delete a reminders list it deletes all the reminders in it. And we even have a many-to-many join table so that a reminder can have any number of tags, and vice-versa. Stephen

NULL 51:54

We also constructed quite nuanced and complex queries for fetching data from the database for our features. This often required us to perform computations in SQL so that we could select the most pristine version of our data and hand it off to SwiftUI. Brandon

NULL 52:08

And no matter how complex these queries got, our tools observed changes in the database each step of the way so that if anything changed, our views immediately updated and showed the freshest data. Stephen

NULL 52:19

And on top of all of this we were able to get deep and comprehensive test coverage on how our features loaded data from the database. We could exercise a script of actions the user can take in our features, and then assert on how data is written to the database and how data was fetched from the database.

NULL 52:38

And we feel this is enough to conclude this series and call this our take on “Modern Persistence.” Brandon

NULL 52:43

But in reality, there is so, so much more we want to cover. But, rather than drawing this out into a 20-part series, we are going to break it up into smaller mini-series.

NULL 52:54

We have some truly eye opening videos coming soon where we will dive into more complex functionality of SQLite, including triggers, and soon we will finally dip our toes into the vast ocean cloud synchronization. It is possible for you to make minor changes to your app and immediately have your data synchronized across all of your devices, iPhone, iPad and Mac, and even share data with other users so that multiple people can make edits to the same database records.

NULL 53:25

It’s incredible see, but that will have to wait for another time.

NULL 53:29

Until next time. References SQLiteData Brandon Williams & Stephen Celis 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 0328-modern-persistence-pt6 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 .