البحث النصي الكامل في PostgreSQL مع Next.js — بناء بحث قوي بدون Elasticsearch (2026)

npx create-next-app@latest pg-search-demo --typescript --tailwind --app --src-dir
cd pg-search-demo

npm install prisma @prisma/client
npx prisma init

DATABASE_URL="postgresql://user:password@localhost:5432/search_demo?schema=public"

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["fullTextSearchPostgres"]
}
 
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
 
model Article {
  id          Int      @id @default(autoincrement())
  title       String
  body        String
  category    String
  tags        String[]
  publishedAt DateTime @default(now())
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
}

npx prisma migrate dev --name init

npx prisma migrate dev --name add-fts --create-only

-- إضافة عمود tsvector مُولّد يجمع العنوان (وزن A) والمحتوى (وزن B)
ALTER TABLE "Article" ADD COLUMN "search_vector" tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce("title", '')), 'A') ||
    setweight(to_tsvector('english', coalesce("body", '')), 'B') ||
    setweight(to_tsvector('english', coalesce("category", '')), 'C') ||
    setweight(to_tsvector('english', array_to_string("tags", ' ')), 'D')
  ) STORED;
 
-- إنشاء فهرس GIN للبحث النصي السريع
CREATE INDEX "Article_search_vector_idx" ON "Article" USING GIN ("search_vector");
 
-- تفعيل إضافة pg_trgm للمطابقة الضبابية
CREATE EXTENSION IF NOT EXISTS pg_trgm;
 
-- إنشاء فهرس trigram على العنوان للإكمال التلقائي المتسامح مع الأخطاء
CREATE INDEX "Article_title_trgm_idx" ON "Article" USING GIN ("title" gin_trgm_ops);

npx prisma migrate dev

import { PrismaClient } from "@prisma/client";
 
const prisma = new PrismaClient();
 
const articles = [
  {
    title: "Getting Started with Next.js App Router",
    body: "The Next.js App Router introduces a new paradigm for building React applications. With server components, streaming, and nested layouts, it provides a powerful foundation for modern web development.",
    category: "frontend",
    tags: ["nextjs", "react", "app-router", "server-components"],
  },
  {
    title: "PostgreSQL Performance Tuning for Production",
    body: "Optimizing PostgreSQL for production workloads requires understanding query planning, index strategies, connection pooling, and resource allocation.",
    category: "database",
    tags: ["postgresql", "performance", "indexing", "production"],
  },
  {
    title: "Building Type-Safe APIs with tRPC and Prisma",
    body: "tRPC eliminates the API layer by sharing TypeScript types between your frontend and backend. Combined with Prisma for database access, you get end-to-end type safety.",
    category: "backend",
    tags: ["trpc", "prisma", "typescript", "api"],
  },
];
 
async function main() {
  console.log("Seeding database...");
  for (const article of articles) {
    await prisma.article.create({ data: article });
  }
  console.log(`Seeded ${articles.length} articles`);
}
 
main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

{
  "prisma": {
    "seed": "npx tsx prisma/seed.ts"
  }
}

npx prisma db seed

import { PrismaClient } from "@prisma/client";
 
const prisma = new PrismaClient();
 
export interface SearchResult {
  id: number;
  title: string;
  body: string;
  category: string;
  tags: string[];
  publishedAt: Date;
  rank: number;
  headline: string;
}
 
export interface SearchOptions {
  query: string;
  limit?: number;
  offset?: number;
  category?: string;
}
 
