API with NestJS #181. Prepared statements in PostgreSQL with Drizzle ORM

December 30, 2024

This entry is part 181 of 181 in the API with NestJS

When we execute an SQL query, PostgreSQL follows a process that consists of multiple steps. First, it parses the SQL statement, checking for syntax errors. Next, it analyzes whether the tables and columns used in the query exist. Then, it plans the instructions necessary to achieve the desired result. Finally, it executes the instructions based on the plan. By default, PostgreSQL parses, analyzes, and plans the query repeatedly, even if we run the same SQL query multiple times. The time needed can add up, causing unnecessary overhead.

To tackle this issue, PostgreSQL offers prepared statements. They can optimize the process by letting us parse, analyze, and plan a query beforehand. This way, the query is parsed, analyzed, and planned once and can be executed multiple times without redoing those steps.

In this article, we explain how prepared statements work in PostgreSQL and implement them both using raw SQL queries and with the Drizzle ORM.

Introducing prepared statements

Each article our database stores consists of a title, content, and creation date.

database-schema.ts

export const articles = pgTable('articles', {

id: serial('id').primaryKey(),

title: text('title').notNull(),

content: text('content').notNull(),

createdAt: timestamp('created_at', {

withTimezone: true,

})

.notNull()

.defaultNow(),

});

Let’s write the SQL query that returns all articles created yesterday.

SELECT * FROM articles

WHERE created_at >= current_date - interval '1 day'

AND created_at < current_date;

When we run our query, PostgreSQL first parses and analyzes it. Then, it creates an execution plan. To see it, we can use the EXPLAIN keyword.

EXPLAIN

SELECT * FROM articles

WHERE created_at >= current_date - interval '1 day'

AND created_at < current_date;

Creating a prepared statement with raw SQL

To create a prepared statement with raw SQL, we need to use the PREPARE keyword together with a descriptive name.

PREPARE get_articles_from_yesterday AS

SELECT * FROM articles

WHERE created_at >= current_date - interval '1 day'

AND created_at < current_date;

To run our prepared statement, we need the EXECUTE keyword.

1	EXECUTE get_articles_from_yesterday;

When we create a prepared statement, it’s only for the current database session. If we close our session, we need to create our prepared statement again. This also means that other sessions can’t use the prepared statement we created.

Prepared statements with parameters

We can create a prepared statement to retrieve articles from a particular range of dates. To do that, we need to create a prepared statement with parameters.

PREPARE get_articles_between_dates(timestamptz, timestamptz) AS

SELECT * FROM articles

WHERE created_at >= $1

AND created_at < $2;

In the above example, we specify two parameters. One is the starting date, and the other is the end date.

Now, we can provide the parameters when executing the prepared statement. While doing that, keeping the correct order of parameters is crucial.

-- Fetch articles from the start of December to the end of December

EXECUTE get_articles_between_dates('2024-12-01', '2025-01-01');

-- Fetch articles from last two months

EXECUTE get_articles_between_dates(current_date - interval '2 months', current_date + interval '1 day');

Prepared statements with the Drizzle ORM

First, let’s run our query in a regular way without creating the prepared statement.

articles-service.ts

import { Injectable } from '@nestjs/common';

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { and, gte, lt, sql } from 'drizzle-orm';

@Injectable()

export class ArticlesService {

constructor(private readonly drizzleService: DrizzleService) {}

getAllFromYesterday() {

return this.drizzleService.db

.select()

.from(databaseSchema.articles)

.where(

and(

gte(

databaseSchema.articles.createdAt,

sql`current_date - interval '1 day'`,

lt(databaseSchema.articles.createdAt, sql`current_date`),

);

}

// ...

}

To create a prepared statement with the Drizzle ORM, we need to call the prepare() function. When we do that, we can use it with the execute() function.

articles-service.ts

import { Injectable } from '@nestjs/common';

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { and, gte, lt, sql } from 'drizzle-orm';

@Injectable()

export class ArticlesService {

constructor(private readonly drizzleService: DrizzleService) {}

private getArticlesFromYesterdayStatement = this.drizzleService.db

.select()

.from(databaseSchema.articles)

.where(

and(

gte(

databaseSchema.articles.createdAt,

sql`current_date - interval '1 day'`,

lt(databaseSchema.articles.createdAt, sql`current_date`),

)

.prepare('get_articles_from_yesterday');

getAllFromYesterday() {

return this.getArticlesFromYesterdayStatement.execute();

}

// ...

}

Prepared statements with parameters

To create a prepared statement with parameters while using Drizzle ORM, we need to use the sql.placeholder function whenever we want to use a parameter.

articles-service.ts

import { Injectable } from '@nestjs/common';

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { and, gte, lt, sql } from 'drizzle-orm';

@Injectable()

export class ArticlesService {

constructor(private readonly drizzleService: DrizzleService) {}

private getArticlesBetweenDatesStatement = this.drizzleService.db

.select()

.from(databaseSchema.articles)

.where(

and(

gte(databaseSchema.articles.createdAt, sql.placeholder('startingDate')),

lt(databaseSchema.articles.createdAt, sql.placeholder('endingDate')),

)

.prepare('get_articles_between_dates');

// ...

}

Now, we need to use the execute() function and provide the parameters using the same names we provided when declaring the prepared statement.

articles-service.ts

import { Injectable } from '@nestjs/common';

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { and, gte, lt, sql } from 'drizzle-orm';

@Injectable()

export class ArticlesService {

constructor(private readonly drizzleService: DrizzleService) {}

private getArticlesBetweenDatesStatement = this.drizzleService.db

.select()

.from(databaseSchema.articles)

.where(

and(

gte(databaseSchema.articles.createdAt, sql.placeholder('startingDate')),

lt(databaseSchema.articles.createdAt, sql.placeholder('endingDate')),

)

.prepare('get_articles_between_dates');

getArticlesBetweenDates(startingDate: Date, endingDate: Date) {

return this.getArticlesBetweenDatesStatement.execute({

startingDate,

endingDate,

});

}

// ...

}

With the above approach, we can use prepared statements with parameters when using the Drizzle ORM.

Summary

In this article, we learned what prepared statements are and how to create them with PostgreSQL. In addition to using raw SQL, we created examples using the Drizzle ORM and NestJS.

Prepared statements are a valuable tool for optimizing our queries, but it does not mean we should always use them. They can improve performance when executing a large number of similar queries, especially if those queries are complex and would otherwise require a lot of time to parse, analyze, and plan. For example, queries involving multiple joins can benefit from prepared statements.

However, it’s important to note that prepared statements don’t speed up query execution. Preparing statements won’t improve performance if a query is simple to parse and analyze but takes a long time to execute. Because of that, we should use prepared statements sparingly. By knowing when to use them, we can improve query performance without adding unnecessary complexity.

Series Navigation<< API with NestJS #180. Organizing Drizzle ORM schema with PostgreSQL