Utility · Intermediate

Full-Text Search

PostgreSQL full-text search via Supabase — weighted tsvector search, websearch_to_tsquery format, autocomplete, facet counts, highlight extraction, recent searches, and a debounced React hook with pagination.

Read the Getting Access guideif you haven't yet.

1. Get the file

Sign in at marrowstack.dev, open the Full-Text Search block, and click Copy all files. Paste lib/search.ts (and the React hook) into your project.

Run the included migration SQL to add the search_vector column and trigger to your target table before wiring in the API route.

2. Prerequisites

Next.js 14 or 15, App Router
Supabase project with at least one table to search
TypeScript 5+

3. Install

Copy the block files into your project.
Install peer dependencies:

bash

npm install @supabase/supabase-js

4. Environment

Variable	Required	Default	Purpose
NEXT_PUBLIC_SUPABASE_URL	yes	—	Your Supabase project URL
NEXT_PUBLIC_SUPABASE_ANON_KEY	yes	—	Anon key — used client-side for search queries
SUPABASE_SERVICE_ROLE_KEY	yes	—	Service role key — used server-side for migrations and index updates

5. Database setup

Run this migration in Supabase SQL Editor → New query. Adjust your_table, column names, and weights to match your schema.A is highest weight, D is lowest.

Supabase SQL Editor

-- 1. Add the search vector column
alter table your_table
  add column if not exists search_vector tsvector;

-- 2. Populate it from existing rows
update your_table
set search_vector =
  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(description, '')), 'B') ||
  setweight(to_tsvector('english', coalesce(body, '')), 'C');

-- 3. Keep it updated automatically
create or replace function your_table_search_vector_update()
returns trigger language plpgsql as $$
begin
  new.search_vector :=
    setweight(to_tsvector('english', coalesce(new.title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(new.description, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(new.body, '')), 'C');
  return new;
end;
$$;

create trigger your_table_search_vector_trigger
  before insert or update on your_table
  for each row execute function your_table_search_vector_update();

-- 4. Create a GIN index for fast lookups
create index if not exists your_table_search_vector_idx
  on your_table using gin(search_vector);

6. Wire it in

app/api/search/route.ts

import { searchRecords } from '@/lib/search'
import { NextRequest, NextResponse } from 'next/server'

export async function GET(req: NextRequest) {
  const { searchParams } = new URL(req.url)
  const query = searchParams.get('q') ?? ''
  const page  = Number(searchParams.get('page') ?? '1')

  if (!query.trim()) return NextResponse.json({ results: [], total: 0 })

  const { results, total } = await searchRecords({
    table: 'your_table',
    query,
    page,
    pageSize: 10,
    columns: ['id', 'title', 'description', 'slug'],
  })

  return NextResponse.json({ results, total })
}

Client — debounced search hook

'use client'
import { useSearch } from '@/lib/search'

export function SearchBar() {
  const { query, setQuery, results, isLoading, total } = useSearch({
    endpoint: '/api/search',
    debounceMs: 300,
  })

  return (
    <div>
      <input
        value={query}
        onChange={(e) => setQuery(e.target.value)}
        placeholder="Search…"
        className="w-full rounded border px-3 py-2"
      />
      {isLoading && <p className="text-xs mt-1">Searching…</p>}
      <ul className="mt-2 space-y-1">
        {results.map((r) => (
          <li key={r.id}>
            <a href={`/posts/${r.slug}`}>{r.title}</a>
          </li>
        ))}
      </ul>
      {total > 0 && <p className="text-xs mt-2">{total} results</p>}
    </div>
  )
}

Autocomplete suggestions

import { getAutocompleteSuggestions } from '@/lib/search'

// Call this in a GET /api/search/suggest route
const suggestions = await getAutocompleteSuggestions({
  table: 'your_table',
  column: 'title',
  prefix: 'next',
  limit: 5,
})

7. Verify it works

Insert a row into your table and confirm the search_vector column is populated by the trigger.
Call GET /api/search?q=your+term and confirm results are returned.
Search for a partial word and confirm weighted ranking (title matches rank above body matches).
Type quickly in the SearchBar and confirm the debounce delays the API call.

8. Failure modes & fixes

search_vector column is always null

Cause: Existing rows were not backfilled after adding the column.

Fix: Run the UPDATE statement from the migration to populate search_vector for existing rows. New rows are handled automatically by the trigger.

No results for a query that should match

Cause: The tsvector uses 'english' dictionary but your content is in another language.

Fix: Change 'english' to the appropriate Postgres text search configuration (e.g. 'french', 'german', 'simple').

Search is slow on large tables

Cause: The GIN index may not exist or may need a REINDEX.

Fix: Run CREATE INDEX IF NOT EXISTS ... USING gin(search_vector) and confirm the index is used with EXPLAIN ANALYZE.