Video #336: Modern Search: Full-Text Search

Episode: Video #336 Date: Aug 25, 2025 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep336-modern-search-full-text-search

Description

We start to leverage SQLite’s built-in full-text search capabilities to power our feature. We learn about virtual tables, create one that stores the searchable data, populate it with the help of database triggers, and show just how powerful and succinct search can be.

Video

Cloudflare Stream video ID: b23257005cc49b94cd53bb99dfb126e3 Local file: video_336_modern-search-full-text-search.mp4 *(download with --video 336)*

References

Transcript

— 0:05

OK, we have now explored some pretty advanced topics in searching for text inside of a database. The actual search technique is pretty rudimentary, just splitting on the search text to get each search term, and then checking if various columns contain those search terms. But we have layered on some real world, advanced requirements too, such as searching the reminders’ titles, notes and tags, and we are computing a count of completed reminders right alongside the search results. Brandon

— 0:29

But let’s now improve on the actual search technique being employed here. There is a technology known as “full-text search” that is built into many databases, such as Postgres and MySQL, and of course SQLite has fantastic support for it. Full-text search allows you to maintain a separate table, or really tables , of indexed information about the text in your application so that it can quickly and efficiently search large corpuses of data. It also has some fancy tricks up its sleeve that allow you to rank search results and even highlight the search terms in the results.

— 1:06

Let’s see what it takes to replace our rudimentary substring search technique with a more high-powered full-text search technique. Virtual tables

— 1:15

Full-text search in SQLite is implemented via what they call the “virtual table mechanism”, which is kind of like a plug-in system for SQLite databases. It allows you to install a module into SQLite, which then allows users of SQLite to create virtual tables. These tables look mostly like regular tables that one can create in SQLite, but behind the scenes interactions with these tables via SQL call into methods of the module that was installed rather than executing actual SQL.

— 1:47

And this is the mechanism that is used to implement full-text search in SQLite. One starts by defining a regular table in Swift using our tools that will hold all of the information we wish to index using full-text search, which we will call ReminderText : @Table struct ReminderText { }

— 2:28

Note that we are using the @Table macro even though this will not be a standard table held in the database. But by using @Table we will get access to all of the querying tools we know and love from StructuredQueries.

— 2:43

Now we can just add a bunch of fields to this type that represents the various pieces of text we want to search that is related to a reminder. For example, we definitely want to be able to search for reminders’ titles and notes, so we can start there: @Table struct ReminderText { let title: String let notes: String }

— 2:54

We further want to be able to search tags, and one might hope we can store tags as an array of strings that is encoded into JSON for SQLite, just like we have done in the past: @Table struct ReminderText { let title: String let notes: String @Column(as: [String].JSONRepresentation.self) let tags: [String] }

— 3:28

However, this is not correct to do. Full-text search is a technology that wants to work with plain strings. It would not be appropriate to search a JSON encoded string for search terms. And further, many of the tools that enhance full-text searching, such as term highlighting and snippet extraction, assume we are dealing with simple, structureless strings, and so let’s keep it that way: @Table struct ReminderText { let title: String let notes: String let tags: String }

— 3:58

And then when it comes time to populate this field we will simply concatenate all of the tags together with a delimiter, such as a comma.

— 4:11

This is nearly all it takes to define a virtual table as far as Swift is concerned, but there are just two more things we need to do. First of all, we need to hold onto a reminder ID in this type: @Table struct ReminderText { let reminderID: Reminder.ID let title: String let notes: String let tags: String }

— 4:29

This will make it so that we can join our reminders table to this table of our searchable text and then filter reminders based on search terms matching in this virtual table.

— 4:43

And then finally we need to apply a protocol to our ReminderText type in order to designate it as a virtual table and unlock many of the tools SQLite defines for full-text search. The protocol is called FTS5 because “FTS” is an acronym for “full-text search”, and this is the 5th version of the tools that have been implemented in SQLite: @Table struct ReminderText: FTS5 { let reminderID: Reminder.ID let title: String let notes: String let tags: String }

— 5:46

However, this FTS5 protocol exists in our StructuredQueries library, and GRDB also defines a symbol with the same name. So, we have to disambiguate it by prefixing with our module name: @Table struct ReminderText: StructuredQueries.FTS5 { let reminderID: Reminder.ID let title: String let notes: String let tags: String }

— 5:59

OK, so that is what it takes to model a Swift data type that will represent a full-text search virtual table.

— 6:06

Next we need to run the actual SQL that will create the virtual table for our ReminderText type. In many ways this process is very similar to creating a regular table, but there are some key differences.

— 6:20

We start by defining a migration, just as we would when introducing a table to an application. This gives us an opportunity to run a piece of code one single time on a device, and this is where it is appropriate to create new tables or make alterations to existing tables.

— 6:48

So, let’s register a migration: migrator.registerMigration("Create reminders FTS table") { db in }

— 7:00

Inside this migration we will run some SQL for creating the virtual table, which we will do using our #sql macro: migrator.registerMigration("Create reminders FTS table") { db in try #sql( """ """ ) .execute(db) }

— 7:34

And here is where things start to deviate from normal SQL tables. Virtual tables are created with a specific syntax, and we can even head over to the documentation to see a brand new flow chart for creating virtual tables:

— 7:50

We start with CREATE VIRTUAL TABLE , and there is an option to skip creating if the table already exists, but we don’t need that right now: try #sql( """ CREATE VIRTUAL TABLE """ )

— 8:19

Next we specify the name of the table, which will be reminderTexts : try #sql( """ CREATE VIRTUAL TABLE "reminderTexts" """ )

— 8:34

Next we specify

USING 8:56

This fts5 module is what contains the implementation of the full-text search functionality. When we invoke FTS functions in our SQL queries, as we will soon enough, it will secretly invoke methods inside this module.

USING 9:15

Next according to the flow chart we open parentheses and provide arguments to the module: try #sql( """ CREATE VIRTUAL TABLE "reminderTexts" USING fts5( ) """ )

USING 9:22

The arguments that one provides to a virtual table module depends on how the module is defined. The fts5 module requires that one first pass the names of the columns you want to hold in the virtual table, which in our case is the reminderID , title , notes , and tags columns: try #sql( """ CREATE VIRTUAL TABLE "reminderTexts" USING fts5( "reminderID", "title", "notes", "tags" ) """ )

USING 10:00

One very unique thing about virtual tables is that their columns do not have types. It’s unfortunate, but it’s just how things are, and so we will need to be careful to make sure to only store strings in this virtual table, and luckily our query building tools will help us with that.

USING 10:23

After all columns are specified we are allowed to provide a number of arguments to customize full-text searching. The most important one is the tokenizer: try #sql( """ CREATE VIRTUAL TABLE "reminderTexts" USING fts5( "reminderID", "title", "notes", "tags", tokenize= ) """ )

USING 10:49

The fts5 module comes with 4 tokenizers, which we can find info about in the SQLite documentation .

USING 11:17

Without getting into the weeds on these tokenizers and the various ways to customize them, a good default to start with is the trigram tokenizer: try #sql( """ CREATE VIRTUAL TABLE "reminderTexts" USING fts5( "reminderID", "title", "notes", "tags", tokenize='trigram' ) """ )

USING 11:31

This tokenizer allows you to search for substrings inside words instead of matching the entire token. There are other tokenizers, and even arguments you can pass to the tokenizer to customize it, but we don’t need any of that power right now.

USING 11:55

And one last change we will make to this CREATE VIRTUAL STATEMENT is to mark that the reminderID column is

UNINDEXED 12:08

This makes it so that SQLite does not generate any indexing information about that column because we don’t need to ever perform full-text searches on it. We only need that column in order to join our reminders table to this table so that we can filter reminders based on search terms. So that small change can help optimize the storage for our virtual table.

UNINDEXED 12:34

We can even see what the virtual table is doing under the hood by running our app so that the migration is executed, and then opening our database to inspect the tables. We will find that there are actually 6 tables related to reminderTexts : reminderTexts reminderTexts_config reminderTexts_content reminderTexts_data reminderTexts_docsize reminderTexts_idx

UNINDEXED 13:07

These additional 5 tables are known as “shadow” tables and should be thought of as an implementation detail of the virtual table. You shouldn’t ever insert, update, or delete info in these tables.

UNINDEXED 13:30

Further, if we inspect the structure of the reminderTexts table we will find that none of the columns have types defined, as we mentioned before, but also all of the columns are nullable. This means it is technically possible to insert

NULL 14:01

OK, that’s all it takes to set up our virtual table. We have a Swift data type that describes it so that we can construct type-safe queries, and we have a migration that actually creates the virtual table in our database. But how can we use this new virtual table to actually search for reminders? Stephen

NULL 14:17

Well, once the FTS5 virtual table is in place we get access to a whole bunch of new powerful APIs for searching through the virtual table. And luckily for us, our StructuredQueries library has fantastic support for these APIs.

NULL 14:29

