Video #333: Persistence Callbacks: Advanced Triggers

Episode: Video #333 Date: Jul 28, 2025 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep333-persistence-callbacks-advanced-triggers

Description

We conclude our series on modern persistence and callbacks by showing how we can call back to Swift from a database trigger. We will take advantage of this by improving the ergonomics of two features in our Reminders application.

Video

Cloudflare Stream video ID: 38090464aec19a8ee25e6793fbf78452 Local file: video_333_persistence-callbacks-advanced-triggers.mp4 *(download with --video 333)*

References

Transcript

— 0:05

We now have the ability to bake validation rules directly into our database. Every single time we assign or remove a tag from a reminder, our validation logic will run, and if the configuration of tags is invalid an error will be emitted that can be easily intercepted and displayed from Swift. It’s just really incredible to see how easy it is to install little validation rules like this at a global level without worrying about auditing every single place a reminder is edited to make sure we are enforcing this rule. And of course, like everything we do in this modern persistence series, this validation logic is 100% unit testable since our tests use the actual database connection, but we won’t explore that right now. Brandon

— 0:41

Let’s move onto our final topic for SQLite triggers. So far we have explored some of the more typical use cases for triggers. We’ve performed an update when a database event happens, such as refreshing updated at timestamps on a record. We’ve performed an insert when a database event happens, such as making sure that we always have at least one reminders list in the database. And we just explored raising errors after certain database events when we detect an invalid configuration of the data in our database.

— 1:09

The final form of trigger we are going to explore is calling out to actual Swift code when a database event happens. This can be great for performing little bits of extra logic that are not purely just database transformations, such as inserts, updates, or deletes. We will begin by exploring this by adding a very simple user experience improvement to our app.

— 1:33

Let’s check it out! Calling back to Swift

— 1:36

Currently we have a really fun trigger that listens for when lists are deleted so that when the last list is deleted it automatically inserts a brand new one for you: try RemindersList.createTemporaryTrigger( after: .delete { old in RemindersList.insert { RemindersList.Draft(title: "Reminders") } } when: { old in !RemindersList.exists() } ) .execute(db)

— 1:56

It’s great to see how easy it is to add global logic like this to our database, but it also has the downside that the user experience of deleting the last reminders list is a bit jarring. While the last list is animating away, the new one begins animating into place simultaneously. What if we could space out these events so that the last reminders list fully animates away, and then the new stub list animates in?

— 2:39

Well, unfortunately there is no “sleep” function in SQLite to delay the execution of a SQL statement. So this is something we can’t purely do in SQL. But, what if we could invoke a Swift function from SQL, and then in that Swift function we would be free to suspend for a bit of time, and then perform the action.

— 3:10

