How to Use PostgreSQL Full-Text Search in FastAPI Applications

FastAPI is an emergent web framework for building APIs with Python 3.7+ based on standard Python-type hints. It is gaining popularity due to its high performance and easy-to-use features. On the other hand, PostgreSQL is a robust, open-source relational database with advanced querying capabilities. One of those is its full-text search, which is instrumental for applications that need to implement search features. In this article, we will explore how to incorporate PostgreSQL full-text search into a FastAPI application.

Getting Started

Before diving into code examples, ensure that you have Python and PostgreSQL installed in your development environment. You can verify your installations by running the following commands:

python --version
psql --version

Additionally, we will need to install FastAPI, Uvicorn (for running the server), and databases for asynchronous database interaction:

pip install fastapi uvicorn[standard] databases asyncpg

Setting Up PostgreSQL

First, let's create a PostgreSQL database. Start the PostgreSQL shell:

psql postgres

Create a database named search_demo and a user for authentication:

CREATE DATABASE search_demo;
CREATE USER api_user WITH PASSWORD 'securepassword';
GRANT ALL PRIVILEGES ON DATABASE search_demo TO api_user;

Creating a Data Model

Next, define a data model in Python with SQLAlchemy. For illustration, let's consider a simple model for articles that includes a full-text search setup:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
from sqlalchemy.sql import text

database_url = "postgresql+asyncpg://api_user:securepassword@localhost/search_demo"
metadata = MetaData()

articles = Table(
    'articles', metadata,
    Column('id', Integer, primary_key=True),
    Column('title', String, nullable=False),
    Column('content', String, nullable=False)
)

When setting up the database, ensure the searchable fields use PostgreSQL's full-text search:

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT
);

CREATE INDEX idx_gin_search ON articles USING gin(to_tsvector('english', title || ' ' || content));

Connecting FastAPI with PostgreSQL

Open the database connection in your FastAPI app:

from databases import Database
from fastapi import FastAPI

app = FastAPI()
database = Database(database_url)

@app.on_event("startup")
async def startup():
    await database.connect()

@app.on_event("shutdown")
async def shutdown():
    await database.disconnect()

Implementing Full-Text Search

Let's add the search functionality to your FastAPI application. Here is how you can use the tsvector column to perform a full-text search:

from fastapi import HTTPException, Query

@app.get("/search")
async def search_articles(q: str = Query(..., min_length=3, description="Text to search for")):
    query = "SELECT id, title, content FROM articles WHERE to_tsvector('english', title || ' ' || content) @@ plainto_tsquery(:search_query);"
    results = await database.fetch_all(query, values={"search_query": q})
    if not results:
        raise HTTPException(status_code=404, detail="No articles found")
    return results

Testing the Search Endpoint

Finally, to run your application, execute:

uvicorn myapp:app --reload

You can then navigate to http://127.0.0.1:8000/docs to interact with your API using the built-in FastAPI documentation viewer.

Try searching for a keyword within your existing articles and observe the results swiftly returned by the full-text search capabilities of PostgreSQL.

Conclusion

Integrating PostgreSQL's full-text search with FastAPI makes your Python applications capable of handling complex search queries efficiently. This combination provides a scalable approach, leveraging both technologies' strengths for enriched and high-performance web applications. Armed with these tools, you can extend your knowledge and build robust search functionalities tailored to your applications' needs.