Full-text search in PostgreSQL with Drizzle
Drizzle full text search on Postgres in NestJS: fullTextSearch() ranks with ts_rank, takes filters and relations, a language and websearch queries.
For Drizzle full text search on PostgreSQL in NestJS, call fullTextSearch(term, columns, pagination, options) on any service that extends SqlBaseCrudService. By default it matches to_tsvector('english', ...) across the columns you name against plainto_tsquery('english', term), orders the rows by ts_rank, and returns the same { data, total, page, limit } envelope as findAll. Since 3.7.0 the options take filters, relations, column selection, another text search configuration and search engine style queries. It works on PostgreSQL only and needs no flag to turn on.
PostgreSQL only
Full-text search uses PostgreSQL's tsvector and tsquery types. On MySQL and SQLite the
method throws a 400 BadRequestException (Full-text search only supported in PostgreSQL).
How do I run a full-text search?
Pass the term, the columns to search and, optionally, the page you want:
const { data, total, page, limit } = await this.users.fullTextSearch(
"john doe", // search term: passed to plainto_tsquery
["name", "email", "bio"], // columns to search across
{ page: 1, limit: 20 }, // optional pagination
);
// data: at most 20 rows, strongest match first
// total: number of matching rows
// page: the page used (1-indexed)
// limit: the limit used, clamped to pagination.maxLimitsearchTermRequiredstring
searchColumnsRequiredstring[]
paginationOptionalPaginationOptions
optionsOptionalFullTextSearchOptions
options.filtersOptionalCrudFilter
options.languageOptionalstring
Defaults to "english"
options.queryModeOptional"plain" | "websearch"
Defaults to "plain"
The envelope always reports the page and limit that were used. Before 3.3.1 an omitted pagination returned every match while the envelope said limit: 20. The data and count queries run together in one Promise.all.
What SQL does it generate?
The columns go into a Drizzle sql template as bound column references, joined with ||. With more than one column, each is wrapped in coalesce(column, '') (since 3.7.2):
select * from users
where to_tsvector('english', coalesce("name", '')) || to_tsvector('english', coalesce("email", '')) || to_tsvector('english', coalesce("bio", ''))
@@ plainto_tsquery('english', $1)
and "deleted_at" is null
order by ts_rank(<same tsvector>, plainto_tsquery('english', $1)) desc, "id" asc
limit 20 offset 0What that means for results:
- A
NULLcolumn contributes nothing to the vector, so a row still matches on its other columns. Before 3.7.2 the vector wasto_tsvector(a) || to_tsvector(b)withoutcoalesce, and sincex || NULLisNULL, a row with anyNULLcolumn never matched. - A single column stays
to_tsvector('english', "bio"), withoutcoalesce, so an index on that expression keeps matching. - A column that already is a
tsvector(a generated, stored column) goes in as is, with noto_tsvectoraround it, since 3.8.0. Next to other columns it iscoalesce("search", ''::tsvector). - The text search configuration is
'english'unless you passlanguage. The name is written into the SQL as a literal, not as a bound parameter, so an index onto_tsvector('german', ...)still matches. That is why it has to pass the identifier check first. - Rows are ordered by
ts_rankdescending, then by the primary key ascending, so rows with the same rank keep a stable order across pages. - With soft delete on, soft-deleted rows are left out of both
dataandtotal, as infindAllandcount. A tenant scope applies the same way, and so dooptions.filters.
How do I search within filters and load relations?
Pass filters, relations and select in the options. They work exactly as they do on findAll: filters narrow the matches before they are ranked and counted, a belongs-to relation is joined, collections are loaded in one batched query, and select narrows the columns:
const page = await this.posts.fullTextSearch(
"drizzle migrations",
["title", "body"],
{ page: 1, limit: 20 },
{
filters: { status: "published", author: { role: "admin" } },
relations: ["author", "tags"],
select: ["id", "title", "author.name", "tags.name"],
counts: ["comments"],
},
);Before 3.7.0 the options only carried the transaction and the soft delete flags; filters, relations and select were ignored.
How do I search in another language or with search engine syntax?
Set language to the text search configuration and queryMode to "websearch":
await this.articles.fullTextSearch(
'"zero downtime" -kubernetes',
["title", "body"],
{ limit: 20 },
{ language: "german", queryMode: "websearch" },
);queryMode | Function | post -1 reads as |
|---|---|---|
"plain" (default) | plainto_tsquery | every word required; -1 is just a number |
"websearch" | websearch_to_tsquery | post and not 1; quotes make a phrase, or an alternative |
Neither mode raises a syntax error on odd input, so both are safe for a search box. A configuration that does not exist in the database (language: "klingon") passes the identifier check and fails in PostgreSQL, so check the name in your code if it comes from a client.
Do I need to enable it or add an index?
You do not need to enable it. fullTextSearch() works on any PostgreSQL entity, and the sql.enableFullTextSearch flag is deprecated and has no effect.
An index is your call, and it matters on large tables. Without one, PostgreSQL computes to_tsvector for every row on every search. There are two ways to give it one: a GIN index on the exact expression the query uses, or a stored tsvector column with a GIN index on the column.
Option 1: a GIN index on the expression
PostgreSQL only uses an expression index whose expression is exactly the one in the query, so repeat the generated vector, in the same column order as your columns array:
-- fullTextSearch(term, ["name", "email", "bio"])
CREATE INDEX users_fts ON users USING gin (
(to_tsvector('english', coalesce(name, '')) || to_tsvector('english', coalesce(email, '')) || to_tsvector('english', coalesce(bio, '')))
);
-- fullTextSearch(term, ["bio"]): one column, no coalesce
CREATE INDEX users_bio_fts ON users USING gin (to_tsvector('english', bio));An index built on several columns before 3.7.2, without coalesce, no longer matches the query; recreate it as above. Pass language and the index needs the same configuration name.
Option 2: a stored tsvector column (since 3.8.0)
Compute the vector once per write in a generated column, index the column, and search on it. The service sees that the column is a tsvector and uses it as is, so the index matches whatever the column order or configuration:
ALTER TABLE users ADD COLUMN search tsvector GENERATED ALWAYS AS (
to_tsvector('english', coalesce(name, '') || ' ' || coalesce(bio, ''))
) STORED;
CREATE INDEX users_search ON users USING gin (search);import { customType, pgTable, serial, text } from "drizzle-orm/pg-core";
const tsvector = customType<{ data: string }>({ dataType: () => "tsvector" });
export const users = pgTable("users", {
id: serial("id").primaryKey(),
name: text("name").notNull(),
bio: text("bio"),
search: tsvector("search"), // generated in the migration above
});// where "users"."search" @@ plainto_tsquery('english', $1)
await this.users.fullTextSearch("john doe", ["search"], { limit: 20 });On 200,000 rows on PGlite, a search on a stored column with its GIN index took 0.6 ms, against 600 to 900 ms computing to_tsvector per row without an index. Before 3.8.0 a tsvector column failed with function to_tsvector(unknown, tsvector) does not exist. The Drizzle guide to full-text search shows both options on the schema side, and Database indexes for fast Drizzle queries covers pg_trgm for the default ilike search.
How do I add search to a filtered list endpoint?
Use options.search with mode: "fullText" on findAll or count. The match then composes with your structured filters, pagination, sorting, relation filters and the total count. language and queryMode work there too:
const page = await this.users.findAll(
{ status: "active" },
{ page: 1, limit: 20, sortBy: "createdAt", sortOrder: "desc" },
{
search: {
term: "john doe",
columns: ["name", "email", "bio"],
mode: "fullText",
queryMode: "websearch",
},
},
);The two differ in a few ways:
fullTextSearch() | findAll with search.mode: "fullText" | |
|---|---|---|
| Order | ts_rank, strongest match first | Your sortBy, not ranked |
| Structured filters | Yes, in options.filters (since 3.7.0) | Yes, plus relation filters |
| Unknown column name | 400 | Skipped |
| Empty term | Matches no rows | Adds no condition |
language, queryMode | Yes | Yes |
| Dialect | PostgreSQL only | PostgreSQL only |
Use fullTextSearch() when relevance should drive the order. Use findAll(..., ..., { search }) when search is one more filter on a normal list. Filtering with Drizzle in NestJS lists every search option.
What does it not do?
- No highlighting.
ts_headlineis not generated. - One configuration per call. Rows in different languages need one call per language, or a custom query.
- No raw tsquery or prefix queries.
to_tsquery(with:*prefixes) andphraseto_tsqueryneed a custom method.websearchcovers phrases,orand negation. - No MySQL or SQLite. MySQL has
MATCH ... AGAINSTand SQLite has FTS5, but the package generates neither.
How do I write a custom full-text query?
Drop down to Drizzle in a method on your service. The injected database is this.config.db:
export class UsersService extends SqlBaseCrudService<User> {
async findWithSnippet(term: string) {
return this.config.db
.select({
id: users.id,
name: users.name,
snippet: sql<string>`ts_headline('english', ${users.bio}, plainto_tsquery('english', ${term}))`,
})
.from(users)
.where(
sql`to_tsvector('english', ${users.bio}) @@ plainto_tsquery('english', ${term})`,
);
}
}A custom query skips what the service adds for you: add isNull(users.deletedAt) and any tenant condition yourself.
Common questions
No. It is PostgreSQL only and is a 400 on any other dialect. Use options.search in its default ilike mode for a portable substring search.
Yes, since 3.7.0. Pass language: "german" (or any configuration name, optionally schema qualified) in the options of fullTextSearch or in search on findAll. A name that is not a plain identifier is a 400.
Yes, since 3.7.0. options.filters takes the same filters as findAll, and relations, select and counts shape the rows as they do there.
fullTextSearch() orders by ts_rank descending, then by the primary key. findAll with search.mode: "fullText" filters on the same match but keeps your own sort.
No. With soft delete on, they are left out of both data and total unless you pass withDeleted or onlyDeleted in the options.
Next steps
Filtering with Drizzle in NestJS
Structured filters and options.search on findAll and count.
Defining services
Add custom methods next to the inherited CRUD ones.
Allowlists
Limit which columns a client may search with searchableColumns.
Database indexes for fast Drizzle queries
GIN for full-text search, pg_trgm for contains, and the rest.
Select columns with Drizzle in NestJS
Select columns in NestJS with Drizzle: pass options.select to return only the columns you need, and use dotted paths to narrow eager-loaded relations.
Count, group by and aggregates in Drizzle
Drizzle aggregates in NestJS: count() rows, group by a column with countBy, and relation counts per row with options.counts. SUM needs a custom method.