API with NestJS #162. Identity columns with the Drizzle ORM and PostgreSQL

NestJS

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

Most of our SQL tables contain the ID column, which acts as a primary key and uniquely identifies each row. Historically, the most common way of defining them in PostgreSQL was with the serial type. However, nowadays, the official documentation advises against them. In this article, we compare the serial type and its alternative, the identity columns.

Serial type

With the serial data type, we can generate a unique integer automatically for each row. Therefore, it used to be the number one choice for declaring primary keys in our tables.

database-schema.ts

Sequences

When we create a column that uses the serial type, the database creates a sequence under the hood. It can generate a series of integer numbers.

By default, using the type creates a sequence with a maximum value of 2147483647. If we need more, we can use the type instead, which gives us a sequence with a maximum value of over nine quintillions.

database-schema.ts

In JavaScript, the biggest possible regular number is 253 – 1. If we expect our serial nubers not to be bigger than that, we can use the option. Otherwise, we should use that uses BigInt to work with values too large to be represented by a regular number.

With the option, we can specify how many numbers should be preallocated in the memory for faster access. When using the type, the default cache value is 1.

Since our sequence is not cycled, getting a number from our sequence after reaching the maximum causes an error. If we were to create a cycled sequence, it would start from the beginning after reaching the end.

Our sequence is owned by the column in the database. If we ever drop the associated column or the whole table, we delete the sequence as well.

Working with sequences

The most important function related to sequences is . It returns the next value in the sequence and increments the current value. This is why PostgreSQL configures our serial column to have a default value of . However, this can lead to some issues.

For instance, nothing prevents us from manually providing the ID when adding a row.

Above, we added an article with ID 100. However, there is a chance that our sequence hasn’t reached this number yet. If we return to relying on the default sequence value, it might eventually give us the number 100 and cause an error because an article with this ID already exists.

ERROR: duplicate key value violates unique constraint “articles_pkey”
DETAIL: Key (id)=(100) already exists.

We can manually change the current value using the function, which can cause similar errors.

The identity columns

Let’s try to solve the above problems using identity columns.

Generated by default

The first type of identity columns are the ones that provide a default value. We must use the function to create them with the Drizzle ORM.

database-schema.ts

When we create an identity column, an implicit sequence is attached to it.

The crucial thing about this type of identity column is that we still can force the ID value and don’t use our sequence.

Generated always

To change this behavior, we can create our identity column differently.

database-schema.ts

Thanks to using the function, trying to define the ID explicitly results in an error.

ERROR: cannot insert a non-DEFAULT value into column “id”
DETAIL: Column “id” is an identity column defined as GENERATED ALWAYS.

If we ever need to, we could still force an ID using the statement.

database-schema.ts

Benefits of the identity columns

The serial data type is not a part of the official SQL standard and might not be implemented by every database type. On the other hand, the identity columns follow the SQL standard, so our database schema is more portable across different types of databases.

The identity columns also give us more control over the IDs in our database. Thanks to using the function, we can prevent our application from inserting incorrect ID values. This adds an extra layer of protection and ensures our data is consistent and reliable.

Summary

In this article, we’ve reviewed both the serial data type and the identity columns. To understand how they work, we also had to learn more about sequences. Thanks to the benefits of the identity columns and the fact that the official PostgreSQL documentation recommends them, it makes sense to switch. Doing that aligns our schema with modern best practices.

Series Navigation<< API with NestJS #161. Generated columns with the Drizzle ORM and PostgreSQLAPI with NestJS #163. Full-text search with the Drizzle ORM and PostgreSQL >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments