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.