Datasette for search
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.
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.
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
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.