An Introduction to Building Full-Text Search and Admin Search APIs with FastAPI: From LIKE Search to PostgreSQL FTS, SQLite FTS5, and Index Design

greeden

2 days ago

An Introduction to Building Full-Text Search and Admin Search APIs with FastAPI: From LIKE Search to PostgreSQL FTS, SQLite FTS5, and Index Design

Summary

Search APIs for admin panels and SaaS products should not be designed as simple LIKE '%keyword%' queries. They need to include search conditions, filtering, sorting, pagination, permissions, and auditing.
FastAPI makes it easy to define and validate query parameters with Query, and to commonize search conditions through dependency functions.
For small-scale systems, starting with LIKE / ILIKE and normal indexes is often enough. As data grows, PostgreSQL full-text search (tsvector / tsquery), SQLite FTS5, or an external search engine may become necessary.
In admin search, full-text search is not the only important feature. Filters such as “status,” “date range,” “tenant,” “assignee,” and “sort order” are also critical. Search APIs should be designed as features that support business operations as a whole, not just a search box.
This article explains FastAPI search API design in the order of basic policy → query parameter design → SQLAlchemy search → PostgreSQL full-text search → SQLite FTS5 → permissions, auditing, and testing.

Who Will Benefit from This Article

Individual Developers and Learners

This article is for people who can already build CRUD APIs with FastAPI and are thinking, “Next, I want to add a search box to a list screen.”
At first, an implementation like name LIKE '%q%' may seem enough. But as the amount of data grows, search speed and increasing conditions can quickly become difficult to manage. This article shows a step-by-step path from simple search to future full-text search.

Backend Engineers in Small Teams

This article is for engineers building user search, order search, inquiry search, audit log search, and similar features for internal admin panels or SaaS admin screens.
If search conditions differ from screen to screen and it is unclear “which parameters are available” or “how pagination and sorting should be standardized,” this can be a starting point for creating a shared design.

SaaS Development Teams and Startups

This article is for teams whose number of tenants or data volume has increased and whose simple LIKE searches are becoming slow.
It helps organize how to stabilize the FastAPI-side API design while considering PostgreSQL full-text search, SQLite FTS5, and future migration to OpenSearch / Elasticsearch-like systems. Search directly affects the efficiency of CS, sales, accounting, and operations teams, so it is worth treating it as infrastructure early.

Accessibility Evaluation

The article begins with a summary, then moves through target readers, basic policies, implementation examples, operational design, and testing.
Terms such as “LIKE,” “full-text search,” “index,” and “ranking” are briefly explained when first introduced.
Code examples are split by small responsibilities, with each block covering one idea.
The article also includes the viewpoint of admin screen users, considering both accessibility and operational efficiency, such as ease of finding search results, understandable error states, and retention of filter conditions.
The target level is roughly equivalent to WCAG AA.

1. A Search API Is Not a “Convenience Feature,” but Business Infrastructure

A search API may look like a small feature at first.
For example, if you only need to add a search box to a user list and filter by email address or name, the implementation is not very difficult.

However, real-world requirements quickly expand:

Search by email address, name, and company name
Filter by status
Specify a date range by creation date or last login date
Separate search results by tenant
Search by values in related tables
Export search results as CSV
Show only data the user has permission to access
Improve slow search performance

In other words, a search API is not just a q parameter. It is infrastructure that supports business operations.
In internal admin panels especially, search usability directly affects work time when CS staff handle inquiries, accounting staff find billing targets, or operations staff investigate suspicious activity.

FastAPI allows declarative typing and validation of query parameters. The official documentation explains that FastAPI can distinguish path parameters and query parameters by name, and that query parameters can have types and default values.
Reference: FastAPI Query Parameters

2. Types of Search: Exact Match, Partial Match, Full-Text Search, and Faceted Search

When designing a search API, it is easier to organize the design by first separating “what kind of search” you want to perform.

2.1 Exact Match Search

This searches for values that match exactly, such as IDs, email addresses, order numbers, or external payment IDs.

Example:

