Full Text Search in Room Tutorial: Getting Started

A great search experience is a must-have feature in modern apps, but creating one is a challenge. It must provide relevant results, be fast, work offline and handle potentially large amounts of data. Fortunately, our search for tools to build it need not go further than Room and SQLite by using Full Text Search.

In this tutorial, you’ll build an app to search for SpaceX launches, and learn about:

• The Full Text Search (FTS) feature in SQLite
• FTS with Room
• Benefits of FTS over pattern matching
• Advanced FTS operations, such as ranking search results

Getting Started

Download the starter project by clicking on the Download Materials button at the top or bottom of the tutorial. Then, open the starter project in Android Studio to discover Find My Launch, your sandbox for playing with FTS.

Build and run the app. You’ll see the following screen:

At the top is a search field, followed by a list of SpaceX launches. Each launch has a unique name and a small description. Go ahead and try searching for something. The results update automatically as you type.

The starter app uses the SQL LIKE operator to find matches. You’ll modify it to use FTS instead. First, though, take a quick look at what FTS is.

Full Text Search

Full Text Search (or FTS) is a search technique in which every word stored in a text document is compared against the given query to find matches. Its ability to search a large number of documents quickly has led to its wide adoption in search engines, word processors and databases.

However, in Android, it is much more common to search SQLite databases using the LIKE operator (aka pattern matching), due to its ease of use. FTS offers several benefits over that, including that it:

• Is much faster and efficient, especially over large datasets
• Can match against multiple columns in a row
• Also matches any discrete tokens that can be extracted from the search query
• Makes it possible to retrieve additional metadata about each match for custom ranking algorithms

FTS was added to SQLite as a set of extensions and has received a few updates over the years (FTS3, FTS4 and FTS5). Room supports only FTS3 and FTS4, and it is recommended to use FTS4 in most cases.

As with any technology, FTS has a few drawbacks, too:

• It is more complicated to use than pattern matching.
• FTS indices occupy more disk space, leading to a larger database file. For databases containing hundreds of thousands of entries, this can be problematic.
• FTS5 isn’t supported on SQLite shipped with older versions of Android, so Room doesn’t support it.

With the basics out of the way, now look at how FTS is implemented.

Virtual Tables

FTS indexes data in a special format called the full-text-index. It provides the basis for all operations supported by FTS. Users interact with it through a Virtual Table.

Quoting the official SQLite documentation,

In general, you can do anything with a virtual table that you can do with an ordinary table, except create indices or triggers on them.

Virtual Tables make it easy to work with the full text index, because you can read from them as if they were regular SQL tables. From now on, the tutorial will refer to them as FTS Tables.

FTS Tables

FTS tables can only store TEXT columns, but they internally have an INTEGER rowid column as their primary key.

You can write data to these tables directly. They can also be made to index the data in another table using the content option. Such FTS tables are called content-less because they store no records. They contain the full-text index only for the table they refer to.

For this tutorial, you’ll create a content-less FTS table to index details of the SpaceX launches in the database.

Full Text Search and Room

The @Fts3 and @Fts4 annotations shipped with Room can be used to leverage FTS features. Pick one depending on what version of FTS you want in your database. For this tutorial, only @Fts4 is required.

Room generates tables for classes annotated with @Entity. On adding an additional annotation of @Fts4 to the entity class, Room generates a virtual table for it instead.

Such entities support only String fields, except for row ID and language ID fields, which can be integers.

The FTS annotations support quite a few configuration parameters, but you’ll use only contentEntity in this app. Feel free to play around with the others, such as the one for custom tokenizers.

Creating FTS Tables

Open the Launch.kt file in the db package, and add a new FTS entity class to it with the table name launches_fts.

@Entity(tableName = "launches_fts")
@Fts4(contentEntity = Launch::class)
data class LaunchFTS(
    @ColumnInfo(name = "name")
    val name: String,
    @ColumnInfo(name = "details")
    val details: String
)

The contentEntity parameter points to the Launch class defined in the same file, making this a content-less table that indexes the name and details fields of the Launch entity. It’s important that the column names of the FTS entity match the corresponding columns in the content entity.

Next navigate to LaunchesDatabase.kt file in the db package. Update the @Database annotation to include the new entity LaunchFTSyou just defined above in the entities array field. You also must increment the version number to 2, because the database schema changes with this operation:

