Video #331: Persistence Callbacks: Type-Safe Triggers

Episode: Video #331 Date: Jul 14, 2025 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep331-persistence-callbacks-type-safe-triggers

Description

We build a bunch of triggers in a schema-safe, type-safe way using APIs from the StructuredQueries library, including a callback that ensures the Reminders app always has at least one list, and a callback that helps us support drag–drop positional ordering of lists.

Video

Cloudflare Stream video ID: 915aa46c9a1a2b9e3adedf0501222546 Local file: video_331_persistence-callbacks-type-safe-triggers.mp4 *(download with --video 331)*

References

Transcript

— 0:05

I think this is pretty incredible, and I think it shows that SQLite has a ton of untapped power. When implementing far reaching behavior across our databases we should always first ask whether or not SQLite already provides a tool for this. If it does, then it is far better to delegate that responsibility to SQLite than for us to try to recreate it in our Swift application. After all, SQLite is the true arbiter of the data in our application. Stephen

— 0:31

This is looking really great, but we can make some improvements. Right now we are creating our trigger as a SQL string. On the one hand, this isn’t so bad. We have never said that all queries in your app should be written with our query builder. We think it’s incredibly important to be familiar with SQL syntax, and we never want to hide it from you. Brandon

— 0:50

But, the primary situation we advocate for writing SQL strings is in migrations, like what we have done when creating and altering our tables. The reason we advocate for SQL strings here is because migrations are little SQL fragments that are frozen in time. Once a migration has been shipped to users it should never be edited. Ever.

— 1:10

And so it’s not appropriate to use static symbols in creating those SQL statements because that would leave us open to accidentally changing those statements later on by doing something seemingly innocuous such as renaming a field in our data types. Stephen

— 1:23

However, our trigger is not being created in a migration. In fact, we are installing the trigger after all migrations have run, and it’s only temporary. The trigger will be removed when the app is killed and will be reinstalled when the app starts up again. This means it is perfectly OK to use static symbols in order to construct temporary triggers because they always have access to the most recent form of the schema. They never have to deal with older versions of the schema that are frozen in time.

— 1:48

And so we can actually add a bit of type-safety and schema-safety to the creation of our triggers by using the static symbols created for us by the @Table macro. Let’s take a look. Type-safe triggers

— 2:00

As a first pass at adding a bit of safety to our trigger we can interpolate parts of the statically known schema directly into the #sql query string. For example, interpolating the metatype of Reminder : AFTER UPDATE ON \(Reminder.self) …it will simply insert the quoted name of the table into the query.

— 2:19

We can do that for the

UPDATE 2:23

We can also interpolate the static columns of updatedAt and id directly into the query: SET \(Reminder.updatedAt) = datetime('subsec') WHERE \(Reminder.id) = "new"."id";

UPDATE 2:33

