Full-Text Search

Truss provides a full-text search module built on PostgreSQL’s native tsvector and tsquery capabilities. Set up search on any table with weighted columns, auto-updating triggers, and GIN indexes — all from the dashboard or API.

How it works

PostgreSQL full-text search converts text into tsvector tokens and matches them against tsquery queries. Truss automates the setup:

1. Adds a search_vector column (type tsvector) to your table
2. Creates a trigger that auto-updates the vector on INSERT/UPDATE
3. Creates a GIN index for fast lookups
4. Supports weighted columns (A, B, C, D) for relevance ranking

Setup wizard

The easiest way to enable search is via the dashboard. Navigate to Search and use the setup wizard:

1. Pick a table
2. Select columns to index (and assign weights)
3. Choose a text search configuration (e.g., english, simple)
4. Click “Create” — Truss generates the tsvector column, trigger, and GIN index

Via API

curl -X POST http://localhost:8787/api/search/setup \
  -H "Content-Type: application/json" \
  -d '{
    "schema": "public",
    "table": "articles",
    "columns": [
      {"name": "title", "weight": "A"},
      {"name": "body", "weight": "B"},
      {"name": "tags", "weight": "C"}
    ],
    "config": "english"
  }'

This generates SQL like:

ALTER TABLE articles ADD COLUMN search_vector tsvector;

CREATE FUNCTION articles_search_update() RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(NEW.body, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(NEW.tags, '')), 'C');
  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER articles_search_trigger
  BEFORE INSERT OR UPDATE ON articles
  FOR EACH ROW EXECUTE FUNCTION articles_search_update();

CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);

Searching

Test a query via the dashboard

Navigate to Search > Playground, select a table, type a query, and see results with highlighted matches (ts_headline).

Via API

curl -X POST http://localhost:8787/api/search/test \
  -H "Content-Type: application/json" \
  -d '{
    "schema": "public",
    "table": "articles",
    "query": "postgres & full-text",
    "limit": 20
  }'

Via SQL-over-HTTP

For full control, use the SQL endpoint:

curl -X POST http://localhost:8787/v1/sql \
  -H "apikey: truss_sk_your_key" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT id, title, ts_headline(body, q) AS snippet, ts_rank(search_vector, q) AS rank FROM articles, to_tsquery($1) q WHERE search_vector @@ q ORDER BY rank DESC LIMIT 10",
    "params": ["english", "database & search"]
  }'

Inspecting search configuration

# List text search configurations
curl http://localhost:8787/api/search/configs

# List tables with search indexes
curl http://localhost:8787/api/search/indexes

# List text columns eligible for search
curl http://localhost:8787/api/search/columns

# List tables eligible for search setup
curl http://localhost:8787/api/search/eligible

Query syntax

PostgreSQL tsquery supports:

Syntax	Meaning	Example
&	AND	cat & dog
|	OR	cat | dog
!	NOT	cat & !dog
<->	Followed by	full <-> text
:*	Prefix match	post:*

Weights

Columns can be assigned weights A through D (A is highest priority):

• A (weight 1.0) — titles, names
• B (weight 0.4) — body text, descriptions
• C (weight 0.2) — tags, categories
• D (weight 0.1) — metadata, comments

Results are ranked by ts_rank, which considers these weights.