API with NestJS #79. Implementing searching with pattern matching and raw SQL

JavaScript NestJS SQL

This entry is part 79 of 168 in the API with NestJS

The possibility of searching through the contents of the database is a very common feature. There are great solutions built with that use case in mind, such as Elasticsearch. Even though that’s the case, PostgreSQL also has the functionality of matching a given string pattern. In this article, we explore what PostgreSQL offers and use this in our NestJS project.

The code from this article is in this repository.

Pattern matching with LIKE

The idea behind pattern matching is to check if a given string has specific characteristics. The most straightforward way of doing that in PostgreSQL is by using the operator.

Besides regular text, our pattern can contain the percent sign – . It matches a sequence of zero or more characters.

When using the operator, we can also take advantage of the underscore sign – . It matches a single character.

We can also use multiple percentages and underscore signs in a single pattern.

Using pattern matching in a real use-case

Pattern matching is especially useful when performing a on a table and using .

Above, we use the sign on both the left and right sides of the string when looking through the column. This means we are looking for rows that use the string in any way in the column. The above is a very common case.

Finding rows that don’t match a pattern

We can also use to find rows that don’t match a particular pattern.

Above, we look for posts with a title that does not contain the word “content”.

Implementing searching in NestJS

Let’s use the approach with the operator and two signs in practice. To do that, let’s expect the user to provide a query parameter.

searchPostsQuery.ts

Once we have the above class, we need to use it in our controller.

posts.controller.ts

We can rely on to call the correct methods from our repositories.

posts.service.ts

We can delegate the logic of searching through the posts to a separate repository to avoid creating one big file that’s difficult to read.

postsSearch.repository.ts

Above, we implement pagination. If you want to know more, check out API with NestJS #77. Offset and keyset pagination with raw SQL queries

A few significant things are happening above. We use pattern matching with both and columns. We wrap the query provided by the user with the signs on both ends. To do that, we use the concat function.

It is important to acknowledge that our query might work in an unexpected way if the users puts or characters in their search input. To prevent this, we could sanitize the provided string by prepending all special characters with the sign.

We also indicate that we want the argument to be treated as a string because the function works with different data types. Without it, PostgreSQL would throw an error.

The ILIKE operator

The operator works in a similar way to . However, an essential thing about is that it is case-insensitive.

Since we let the user search for any occurrence of a given string, let’s make it case-insensitive.

postsSearch.repository.ts

Using regular expressions

Using and can cover a lot of use cases with pattern matching. But, unfortunately, not all of them. Sometimes we might need to be more specific when describing the pattern.

Fortunately, PostgreSQL allows us to use regular expressions with the operator.

If you want to know more about regular expressions, check out my series abour regex.

We can also make it case-insensitive by using the operator.

To check if a string does not match the regular expression, we can use the  operator.

We can also mix it up and check if a string does not match the regular expression and keep it case-insensitive.

Regular expressions can be handy when the operator is not enough. Unfortunately, we need to ensure we are writing an expression that does not cause issues with the performance. If you want to know more, check out Regex course – part four. Avoiding catastrophic backtracking using lookahead.

The SIMILAR TO operator

The SQL standard also contains the operator. It is a blend of the operator and regular expressions. Patterns used with are similar to regex but use and instead of and .

The interesting thing is that PostgreSQL translates the patterns from the format to regular expressions.

Because of the above, I suggest writing regular expressions instead of using the operator when the keyword is not enough.

Summary

In this article, we’ve gone through pattern matching with PostgreSQL. We’ve used it to implement a search feature with NestJS. We also compared the and   operators and regular expressions to get a better picture.

There is still more to learn when it comes to searching through text in PostgreSQL, such as the text search types. Stay tuned for more content!

Series Navigation<< API with NestJS #78. Generating statistics using aggregate functions in raw SQLAPI with NestJS #80. Updating entities with PUT and PATCH using raw SQL queries >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments