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 LIKE operator.
1SELECT 'Hi! This is my 1st post.' LIKE 'Hi! This is my 1st post.' -- trueBesides regular text, our pattern can contain the percent sign – %. It matches a sequence of zero or more characters.
1SELECT 'Hi! This is my 1st post.' LIKE 'Hi!%'; -- true
2SELECT 'I wrote this post.' LIKE '%post.'; -- true
3SELECT 'Hi! This is my favourite post.' LIKE 'Hi! % post.'; -- true
4SELECT 'This is the 1st time I am writing.' LIKE '%1st%'; -- trueWhen using the LIKE operator, we can also take advantage of the underscore sign – _. It matches a single character.
1SELECT 'Hi! This is my 1st post.' LIKE 'Hi! This is my ___ post.'; -- true
2SELECT 'Hi! This was a 2nd post.' LIKE 'Hi! This _____ ___ post.'; -- true
3SELECT 'Hi! This is my 1st post.' LIKE 'Hi! This _____ ___ post.'; -- trueWe can also use multiple percentages and underscore signs in a single pattern.
1SELECT 'This is my 1st post.' LIKE 'This % my ___ %.'; -- true
2SELECT 'This was my 2nd article.' LIKE 'This % my ___ %.'; -- trueUsing pattern matching in a real use-case
Pattern matching is especially useful when performing a SELECT on a table and using WHERE.
1SELECT * FROM posts
2WHERE title LIKE '%post%';Above, we use the % sign on both the left and right sides of the post string when looking through the title column. This means we are looking for rows that use the post string in any way in the title column. The above is a very common case.
Finding rows that don’t match a pattern
We can also use NOT LIKE to find rows that don’t match a particular pattern.
1SELECT * FROM posts
2WHERE title NOT LIKE '%content%';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 LIKE operator and two % signs in practice. To do that, let’s expect the user to provide a query parameter.
1import { IsString, IsNotEmpty, IsOptional } from 'class-validator';
2
3class SearchPostsQuery {
4 @IsString()
5 @IsNotEmpty()
6 @IsOptional()
7 search?: string;
8}
9
10export default SearchPostsQuery;Once we have the above class, we need to use it in our controller.
1import {
2 ClassSerializerInterceptor,
3 Controller,
4 Get,
5 Query,
6 UseInterceptors,
7} from '@nestjs/common';
8import { PostsService } from './posts.service';
9import GetPostsByAuthorQuery from './getPostsByAuthorQuery';
10import PaginationParams from '../utils/paginationParams';
11import SearchPostsQuery from "./searchPostsQuery";
12
13@Controller('posts')
14@UseInterceptors(ClassSerializerInterceptor)
15export default class PostsController {
16 constructor(private readonly postsService: PostsService) {}
17
18 @Get()
19 getPosts(
20 @Query() { authorId }: GetPostsByAuthorQuery,
21 @Query() { search }: SearchPostsQuery,
22 @Query() { offset, limit, idsToSkip }: PaginationParams,
23 ) {
24 return this.postsService.getPosts(authorId, offset, limit, idsToSkip, search);
25 }
26
27 // ...
28}We can rely on PostsService to call the correct methods from our repositories.
1import { Injectable } from '@nestjs/common';
2import PostsRepository from './posts.repository';
3import PostsStatisticsRepository from './postsStatistics.repository';
4import PostsSearchRepository from './postsSearch.repository';
5
6@Injectable()
7export class PostsService {
8 constructor(
9 private readonly postsRepository: PostsRepository,
10 private readonly postsStatisticsRepository: PostsStatisticsRepository,
11 private readonly postsSearchRepository: PostsSearchRepository,
12 ) {}
13
14 getPosts(
15 authorId?: number,
16 offset?: number,
17 limit?: number,
18 idsToSkip?: number,
19 searchQuery?: string,
20 ) {
21 if (authorId && searchQuery) {
22 return this.postsSearchRepository.searchByAuthor(
23 authorId,
24 offset,
25 limit,
26 idsToSkip,
27 searchQuery,
28 );
29 }
30 if (authorId) {
31 return this.postsRepository.getByAuthorId(
32 authorId,
33 offset,
34 limit,
35 idsToSkip,
36 );
37 }
38 if (searchQuery) {
39 return this.postsSearchRepository.search(
40 offset,
41 limit,
42 idsToSkip,
43 searchQuery,
44 );
45 }
46 return this.postsRepository.get(offset, limit, idsToSkip);
47 }
48 // ...
49}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.
1import { Injectable } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import PostModel from './post.model';
4
5@Injectable()
6class PostsSearchRepository {
7 constructor(private readonly databaseService: DatabaseService) {}
8
9 async search(
10 offset = 0,
11 limit: number | null = null,
12 idsToSkip = 0,
13 searchQuery: string,
14 ) {
15 const databaseResponse = await this.databaseService.runQuery(
16 `
17 WITH selected_posts AS (
18 SELECT * FROM posts
19 WHERE id > $3 AND concat(post_content, title) LIKE concat('%', $4::text, '%')
20 ORDER BY id ASC
21 OFFSET $1
22 LIMIT $2
23 ),
24 total_posts_count_response AS (
25 SELECT COUNT(*)::int AS total_posts_count FROM posts
26 WHERE concat(post_content, title) LIKE concat('%', $4::text, '%')
27 )
28 SELECT * FROM selected_posts, total_posts_count_response
29 `,
30 [offset, limit, idsToSkip, searchQuery],
31 );
32 const items = databaseResponse.rows.map(
33 (databaseRow) => new PostModel(databaseRow),
34 );
35 const count = databaseResponse.rows[0]?.total_posts_count || 0;
36 return {
37 items,
38 count,
39 };
40 }
41
42 // ...
43}
44
45export default PostsSearchRepository;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 title and post_content 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 $4 argument to be treated as a string because the concat() function works with different data types. Without it, PostgreSQL would throw an error.
The ILIKE operator
The ILIKE operator works in a similar way to LIKE. However, an essential thing about ILIKE is that it is case-insensitive.
1SELECT 'Hi! This is my favourite post.' ILIKE 'hI! % pOsT.'; -- trueSince we let the user search for any occurrence of a given string, let’s make it case-insensitive.
1import { Injectable } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import PostModel from './post.model';
4
5@Injectable()
6class PostsSearchRepository {
7 constructor(private readonly databaseService: DatabaseService) {}
8
9 // ...
10
11 async searchByAuthor(
12 authorId: number,
13 offset = 0,
14 limit: number | null = null,
15 idsToSkip = 0,
16 searchQuery: string,
17 ) {
18 const databaseResponse = await this.databaseService.runQuery(
19 `
20 WITH selected_posts AS (
21 SELECT * FROM posts
22 WHERE author_id=$1 AND id > $4 AND (title ILIKE concat('%', $5::text, '%') OR post_content ILIKE concat('%', $5::text, '%'))
23 ORDER BY id ASC
24 OFFSET $2
25 LIMIT $3
26 ),
27 total_posts_count_response AS (
28 SELECT COUNT(*)::int AS total_posts_count FROM posts
29 WHERE author_id=$1 AND id > $4 AND (title ILIKE concat('%', $5::text, '%') OR post_content ILIKE concat('%', $5::text, '%'))
30 )
31 SELECT * FROM selected_posts, total_posts_count_response
32 `,
33 [authorId, offset, limit, idsToSkip, searchQuery],
34 );
35 const items = databaseResponse.rows.map(
36 (databaseRow) => new PostModel(databaseRow),
37 );
38 const count = databaseResponse.rows[0]?.total_posts_count || 0;
39 return {
40 items,
41 count,
42 };
43 }
44}
45
46export default PostsSearchRepository;Using regular expressions
Using LIKE and ILIKE 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.
1SELECT 'Hi!' ~ '^[0-9]*$'; -- false
2SELECT '123' ~ '^[0-9]*$'; -- trueIf 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.
1SELECT 'Admin' ~* 'admin|user|moderator'; -- trueTo check if a string does not match the regular expression, we can use the !~ operator.
1SELECT 'admin' !~ 'admin|user|moderator'; -- false
2SELECT 'editor' !~ 'admin|user|moderator'; -- trueWe can also mix it up and check if a string does not match the regular expression and keep it case-insensitive.
1SELECT 'ADMIN' !~* 'admin|user|moderator'; -- falseRegular expressions can be handy when the LIKE 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 SIMILAR TO operator. It is a blend of the LIKE operator and regular expressions. Patterns used with SIMILAR TO are similar to regex but use _ and % instead of . and .*.
1SELECT '123' SIMILAR TO '[0-9]{1,}'; -- trueThe interesting thing is that PostgreSQL translates the patterns from the SIMILAR TO format to regular expressions.
1EXPLAIN ANALYZE
2SELECT * FROM posts WHERE title SIMILAR TO '[0-9]{1,}';Because of the above, I suggest writing regular expressions instead of using the SIMILAR TO operator when the LIKE 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 LIKE and SIMILAR TO 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!