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.
I used the datasette-publish-fly to publish the database to fly.io. You can try out the search feature at https://amjith-blog-fts-search.fly.dev/fts_blog/posts. 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.