API with NestJS #165. Time intervals with the Drizzle ORM and PostgreSQL

September 9, 2024

This entry is part 165 of 168 in the API with NestJS

Sometimes, in our application, we would like to define a specific duration of time. We could represent it as a number of seconds, for example. However, we might want to be more flexible and be able to use various units, such as minutes, hours, days, or weeks. In this article, we learn how to achieve that with PostgreSQL and the Drizzle ORM using intervals.

Defining an interval

Fortunately, the Drizzle ORM supports the interval column type built into PostgreSQL. Let’s use it.

database-schema.ts

import { serial, text, pgTable, interval } from 'drizzle-orm/pg-core';

export const food = pgTable('food', {

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

expiryAfterOpening: interval('expiry_after_opening'),

});

export const databaseSchema = {

food,

};

Validating input data

We must ensure users use the correct data format before letting them add rows to our new table. ISO 8601 is a popular format for dates and time-related data. An interval defined with ISO 8601 starts with the letter P followed by the interval value. The letter T separates the date from the time.

An example of a valid ISO interval is P2Y3M4DT5H6M7S.

Years	Y
Months / Minutes	M
Weeks	W
Days	D
Hours	H
Seconds	S

We can use the M letter either to indicate minutes or months depending on whether we use it before or after T.

The table above shows that P2Y3M4DT5H6M7S means 2 years, 3 months, 4 days, 5 hours, 6 minutes, and 7 seconds.

Let’s validate the data users send through our API before putting it into our database. Unfortunately, JavaScript does not support ISO intervals out of the box. We can use a date library such as Luxon to create a custom validator with the class-validator library to address this.

IsIsoInterval.ts

import {

ValidationArguments,

ValidatorConstraint,

ValidatorConstraintInterface,

} from 'class-validator';

import { Duration } from 'luxon';

@ValidatorConstraint()

export class IsIsoInterval implements ValidatorConstraintInterface {

validate(value: unknown) {

if (typeof value !== 'string') {

return false;

}

const interval = Duration.fromISO(value);

return interval.isValid;

}

defaultMessage({ property }: ValidationArguments) {

return `${property} must be a valid ISO duration`;

}

We can now use the custom validator in our DTO.

CreateFood.dto.ts

import { IsString, IsNotEmpty, Validate } from 'class-validator';

import { IsIsoInterval } from './IsIsoInterval';

export class CreateFoodDto {

@IsString()

@IsNotEmpty()

@IsString()

@IsNotEmpty()

@Validate(IsIsoInterval)

expiryAfterOpening: string;

}

This approach allows us to respond with the 400 Bad Request status if the user provides an invalid interval.

Interval formats built into PostgreSQL

Let’s make an HTTP request and create a row in our table.

postgres

The database returned the interval in a format different than ISO. By default, PostgreSQL represents the intervals using a format called postgres. We can check that out by looking at the IntervalStyle value.

The postgres format explicitly specifies years, months, and days followed by the time in the hh:mm:ss format.

We use ::INTERVAL to convert a string to an interval.

We can go a step further and specify microseconds, milliseconds, weeks, decades, centuries, or even millennia. Moreover, we can use the - sign to negate a part of the value.

Alternatively, we can use the ago keyword to negate all parts of the date to achieve a negative interval.

postgres_verbose

Another format is postgres_verbose, where hh:mm:ss is replaced with hours, minutes, and seconds stated explicitly. To change IntervalStyle to postgres_verbose in a particular session, we need to run the SET IntervalStyle = 'postgres_verbose' command in our PostgreSQL database.

In postgresql_verbose, the same rules apply when dealing with negative intervals as with the postgres format.

sql_standard

Alternatively, PostgreSQL also supports the sql_standard interval output format. When we use it, PostgreSQL outputs the intervals using the SQL standard.

It starts with the years and months separated by a dash, followed by the number of days and the time separated by spaces.

To create a negative interval, we must use the - sign instead of + next to every section we want to negate.

iso_8601

Fortunately, we can also use the ISO 8601 format, which we explained at the beginning of our article. To change the interval format in our database permanently for all database connections, we need to run the following command

1	ALTER DATABASE nestjs SET IntervalStyle = 'iso_8601';

where nestjs is the name of our database. Once we do that, PostgreSQL starts using the ISO format for intervals.

To define a negative interval, we must use the - sign before every part of our interval that we want to negate.

Working with intervals

Thanks to changing the interval format in our database to iso_8601 permanently, it also affects our Drizzle ORM queries.

While Drizzle ORM queries the interval as a simple string, we can use Luxon to parse it and interact with it.

food.service.ts

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

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

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

import { eq } from 'drizzle-orm';

import { UpdateFoodDto } from './dto/update-food.dto';

import { Duration } from 'luxon';

@Injectable()

export class FoodService {

constructor(private readonly drizzleService: DrizzleService) {}

async decreaseExpirationByOneDay(id: number) {

const food = await this.getById(id);

const interval = Duration.fromISO(food.expiryAfterOpening);

const newInterval = interval.minus({ days: 1 });

await this.update(id, {

expiryAfterOpening: newInterval.toISO(),

});

}

async getById(id: number) {

const foodResults = await this.drizzleService.db

.select()

.from(databaseSchema.food)

.where(eq(databaseSchema.food.id, id));

const food = foodResults.pop();

if (!food) {

throw new NotFoundException();

}

return food;

}

async update(id: number, food: UpdateFoodDto) {

const updatedFoods = await this.drizzleService.db

.update(databaseSchema.food)

.set({

expiryAfterOpening: food.expiryAfterOpening,

})

.where(eq(databaseSchema.food.id, id))

.returning();

if (updatedFoods.length === 0) {

throw new NotFoundException();

}

return updatedFoods.pop();

}

For example, in the decreaseExpirationByOneDay method, we decrease the expiration interval by one day. To do that, we used the minus method built into Luxon. If you want to know more about how you can interact with intervals with Luxon, check out the official documentation.

Functions and operators in PostgreSQL

PostgreSQL allows us to perform various operations with intervals. For example, we can subtract them from dates.

We can also add and subtract intervals from each other.

Additionally, we can use math to multiply and divide the intervals.

Summary

In this article, we explored the interval data type in PostgreSQL and learned how to use it with Drizzle ORM. To do that, we had to get familiar with various interval formats built into PostgreSQL. Additionally, we used the Luxon library to implement validation in our API and to interact with the intervals in our TypeScript code. Thanks to combining the intervals functionality built into PostgreSQL with Luxon’s date and time handling, we were able to manage the interval data effectively.

Series Navigation<< API with NestJS #164. Improving the performance with indexes using Drizzle ORMAPI with NestJS #166. Logging with the Drizzle ORM >>