SQLite Full-Text Search: The Hidden Search Engine Inside Your Database

Jay

December 14, 2025

Need to add search to your app? Most developers immediately reach for Elasticsearch, Algolia, or Meilisearch. But if you're already using SQLite, you might not need any of that.

SQLite has a built-in full-text search engine called FTS5. It's been around since 2015, it's fast, it requires zero external dependencies, and it's surprisingly capable. You get relevance ranking, phrase searches, boolean operators, and even result highlighting - all from a few SQL statements.

Let's build a searchable knowledge base to see how it works.

Creating an FTS5 Table

FTS5 uses virtual tables. Instead of CREATE TABLE, you use CREATE VIRTUAL TABLE ... USING fts5():

SQL

-- Create a full-text search table for our knowledge base
CREATE VIRTUAL TABLE articles USING fts5(
    title,
    content,
    category
);

-- Insert some help articles
INSERT INTO articles (title, content, category) VALUES
('Getting Started with SQLite',
 'SQLite is a lightweight database engine. It stores data in a single file and requires no server setup. Perfect for desktop apps, mobile apps, and small web applications.',
 'Database'),
('How to Write SQL Queries',
 'SQL queries let you retrieve data from your database. Use SELECT to fetch rows, WHERE to filter results, and ORDER BY to sort them. JOINs combine data from multiple tables.',
 'Database'),
('Understanding JSON in SQLite',
 'SQLite supports JSON through built-in functions like json_extract and json_set. You can store JSON documents and query them efficiently using virtual columns and indexes.',
 'Database'),
('Setting Up Your Development Environment',
 'A good development environment includes a code editor, version control, and a database client. VS Code, Git, and DB Pro make a great combination for most projects.',
 'Setup'),
('Debugging Common Database Errors',
 'Database errors often stem from syntax mistakes, missing tables, or constraint violations. Check your SQL syntax first, then verify table names and column types.',
 'Troubleshooting'),
('Performance Tips for Large Databases',
 'Indexes are key to database performance. Create indexes on columns you frequently search or sort by. Use EXPLAIN QUERY PLAN to understand how SQLite executes your queries.',
 'Performance');

That's it. We now have a searchable knowledge base with 6 articles. The FTS5 engine automatically tokenizes and indexes all the text.

Basic Searching with MATCH

To search an FTS5 table, use the MATCH operator:

SQL

-- Search for articles about "database"
SELECT title, category FROM articles WHERE articles MATCH 'database';

Multiple search terms are implicitly ANDed together:

SQL

-- Search for articles containing BOTH "database" AND "index"
SELECT title FROM articles WHERE articles MATCH 'database index';

Search Operators & Syntax

FTS5 supports powerful query syntax for precise searches.

Phrase Searches

Use double quotes to match exact phrases:

SQL

-- Exact phrase search - matches "database engine" as a phrase
SELECT title FROM articles WHERE articles MATCH '"database engine"';

Boolean Operators

Use OR for alternatives, NOT to exclude:

SQL

-- OR: match either term
SELECT title FROM articles WHERE articles MATCH 'JSON OR errors';

-- NOT: exclude results containing a term
SELECT title FROM articles WHERE articles MATCH 'SQLite NOT JSON';

Prefix Searches

Use * to match word prefixes:

SQL

-- Prefix search: matches "index", "indexes", "indexing", etc.
SELECT title FROM articles WHERE articles MATCH 'index*';

Column Filters

Search within a specific column:

SQL

-- Search only in the title column
SELECT title, category FROM articles WHERE articles MATCH 'title:database';

Ranking Results with bm25()

By default, FTS5 returns results in arbitrary order. Use bm25() to rank by relevance:

SQL

-- Rank results by relevance (lower bm25 score = more relevant)
SELECT
    title,
    bm25(articles) AS relevance_score
FROM articles
WHERE articles MATCH 'database'
ORDER BY bm25(articles);

The bm25() function implements the Okapi BM25 ranking algorithm - the same algorithm used by search engines like Elasticsearch. Lower (more negative) scores mean higher relevance.

