Build a Perfect Blog with FastAPI: Full-Text Search for Posts

In the previous article, we added an image upload feature to our blog posts.

As time goes on, you can imagine your blog has accumulated a significant number of articles. A new problem gradually emerges: How can readers quickly find the articles they want to read?

The answer, of course, is search.

In this tutorial, we will add a full-text search feature to our blog.

You might be thinking, can't I just use a SQL LIKE '%keyword%' query to implement search?

For simple scenarios, you certainly can. However, LIKE queries perform poorly when dealing with large blocks of text and cannot handle fuzzy searches (for example, searching for "creation" won't match "create").

Therefore, we will adopt a more efficient solution: utilizing PostgreSQL's built-in Full-Text Search (FTS) functionality. It's not only fast but also supports features like stemming and ranking by relevance, providing search capabilities far superior to LIKE.

Step 1: Database Search Infrastructure

To use PostgreSQL's FTS feature, we first need to make some modifications to our post table. The core idea is to create a special column dedicated to storing optimized text data that can be searched at high speed.

Core Concept: tsvector

We will add a new column of type tsvector to the post table. It will break down the title and content of an article into individual words (lexemes) and normalize them (for example, processing both "running" and "ran" into "run") for subsequent queries.

Modifying the Table Structure

Execute the following SQL statement in your PostgreSQL database to add the search_vector column to the post table.

ALTER TABLE "post" ADD COLUMN "search_vector" tsvector;

If your database was created on Leapcell,

you can easily execute SQL statements using the graphical interface. Simply go to the Database management page on the website, paste the above statement into the SQL interface, and execute it.

Updating the Search Vector for Existing Posts

Updating the search vector (search_vector) for posts will make them searchable.

Since your blog already has some articles, you can simply execute the following SQL statement to generate search_vector data for them:

UPDATE "post" SET search_vector =
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(content, '')), 'B');

Automatic Updates with a Trigger

No one wants to manually update the search_vector column every time a post is created or updated. The best way is to have the database do this work automatically. This can be achieved by creating a trigger.

First, create a function that, just like the query above, generates the search_vector data for a post.

