API with NestJS #136. Raw SQL queries with Prisma and PostgreSQL range types

NestJS SQL

This entry is part 136 of 158 in the API with NestJS

While Prisma gradually adds various features, PostgreSQL still has a lot of functionalities that Prisma does not support yet. One of them is range types. In this article, we learn how to use a column type not supported by Prisma and how to make raw SQL queries.

Range types

Sometimes, when working with our database, we might want to represent a range of values. For example, we might want to define a set of available dates. One way would be to create two columns that hold the bound values.

schema.prisma

Above, we use the timestamp with timezone data type. If you want to know more, check out API with NestJS #108. Date and time with Prisma and PostgreSQL

Unfortunately, this approach does not ensure data integrity. Nothing stops the user from storing the end date that happens before the start date.

Thankfully, PostgreSQL has various built-in range types that can make working with ranges a lot more straightforward.

Creating the migration

For example, to represent a range of timestamps with timezones, we need the type. Since Prisma does not support it, we must use the type. It allows us to define fields in the schema for types that are not yet supported.

schema.prisma

Let’s create a migration that adds the above table.

If you would like to read more about database migrations with Prisma, check out API with NestJS #115. Database migrations with Prisma

migrate.sql

As you can see, Prisma generated a correct migration despite not supporting the column.

Defining a range

Let’s allow the user to create an event with a date range.

Ranges in PostgreSQL

Ranges in PostgreSQL have the lower bound and the upper bound. Every value in between is considered to be within the range.

Notice that in the above code, we specify our range between the and characters.

The square brackets represent inclusive bounds. This means that the bound value is included in the range.

The round brackets represent exclusive bounds. Using them ensures that the bound value is not included in the range.

Considering that, we can see that our lower bound is inclusive, and our upper bound is exclusive.

Creating ranges with Prisma

Let’s require the user to provide the start and end dates separately in the request body. Let’s assume that all our event date ranges have inclusive bounds to keep the API straightforward.

create-event.dto.ts

Fortunately, we don’t have to create the range manually using the and properties. Instead, we can use the library.

A downside of the library is that the constructor is quite peculiar and requires us to pass a single number representing which bounds are inclusive or exclusive. The easiest way to do that is to use the bitwise OR operator with the and constants that represent the inclusive lower bound and inclusive upper bound.

We stringify the range using the function.

Since the data is not supported by Prisma, we need to use the tagged template that allows us to make a raw SQL query. It returns an array of results, but in our case, this array should have only one element.

events.service.ts

Prisma requires us to cast the serialized range using . If we don’t do that, it throws an error.
Simiarly, we need to cast the data returned by the database back to a string using .

A crucial thing about the is that it sends the SQL query to the database separately from the arguments, such as the , using parametrized queries to prevent SQL injection vulnerabilities.

Fetching existing data from the database

We must also make a raw SQL query to fetch the events from the database.

events.service.ts

Instead of sending the users the date ranges that are plain strings, let’s split it back into the start and end dates. To do that, we can use the library combined with the .

If you want to know more about response serialization, check out API with NestJS #112. Serializing the response with Prisma

event-response.dto.ts

Above, we use the function to parse the string into an instance of the class. It contains the and properties that represent our lower and upper bounds.

The most straightforward way of creating instances of our class is by using the decorator provided by the library.

events.controller.ts

The benefits of the range columns

Above, the user provides the start and end dates separately, and we store them in a column. While this adds quite a bit of work, it has its benefits. For example, PostgreSQL gives us various operators we can use with ranges.

One of the most important operators related to ranges is . With it, we can check if a range contains a particular value. We can use it to implement a search feature that returns all events happening on a particular date.

events.service.ts

We can improve the performance of this operation by creating a GiST index on the column. If you want to know more, check out API with NestJS #106. Improving performance through indexes with Prisma

Let’s allow users to provide the date they’re looking for through a query parameter. To make sure that they are using the correct format, we can use the library.

find-events-params.dto.ts

The last step is to use the new method and DTO in our controller.

events.controller.ts

Summary

In this article, we’ve shown how to use data types Prisma does not support yet, such as date ranges. To do that, we had to learn how to use the type built into Prisma and how to make raw SQL queries. By understanding how the data range type works, we used it to our advantage by using operators not available with other data types. Thanks to all of that, we’ve learned quite a few valuable skills we can use with other features from PostgreSQL that Prisma does not implement.

Series Navigation<< API with NestJS #135. Referential actions and foreign keys in PostgreSQL with PrismaAPI with NestJS #137. Recursive relationships with Prisma and PostgreSQL >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments