Photo by John Cameron on Unsplash
Introduction
Picture this: You’re using a mobile app, trying to find crucial information, but each search query feels like an eternity. Frustration mounts as precious seconds tick away. If you’ve ever experienced this, you’re not alone. Slow search functionality can be a death sentence for user satisfaction in today’s fast-paced digital world.
I too faced this dilemma, with search times in my app soaring to a frustrating 10 seconds. However, I didn’t settle for sluggish search results. In this article, I will take you on a journey into the world of lightning-fast mobile searches. I’ll present to you a powerful technique called Full Text Search (FTS), armed with the formidable tools of SQLDelight and Kotlin Multiplatform.
Search in Mobile Apps
Search functionality is non-negotiable, users demand lightning-fast, intuitive and accurate search experiences, and the success of your app often depends on delivering just that.
In big complex apps with lots of data, this can be quite a challenge. That’s where FTS comes to the rescue solving this problem whether your app is offline first or switches to a local database when there’s no connection.
Quick overview on KMP and SQLDelight
Kotlin Multiplatform (KMP) is a technology that allows us to share code between iOS and Android apps (among other platforms), its initial goal was to share only business logic, but now we have Compose Multiplatform which lets us share UI code too.
I wrote an article proposing an architecture for KMP sharing ViewModels, check it out here.
On the other hand, SQLDelight is just a plugin that generates Kotlin APIs from SQL statements, with SQLDelight we can easily create tables and define queries using SQLite and get some Kotlin classes and functions to access them for free.
Is it magic? How it works?
There’s no magic here; it’s all about very clever indexing. FTS relies on creating highly efficient indexes over the columns we specify. These indexes are like well-organized reference books that make searching through massive amounts of data lightning-fast. So, while it might seem like magic, it’s actually a smart and efficient way to find precisely what you’re looking for in your app’s data.
In simple terms, FTS takes an input (text) and generates a list of tokens, which are later indexed. How these tokens are generated depends on the chosen tokenizer.
Also with FTS, I was able to craft SQL queries that can:
- Search for specific words or phrases.
- Exclude or include a specific set of columns.
- Use logical operators (AND, OR, NOT) to refine search conditions.
- Conduct proximity searches by specifying the distance between words.
Can you implement it on your project?
You can if:
- You have a Kotlin Multiplatform Mobile app
- Your app has a local SQLite database
The Room library for Android also supports FTS, but in this article I am only focusing on KMP.
Implementing FTS with KMP & SQLDelight
Theory is nice but let me show to you some real code, I’ve written a very simple app that fetches some Rocket Launches from https://docs.spacexdata.com/ API.
In order to have a pretty large database, the app duplicates rocket launches.
You can check the code of the demo app here
Setting the Stage: First steps
- In your shared gradle file you must add the dependencies for SQLDelight, apply the plugin and specify the name of your database.
Check the complete gradle file here
plugins {
//...
id("com.squareup.sqldelight")
}
// ....
val commonMain by getting {
dependencies {
implementation("app.cash.sqldelight:runtime:$sqlDelightVersion")
}
}
val androidMain by getting {
dependencies {
implementation("app.cash.sqldelight:android-driver:$sqlDelightVersion")
}
}
val iosMain by getting {
dependencies {
implementation("app.cash.sqldelight:native-driver:$sqlDelightVersion")
}
}
// ....
sqldelight {
databases {
create("AppDatabase") {
packageName.set("com.example")
}
}
}
- Define database Schema by creating required tables, in this case we will have a Launch table containing information for rocket launches. All your queries should be inside .sq files so SQLDelight can generate the required Kotlin code for you.
CREATE TABLE Launch ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, flightNumber INTEGER NOT NULL, missionName TEXT NOT NULL, details TEXT, launchSuccess INTEGER AS Boolean DEFAULT NULL, launchDateUTC TEXT NOT NULL, patchUrlSmall TEXT, patchUrlLarge TEXT, articleUrl TEXT ); insertLaunch: INSERT INTO Launch(flightNumber, missionName, details, launchSuccess, launchDateUTC, patchUrlSmall, patchUrlLarge, articleUrl) VALUES(?, ?, ?, ?, ?, ?, ?, ?); removeAllLaunches: DELETE FROM Launch;
Job Offers
OUR VIDEO RECOMMENDATION
Jobs
Create Full Text Search table
Now you can create your FTS table: it is a virtual table with no primary key and no constraints, this table will contain all your indexed data
The syntax is very similar to the creation of a normal virtual table but adding the USING fts5 keywords, which basically tells to SQLDelight that you are using the fts5 SQLite extension
CREATE VIRTUAL TABLE LaunchFTS USING fts5(
id INTEGER UNINDEXED, -- (1)
flightNumber INTEGER NOT NULL,
missionName TEXT NOT NULL,
details TEXT,
launchSuccess INTEGER AS Boolean DEFAULT NULL,
launchDateUTC TEXT NOT NULL,
patchUrlSmall TEXT,
patchUrlLarge TEXT,
articleUrl TEXT,
tokenize = 'unicode61' -- (2)
);
(1) I added an unindexed id column: this column is usually useful for doing complex queries, but most of the time you don’t want to make this value searchable, that is why I added the UNINDEXED keyword.
(2) There are a lot of configurations that you can set in a FTS table, for this app I only set a specific tokenizer which is the unicode tokenizer, this way you support a huge amount of different characters and languages like Japanese, Chinese and more.
Populate the FTS table
Maintaining synchronization with the FTS table with the original source of data might be a hard task depending on your app needs, for instance if you are indexing some columns that needs some kind of pre-processing or joining with other tables, then you might need to implement a more complex solution.
And that was my particular case, some columns on the original table were foreign keys which are totally useless for text search, but I wanted to let users to be able to also search on those columns (the name of the underlying entity, not the FKs)
But if that’s not your case, you can go for a simpler solution which is just use triggers:
CREATE TRIGGER launch_after_insert
AFTER INSERT ON Launch
BEGIN
INSERT INTO LaunchFTS (
// ...
)
VALUES (
// ...
);
END;
CREATE TRIGGER launch_after_update
AFTER UPDATE ON Launch
BEGIN
UPDATE LaunchFTS SET
// ...
WHERE id = new.id;
END;
CREATE TRIGGER launch_after_delete
AFTER DELETE ON Launch
BEGIN
DELETE FROM LaunchFTS WHERE id = old.id;
END;
This way your LaunchFTS table will always be in sync with your Launchtable.
But, why didn’t I join tables within the triggers?
Sadly it is not possible to do that in SQLite.
Create FTS query
The next step is just create the SQL query using the FTS table, the syntax is pretty easy:
searchLaunchesPaginated: SELECT id, missionName, launchDateUTC, details FROM LaunchFTS WHERE LaunchFTS MATCH :searchQuery -- (1) LIMIT :limit OFFSET :offset; -- (2)
(1) The important thing here is the MATCH keyword followed by :searchQuery which is the text typed by the user which in turn is passed down to the query, this will return every row in LaunchFTS where any column matches the given search parameter.
(2) This is just to support pagination, I created a simple paginated query to support multiplatform pagination, you can check it here
Under the hood FTS will compare against the saved indexes, if you instead use the LIKE ‘%:searchQuery%’ format, SQL will have to perform a complete full scan of the table, making it significantly slower.
Check the results!
Here is a video comparing regular search against Full Text Search.
Analyzing Pros and Cons
The Benefits
- Enhanced User Experience: Faster, more relevant and accurate search results lead to satisfied users.
- Powerful search: In my personal experience, I was able to make the search more powerful by searching in every entity attribute, using a normal approach would result in an extremely low query.
- Flexibility: FTS offers advanced query capabilities like proximity searches, logical operators, and ranking.
- Multilingual Support: FTS supports various languages and character sets, making it suitable for apps supporting a large number of languages.
- Offline Search: FTS allows for efficient searching even when the app is offline
- Order by relevance: with the bm25() function we can calculate a ranking number for each row and order the results based on its relevance.
- Cross-platform Compatibility: With KMM, we ensure both Android and iOS users get the same high-quality search experience.
The Not-So-Good
- Complex implementation: in some cases it might be hard to implement FTS and it might require some expertise in database management.
- Increased complexity: you will end having a more complex source code
- Increased overhead: to keep in sync both Launch and LaunchFTS tables, you add some overhead each time a new row is updated, created or deleted.
- Increased storage requirements: this is the most obvious tradeoff, indexing techniques sacrifices storage for CPU time, FTS is not the exception.
Conclusion
While FTS is a very powerful technique, it may not be a one-size-fits-all solution and does come with implementation challenges. For instance if your users have low end devices and/or very limited disk space, FTS could not be the right solution for your app.
Even so, it is a valuable tool in the arsenal of mobile app developers. Its ability to enhance user experiences, support several languages, and operate in low-network or offline scenarios makes it a formidable choice for many projects.
Mastery often requires practical experience. Dive into your project, experiment, and unlock the full potential of mobile search. Your users will appreciate the difference, and your app will stand out in today’s competitive landscape.
FTS has a lot of features that are impossible to cover in just a single article, I strongly recommend you to visit the official documentation page to get more information here.
This article was previously published on proandroiddev.com
