API with NestJS #161. Generated columns with the Drizzle ORM and PostgreSQL

August 12, 2024

This entry is part 161 of 174 in the API with NestJS

In SQL, generated columns automatically calculate their values using data from other fields in the same table. This can help ensure data consistency, improve query performance, and simplify our database design in general. The SQL standard contains two types of generated columns.

Virtual generated columns

With virtual generated columns, we avoid using additional disk storage. Instead, the database calculates their value on demand, for example, during a SELECT query. However, PostgreSQL currently lacks support for the virtual generated columns.

Stored generated columns

The stored generated columns use storage like regular ones. However, PostgreSQL updates their values only when the row is modified, not every time they’re requested.

To create a stored generated column with the Drizzle ORM, we must use the generatedAlwaysAs function. It makes a lot of sense to refer to other columns while defining a generated column.

However, a generated column can’t refer to other generated columns.

database-schema.ts

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

import { SQL, sql } from 'drizzle-orm';

// ...

export const users = pgTable('users', {

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

email: text('email').unique().notNull(),

first_name: text('name').notNull(),

last_name: text('name').notNull(),

fullName: text('full_name').generatedAlwaysAs(

(): SQL => sql`${users.first_name} || ' ' || ${users.last_name}`,

});

export const databaseSchema = {

users,

// ...

};

In the above example, we defined the full_name column as a combination of the first_name and the last_name. Let’s test it by inserting a row.

INSERT INTO users(

email, first_name, last_name

)

VALUES (

'marcin@wanago.io', 'Marcin', 'Wanago'

);

As we can see above, PostgreSQL automatically filled the value of the full_name column.

Working with numbers

Besides operating on text, we can work with numbers as well.

database-schema.ts

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

import { SQL, sql } from 'drizzle-orm';

export const routes = pgTable('routes', {

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

distanceInKilometers: numeric('distance_in_kilometers').notNull(),

distanceInMiles: numeric('distance_in_miles').generatedAlwaysAs(

(): SQL => sql`${routes.distanceInKilometers} / 1.609344`,

});

// ...

export const databaseSchema = {

routes,

};

Using SQL functions

We can also use SQL functions, but they need to be immutable. An immutable function does not modify the database and always returns the same result given the same arguments.

database-schema.ts

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

import { SQL, sql } from 'drizzle-orm';

export const articles = pgTable('articles', {

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

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

paragraphs: text('paragraphs').array().notNull(),

paragraphs_number: numeric('paragraphs_number').generatedAlwaysAs(

(): SQL => sql`array_length(${articles.paragraphs}, 1)`,

});

// ...

export const databaseSchema = {

articles,

// ...

};

In the first example in this article, we combined the first_name and last_name columns using the || operator. Instead, we might think about using the concat function built into PostgreSQL.

database-schema.ts

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

import { SQL, sql } from 'drizzle-orm';

// ...

export const users = pgTable('users', {

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

email: text('email').unique().notNull(),

first_name: text('name').notNull(),

last_name: text('name').notNull(),

fullName: text('full_name').generatedAlwaysAs(

(): SQL => sql`concat(${users.first_name}, ' ', ${users.last_name})`,

});

export const databaseSchema = {

users,

// ...

};

Unfortunately, the concat function is not immutable. Surprisingly, it can give us different results based on the configuration of our database.

SET TIME ZONE 'UTC';

-- Returns "Current time: 2021-11-25 22:08:00.041641"

SELECT CONCAT('Current time: ', NOW()::TIMESTAMP);

SET TIME ZONE 'UTC+1';

-- Returns "Current time: 2021-11-25 21:08:00.041641"

SELECT CONCAT('Current time: ', NOW()::TIMESTAMP);

If you want to know more about timezones, check outAPI with NestJS #159. Date and time with PostgreSQL and the Drizzle ORM

Type-safety in Drizzle ORM

Thanks to how Drizzle ORM is written, TypeScript stops us if we try to insert a row while providing an explicit value for a generated column.

users.service.ts

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

import { UserDto } from './user.dto';

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

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

@Injectable()

export class UsersService {

constructor(private readonly drizzleService: DrizzleService) {}

async create(user: UserDto) {

const createdUsers = await this.drizzleService.db

.insert(databaseSchema.users)

.values({

firstName: user.firstName,

lastName: user.lastName,

email: user.email,

password: user.password,

fullName: `${user.firstName} ${user.lastName}`,

})

.returning();

return createdUsers.pop();

}

// ...

}

When we try the above code, we see the “No overload matches this call” error.

Simulating virtual generated columns

Although PostgreSQL does not natively support virtual generated columns, we can simulate this functionality in NestJS by serializing the data we send in our HTTP responses. One way to do that would be with the class-transformer library.

user-response.dto.ts

import { Expose, Transform } from 'class-transformer';

export class UserResponseDto {

id: number;

firstName: string;

lastName: string;

@Expose()

@Transform(({ obj }) => {

return `${obj.firstName} ${obj.lastName}`;

})

fullName: string;

}

The most straightforward way to create an instance of the UserResponseDto class is to use the @TransformPlainToInstance() decorator built into the class-transformer library.

users.controller.ts

import { Body, Controller, HttpCode, Post, Res } from '@nestjs/common';

import { AuthenticationService } from './authentication.service';

import { LogInDto } from './dto/log-in.dto';

import { Response } from 'express';

import { TransformPlainToInstance } from 'class-transformer';

import { UserResponseDto } from '../users/user-response.dto';

@Controller('authentication')

export class AuthenticationController {

constructor(private readonly authenticationService: AuthenticationService) {}

@HttpCode(200)

@Post('log-in')

@TransformPlainToInstance(UserResponseDto)

async logIn(

@Body() logInData: LogInDto,

@Res({ passthrough: true }) response: Response,

) {

const user =

await this.authenticationService.getAuthenticatedUser(logInData);

const cookie = this.authenticationService.getCookieWithJwtToken(user.id);

response.setHeader('Set-Cookie', cookie);

return user;

}

// ...

}

With this approach, the database does not store the fullName value in a column. Instead, it calculates it on the fly every time a user makes the HTTP request. We must remember, though, that PostgreSQL won’t know about the fullName field in this approach. Therefore, we can’t use it in any SQL queries.

Summary

Generated columns can be helpful if we frequently perform certain operations on our data. By storing the results ahead of time, we can improve our application’s performance. However, it’s important to note that this optimization can come at the expense of slower INSERT and UPDATE operations.

Generated columns can also be used when we refactor our database. If we need to modify some columns while maintaining backward compatibility, generated columns can help. All of the above make generated columns a feature worth knowing, particularly now that the Drizzle ORM has recently introduced support for them in PostgreSQL.

Series Navigation<< API with NestJS #160. Using views with the Drizzle ORM and PostgreSQLAPI with NestJS #162. Identity columns with the Drizzle ORM and PostgreSQL >>