Implementing Full-Text Search
1. Creating Full-Text Index
@@fulltext([title, body])
| DB | Detail |
| MySQL | Native FULLTEXT |
| PG | Preview fullTextSearchPostgres, uses tsvector |
2. Using Search Filter
await prisma.post.findMany({
where: { title: { search: "react & typescript" } }
});
| Operator | Syntax |
| AND | & |
| OR | | |
| NOT | ! |
| Prefix | word:* |
3. Ranking Search Results
await prisma.post.findMany({
where: { title: { search: "prisma" } },
orderBy: { _relevance: { fields: ["title"], search: "prisma", sort: "desc" } }
});
| Aspect | Detail |
| _relevance | Computed score |
| Multi-field | Weight by field listing order |
4. Searching Multiple Fields
| Approach | Detail |
| Composite fulltext | @@fulltext([title, body]) |
| OR clauses | Search each field separately |
5. Using Natural Search Mode
| MySQL Mode | Detail |
| NATURAL LANGUAGE | Default; ranks by relevance |
| BOOLEAN | Operator-based |
| WITH QUERY EXPANSION | Pseudo-relevance feedback |
6. Implementing Autocomplete Pattern
| Strategy | Detail |
| Trigram | PG pg_trgm + GIN index |
| Prefix index | name LIKE 'abc%' on B-tree |
| External | Meilisearch / Algolia / Typesense |
7. Escaping Special Characters
| Char | Handling |
| & | ! | Reserved operators — escape with quotes |
| User input | Sanitize before passing to search |
8. Using Stemming Configuration
| DB | Detail |
| PG | Configurable tsvector language |
| MySQL | Built-in stopwords; no stemming |
9. Combining Search with Filters
await prisma.post.findMany({
where: {
AND: [
{ body: { search: "performance" } },
{ published: true }
]
}
});
| Tip | Detail |
| Index order | Filter columns first, then search |
| Tip | Detail |
| Materialize tsvector | Store column + GIN index |
| Limit fields | Index only searched fields |
| External engine | Use for large catalogs |