export async function searchArticles({
  query,
  limit = 10,
  offset = 0,
  category,
}: SearchOptions): Promise<{ results: SearchResult[]; total: number }> {
  if (!query.trim()) {
    return { results: [], total: 0 };
  }
 
  const searchQuery = query.trim();
  const categoryFilter = category
    ? `AND "category" = '${category}'`
    : "";
 
  const results = await prisma.$queryRawUnsafe<SearchResult[]>(
    `
    SELECT
      "id", "title", "body", "category", "tags", "publishedAt",
      ts_rank_cd("search_vector", websearch_to_tsquery('english', $1), 32) AS "rank",
      ts_headline('english', "body", websearch_to_tsquery('english', $1),
        'StartSel=<mark>, StopSel=</mark>, MaxWords=35, MinWords=15, MaxFragments=2'
      ) AS "headline"
    FROM "Article"
    WHERE "search_vector" @@ websearch_to_tsquery('english', $1)
    ${categoryFilter}
    ORDER BY "rank" DESC, "publishedAt" DESC
    LIMIT $2 OFFSET $3
    `,
    searchQuery, limit, offset
  );
 
  const countResult = await prisma.$queryRawUnsafe<[{ count: bigint }]>(
    `
    SELECT COUNT(*) as "count"
    FROM "Article"
    WHERE "search_vector" @@ websearch_to_tsquery('english', $1)
    ${categoryFilter}
    `,
    searchQuery
  );
 
  const total = Number(countResult[0]?.count ?? 0);
  return { results, total };
}

export interface Suggestion {
  id: number;
  title: string;
  similarity: number;
}
 
export async function getAutocompleteSuggestions(
  query: string,
  limit: number = 5
): Promise<Suggestion[]> {
  if (!query.trim() || query.length < 2) {
    return [];
  }
 
  const results = await prisma.$queryRawUnsafe<Suggestion[]>(
    `
    SELECT
      "id", "title",
      similarity("title", $1) AS "similarity"
    FROM "Article"
    WHERE "title" ILIKE $2 OR similarity("title", $1) > 0.15
    ORDER BY
      CASE WHEN "title" ILIKE $2 THEN 0 ELSE 1 END,
      similarity("title", $1) DESC
    LIMIT $3
    `,
    query, `%${query}%`, limit
  );
 
  return results;
}
 
export async function fuzzySearch(
  query: string,
  limit: number = 10
): Promise<SearchResult[]> {
  if (!query.trim()) return [];
 
  const results = await prisma.$queryRawUnsafe<SearchResult[]>(
    `
    SELECT
      "id", "title", "body", "category", "tags", "publishedAt",
      similarity("title", $1) AS "rank",
      ts_headline('english', "body", plainto_tsquery('english', $1),
        'StartSel=<mark>, StopSel=</mark>, MaxWords=35, MinWords=15'
      ) AS "headline"
    FROM "Article"
    WHERE similarity("title", $1) > 0.15
       OR similarity("body", $1) > 0.05
    ORDER BY similarity("title", $1) DESC
    LIMIT $2
    `,
    query, limit
  );
 
  return results;
}
 
export async function hybridSearch(options: SearchOptions) {
  const ftsResults = await searchArticles(options);
  if (ftsResults.results.length > 0) return ftsResults;
 
  const fuzzyResults = await fuzzySearch(options.query, options.limit);
  return { results: fuzzyResults, total: fuzzyResults.length, fuzzy: true };
}

import { NextRequest, NextResponse } from "next/server";
import { hybridSearch, getAutocompleteSuggestions } from "@/lib/search";
 
export async function GET(request: NextRequest) {
  const searchParams = request.nextUrl.searchParams;
  const query = searchParams.get("q") ?? "";
  const category = searchParams.get("category") ?? undefined;
  const limit = Math.min(parseInt(searchParams.get("limit") ?? "10"), 50);
  const offset = parseInt(searchParams.get("offset") ?? "0");
  const mode = searchParams.get("mode");
 
  try {
    if (mode === "suggest") {
      const suggestions = await getAutocompleteSuggestions(query);
      return NextResponse.json({ suggestions });
    }
 
    const results = await hybridSearch({ query, limit, offset, category });
    return NextResponse.json(results);
  } catch (error) {
    console.error("Search error:", error);
    return NextResponse.json({ error: "Search failed" }, { status: 500 });
  }
}

