Raw SQL queries with Prisma and PostgreSQL range types

December 4, 2023

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#

model Event {
  id        Int      @id @default(autoincrement())
  name      String
  startDate DateTime @db.Timestamptz
  endDate   DateTime @db.Timestamptz
}

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.

schema.prisma#

model Event {
  id        Int                      @id @default(autoincrement())
  name      String
  dateRange Unsupported("tstzrange")
}

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

npx prisma migrate dev --name add_event_table

migrate.sql#

-- CreateTable
CREATE TABLE "Event" (
    "id" SERIAL NOT NULL,
    "name" TEXT NOT NULL,
    "dateRange" tstzrange NOT NULL,
 
    CONSTRAINT "Event_pkey" PRIMARY KEY ("id")
);

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.

INSERT INTO "Event"(
  name, "dateRange"
)
VALUES (
 'Festival', '[2023-12-10 10:00, 2023-12-15 18:00)'
)
RETURNING *;

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#

import { IsString, IsNotEmpty, IsISO8601 } from "class-validator"
export class CreateEventDto {
  @IsString()
  @IsNotEmpty()
  name: string
  @IsISO8601({
    strict: true,
  })
  startDate: string
  @IsISO8601({
    strict: true,
  })
  endDate: string
}

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

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

import { Range, RANGE_LB_INC, RANGE_UB_INC, serialize } from "postgres-range"
 
const range = new Range("2023-12-10 10:00", "2023-12-15 18:00", RANGE_LB_INC | RANGE_UB_INC)
// '[2023-12-10 10:00, 2023-12-15 18:00)'
console.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.

events.service.ts#

import { Injectable, InternalServerErrorException } from "@nestjs/common"
import { PrismaService } from "../database/prisma.service"
import { Range, RANGE_LB_INC, RANGE_UB_INC, serialize } from "postgres-range"
import { CreateEventDto } from "./dto/create-event.dto"
@Injectable()
export class EventsService {
  constructor(private readonly prismaService: PrismaService) {}
  async create(eventData: CreateEventDto) {
    const range = new Range(eventData.startDate, eventData.endDate, RANGE_LB_INC | RANGE_UB_INC)
    const queryResponse = await this.prismaService.$queryRaw`
      INSERT INTO "Event"(
        name, "dateRange"  
      )
      VALUES (
        ${eventData.name},
        ${serialize(range)}::tstzrange
      )
      RETURNING id, name, "dateRange"::text
    `
    if (Array.isArray(queryResponse) && queryResponse.length === 1) {
      return queryResponse[0]
    }
    throw new InternalServerErrorException()
  }
}

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.

events.service.ts#

import { Injectable } from "@nestjs/common"
import { PrismaService } from "../database/prisma.service"
@Injectable()
export class EventsService {
  constructor(private readonly prismaService: PrismaService) {}
  getAll() {
    return this.prismaService.$queryRaw`
      SELECT id, name, "dateRange"::text FROM "Event"
    `
  } // ...
}

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.

event-response.dto.ts#

import { Exclude, Expose, Transform } from "class-transformer"
import { parse } from "postgres-range"
export class EventResponseDto {
  name: string
  @Exclude()
  dateRange: string
  @Expose()
  @Transform(({ obj }) => {
    const parsedRange = parse(obj.dateRange)
    return parsedRange.lower
  })
  startDate: string
  @Expose()
  @Transform(({ obj }) => {
    const parsedRange = parse(obj.dateRange)
    return parsedRange.upper
  })
  endDate: string
}

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.

events.controller.ts#

import { Body, Controller, Get, Post } from "@nestjs/common"
import { EventsService } from "./events.service"
import { CreateEventDto } from "./dto/create-event.dto"
import { EventResponseDto } from "./dto/event-response.dto"
import { TransformPlainToInstance } from "class-transformer"
@Controller("events")
export class EventsController {
  constructor(private readonly eventsService: EventsService) {}
  @Get()
  @TransformPlainToInstance(EventResponseDto)
  getAll() {
    return this.eventsService.getAll()
  }
  @Post()
  @TransformPlainToInstance(EventResponseDto)
  create(@Body() event: CreateEventDto) {
    return this.eventsService.create(event)
  }
}

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.

events.service.ts#

import { Injectable } from "@nestjs/common"
import { PrismaService } from "../database/prisma.service"
@Injectable()
export class EventsService {
  constructor(private readonly prismaService: PrismaService) {}
  search(date: string) {
    return this.prismaService.$queryRaw`
      SELECT id, name, "dateRange"::text FROM "Event"
      WHERE "dateRange" @> ${date}::timestamptz
    `
  } // ...
}

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.

find-events-params.dto.ts#

import { IsOptional, IsISO8601 } from "class-validator"
export class FindEventsParamsDto {
  @IsOptional()
  @IsISO8601({
    strict: true,
  })
  date?: string
}

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

events.controller.ts#

import { Controller, Get, Query } from "@nestjs/common"
import { EventsService } from "./events.service"
import { EventResponseDto } from "./dto/event-response.dto"
import { TransformPlainToInstance } from "class-transformer"
import { FindEventsParamsDto } from "./dto/find-events-params.dto"
@Controller("events")
export class EventsController {
  constructor(private readonly eventsService: EventsService) {}
  @Get()
  @TransformPlainToInstance(EventResponseDto)
  getAll(@Query() { date }: FindEventsParamsDto) {
    if (date) {
      return this.eventsService.search(date)
    }
    return this.eventsService.getAll()
  } // ...
}

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.