Create Fts5 Virtual Table in the Sqlite Cli
Related to my recent interest in information retrieval in general and SQLite full-text search in particular, I wanted to see if I could create a virtual table using FTS5 in the SQLite CLI. It works!
SQLite FTS5 #
FTS5 is an SQLite virtual table module that provides full-text search functionality to database application. In their most elementary form, full-text search engines allow the user to efficiently search a large collection of documents for the subset that contain one or more instances of a search term. ( https://www.sqlite.org/fts5.html )
Among other things, Google is a full-text search engine.
Create an FTS5 virtual table #
find-command ❯ sqlite3 tmux_key_bindings.sqlite3
SQLite version 3.53.4 2026-07-24 19:02:57
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE key_bindings (
id INTEGER PRIMARY KEY,
key TEXT NOT NULL UNIQUE,
description TEXT NOT NULL,
prefix TEXT NOT NULL,
tmux_version TEXT NOT NULL,
source TEXT NOT NULL
);
sqlite> CREATE VIRTUAL TABLE temp.key_bindings_fts
...> USING fts5(
(x1...> key,
(x1...> description,
(x1...> prefix,
(x1...> tmux_version,
(x1...> source
(x1...> );
sqlite> INSERT INTO temp.key_bindings_fts (
(x1...> key,
(x1...> description,
(x1...> prefix,
(x1...> tmux_version,
(x1...> source
(x1...> )
...> SELECT
...> key,
...> description,
...> prefix,
...> tmux_version,
...> source
...> FROM main.key_bindings;
sqlite> SELECT key, description
...> FROM temp.key_bindings_fts
...> WHERE key_bindings_fts MATCH 'resize cell';
╭───────────────────────────────┬───────────────────────────────────────────────╮
│ key │ description │
╞═══════════════════════════════╪═══════════════════════════════════════════════╡
│ C-Up, C-Down, C-Left, C-Right │ Resize the current pane in steps of one cell. │
╰───────────────────────────────┴───────────────────────────────────────────────╯
Full text search with boolean operators #
Boolean operators, or query operators can be used: AND, OR, NOT:
AND:
sqlite> SELECT key, description FROM temp.key_bindings_fts WHERE key_bindings_fts MATCH 'one AND pane';
╭───────────────────────────────┬───────────────────────────────────────────────╮
│ key │ description │
╞═══════════════════════════════╪═══════════════════════════════════════════════╡
│ C-Up, C-Down, C-Left, C-Right │ Resize the current pane in steps of one cell. │
╰───────────────────────────────┴───────────────────────────────────────────────╯
OR:
sqlite> SELECT key, description FROM temp.key_bindings_fts WHERE key_bindings_fts MATCH 'create OR clear';
╭─────┬─────────────────────────────╮
│ key │ description │
╞═════╪═════════════════════════════╡
│ * │ Create a new floating pane. │
│ c │ Create a new window. │
│ M │ Clear the marked pane. │
╰─────┴─────────────────────────────╯
NOT:
sqlite> SELECT key, description FROM temp.key_bindings_fts WHERE key_bindings_fts MATCH 'one NOT seven';
╭───────────────────────────────┬───────────────────────────────────────────────╮
│ key │ description │
╞═══════════════════════════════╪═══════════════════════════════════════════════╡
│ Page Up │ Enter copy mode and scroll one page up. │
│ C-Up, C-Down, C-Left, C-Right │ Resize the current pane in steps of one cell. │
╰───────────────────────────────┴───────────────────────────────────────────────╯
Phrase search #
'"current window"' versus 'current window'
sqlite> SELECT key, description FROM temp.key_bindings_fts WHERE key_bindings_fts MATCH '"current window"';
╭───────┬───────────────────────────────────────────────────────╮
│ key │ description │
╞═══════╪═══════════════════════════════════════════════════════╡
│ C-o │ Rotate the panes in the current window forwards. │
│ & │ Kill the current window. │
│ , │ Rename the current window. │
│ . │ Prompt for an index to move the current window. │
│ i │ Display some information about the current window. │
│ o │ Select the next pane in the current window. │
│ w │ Choose the current window interactively. │
│ Space │ Arrange the current window in the next preset layout. │
│ M-o │ Rotate the panes in the current window backwards. │
╰───────┴───────────────────────────────────────────────────────╯
sqlite> SELECT key, description FROM temp.key_bindings_fts WHERE key_bindings_fts MATCH 'current window';
╭───────┬───────────────────────────────────────────────────────╮
│ key │ description │
╞═══════╪═══════════════════════════════════════════════════════╡
│ C-o │ Rotate the panes in the current window forwards. │
│ ! │ Break the current pane out of the window. │
│ & │ Kill the current window. │
│ , │ Rename the current window. │
│ . │ Prompt for an index to move the current window. │
│ i │ Display some information about the current window. │
│ o │ Select the next pane in the current window. │
│ w │ Choose the current window interactively. │
│ Space │ Arrange the current window in the next preset layout. │
│ M-o │ Rotate the panes in the current window backwards. │
╰───────┴───────────────────────────────────────────────────────╯
Prefix search #
'select*' matches “select”, “selected”, “selected-pane”,…
sqlite> SELECT key, description FROM temp.key_bindings_fts WHERE key_bindings_fts MATCH 'select*';
╭────────┬─────────────────────────────────────────────────────────────╮
│ key │ description │
╞════════╪═════════════════════════════════════════════════════════════╡
│ '''' │ Prompt for a window index to select. │
│ 0 to 9 │ Select windows 0 to 9. │
│ l │ Move to the previously selected window. │
│ m │ Mark the current pane (see select-pane -m). │
│ o │ Select the next pane in the current window. │
│ s │ Select a new session for the attached client interactively. │
╰────────┴─────────────────────────────────────────────────────────────╯
Restrict search to one column #
sqlite> SELECT key, description FROM temp.key_bindings_fts WHERE key_bindings_fts MATCH 'key:c';
╭───────────────────────────────┬───────────────────────────────────────────────────────╮
│ key │ description │
╞═══════════════════════════════╪═══════════════════════════════════════════════════════╡
│ C-b │ Send the prefix key (C-b) through to the application. │
│ C-o │ Rotate the panes in the current window forwards. │
│ C-z │ Suspend the tmux client. │
│ c │ Create a new window. │
│ C-Up, C-Down, C-Left, C-Right │ Resize the current pane in steps of one cell. │
╰───────────────────────────────┴───────────────────────────────────────────────────────╯
Ranking results with the BM25 algorithm #
sqlite> SELECT key, description, bm25(key_bindings_fts) AS score FROM temp.key_bindings_fts WHERE key_bindings_fts MATCH 'pane' ORDER BY score;
bm25() is a relevance scoring function for full-text search results. See
https://www.sqlite.org/fts5.html#the_bm25_function
.
The built-in auxiliary function bm25() returns a real value indicating how well the current row matches the full-text query. The better the match, the numerically smaller the value returned. A query such as the following may be used to return matches in order from best to worst match:
SELECT * FROM ft WHERE ft MATCH ? ORDER BY bm25(ft)In order to calculate a document’s score, the full-text query is separated into its compnent phrases. The bm25 score for document D and query Q is then calculates as follows:
$$ \operatorname{bm25}(D,Q) #
-1 \sum_{i=1}^{nPhrase} \operatorname{IDF}(q_i) \frac{ f(q_i,D)\cdot(k_1+1) }{ f(q_i,D) + k_1\cdot \left( 1-b+b\frac{|D|}{avgdl} \right) } $$
nPhrases is the number of phrases in the query, |D| is the number of tokens in the current document, and avgdl is the average number of tokens in all documents within the FTS5 table. k1 and b are both constants, hard-coded at 1.2 and 0.75 respectively.
The “-1” term at the start of the formula is not found in most implementations of the BM25 algorithm. It just affects the order. Without it, appending “ORDER BY bm25(ft)” would cause results to be returned from worst to best.
Look at the actual docs for more details.
Given the formula, these results make intuivive sense (shorter documents result in better ranking):
sqlite> SELECT description, bm25(key_bindings_fts) AS score FROM temp.key_bindings_fts WHERE key_bindings_fts MATCH 'attached client' ORDER BY score;
╭─────────────────────────────────────────────────────────────┬─────────────────────╮
│ description │ score │
╞═════════════════════════════════════════════════════════════╪═════════════════════╡
│ Force redraw of the attached client. │ -4.0382103086858816 │
│ Switch the attached client to the previous session. │ -3.9426941647222211 │
│ Switch the attached client to the next session. │ -3.9426941647222211 │
│ Switch the attached client back to the last session. │ -3.7646050932918573 │
│ Select a new session for the attached client interactively. │ -3.7646050932918573 │
╰─────────────────────────────────────────────────────────────┴─────────────────────╯
In the query 'application OR move', “application” only exists in one document, so it has a high inverse document frequency. That makes it an informative term. The row containing it gets a strong score. The term “move” appears in 5 documents, so it has a lower inverse document frequency. The differences in scores for documents containing “move” are mostly explained by document-length normalization—the shorter the document, the better the ranking.
sqlite> SELECT description, bm25(key_bindings_fts) AS score FROM temp.key_bindings_fts WHERE key_bindings_fts MATCH 'application OR move' ORDER BY score;
╭─────────────────────────────────────────────────────────────┬─────────────────────╮
│ description │ score │
╞═════════════════════════════════════════════════════════════╪═════════════════════╡
│ Send the prefix key (C-b) through to the application. │ -3.3038976473724171 │
│ Move to the previously active pane. │ -2.3336040338605368 │
│ Move to the previously selected window. │ -2.2770698849917754 │
│ Prompt for an index to move the current window. │ -2.1718394410126951 │
│ Move to the next window with a bell or activity marker. │ -1.9880880429481658 │
│ Move to the previous window with a bell or activity marker. │ -1.9880880429481658 │
╰─────────────────────────────────────────────────────────────┴─────────────────────╯