@Database(
    entities = [Launch::class, LaunchFTS::class], // Add LaunchFTS to this array
    exportSchema = false,
    version = 2 // Increment the version number
)

Build and run the app. You shouldn’t see any noticeable changes.

Using FTS in a DAO

Searching an FTS table requires use of the MATCH operator. It operates on a hidden column in the table with the same name as the table itself. Use it to implement the SQL query.

Navigate to the LaunchDao.kt file in the db package. Next, find the search method, which uses the LIKE operator on a launch’s name to search for matches in its @Query annotation. Replace the search method along with the complete SQL query defined in the @Query annotation with one based on LaunchFTS entity, as shown below:

@Query("""
  SELECT *
  FROM launches_fts
  WHERE launches_fts MATCH :query
""")
suspend fun search(query: String): List<LaunchFTS>

The LIKE operator uses the modulus symbol (%) to match search-queries in the middle of a phrase. FTS on the other hand uses asterisks (*) for this purpose. Update the SearchViewModel class to reflect this change. Navigate to SearchViewModel.kt inside the search package, and change the search method to use asterisks:

fun search(query: Editable?) {
  viewModelScope.launch {
    if (query.isNullOrBlank()) {
      launchDao.all().let {
        _searchResults.postValue(it)
      }
    } else {
      // Replace % with * here
      launchDao.search("*$query*").let {
        _searchResults.postValue(it)
      }
    }
  }
}

The app’s search implementation now returns all the matching LaunchFTS entries in the database based on the given query parameter. This change has an added benefit of searching both, the launch’s name and its description. Unfortunately, it also breaks the app. Try building and running the app now. You will notice the build fails with below error coming from SearchViewModel class:

Type mismatch: inferred type is List<LaunchFTS> but List<Launch>! was expected

Notice that the search method in SearchViewModel class expects the DAO’s search method to return List<Launch>, instead of List<LaunchFTS>. You must modify the query to return the correct type of data, using the JOIN operator.

Fixing the Return Type

Navigate back to the LaunchDao.kt file in the db package and find the search method. Next, replace the search method along with the complete SQL query defined in the @Query annotation with one as below:

@Query("""
  SELECT *
  FROM launches
  JOIN launches_fts ON launches.name = launches_fts.name
  WHERE launches_fts MATCH :query
""")
suspend fun search(query: String): List<Launch>

Here, notice that you have updated the @Query annotation as well as updated the return type of search method to List<Launch>.

Much better! Here the launches table joins with the launches_fts table on the name of a launch. This way, the query matches against the launches_fts table but returns the columns of the launches table.

With this change, the app now uses FTS instead of pattern matching. Build and run the app and try searching for something.

You should see … no search results?

To understand why, you must learn how the FTS index is maintained. Head over to the next section to learn about it.

Maintaining the Full Text Search Index

In a newly created database, the FTS index is empty. It must be updated every time its content table is modified, but SQLite doesn’t perform this task automatically. Instead, it provides a feature to automate such operations: Triggers.

You don’t need to write these triggers by hand, because Room Jetpack Library generates them automatically. As a result, you almost never need to maintain the FTS index manually.

However, Find My Launch ships with a database asset to pre-populate the application database when it is first created. The pre-population process doesn’t trigger an update to the FTS index. Consequently, the index remains empty and returns zero results for every query.

To fix this, you must trigger an FTS rebuild after the application database creation completes.

Triggering FTS Rebuilds

As mentioned earlier, FTS tables have a hidden column with the same name as the table itself. This column supports some special commands, one of which is rebuild. Writing this command to the hidden column triggers a rebuild of the full-text index.

Navigate to the DatabaseModule.kt file in the db package. In the launchesDB method, you need to add a callback to the database builder to rebuild the FTS index, right after calling createFromAsset. Replace the existing launchesDB method with the below implementation:

@Provides
@Singleton
fun launchesDB(context: Context): LaunchesDatabase {
  return Room.databaseBuilder(context, LaunchesDatabase::class.java, "findmylaunch.db")
      // 1
      .createFromAsset("launches.db")
      // 2
      .addCallback(object : RoomDatabase.Callback() {
        override fun onCreate(db: SupportSQLiteDatabase) {
          super.onCreate(db)
          // 3
          db.execSQL("INSERT INTO launches_fts(launches_fts) VALUES ('rebuild')")
        }
      })
      .fallbackToDestructiveMigration()
      .build()
}

