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).

Pass the term, the columns to search and, optionally, the page you want:

users.service.ts
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.maxLimit
searchTermRequired

string

The text to match. It goes to plainto_tsquery by default (plain words, all required), or to websearch_to_tsquery with queryMode: "websearch".
searchColumnsRequired

string[]

Column keys on the table. An empty list or an unknown name is a 400, and so is a column outside searchableColumns when that allowlist is set.
paginationOptional

PaginationOptions

page and limit. Omitted, page 1 and pagination.defaultLimit (20 by default) apply, and the limit is clamped to pagination.maxLimit.
optionsOptional

FullTextSearchOptions

Everything findAll takes in its options (transaction, relations, select, counts, withDeleted / onlyDeleted, the tenant context), plus filters, language and queryMode below.
options.filtersOptional

CrudFilter

The same filters as the first argument of findAll: columns, operators, relation filters, and / or / not. Added in 3.7.0.
options.languageOptional

string

The text search configuration, such as "simple", "german" or "public.my_config". It must be a plain identifier, optionally schema qualified; anything else is a 400. Added in 3.7.0.

Defaults to "english"

options.queryModeOptional

"plain" | "websearch"

plain is plainto_tsquery. websearch is websearch_to_tsquery: quoted phrases, or and a leading - work as on a search engine. Added in 3.7.0.

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):

generated.sql
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 0

What that means for results:

  • A NULL column contributes nothing to the vector, so a row still matches on its other columns. Before 3.7.2 the vector was to_tsvector(a) || to_tsvector(b) without coalesce, and since x || NULL is NULL, a row with any NULL column never matched.
  • A single column stays to_tsvector('english', "bio"), without coalesce, so an index on that expression keeps matching.
  • A column that already is a tsvector (a generated, stored column) goes in as is, with no to_tsvector around it, since 3.8.0. Next to other columns it is coalesce("search", ''::tsvector).
  • The text search configuration is 'english' unless you pass language. The name is written into the SQL as a literal, not as a bound parameter, so an index on to_tsvector('german', ...) still matches. That is why it has to pass the identifier check first.
  • Rows are ordered by ts_rank descending, 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 data and total, as in findAll and count. A tenant scope applies the same way, and so do options.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:

posts.service.ts
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":

articles.service.ts
await this.articles.fullTextSearch(
  '"zero downtime" -kubernetes',
  ["title", "body"],
  { limit: 20 },
  { language: "german", queryMode: "websearch" },
);
queryModeFunctionpost -1 reads as
"plain" (default)plainto_tsqueryevery word required; -1 is just a number
"websearch"websearch_to_tsquerypost 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:

migration.sql
-- 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:

migration.sql
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);
schema.ts
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
});
users.service.ts
// 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:

users.service.ts
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"
Orderts_rank, strongest match firstYour sortBy, not ranked
Structured filtersYes, in options.filters (since 3.7.0)Yes, plus relation filters
Unknown column name400Skipped
Empty termMatches no rowsAdds no condition
language, queryModeYesYes
DialectPostgreSQL onlyPostgreSQL 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_headline is 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) and phraseto_tsquery need a custom method. websearch covers phrases, or and negation.
  • No MySQL or SQLite. MySQL has MATCH ... AGAINST and 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:

users.service.ts
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

Next steps

Last updated View change history

On this page