Full-text search with SQLite

SQLite has gained significant popularity due to its simplicity across many use cases—even in production environments.

One of the feature that I recently came-across is its built-in support for full-text search via the FTS4 module. This allows you to implement powerful search capabilities directly within your database, eliminating the need for external search services in many applications.

Create an FTS table

To enable full-text search in SQLite, you need to create a separate FTS table using the virtual table syntax. This table acts as a search index.

It’s best to keep the original data in a separate table and use the FTS table solely for searchable text for better performance and clarity.

CREATE VIRTUAL TABLE posts_index USING fts4(title);

Here the posts_index is the name of the index and title is the column being indexed. You can add multiple columns separated by comma.

Inserting data

Every FTS table in SQLite has a in-built column named rowid that uniquely identifies each row.

You can use this rowid column to store the unique id (primary key) of the corresponding record in the original table, so that you can directly map the row to the original table if required.

INSERT INTO posts_index(rowid, title) VALUES(1, 'SQLite is awesome');
INSERT INTO posts_index(rowid, title) VALUES(2, 'Laravel is awesome');

Basic Search

To perform a full-text search, use the MATCH operator.

SELECT * FROM posts_index WHERE title MATCH 'awesome';

The above query should return the two rows we have inserted in the previous section.

Highlighting Matches

Use the snippet function to highlight the matching terms in search result.

SELECT snippet(posts_index) AS snippet
FROM posts_index
WHERE title MATCH 'awesome';

By default, matched words are wrapped in <b> tags.

SQLite is <b>awesome</b>
Laravel is <b>awesome</b>

You can also customize the highlight tags.

SELECT snippet(posts_index, '<mark><b>', '</b></mark>') AS highlighted_title
FROM posts_index
WHERE title MATCH 'awesome';

Demo

I have setup a demo application using the SQLite FTS4 with Laravel.

You can download the project from GitHub and playaround.

Below is the snapshot from the demo application -