Let’s take a look.

NULL 14:32

We are currently searching through our reminders using the following where clause: .where { reminder, _, tag in for term in searchText.split(separator: " ") { reminder.title.contains(term) || reminder.notes.contains(term) || (tag.title ?? "").hasPrefix(term) } }

NULL 14:36

On the one hand it is pretty cool to see how we can leverage result builders to simply loop over all the sub-terms of the search text and check if any of the reminder title, notes, or tags contains that term. But on the other hand, this kind of searching is very naive. We are only checking if our search term exists wholly as a substring of a field, or in the case of tags, check if it is a prefix. But full-text search can employ more advanced searching techniques such as stemming, lemmatization, and can even perform fuzzy matching to handle typos. And if there are more tables related to reminders that we want to search we will have to join them in, potentially making this query very inefficient.

NULL 15:15

So, rather than joining the ReminderTag and Tag tables in order to perform this search, we can simply join only the ReminderText table in the base query: let query = Reminder .join(ReminderText.all) { $0.id.eq($1.reminderID) }

NULL 15:31

Notice that we can perform this join as if ReminderText were a normal table.

NULL 15:37

And then the real magic comes with a brand new match method defined on reminderText ’s table definition, which we have access to in the where clause: let query = Reminder .join(ReminderText.all) { $0.id.eq($1.reminderID) } .where { reminder, reminderText in reminderText.match(<#StringProtocol#>) }

NULL 15:48

We can specify the full search text in this method and full-text search will do the hard work of splitting it up into search terms and searching all of the data in the ReminderText table: let query = Reminder .join(ReminderText.all) { $0.id.eq($1.reminderID) } .where { reminder, reminderText in reminderText.match(searchText) }

NULL 15:57

So it’s pretty nice that we don’t have to join tables just to search the tags and we don’t have to do all that splitting logic to search for each sub-term.

NULL 16:12

Now the rest of this function is not compiling because we have fundamentally changed the base query. First, in computing the completedCount property we now only have two tables we are dealing with instead of 3: completedCount: query .where { reminder, _ in reminder.isCompleted } .select { reminder, _ in reminder.id.count(distinct: true) } .fetchOne(db) ?? 0,

NULL 16:24

And so that means this query will be a lot more efficient since we are no longer joining a many-to-many table association.

NULL 16:29

But we still do need to join the tags table in the query that selects reminders because we still need to compute the array of tags for each found reminder, but it’s nice that we get to localize these joins to just this one query: rows: query .group { reminder, _ in reminder.id } .leftJoin(ReminderTag.all) { $0.id.eq($2.reminderID) } .leftJoin(Tag.all) { $2.tagID.eq($3.id) } .join(RemindersList.all) { $0.remindersListID.eq($4.id) } .order { reminder, _, _, _, _ in reminder.isCompleted } .select { reminder, _, _, tag, remindersList in Row.Columns( color: remindersList.color, isPastDue: reminder.isPastDue, reminder: reminder, tags: tag.jsonTitles ) } .fetchAll(db)

NULL 17:02

Now everything is compiling, and it would be pretty amazing if everything just magically worked, but of course that would be too good to true. Let’s run the app in the simulator and search for “week” to see that there are no results displayed.

NULL 17:21

We can see in the logs that our search query was successfully executed:

MATCH 17:29

Yet, no results.

MATCH 17:30

Well, this is because we haven’t actually inserted any data into the ReminderText table. That table represents an indexed and searchable version of the text in our tables, and so we need to insert the things we want to be able to search into that virtual table.

MATCH 17:41

There are a variety of ways one can accomplish this, but the easiest one to get started with is to use a powerful tool that we devoted a 4-part series of episodes to: triggers! SQL triggers allow us to listen for events in our database so that we can react to them. And so one thing we could try is to listen for any insertions into the reminders table and react to it by inserting the data we want to be able to search into the remindersText table.

MATCH 18:05

We will take this one step at a time since it’s been a few weeks since we dealt with triggers. Right after the migration for our database is run we create a temporary trigger using the tools of our StructuredQueries library: Reminder.createTemporaryTrigger( ) Note that there is no need to persist this trigger, and it is totally fine to just creating it every the app boots up.

MATCH 18:28

Next we will specify that we want to listens for insert events in the database and we want to react to it after the insert has happened: Reminder.createTemporaryTrigger( after: .insert { new in } )

MATCH 18:32

The query we specify inside this trailing closure will be executed after a new row is inserted into the reminders table. We want to execute an insertion on the reminderTexts table so we can start with the basics: Reminder.createTemporaryTrigger( after: .insert { new in ReminderText.insert { } } }

MATCH 18:46

And then inside here we need to somehow specify the columns and data to insert into ReminderText . One can do this in a manual way by literally specify each column and the value to insert to each column: ReminderText.insert { ($0.reminderID, $0.title, $0.notes, $0.tags) } values: { (new.id, new.title, new.notes, "") } We can use an empty string for tags because a newly created reminder does not have any tags. To capture tags in ReminderText we will eventually need to add more triggers, but we will deal with that later.

MATCH 19:20

But this isn’t ideal because we need to remember to specify all of the columns, and if we forget then

NULL 19:32

Luckily there is a better way. We can apply the @Selection macro that we have used a number of times in our “Modern Persistence” collection in addition to the @Table macro: @Table @Selection struct ReminderText: StructuredQueries.FTS5 { … }

NULL 19:38

This one change gives us access to a new inner type of ReminderText called Columns : ReminderText.Columns( reminderID: <#QueryExpression#>, title: <#QueryExpression#>, notes: <#QueryExpression#>, tags: <#QueryExpression#> )

NULL 19:50

And to fill in the fields for Columns we can use the special new value that handed to the insert trailing closure. This value holds the data from the Reminder table that was just inserted: Reminder.createTemporaryTrigger( after: .insert { new in ReminderText.insert { ReminderText.Columns( reminderID: new.id, title: new.title, notes: new.notes, tags: "" ) } } )

NULL 20:00

We’ve already come across this Columns type a few times because it is what we use when we want to mould the data we fetched from the database into a specific shape. For example, in the search feature we use it to construct the data for each row of the search results: .select { reminder, _, _, tag, remindersList in Row.Columns( color: remindersList.color, isPastDue: reminder.isPastDue, reminder: reminder, tags: tag.jsonTitles ) }

NULL 20:23

Well, this type can also be used to insert data into a table, as we just saw. By constructing it in the context of an insert statement, StructuredQueries will pluck out the columns and values from this type and construct the appropriate SQL insert statement.

NULL 20:38

With that done we might hope that everything just works. We can run the app in the simulator, search for some terms like “week”, but sadly no results are displayed. Now the problem is that we already have a bunch of reminders in our app before we even added full-text searching, and the AFTER INSERT trigger we just created does not magically index those older reminders.

NULL 20:59

But, if we create a new reminder, say “Audit weekly Stripe transactions”, and then we search for “week”, we will see it appear! So our full-text search is partially functional, we just have a bit more work to do to make it air tight. Keeping the index in sync

NULL 21:37

OK, we’ve got the beginnings of full-text search working in our app, but there is still a long way to go. Whenever new reminders are added we will properly index their titles and notes, but that’s about the extent of our indexing logic. We need to perform more work to update the index when reminders are edited or deleted. Brandon

NULL 21:52

And that is what we will tackle now, but first we want to make a small improvement to our existing app. When we started our “ Modern Persistence ” series by building this reminders app from scratch, we demonstrated a simple technique for seeding the database in debug builds. This made it so that previews, tests and even simulators got a baseline of simple data added to the database so that we didn’t have to waste our time creating that data from scratch each time.

NULL 22:17

Well, that technique works great for simple apps, and we highly recommend using it when first starting out, but it’s now only complicating things. Now that we have this additional FTS5 index we need to manage, we can no longer seed the database as a part of a migration step because that migration is run before we even create our FTS triggers.

NULL 22:37

So, let’s show off a better technique for when apps get a bit more complicated, and then let’s do what is necessary to update the FTS index when reminders are edited and deleted.

NULL 22:52

Currently we seed our database as a migration step that runs after all other migration steps: #if DEBUG migrator.registerMigration("Seed database") { db in … } #endif

NULL 23:00

We are going to slightly alter this so that we do seed the database automatically when provisioning it for tests and previews, but when running in the simulator we will add an explicit button to the interface that allows the user to choose when they want to seed the database. That way they can even choose to not seed the database if they don’t want that mock data.

NULL 23:26

To do this we are going to move all of this database seeding logic to a function that takes the opaque Database value as an argument: #if DEBUG func seedDatabase(_ db: Database) throws { … } }

NULL 23:55

We are going to continue hiding this functionality behind the

DEBUG 24:03

Now we can delete the database seeding migration…

DEBUG 24:08

And after all triggers are installed in the database we will seed the database only if not running in a live context: #if DEBUG if context != .live { try seedDatabase(db) } #endif

DEBUG 24:43

And then over in our root view for the application, which is the RemindersListsView , we will add a toolbar item in

DEBUG 25:33

Now let’s delete the app and reinstall fresh to see that there are no reminders by default. But if we tap the “Seed” button we will see a bunch of reminders fly in. And even better, now we can search for terms like “take” and see that both “Take a walk” and “Take out trash” are found. And that is all thanks to our insert trigger.

DEBUG 26:24

However, let’s navigate over to the “Take out trash” reminder, and edit its title to “Empty out trash”. Now if we go back to search and enter “Take” we will see that unfortunately the “Empty out trash” reminder is being displayed even though it shouldn’t.

DEBUG 26:56

This is happening because when reminders are edited we are not updating its full-text index with the new information. We need an additional trigger to do this, and it will be an AFTER UPDATE trigger instead of an AFTER INSERT : Reminder.createTemporaryTrigger( after: .update { } )

DEBUG 27:35

Then, in this last trailing closure we can specify a query that will be executed when the title or notes of a reminder is changed, and we will have access to the newest values via the new argument. And this is where we will get to use the fact that we added the reminderID as a column to our full-text virtual table. We can search for the ReminderText that belongs with the reminder being edited and then update its title and notes with the freshest values: ReminderText .where { $0.reminderID.eq(new.id) } .update { $0.title = new.title $0.notes = new.notes }

DEBUG 28:25

Further, we don’t need to be notified of all changes to reminders. We only care about updates that change the title or notes of the reminder, and so we can further specify those columns in the trigger: Reminder.createTemporaryTrigger( after: .update { ($0.title, $0.notes) } forEachRow: { _, new in ReminderText .where { $0.reminderID.eq(new.id) } .update { $0.title = new.title $0.notes = new.notes } } )

DEBUG 29:12

That is all it takes.

DEBUG 29:22

And while we are here, let’s go above and beyond by also cleaning up the ReminderText virtual table when a reminder is deleted: try Reminder.createTemporaryTrigger(after: .delete { old in ReminderText .where { $0.reminderID.eq(old.id) } .delete() }) .execute(db)

DEBUG 29:48

This will make it so that when a reminder is deleted we delete its corresponding indexed data so that it doesn’t take up space in our database.

DEBUG 29:55

OK, with these triggers added we can now see that things are working a lot better. I am going to rename “Empty out trash” to “Deal with trash”, and now when I search for “take” or “empty” I do not see the trash reminder, but if I search for “deal” or “trash”, then I do see it.

DEBUG 30:25

We can even search for a compound search term, like “doctor diet”, and that returns a result because we have “Doctor appointment”, and its notes contains the word “diet”. However if we search for a compound statement such that one word is found in one reminder and the other word is found in a different reminder, such as “doctor accountant”, we will see that no reminders are found. So the machinery of full-text search is handling this situation automatically for us behind the scenes without us having to do extra work, such as splitting the search text by spaces and joining them together to form a complex query.

DEBUG 31:22

Now there is a SwiftUI bug that we occasionally hit with searchable where search results freeze over time and stop getting updated. We can work around the bug by providing the search feature view with an identity that matches the current search text, which will force SwiftUI to re-compute the views when the search changes: SearchRemindersView(model: model.searchRemindersModel) .id(model.searchText) Next time: Advanced FTS features

DEBUG 33:25

OK, we now have the basics of full-text search in place for our reminders app. When reminders are created, updated or deleted, we update the full-text search index with the newest information, which gives us immediate and efficient access to searching through our reminders. And in the process we were able to simplify our queries, reduce the number of tables we have to join, and just generally offload the complexities of search to the FTS5 module in SQLite. Stephen

DEBUG 33:52

But we haven’t captured all of the behavior we had previously when searching with the simple SQL

LIKE 34:12

But, luckily for us it is quite easy to deal with using just a few more triggers. So, let’s take a look…next time! References SQLite FTS5 Extension FTS5 is an SQLite virtual table module that provides full-text search functionality to database applications. https://www.sqlite.org/fts5.html 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 0336-fts-pt3 Point-Free A hub for advanced Swift programming. Brought to you by Brandon Williams and Stephen Celis . Content Become a member The Point-Free Way Beta previews Gifts Videos Collections Free clips Blog More About Us Community Slack Mastodon Twitter BlueSky GitHub Contact Us Privacy Policy © 2026 Point-Free, Inc. All rights are reserved for the videos and transcripts on this site. All other content is licensed under CC BY-NC-SA 4.0 , and the underlying source code to run this site is licensed under the MIT License .