Video #345: CloudKit Sync: Participants

Episode: Video #345 Date: Nov 10, 2025 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep345-cloudkit-sync-participants

Description

We add advanced sharing functionality to our reminders app by fetching and displaying participant information, all without hitting CloudKit servers by leveraging SQLiteData’s metadata, instead. Along the way we will explore two powerful tools to simplify our app: database “views” and the @DatabaseFunction macro.

Video

Cloudflare Stream video ID: 7f9dad32cd8f64dc99a9b4c90b7d5399 Local file: video_345_cloudkit-sync-participants.mp4 *(download with --video 345)*

References

Transcript

— 0:05

We have now greatly improved the user experience for our reminders lists by separating the private lists from the shared ones. To accomplish this we did something kind of incredible We joined our SQL tables to the sync metadata table that exists in a completely different database. That’s right, we are joining across databases.

— 0:21

And we do this so that we can inspect the iCloud metadata for each reminders list, and in particular whether or not that list is shared. The fact that SQLiteData does not hide this metadata from you, and really quite the opposite, makes it very public and even queryable, makes it so easy to implement features like this. Brandon

— 0:38

But one not so ideal thing about what we just did is that we literally copied-and-pasted a large query just so that we could query for the private lists and then the shared lists. Luckily for us there are many ways for us to reuse the logic in our queries so that we don’t have to literally copy-and-paste code around. And we’ve shown a number of these techniques in past episodes, but we are going to show off a whole new one that is more appropriate to use here.

— 1:03

There is a concept in SQL known as database “views”, and they allow you to define table-like entities in your database that are secretly backed by a query. This is a powerful tool to share complex logic amongst many queries and also hide the implementation details of the complex query.

— 1:22

Let’s take a look. Reusing SQL: Views

— 1:25

Let’s head over to the documentation for SQLite and search for

VIEW 1:29

We will find a whole new syntax for creating what are known as views, which the docs describe as: Note The CREATE VIEW command assigns a name to a pre-packaged

SELECT 1:47

Views allow us to define a complex query to describe some subset of data in our database, and then we can query that subset as if it was a table in its own right.

SELECT 1:57

The syntax to create a view is described with a flow chart .

SELECT 2:10

And just as we did for our triggers, we are only going to create temporary views. That is, views that are defined only for the lifetime of our connection to the database, and so it’s appropriate to create them each time our app starts up.

SELECT 2:24

Let’s see what it would look like to write the raw SQL to define a view that represents all reminders lists, along with a count of the implemented reminders in each list, as well as a boolean that determines if the list is shared or not. A moment ago we saw that such a query looks like this because it’s what our StructuredQueries library produced:

SELECT 3:00

Let’s hop over to a SQLite console connected to the SQLite database for our app in the simulator, and we can start by saying that we will create a temporary view: CREATE TEMPORARY VIEW

SELECT 3:20

Next we specify the name of the view, which we can call remindersListRows : CREATE TEMPORARY VIEW "remindersListRows"

SELECT 3:26

This followed by the

AS 3:30

And finally a

SELECT 4:05

We have now created a view.

SELECT 4:10

We can query from it just as we would any table. Including selecting a subset of columns, filtering results with

WHERE 4:27

Well, that doesn’t work because secretly our query is referencing a table in the meta-database, which our current database connection has no access to.

WHERE 4:45

But we can simply do the work that SQLiteData does under the hood for us when we invoke the attachMetadatabase method in our app code. We can perform an ATTACH DATABASE command in SQLite, pass the location of the meta-database file, and give a name for this attached database: sqlite> attach database ...> "…/.db.metadata-iCloud.co.pointfree.ModernPersistence.CloudKit.sqlite" ...> as "metadatabase";

WHERE 5:24

Once that is done we can successfully query the view: sqlite> select * from remindersListRows; ┌──────────────────────────┬──────────┬──────────────────────────────────────┬────────────┬──────────┬─────────────┐ │ incompleteRemindersCount │ isShared │ id │ color │ position │ title │ ├──────────────────────────┼──────────┼──────────────────────────────────────┼────────────┼──────────┼─────────────┤ │ 7 │ 0 │ 00000000-0000-0000-0000-000000000001 │ 1251602431 │ 0 │ Personal │ │ 1 │ 0 │ 00000000-0000-0000-0000-000000000003 │ 2128628479 │ 2 │ Business │ │ 2 │ 1 │ 00000000-0000-0000-0000-000000000002 │ 4018031359 │ 3 │ Family │ │ 0 │ 0 │ a757e19b-0d93-4469-b5ab-d6e9d99248b2 │ 1251602431 │ 4 │ Adfasdfasdf │ └──────────────────────────┴──────────┴──────────────────────────────────────┴────────────┴──────────┴─────────────┘

WHERE 5:54

And incredibly that worked. We get all lists, along with a count of their incomplete reminders and even ordered by that value, as well as a boolean value representing whether or not the list is shared.

WHERE 6:02

If we only wanted the shared lists we could simply add a

WHERE 6:12