email = "user@example.com"
order_code = "ORD-2026-0001"

This type of search works well with normal B-tree indexes and is usually fast.

2.2 Partial Match Search

This searches for values that contain part of a name or title.

Example:

name LIKE '%Yamada%'
title ILIKE '%fastapi%'

It is convenient for small-scale systems, but searches with % at the beginning often cannot use normal indexes effectively and tend to become slow as data grows.

2.3 Full-Text Search

This searches long text or documents based on words and related terms.
PostgreSQL has full-text search features and can handle documents and search queries using tsvector and tsquery. The official documentation explains full-text search as a feature for searching natural-language documents and, when needed, sorting them by relevance.
Reference: PostgreSQL Full Text Search

2.4 Faceted Search

This filters search results by status, category, assignee, date range, and similar conditions.

Example:

q = "billing"
status = "open"
assignee_id = 3
created_from = "2026-05-01"
created_to = "2026-05-31"

In admin panels, this faceted search can be more important than full-text search.
The usability of an admin screen is greatly affected not only by the search box, but also by how filtering conditions are designed.

3. Designing Search Parameters with FastAPI

First, organize the query parameters that serve as the entry point for the search API.

3.1 Minimal Search API

from fastapi import APIRouter, Query

router = APIRouter(prefix="/admin/users", tags=["admin-users"])

@router.get("")
def search_users(
    q: str | None = Query(default=None, description="Search by name or email address"),
    limit: int = Query(default=50, ge=1, le=200),
    offset: int = Query(default=0, ge=0),
):
    return {
        "items": [],
        "meta": {
            "q": q,
            "limit": limit,
            "offset": offset,
        },
    }

Here, q, limit, and offset are defined.
By using FastAPI’s Query, descriptions, minimum values, maximum values, and other metadata can also be reflected in OpenAPI. Since search APIs are closely connected to admin panel frontends, having visible conditions in OpenAPI is a major advantage.

3.2 Model Conditions When They Increase

As search conditions increase, router arguments become long.
In that case, creating a search condition object as a dependency function makes the code easier to read.

from dataclasses import dataclass
from typing import Literal
from fastapi import Depends, Query

@dataclass
class UserSearchParams:
    q: str | None
    status: str | None
    sort: str
    limit: int
    offset: int

def get_user_search_params(
    q: str | None = Query(default=None, description="Search by name or email address"),
    status: Literal["active", "suspended", "invited"] | None = Query(default=None),
    sort: Literal["created_desc", "created_asc", "email_asc"] = Query(default="created_desc"),
    limit: int = Query(default=50, ge=1, le=200),
    offset: int = Query(default=0, ge=0),
) -> UserSearchParams:
    return UserSearchParams(
        q=q,
        status=status,
        sort=sort,
        limit=limit,
        offset=offset,
    )

The router becomes cleaner.

@router.get("")
def search_users(
    params: UserSearchParams = Depends(get_user_search_params),
):
    return {"items": [], "meta": params.__dict__}

With this structure, the same search conditions can be reused more easily for CSV export and admin list screens.

4. Standardize Pagination and Sorting from the Beginning

Pagination and sorting often become painful later in search APIs.

4.1 offset/limit Method

For admin panels, offset / limit is easy to understand.

GET /admin/users?q=yamada&limit=50&offset=0

The advantage is that it is easy to build page-number-based UIs.
The disadvantage is that when the data volume becomes very large, deep pages can become slow.

4.2 cursor Method

For feeds or very large datasets, the cursor method is suitable.

GET /admin/audit-logs?cursor=eyJpZCI6...

However, if the admin panel needs a UI like “go to page 3,” offset may still be easier to handle.

4.3 Do Not Allow Free-Form Sort Keys

It is dangerous to insert sort conditions directly into SQL.
Always select from an allowlist.

SORT_MAP = {
    "created_desc": ("created_at", "desc"),
    "created_asc": ("created_at", "asc"),
    "email_asc": ("email", "asc"),
}

Do not put the user-provided sort value directly into ORDER BY. It is safer to convert it through an internal mapping table.