You can also weight columns differently. For example, matches in the title might be more important than matches in the content:

SQL

-- Weight columns: title (10x), content (1x), category (5x)
SELECT
    title,
    bm25(articles, 10.0, 1.0, 5.0) AS weighted_score
FROM articles
WHERE articles MATCH 'database'
ORDER BY bm25(articles, 10.0, 1.0, 5.0);

Highlighting & Snippets

When building a search UI, you want to show users where their search terms matched. FTS5 has built-in functions for this.

highlight()

The highlight() function wraps matching terms in tags:

SQL

-- Highlight matching terms with <b> tags
SELECT
    highlight(articles, 0, '<b>', '</b>') AS highlighted_title,
    highlight(articles, 1, '<b>', '</b>') AS highlighted_content
FROM articles
WHERE articles MATCH 'database';

The arguments are: table name, column index (0-based), opening tag, closing tag. You can use any HTML or markers you want.

snippet()

The snippet() function extracts a portion of text around the match - perfect for search result previews:

SQL

-- Get a snippet of content around the match
SELECT
    title,
    snippet(articles, 1, '<b>', '</b>', '...', 10) AS preview
FROM articles
WHERE articles MATCH 'database';

The snippet() arguments are: table, column index, opening tag, closing tag, ellipsis text, and max tokens to include.

Using FTS5 with Existing Tables

So far we've stored content directly in the FTS5 table. But what if you already have a regular table and want to add search?

FTS5 supports external content tables - the FTS index references data stored elsewhere:

SQL

-- Create a regular table with our articles
CREATE TABLE articles_data (
    id INTEGER PRIMARY KEY,
    title TEXT,
    content TEXT,
    category TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO articles_data (title, content, category) VALUES
('Getting Started with SQLite', 'SQLite is a lightweight database engine.', 'Database'),
('How to Write SQL Queries', 'SQL queries let you retrieve data from your database.', 'Database'),
('Performance Tips', 'Indexes are key to database performance.', 'Performance');

-- Create FTS5 table that references the external content
CREATE VIRTUAL TABLE articles_fts USING fts5(
    title,
    content,
    category,
    content=articles_data,
    content_rowid=id
);

-- Populate the FTS index from existing data
INSERT INTO articles_fts(articles_fts) VALUES('rebuild');

-- Now search!
SELECT title FROM articles_fts WHERE articles_fts MATCH 'database';

The content= option tells FTS5 where the actual data lives. You'll need to keep the FTS index in sync when data changes - typically using triggers:

SQL

-- Trigger to keep FTS in sync on INSERT
CREATE TRIGGER articles_ai AFTER INSERT ON articles_data BEGIN
    INSERT INTO articles_fts(rowid, title, content, category)
    VALUES (new.id, new.title, new.content, new.category);
END;

-- Trigger for DELETE
CREATE TRIGGER articles_ad AFTER DELETE ON articles_data BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, content, category)
    VALUES ('delete', old.id, old.title, old.content, old.category);
END;

-- Trigger for UPDATE
CREATE TRIGGER articles_au AFTER UPDATE ON articles_data BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, content, category)
    VALUES ('delete', old.id, old.title, old.content, old.category);
    INSERT INTO articles_fts(rowid, title, content, category)
    VALUES (new.id, new.title, new.content, new.category);
END;

For many use cases, storing data directly in the FTS5 table is simpler. Use external content tables when you need the relational features of regular tables (foreign keys, additional columns, etc.).

Wrapping Up

SQLite's FTS5 is a legitimate full-text search engine hiding inside your database. You can:

Create searchable tables with CREATE VIRTUAL TABLE ... USING fts5()
Search with MATCH using terms, phrases, boolean operators, and prefixes
Rank by relevance with the bm25() function
Highlight matches and extract snippets for your UI
Index existing tables using external content mode

For many applications - especially desktop apps, mobile apps, and smaller web services - FTS5 is all the search engine you need. No external dependencies, no separate service to manage, just SQL.

Check out my other SQLite posts on JSON virtual columns and JSON functions if you want to keep exploring what SQLite can do.

Thanks for reading!

Jay