Full-text search

PostgreSQL tsvector / tsquery via fullTextSearch(): ranked, paginated, with a clean result envelope.

The package ships a single fullTextSearch() method on SqlBaseCrudService. It builds a to_tsvector(...) @@ plainto_tsquery(...) expression across the columns you pass, orders by ts_rank, and returns the same { data, total, page, limit } envelope as findAll.

PostgreSQL only

Full-text search uses PostgreSQL's tsvector and tsquery types. The method throws if the entity's dialect is 'mysql'. There is no MySQL-specific implementation.

Soft-delete aware

When soft-delete is enabled on the entity, soft-deleted rows are excluded from both the data and the total, matching the behavior of findAll and count.

Do I need to enable it?

No. fullTextSearch() works on any PostgreSQL entity. The sql.enableFullTextSearch flag is deprecated and has no effect.

You do need either an english (or other language) tsvector index on the columns you're searching, or a generated tsvector column. Drizzle has helpers for both. See the Drizzle docs on full-text search for the schema side.

Usage

const { data, total, page, limit } = await 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 ordered by ts_rank
// total: total number of matching rows
// page:  resolved (clamped) page: 1-indexed
// limit: resolved (clamped) limit

Without the pagination argument the entity's default page and limit still apply (page 1, defaultLimit rows), and the envelope reports the values 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.

Every name in the columns list must be a column key on the table. An unknown name, or an empty list, is a 400.

The columns are passed to a Drizzle sql template that generates:

to_tsvector('english', "name") || to_tsvector('english', "email") || to_tsvector('english', "bio")
  @@ plainto_tsquery('english', $1)

A NULL column contributes nothing to the vector, so a row still matches on its other columns.

Full-text search inside list endpoints

Since 3.1.0, findAll and count can also apply a search predicate through options.search. This is useful for list endpoints where search must compose with structured filters, pagination, sorting, relation filters, and the same total query:

const page = await users.findAll(
  { status: 'active' },
  { page: 1, limit: 20, sortBy: 'createdAt', sortOrder: 'desc' },
  {
    search: {
      term: 'john doe',
      columns: ['name', 'email', 'bio'],
      mode: 'fullText',
    },
  },
);

The options.search full-text mode is PostgreSQL-only and is not ranked. Use fullTextSearch() when search relevance should drive ordering; use findAll(..., ..., { search }) when you need a normal list endpoint with search folded into its filtering and count behavior.

Ordering

Results are ordered by ts_rank(...) descending, then by the primary key ascending. The first result is the strongest match, and rows with the same rank keep a stable order across pages.

Limitations

No highlighting. ts_headline isn't generated. If you need highlighted snippets, write a custom method.
No multi-language documents per row. The to_tsvector config name is fixed per call (defaults to 'english'). For multi-language content, add a language column and use a raw Drizzle call.
No fuzzy / prefix matching. plainto_tsquery is the simplest of the tsquery constructors. Use phraseto_tsquery or to_tsquery for phrase / boolean queries via a custom method.
MySQL is unsupported. MySQL has MATCH ... AGAINST but the package doesn't generate dialect-specific full-text SQL.

When to use a custom method instead

For anything beyond "search a term across these text columns, rank by relevance, paginate," drop down to raw Drizzle:

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})`,
      );
  }
}