5. Building Basic Search with SQLAlchemy

In SQLAlchemy 2.0, the select() style is the main approach. The official documentation’s ORM Querying Guide is also based on the 2.0-style select().
Reference: SQLAlchemy ORM Querying Guide

5.1 Model Example

from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy import Integer, String, DateTime
from datetime import datetime

from app.db.base import Base

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    tenant_id: Mapped[int] = mapped_column(Integer, index=True, nullable=False)
    email: Mapped[str] = mapped_column(String(255), index=True, nullable=False)
    name: Mapped[str] = mapped_column(String(255), index=True, nullable=False)
    status: Mapped[str] = mapped_column(String(50), index=True, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)

5.2 Building the Search Query

from sqlalchemy import select, or_
from sqlalchemy.orm import Session

def search_users_query(
    db: Session,
    tenant_id: int,
    params: UserSearchParams,
):
    stmt = select(User).where(User.tenant_id == tenant_id)

    if params.q:
        keyword = f"%{params.q}%"
        stmt = stmt.where(
            or_(
                User.email.ilike(keyword),
                User.name.ilike(keyword),
            )
        )

    if params.status:
        stmt = stmt.where(User.status == params.status)

    if params.sort == "created_asc":
        stmt = stmt.order_by(User.created_at.asc())
    elif params.sort == "email_asc":
        stmt = stmt.order_by(User.email.asc())
    else:
        stmt = stmt.order_by(User.created_at.desc())

    stmt = stmt.limit(params.limit).offset(params.offset)
    return db.execute(stmt).scalars().all()

The key point is to always include tenant_id in the conditions.
In multi-tenant environments, search APIs are especially prone to boundary leaks, so the tenant condition should always be included at the beginning of the search condition design.

6. Understand the Limits of `LIKE` / `ILIKE`

LIKE and ILIKE are convenient, but they are not万能.

Suitable Cases

Small datasets
Simple admin panel search
Partial match on email addresses or names
List screens without strict speed requirements

Cases Where They Become Difficult

Tables with hundreds of thousands of rows or more
Search over long body text
Need to sort by relevance
Natural-language search in Japanese or multiple languages
Search across multiple columns

In particular, searches with a leading wildcard such as %keyword% are difficult for normal B-tree indexes to optimize.
Even if ILIKE is enough at first, once data grows, you need to consider full-text search or a dedicated search platform.

7. PostgreSQL Full-Text Search Concepts

PostgreSQL full-text search converts documents into tsvector and search terms into tsquery, then matches them. The official documentation explains that full-text search combines functions for creating tsvector from documents, creating tsquery from user queries, and ranking results by relevance.
Reference: PostgreSQL Controlling Text Search

7.1 Basic Image

SELECT *
FROM articles
WHERE to_tsvector('english', title || ' ' || body)
      @@ plainto_tsquery('english', 'fastapi search');

Here, title and body are the search targets.
plainto_tsquery is used to convert natural search strings entered by users into search queries.

7.2 Sorting by Relevance

SELECT *,
       ts_rank(
           to_tsvector('english', title || ' ' || body),
           plainto_tsquery('english', 'fastapi search')
       ) AS rank
FROM articles
WHERE to_tsvector('english', title || ' ' || body)
      @@ plainto_tsquery('english', 'fastapi search')
ORDER BY rank DESC;

A major advantage of full-text search is that it can handle not only whether a term is included, but also how relevant the result appears to be.

8. Using PostgreSQL Full-Text Search from SQLAlchemy

When calling PostgreSQL functions from SQLAlchemy, use func.

from sqlalchemy import select, func

def search_articles_fulltext(db: Session, tenant_id: int, q: str):
    document = func.to_tsvector(
        "english",
        Article.title + " " + Article.body,
    )
    query = func.plainto_tsquery("english", q)
    rank = func.ts_rank(document, query).label("rank")

    stmt = (
        select(Article, rank)
        .where(Article.tenant_id == tenant_id)
        .where(document.op("@@")(query))
        .order_by(rank.desc())
        .limit(50)
    )

    return db.execute(stmt).all()