CREATE OR REPLACE FUNCTION update_post_search_vector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(NEW.content, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

The setweight function allows you to assign different weights to text from different fields. Here, we've set the weight of the title ('A') higher than the content ('B'). This means that in search results, articles with the keyword in the title will be ranked higher.

Next, create a trigger that automatically calls the function we just created whenever a new post is inserted (INSERT) or updated (UPDATE).

CREATE TRIGGER post_search_vector_update
BEFORE INSERT OR UPDATE ON "post"
FOR EACH ROW EXECUTE FUNCTION update_post_search_vector();

Creating a Search Index

Finally, we need to create a GIN (Generalized Inverted Index) on the search_vector column to ensure search performance.

CREATE INDEX post_search_vector_idx ON "post" USING gin(search_vector);

Now, your database is search-ready. It will automatically maintain an efficient search index for every article.

Step 2: Building the Search Logic in FastAPI

With the database layer prepared, let's return to our FastAPI project to write the backend code for handling search requests.

Creating the Search Route

We will add the search-related logic directly to the routers/posts.py file. Since SQLModel is based on SQLAlchemy, we can use SQLAlchemy's text() function to execute raw SQL queries.

Open routers/posts.py and make the following changes:

# routers/posts.py
import uuid
from fastapi import APIRouter, Request, Depends, Form, Query
from fastapi.responses import HTMLResponse, RedirectResponse
from fastapi.templating import Jinja2Templates
from sqlmodel import Session, select
from sqlalchemy import text # Import the text function

from database import get_session
from models import Post
from auth_dependencies import get_user_from_session, login_required
import comments_service
import markdown2

router = APIRouter()
templates = Jinja2Templates(directory="templates")

# ... other routes ...

@router.get("/posts/search", response_class=HTMLResponse)
def search_posts(
    request: Request, 
    q: str = Query(None), # Get the search term from query parameters
    session: Session = Depends(get_session),
    user: dict | None = Depends(get_user_from_session)
):
    posts = []
    if q:
        # Convert user input (e.g., "fastapi blog") to a format
        # to_tsquery can understand ("fastapi & blog")
        search_query = " & ".join(q.strip().split())
        
        # Use raw SQL for full-text search
        statement = text("""
            SELECT id, title, content, "createdAt"
            FROM post
            WHERE search_vector @@ to_tsquery('english', :query)
            ORDER BY ts_rank(search_vector, to_tsquery('english', :query)) DESC
        """)
        
        results = session.exec(statement, {"query": search_query}).mappings().all()
        posts = list(results)

    return templates.TemplateResponse(
        "search-results.html", 
        {
            "request": request, 
            "posts": posts, 
            "query": q, 
            "user": user, 
            "title": f"Search Results for '{q}'"
        }
    )

# Ensure this route is placed after /posts/search to avoid route conflicts
@router.get("/posts/{post_id}", response_class=HTMLResponse)
def get_post_by_id(
    # ... function content remains the same
# ...

Code Explanation:

• We add from sqlalchemy import text at the top of the file.
• A new /posts/search route is added. To avoid conflicts with the /posts/{post_id} route, make sure to place this new route before the get_post_by_id route.
• q: str = Query(None): FastAPI gets the value of q from the URL's query string (e.g., /posts/search?q=keyword).
• to_tsquery('english', :query): This function converts the user-provided search string into a special query type that can be matched against a tsvector column. We use & to join multiple words, indicating that all words must be matched.
• @@ operator: This is the "matches" operator for full-text search. The line WHERE search_vector @@ ... is the core of the search operation.
• ts_rank(...): This function calculates a "relevance ranking" based on how well the query terms match the blog post. We sort by this rank in descending order to ensure the most relevant articles appear first.
• session.exec(statement, {"query": search_query}).mappings().all(): We execute the raw SQL query and use .mappings().all() to convert the results into a list of dictionaries, making them easy to use in the template.

Step 3: Integrating the Search Functionality into the Frontend

The backend API is ready. Now let's add a search box and a search results page to our user interface.

Adding the Search Box

Open the templates/_header.html file and add a search form to the navigation bar.

<header>
  <h1><a href="/">My Blog</a></h1>
  <nav>
    <form action="/posts/search" method="GET" class="search-form">
      <input type="search" name="q" placeholder="Search posts..." required>
      <button type="submit">Search</button>
    </form>
    {% if user %}
      <span class="welcome-msg">Welcome, {{ user.username }}</span>
      <a href="/posts/new" class="new-post-btn">New Post</a>
      <a href="/auth/logout" class="nav-link">Logout</a>
    {% else %}
      <a href="/users/register" class="nav-link">Register</a>
      <a href="/auth/login" class="nav-link">Login</a>
    {% endif %}
  </nav>
</header>

Creating the Search Results Page

Create a new file named search-results.html in the templates directory. This page will be used to display the search results.

{% include "_header.html" %}

<div class="search-results-container">
  <h2>Search Results for: "{{ query }}"</h2>

  {% if posts %}
  <div class="post-list">
    {% for post in posts %}
    <article class="post-item">
      <h2><a href="/posts/{{ post.id }}">{{ post.title }}</a></h2>
      <p>{{ post.content[:150] }}...</p>
      <small>{{ post.createdAt.strftime('%Y-%m-%d') }}</small>
    </article>
    {% endfor %}
  </div>
  {% else %}
  <p>No posts found matching your search. Please try different keywords.</p>
  {% endif %}
</div>

{% include "_footer.html" %}

Running and Testing

Restart your application:

uvicorn main:app --reload

Open your browser and navigate to your blog's homepage.

Let's write a new article containing the keyword "testing".

After saving the post, type "test" into the search box and perform a search.

On the search results page, the article you just created now appears in the results.

Your blog now supports a full-text search feature. No matter how much you write, your readers will no longer get lost.