Or the non-shared lists: sqlite> select * from remindersListRows where isShared = 0; ┌──────────────────────────┬──────────┬──────────────────────────────────────┬────────────┬──────────┬─────────────┐ │ incompleteRemindersCount │ isShared │ id │ color │ position │ title │ ├──────────────────────────┼──────────┼──────────────────────────────────────┼────────────┼──────────┼─────────────┤ │ 7 │ 0 │ 00000000-0000-0000-0000-000000000001 │ 1251602431 │ 0 │ Personal │ │ 1 │ 0 │ 00000000-0000-0000-0000-000000000003 │ 2128628479 │ 2 │ Business │ │ 0 │ 0 │ a757e19b-0d93-4469-b5ab-d6e9d99248b2 │ 1251602431 │ 4 │ Adfasdfasdf │ └──────────────────────────┴──────────┴──────────────────────────────────────┴────────────┴──────────┴─────────────┘

WHERE 6:17

And so our complex query has been reduced to a very simple query. One that selects all columns from a table and applies a simple

WHERE 6:41

So, that’s a quick introduction to database views. What does it take to create views in our app that represent this complex query and further, how can we query the view using our @FetchAll property wrappers?

WHERE 6:56

We start by defining a Swift data type that represents all of the data we want to gather in our view: struct RemindersListRow { let incompleteRemindersCount: Int let isShared: Bool let remindersList: RemindersList }

WHERE 7:38

Further, because views are very table-like, we will annotate this type with the @Table macro: @Table struct RemindersListRow { … }

WHERE 7:46

And it’s important to note that while RemindersListRow is not going to literally be a stored table in SQLite, it is similar enough that it is appropriate to apply the @Table macro. And really the @Table macro can be applied to anything that is table-like in SQLite, including virtual tables that we defined for full-text search: @Table struct ReminderText: FTS5 { … }

WHERE 8:09

Next, in the appDatabase method that is responsible for forming a connection to the database we will create the temporary view that queries for this new data type. Currently we create other temporary things in this function, which are the temporary database triggers, such as the trigger that sets the updatedAt timestamp when a reminder is updated: try database.write { db in try Reminder.createTemporaryTrigger( after: .update(touch: \.updatedAt) ) .execute(db) … }

WHERE 8:49

This is being done in the write transaction which means the trigger will be accessible to any queries executed in a write across the entire app. The triggers will not be accessible from any read s: try database.read { db in }

WHERE 9:05

This is because we are using a DatabasePool in our app: database = try DatabasePool(path: path, configuration: configuration)

WHERE 9:13

…which maintains a single writer to the database, and a pool of readers. That allows one to read from the database concurrently from multiple threads, and the database is only locked once one needs to write to it.

WHERE 9:26

But also it’s totally OK that triggers are not accessible from readers because triggers can’t be executed in read-only database transactions. They only make sense when making writes to the database.

WHERE 9:38

Views, however, are different. We want to be able to access our database view from both the readers and writer in the database, and so we can’t install the view here. Doing so would make it only available to the writer, and in particular it would not be available to us when querying the database in the @FetchAll property wrapper.

WHERE 10:07