This example is intended to show the concept.
In real-world systems, dynamically calculating to_tsvector every time can become heavy, so generated columns, dedicated columns, and GIN indexes should be considered. The official PostgreSQL documentation also explains that full-text search is built by combining document, query, and ranking functions.
Reference: PostgreSQL Full Text Search

9. SQLite FTS5 Concepts

SQLite is often used for learning, small applications, and local tools.
SQLite includes a full-text search extension called FTS5. The official documentation explains that FTS5 is a virtual table module that provides full-text search functionality to applications.
Reference: SQLite FTS5 Extension

9.1 Example FTS5 Table

CREATE VIRTUAL TABLE articles_fts USING fts5(
    title,
    body
);

9.2 Search Example

SELECT rowid, title
FROM articles_fts
WHERE articles_fts MATCH 'fastapi';

SQLite FTS5 is useful when you want lightweight full-text search inside the application.
However, for multi-tenant SaaS or large-scale search, PostgreSQL or an external search engine may be easier to operate.

10. Search APIs and Permissions: Return Only What the User Is Allowed to See

Search APIs are places where permission leaks easily occur.
You must avoid situations where detailed APIs check permissions, but search list APIs leak data from other tenants because a condition is missing.

10.1 Always Add Scope Conditions First

stmt = select(Project).where(Project.tenant_id == tenant_id)

Add search conditions after this.
Conditions related to permissions, such as tenant_id, owner_id, visibility, and role, should be treated as prerequisites rather than ordinary search filters.

10.2 Be Careful About Scope Even in Admin Search

In internal admin panels, superadmins may be able to search across all tenants.
Even then, searchable ranges need to differ between normal admins, CS staff, accounting staff, and others.

Example:

CS staff: assigned tenants only
Accounting staff: billing-related data only
superadmin: cross-tenant search
Tenant admin: own tenant only

Because search APIs are convenient, they can retrieve data broadly.
It is safer to think about permissions even more strictly than with normal detail APIs.

11. Should Search Conditions Be Recorded in Audit Logs?

Search is a read operation, so logging every search can create a large volume of logs.
However, the following searches are worth auditing:

Searches involving personal information
Searches involving billing information
Audit log searches
Cross-tenant searches
Searches that lead to CSV export
Searches that precede fraud investigations or admin operations

If you record audit logs, the following information is useful at minimum:

actor_id
tenant_id
action = "user.search"
query_params
result_count
request_id
created_at

However, search terms themselves may contain personal or confidential information.
It is recommended to consider masking and retention periods for logged values.

12. Share Search Conditions with CSV Export

CSV export, covered in the previous article, works very well with search APIs.
In admin panels, the requirement “export CSV using the current search conditions” almost always appears.

Therefore, it is useful to make search conditions reusable.

@router.get("")
def search_users(
    params: UserSearchParams = Depends(get_user_search_params),
):
    ...

@router.get("/export")
def export_users(
    params: UserSearchParams = Depends(get_user_search_params),
):
    ...

By using the same get_user_search_params, you can prevent condition mismatches between list display and CSV export.
Also decide whether CSV export should ignore limit or use a separate maximum row limit.

13. Search Result Response Design

In admin panel search, returning only items is not enough. Including search conditions and total count makes the frontend easier to handle.

from pydantic import BaseModel

class PageMeta(BaseModel):
    total: int
    limit: int
    offset: int

class SearchResponse(BaseModel):
    items: list[dict]
    meta: PageMeta

Response example:

{
  "items": [
    {
      "id": 1,
      "email": "a@example.com",
      "status": "active"
    }
  ],
  "meta": {
    "total": 123,
    "limit": 50,
    "offset": 0
  }
}

Calculating total every time can be expensive.
Depending on the search type, you may omit total, return an approximate value, or calculate it only on the first page.

14. Order of Performance Improvements

When search is slow, it is better to check the following before immediately introducing a full-text search engine.

