Full-text search in Next.js App Router with Drizzle ORM and Neon DB

For small tables, ILIKE '%query%' works fine. Once your table grows past a few thousand rows, or you want ranked results and word stemming, PostgreSQL full-text search (tsvector) is the right tool. Neon DB supports it natively -- no Elasticsearch, no Algolia required.

This post shows the complete pattern: a Drizzle ORM query using plainto_tsquery, a GIN expression index for speed, a service layer validation, and a URL-driven server component that composes with pagination.

When ILIKE is enough

For tables under ~10k rows with no ranking requirement, ILIKE is simpler and perfectly fine:

.where(ilike(postTable.title, `%${query}%`))

Switch to tsvector when the table is large, you want word stemming ("running" matches "run"), or you need results ordered by relevance.

The Drizzle query

No schema changes required for a first pass -- call to_tsvector and plainto_tsquery inline using Drizzle's sql template. The @@ operator returns true when the document matches. ts_rank returns a float you can order by.

// modules/post/post.repo.ts
import { db } from '@/db/drizzle';
import { postTable } from '@/db/schema';
import { sql, desc, and, eq } from 'drizzle-orm';
 
const ftsVector = (table: typeof postTable) =>
  sql`to_tsvector('english', coalesce(${table.title}, '') || ' ' || coalesce(${table.description}, ''))`;
 
const ftsQuery = (q: string) => sql`plainto_tsquery('english', ${q})`;
 
export const postRepo = {
  async search(query: string, limit = 20) {
    return db
      .select({
        id: postTable.id,
        title: postTable.title,
        slug: postTable.slug,
        description: postTable.description,
        rank: sql<number>`ts_rank(${ftsVector(postTable)}, ${ftsQuery(query)})`,
      })
      .from(postTable)
      .where(
        and(
          eq(postTable.published, true),
          sql`${ftsVector(postTable)} @@ ${ftsQuery(query)}`
        )
      )
      .orderBy(desc(sql`ts_rank(${ftsVector(postTable)}, ${ftsQuery(query)})`));
      .limit(limit);
  },
};

plainto_tsquery accepts raw user input safely -- it treats the string as a phrase search without requiring tsquery syntax. Drizzle parameterizes ${query} automatically, so no manual sanitization is needed.

Adding a GIN index

Without an index, Postgres scans the full table on every search. Add an expression GIN index via a raw SQL migration file:

-- drizzle/migrations/0005_search_index.sql
CREATE INDEX IF NOT EXISTS posts_fts_idx
  ON posts
  USING GIN (
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(description, ''))
  );

Apply it with npm run db:migrate. The index expression must match the expression in the query exactly for Postgres to use it.

Service layer

Validate the query length before hitting the DB. Two characters is a practical minimum that avoids noise.

// modules/post/post.service.ts
import { postRepo } from './post.repo';
import { HttpError } from '@/lib/errors';
 
export const postService = {
  async search(query: string) {
    const trimmed = query.trim();
    if (trimmed.length < 2) {
      throw new HttpError(400, 'Query must be at least 2 characters');
    }
    return postRepo.search(trimmed);
  },
};

Server component: URL-driven search

Read ?q= from searchParams. When the param is present, call search; otherwise fall back to the default listing.

// app/(main)/blog/page.tsx
import { postService } from '@/modules/post';
import { BlogSearch } from '@/components/blog/BlogSearch';
import { BlogList } from '@/components/blog/BlogList';
 
interface Props {
  searchParams: Promise<{ q?: string }>;
}
 
export default async function BlogPage({ searchParams }: Props) {
  const { q } = await searchParams;
 
  const posts = q
    ? await postService.search(q).catch(() => [])
    : await postService.getPublished();
 
  return (
    <div className="max-w-3xl mx-auto py-12 space-y-8">
      <BlogSearch defaultValue={q} />
      <BlogList posts={posts} />
    </div>
  );
}

The .catch(() => []) swallows the HttpError(400) thrown for queries shorter than 2 characters, so partial typing does not crash the page.

Search input component

The input pushes ?q= to the URL on change. The server re-renders with the new query -- no client-side filtering, no state to sync.

// components/blog/BlogSearch.tsx
'use client';
 
import { useRouter, usePathname } from 'next/navigation';
import { useCallback, useRef } from 'react';
import { Input } from '@/components/ui/input';
 
interface Props {
  defaultValue?: string;
}
 
export function BlogSearch({ defaultValue }: Props) {
  const router = useRouter();
  const pathname = usePathname();
  const timer = useRef<ReturnType<typeof setTimeout>>(null);
 
  const handleChange = useCallback(
    (e: React.ChangeEvent<HTMLInputElement>) => {
      const value = e.target.value.trim();
      clearTimeout(timer.current ?? undefined);
      timer.current = setTimeout(() => {
        const params = new URLSearchParams();
        if (value) params.set('q', value);
        router.push(`${pathname}?${params.toString()}`);
      }, 300);
    },
    [pathname, router]
  );
 
  return (
    <Input
      type="search"
      placeholder="Search posts..."
      defaultValue={defaultValue}
      onChange={handleChange}
      className="max-w-sm"
    />
  );
}

The 300ms debounce prevents a navigation on every keystroke. The defaultValue (not value) keeps the input uncontrolled so it does not reset on server re-renders.

Combining with pagination

Search and pagination share the URL: ?q=drizzle&page=2. When the query changes, reset to page 1 by omitting the page param from the search handler:

const params = new URLSearchParams();
if (value) {
  params.set('q', value);
  // page is intentionally omitted -- results start at 1
}

In the server component, read both q and page, pass them to the service, and the repo applies OFFSET accordingly. The pagination component passes the current q through as a hidden param on each page link so the query is preserved across pages.

Takeaway

PostgreSQL full-text search through Neon DB handles most SaaS search requirements without a dedicated service. The pattern -- plainto_tsquery in a Drizzle sql template, a GIN expression index, and a URL param driving a server component -- is production-ready and requires no new infrastructure. Add the index, wire the ?q= param, ship.