Using Postgres Full-Text Search on a Next.JS Fullstack App

Source: Dev.to

Table of Contents

1. Assumptions
2. Create a Full‑Text Search Index (Neon)
3. Connect Drizzle to Neon
4. Search API Route (/api/search)
5. Install Shadcn UI components
6. Search Page UI (/search)
7. How It Works (summary)
8. Article Search Component (Next.js)

Assumptions

• The Next.js full‑stack app is complete.
• A PostgreSQL database (hosted on Neon) is already attached.
• The articles table contains data (title, summary, content, etc.) that we want to search.

Create a Full‑Text Search Index (Neon)

Run the following SQL in the Neon SQL Editor (or any PostgreSQL client).
It creates a GIN index that speeds up full‑text queries and enables ranking by relevance.

CREATE INDEX articles_search_idx
  ON articles
  USING GIN (
    to_tsvector(
      'english',
      title || ' ' || summary || ' ' || content
    )
  );

You can rename articles_search_idx to anything you like.

Connect Drizzle to Neon

If the database connection isn’t already set up, add the following file:

📁 db/index.ts

import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";
import * as schema from "./schema";

const sql = neon(process.env.DATABASE_URL!);   // The rest of the app should already import `db` from this module.

Search API Route (/api/search)

Create the API endpoint that receives a query string, runs a full‑text search, ranks results, and returns the top 20 articles.

📁 app/api/search/route.ts

import { NextResponse } from "next/server";
import { db } from "@/db";
import { sql } from "drizzle-orm";

export async function GET(req: Request) {
  const { searchParams } = new URL(req.url);
  const query = searchParams.get("q");

  if (!query) {
    return NextResponse.json([]);
  }

  const results = await db.execute(sql`
    SELECT
      id,
      title,
      slug,
      summary,
      image_url,
      created_at,
      ts_rank(
        to_tsvector('english', title || ' ' || COALESCE(summary, '') || ' ' || content),
        plainto_tsquery('english', ${query})
      ) AS rank
    FROM articles
    WHERE published = true
      AND to_tsvector(
        'english',
        title || ' ' || COALESCE(summary, '') || ' ' || content
      ) @@ plainto_tsquery('english', ${query})
    ORDER BY rank DESC
    LIMIT 20
  `);

  return NextResponse.json(results.rows);
}

What happens here?

1. The endpoint reads ?q= from the request.
2. It builds a plainto_tsquery from the user input.
3. It searches the concatenated title + summary + content vector.
4. ts_rank scores each row; results are ordered by that score.

Install Shadcn UI Components

The UI uses Shadcn + Tailwind. Run the commands once (they add the component files to src/components/ui).

npx shadcn@latest init
npx shadcn@latest add input
npx shadcn@latest add button
npx shadcn@latest add card
npx shadcn@latest add separator

Search Page UI (/search)

Create a client‑side page that lets users type a query, calls the API, and displays the ranked results.

📁 app/search/page.tsx

"use client";

import { useState } from "react";
import Link from "next/link";

import { Input } from "@/components/ui/input";
import { Button } from "@/components/ui/button";
import { Card, CardContent } from "@/components/ui/card";
import { Separator } from "@/components/ui/separator";

type Article = {
  id: number;
  title: string;
  slug: string;
  summary: string | null;
  created_at: string;
};

export default function ArticleSearchPage() {
  const [query, setQuery] = useState("");
  const [results, setResults] = useState([]);
  const [loading, setLoading] = useState(false);

  async function handleSearch() {
    if (!query.trim()) return;

    setLoading(true);
    const res = await fetch(`/api/search?q=${encodeURIComponent(query)}`);
    const data = await res.json();
    setResults(data);
    setLoading(false);
  }

  return (
    <div className="max-w-2xl mx-auto p-4">
      {/* Page Header */}
      <h1 className="text-2xl font-bold">Search Articles</h1>
      <p className="text-muted-foreground mb-4">
        Find articles by title, summary, or content
      </p>

      {/* Search Input */}
      <div className="flex gap-2 mb-4">
        <Input
          value={query}
          onChange={(e) => setQuery(e.target.value)}
          placeholder="Search articles..."
          className="flex-1"
        />
        <Button onClick={handleSearch} disabled={loading}>
          {loading ? "Searching…" : "Search"}
        </Button>
      </div>

      {/* Results */}
      <div className="space-y-4">
        {results.length === 0 && !loading && query && (
          <p>No articles found.</p>
        )}

        {results.map((article) => (
          <Card key={article.id}>
            <CardContent className="pt-4">
              <Link href={`/articles/${article.slug}`} className="text-lg font-medium hover:underline">
                {article.title}
              </Link>
              {article.summary && (
                <p className="text-sm text-muted-foreground mt-1">
                  {article.summary}
                </p>
              )}
              <p className="text-xs text-muted-foreground mt-2">
                {new Date(article.created_at).toDateString()}
              </p>
            </CardContent>
          </Card>
        ))}
      </div>
    </div>
  );
}

