Build a Perfect Blog with FastAPI: Full-Text Search for Posts
Wenhao Wang
Dev Intern · Leapcell

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 theget_post_by_id
route. q: str = Query(None)
: FastAPI gets the value ofq
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 atsvector
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 lineWHERE 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.