It’s pretty straightforward to write such a Swift function: func handleDeletedRemindersList() async throws { @Dependency(\.defaultDatabase) var database try await Task.sleep(for: .seconds(1)) try await database.write { db in let isRemindersListsEmpty = try RemindersList.count() .fetchOne(db) == 0 if isRemindersListsEmpty { try RemindersList .insert { RemindersList.Draft(title: "Reminders") } .execute(db) } }

— 4:54

But the question is, how can we invoke this function from SQL? Would be pretty cool if we could just do something like this: try RemindersList.createTemporaryTrigger( after: .delete { old in handleDeletedRemindersList() } ) .execute(db)

— 5:18

But that can’t be correct because we are constructing a SQL statement here, and handleDeletedRemindersList is not a SQL expression.

— 5:33

Well, maybe we could turn it into a SQL expression by just wrapping it in the #sql macro: try RemindersList.createTemporaryTrigger( after: .delete { old in #sql("handleDeletedRemindersList()") } ) .execute(db)

— 5:42

This too is not correct for a few reasons. First, if we try running the app we will see that this is just invalid SQL syntax: Thread 1: Fatal error: ‘try!’ expression unexpectedly raised an error: SQLite error 1: near “remindersListDeleted”: syntax error - while executing CREATE TEMPORARY TRIGGER "after_delete_on_remindersLists@ModernPersistence/Schema.swift:321:45" AFTER DELETE ON "remindersLists" FOR EACH ROW BEGIN remindersListDeleted(); END

— 6:15

And while our query builder tries to always generate valid SQL syntax for you, if you use the #sql macro you must shoulder a bit of the responsibility.

— 6:31

There is a fun trick to fix this though. To turn it into a valid SQL statement we can make it a

SELECT 6:40

The act of executing a

SELECT 7:23

It seems that SQLite cannot see our Swift function.

SELECT 7:28

And indeed, SQLite doesn’t just magically see all of the symbols in our application. Heck, it doesn’t even know about Swift as a language. SQLite is a pure C library.

SELECT 7:38

But, we can register a custom function with SQLite that then invokes our Swift function under the hood. SQLite exposes various C functions to achieve this, but luckily for us GRDB provides a nice Swift API to make this very easy. We can simply invoke the add(function:) method defined on the opaque Database connection in a write transaction. We can even do this just before we create our temporary triggers: try database.write { db in db.add( function: DatabaseFunction( "handleDeletedRemindersList" ) { _ in } ) … }

SELECT 8:34

The trailing closure will be invoked when the handleDeletedRemindersList() function is invoked from SQLite, and that closure is handed an array of arguments that can be passed from SQLite to Swift.

SELECT 9:02

Further, whatever you return from this function will be returned to SQLite, but we currently don’t care about a return value so we can just return nil : try database.write { db in db.add( function: DatabaseFunction( "handleDeletedRemindersList" ) { _ in return nil } ) … }

SELECT 9:19

Things are compiling, but we are currently adding this function in a one-off write transaction, which means this function will only be available to the write connection of our database pool. That is probably fine for the purpose of our trigger, but we should also show a more thorough configuration that makes the function available to every database pool connection, and that is to define it using the configuration’s prepareDatabase block: configuration.prepareDatabase { db in db.add( function: DatabaseFunction( "handleDeletedRemindersList" ) { _ in return nil } ) … }

SELECT 10:49

Everything is still building, and we can even put a breakpoint in this function, run the app, and delete a list to see the breakpoint catches. This shows that we are invoke Swift code from a SQLite trigger, which is pretty cool.

SELECT 11:08

But now let’s actually perform the work in the SQL function. We could of course just spin up an unstructured task and invoke the Swift handleDeletedRemindersList from it: db.add( function: DatabaseFunction( "handleDeletedRemindersList" ) { _ in Task { try await handleDeletedRemindersList() } return nil } )

SELECT 11:31

But at this point there really is no point in having the Swift function around when we can just perform the work directly in the DatabaseFunction : db.add( function: DatabaseFunction( "handleDeletedRemindersList" ) { _ in Task { try await Task.sleep(for: .seconds(1)) try await database.write { db in let isRemindersListsEmpty = try RemindersList .limit(1).fetchOne(db) == nil if isRemindersListsEmpty { try RemindersList .insert { RemindersList.Draft(title: "Reminders") } .execute(db) } } } return nil } )

SELECT 12:04

That is all it takes to implement this improvement to the user experience of our app. Now when we run the app we will see that when we delete the last list it fully animates a way, and right after the new stub list animates in. Throttling completion

SELECT 12:21

This is pretty cool stuff. We are now using triggers to call into Swift code when a particular event occurs in our database. And this can be useful for those times that the logic we want to perform in the trigger doesn’t fit the simple insert/update/delete paradigm. In this case we want to suspend for a second, and then perform the action, which is something that Swift can handle quite easily. Stephen

SELECT 12:46

So we now know how to invoke Swift functions from the database. Let’s amp things up even more. We are going to further improve the user experience of our app. Right now when completing a reminder it immediately disappears from the view or jumps down to the bottom of the list, depending how our settings are configured.

SELECT 13:01

This can not only be jarring, but also lead to accidentally completing a reminder we didn’t mean to if we are rapid-fire going completing multiple reminders in quick succession while reminders are flying all of the place.

SELECT 13:14

The official Reminders app from Apple handles this situation really nicely. When you complete a reminder it waits a few seconds before animating away. And further, if you complete multiple reminders in quick succession, the delay is reset so that you can be more sure that reminders are not moving around while trying to tap on one.

SELECT 13:32

Amazingly triggers provide the perfect mechanism to implement this feature. Let’s take a look.

SELECT 13:40

To see the problem let’s first run Apple’s Reminders app to see its behavior. We can navigate to a list, check off a reminder, and see that it hangs around for a couple of seconds before finally fading away. Further, if we check off a bunch of reminders in rapid succession, this delay is throttled so that only when we stop completing reminders, the reminders will fade away 2 seconds later.

SELECT 14:35

Now let’s run our version of the app in the simulator, navigate to a list with a lot of reminders, and then rapid-fire complete a bunch of reminders. Well we see that rows are flying around while trying to complete reminders. Clearly if we are in a hurry we may accidentally complete a reminder we didn’t mean to, and worse, we may not even notice which reminder we did that too.

SELECT 14:45

So, how are we going to implement this feature? It sounds like we need to allow for our reminders to be in a kind of intermediate completion state between completed and uncompleted, and we will call this state “completing”. When the user taps on a reminder to complete it, we will secretly flip it to the intermediate “completing” state, and while in this state we will allow for the reminder to remain in its regular position in the list. And then a few seconds later we will update any “completing” reminders to be actually completed.

SELECT 15:13

There are a few steps to accomplish this, but we can begin with the domain modeling part, which is often the most important part to get right. When your domain is properly modeled you can often solve very difficult problems quite easily.

SELECT 15:22

We will represent the status of a reminder as a 3 valued enum that can be represented as an integer in SQL: enum Status: Int, QueryBindable { case completed = 1 case completing = 2 case incomplete = 0 } We have also taken care to have incomplete correspond to 0 , which is “false” in SQLite, and completed correspond to 1 , which is typically used to represent “true”. And then the intermediate status of “completing” corresponds to a new value 2. This means that any values we currently have in our database for isCompleted will naturally map to the corresponding case in this enum. We will now use this Status enum to represent completion of the reminder instead of the simple isCompleted boolean: // var isCompleted = false var status: Status = .incomplete

SELECT 16:31

And because there’s probably already a lot of code out there using isCompleted we can provide a computed property for getting that information quickly: var isCompleted: Bool { status != .incomplete }

SELECT 16:44

With that done we do have a few seeded reminders that we now need to fix…

SELECT 17:05

And then we have a compiler error where we defined an isPastDue query helper: var isPastDue: some QueryExpression<Bool> { !isCompleted && (dueDate ?? Date.distantFuture) < Date() }

SELECT 17:13

We no longer have isCompleted , but we can define a helper for it on the table definition of the reminder just as we did on the reminder itself: extension Reminder.TableColumns { var isCompleted: some QueryExpression<Bool> { status.neq(Reminder.Status.incomplete) } … }

SELECT 17:40

And the final error in our project is when trying to toggle the isCompleted state on a reminder: try Reminder .find(reminder.id) .update { $0.isCompleted.toggle() } .execute(db)

SELECT 17:49

We no longer have this properly. We need to update this logic to use the 3-valued status column.

SELECT 17:53

But because there are 3 values, it’s a little complicated to do this. If the status is currently incomplete , we’d like to the status to completing , and otherwise flip the status to incomplete . SQL has support for switch-like statements, but the syntax is a little different. You use

ELSE 18:33

Which roughly translates to the following switch statement in Swift: switch status { case 0: 2; default: 0 }

ELSE 18:43

And our StructuredQueries library provides query builder helpers for this syntax: .update { $0.status = Case($0.status) .when( Reminder.Status.incomplete, then: Reminder.Status.completing ) .else(Reminder.Status.incomplete) }

ELSE 19:23

This is a bit of a mouthful, and it’s going to be a bummer to have to repeat this logic any place we want to simply toggle the status of a reminder.

ELSE 19:33

And luckily for us there is a better way!

ELSE 19:35

Just as we are able to cook up little query helpers on our tables by extending TableColumns , we can also cook up mutation helpers so that we can do something as simple as this: try Reminder .find(reminder.id) .update { $0.toggleStatus() } .execute(db)

ELSE 19:50

And under the hood this will expand to the SQL we want.

ELSE 19:55

The $0 in the trialing closure of update looks like it’s just the static description of the Reminder schema, but secretly it is actually a wrapper type that tracks the mutations you perform so that it can be translated into the equivalent SQL. That wrapper type is called Updates , and we can extend it to add new functionality: extension Updates<Reminder> { }

ELSE 20:22

Any properties or methods we add in this extension will immediately be available on $0 in the trailing closure.

ELSE 20:30

So, we can add a toggleStatus method: extension Updates<Reminder> { func toggleStatus() { } }

ELSE 20:36

And we can repeat the work we were doing over in the view in this method: extension Updates<Reminder> { func toggleStatus() { self.status = Case(self.status) .when( Reminder.Status.incomplete, then: Reminder.Status.completing ) .else(Reminder.Status.incomplete) } }

ELSE 20:45

This doesn’t compile: Cannot assign through dynamic lookup property: ‘status’ is a ‘let’ constant …because this method needs to be mutating. Mutation is how the Updates type keeps track of what things you changed on the table so that it can generate the correct SQL, and so let’s make it mutating: extension Updates<Reminder> { mutating func toggleStatus() { self.status = Case(self.status) .when( Reminder.Status.incomplete, then: Reminder.Status.completing ) .else(Reminder.Status.incomplete) } }

ELSE 20:57

And now everything compiles. However, things are not yet going to work. We have a few extra steps to perform.

ELSE 21:12

First of all, we haven’t even migrated our database so that SQLite understands this new column. Let’s do that now, though this migration is going to be a bit trickier than the ones we have encountered so far. Let’s start by registering a brand new migration: migrator.registerMigration( "Add 'status' to 'reminders'" ) { db in }

ELSE 21:36

We are doing this as a new migration because we are going to pretend that since the last migration was added we’ve already shipped a version of the app to our users, which means all migrations before this one are frozen in time and should never be edited again.

ELSE 21:48

First the easy part, we will add a new “status” column to the “reminders” table: try #sql( """ ALTER TABLE "reminders" ADD COLUMN "status" INTEGER NOT NULL DEFAULT 0 """ ) .execute(db)

ELSE 22:13

And now for the tricky part. Right now this will add a new column to the reminders table, but all values in that column will be 0. We need to copy over the current isCompleted value over to the new status column, which we can do as a big ole

UPDATE 22:41

And here we are getting to take advantage of the fact that the isCompleted column held 0’s and 1’s for its state, and that maps cleanly to the incomplete and complete values in the Status enum.

UPDATE 22:55

And finally we want to drop the isCompleted column because we no longer need it: try #sql( """ ALTER TABLE "reminders" DROP COLUMN "isCompleted" """ ) .execute(db)

UPDATE 23:16

A couple of interesting things to note about this migration:

UPDATE 23:18

First, this shows just how complicated migrations can get in real world code bases, and this is the kind of migration that SwiftData’s magical migrations cannot handle. To perform this kind of migration in SwiftData you would need to use its “manual migrations” tools, which are very cumbersome and require you to literally copy-and-paste your model.

UPDATE 23:34

And second, while this migration allowed us to model our domain very concisely, it would not be the correct thing to do if we were syncing this database to CloudKit. Once you distribute your schema to other devices your options for migrating your database because severely restricted, and one example of this is that you are not allowed to drop columns as we are doing here. We will have a lot more to say about this as we start to release our CloudKit syncing tools, but for now we will just assume that this app is local-only.

UPDATE 24:01

Ok, we have now completed the second step of implementing this feature, but still the app will not work as we expect. If we run in the simulator and complete a few reminders we will see that it works exactly as it did before we embarked on this refactor. Completing a reminder just makes it immediately animate away.

UPDATE 24:20

The reason why things are animating away immediately is because we are always filtering by isCompleted , which includes our new completing state. .where { if detailType != .completed && !showCompleted { !$0.isCompleted } }

UPDATE 24:35

And so this is also hiding the transitionary completing reminders, but we don’t want that. We want completing reminders to hang around until they have completely been transitioned to completed. And so we can update the logic to express that fact: .where { if detailType != .completed && !showCompleted { $0.status.neq(Reminder.Status.completed) } }

UPDATE 24:44

And now when we check off a reminder it sticks around, but it immediately animates down to the bottom of the list. We also need to update the sorting logic so that completing reminders hang around in place until they fully transition to the completed stage. Right now our ordering is quite simple: .order { $0.isCompleted }

UPDATE 25:07

Since the isCompleted helper checks if the status of the reminder is either complet ed or complet ing , this will put all incomplete reminders up top, and all others down below. We can adapt this to instead make it so that only fully completed reminders are put at the bottom, and then incomplete and completing reminders can remain up top: .order { $0.status.eq(Reminder.Status.completed) }

UPDATE 25:16

Now when we run the app we will see that checking a reminder does make the row re-render so that it looks completed, but it does not sort, and that’s because it is currently in the intermediate completing state.

UPDATE 25:25

We can further beef up this logic by making it depend on whether or not we are showing completed reminders in this list. The official Reminders app from Apple performs the delayed sorting only if you are not showing completed reminders. If you are showing completed reminders, then they animate away as soon as you complete them.

UPDATE 25:44

And thanks to the builder context we have in this trailing closure this is quite easy to do: .order { if showCompleted { $0.isCompleted } else { $0.status.eq(Reminder.Status.completed) } }

UPDATE 26:03

We are getting closer to implementing our user experience improvement, but there is still one last step to complete in the refactor. We are now ready to install a trigger so that we can listen for anytime a reminder is completed so that we suspend for a bit of time, and then finish transitioning the status of the reminder from complet ing to complet ed . This will require us to call a Swift function from a trigger, but let’s start by designing the trigger, and then we will figure out the Swift code we want to invoke from it. And this gives us an opportunity to explore another variation of triggers that we haven’t yet encountered.

UPDATE 26:33

Let’s get the basics into place. This will be an AFTER UPDATE trigger so that we can react to a reminder being updated: Reminder.createTemporaryTrigger( after: .update( ) )

UPDATE 26:57

And there’s a version of update that takes 3 trailing closures: try Reminder.createTemporaryTrigger( after: .update(of: { <#Reminder.TableColumns#> in <#code#> }, forEachRow: { old, new in <#code#> }, when: { old, new in <#code#> })) )

UPDATE 27:03

The 2nd and 3rd closures are ones that we have already come across in this series. The 2nd is the SQL query executed when the trigger is invoked, and the 3rd trailing closure allows us to skip the trigger when some condition is not satisfied.

UPDATE 27:14

The 1st trailing closure, though, is new to us, and it allows us to listen to a subset of columns for changes. So far we have been listening to all columns in the table for changes, and we could technically do that here, but it wouldn’t be very efficient. All we care about is when the status column of a reminder changes, and so we now have the ability to specify that our trigger only observe changes to that column: try Reminder.createTemporaryTrigger(after: .update { $0.status } forEachRow: { old, new in } when: { old, new in }) .execute(db)

UPDATE 27:47

In the next trailing closure we will want to invoke our Swift function, which doesn’t exist yet, but we can at least provide a stub for the name of the function: try Reminder.createTemporaryTrigger(after: .update { $0.status } forEachRow: { old, new in #sql("SELECT handleReminderStatusUpdate()") } when: { old, new in }) .execute(db)

UPDATE 28:06

And then in the final closure we will specify that we want this trigger to execute only when putting a reminder into the “completing” state: 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)

UPDATE 28:19

This is because only completing reminders need the extra logic of transitioning to completed after a few seconds of delay.

UPDATE 28:37

Next we need to register this handleReminderStatusUpdate function in the database: db.add( function: DatabaseFunction( "handleReminderStatusUpdate" ) { _ in return nil } )

UPDATE 29:02

And in this function we can perform the delaying logic. Since we aren’t in an asynchronous context at this point, we really have no choice but to create one via an unstructured task: db.add( function: DatabaseFunction( "handleReminderStatusUpdate" ) { _ in Task { } return nil } )

UPDATE 29:11

In this task we will sleep for a few seconds: Task { try await Task.sleep(for: .seconds(2)) }

UPDATE 29:18

And if we make it past this sleep we can update all of our transitionary reminders to be fully completed: @Dependency(\.defaultDatabase) var database try await database.write { db in try Reminder .where { $0.status.eq(Reminder.Status.completing) } .update { $0.status = .completed } .execute(db) }

UPDATE 30:06

This is nearly all it takes. If we run the app we will see that indeed upon checking a reminder it will hang out in the list for a few seconds, and then animate away.

UPDATE 30:25

However, the problem is if we start checking many reminders they will not reset their delay, so eventually they start animating away even though we are in the middle of completing other reminders.

UPDATE 30:42

What we need to do is keep track of the unstructured task we are creating so that we can cancel it at a later time if another reminder is updated. However, if we do this naively by creating a mutable Task reference that can be referenced inside the DatabaseFunction : var task: Task<Void, any Error>? db.add( function: DatabaseFunction( "handleReminderStatusUpdate" ) { _ in task?.cancel() … } ) …we are met with a compiler error: Reference to captured var ‘task’ in concurrently-executing code

UPDATE 31:04

This is happening because the trailing closure of DatabaseFunction is both escaping and sendable, and hence it cannot capture mutable state.

UPDATE 31:12

To fix this we need to wrap the mutable state in a concurrency-safe package, and we can use Swift’s relatively new Mutex tool for this. Let’s import the Synchronization library: import Synchronization

UPDATE 31:24

And then package up the task in the Mutex : let task = Mutex<Task<Void, any Error>?>(nil)

UPDATE 31:36

And then use the withLock method on the mutex in order to get access to the underlying mutable state: task.withLock { task in } The first thing we will do in here is cancel any existing inflight work: task.withLock { task in task?.cancel() }

UPDATE 31:45

And then we will fire up the new task and assign it to the mutable state: task = Task { … }

UPDATE 31:56

That is all it takes, and now our app works exactly how we want. We are free to complete and un-complete as many reminders we want in rapid succession, and they will not animate away until we have stopped for a few seconds. Conclusion

UPDATE 32:20

And we are now at the end of our the “Callbacks” portion of our “Modern Persistence” series. In the past 4 episodes we have shown just how powerful SQL triggers can be. They give you a global view into what is happening in your database so that you can react accordingly, and we explored 5 different use cases for this:

UPDATE 32:34

We showed how to automatically refresh timestamps in your database when a record is edited. Brandon

UPDATE 32:40

We showed how to make sure that a table always has at least one row by inserting a default value whenever it detects the last row is deleted. Stephen

UPDATE 32:50

We also used triggers to support a drag-and-drop feature by making sure that newly created reminders lists are inserted in the last position of the list. Brandon

UPDATE 32:57

We then used triggers to insert little bits of validation logic in our database, and it was cool to see how the validation was enforced at a global level across our entire database. Stephen

UPDATE 33:08

We used triggers to improve the user experience of our app by making it so that when you complete a reminder it does not immediately animate away. It waits on screen for a few moments so that if you are checking off other items you don’t accidentally complete the wrong one. Brandon

UPDATE 33:22

And honestly, this is only the beginning of what triggers are capable of. We heavily use triggers in the CloudKit synchronization tools we are currently building and hoping to release soon. We install triggers in the user’s tables so that we can observe when their data changes, and then we use that as the moment to send data to a CloudKit database for storing externally. Or at least that’s a simplified description of what we are doing. There’s a little more to it than just that, but suffice it to say that triggers play a major role in those tools. Stephen

UPDATE 33:52

We still have a lot more we want to discuss about “modern persistence” in general, but this is the end of this little mini-series on triggers.

UPDATE 33:40

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 0333-callbacks-pt4 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 .