Datasette for search
Datasette is built on top of SQLite, which includes a robust, full-featured full-text search implementation.
Datasette automatically detects tables which have been configured for full-text search and adds a search box which can be used by human site users or accessed from the JSON or CSV APIs.
You can try this out now by searching FARA records or global power plants.
Enabling search for a table
The sqlite-utils command-line tool can be used to enable full-text search on a specific set of columns for a table:
% sqlite-utils enable-fts mydb.db documents title summary
Alternatively, you can configure search directly within the Datasette interface by installing the datasette-configure-fts plugin.
Fast Autocomplete Search for Your Website is a tutorial that shows how to write a scraper that populates a full-text search index in Datasette, then build a JavaScript autocomplete search interface using the Datasette JSON API.
Implementing search with a canned query
Datasette canned queries can be used to configure a SQL query which returns search results for a table, ordered by relevance.
The search on www.niche-museums.com uses this approach. Here's an example search, for bones:
This uses the search canned query, which is defined here and looks like this:
select
museums_fts.rank,
museums.*
from
museums
join museums_fts on museums.id = museums_fts.rowid
where
museums_fts match case
:q
when '' then '*'
else escape_fts_query(:q)
end
order by
museums_fts.rank
Try that query here.
The results are then rendered by this custom template.
Plugins for search
The datasette-search-all plugin adds a search box which runs searches in parallel against all of the FTS-configured tables in all of the databases attached to Datasette.
You can see that in action here: fara.datasettes.com/-/search?q=manafort
dogsheep-beta is a plugin which adds a customizable search engine that can create an index to search multiple tables at once with combined relevance scoring. It is used for the search on this site, for example datasette.io/-/beta?q=fts - you can read more about how it works in Building a search engine for datasette.io.