Full-Text Search | Bootspring Docs

Patterns for implementing search functionality with PostgreSQL full-text search.

Overview#

Full-text search enables efficient content searching:

Natural language queries
Relevance ranking
Stemming and tokenization
PostgreSQL native support

Prerequisites:

PostgreSQL database
Prisma ORM setup

Implementation#

PostgreSQL Full-Text Search#

// lib/search.ts
import { prisma } from '@/lib/db'

// PostgreSQL full-text search
export async function searchPosts(query: string, options?: {
  limit?: number
  offset?: number
}) {
  const { limit = 10, offset = 0 } = options ?? {}

  // Convert query to tsquery format
  const searchQuery = query
    .trim()
    .split(/\s+/)
    .map(term => `${term}:*`)
    .join(' & ')

  const posts = await prisma.$queryRaw`
    SELECT
      id,
      title,
      content,
      ts_rank(
        to_tsvector('english', title || ' ' || content),
        to_tsquery('english', ${searchQuery})
      ) as rank
    FROM "Post"
    WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', ${searchQuery})
    ORDER BY rank DESC
    LIMIT ${limit}
    OFFSET ${offset}
  `

  return posts
}

// Prisma native search (preview)
export async function searchWithPrisma(query: string) {
  return prisma.post.findMany({
    where: {
      OR: [
        { title: { search: query } },
        { content: { search: query } }
      ]
    },
    orderBy: {
      _relevance: {
        fields: ['title', 'content'],
        search: query,
        sort: 'desc'
      }
    }
  })
}

Search API Route#

// app/api/search/route.ts
import { searchPosts } from '@/lib/search'

export async function GET(request: Request) {
  const { searchParams } = new URL(request.url)
  const query = searchParams.get('q')
  const page = parseInt(searchParams.get('page') ?? '1')
  const limit = parseInt(searchParams.get('limit') ?? '10')

  if (!query || query.length < 2) {
    return Response.json({ results: [] })
  }

  const offset = (page - 1) * limit

  const results = await searchPosts(query, { limit, offset })

  return Response.json({
    results,
    page,
    limit,
    query
  })
}

Search Input with Debounce#

// components/SearchInput.tsx
'use client'

import { useState, useCallback } from 'react'
import { useDebouncedCallback } from 'use-debounce'
import { useRouter, useSearchParams } from 'next/navigation'

export function SearchInput() {
  const router = useRouter()
  const searchParams = useSearchParams()
  const [query, setQuery] = useState(searchParams.get('q') ?? '')

  const handleSearch = useDebouncedCallback((term: string) => {
    const params = new URLSearchParams(searchParams)

    if (term) {
      params.set('q', term)
    } else {
      params.delete('q')
    }

    router.push(`/search?${params.toString()}`)
  }, 300)

  return (
    <input
      type="search"
      value={query}
      onChange={(e) => {
        setQuery(e.target.value)
        handleSearch(e.target.value)
      }}
      placeholder="Search..."
      className="w-full rounded-lg border px-4 py-2"
    />
  )
}

Search Results Page#

// app/search/page.tsx
import { searchPosts } from '@/lib/search'
import { SearchInput } from '@/components/SearchInput'

export default async function SearchPage({
  searchParams
}: {
  searchParams: { q?: string; page?: string }
}) {
  const query = searchParams.q ?? ''
  const page = parseInt(searchParams.page ?? '1')

  const results = query
    ? await searchPosts(query, { limit: 10, offset: (page - 1) * 10 })
    : []

  return (
    <div className="mx-auto max-w-2xl py-8">
      <SearchInput />

      <div className="mt-8">
        {query && (
          <p className="mb-4 text-gray-600">
            Results for "{query}"
          </p>
        )}

        {results.length === 0 && query && (
          <p className="text-gray-500">No results found</p>
        )}

        <div className="space-y-4">
          {results.map((post: any) => (
            <article key={post.id} className="rounded-lg border p-4">
              <h2 className="font-semibold">{post.title}</h2>
              <p className="mt-1 text-gray-600">{post.content?.slice(0, 150)}...</p>
            </article>
          ))}
        </div>
      </div>
    </div>
  )
}

Search Index Migration#

-- migrations/add_search_index.sql

-- Create GIN index for full-text search
CREATE INDEX IF NOT EXISTS post_search_idx ON "Post"
USING GIN (to_tsvector('english', title || ' ' || COALESCE(content, '')));