"use client";
 
import { useState, useEffect, useRef, useCallback } from "react";
 
interface SearchResult {
  id: number;
  title: string;
  category: string;
  tags: string[];
  rank: number;
  headline: string;
}
 
export function SearchBox() {
  const [query, setQuery] = useState("");
  const [results, setResults] = useState<SearchResult[]>([]);
  const [total, setTotal] = useState(0);
  const [loading, setLoading] = useState(false);
 
  const handleSearch = useCallback(async (searchQuery: string) => {
    if (!searchQuery.trim()) { setResults([]); setTotal(0); return; }
    setLoading(true);
    try {
      const res = await fetch(`/api/search?q=${encodeURIComponent(searchQuery)}`);
      const data = await res.json();
      setResults(data.results ?? []);
      setTotal(data.total ?? 0);
    } finally { setLoading(false); }
  }, []);
 
  return (
    <div className="w-full max-w-2xl mx-auto">
      <div className="relative">
        <input
          type="search"
          value={query}
          onChange={(e) => setQuery(e.target.value)}
          onKeyDown={(e) => { if (e.key === "Enter") handleSearch(query); }}
          placeholder="ابحث في المقالات..."
          className="w-full px-4 py-3 rounded-lg border border-gray-300 focus:ring-2 focus:ring-blue-500"
        />
        <button
          onClick={() => handleSearch(query)}
          className="absolute left-2 top-1/2 -translate-y-1/2 px-4 py-1.5 bg-blue-600 text-white rounded-md"
        >
          بحث
        </button>
      </div>
 
      {loading && <div className="mt-6 text-center text-gray-400">جارٍ البحث...</div>}
 
      <div className="mt-4 space-y-4">
        {results.map((result) => (
          <article key={result.id} className="p-4 border rounded-lg hover:shadow-md">
            <h3 className="text-lg font-semibold">{result.title}</h3>
            <p
              className="mt-1 text-sm text-gray-600 [&_mark]:bg-yellow-200"
              dangerouslySetInnerHTML={{ __html: result.headline }}
            />
          </article>
        ))}
      </div>
    </div>
  );
}

import { SearchBox } from "@/components/SearchBox";
 
export default function SearchPage() {
  return (
    <main className="min-h-screen bg-gray-50 py-12 px-4">
      <div className="max-w-4xl mx-auto">
        <h1 className="text-3xl font-bold text-center mb-2">بحث المقالات</h1>
        <p className="text-center text-gray-500 mb-8">
          يدعم اللغة الطبيعية والعبارات المقتبسة والاستثناءات
        </p>
        <SearchBox />
      </div>
    </main>
  );
}

npm run dev

ALTER TABLE "Article" ADD COLUMN "lang" TEXT DEFAULT 'english';
 
ALTER TABLE "Article" ADD COLUMN "search_vector" tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector(
      CASE
        WHEN "lang" = 'arabic' THEN 'arabic'::regconfig
        WHEN "lang" = 'french' THEN 'french'::regconfig
        ELSE 'english'::regconfig
      END,
      coalesce("title", '')
    ), 'A') ||
    setweight(to_tsvector(
      CASE
        WHEN "lang" = 'arabic' THEN 'arabic'::regconfig
        WHEN "lang" = 'french' THEN 'french'::regconfig
        ELSE 'english'::regconfig
      END,
      coalesce("body", '')
    ), 'B')
  ) STORED;

EXPLAIN ANALYZE
SELECT *
FROM "Article"
WHERE "search_vector" @@ websearch_to_tsquery('english', 'react hooks');

"use server";
 
import { hybridSearch, getAutocompleteSuggestions } from "@/lib/search";
 
export async function searchAction(formData: FormData) {
  const query = formData.get("q") as string;
  const category = formData.get("category") as string | undefined;
  return hybridSearch({ query, category });
}
 
export async function suggestAction(query: string) {
  return getAutocompleteSuggestions(query);
}