Basics of SQLite Full-Text Search (FTS)

What Is Full-Text Search?

Normal SQL search:

SELECT *
FROM posts
WHERE content LIKE '%sqlite%';

Full-text search is designed specifically for searching large amounts of text.

SQLite provides this through FTS5.

Create an FTS5 Table

CREATE VIRTUAL TABLE posts_fts
USING fts5(title, content);

Insert data:

INSERT INTO posts_fts(title, content)
VALUES
  ('SQLite Basics', 'SQLite is a small embedded database.'),
  ('FTS5', 'SQLite provides full-text search.'),
  ('JavaScript', 'JavaScript can work with SQLite.');

Search

Use MATCH:

SELECT *
FROM posts_fts
WHERE posts_fts MATCH 'SQLite';

Multiple words:

SELECT *
FROM posts_fts
WHERE posts_fts MATCH 'SQLite database';

This searches for documents containing the terms.

Search a Specific Column

SELECT *
FROM posts_fts
WHERE title MATCH 'SQLite';

Or:

SELECT *
FROM posts_fts
WHERE content MATCH 'database';

Prefix Search

Use *:

SELECT *
FROM posts_fts
WHERE posts_fts MATCH 'java*';

Matches words such as:

java
javascript
javafx

Exact Phrases

Use quotes:

SELECT *
FROM posts_fts
WHERE posts_fts MATCH '"full text search"';

Searches for the phrase:

full text search

Boolean Search

AND

SELECT *
FROM posts_fts
WHERE posts_fts MATCH 'sqlite AND database';

Both terms must match.

OR

SELECT *
FROM posts_fts
WHERE posts_fts MATCH 'sqlite OR postgres';

Either term can match.

NOT

SELECT *
FROM posts_fts
WHERE posts_fts MATCH 'sqlite NOT mysql';

Matches sqlite but excludes mysql.

Ranking Results

FTS5 provides bm25() for relevance ranking.

SELECT
  title,
  content,
  bm25(posts_fts) AS score
FROM posts_fts
WHERE posts_fts MATCH 'sqlite'
ORDER BY score;

Lower bm25() scores are more relevant.

Highlight Matches

Useful for search results:

SELECT
  highlight(posts_fts, 1, '<b>', '</b>') AS result
FROM posts_fts
WHERE posts_fts MATCH 'sqlite';

1 means the second column (content).

FTS With a Normal Table

Usually you have a real table:

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  title TEXT,
  content TEXT
);

And an FTS table:

CREATE VIRTUAL TABLE posts_fts
USING fts5(
  title,
  content,
  content='posts',
  content_rowid='id'
);

Now FTS indexes the content from posts.

Keep the Index Updated

If you use an external-content FTS table, changes to the original table must be reflected in the FTS index.

For example:

INSERT INTO posts(title, content)
VALUES ('SQLite', 'SQLite is awesome.');

Then update FTS:

INSERT INTO posts_fts(rowid, title, content)
VALUES (last_insert_rowid(), 'SQLite', 'SQLite is awesome.');

For automatic synchronization, SQLite triggers can be used.

FTS5 vs LIKE

LIKE

WHERE content LIKE '%sqlite%'

Good for:

• Small tables
• Simple substring searches
• Exact text matching

FTS5

WHERE posts_fts MATCH 'sqlite'

Good for:

• Large text collections
• Search boxes
• Relevance ranking
• Tokenized word searches
• Phrase searches
• Prefix searches

Simple Search Example

CREATE VIRTUAL TABLE docs
USING fts5(title, body);

INSERT INTO docs(title, body)
VALUES
  ('SQLite', 'SQLite is an embedded database.'),
  ('PostgreSQL', 'PostgreSQL is a powerful database.'),
  ('JavaScript', 'JavaScript is commonly used for web development.');

SELECT
  title,
  body,
  bm25(docs) AS score
FROM docs
WHERE docs MATCH 'database'
ORDER BY score;

Remember

LIKE       → simple text matching
FTS5       → real full-text search
MATCH      → perform an FTS search
bm25()     → rank results
highlight() → highlight matches
*          → prefix search
"..."      → phrase search
AND/OR/NOT → combine terms

FTS5 is SQLite’s built-in search engine for text.