Build Your Own Forum with FastAPI: Step 8 - Full Text Search

In the previous article, we implemented a basic permissions system for our forum, supporting "administrator" and "user banning" capabilities, laying the groundwork for a healthy community.

As the forum accumulates more content, users might find it difficult to locate old posts they are interested in. A new requirement is emerging: shouldn't there be a search function to help users quickly find the articles they want to read?

In this article, we are going to add a full-text search feature to our forum.

If you have some knowledge of SQL, you might be thinking: can't we just use a LIKE '%keyword%' query to implement search? For simple scenarios, this is indeed possible. But LIKE queries perform extremely poorly when dealing with large amounts of text and cannot understand linguistic complexities (for example, searching for "create" won't match "creating").

Therefore, we will adopt a more professional and efficient solution: using PostgreSQL's built-in Full-Text Search (FTS) feature. It's not only fast but also supports stemming, ignoring stop words, and sorting by relevance, providing search capabilities far superior to LIKE.

Step 1: Database Search Infrastructure (SQL)

To use PostgreSQL's FTS feature, we first need to make some modifications to our posts table: we'll create a special column specifically for storing optimized, high-speed searchable text data.

Add the tsvector Column

We will add a new column named search_vector of type tsvector to the posts table. Its purpose is like a dictionary, breaking down the post's title and content into individual words (lexemes) and processing them.

ALTER TABLE posts ADD COLUMN "search_vector" tsvector;

Use a Trigger to Automatically Update the tsvector Column

The search_vector column doesn't contain content by itself; we need to convert the title and content into the tsvector format and write it into this column.

Nobody 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 using a trigger.

First, let's create a function. This function's job is to concatenate the title and content and convert them into the tsvector format.

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 us to set different weights for text from different fields. Here, we are setting the weight for the title ('A') higher than for the content ('B'). This means that in the search results, posts with the keyword in the title will be ranked higher.

Next, create a trigger that will automatically call the function we just created every time a new post is inserted (INSERT) or updated (UPDATE).

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

Create the Search Index

To ensure search speed, the final step is to create a GIN (Generalized Inverted Index) index on the search_vector column.

CREATE INDEX post_search_vector_idx ON posts USING gin(search_vector);

Step 2: Backfill Existing Data

It's important to note that the trigger we created only works for posts created or modified in the future. For posts that already exist in the database, their search_vector field is still NULL.

We need to run a one-time UPDATE statement to generate search vectors for all existing posts:

-- Backfill search_vector for all existing posts
UPDATE posts SET search_vector =
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(content, '')), 'B');

If your database was created using Leapcell,

you can execute these SQL statements directly in its web-based operation panel.

Step 3: Create the Search Results Page

We need a new HTML page to display the search results.

In the templates folder, create a new file named search_results.html. This page is very similar to posts.html, but it will additionally display the user's search query.

templates/search_results.html

<!DOCTYPE html>
<html>
  <head>
    <title>Search Results - My FastAPI Forum</title>
    <style>
      /* (Copy all styles from templates/posts.html) */
      body {
        font-family: sans-serif;
        margin: 2em;
      }
      input,
      textarea {
        width: 100%;
        padding: 8px;
        margin-bottom: 10px;
        box-sizing: border-box;
      }
      button {
        padding: 10px 15px;
        background-color: #007bff;
        color: white;
        border: none;
        cursor: pointer;
      }
      header {
        display: flex;
        justify-content: space-between;
        align-items: center;
      }
      .post-item {
        border: 1px solid #ccc;
        padding: 10px;
        margin-bottom: 10px;
      }
      /* (End of copied styles) */
    </style>
  </head>
  <body>
    <header>
      <h1><a href="/posts" style="text-decoration: none; color: black;">Welcome to my Forum</a></h1>
      <div class="auth-links">
        {% if current_user %}
        <span>Welcome, {{ current_user.username }}!</span>
        {% if current_user.is_admin %}
        <a href="/admin" style="color: red; font-weight: bold;">[Admin Panel]</a>
        {% endif %}
        <a href="/logout">Logout</a>
        {% else %}
        <a href="/login">Login</a> |
        <a href="/register">Register</a>
        {% endif %}
      </div>
    </header>
    <form action="/search" method="GET" style="display: inline-block;">
      <input type="search" name="q" placeholder="Search posts..." value="{{ query | escape }}" />
      <button type="submit">Search</button>
    </form>

    <hr />
    <h2>Search Results: "{{ query | escape }}"</h2>

    {% if posts %} {% for post in posts %}
    <div class="post-item">
      <a href="/posts/{{ post.id }}"><h3>{{ post.title }}</h3></a>
      <p>{{ post.content }}</p>
      <small>Author: {{ post.owner.username if post.owner else 'Unknown' }}</small>
    </div>
    {% endfor %} {% else %}
    <p>No posts found matching "{{ query | escape }}". Please try different keywords.</p>
    {% endif %}
  </body>