How It Works (summary)

🎉 Done!

You now have a performant, relevance‑sorted full‑text search integrated into your Next.js blog, powered by PostgreSQL, Neon, Drizzle ORM, and styled with Shadcn UI. Feel free to tweak the ranking formula, add pagination, or expand the searchable fields as needed. Happy coding!

Article Search Component (Next.js)

Overview

• User flow

  1. User types a query → the frontend updates query.
  2. Clicking Search runs handleSearch(), which calls /api/search?q=….
  3. The backend performs a PostgreSQL full‑text search.
  4. Results are returned as JSON and the UI updates.

• Optional live‑search – start searching as the user types (similar to Google).

Front‑end Code (app/articles/search/page.tsx)

"use client";

import { useState } from "react";
import Link from "next/link";

import { Input } from "@/components/ui/input";
import { Button } from "@/components/ui/button";
import { Card, CardContent } from "@/components/ui/card";
import { Separator } from "@/components/ui/separator";

type Article = {
  id: number;
  title: string;
  slug: string;
  summary: string | null;
  created_at: string;
};

export default function ArticleSearchPage() {
  const [query, setQuery] = useState("");
  const [results, setResults] = useState([]);
  const [loading, setLoading] = useState(false);

  async function handleSearch() {
    if (!query.trim()) return;

    setLoading(true);
    const res = await fetch(`/api/search?q=${encodeURIComponent(query)}`);
    const data = await res.json();
    setResults(data);
    setLoading(false);
  }

  return (
    <div className="max-w-2xl mx-auto p-4">
      {/* Page Header */}
      <h1 className="text-2xl font-bold">Search Articles</h1>
      <p className="text-muted-foreground mb-4">
        Find articles by title, summary, or content
      </p>

      {/* Search Input */}
      <div className="flex gap-2 mb-4">
        <Input
          value={query}
          onChange={(e) => setQuery(e.target.value)}
          placeholder="Search articles..."
          className="flex-1"
        />
        <Button onClick={handleSearch} disabled={loading}>
          {loading ? "Searching…" : "Search"}
        </Button>
      </div>

      {/* Results */}
      <div className="space-y-4">
        {results.length === 0 && !loading && query && (
          <p>No articles found.</p>
        )}

        {results.map((article) => (
          <Card key={article.id}>
            <CardContent className="pt-4">
              <Link href={`/articles/${article.slug}`} className="text-lg font-medium hover:underline">
                {article.title}
              </Link>
              {article.summary && (
                <p className="text-sm text-muted-foreground mt-1">
                  {article.summary}
                </p>
              )}
              <p className="text-xs text-muted-foreground mt-2">
                {new Date(article.created_at).toDateString()}
              </p>
            </CardContent>
          </Card>
        ))}
      </div>
    </div>
  );
}

Live‑Search (Optional)

Replace the static handler with an on‑change that queries the API on every keystroke:

{
  const value = e.target.value;
  setQuery(value);

  if (!value.trim()) {
    setResults([]);
    return;
  }

  setLoading(true);
  const res = await fetch(
    `/api/search?q=${encodeURIComponent(value)}`
  );
  const data = await res.json();
  setResults(data);
  setLoading(false);
}

Tip: Debounce the request (e.g., 300 ms) to avoid flooding the server.

Extending the Search Backend

• Algolia or Typesense can replace the PostgreSQL full‑text search for faster, typo‑tolerant results.
• The API route (/api/search) would simply forward the query to the chosen service and return its JSON response.

Demo

A live demo of the component (including optional live‑search) can be viewed at:
https://your-demo-url.com/articles/search

Thank you for taking the time to read through this article! 🙏