There’s been a trend recently to use SQLite as the main database for Rails applications. And I get it: SQLite is lightweight, easy to set up, and doesn’t require a separate server process. Stephen Margheim, one of the main contributors for the sqlite3 gem has several talks about it, so why not give it a go?

SQLite has a built-in full-text search (FTS) extension that allows you to perform full-text searches on your data called, surprisingly, FTS5. It uses a virtual table to store the indexed data, which is a bit different from how PostgreSQL does it (but I’ll get to that later).

Setup

We start with a simple Document model that has a title and content. Let’s pretend the whole document’s content is gonna be stored in a single column, for simplicity’s sake.

class CreateDocuments < ActiveRecord::Migration[8.0]
  def change
    create_table :documents do |t|
      t.string :title
      t.text :content
      t.timestamps
    end
  end
end

In order to use FTS5, we need to create a virtual table. Thankfully, the sqlite3 adapter includes a handy method for this, which we can use in our migration.

class CreateDocuments < ActiveRecord::Migration[8.0]
  def change
    create_table :documents do |t|
      t.string :title
      t.text :content
      t.timestamps
    end

    # Create the FTS5 virtual table
    create_virtual_table :document_fts, using: :fts5, %w[title content document_id]
  end
end

We can link these two tables together by creating the relationship between them via ActiveRecord.

# app/models/document.rb
class Document < ApplicationRecord
  has_one :document_fts, foreign_key: :document_id, dependent: :destroy
end

# app/models/document_fts.rb
class DocumentFts < ApplicationRecord
  self.primary_key = "rowid"
end

Automatically updating the FTS table

To keep the FTS table in sync with the main table, we can use ActiveRecord callbacks.

# app/models/document.rb
class Document < ApplicationRecord
  has_one :document_fts, foreign_key: :document_id, dependent: :destroy

  after_create :create_fts
  after_update :update_fts

  private

  def create_fts
    DocumentFts.create(
      title: title,
      content: content,
      document_id: id
    )
  end

  def update_fts
    document_fts.update(
      title: title,
      content: content
    )
  end
end

Searching

Now we can perform full-text searches on our documents using the FTS table. We can create a simple search method in our Document model.

# app/models/document.rb
class Document < ApplicationRecord
  scope :search, ->(query) {
    return none if query.blank?
    
    where("document_fts MATCH ?", query)
      .joins(:document_fts)
      .order("bm25(document_fts)")
      .distinct
  }
end

..So now, you can easily call it from your controller.

# app/controllers/documents_controller.rb
class DocumentsController < ApplicationController
  def index
    if params[:query].present?
      @documents = Document.search(params[:query])
    else
      @documents = Document.all
    end
  end
end

Conclusion

Easy, right? Suspiciously easy. But there are a few caveats to keep in mind. The most serious of them being that tou’re responsible for keeping the FTS table in sync with the main table. This is a bit of a pain, but it’s not too bad if you remember to update the FTS table whenever you create or update a document. While I’m using callbacks in this examples, a better suited approach might be to call Service Objects to handle the creation and updating of the FTS table. This way, you can keep your models clean and separate the concerns.

Further reading