Enabling SQLite FTS in Room 2.1

I was so very fortunate to be able to attend the Android Dev Summit in Mountain View this week, where one announcement in particular caught my attention: the 2.1 release of the Room library supports full-text search (FTS), a feature I had been looking into for the Japanese-English dictionary app I’ve been working on.

To give some background on implementing search in SQL-like databases, the most naïve way to look for a term that appears in a text column is to write a query that looks like this (say you want to find the word “pineapple”):

SELECT * FROM my_table WHERE my_column LIKE "%pineapple%";

This will return rows from your table where the word “pineapple” appears between any other text. The problem with the LIKE keyword is that it’s slow. It essentially has to read through the entire table to get you what you want, and when you have a big table, the performance hit is bad.

Luckily, there is already a solution we can leverage in SQLite that is better than the LIKE keyword, and that’s FTS. FTS creates a highly searchable virtual table that can mirror an existing table. All you need to do is tell SQLite which columns you’d like to reference, insert the necessary data, and perform a MATCH query and you’re good to go.

Since this feature is so new in Room, I had to go dig around in the SQLite FTS4 documentation to get off the ground, but once I played around with the commands in the SQLite terminal enough, I was able to get this working in my Android app no problem. I also took some hints from the Android Architecture Components sample repo, but my solution differed from theirs so thought I would share.

Understanding FTS

The first decision you’ll need to make is which columns to put in your virtual FTS table. You can only use text columns. You can define which table you want your virtual table to mirror, and that’s known as a “content table.” In my case, I wanted to put four columns from my entries table into the FTS table, which I named entriesFts.

CREATE VIRTUAL TABLE IF NOT EXISTS `entriesFts` 
USING FTS4(
  `primary_kanji`, 
  `primary_reading`, 
  `other_kanji`, 
  `other_readings`, 
  content=`entries`
)

This creates a new virtual table entriesFts with these four columns (which have the same name as the columns in my content table, entries). You can leave off the type since it has to be TEXT.

(The IF NOT EXISTS part I grabbed from the sample repo but I’m not sure it’s needed, since this migration should only run once so the table shouldn’t exist when it runs.)

So far so good. I learned from the SQLite documentation that my virtual table has a magic column named docid that holds the reference to the content table’s matching ID (so in my case, docid holds an id from the entries table, like a foreign key). Interestingly, if I performed a SELECT COUNT(docid) FROM entriesFts, I got the same number as rows in my entries table, but if I tried to perform a MATCH query at this point, I got nothing. This is because the virtual table needs to be rebuilt from its source, the content table entries. Turns out there’s a really easy way to do this that I got to partially from this Stack Overflow question and the official FTS docs:

INSERT INTO entriesFts(entriesFts) VALUES ('rebuild')

When I ran this from my SQLite terminal, there was a noticeable pause, which I took to be a good thing. Now when I ran queries, I actually got results!

SELECT entries.id, entries.primary_kanji, entries.primary_reading 
FROM entries 
JOIN entriesFts ON entries.id = entriesFts.docid
WHERE entriesFts MATCH '*順*';

The real key here was how to join the FTS table and use the MATCH query correctly: I had to join on that magic docid column, and I didn’t need to specify which columns in the FTS table to look in, just provide the table name itself.

The wildcard characters (*) allow me to match things where “順” appears in the middle of other characters (there are no spaces in Japanese, so depending on the kind of search you want, you may or may not want those wildcard characters there).

Once I understood how these pieces fit together, I just had to tweak a few things in my app to get this all working.

Using FTS with Room Tutorial

Before you get started, you’ll need Room 2.1+, which might require you to upgrade to AndroidX. I used the Android Studio migration tool and it worked great.

First, I added a Room migration and incremented the database version:

Hopefully those SQL statements look familiar to you now!

We’ll be adding a new entity (EntryFts.class), so we have to add that to our @Database annotation (as above) and then add the class:

You need to have public getters/setters for all the columns in the FTS table, but you don’t need to do anything about the magic column docid. This Room entity also specifies the content table in the @Fts4(contentEntity = Entry.class) annotation.

At this point, you should probably make sure your migration went okay before trying to use the virtual table. You might get some data integrity errors if you mistyped any table names (even though I’m a Rails dev, that singular vs. plural gets me every time…).

If that all went okay, it’s time to update our DAO:

That SQL query should also look familiar! Note the magic docid and the MATCH!

And finally, my repository class throws in some wildcards for good luck:

And here’s my final UI result!

Search results for “朝”

Overall I’m really thrilled with how easy this was. If you’d like to check out the diff or the repo, here it is (although most of the diff is present in this article already).

I hope this helps you get started with using FTS and Room!