But if we run this it sadly causes a SQL syntax error: ModernPersistence/ModernPersistenceApp.swift:12: Fatal error: ‘try!’ expression unexpectedly raised an error: SQLite error 1: near “.”: syntax error - while executing `CREATE TEMPORARY TRIGGER “reminders_updatedAt”

UPDATE 2:45

The problem with this is by default this kind of interpolation expands to the following SQL: SET "reminders"."updatedAt" = datetime('subsec');

UPDATE 2:51

The qualification of updatedAt with reminders is almost always the right thing to do. When writing regular SQL statements we want to qualify columns with their tables because we may be joining two tables that have overlapping column names, such as id , or title , or updatedAt . And to prevent us from constructing SQL statements with ambiguous column names we need to perform this qualification.

UPDATE 3:09

However, in

UPDATE 3:29

We can still use the static description of the column, but we just have to access its name property, which is the raw string name of the column, and we have to quote it as a string: try #sql( """ CREATE TEMPORARY TRIGGER "reminders_updatedAt" AFTER UPDATE ON \(Reminder.self) FOR EACH ROW BEGIN UPDATE \(Reminder.self) SET \(quote: Reminder.updatedAt.name) = datetime('subsec') WHERE \(quote: Reminder.id.name) = "new".\(quote: Reminder.id.name);

END 3:53

Now this runs just fine, and works exactly as it did before, but our query is a little safer because we are getting to use the static information provided to us from the @Table macro.

END 4:04

But we can do better. We have recently released an update to StructuredQueries that gives us access to query building tools for temporary triggers. This means we can write this statement in 100% Swift code and make use of autocomplete in order to help guide us to constructing this query.

END 4:19

It starts by invoking the new createTemporaryTrigger static method on a table: Reminder.createTemporaryTrigger( after: <#TemporaryTrigger<Reminder>.Operation#> )

END 4:34

For the after argument we can type . in order to see what we have available to us from autocomplete: Reminder.createTemporaryTrigger(after: .<#⎋#>)

END 4:39

We can see we have a choice of delete, insert, or update. In our situation we want to execute our trigger after updating: Reminder.createTemporaryTrigger( after: .update( forEachRow: <#(TemporaryTrigger<Reminder>.Operation.Old, TemporaryTrigger<Reminder>.Operation.New) -> Statement#> ) )

END 4:42

This update value takes a trailing closure: Reminder.createTemporaryTrigger( after: .update { } ) And this trailing closure takes two arguments for the old and new keywords that we previously saw is made available to us in an update trigger: Reminder.createTemporaryTrigger( after: .update { old, new in } )

END 4:52

We don’t need the old value, but we do need the new in order to scope the update to the row that was just updated: Reminder.createTemporaryTrigger( after: .update { _, new in } )

END 4:57

And now inside this trailing closure we are free to execute a

UPDATE 5:11

And then inside this trailing closure we are free to make mutations to the columns of the reminder, which is represented by $0 . We can even use autocomplete to see what we have available to us: Reminder.createTemporaryTrigger( after: .update { _, new in Reminder.update { $0.<#⎋#> } } )

UPDATE 5:19

We have access to all of the columns in the Reminder table. And in here we want to update the updatedAt value, but the question is what do we assign it to: Reminder.createTemporaryTrigger( after: .update { _, new in Reminder.update { $0.updatedAt = <#???#> } } )

UPDATE 5:32

And this can be a little tricky. We have to remember that all of this Swift syntax here is really only building a SQL query. This Swift code doesn’t represent the actual code being run in our database. And so if we did something naive, such as assigning a Swift date: Reminder.createTemporaryTrigger( after: .update { _, new in Reminder.update { $0.updatedAt = Date() } } ) …this will not be correct. This will construct a single SQL query, with the current date bound to the query, and then use that as the trigger. We aren’t going to be getting the freshest date each time the trigger is invoked.

UPDATE 5:40

And we can see this concretely by finishing up the creation of this trigger by executing the SQL: try Reminder.createTemporaryTrigger( after: .update { _, new in Reminder.update { $0.updatedAt = Date() } } ) .execute(db) …and when we run the app we get a purple runtime warning letting us know what went wrong: Runtime warning Cannot bind a date to a trigger statement. Specify dates using the ‘#sql’ macro, instead.

UPDATE 5:52

This is because the SQL for the trigger is generated a single time and installed in SQLite, and so it would be using the constant Swift date from the time the trigger was installed each time, and not the current date from whenever the trigger actually executes.

UPDATE 6:09

So, we can’t use a Swift date in the trigger. We instead need to compute the current date in SQL so that we can get the freshest date at the moment that the trigger is triggered. We can dip down into a SQL string to get access to that date function: try Reminder.createTemporaryTrigger( after: .update { _, _ in Reminder .update { $0.updatedAt = #sql("datetime('subsec')") } } ) .execute(db)

UPDATE 6:31

And finally we will want to make sure to scope this update to only the row that was updated: try Reminder.createTemporaryTrigger( after: .update { _, new in Reminder .find(new.id) .update { $0.updatedAt = #sql("datetime('subsec')") } } ) .execute(db)

UPDATE 6:46

And now this is the correct trigger to use. We can run the app to see that it works exactly as it did before, but now we have a type-safe and schema-safe way of creating this trigger.

UPDATE 7:19

But also, updating timestamps like this is a pretty common thing. We may want to do this with all of our tables, and we may have dozens of tables. So, it would be nice if there was a shorter syntax, and luckily we offer just that.

UPDATE 7:35

The act of “touching” a column of a row when it’s updated is common enough that we have a special trigger helper just for that: try Reminder .createTemporaryTrigger( afterUpdateTouch: { $0.updatedAt = #sql("datetime('subsec')") } ) .execute(db)

UPDATE 7:58

This allows you to modify any column of the reminders table after a reminder is updated.

UPDATE 8:07

But the act of specifically touching a date column is so common, that we even provide a special overload just for that: try Reminder .createTemporaryTrigger(afterUpdateTouch: \.updatedAt) .execute(db)

UPDATE 8:23

And now this fits on one line, which means it will be very easy to add this to every table in our database if we so desire. And if we run the app we will see that it works exactly as before, which a reminder’s updatedAt date getting automatically set whenever we make edits to it.

UPDATE 8:45

If we create a new reminder we’ll see that sadly it is not given a createdAt date. This is because we need to introduce an AFTER INSERT trigger, as well, which we can do with a very similar API: try Reminder .createTemporaryTrigger(afterInsertTouch: \.createdAt) .execute(db)

UPDATE 9:17

And with that, everything works as expected.

UPDATE 9:29

It’s really cool to see how these two statements install a global listener in our database so that we can make sure no matter how data is inserted or updated in our database, the createdAt and updatedAt timestamps will always be refreshed correctly. And even better that we didn’t have to litter our model code with little beforeSave , afterSave handlers that is just noisy, and as we have seen, come with lots of gotchas. Case study: non-empty tables

UPDATE 9:53

OK, we have now given a crash course in triggers for SQL. They allow us to install a global listener in our database so that we can declaratively react to changes in our tables and perform additional actions. The canonical example of a trigger is that of automatically refreshing an updatedAt timestamp on a model when its row in the database changes. Not only have we accomplished that in our reminders app, but we were even able to create the trigger using our SQL building library so that we get a bit of type-safety and schema-safety.

UPDATE 10:22

And we want to take a moment to reiterate that we feel that triggers are the correct tool for this kind of thing. They are the only way to get a fully global view of all the events happening in our database and react accordingly. Things like callbacks seem really handy at first, but they have a lot of sharp edges, and we just feel that we should let SQLite do the things it excels at while we let Swift do the things it excels at. Brandon

UPDATE 10:46

Now that we know the basics of triggers, let’s amp things up. We are going to explore some more advanced topics in triggers in order to show off what’s possible. It can sometimes be surprising what is possible to accomplish with triggers, and so we want to take this moment to help spark some creativity in everyone.

UPDATE 11:02

Let’s dig in.

UPDATE 11:07

The first example of a trigger we will explore is to implement a special behavior that prevents us from ever having no reminders lists in our app. Right now we are free to delete every list…

UPDATE 11:20

And then we are left in a bit of weird state with no lists. What if we could make it so that as soon as you delete the last reminders list we go ahead and insert a stub of a list for you. That way it makes it clear that we intend for our users to always have one list active.

UPDATE 11:37

Well, this sounds like a good use case for a trigger. We would want to listen for deletions on the RemindersList table, and when such a deletion causes the table to become empty, we would want to go ahead and insert a stub of a list so that we can make sure the table is never empty.

UPDATE 12:42

So, let’s go back to the appDatabase function where our other trigger is, and let’s create a new temporary trigger: RemindersList.createTemporaryTrigger( after: <#TemporaryTrigger<Reminder>.Operation#> )

UPDATE 13:19

We will want to listen for deletions so we can provide the .delete option to the after argument: RemindersList.createTemporaryTrigger( after: .delete { old in } )

UPDATE 13:23

And when we detect a deletion we want to insert a new stub of a reminders list, which we will just give a generic title of “Reminders”: RemindersList.createTemporaryTrigger( after: .delete { old in RemindersList .insert { RemindersList.Draft(title: "Reminders") } } )

UPDATE 13:44

This is close to what we want, but it’s not quite right. This trigger is currently listening for any deletion on the RemindersList table, and then inserting a new list.

UPDATE 13:53

We can see why this is wrong by first execute this query in order to actually install the trigger: try RemindersList.createTemporaryTrigger( after: .delete { RemindersList .insert { RemindersList.Draft(title: "Reminders") } } ) .execute(db)

UPDATE 13:58

And now we can run the preview for the reminders lists to see that each time we delete a list, a new “Reminders” list is inserted.

UPDATE 14:10

We want to tweak this trigger logic a bit. We only want to execute our trigger when the RemindersList table is completely emptied out. And luckily for us SQL triggers support this functionality. If we go back to the SQLite documentation and look at the flow chart for triggers we will see a

END 14:42

And our query builder allows us to specify this

WHEN 14:50

Inside this trailing closure we are allowed to execute any query that returns a boolean value, and that boolean will be used to determine if the action from the trigger is actually run.

WHEN 14:59

And we just want to check if there are any rows in the RemindersList , and this can be done quite simply: try RemindersList .createTemporaryTrigger( after: .delete { _ in RemindersList .insert { RemindersList.Draft(title: "Reminders") } } when: { _ in !RemindersList.exists() } ) .execute(db)

WHEN 15:41

Now we can run the preview to see that everything works exactly as we expect. The moment we delete the last list, a new one for “Reminders” slides into place. And of course it works no matter how you delete reminders lists. We can even quickly add a button to the UI that when tapped deletes all lists: ToolbarItem(placement: .primaryAction) { Menu { Button("Delete all lists") { @Dependency(\.defaultDatabase) var database try! database.write { db in try RemindersList.delete().execute(db) } } } label: { Image(systemName: "ellipsis.circle") } }

WHEN 16:53

And when tapping this button we will see all lists animate away, and then immediately the new, default one appears.

WHEN 17:03

It’s really just incredible how easy it was to add this feature. Without triggers we would have had to scour our entire codebase to find any places we might potentially be deleting a reminders list. Then, after that, check if the reminders lists table is empty. And if it is, create a new default reminders list. And we would never be 100% sure that it definitely works correctly. We can only hope.

WHEN 17:25

Now there is one situation in which our application can still have no lists, and that is when a release build is first installed. In release builds we do not automatically seed initial data, and this includes the stub list that our delete trigger automatically inserts.

WHEN 18:06

To address this, we should also detect when no lists exist when the app first starts up and repeat this logic outside of the trigger. We’ll leave this as an exercise for the viewer. Case study: positional ordering

WHEN 18:31

OK, that was a quick and fun trigger to add to our app. Our database is acting as a global gatekeeper for the data in our application, and that gives us infinite flexibility in seeing when rows are inserted, updated or deleted. And we just leveraged that to make sure that we always have at least one reminders list in our screen. Stephen

WHEN 19:04

Let’s move onto another fun example of triggers. In the real Reminders app on iOS devices you can drag and drop lists to reorder them however you want. That functionality alone does not need the power of triggers, but what does need triggers is what happens when you add a new list. A newly added list should have its position be one more than the max position across the entire app, and inserting that little bit of logic at the database layer means we never have to think about setting the initial position correctly in our app code.

WHEN 19:34

Let’s take a look.

WHEN 19:37

Let’s start by updating our Swift domain model so that it has a position field: @Table struct RemindersList: Equatable, Identifiable { let id: Int var color = 0x4a99ef_ff var position = 0 var title = "" }

WHEN 19:46

Next we will create a new migration to add this column to the table: migrator.registerMigration( "Add 'position' to 'remindersLists'" ) { db in try #sql( """ ALTER TABLE "remindersLists" ADD COLUMN "position" INTEGER NOT NULL DEFAULT 0 """ ) .execute(db) }

WHEN 20:30

Now technically it would have been OK to combine this migration with our previous one that added the createdAt and updatedAt columns to the reminders table. But that is only OK because we haven’t released an update of our app that caused our users t run that migration on their devices yet. If we had released an update between when that previous migration was created and this migration, then we would have no choice but to create a brand new migration from scratch.

WHEN 20:48

Next we will update our seeded data so that we can assign unique positions to the lists: RemindersList( id: 1, color: 0x4a99ef_ff, position: 0, title: "Personal" ) RemindersList( id: 2, color: 0xef7e4a_ff, position: 1, title: "Family" ) RemindersList( id: 3, color: 0x7ee04a_ff, position: 2, title: "Business" )

WHEN 21:00

And then we will update the RemindersListsView so that we order the lists by their position: RemindersList .order(by: \.position) …

WHEN 21:10

Now the question is, how do we allow the user to drag-and-drop the reminders lists so that they can reorder them however they want?

WHEN 21:16

Well, luckily SwiftUI comes with a wonder SwiftUI view modifier just for that: .onMove { source, destination in }

WHEN 21:41

The very act of adding that view modifier signals to SwiftUI that it should allow dragging and dropping the rows of the list. We can even give it a test in the preview. However, when dropping a list into a new position it acts a little strangely because we haven’t actually implemented the logic that updates the position field on all of the lists.

WHEN 22:07

To do that we will invoke a method on the model that passes the source and destination values: .onMove { source, destination in model.moveRemindersList( fromOffsets: source, toOffset: destination ) }

WHEN 22:24

…and then it will be the responsibility of the model to implement this logic: func moveRemindersList( fromOffsets source: IndexSet, toOffset destination: Int ) { }

WHEN 22:39

We can defer most of the complex parts of this implementation to a helper method provided by SwiftUI. There is a method called move(fromOffsets:toOffset:) defined in SwiftUI on mutable collections: [].move(fromOffsets: <#T##IndexSet#>, toOffset: <#T##Int#>)

WHEN 22:51

And it will mutate the collection to move some elements to its final destinations.

WHEN 22:57

To leverage this method we will create a mutable collection of our reminders lists, in the order it is being displayed in the UI right now: var remindersLists = remindersListRows.map(\.remindersList)

WHEN 23:16

Then we can apply SwiftUI’s move method: remindersLists.move(fromOffsets: source, toOffset: destination)

WHEN 23:22

Next we can iterate over this reordered collection, along with the offsets of each element, and use those offsets to update the position of the lists: withErrorReporting { try database.write { db in for (offset, remindersList) in remindersLists.enumerated() { try RemindersList .find(remindersList.id) .update { $0.position = offset } .execute(db) } } }

WHEN 24:31

That’s all it takes, but also it seems like all we needed from our reminders lists was their IDs. So we can make this a tad more efficiently by just grabbing their IDs: var remindersListIDs = remindersListRows.map(\.remindersList.id) remindersListIDs.move(fromOffsets: source, toOffset: destination) withErrorReporting { try database.write { db in for (offset, remindersListID) in remindersListIDs.enumerated() { try RemindersList .find(remindersListID) .update { $0.position = offset } .execute(db) } } }

WHEN 24:58

We can run the app in the simulator, reorder the lists however we want, and when we relaunch the app we will see the lists are still in that same order.

WHEN 25:12

Further, we can reorder the list again, and then check out the logs to see exactly what happened: BEGIN IMMEDIATE TRANSACTION; UPDATE "remindersLists" SET "position" = 0 WHERE ("remindersLists"."id" = 3); UPDATE "remindersLists" SET "position" = 1 WHERE ("remindersLists"."id" = 2); UPDATE "remindersLists" SET "position" = 2 WHERE ("remindersLists"."id" = 1); COMMIT TRANSACTION; PRAGMA query_only = 1; BEGIN DEFERRED TRANSACTION;

SELECT 25:36

However, what does not yet work is the positioning of newly created reminders lists. If we create a new reminder it is placed towards the top because its default position is 0. We would rather if it was put at the end of the list.

SELECT 26:02

What we can do is create a trigger that executes after a new reminders list is inserted into the database, and then update it so that it’s position is last. Let’s start by creating a new AFTER INSERT trigger: try RemindersList.createTemporaryTrigger( afterInsertTouch: { } ) .execute(db)

SELECT 0:00

Inside the trailing closure we can provide the query we want to execute when this event is triggered. The easiest way to do this is to just compute the number of reminders lists currently in the table, and then subtract 1: try RemindersList.createTemporaryTrigger( afterInsertTouch: { $0.position = RemindersList.count() - 1 } ) .execute(db) That way when inserting the first list it will have position 0, the second list will have position 1, and so on.

SELECT 26:50

That’s all it takes. Now when we create a new reminders list it is naturally added to the bottom of the UI. Next time: More advanced triggers

SELECT 27:10

That was surprisingly easy! We have yet another trigger in our database, this time for making sure that newly created reminders lists are inserted in the right spot. We hope this is giving all of our viewers some inspiration for figuring out where little bits of logic can be extracted out of your app code and put into the database layer, where it can truly be enforced 100% of the time. Brandon

SELECT 27:29

Let’s play around with another trigger, and this one will be a little different. Let’s say that we only want users to be able to associate at most 5 tags with a reminder. This kind of validation logic is easy enough to implement in SwiftUI. When the user taps the “Save” button we can simply check how many tags are assigned, and if it’s more than 5 we refuse to actually save the data and display an alert.

SELECT 27:50

But that is a weak enforcement of this rule. Nothing is stopping other parts of the code base from sneaking in some extra tags, breaking the rule that at most 5 tags should be associated with a reminder. A better way to enforce this would be to install a trigger that makes sure one is never allowed to assign more than 5 tags to a single reminder.

SELECT 28:14

And it’s surprising just how easy it is to implement this with triggers. But, first, we don’t even currently have a way for users to associate tags with reminders in our app. We never built out that part of the UI. So let’s do that, and then write a trigger…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 0331-callbacks-pt2 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 .