Full-text search

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.

Enabling

The method is opt-in per project:

DrizzleCrudModule.forRoot({
  dialect: 'postgresql',
  connectionString: process.env.DATABASE_URL,
  schema,
  sql: { enableFullTextSearch: true },
});

You also 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:  20 rows ordered by ts_rank
// total: total number of matching rows
// page:  resolved (clamped) page — 1-indexed
// limit: resolved (clamped) limit

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

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

The exact generated SQL depends on the column types. Numeric columns are cast to text. Nullable columns are coalesced to the empty string.

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. The first result is the strongest match. Tiebreaker is the entity's defaultSort (or the row's primary key, if no defaultSort).

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

Next

• → Filtering — structured filters plus options.search
• → Defining services — custom methods that combine full-text with structured filters