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.
1import { serial, text, pgTable } from 'drizzle-orm/pg-core';
2import { SQL, sql } from 'drizzle-orm';
3
4// ...
5
6export const users = pgTable('users', {
7 id: serial('id').primaryKey(),
8 email: text('email').unique().notNull(),
9 first_name: text('name').notNull(),
10 last_name: text('name').notNull(),
11 fullName: text('full_name').generatedAlwaysAs(
12 (): SQL => sql`${users.first_name} || ' ' || ${users.last_name}`,
13 ),
14});
15
16export const databaseSchema = {
17 users,
18 // ...
19};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.
1INSERT INTO users(
2 email, first_name, last_name
3)
4VALUES (
5 'marcin@wanago.io', 'Marcin', 'Wanago'
6);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.
1import { serial, pgTable, numeric } from 'drizzle-orm/pg-core';
2import { SQL, sql } from 'drizzle-orm';
3
4export const routes = pgTable('routes', {
5 id: serial('id').primaryKey(),
6 distanceInKilometers: numeric('distance_in_kilometers').notNull(),
7 distanceInMiles: numeric('distance_in_miles').generatedAlwaysAs(
8 (): SQL => sql`${routes.distanceInKilometers} / 1.609344`,
9 ),
10});
11
12// ...
13
14export const databaseSchema = {
15 routes,
16};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.
1import { serial, text, pgTable, numeric } from 'drizzle-orm/pg-core';
2import { SQL, sql } from 'drizzle-orm';
3
4export const articles = pgTable('articles', {
5 id: serial('id').primaryKey(),
6 title: text('title').notNull(),
7 paragraphs: text('paragraphs').array().notNull(),
8 paragraphs_number: numeric('paragraphs_number').generatedAlwaysAs(
9 (): SQL => sql`array_length(${articles.paragraphs}, 1)`,
10 ),
11});
12
13// ...
14
15export const databaseSchema = {
16 articles,
17 // ...
18};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.
1import { serial, text, pgTable } from 'drizzle-orm/pg-core';
2import { SQL, sql } from 'drizzle-orm';
3
4// ...
5
6export const users = pgTable('users', {
7 id: serial('id').primaryKey(),
8 email: text('email').unique().notNull(),
9 first_name: text('name').notNull(),
10 last_name: text('name').notNull(),
11 fullName: text('full_name').generatedAlwaysAs(
12 (): SQL => sql`concat(${users.first_name}, ' ', ${users.last_name})`,
13 ),
14});
15
16export const databaseSchema = {
17 users,
18 // ...
19};Unfortunately, the concat function is not immutable. Surprisingly, it can give us different results based on the configuration of our database.
1SET TIME ZONE 'UTC';
2
3-- Returns "Current time: 2021-11-25 22:08:00.041641"
4SELECT CONCAT('Current time: ', NOW()::TIMESTAMP);
5
6SET TIME ZONE 'UTC+1';
7
8-- Returns "Current time: 2021-11-25 21:08:00.041641"
9SELECT 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.
1import { Injectable } from '@nestjs/common';
2import { UserDto } from './user.dto';
3import { DrizzleService } from '../database/drizzle.service';
4import { databaseSchema } from '../database/database-schema';
5
6@Injectable()
7export class UsersService {
8 constructor(private readonly drizzleService: DrizzleService) {}
9
10 async create(user: UserDto) {
11 const createdUsers = await this.drizzleService.db
12 .insert(databaseSchema.users)
13 .values({
14 firstName: user.firstName,
15 lastName: user.lastName,
16 email: user.email,
17 password: user.password,
18 fullName: `${user.firstName} ${user.lastName}`,
19 })
20 .returning();
21
22 return createdUsers.pop();
23 }
24
25 // ...
26}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.
1import { Expose, Transform } from 'class-transformer';
2
3export class UserResponseDto {
4 id: number;
5
6 firstName: string;
7
8 lastName: string;
9
10 @Expose()
11 @Transform(({ obj }) => {
12 return `${obj.firstName} ${obj.lastName}`;
13 })
14 fullName: string;
15}The most straightforward way to create an instance of the UserResponseDto class is to use the @TransformPlainToInstance() decorator built into the class-transformer library.
1import { Body, Controller, HttpCode, Post, Res } from '@nestjs/common';
2import { AuthenticationService } from './authentication.service';
3import { LogInDto } from './dto/log-in.dto';
4import { Response } from 'express';
5import { TransformPlainToInstance } from 'class-transformer';
6import { UserResponseDto } from '../users/user-response.dto';
7
8@Controller('authentication')
9export class AuthenticationController {
10 constructor(private readonly authenticationService: AuthenticationService) {}
11
12 @HttpCode(200)
13 @Post('log-in')
14 @TransformPlainToInstance(UserResponseDto)
15 async logIn(
16 @Body() logInData: LogInDto,
17 @Res({ passthrough: true }) response: Response,
18 ) {
19 const user =
20 await this.authenticationService.getAuthenticatedUser(logInData);
21 const cookie = this.authenticationService.getCookieWithJwtToken(user.id);
22 response.setHeader('Set-Cookie', cookie);
23 return user;
24 }
25
26 // ...
27}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.