Are there indexes for the necessary WHERE conditions?
Are strong filtering conditions such as tenant_id and status applied effectively first?
Are there unnecessary JOINs?
Are unnecessary columns being returned in the list?
Is COUNT(*) becoming heavy?
Is LIKE '%keyword%' the bottleneck?
Consider PostgreSQL FTS or SQLite FTS5.
If that is still not enough, consider an external search engine.

PostgreSQL full-text search and SQLite FTS5 are powerful, but often performance improves just by reviewing normal query design and indexes first.
Because search APIs involve DB design, permissions, and response design, improving them step by step is realistic.

15. When to Move to an External Search Engine

As data volume and search requirements grow, you may consider external search engines such as OpenSearch, Elasticsearch, Meilisearch, or Typesense.

External search engines are suitable for cases such as:

Fast search across multiple fields is required
Fine-tuned relevance control is needed
Suggestions or autocomplete are needed
Typo tolerance is needed
DB search has reached its limit with large amounts of data
You want to create search-specific rankings

However, introducing an external search engine increases synchronization and consistency issues.
You also need to consider delays between DB writes and search index updates, reindexing, and recovery during failures.

Rather than introducing an external search engine from the beginning, it is more practical to first stabilize the API contract on the FastAPI side and make the internal implementation replaceable later.

16. Stabilize the API Contract: Hide the Internal Search Method

The search method may change from ILIKE at first, to PostgreSQL full-text search later, and eventually to an external search engine.
If API responses or parameters change frequently, the frontend and users will have trouble.

Therefore, it is important to stabilize the outside of the search API and replace only the inside.

class UserSearchService:
    def search(self, tenant_id: int, params: UserSearchParams):
        # First: SQL LIKE
        # Later: PostgreSQL FTS
        # Future: external search engine
        ...

If the router only calls UserSearchService, changes to the search method are less likely to leak to the frontend.

17. Testing Policy: Search APIs Must Preserve Combinations of Conditions

For search API tests, combinations of conditions are important, not just single conditions.

At minimum, the following tests provide confidence:

A list is returned without q
Name search works with q
Email search works with q
Filtering by status works
Results do not cross the tenant_id boundary
Unauthorized data is not returned
Exceeding the maximum limit returns 422
Invalid sort returns 422
CSV export and list search use the same conditions

With FastAPI validation using Query, invalid limit or sort values can automatically be handled as 422 responses.
Testing this behavior helps maintain the safety of search conditions.

18. Common Failure Patterns

18.1 Writing Search Conditions Separately in Each Router

If the meaning of q, status, and sort changes from screen to screen, both frontend and backend become confused.
It is recommended to collect search conditions into dependency functions or dedicated models.

18.2 Inserting `sort` Directly into SQL

This can lead to SQL injection or unexpected queries.
Always convert it from an allowlist to internal columns.

18.3 Forgetting the `tenant_id` Condition

This is the most dangerous failure in multi-tenant environments.
Search APIs retrieve data broadly, so they require even more care than detail APIs.

18.4 Introducing an External Search Engine Too Early

Search engines are useful, but they also bring synchronization and operational costs.
First, check whether DB search, indexes, PostgreSQL FTS, or SQLite FTS5 are enough.

18.5 Overlooking the Cost of `COUNT(*)`

Returning an accurate total for every list can become heavy depending on data volume.
Consider approximate counts, capped counts, or calculating only on the first page when needed.

19. Roadmap by Reader Type

Individual Developers and Learners

Build a search API with q, limit, and offset.
Add validation with Query.
Implement LIKE / ILIKE search with SQLAlchemy.
Collect search conditions into a dependency function.
Try indexes and full-text search when data grows.

Engineers in Small Teams

Inventory search conditions in the admin panel.
Commonize search conditions between list search and CSV export.
Use an allowlist for sort keys.
Build permission and tenant boundaries into the search service layer.
Decide audit and logging policies for search conditions and result counts.

SaaS Development Teams and Startups

