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 uses this approach. Here's an example search, for bones:

This uses the search canned query, which is defined here and looks like this:

  join museums_fts on = museums_fts.rowid
  museums_fts match case
    when '' then '*'
    else escape_fts_query(:q)
order by

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:

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 - you can read more about how it works in Building a search engine for

More uses for Datasette