A better place to install the database view is in the prepareDatabase method: configuration.prepareDatabase { db in

WHERE 10:18

This method is called for each connection made to the database, which includes a writer and potentially many readers, as is the case with database pools. And so this gives us an opportunity to customize every connection made to the database, and this is the most appropriate place to install a database view.

WHERE 10:38

And luckily for us, our StructuredQueries library comes with everything you need to define views in a type-safe and schema-safe manner.

WHERE 10:50

The RemindersListRow type comes with a static method called createTemporaryView : try RemindersListRow.createTemporaryView( ifNotExists: <#Bool#>, as: <#Selection#> )

WHERE 11:02

The ifNotExists parameter is not important to us, so we can remove it: try RemindersListRow.createTemporaryView( as: <#Selection#> )

WHERE 11:11

The as argument is where we specify the

SELECT 11:30

So we can start by copying-and-pasting most of our query that we wrote in our observable model into this as argument: try RemindersListRow.createTemporaryView( as: RemindersList .group(by: \.id) .order(by: \.position) .leftJoin(Reminder.all) { $0.id.eq($1.remindersListID) && !$1.isCompleted } .leftJoin(SyncMetadata.all) { $0.syncMetadataID.eq($2.id) } .select { RemindersListRow.Columns( incompleteRemindersCount: $1.count(), isShared: $2.isShared.ifnull(false), remindersList: $0 ) } ) .execute(db)

SELECT 12:01

Except we did not copy over the where clause because it is not appropriate to do that filtering in the view. We want to leave that filtering to our feature so that it can decide if it wants just shared or non-shared lists.

SELECT 12:17

And finally, this only constructs a SQL query that creates a view, but it does not actually execute that SQL. To do that we need to invoke the execute method: try RemindersListRow.createTemporaryView( as: … ) .execute(db)

SELECT 12:29

And we have now created a view in the database that can be queried like any other table. In fact, because RemindersListRow was annotated with the @Table macro, we have full access to all querying tools that we have become accustomed to while building our app.

SELECT 12:46

This allows us to do something truly incredible. We can now replace over 40 lines of complex and duplicated code with just 6 lines of very simple, readable code: @ObservationIgnored @FetchAll(RemindersListRow.where { !$0.isShared }, animation: .default) var privateRemindersListRow @ObservationIgnored @FetchAll(RemindersListRow.where(\.isShared), animation: .default) var sharedRemindersListRow // @ObservationIgnored // @FetchAll( // RemindersList // .group(by: \.id) // .order(by: \.position) // .leftJoin(Reminder.all) { // $0.id.eq($1.remindersListID) && !$1.isCompleted // } // .leftJoin(SyncMetadata.all) { $0.syncMetadataID.eq($2.id) } // .where { !$2.isShared.ifnull(false) } // .select { // RemindersListRow.Columns( // incompleteRemindersCount: $1.count(), // isShared: $2.isShared.ifnull(false), // remindersList: $0 // ) // }, // animation: .default // ) // var privateRemindersListRow // // @ObservationIgnored // @FetchAll( // RemindersList // .group(by: \.id) // .order(by: \.position) // .leftJoin(Reminder.all) { // $0.id.eq($1.remindersListID) && !$1.isCompleted // } // .leftJoin(SyncMetadata.all) { $0.syncMetadataID.eq($2.id) } // .where { $2.isShared.ifnull(false) } // .select { // RemindersListRow.Columns( // incompleteRemindersCount: $1.count(), // isShared: $2.isShared.ifnull(false), // remindersList: $0 // ) // }, // animation: .default // ) // var sharedRemindersListRow This makes it very clear that we are querying for reminders lists that are not shared, and lists that are shared.

SELECT 14:45

If we run the app we will see that it works exactly as it did before. The private and shared lists are put into their own sections. And if we look in the logs we will see the big ole CREATE TEMPORARY VIEW query executed: CREATE TEMPORARY VIEW "remindersListRows" ( "incompleteRemindersCount", "isShared", "id", "color", "position", "title" ) AS SELECT count("reminders"."id") AS "incompleteRemindersCount", ifnull("sqlitedata_icloud_metadata"."isShared", 0) AS "isShared", "remindersLists"."id" AS "id", "remindersLists"."color" AS "color", "remindersLists"."position" AS "position", "remindersLists"."title" AS "title" FROM "remindersLists" LEFT JOIN "reminders" ON (("remindersLists"."id") = ("reminders"."remindersListID")) AND (NOT (("reminders"."status") <> (0))) LEFT JOIN "sqlitedata_icloud_metadata" ON ("remindersLists"."id", 'remindersLists') = ( "sqlitedata_icloud_metadata"."recordPrimaryKey", "sqlitedata_icloud_metadata"."recordType" ) GROUP BY "remindersLists"."id" ORDER BY "remindersLists"."position"

SELECT 14:59

And we can even see the queries against this view. First the one that gets non-shared lists:

SELECT 15:13

And then the one that gets shared lists:

SELECT 15:48

Now there is one tiny optimization we could make to these queries, but it probably wouldn’t really help in this case. It’s a little strange that we are executing two very similar queries at the same time that are complementary. That is, if a row is not in one result set, then it will be in the other result set. That means technically we could have just executed a single query to get all lists, and then partitioned them into two result sets in Swift.

SELECT 16:30

And that may not seem right to you because we have often said let SQL do the heavy lifting when it comes to querying. But when we say that we mean do not load a huge result set into memory just to filter it in Swift. And that’s not what we are doing in this situation. We do actually want to load all reminders lists into memory, it’s just that we further want to partition them into two buckets: the private ones and the shared ones.

SELECT 16:56

So, if we did want to embrace this tiny optimization we would define a FetchKeyRequest conformance: struct RemindersListRow: FetchKeyRequest { }

SELECT 17:36

And so we could use this one single FetchKeyRequest instead of two individual @FetchAll s. And if we were displaying hundreds or thousands of rows in this view, this would probably be the way to go. But I don’t think that’s really the case for a reminders app, so we won’t do this right now. Displaying participants

SELECT 17:48

We have now leveraged yet another amazing SQL tool to not only improve the quality of our code, but that in turn improved the user experience of our app. The ability to define a little view into our database that hides a complex query so that we can have a simple way to query it empowers us to build more and more features for our users without fearing our code is going to become so complex that we can’t understand it. Stephen

SELECT 18:12

But I think we can improve the user experience in our app even more, and it will give us the opportunity to leverage yet another SQL feature that allows us to hide complexity from our features. It’s nice that we have separated the shared lists from the private lists, but still there isn’t much info exposed about the share in each reminders list row. What if we could list out all of the participants that are on the share so that it was very clear who you shared the list with, or who shared the list with you.

SELECT 18:37

Let’s take a look.

SELECT 18:40

If we go back to the docs for CKShare we will see that it offers us a bunch of information about the share, including information about the participants: Note Accessing the Share’s Attributes : var owner: CKShare.Participant The participant that represents the share’s owner. var currentUserParticipant: CKShare.Participant? The participant that represents the current user. var participants: [CKShare.Participant] An array that contains the share’s participants.

SELECT 18:52

Further, this CKShare.Participant object has additional information that is of interest to us: Note Accessing the Participant’s Identity : var userIdentity: CKUserIdentity The identity of the participant.

SELECT 19:05

And this CKUserIdentity finally has something that will give us the name of the participant: Note Accessing User Information : var nameComponents: PersonNameComponents? The user’s name.

SELECT 19:13

If we had access to the underlying CKShare for our reminders list we should be able to iterate over the participants , get their userIdentity , and then compute their name from the nameComponents . That would allow us to display a list of names for all the people collaborating on the reminders list.

SELECT 19:26

And luckily for us, SQLiteData gives us full access to the underlying CKShare for any shared records. We just need to be able to get access to the CKShare in our view so that we can compute a string of the names of participants.

SELECT 19:37

Our RemindersListRow already has a boolean field that determines if the list is being shared: @Table struct RemindersListRow { let incompleteRemindersCount: Int let isShared: Bool let remindersList: RemindersList }

SELECT 19:45

What if instead of collapsing all of the share data into a simple boolean we extracted the CKShare from the sync metadata table: @Table struct RemindersListRow { let incompleteRemindersCount: Int let isShared: Bool let remindersList: RemindersList let share: CKShare? }

SELECT 20:03

Now unfortunately we can’t do something as naive as just adding a CKShare property to our struct: let share: CKShare? If we try to build this we get some errors in the @Table macro-generated code: Type ‘CKShare’ does not conform to protocol ‘QueryRepresentable’

SELECT 20:19

That’s because CKShare is not a primitive type that SQLite understands, and so we have to provide a custom representation that helps our library convert CKShare into something more appropriate to store in SQLite, and conversely turns that back into a CKShare when loading it from the database.

SELECT 20:33

Luckily the library already comes with such a representation, and it’s called SystemFieldsRepresentation : @Table struct RemindersListRow { let incompleteRemindersCount: Int let remindersList: RemindersList @Column(as: CKShare?.SystemFieldsRepresentation.self) let share: CKShare? }

SELECT 20:51

That right there is enough to allow the library to figure out how to shuttle a CKShare back and forth between the database and app. It’s also possible to make your own representations for storing non-native types in SQLite and you can check out the docs for more information.

SELECT 21:03

And now we don’t even need the isShared property because it can be determined by inspecting the share , but we could keep around a computed property in case its useful: @Table struct RemindersListRow { let incompleteRemindersCount: Int let remindersList: RemindersList let share: CKShare? var isShared: Bool { share != nil } }

SELECT 21:18

Since we made a change to the struct representing our database view we do need to update the creation of this database view: .select { RemindersListRow.Columns( incompleteRemindersCount: $1.count(), remindersList: $0, share: $2.share ) }

SELECT 21:37

Next we have a compilation error in our observable model because we are using the isShared property to filter for private and shared lists: @ObservationIgnored @FetchAll(RemindersListRow.where { !$0.isShared }, animation: .default) var privateRemindersListRow @ObservationIgnored @FetchAll(RemindersListRow.where(\.isShared), animation: .default) var sharedRemindersListRow

SELECT 21:47

That property is now computed instead of stored, and so it’s not appropriate to use it in a query like this. The fact that this is causing a compilation error is a good thing. It is preventing us from writing a query that would only fail at runtime because it’s referencing a column that doesn’t actually exist. SwiftData on the other hand allows one to use computed properties in predicates, even though it doesn’t make sense, and that leads to crashes at runtime.

SELECT 22:06

So we could update this query to check if the share is nil or not: @ObservationIgnored @FetchAll( RemindersListRow.where { $0.share.is(nil) }, animation: .default ) var privateRemindersListRow @ObservationIgnored @FetchAll( RemindersListRow.where { $0.share.isNot(nil) }, animation: .default ) var sharedRemindersListRow

SELECT 22:18

But even better, we can just extend the table definition of RemindersListRow to have a computed isShared property that works at the level of SQL: extension RemindersListRow.TableColumns { var isShared: some QueryExpression<Bool> { self.share.isNot(nil) } }

SELECT 23:04

And now we can go back to our previous queries. That now compiles and generates the correct SQL at runtime because even though we are referencing an isShared symbol, under the hood that is written as SQL that checks the share’s nullability.

SELECT 23:17

Everything should compile, but we are now in a position to leverage the full CKShare in our SwiftUI views. For example, the RemindersListRowView , which is responsible for rendering the row for a list in our root view, can now take a full-blown CKShare instead of a simple boolean: struct RemindersListRowView: View { let incompleteRemindersCount: Int let remindersList: RemindersList var share: CKShare? … }

SELECT 23:32

And in the body of this view we can check if the share is non- nil instead of checking the boolean: if share != nil { Text("Shared") }

SELECT 23:36

And in a moment we will further improve this to actually list out the participants for each row.

SELECT 23:40

But before doing that let’s update all the places we create this row view since it is no longer compiling. The preview down below needs to be updated: #Preview { NavigationStack { List { RemindersListRowView( incompleteRemindersCount: 10, remindersList: RemindersList( id: UUID(1), title: "Personal" ), share: nil ) } } }

SELECT 23:55

In the root view we have two instances of RemindersListRowView , one for private lists and another for shared lists. The private lists just don’t need to pass anything along because the default value of share is nil : RemindersListRowView( incompleteRemindersCount: row.incompleteRemindersCount, remindersList: row.remindersList ) And the shared list can pass along the CKShare it has in its row value: RemindersListRowView( incompleteRemindersCount: row.incompleteRemindersCount, remindersList: row.remindersList, share: row.share )

SELECT 24:08

OK, everything is now compiling and would work exactly has it did before, but we are now passing around a full CKShare , which means we now have the ability to list out participants.

SELECT 24:16

Let’s define a function that is responsible for analyzing a CKShare and turning it into a nicely formatted description of the share’s participants: func participants(share: CKShare) -> String { "Shared" }

SELECT 24:32

Then in the view we can defer to this function to do all formatting: if let share { Text(participants(share: share)) }

SELECT 24:42

Let’s first think about how we want this string formatted. I think it’d be nice to be able to distinguish when we are the one sharing vs. the one being shared with: // Shared with you // Shared by you

SELECT 25:00

Further, if you are the owner and we have access to participants, then we can list them out: // Shared with Brandon, Blob Jr

SELECT 25:10

And if you are not the owner and have access to the owner’s name, then we can display that too: // Shared by Brandon

SELECT 25:19

It’s a little gnarly to get all of this logic down, but we can start by pre-computing some of the pieces we need. For example, we can check if the currently logged in user is the owner of the share: let isOwner = share.currentUserParticipant?.role == .owner

SELECT 25:39

And we can make sure the participants array actually has some participants: let hasParticipants = !share.participants.isEmpty

SELECT 25:51

Then we can deal with the combinations of being an owner or not and have participants or not by switching over these booleans: switch (isOwner, hasParticipants) { case (true, true): case (true, false): case (false, true): case (false, false): }

SELECT 26:13

Some of these cases are very easy to fill in. If we are the owner and have participants, then we can return a label that says “Sharing with” followed by the comma-separate list of names: case (true, true): let participantNames = share.participants.compactMap { $0.role == .owner ? nil : $0.userIdentity.nameComponents?.formatted() } .formatted() return "Sharing with \(participantNames)"

SELECT 27:03

If we are the owner but we don’t yet have any participants we can just say “Shared by you”: case (true, false): return "Shared by you"

SELECT 27:10

Then if we are not the owner we want to get the name of the owner: case (false, _): let owner = share.participants.first(where: { $0.role == .owner }) guard let owner, let ownerName = owner.userIdentity.nameComponents?.formatted() else { }

SELECT 27:36

If we are able to get that name we can display it in the label, and otherwise we will just say “Shared with you”: else { return "Shared with you" } return "Shared by \(ownerName)"

SELECT 27:54

And that’s basically it. When I run the app the app I see that the “Point-Free” list you shared with me earlier is displayed a little differently. It now says “Shared by Michael Williams” because it sees that you are the owner of the share. And further, my “Family” list now says “Shared by you”, but no participants are listed because no one has joined the list yet. Reusing SQL: Database functions

SELECT 28:47

And the really cool thing about all of this is that we are live-observing all changes to the sync metadata, so if someone were to join my “Family” list while the app was opened, the view would automatically update. We don’t have to worry about the view becoming stale and showing old data.

SELECT 29:00

So it’s fantastic that SQLiteData gives us access to all of this CloudKit data because otherwise we would not be able to customize our view in such a nuanced way to let the user know exactly who is participating in these shared lists. Brandon

SELECT 29:11

But we can improve this greatly. Right now this logic isn’t very reusable because it’s confined to the view, and it’s very specific to this one use case we have. So if later we need to display similar information somewhere else in the app, like say in the detail view of a reminders list, we will most likely repeat a lot of this code, including all the message participant munging we are doing.

SELECT 29:36

Well, what if I were to tell you it’s possible to package up a lot of this logic into a reusable unit that is accessible from any SQL query across the entire app? We can leverage database functions so that we can invoke a Swift function from SQLite in order to process the CKShare and transform it into a more structured format that is easier to use in our features.

SELECT 29:58

This technique is reminiscent of database views we employed last episode. That allowed us to share a complex query across our entire app by baking it into a view which then makes it possible to customize and execute that query from anywhere in our app. Now we will do the same, but with a little bit of Swift logic that needs to be accessible from anywhere.

SELECT 30:16

Let’s take a look.

SELECT 30:19

First let’s take a quick look at how the app is structure right now to show that there is something not quite ideal about how we have structured things. Let’s put a print in the function that computes the participants string of a share: func participants(share: CKShare) -> String { print("Computing participant names") … }

SELECT 30:32

What we will find is that this function is called every time the view needs to recompute its body.

SELECT 30:37

To see this, we will add some functionality that mutates local state in the view, but crucially does not require re-fetching any data from the database. @State var uuid = UUID() … let _ = uuid Button("Boop") { uuid = UUID() } The act of accessing uuid in the view body will cause it to recompute whenever the button is tapped and it is mutated.

SELECT 31:58

Even something as simple as opening up the new list form and closing it causes the body to recompute. And this is happening even though no writes have been made to the database.

SELECT 32:11

Had we computed this information in our database query then we could make sure that we only compute when the database changes, which is probably a lot less frequent than when the view recomputes its body. And at the end of the day such a simple computation may not that big of a deal to compute in the view, but there’s a lot of power to be had by computing these kinds of things in the database query.

SELECT 32:41

And the way we can do this is by leveraging custom database functions. It is possible to define a Swift function that is callable from SQL, and this allows you to perform logic and computations in queries that are not achievable in SQL alone.

SELECT 32:56

A few weeks back we even leveraged custom database functions to implement some advanced functionality in our reminders app. The episode was #333, titled Persistence Callbacks: Advanced Triggers .

SELECT 33:15

And if we go to that episode’s code sample …

SELECT 33:29

And then navigate to the schema file … We will see the following database function defined and installed in the database connection: let task = Mutex<Task<Void, any Error>?>(nil) db.add(function: DatabaseFunction("handleReminderStatusUpdate") { _ in task.withLock { $0?.cancel() $0 = Task { @Dependency(\.defaultDatabase) var database try await Task.sleep(for: .seconds(2)) try await database.write { db in try Reminder .where { $0.status.eq(Reminder.Status.completing) } .update { $0.status = .completed } .execute(db) } } } return nil })

SELECT 33:40

This database function allows us to invoke Swift code, the stuff in the trailing closure, from SQL queries. For example, we created a trigger so that when we detect a change of the status column on a reminder we invoke this Swift closure: try Reminder.createTemporaryTrigger(after: .update { $0.status } forEachRow: { old, new in #sql("SELECT handleReminderStatusUpdate()") } when: { old, new in new.status.eq(Reminder.Status.completing) }) .execute(db)

SELECT 34:50

However, this way of doing database functions doesn’t have much type-safety or schema-safety. We can only refer to the function by its string name, which means our code is prone to typos. And if the function accepted arguments things get more complicated. The trailing closure of the DatabaseFunction only gets an open ended array of type erased values, and so you have to perform extra work to validate the arguments and make sure they match what you expect.

SELECT 35:37

This is why two months ago we released a brand new tool for defining database functions that embraces type-safety and schema-safety. And in fact, we silently refactored the reminders app we have been building to use this new tool. We can see that the new way of defining a database function is to define just a regular Swift function and annotate it with @DatabaseFunction : private let task = Mutex<Task<Void, any Error>?>(nil) @DatabaseFunction private func handleReminderStatusUpdate() { task.withLock { $0?.cancel() $0 = Task { @Dependency(\.defaultDatabase) var database try await Task.sleep(for: .seconds(2)) try await database.write { db in try Reminder .where { $0.status.eq(Reminder.Status.completing) } .update { $0.status = .completed } .execute(db) } } } }

SELECT 36:12

And then the database function is added to the database connection like so: db.add(function: $handleReminderStatusUpdate)

SELECT 36:19

And this gives us a static symbol we can use in queries, rather than using a string value: try Reminder.createTemporaryTrigger( after: .update { $0.status } forEachRow: { old, new in #sql("SELECT \($handleReminderStatusUpdate())") } when: { old, new in new.status.eq(Reminder.Status.completing) } ) .execute(db)

SELECT 36:37

This is a much better way to deal with database functions, and this technique really starts to pay dividends when dealing with functions that target arguments and return values.

SELECT 36:56

So, what if we could turn the participants function we wrote over in the RemindersListRowView into a database function, so that it is callable from SQL? I’m going to copy-and-paste it into the schema file since that is where we have gathered all database-related entities: func participants(share: CKShare) -> String { … }

SELECT 37:53

And I am going to just naively try to apply the @DatabaseFunction macro: @DatabaseFunction func participants(share: CKShare) -> String { … }

SELECT 38:06

…and we unfortunately get some compiler errors: Global function '_columnWidth' requires that ‘CKShare’ conform to ‘QueryExpression’ Global function '_requireQueryRepresentable' requires that ‘CKShare’ conform to ‘QueryRepresentable’

SELECT 38:12

These errors are letting us know that CKShare does not conform to QueryExpression or QueryRepresentable , and so isn’t usable directly in the database function, or really any SQL expressions whatsoever. Extra work must be done to convert the share into something SQL does understand, as well as a way to convert it from SQLite’s format back into a CKShare .

SELECT 38:41

We’ve already seen how to do this when it comes to holding a CKShare in a table like our RemindersListRow database view, and we can do something similar here. We can update this signature to give it a hint that it should use the SystemFieldsRepresentation when dealing with the CKShare . But we don’t specify that directly in the argument: @DatabaseFunction func participants( share: CKShare.SystemFieldsRepresentation ) -> String { … }

SELECT 39:08

That would be annoying because then we would need to change the implementation to deal with this weird wrapper type, and it would be difficult to invoke this function in other contexts that have nothing to do with SQLite.

SELECT 39:27

So, instead, we can tell the @DatabaseFunction macro how it should decode and encode the argument by specifying the as argument: @DatabaseFunction( as: ) func participants(share: CKShare) -> String { … }

SELECT 39:41

We must specify the type of the function signature in this argument in a format that SQLite understands. It can be a little gnarly to do this, so let’s take it slow.

SELECT 39:45

First of all, the type of the as argument is literally a function signature, so let’s stub that: @DatabaseFunction( as: ( (/*arguments*/) -> /*return value*/ ).self ) func participants(share: CKShare) -> String { … }

SELECT 39:50

And we just have to figure out what to put in for the arguments and return value.

SELECT 40:05

The argument is the CKShare , but it’s the SystemFieldsRepresentation so that SQLite knows how to encode and decode it: @DatabaseFunction( as: ( (CKShare.SystemFieldsRepresentation) -> /*return value*/ ).self ) func participants(share: CKShare) -> String { … }

SELECT 40:15

And the return value is just a string, which is a type SQLite can handle just fine: @DatabaseFunction( as: ((CKShare.SystemFieldsRepresentation) -> String).self ) func participants(share: CKShare) -> String { … }

SELECT 40:24

And now this compiles.

SELECT 41:02

We can make a further improvement to this database function by marking it as deterministic: @DatabaseFunction( as: ((CKShare.SystemFieldsRepresentation) -> String).self, isDeterministic: true ) func participants(share: CKShare) -> String { … }

SELECT 41:12

A database function is deterministic if it always returns the same value for a given set of input values. For example, the max function that computes the maximum of all arguments handed to it is deterministic, but the random() function is not. SQLite can perform certain optimizations when it knows a function is deterministic because it knows that it only has to invoke it a single time for a given input.

SELECT 41:38

And our participants function is deterministic because if you give it the same CKShare it will give you back the same output. It does not make use of any global mutable state to do its job. So, we might as well mark it as deterministic and reap the benefits. But, you should be absolutely sure your function is deterministic before marking it as such, otherwise it will be possible to use it in a manner that corrupts your database.

SELECT 42:26

Now that we have defined our database function we need to install it in the connection to our database. The best place to do this is in the prepareDatabase closure so that the function is added to every connection in the database pool, including the single writer and all readers: configuration.prepareDatabase { db in … db.add(function: $participants) … }

SELECT 43:09

Before even using this database function we want to show something really cool. Even though we have marked the participants method with the @DatabaseFunction macro, it is still just a regular Swift function at its core.

SELECT 43:34

In fact, if we expand the macro to see what it generates: nonisolated var $participants: $s17ModernPersistence12participants16DatabaseFunctionfMp_12participantsfMu_ { $s17ModernPersistence12participants16DatabaseFunctionfMp_12participantsfMu_(participants) } nonisolated struct $s17ModernPersistence12participants16DatabaseFunctionfMp_12participantsfMu_: ScalarDatabaseFunction { public typealias Input = CKShare.SystemFieldsRepresentation public typealias Output = String public let name = "participants" public var argumentCount: Int? { var argumentCount = 0 argumentCount += _columnWidth( CKShare.SystemFieldsRepresentation.self ) return argumentCount } public let isDeterministic = true public let body: (CKShare) -> String public init(_ body: @escaping (CKShare) -> String) { self.body = body } public func callAsFunction( share: some QueryExpression<CKShare.SystemFieldsRepresentation> ) -> some QueryExpression<String> { $_isSelecting.withValue(false) { SQLQueryExpression( "\(quote: self.name)(\(share))" ) } } public func invoke( _ decoder: inout some QueryDecoder ) throws -> QueryBinding { let share = try decoder.decode( _requireQueryRepresentable( CKShare.SystemFieldsRepresentation.self ) ) guard let share else { throw InvalidInvocation() } return String( queryOutput: self.body(share) ) .queryBinding } private struct InvalidInvocation: Error { } }

SELECT 43:48

We see that’s just extra code in addition to the function. The function we wrote is still there completely untouched. The extra code written is just glue code that allows us to receive SQL values from the database, decode them into Swift types, then we can apply our function to those Swift values, and with the output of that function we encode the result back into a SQL type so that we can send it back to SQLite.

SELECT 44:51

And so what this means is that we can head back to our RemindersListRowView file, delete the instance method that is participants , and the app still compiles and would work exactly as it did before. We are still free to invoke our function from Swift code whenever we want. All we’ve done is further make this function callable from SQLite too. This makes it a fantastic tool for sharing code between your Swift app and your database layer. So that’s cool, but let’s now start using this function from the database. We will make it so that the RemindersListRowView is handed a pre-formatted string of all the sharing info instead of the CKShare : struct RemindersListRowView: View { let incompleteRemindersCount: Int let remindersList: RemindersList var shareSummary: String? … }

SELECT 45:06

Then in the view we will just unwrap the summary to display it: if let shareSummary { Text(shareSummary) }

SELECT 45:30

And we can update the preview to hardcode what the summary will say: RemindersListRow( incompleteRemindersCount: 10, remindersList: RemindersList( id: UUID(1), title: "Personal" ), shareSummary: "Shared with Blob, Blob Jr" ) In order to provide this summary info to our views we need to first compute it in our database query, and in particular in the query that defines the database view RemindersListRow : @Table struct RemindersListRow { … let shareSummary: String? }

SELECT 45:53

To provide this information to the database view we need to update where we create the view because we are now responsible for computing this data: RemindersListRow.Columns( incompleteRemindersCount: $1.count(), remindersList: $0, share: $2.share, shareSummary: <#String?#> )

SELECT 46:13

To invoke the database function in a query we can’t invoke participants directly: shareSummary: participants(share: $2.share)

SELECT 46:31

That’s because participants is just a Swift function, and invoking it right here simply invokes the function at the moment of creating this query. But what we want is the ability to invoke the function from within the query, while the query is executing. To do that we reference the $ -prefixed value generated by the macro: shareSummary: $participants(share: $2.share) This $participants symbol is a special query expression that is turned into the SQL that will actually invoke our function when the query is executed.

SELECT 47:09

And it would be great if we could just invoke $participants on the share, represented by $2 , but unfortunately we left joined the sync metadata table and so $2 is a kind of optionalized table, and so $2.share is an optional CKShare , not an honest one.

SELECT 47:34

But, there’s a nice way of handling this using a special tool of our StructuredQueries library. It is possible to use a map function on query expressions of optional values to unwrap them if they are non-

NULL 48:19

So, if the share is present it will be transformed with the participants function, and otherwise it will just return nil and so shareSummary will be nil .

NULL 48:30

To complete this refactoring we will just make it so that the shareSummary is passed to the RemindersListRowView instead of the full CKShare : RemindersListRow( incompleteRemindersCount: row.incompleteRemindersCount, remindersList: row.remindersList, shareSummary: row.shareSummary )

NULL 49:03

Everything is now compiling, and it works exactly as it did before, but now we will not get extra prints to the console when the view re-renders due to unrelated events. The only time we compute the participants of a share is when the database changes.

NULL 50:01

Also, at this point we are no longer even using the CKShare we store in the database view. We might as well get rid of that for now and update the isShared property to use the summary instead: var isShared: Bool { shareSummary != nil }

NULL 50:37

And we can bring back the full CKShare later if it turns out we need it. But in practice we find that it’s better to define little database helper functions for transforming the data into the format we need rather than passing CKShare s around the application.

NULL 50:51

Just to show how easy it is, let’s define a database function that will determine if the current user is the owner of a CKShare : @DatabaseFunction( as: ((CKShare.SystemFieldsRepresentation) -> Bool).self, isDeterministic: true ) func isOwner(of share: CKShare) -> Bool { share.currentUserParticipant?.role == .owner }

NULL 51:31

And we will install it in the database connection: db.add(function: $isOwner)

NULL 51:39

And now this function is callable as just a regular Swift function from anywhere in our app, but it’s also available to call from SQL queries. We could add some additional state to our database view for reminders list rows: @Table struct RemindersListRow { … let isOwner: Bool … }

NULL 51:51

And then compute that data when the view is created: isOwner: $2.share.map { $isOwner(of: $0) }.ifnull(false),

NULL 52:27

Now everywhere we query for this database view we immediately have access to whether or not the current user is the owner of the share. Among many things, this means we can update our deleteButtonTapped method to take a full RemindersListRow value and then we no longer need to query the metadata directly: func deleteButtonTapped(row: RemindersListRow) { if row.isOwner { deleteRemindersListAlert = row.remindersList } else { withErrorReporting { try database.write { db in try RemindersList .delete(row.remindersList) .execute(db) } } } }

NULL 53:16

And we can update the view to pass the whole row value: model.deleteButtonTapped(row: row)

NULL 54:28

This is just a small example of how we can simplify our views and our app logic by computing more and more things in the database layer. Next time: A grab bag finale

NULL 55:02

Not only are we now showing the participants for each shared reminders list in the root view of our app, but we were able to compute all of that information directly in our database query. That allowed our views to become super simple. They just display the data handed to them. And it even allowed our feature logic to be quite simple because it already had the data it needed to do its job. It didn’t need to execute extra queries just to figure out what to do. Stephen

NULL 55:30

And we are now nearing the end of this series of episodes, and in fact we’re near the end of our Modern Persistence mega series. But our SQLiteData library has so many little features that are packed into it that are easy to miss, we wanted to dedicate one last episode to showing off a grab bag of techniques that you can employ in your apps.

NULL 55:48

The first technique we will show off is how to explicitly tell the sync engine to synchronize any pending changes, including the data stored locally and data stored remotely. 99% of the time you don’t need to think about explicitly synchronization because the sync engine does all the work for you. But there are times you may want the explicit behavior, and luckily we expose all of that information for you.

NULL 56:12

Let’s take a look…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 0345-sync-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 .