Redesign search APIs as business infrastructure.
Identify where PostgreSQL full-text search or SQLite FTS5 should apply.
Create a service layer that can migrate easily to an external search engine.
Measure search latency, search count, and zero-result rate.
Grow the search infrastructure together with permissions, auditing, CSV export, and admin UX.

Reference Links

FastAPI
SQLAlchemy
- SQLAlchemy Documentation
- ORM Querying Guide
PostgreSQL
- Full Text Search
- Controlling Text Search
SQLite
- SQLite FTS5 Extension

Conclusion

Search APIs in FastAPI become more stable when designed not only as q plus LIKE, but also with filtering, sorting, pagination, permissions, auditing, and CSV export.
For small-scale systems, starting with LIKE / ILIKE and normal indexes is enough. However, if long-text search or relevance-based sorting becomes necessary, PostgreSQL full-text search or SQLite FTS5 is worth considering.
Collect search conditions into dependency functions or dedicated models, and share them between list APIs and CSV export to avoid condition mismatches.
In multi-tenant environments, it is important to always include tenant_id and permission conditions in search APIs. Because search can expose a wide range of data, boundary leaks require even more attention than in detail APIs.
Even if the search implementation later changes from ILIKE to PostgreSQL FTS or an external search engine, a stable API contract keeps the impact on frontend and operations small.

Natural follow-up articles include “Designing CS-Oriented Inquiry Handling APIs with FastAPI” and “Designing Notification and Email Sending Infrastructure with FastAPI.”

An Introduction to Building Full-Text Search and Admin Search APIs with FastAPI: From LIKE Search to PostgreSQL FTS, SQLite FTS5, and Index Design

Summary

Who Will Benefit from This Article

Individual Developers and Learners

Backend Engineers in Small Teams

SaaS Development Teams and Startups

Accessibility Evaluation

1. A Search API Is Not a “Convenience Feature,” but Business Infrastructure

2. Types of Search: Exact Match, Partial Match, Full-Text Search, and Faceted Search

2.1 Exact Match Search

2.2 Partial Match Search

2.3 Full-Text Search

2.4 Faceted Search

3. Designing Search Parameters with FastAPI

3.1 Minimal Search API

3.2 Model Conditions When They Increase

4. Standardize Pagination and Sorting from the Beginning

4.1 offset/limit Method

4.2 cursor Method

4.3 Do Not Allow Free-Form Sort Keys

5. Building Basic Search with SQLAlchemy

5.1 Model Example

5.2 Building the Search Query

6. Understand the Limits of LIKE / ILIKE

Suitable Cases

Cases Where They Become Difficult

7. PostgreSQL Full-Text Search Concepts

7.1 Basic Image

7.2 Sorting by Relevance

8. Using PostgreSQL Full-Text Search from SQLAlchemy

9. SQLite FTS5 Concepts

9.1 Example FTS5 Table

9.2 Search Example

10. Search APIs and Permissions: Return Only What the User Is Allowed to See

10.1 Always Add Scope Conditions First

10.2 Be Careful About Scope Even in Admin Search

11. Should Search Conditions Be Recorded in Audit Logs?

12. Share Search Conditions with CSV Export

13. Search Result Response Design

14. Order of Performance Improvements

15. When to Move to an External Search Engine

16. Stabilize the API Contract: Hide the Internal Search Method

17. Testing Policy: Search APIs Must Preserve Combinations of Conditions

18. Common Failure Patterns

18.1 Writing Search Conditions Separately in Each Router

18.2 Inserting sort Directly into SQL

18.3 Forgetting the tenant_id Condition

18.4 Introducing an External Search Engine Too Early

18.5 Overlooking the Cost of COUNT(*)

19. Roadmap by Reader Type

Individual Developers and Learners

Engineers in Small Teams

SaaS Development Teams and Startups

Reference Links

Conclusion

Share this:

6. Understand the Limits of `LIKE` / `ILIKE`

18.2 Inserting `sort` Directly into SQL

18.3 Forgetting the `tenant_id` Condition

18.5 Overlooking the Cost of `COUNT(*)`