</html>

Note that we also placed {{ query }} in the search box's value attribute. This way, after searching, the search box will retain the user's search term.

Step 4: Implement the Search Backend Route

With the database and frontend page ready, we will now add the backend logic in main.py to handle search requests.

First, update the Post model:

models.py

# ... (previous imports) ...
from sqlalchemy.dialects.postgresql import TSVECTOR

class Post(Base):
    __tablename__ = "posts"

    # ... (other existing fields)

    # --- New field ---
    search_vector = Column(TSVECTOR, nullable=True)

Next, modify main.py:

main.py (Add new route and imports)

# ... (previous imports) ...
from fastapi import Query
from sqlalchemy import func, desc
from sqlalchemy.orm import selectinload

# ... (app, templates, dependencies get_db, get_current_user, get_admin_user remain unchanged) ...

# --- Routes ---

# ... (previous routes /, /posts, /api/posts, /admin, etc. remain unchanged) ...

# 1. Add new search route
@app.get("/search", response_class=HTMLResponse)
async def search_posts(
    request: Request,
    q: Optional[str] = Query(None),  # Get 'q' parameter from URL query string
    db: AsyncSession = Depends(get_db),
    current_user: Optional[models.User] = Depends(get_current_user)
):
    posts = []

    if q and q.strip():
        # 1. Process search term: replace spaces with '&' (AND operator)
        processed_query = " & ".join(q.strip().split())

        # 2. Build FTS query
        # func.to_tsquery('english', ...) converts the query string to tsquery type
        # models.Post.search_vector.op('@@')(...) is the FTS match operator
        # func.ts_rank(...) calculates the relevance rank
        stmt = (
            select(models.Post)
            .where(models.Post.search_vector.op('@@')(func.to_tsquery('english', processed_query)))
            .order_by(desc(func.ts_rank(
                models.Post.search_vector,
                func.to_tsquery('english', processed_query)
            )))
            .options(selectinload(models.Post.owner))  # Preload owner information
        )

        result = await db.execute(stmt)
        posts = result.scalars().all()

    return templates.TemplateResponse("search_results.html", {
        "request": request,
        "posts": posts,
        "query": q if q else "",
        "current_user": current_user
    })


# ... (subsequent routes /posts/{post_id}, /posts/{post_id}/comments, etc. remain unchanged) ...

The new GET /search route primarily does these things:

Reads the q parameter (search term), replaces spaces in it with & so that the query will match all keywords.
Uses func.to_tsquery, func.ts_rank, and op('@@') to build the specialized FTS query, and sorts the results by relevance (ts_rank) in descending order.
Renders the search_results.html template with the query results.

Step 5: Add the Search Box to the Homepage

Finally, we need to provide a search entry point for users on the forum's homepage.

Modify templates/posts.html to add the search form in the <header>.

templates/posts.html (Update header)

... (head and style remain unchanged) ...
<body>
    <header>
      <h1><a href="/posts" style="text-decoration: none; color: black;">Welcome to My Forum</a></h1>
      <div class="auth-links">
        {% if current_user %}
        <span>Welcome, {{ current_user.username }}!</span>
        {% if current_user.is_admin %}
        <a href="/admin" style="color: red; font-weight: bold;">[Admin Panel]</a>
        {% endif %}
        <a href="/logout">Logout</a>
        {% else %}
        <a href="/login">Login</a> |
        <a href="/register">Register</a>
        {% endif %}
      </div>
    </header>
    <form action="/search" method="GET" style="display: inline-block;">
      <input type="search" name="q" placeholder="Search posts..." />
      <button type="submit">Search</button>
    </form>

    ... (rest of the page remains unchanged) ...
</body>
</html>