Pattern matching search with Drizzle ORM and PostgreSQL

Searching through text documents is a very common feature in many web applications. In this article, we learn how to implement it using pattern matching using the Drizzle ORM, PostgreSQL, and NestJS.

Pattern matching with LIKE and ILIKE

With pattern matching, we can determine if a given piece of text matches a particular pattern. To do that, we need to use the LIKE operator built into SQL.

1SELECT 'Hi! This is my first article' LIKE 'Hi! This is my first article'; -- true

In the above example, the LIKE operator works like an equals operator and returns true only if the text exactly matches the pattern. To change that, we can use the % operator that matches a sequence of zero or more characters.

We can also use the _ operator to match a single character.

1SELECT 'Hi! This is my first article' LIKE 'Hi!%'; -- true

1SELECT 'Hi! This is my first article' LIKE '%first article'; -- true

If we want to search for a piece of text in an entire string, we should wrap it with the % sign on both the left and right sides.

1SELECT 'Hi! This is my first article' LIKE '%first%'; -- true

Making the search case-insensitive

If we want to make our search case-insensitive, we can use the ILIKE operator.

1SELECT 'Hi! This is my first article' ILIKE '%FiRsT%'; -- true

1SELECT 'Hi! This is my first article' LIKE '%FiRsT%';  -- false

Filtering rows

We can use the above operators to filter the rows of a table.

1SELECT * from articles
2WHERE title LIKE '%third%';

Implementing searching with NestJS

To implement the above functionality with NestJS, we need to allow the user to provide the pattern through query parameters. To do that, we should create a DTO class to validate the parameter.

1import { IsString, IsNotEmpty, IsOptional } from 'class-validator';
2 
3export class SearchArticlesQuery {
4  @IsString()
5  @IsNotEmpty()
6  @IsOptional()
7  search?: string;
8}

Now, we can use our DTO in the controller.

1import { Controller, Get, Query } from '@nestjs/common';
2import { ArticlesService } from './articles.service';
3import { SearchArticlesQuery } from './dto/search-articles-query.dto';
4 
5@Controller('articles')
6export class ArticlesController {
7  constructor(private readonly articlesService: ArticlesService) {}
8 
9  @Get()
10  getAll(@Query() { search }: SearchArticlesQuery) {
11    if (search) {
12      return this.articlesService.search(search);
13    }
14    return this.articlesService.getAll();
15  }
16 
17  // ...
18}

Pattern matching with Drizzle ORM

To use the LIKE operator with the Drizzle ORM to filter rows, we need to combine the where() and like() functions.

1import { Injectable } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { like } from 'drizzle-orm';
5 
6@Injectable()
7export class ArticlesService {
8  constructor(private readonly drizzleService: DrizzleService) {}
9 
10  search(pattern: string) {
11    return this.drizzleService.db
12      .select()
13      .from(databaseSchema.articles)
14      .where(like(databaseSchema.articles.title, `%${pattern}%`));
15  }
16 
17  // ...
18}

Please notice that we’re not expecting the users to provide the % operators explicitly. Instead, we’re wrapping their input with %. This might work in unexpected ways if the user puts the % or _ characters in their input. To prevent this, we could sanitize the provided string by prepending all special characters with the \ sign.

If we want our search to be case-insensitive, we can use the ilike() function.

1import { Injectable } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { ilike } from 'drizzle-orm';
5 
6@Injectable()
7export class ArticlesService {
8  constructor(private readonly drizzleService: DrizzleService) {}
9 
10  search(pattern: string) {
11    return this.drizzleService.db
12      .select()
13      .from(databaseSchema.articles)
14      .where(ilike(databaseSchema.articles.title, `%${pattern}%`));
15  }
16 
17  // ...
18}

Searching through multiple columns

Right now, we’re searching only through one column. We can use the or() function if we want to use multiple columns.

1import { Injectable } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { ilike, or } from 'drizzle-orm';
5 
6@Injectable()
7export class ArticlesService {
8  constructor(private readonly drizzleService: DrizzleService) {}
9 
10  search(pattern: string) {
11    return this.drizzleService.db
12      .select()
13      .from(databaseSchema.articles)
14      .where(
15        or(
16          ilike(databaseSchema.articles.title, `%${pattern}%`),
17          ilike(databaseSchema.articles.content, `%${pattern}%`),
18        ),
19      );
20  }
21 
22  // ...
23}

Pattern matching vs. Full-text search

In one of the previous articles, we covered how to use the Drizzle ORM to implement Full-text search using the tsvector column. While this method is more complicated to implement, it is also more powerful.

Let’s take a look at the example with the following sentences:

• Birds fly south for the winter.
• We saw a helicopter flying above the city.
• We flew to San Francisco last summer.
• The years have flown by quickly.

With pattern matching, we’re looking for an exact match. If we use the LIKE operator to look for the word “fly”, we will only match sentences one and two because they contain the word “fly”.

However, with the full-text search feature, each word goes through the stemming process, where it is simplified to its root form. Because of that, the words “flying“, “flew“, and “flown” are all simplified to the word “fly”. When the user tries to search for sentences that include the word “fly”, all the above sentences match. This improves the search process a lot and returns more meaningful results.

Regular expressions

While we could cover a bunch of real-life scenarios with the LIKE operator, it might not be enough in some cases. Fortunately, PostgreSQL allows us to use regular expressions.

1SELECT 'Hi!' ~ '^[0-9]*$'; -- false

1SELECT '123' ~ '^[0-9]*$'; -- true

The SQL standard also includes the SIMILAR TO operator that’s a blend of of the LIKE operator and regular expressions.

While Drizzle ORM does not natively support it, we could write a piece of raw SQL to use it.

1this.drizzleService.db
2  .select()
3  .from(databaseSchema.articles)
4  .where(sql`${databaseSchema.articles.title} ~ '^[0-9]*$'`);

Summary

In this article, we explained pattern matching and implemented it using raw SQL queries and the Drizzle ORM. We also learned how to support the search feature in our REST API with NestJS and explained how pattern matching differs from full-text search. This helped us understand when pattern matching is good enough and why we might want to choose full-text search instead.