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

August 19, 2024

This entry is part 162 of 174 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

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

export const articles = pgTable('articles', {

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

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

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

});

export const databaseSchema = {

articles,

};

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 serial type creates a sequence with a maximum value of 2147483647. If we need more, we can use the bigserial type instead, which gives us a sequence with a maximum value of over nine quintillions.

database-schema.ts

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

export const articles = pgTable('articles', {

id: bigserial('id', { mode: 'number' }).primaryKey(),

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

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

});

export const databaseSchema = {

articles,

};

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

With the cache option, we can specify how many numbers should be preallocated in the memory for faster access. When using the serial 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 id column in the articles 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 nextval. 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 nextval('articles_id_seq'). However, this can lead to some issues.

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

await this.drizzleService.db

.insert(databaseSchema.articles)

.values({

id: 10-,

title: 'Hello world',

content: 'My article',

})

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 setval 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 generatedByDefaultAsIdentity() function to create them with the Drizzle ORM.

database-schema.ts

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

export const articles = pgTable('articles', {

id: integer('id').primaryKey().generatedByDefaultAsIdentity(),

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

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

});

export const databaseSchema = {

articles,

};

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

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

export const articles = pgTable('articles', {

id: integer('id').primaryKey().generatedAlwaysAsIdentity(),

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

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

});

export const databaseSchema = {

articles,

};

Thanks to using the generatedAlwaysAsIdentity 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 OVERRIDING SYSTEM VALUE statement.

database-schema.ts

INSERT INTO articles (

id, title, content

)

OVERRIDING SYSTEM VALUE

VALUES (

100,

'Hello world',

'My article'

);

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 generatedAlwaysAsIdentity 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 >>