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

#nextjs #postgres #tutorial

Although workshops can be both instructive and exhausting, I made certain to complete Brian Holt’s workshop on developing a comprehensive Fullstack Next.js application. You may review it on Frontendmasters. I intend to enhance the application by integrating comprehensive full-text search capabilities. I plan to systematically record the procedure for future reference.

Based on this, I assume the:

The Next.js full-stack app is completed.
It includes a PostgreSQL database.
and it contains some data to search for.

The processes below are the steps I followed and researched to implement the functionality. We will build:

Full-text search using Postgres
Powered by Neon
The query will be done through Drizzle ORM.
accessed through Next.js API route
used on the search page UI

Since the application is a blog app, we will add:

Full-text search index by running it inside Neon SQL Editor (this would have been set up while building the app).
Connection to Drizzle Database

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

this (articles_search_idx) creates a search index on the article, you can use any other name you prefer. This intends to make the search fast and sort by relevance.

Connection to Drizzle Database

It is expected that the database would have been connected while building the fullstack application. However, if not done, use the sample below:

📁 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!);

 export const db = drizzle(sql, { schema });

Create the Search API Folder/Routes for 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);
 }

the easy way I use to understand this is:

I type a search criterion in the box.
It calls /api/search?q=term from the frontend.

On the backend, it: - turns the character into searchable form

            -finds matching articles

            -score them based on relevance

It returns the best matches and displays results.

Install Shadcn components

I used Shadcn and Tailwind for the application.

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

Creation of UI for Searches (Articles)

📁 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<Article[]>([]);
   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="mx-auto max-w-3xl px-4 py-10">
       {/* Page Header */}
       <div className="mb-8 space-y-2 text-center">
         <h1 className="text-3xl font-bold tracking-tight">
           Search Articles
         </h1>
         <p className="text-muted-foreground">
           Find articles by title, summary, or content
         </p>
       </div>

       {/* Search Input */}
       <div className="flex gap-2">
         <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>

       <Separator className="my-8" />

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

         {results.map((article) => (
           <Card key={article.id} className="hover:shadow-md transition">
             <CardContent className="p-6 space-y-2">
               <Link href={`/articles/${article.slug}`}>
                 <h3 className="text-lg font-semibold hover:underline">
                   {article.title}
                 </h3>
               </Link>

               {article.summary && (
                 <p className="text-sm text-muted-foreground line-clamp-3">
                   {article.summary}
                 </p>
               )}

               <p className="text-xs text-muted-foreground">
                 {new Date(article.created_at).toDateString()}
               </p>
             </CardContent>
           </Card>
         ))}
       </div>
     </div>
   );
 }

It links as follows:

When a user searches, the frontend updates the query.

After clicking search, handleSearch() runs, then the API runs /api/search?q=...

backend runs Postgres full-text search

Results are returned as JSON, and the UI is updated.

Optional: live search

We can make search behave like Google search. It starts searching immediately a user starts by typing.

"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<Article[]>([]);
   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="mx-auto max-w-3xl px-4 py-10">
       {/* Page Header */}
       <div className="mb-8 space-y-2 text-center">
         <h1 className="text-3xl font-bold tracking-tight">
           Search Articles
         </h1>
         <p className="text-muted-foreground">
           Find articles by title, summary, or content
         </p>
       </div>

       {/* Search Input */}
       <div className="flex gap-2">
         <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>

       <Separator className="my-8" />

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

         {results.map((article) => (
           <Card key={article.id} className="hover:shadow-md transition">
             <CardContent className="p-6 space-y-2">
               <Link href={`/articles/${article.slug}`}>
                 <h3 className="text-lg font-semibold hover:underline">
                   {article.title}
                 </h3>
               </Link>

               {article.summary && (
                 <p className="text-sm text-muted-foreground line-clamp-3">
                   {article.summary}
                 </p>
               )}

               <p className="text-xs text-muted-foreground">
                 {new Date(article.created_at).toDateString()}
               </p>
             </CardContent>
           </Card>
         ))}
       </div>
     </div>
   );
 }

It links as follows:

When a user searches, the frontend updates the query.
After clicking search, handleSearch() runs, then the API runs /api/search?q=...
backend runs Postgres full-text search
Results are returned as JSON, and the UI is updated.

Optional: live search

We can make search behave like Google search. It starts searching immediately a user starts by typing.

<Input
  value={query}
  onChange={async (e) => {
    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);
  }}
  placeholder="Search articles..."
/>

Check a live demo here

This search can also be expanded to use Algolia/Typesense.

Thank you for taking time to go through the article.