Raw SQL queries with Prisma and PostgreSQL range types

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.

1model Event {
2  id        Int      @id @default(autoincrement())
3  name      String
4  startDate DateTime @db.Timestamptz
5  endDate   DateTime @db.Timestamptz
6}

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 tstzrange type. Since Prisma does not support it, we must use the Unsupported type. It allows us to define fields in the schema for types that are not yet supported.

1model Event {
2  id        Int                      @id @default(autoincrement())
3  name      String
4  dateRange Unsupported("tstzrange")
5}

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

1npx prisma migrate dev --name add_event_table

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

1-- CreateTable
2CREATE TABLE "Event" (
3    "id" SERIAL NOT NULL,
4    "name" TEXT NOT NULL,
5    "dateRange" tstzrange NOT NULL,
6 
7    CONSTRAINT "Event_pkey" PRIMARY KEY ("id")
8);

As you can see, Prisma generated a correct migration despite not supporting the tstzrange 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.

1INSERT INTO "Event"(
2  name, "dateRange"
3)
4VALUES (
5 'Festival', '[2023-12-10 10:00, 2023-12-15 18:00)'
6)
7RETURNING *;

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.

1import { IsString, IsNotEmpty, IsISO8601 } from 'class-validator';
2 
3export class CreateEventDto {
4  @IsString()
5  @IsNotEmpty()
6  name: string;
7 
8  @IsISO8601({
9    strict: true,
10  })
11  startDate: string;
12 
13  @IsISO8601({
14    strict: true,
15  })
16  endDate: string;
17}

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

1npm install postgres-range

A downside of the postgres-range library is that the Range 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 RANGE_LB_INC and RANGE_UB_INC constants that represent the inclusive lower bound and inclusive upper bound.

1import { Range, RANGE_LB_INC, RANGE_UB_INC, serialize } from 'postgres-range';
2 
3const range = new Range(
4  '2023-12-10 10:00',
5  '2023-12-15 18:00',
6  RANGE_LB_INC | RANGE_UB_INC,
7);
8 
9// '[2023-12-10 10:00, 2023-12-15 18:00)'
10console.log(serialize(range));

We stringify the range using the serialize function.

Since the tstzrange data is not supported by Prisma, we need to use the $queryRaw 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.

1import { Injectable, InternalServerErrorException } from '@nestjs/common';
2import { PrismaService } from '../database/prisma.service';
3import { Range, RANGE_LB_INC, RANGE_UB_INC, serialize } from 'postgres-range';
4import { CreateEventDto } from './dto/create-event.dto';
5 
6@Injectable()
7export class EventsService {
8  constructor(private readonly prismaService: PrismaService) {}
9  
10  async create(eventData: CreateEventDto) {
11    const range = new Range(
12      eventData.startDate,
13      eventData.endDate,
14      RANGE_LB_INC | RANGE_UB_INC,
15    );
16 
17    const queryResponse = await this.prismaService.$queryRaw`
18      INSERT INTO "Event"(
19        name, "dateRange"  
20      )
21      VALUES (
22        ${eventData.name},
23        ${serialize(range)}::tstzrange
24      )
25      RETURNING id, name, "dateRange"::text
26    `;
27    if (Array.isArray(queryResponse) && queryResponse.length === 1) {
28      return queryResponse[0];
29    }
30    throw new InternalServerErrorException();
31  }
32}

Prisma requires us to cast the serialized range using ::tstzrange. 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 ::text.

A crucial thing about the $queryRaw is that it sends the SQL query to the database separately from the arguments, such as the eventData.name, 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.

1import { Injectable } from '@nestjs/common';
2import { PrismaService } from '../database/prisma.service';
3 
4@Injectable()
5export class EventsService {
6  constructor(private readonly prismaService: PrismaService) {}
7 
8  getAll() {
9    return this.prismaService.$queryRaw`
10      SELECT id, name, "dateRange"::text FROM "Event"
11    `;
12  }
13 
14  // ...
15}

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 class-transformer library combined with the postgres-range.

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

1import { Exclude, Expose, Transform } from 'class-transformer';
2import { parse } from 'postgres-range';
3 
4export class EventResponseDto {
5  name: string;
6 
7  @Exclude()
8  dateRange: string;
9 
10  @Expose()
11  @Transform(({ obj }) => {
12    const parsedRange = parse(obj.dateRange);
13    return parsedRange.lower;
14  })
15  startDate: string;
16 
17  @Expose()
18  @Transform(({ obj }) => {
19    const parsedRange = parse(obj.dateRange);
20    return parsedRange.upper;
21  })
22  endDate: string;
23}

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

The most straightforward way of creating instances of our EventResponseDto class is by using the @TransformPlainToInstance() decorator provided by the class-transformer library.

1import { Body, Controller, Get, Post } from '@nestjs/common';
2import { EventsService } from './events.service';
3import { CreateEventDto } from './dto/create-event.dto';
4import { EventResponseDto } from './dto/event-response.dto';
5import { TransformPlainToInstance } from 'class-transformer';
6 
7@Controller('events')
8export class EventsController {
9  constructor(private readonly eventsService: EventsService) {}
10 
11  @Get()
12  @TransformPlainToInstance(EventResponseDto)
13  getAll() {
14    return this.eventsService.getAll();
15  }
16 
17  @Post()
18  @TransformPlainToInstance(EventResponseDto)
19  create(@Body() event: CreateEventDto) {
20    return this.eventsService.create(event);
21  }
22}

The benefits of the range columns

Above, the user provides the start and end dates separately, and we store them in a tstzrange 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.

1import { Injectable } from '@nestjs/common';
2import { PrismaService } from '../database/prisma.service';
3 
4@Injectable()
5export class EventsService {
6  constructor(private readonly prismaService: PrismaService) {}
7 
8  search(date: string) {
9    return this.prismaService.$queryRaw`
10      SELECT id, name, "dateRange"::text FROM "Event"
11      WHERE "dateRange" @> ${date}::timestamptz
12    `;
13  }
14 
15  // ...
16}

We can improve the performance of this operation by creating a GiST index on the dateRange 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 class-validator library.

1import { IsOptional, IsISO8601 } from 'class-validator';
2 
3export class FindEventsParamsDto {
4  @IsOptional()
5  @IsISO8601({
6    strict: true,
7  })
8  date?: string;
9}

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

1import { Controller, Get, Query } from '@nestjs/common';
2import { EventsService } from './events.service';
3import { EventResponseDto } from './dto/event-response.dto';
4import { TransformPlainToInstance } from 'class-transformer';
5import { FindEventsParamsDto } from './dto/find-events-params.dto';
6 
7@Controller('events')
8export class EventsController {
9  constructor(private readonly eventsService: EventsService) {}
10 
11  @Get()
12  @TransformPlainToInstance(EventResponseDto)
13  getAll(@Query() { date }: FindEventsParamsDto) {
14    if (date) {
15      return this.eventsService.search(date);
16    }
17    return this.eventsService.getAll();
18  }
19 
20  // ...
21}

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 Unsupported 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.