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

JavaScript

This entry is part 161 of 177 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 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 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

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

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

Working with numbers

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

database-schema.ts

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

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

database-schema.ts

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

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

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

user-response.dto.ts

The most straightforward way to create an instance of the class is to use the decorator built into the library.

users.controller.ts

With this approach, the database does not store the 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 and  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 >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments