Full-text search with Rails and SQLite

In this two-part blog post series we're going to use Rails 8 and SQLite to search over a collection of movies using full-text search and vector search. You can find the code in this repository. On this first part, we'll be focusing on finding movies via a full-text search.

Why SQLite?

A bunch of work has gone into making the SQLite adapter and Ruby driver suitable for real production use in Rails 8. Rails 8 takes SQLite from a lightweight development tool to a reliable choice for production use. SQLite now has the capability to power Action Cable, Rails.cache, and Active Job effectively.

If you are interested in learning more about running SQLite in production. I suggest taking a look at Stephen Margheim's Rails World talk 'SQLite on Rails'.

The Application

The app is going to show a catalog of movies which I got from the Movie Database (TMDB). Here's the Movie model that we will use.

# == Schema Information
#
# Table name: movies
#
#  id         :integer          not null, primary key
#  overview   :text
#  poster_url :string
#  title      :string           not null
#  created_at :datetime         not null
#  updated_at :datetime         not null
#  tmdb_id    :bigint           not null
#
class Movie < ApplicationRecord
  include FullTextSearch
  include VectorSearch
end

‍

Full-text search

We'll be using FTS5 to power the full-text search.

‍FTS5 is an SQLite extension that provides full-text search capabilities. It uses virtual tables optimized for text searching, as well as specialized indexing for quick retrieval of text data.

Overall, the steps to follow are:

1. Create the fts5 virtual table
2. Populate the virtual table
3. Write the search query
4. Search for records

1. Create the fts5 virtual table

We'll start by generating a migration and using the recently added create_virtual_table method to create the virtual table. We'll use "title" and "overview" as the search columns.

class CreateVirtualMovieFts < ActiveRecord::Migration[8.0]  
  def change    
    create_virtual_table :movie_fts, :fts5, ["title", "overview"]
  end
end

# app/models/movie_fts.rb
class MovieFts < ApplicationRecord
  self.primary_key = "rowid"
end

‍

2. Populate the virtual table

Next, we want to create a new MovieFts every time a new Movie is added to the database. It's important to note that we can't add a primary key declaration to fts5 virtual tables, so we'll use the implicit rowid key to match records to the "movies" table.

module Movie::FullTextSearch
  extend ActiveSupport::Concern

  included do
    has_one :movie_fts, foreign_key: "rowid"
  end

  def find_or_create_movie_fts
    return if movie_fts

    sql = ActiveRecord::Base.sanitize_sql_array(
      [
        "INSERT INTO movie_fts (rowid, title, overview) VALUES (?, ?, ?)",
        id, title, overview
      ]
    )
    ActiveRecord::Base.connection.execute(sql)
  end
end

‍

3. Write the search query

Let's create a query that uses FTS5 to search for movies. We'll use the bm25 function to get the most relevant results first.

BM25 is a ranking function measuring relevance based on term frequency and document length.

module Movie::FullTextSearch
  extend ActiveSupport::Concern

  included do
    has_one :movie_fts, foreign_key: "rowid"
  end

  class_methods do
    def full_text_search(input:, limit:)
      where("movie_fts MATCH ?", input)
        .joins(:movie_fts)
        .limit(limit)
        .order("bm25(movie_fts)")
        .distinct
    end
  end
end

‍

4. Search for records

We can use the full_text_search method when users want to search for a movie.

class MoviesController < ApplicationController
  def index
    @movies = if params[:search].present?
      Movie.full_text_search(input: params[:search], limit: 40)
    else
      Movie.all.limit(40)
    end
  end
end

‍

That's it, if you have any questions you can find me at sergio@teloslabs.co. On the next blog post, we’ll go beyond keywords and explore vector search, making it possible to find similar movies based on meaning rather than just words.

‍