SQLite includes a powerful mechanism for enabling full-text search against SQLite records. Datasette can detect if a table has had full-text search configured for it in the underlying database and display a search interface for filtering that table.
Datasette detects which tables have been configured for full-text search when it first inspects the database on startup (or via the
datasette inspect command). You can visit the
/-/inspect page on your Datasette instance to see the results of this inspection. Tables that have been configured for full-text search will have their
fts_table property set to the name of another table (tables without full-text search will have this property set to
There are three different versions of the SQLite FTS module: FTS3, FTS4 and FTS5. You can tell which versions are supported by your instance of Datasette by checking the
FTS5 is the most advanced module, but is usually not available is the SQLite version that is bundled with Python. If in doubt, you should use FTS4.
Adding full-text search to a SQLite table¶
Datasette takes advantage of the external content mechanism in SQLite, which allows a full-text search virtual table to be associated with the contents of another SQLite table.
To set up full-text search for a table, you need to do two things:
- Create a new FTS virtual table associated with your table
- Populate that FTS table with the data that you would like to be able to run searches against
To enable full-text search for a table called
items that works against the
description columns, you would run the following SQL to create a new
items_fts FTS virtual table:
CREATE VIRTUAL TABLE "items_fts" USING FTS4 ( name, description, content="items" );
This creates a set of tables to power full-text search against
items. The new
items_fts table will be detected by Datasette as the
fts_table for the
Creating the table is not enough: you also need to populate it with a copy of the data that you wish to make searchable. You can do that using the following SQL:
INSERT INTO "items_fts" (rowid, name, description) SELECT rowid, name, description FROM items;
If your table has columns that are foreign key references to other tables you can include that data in your full-text search index using a join. Imagine the
items table has a foreign key column called
category_id which refers to a
categories table - you could create a full-text search table like this:
CREATE VIRTUAL TABLE "items_fts" USING FTS4 ( name, description, category_name, content="items" );
And then populate it like this:
INSERT INTO "items_fts" (rowid, name, description, category_name) SELECT items.rowid, items.name, items.description, categories.name FROM items JOIN categories ON items.category_id=categories.id;
You can use this technique to populate the full-text search index from any combination of tables and joins that makes sense for your project.
Setting up full-text search using csvs-to-sqlite¶
If your data starts out in CSV files, you can use Datasette’s companion tool csvs-to-sqlite to convert that file into a SQLite database and enable full-text search on specific columns. For a file called
items.csv where you want full-text search to operate against the
description columns you would run the following:
csvs-to-sqlite items.csv items.db -f name -f description
The table view API¶
Table views that support full-text search can be queried using the
?_search=TERMS querystring parameter. This will run the search against content from all of the columns that have been included in the index.
SQLite full-text search supports wildcards. This means you can easily implement prefix auto-complete by including an asterisk at the end of the search term - for example:
This will return all records containing at least one word that starts with the letters
You can also run searches against just the content of a specific named column by using
_search_COLNAME=TERMS - for example, this would search for just rows where the
name column in the FTS index mentions
Searches using custom SQL¶
You can include full-text search results in custom SQL queries. The general pattern with SQLite search is to run the search as a sub-select that returns rowid values, then include those rowids in another part of the query.
You can see the syntax for a basic search by running that search on a table page and then clicking “View and edit SQL” to see the underlying SQL. For example, consider this search for cherry trees in San Francisco:
If you click View and edit SQL you’ll see that the underlying SQL looks like this:
select rowid, * from Street_Tree_List where rowid in ( select rowid from [Street_Tree_List_fts] where [Street_Tree_List_fts] match "cherry" ) order by rowid limit 101