Search with the database's own full-text index

How to do it

In PostgreSQL, add a generated tsvector column with a named language configuration and put a GIN index on it. GIN is the preferred text search index type in the PostgreSQL docs. Turn the user's words into a query with websearch_to_tsquery, which never raises a syntax error on raw input. In MySQL, add a FULLTEXT index to an InnoDB table and query it with MATCH ... AGAINST. In SQLite, use an FTS5 table, and keep an external content table in step with triggers. Which software to pick, and when a separate search engine is worth it, is in Use PostgreSQL search first and OpenSearch when it falls short. Highlighted excerpts from ts_headline are not safe HTML, so escape them as Escape everything you show requires.

Examples

SQL example

-- PostgreSQL
ALTER TABLE products ADD COLUMN search_text tsvector
  GENERATED ALWAYS AS (to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, ''))) STORED;
CREATE INDEX CONCURRENTLY products_search ON products USING GIN (search_text);
SELECT id, name FROM products
WHERE search_text @@ websearch_to_tsquery('english', $1)
LIMIT 20;

SQL example 2

-- MySQL with InnoDB
ALTER TABLE products ADD FULLTEXT INDEX products_search (name, description);
SELECT id, name FROM products
WHERE MATCH (name, description) AGAINST (? IN NATURAL LANGUAGE MODE)
LIMIT 20;

How to check

EXPLAIN of every search query shows the full-text index, such as a bitmap index scan on the GIN index in PostgreSQL. A search of the code finds no LIKE or ILIKE with a leading % in search routes.

Sources

• PostgreSQL text search tables and indexes
• PostgreSQL text search index types
• SQLite FTS5

Related rules

• Use PostgreSQL search first and OpenSearch when it falls short in Software choices
• Escape everything you show in Security testing