Here in this code snippet, you are:

1. Creating/Pre-populating the database using the launches.db file from assets
2. Adding the callback that is triggered after the database is pre-populated
3. Executing the SQL query that will use the rebuild trigger

Make sure to add missing imports using the IDE.

Build and run the app. Search should be fully functional again!

Once you have verified the search functionality, try searching for the following query: -9. You will notice that the application crashes with the following error in the logcat:

android.database.sqlite.SQLiteException: malformed MATCH expression: [*-9*] (code 1 SQLITE_ERROR)

To fix this error, you need to properly escape the search queries received from the UI.

Escaping FTS Queries

Full Text Search in SQLite supports a myriad of features, one of which is Set Operations.

Set Operations allow you to manipulate the results of an FTS query in various ways using logical operators. One such operator is the NOT operator, which returns the complement result set of the given query (i.e., all results minus the results of the given query). SQLite treats every minus sign (-) in the search-query as the NOT operator, and forbids any queries that contain - as a prefix. Therefore, searching for a seemingly harmless query like -9 crashes the app as it contains - as a prefix.

To solve this problem, you need to treat the user’s search queries as verbatim.

Navigate to the SearchViewModel.kt file in the search package, and add the following method to it:

private fun sanitizeSearchQuery(query: Editable?): String {
  return "\"$query\""
}

The sanitizeSearchQuery method surrounds the search query with quotes, which ensures that SQLite treats this string literally.

Next, modify the search method to sanitize the search-query before using it to fetch search results:

fun search(query: Editable?) {
  viewModelScope.launch {
    if (query.isNullOrBlank()) {
      launchDao.all().let {
        _searchResults.postValue(it)
      }
    } else {
      val sanitizedQuery = sanitizeSearchQuery(query)
      launchDao.search(sanitizedQuery).let {
        _searchResults.postValue(it)
      }
    }
  }
}

Build and run the app. You should now be able to search for queries that contain - as a prefix.

Unfortunately, this solution isn’t complete yet because a double-quote symbol in the search query still crashes the app. Try searching for "-9. The app should crash again with the following error:

android.database.sqlite.SQLiteException: malformed MATCH expression: [""-9"] (code 1 SQLITE_ERROR)

To fix this, you must correctly escape double-quotes too.

Escaping double-quotes

The method to escape double-quotes in SQLite is to replace them with… double double-quotes! :]

In the SearchViewModel.kt file, replace the implementation of the sanitizeSearchQuery method with this:

private fun sanitizeSearchQuery(query: Editable?): String {
  if (query == null) {
    return "";
  }
  val queryWithEscapedQuotes = query.replace(Regex.fromLiteral("\""), "\"\"")
  return "*\"$queryWithEscapedQuotes\"*"
}

This code uses a regular expression to replace all occurrences of " in the query with "". It then wraps the query with double quotes, adds asterisks on either ends, and finally returns the result.

Build and run the app. It should no longer crash when you search for queries such as "-9.

This change completes the basic implementation of the search functionality in the app. However, you can still do better by ordering the results according to relevance. Head over to the next section to learn how to do so.

Ranking Search Results

The current search implementation doesn’t order results based on their relevance. This means that less-relevant results could appear in the list before more-relevant ones.

To fix this issue, you must assign a rank to each result and order the results by their ranks. FTS4 has no built-in feature to handle this, but it provides the handy matchinfo function to build one.

The matchinfo function returns a BLOB containing metadata about each match. You can parse this metadata to calculate a relevance score, which can be used to assign a rank to each match. This function supports a lot of configuration options, each of which affects how its value must be parsed.

In the interest of keeping things simple, this tutorial uses only the default variant of this function. The default variant takes only the hidden FTS column name as its parameter.

Model Class for matchinfo

Navigate to the Launch.kt file in the db package and add the following code at the end:

