How to Add Full-Text Search to Your Next.js SaaS -- PostgreSQL Built-In, No Algolia Required

The Problem Most SaaS Founders Hit Around Month 3

You have 500 users. Your app has a growing list of records -- projects, contacts, notes, products -- and people are starting to email you: "I can't find the thing I created last week."

The reflex is to reach for Algolia, Typesense, or Meilisearch. You add another service, another API key, another monthly bill ($35 to $100 before you have meaningful search volume), and another sync job to keep the index up to date.

Here is what most tutorials do not tell you: PostgreSQL -- the database you are already running on Neon -- has full-text search built in. It handles typos, ranks results by relevance, and runs in milliseconds on realistic SaaS data sizes. You do not need a separate service until you are at a scale most SaaS products never reach.

How PostgreSQL Full-Text Search Works

PostgreSQL stores a processed, indexed version of your text in a tsvector column. When a user searches, you convert the query to a tsquery and PostgreSQL matches against the index.

The two key functions:

• to_tsvector(lang, text) -- converts a string to a searchable vector, removing stopwords and stemming words ("running" matches "run")
• plainto_tsquery(lang, query) -- converts raw user input to a query that matches the vector safely

A generated column keeps the index current automatically -- no sync job needed.

Adding the Search Column to Your Schema

Add a search_vector generated column to the table you want to search. Here is the SQL migration for a posts table:

ALTER TABLE posts
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector(english, coalesce(title, ) ||   || coalesce(content, ))
  ) STORED;
 
CREATE INDEX posts_search_idx ON posts USING GIN (search_vector);

Generate and apply this with the standard Drizzle commands:

npx drizzle-kit generate
npx drizzle-kit migrate

The GIN index builds in the background on Neon and does not block reads.

The Repository Query

With the index in place, the query is a single .where() clause:

// modules/post/post.repo.ts
async function searchPosts(query: string, authorId: string) {
  return db
    .select()
    .from(postTable)
    .where(
      and(
        eq(postTable.authorId, authorId),
        sql`search_vector @@ plainto_tsquery(english, ${query})`
      )
    )
    .orderBy(
      sql`ts_rank(search_vector, plainto_tsquery(english, ${query})) DESC`
    )
    .limit(20);
}

plainto_tsquery is forgiving with raw user input -- it will not throw on special characters the way to_tsquery does. ts_rank orders results by relevance so exact matches come first.

Wiring It Into a Next.js API Route

The route follows the same thin pattern as every other endpoint in the boilerplate -- validate, call the service, respond:

// app/api/posts/search/route.ts
export async function GET(req: Request) {
  try {
    const user = await getUserFromRequest(req);
    const { searchParams } = new URL(req.url);
    const result = SearchQuerySchema.safeParse({ q: searchParams.get(q) });
    if (!result.success) throw new HttpError(400, Invalid query);
    const posts = await postService.search(result.data.q, user.id);
    return Response.json(posts);
  } catch (error: unknown) {
    return handleError(error);
  }
}

On the client, a debounced input calls GET /api/posts/search?q=... via SWR and updates the list in real time. No extra state management needed.

When to Use PostgreSQL Search vs a Dedicated Service

This is the decision most tutorials skip. Here is a straightforward framework:

Use PostgreSQL search when:

• Your data is already in Neon and you want zero extra services to maintain
• You are searching 1-3 columns in one or two tables
• You are early-stage: under 100,000 records per tenant
• Relevance ranking and stemming ("manage" matches "management") are sufficient

Consider a dedicated search service when:

• You need faceted filtering across many dimensions at once
• You want semantic search -- finding results by meaning, not just keyword match
• You are searching across many loosely related tables that are expensive to join
• Write performance is suffering because GIN index updates are blocking at high volume

For most SaaS products, that second case does not arrive until well after you have product-market fit and revenue to justify the extra service. Start with PostgreSQL, keep the search logic inside your repository layer, and migrate the implementation later without changing the API.

What You Get Without Building It From Scratch

The Next.js SaaS Boilerplate already includes the Drizzle ORM setup, the auth layer that scopes queries to the signed-in user, and the API route pattern shown above. Adding search is a migration, a repository method, and one new route -- a few hours of focused work, not a sprint.

The database setup docs walk through schema conventions and how to run migrations on Neon.

What to Do Next

If your users have asked for search -- or you know they will -- add it this week. The schema change is non-destructive, the index builds without downtime on Neon, and the query pattern above handles real SaaS workloads without a new monthly bill.

Get the boilerplate and ship search before your users ask for it: boilerplate.iteam-company.com.