“Full Text Search” with SQLite

SQLite Logo

Recently, I was working on a LinkedIn spy tool, I have used the SQLite database to store data , this tool need to support a full text search fonctionnality for application.

Googling on the internet and I found that SQLite already support full text searching natively.

This one done via three evolutions of the full text search extension FTS3 / FTS4 / FTS5. The original FTS3 code were contributed to the SQLite project by Scott Hess of Google.

FTS1 and FTS2 are obsolete full-text search modules for SQLite. There are known issues with these older modules and their use should be avoided.

Let’s play with this extension, I choose this Moroccan beautiful Riad “Riad Al Mamoune”, the idea is to extract some reviews and search on them.

Riad Al Mamoune — TripAdvisor

To manipulate the SQLite database, I use the sqlite3 command in ubuntu, but we need to update it as doesn’t support the FTS5, so, to update it run the following commands lines on terminal:

sudo add-apt-repository ppa:jonathonf/backports
sudo apt-get update && sudo apt-get install sqlite3

After update, then go to your workspace and create the SQLite database file by running:

sqlite3 hotels_reviews.db

Now, copy and paste every SQL part on this gist and run it on the sqlite3 prompt, please read comments to understand more:

After you have set properly your database, now, we can try to execute some queries searches:

SELECT rowid, review FROM hotels_reviews_index WHERE hotels_reviews_index MATCH 'review:help';

You can also order by relevance which is the rank score:

SELECT rowid, rank, review FROM hotels_reviews_index WHERE hotels_reviews_index MATCH 'review:square AND location' ORDER BY rank;

Please check the official documentation for syntax and operators supported by the FTS5 extension by following this link: https://www.sqlite.org/fts5.html#full_text_query_syntax

You can read more about this topic here:

Using SQLite Full-Text Search with Python

Using full text search with python and sqlite for tutorial, beginner, sqlite3, fts, database, query…

❤ If this post was helpful, please hit the little green heart and follow me using the follow buttons below!