Full-text search with SQLite and Rails

Today, I planned to move a few small Ruby on Rails applications with Heroku using Kamal onto a single server. All applications use PostgreSQL as the database because it is simple to use with Heroku and because they take advantage of Postgres’ full-text search.

Because of the size and usage of those applications, I was not convinced that I wanted to manage my own PostgreSQL server or pay for a managed service. So, I started to consider replacing the database with SQLite. At least in my feed at X, I have seen so many posts in the past few months on how good and fast it is.

I wanted something like the pg_search gem, where I could define a scope name and the fields to be indexed. On the SQLite side, I found that it has an extension called FTS5, which is defined as:

FTS5 is an SQLite virtual table module that provides full-text search functionality to database applications. In their most elementary form, full-text search engines allow the user to efficiently search a large collection of documents for the subset that contains one or more instances of a search term. The search functionality provided to World Wide Web users by Google is, among other things, a full-text search engine, as it allows users to search for all documents on the web that contain, for example, the term “fts5”.

This looked promising, so I started implementing a quick and dirty solution for my needs. Starting from a model Post with title and content attributes, I want to define a full_search scope and indicate the attributes to index for full-text search.

Something like this:

class Post < ApplicationRecord include SqliteSearch search_scope(:title, :content) end 

Where SqliteSearch is the module that does the heavy work. Before continuing with the implementation, there are decisions to be made regarding how to store the indexed data in the database. A virtual table is required to store the indexed data; one option is to create a single table for this purpose with a record_type and record_id fields to identify the data per model type, just like the way it works for ActiveStorage or ActionText in Rails.

The second option is to create a table per indexed model. I chose this option since not all my models require this functionality. The migration for this table is as follows:

class PostSearch < ActiveRecord::Migration[7.0] def up execute("CREATE VIRTUAL TABLE fts_posts USING fts5(title, content, post_id)") end def down execute("DROP TABLE IF EXISTS fts_posts") end end 

The table name follows the Rails convention, but it adds the prefix fts_. The table fields are the ones that need indexing with the addition of the original record id with the foreign key convention. SQLite virtual tables don’t have data types, primary keys, constraints, or indexes.

The SqliteSearch module needs to implement a way to add or update the model data to the search index. This is done using the ActiveRecord callbacks for save and destroy commit.

module SqliteSearch extend ActiveSupport::Concern private def update_search_index primary_key = self.class.primary_key table_name = self.class.table_name foreign_key = self.class.to_s.foreign_key search_attrs = @@search_scope_attrs.each_with_object({}) { |attr, acc| acc[attr] = quote_string(send(attr) || "") } id_value = attributes[primary_key] sql_delete = <<~SQL.strip DELETE FROM fts_#{table_name} WHERE #{foreign_key} = #{id_value}; SQL self.class.connection.execute(sql_delete) sql_insert = <<~SQL.strip INSERT INTO fts_#{table_name}(#{search_attrs.keys.join(", ")}, #{foreign_key}) VALUES (#{search_attrs.values.map { |value| "'#{value}'" }.join(", ")}, #{attributes[primary_key]}); SQL self.class.connection.execute(sql_insert) end private def delete_search_index primary_key = self.class.primary_key table_name = self.class.table_name foreign_key = self.class.to_s.foreign_key id_value = attributes[primary_key] sql_delete = <<~SQL.strip DELETE FROM fts_#{table_name} WHERE #{foreign_key} = #{id_value}; SQL self.class.connection.execute(sql_delete) end included do after_save_commit :update_search_index after_destroy_commit :delete_search_index scope_foreign_key = to_s.foreign_key scope :full_search, ->(query) { return none if query.blank? sql = <<~SQL.strip SELECT #{scope_foreign_key} AS id FROM fts_#{table_name} WHERE fts_#{table_name} = '#{query}' ORDER BY rank; SQL ids = connection.execute(sql).map(&:values).flatten where(id: ids) } end class_methods do def search_scope(*attrs) @@search_scope_attrs = attrs end def rebuild_search_index(*ids) target_ids = Array(ids) target_ids = self.ids if target_ids.empty? scope_foreign_key = to_s.foreign_key delete_where = Array(ids).any? ? "WHERE #{scope_foreign_key} IN (#{ids.join(", ")})" : "" sql_delete = <<~SQL.strip DELETE FROM fts_#{table_name} #{delete_where}; SQL connection.execute(sql_delete) target_ids.each do |id| record = where(id: id).pluck(*@@search_scope_attrs, :id).first if record.present? id = record.pop sql_insert = <<~SQL.strip INSERT INTO fts_#{table_name}(#{@@search_scope_attrs.join(", ")}, #{scope_foreign_key}) VALUES (#{record.map { |value| "'#{quote_string(value)}'" }.join(", ")}, #{id}); SQL connection.execute(sql_insert) end end end def quote_string(s) s.gsub("\\", '\&\&').gsub("'", "''") end end end 

The class method search_scope tells the module the attributes we need to index. The update_search_index callback is called when the record is created or updated. Since SQLite, virtual tables don’t support upsert, which is a way to tell the database to insert a record if it doesn’t exist or to update it if it does. Also, the Rails SQLite adapter does not support multiple statements in a single execution call.

These limitations forced me to make two additional database calls: the first to delete the indexed data for a specific record, and the second to insert the new indexed data. It’s not very performant, but for a small database, it might be just fine. The delete_search_index callback removes the indexed data when a record is deleted.

The module also implements a class method, rebuild_search_index, which optionally receives an array of record ids to re-index. If no ids are passed, then it rebuilds the index for all records.

Finally, a scope full_search is added to fetch records based on the full-text search index. You can use keywords like “AND”, “OR,” or “NOT” to refine your search or any other special character supported by SQLite FTS5.

Post.full_search("Ruby OR Rails NOT Javascript") 

Conclusion

Adding this module to my applications allowed me to explore the idea of moving from PostgreSQL for these small applications. Not because PostgreSQL is bad, but because it might be too much for the current application’s needs.