How to Do Full-Text Search in Supabase

1-- Example: articles table with full-text search
2create table public.articles (
3  id bigint generated always as identity primary key,
4  title text not null,
5  body text not null,
6  author_id uuid references auth.users(id),
7  created_at timestamptz default now(),
8  -- Generated tsvector column combining title and body
9  fts tsvector generated always as (
10    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
11    setweight(to_tsvector('english', coalesce(body, '')), 'B')
12  ) stored
13);
14
15alter table public.articles enable row level security;
16
17create policy "Public read articles" on public.articles
18  for select to anon, authenticated
19  using (true);

1-- Create a GIN index on the tsvector column
2create index idx_articles_fts on public.articles using gin (fts);
3
4-- Verify the index was created
5select indexname, indexdef
6from pg_indexes
7where tablename = 'articles';

1import { createClient } from '@supabase/supabase-js'
2
3const supabase = createClient(
4  process.env.NEXT_PUBLIC_SUPABASE_URL!,
5  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
6)
7
8// Basic search: all words must match
9const { data: results } = await supabase
10  .from('articles')
11  .select('id, title, body, created_at')
12  .textSearch('fts', 'supabase database')
13
14// Phrase search: exact phrase match
15const { data: phrase } = await supabase
16  .from('articles')
17  .select('id, title')
18  .textSearch('fts', 'row level security', { type: 'phrase' })
19
20// Websearch: Google-style syntax
21const { data: web } = await supabase
22  .from('articles')
23  .select('id, title')
24  .textSearch('fts', '"edge functions" OR "serverless" -lambda', {
25    type: 'websearch'
26  })

1-- Create a search function with ranking
2create or replace function search_articles(search_query text)
3returns table (
4  id bigint,
5  title text,
6  body text,
7  rank real
8)
9language sql
10as $$
11  select
12    a.id,
13    a.title,
14    a.body,
15    ts_rank(a.fts, websearch_to_tsquery('english', search_query)) as rank
16  from public.articles a
17  where a.fts @@ websearch_to_tsquery('english', search_query)
18  order by rank desc
19  limit 20;
20$$;
21
22-- Call from the JS client
23const { data } = await supabase.rpc('search_articles', {
24  search_query: 'supabase edge functions'
25})

1-- Search function with highlighted snippets
2create or replace function search_articles_with_highlights(search_query text)
3returns table (
4  id bigint,
5  title text,
6  snippet text,
7  rank real
8)
9language sql
10as $$
11  select
12    a.id,
13    a.title,
14    ts_headline(
15      'english',
16      a.body,
17      websearch_to_tsquery('english', search_query),
18      'StartSel=<mark>, StopSel=</mark>, MaxWords=35, MinWords=15'
19    ) as snippet,
20    ts_rank(a.fts, websearch_to_tsquery('english', search_query)) as rank
21  from public.articles a
22  where a.fts @@ websearch_to_tsquery('english', search_query)
23  order by rank desc
24  limit 20;
25$$;
26
27const { data } = await supabase.rpc('search_articles_with_highlights', {
28  search_query: 'database security'
29})
30// data[0].snippet: "...configure <mark>database</mark> <mark>security</mark> policies..."