data class LaunchWithMatchInfo(
    @Embedded
    val launch: Launch,
    @ColumnInfo(name = "matchInfo")
    val matchInfo: ByteArray
) {
  override fun equals(other: Any?): Boolean {
    if (this === other) return true
    if (javaClass != other?.javaClass) return false

    other as LaunchWithMatchInfo

    if (launch != other.launch) return false
    if (!matchInfo.contentEquals(other.matchInfo)) return false

    return true
  }

  override fun hashCode(): Int {
    var result = launch.hashCode()
    result = 31 * result + matchInfo.contentHashCode()
    return result
  }
}

Here in this code, LaunchWithMatchInfo is a data class to hold the result of an FTS search returning a Launch object along with its matchinfo (represented as a ByteArray). Note that we need to need to override equals and hashcode methods due to the presence of an array property in the data class.

Next, you’ll add a DAO method to perform search using matchinfo.

Using matchinfo in a DAO

Navigate to the LaunchDao.kt file in the db package and add the following code to it:

@Query("""
  SELECT *, matchinfo(launches_fts) as matchInfo
  FROM launches
  JOIN launches_fts ON launches.name = launches_fts.name
  WHERE launches_fts MATCH :query
""")
suspend fun searchWithMatchInfo(query: String): List<LaunchWithMatchInfo>

This query returns a list of Launch objects, along with their matchinfo metadata. This list will be parsed in the SearchViewModel to order results according to their rank.

Sorting by Scores

Navigate to the SearchViewModel.kt file in the search package and add the following code to it (make sure add missing imports using the IDE):

fun searchWithScore(query: Editable?) {
  // 1
  viewModelScope.launch {
    // 2
    if (query.isNullOrBlank()) {
      // 3
      launchDao.all().let { _searchResults.postValue(it) }
    } else {
      // 4
      val sanitizedQuery = sanitizeSearchQuery(query)
      launchDao.searchWithMatchInfo(sanitizedQuery).let { results ->
        // 5
        results.sortedByDescending { result -> calculateScore(result.matchInfo) }
            // 6
            .map { result -> result.launch }
            // 7
            .let { _searchResults.postValue(it) }
      }
    }
  }
}

Here in this code:

1. Use the launch coroutine builder to start a coroutine
2. Check if query is empty or null
3. If the query is blank or null then query for all the values from the database and update the _searchResults Livedata value
4. If the query is not empty or null, then sanitize it and search the database using matchinfo.
5. The matchInfo array of bytes is supplied to a calculateScore function, which parses it and returns a score value. A higher value indicates a better match, so the list is sorted in descending order according to the scores.
6. The sorted list of LaunchWithMatchInfo is then mapped to a simple list of Launch objects, and used as the search results.
7. Update the _searchResults Livedata value

It is important to parse the matchinfo data and calculate the score on a background thread, because it is a potentially long-running operation for large datasets.

Using the Sorted Results

Finally, navigate to the SearchFragment.kt file in the same package and update the setupSearchField method to use the newly added method in the ViewModel. Replace the existing implementation of setupSearchField method with below:

private fun setupSearchField() {
  binding.searchBox.addTextChangedListener { query ->
    viewModel.searchWithScore(query)
  }
}

Build and run the app. You should have a search implementation that ranks results according to their scores!

Bonus: Tests

Writing tests is the best way to increase confidence in your code. Find My Launch sample apps ships with a couple of them to help verify your FTS implementation by testing the app’s ViewModels.

Navigate to the app module’s androidTest source set in Android Studio, where you’ll find two test files: DetailsViewModelTest and SearchViewModelTest.

Because both the tests are Instrumentation Tests, they require an emulator or a physical Android device to be connected to your computer. Once you have either of them up and running, select the ViewModel tests run configuration.

Run, and view the test results. If everything went well, all tests should pass!

To read more about testing Room databases and understand how these tests were written checkout Testing Android Architecture Components

Where to Go From Here?

You can download the final version of this project using the Download Materials button at the top or bottom of this tutorial.

Congratulations! You learned a lot in this tutorial and can now build an awesome search experience in your own Android apps.

If you are wondering what to learn next, you can checkout tutorial on Database Views with Room for Android, and Room DB: Advanced Data Persistence.

Feel free to share your feedback or findings, and please post your questions in the comments section below or in the forums. I hope that you enjoyed learning about FTS with Room!