-- Or create a generated column for the search vector
ALTER TABLE "Post" ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || COALESCE(content, ''))) STORED;

CREATE INDEX post_search_vector_idx ON "Post" USING GIN (search_vector);

Search Suggestions#

// app/api/search/suggestions/route.ts
import { prisma } from '@/lib/db'

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

  if (!query || query.length < 2) {
    return Response.json({ suggestions: [] })
  }

  // Get popular searches matching query
  const suggestions = await prisma.searchQuery.findMany({
    where: {
      query: { startsWith: query.toLowerCase() }
    },
    orderBy: { count: 'desc' },
    take: 5,
    select: { query: true }
  })

  return Response.json({
    suggestions: suggestions.map(s => s.query)
  })
}

Highlighting Results#

// lib/search.ts
export async function searchWithHighlight(query: string) {
  const searchQuery = query
    .trim()
    .split(/\s+/)
    .map(term => `${term}:*`)
    .join(' & ')

  const results = await prisma.$queryRaw`
    SELECT
      id,
      title,
      ts_headline(
        'english',
        content,
        to_tsquery('english', ${searchQuery}),
        'StartSel=<mark>, StopSel=</mark>, MaxWords=35, MinWords=15'
      ) as highlighted_content,
      ts_rank(
        to_tsvector('english', title || ' ' || content),
        to_tsquery('english', ${searchQuery})
      ) as rank
    FROM "Post"
    WHERE search_vector @@ to_tsquery('english', ${searchQuery})
    ORDER BY rank DESC
    LIMIT 10
  `

  return results
}

Advanced Search Options#

// lib/search.ts
interface SearchOptions {
  query: string
  filters?: {
    category?: string
    author?: string
    dateFrom?: Date
    dateTo?: Date
  }
  sort?: 'relevance' | 'date' | 'popularity'
  limit?: number
  offset?: number
}

export async function advancedSearch(options: SearchOptions) {
  const { query, filters, sort = 'relevance', limit = 10, offset = 0 } = options

  const searchQuery = query
    .trim()
    .split(/\s+/)
    .map(term => `${term}:*`)
    .join(' & ')

  let orderBy = 'rank DESC'
  if (sort === 'date') orderBy = '"createdAt" DESC'
  if (sort === 'popularity') orderBy = '"viewCount" DESC'

  const results = await prisma.$queryRaw`
    SELECT
      p.id,
      p.title,
      p.content,
      p."createdAt",
      p."viewCount",
      u.name as "authorName",
      c.name as "categoryName",
      ts_rank(search_vector, to_tsquery('english', ${searchQuery})) as rank
    FROM "Post" p
    LEFT JOIN "User" u ON p."authorId" = u.id
    LEFT JOIN "Category" c ON p."categoryId" = c.id
    WHERE search_vector @@ to_tsquery('english', ${searchQuery})
      ${filters?.category ? Prisma.sql`AND c.slug = ${filters.category}` : Prisma.empty}
      ${filters?.author ? Prisma.sql`AND u.id = ${filters.author}` : Prisma.empty}
      ${filters?.dateFrom ? Prisma.sql`AND p."createdAt" >= ${filters.dateFrom}` : Prisma.empty}
      ${filters?.dateTo ? Prisma.sql`AND p."createdAt" <= ${filters.dateTo}` : Prisma.empty}
    ORDER BY ${Prisma.raw(orderBy)}
    LIMIT ${limit}
    OFFSET ${offset}
  `

  return results
}

Search Analytics#

// lib/search.ts
export async function trackSearch(query: string, resultsCount: number) {
  await prisma.searchQuery.upsert({
    where: { query: query.toLowerCase() },
    update: {
      count: { increment: 1 },
      lastSearchedAt: new Date()
    },
    create: {
      query: query.toLowerCase(),
      count: 1,
      resultsCount
    }
  })
}

// Track after search
const results = await searchPosts(query)
await trackSearch(query, results.length)

Best Practices#

Create GIN indexes - Essential for full-text search performance
Use generated columns - Pre-compute search vectors
Debounce search input - Reduce unnecessary queries
Implement search suggestions - Improve user experience
Track search queries - Analyze what users are looking for

Queries - Query optimization
Prisma - Prisma setup and basics
Pooling - Connection management