The last advanced feature we're adding to our Lorem News application is site-wide search, powered by SQLite's Full-Text Search (FTS) engine.

Now, let me ask you—would a "toy database" include a powerful and fully-featured full-text search engine out of the box? I don’t think so, but SQLite does, and it comes built-in. The official documentation is linked below, and I highly recommend checking it out. Even after working with SQLite for years, I was surprised at how much power, functionality, and flexibility is packed into its full-text search capabilities.

We’re going to build our site-wide search using SQLite’s native full-text search engine. Even so, we’re only scratching the surface, covering about 10-15% of what’s possible.

Setting Up Full-Text Search in Our Rails App

To start, we need to create a migration that sets up a virtual table for full-text search.

1. We create an FTS5 (Full-Text Search version 5) virtual table called post_documents.
2. We configure it to index both the title and body of our posts table.
3. We optimize storage by using a contentless index, meaning we don’t duplicate data—we just store the searchable index.
4. We use Unicode tokenization, removing diacritics (e.g., Ü becomes U) to ensure a more flexible and predictable search experience.

After running the migration, we now have our virtual table in place.

Building the Search Index and Query Methods

Next, we need to create a PostDocument Active Record model to handle search indexing and queries.

One key thing to note: Active Record models don’t have to be backed by a physical table—they can also be backed by virtual tables like our FTS5 index.

We define two class methods:

• build_index! – Inserts or updates the full-text search index by pulling data from the posts table.
• matches – Runs a search query, retrieving matching row IDs along with a relevance score.

Since our virtual table doesn’t store post content directly, we fetch the actual post records by joining results with our posts table.

Integrating Search into the Post Model

To allow our Post model to perform searches, we define a class-level search method.

Here’s how it works:

1. We use a Common Table Expression (CTE) to define search matches as a virtual table.
2. We join the matches with our posts table to fetch full post details.
3. We sort results by relevance, ensuring the strongest matches appear first.

This efficiently ranks posts based on how well they match the search query.

Building the Search UI

Now that our backend is ready, we need to wire up the frontend.

1. We add a search route that points to a search controller.
2. We allow unauthenticated users to search all posts.
3. We create a search form with a GET request, so searches update the URL query parameters.

Enhancing the Search UI

To improve user experience, we:

• Display the current search term inside the input field.
• Use flexbox styling to improve the layout.
• Render search results dynamically, using the same partial as our posts index.
• Show a "No posts found" message when there are no results.

With just a few refinements, the search UI now looks clean and functions smoothly.

Testing Our Search Implementation

Let’s do a quick test:

1. Searching for "query" returns no results (as expected).
2. Searching for "content" finds one post.
3. Searching for "post" finds two posts (since it appears in multiple titles).

Everything is working exactly as intended!

Final Thoughts

• We successfully implemented site-wide search using SQLite’s FTS5.
• The search is optimized for performance, storage efficiency, and flexibility.
• Our search ranks results based on relevance, ensuring users get the best matches first.

This completes the search feature in our Lorem News application! 🚀