Now that my blog is statically generated I need a way to support searching.

Fuse.js ships with the theme and does a pretty good job of matching words in the blog posts.

I want something a little bit more powerful.

I mentioned in my previous post that I am using SQLite to store the blog posts. SQLite has a full text search feature that I can use to implement search.

Enabling Full Text Search (FTS) is a one-liner using sqlite-utils.

# sqlite-utils enable-fts <dbname> <tablename> <columns> --create-triggers
sqlite-utils enable-fts blog.db posts title mdbody --create-triggers

This takes care of creating the necessary tables and populating them with the inverted index for the columns (“title” and “mdbody”) I specified. The --create-triggers option ensures that the search index stays up to date with any updates to the content.

Now that FTS is enabled, let’s try searching. I could craft a sql query to do the search and try it out in the litecli repl. But using sqlite-utils it is trivial to do it from the commandline.

sqlite-utils search blog.db posts "lemon*" --limit 5

This prints the top 5 rows that match my search query.

I don’t want all the columns, just the url and title columns should suffice. Also let’s print the output as a table instead of JSON.

sqlite-utils search blog.db posts "lemon*" --limit 5 -c url  -c title --table

Tada! We have a working search in commandline.

As much as I love the commandline, it doesn’t help me integrate the search into the blog.

That’s where datasette comes in. Datasette is a tool to create a REST interface (and a Web UI) for SQLite databases.

I can launch a datasette server with the blog database and use the REST API to query the database.

datasette serve blog.db

I can visit http://localhost:8001 to view the web interface and try out the search feature. Datasette is smart enough to autodetect that FTS is enabled for a table and provide a nice input box to search.

datasette screenshot

I used the datasette-publish-fly to publish the database to You can try out the search feature at It is not yet integrated into the blog search yet. That’ll come later.

Thanks to Simon Willison for creating sqlite-utils and datasette and writing such detailed documentation of the tools.