API with NestJS #139. Using UUID as primary keys with Prisma and PostgreSQL

NestJS SQL

This entry is part 139 of 158 in the API with NestJS

Each record in our database should have a unique identifier. Typically, we use a numerical sequence to generate them. However, we can use an alternative approach that includes Universally Unique Identifiers (UUID). In this article, we discuss their advantages and disadvantages and implement them in a project with NestJS, Prisma, and PostgreSQL.

For the full code from this article check out this repository.

The idea behind UUID

A Universally Unique Identifier is a number represented using the hexadecimal system. While we’re used to the decimal system, which uses ten symbols (0-9) for values, there are alternatives. For example, the binary system uses just two symbols (0 and 1), and the hexadecimal system uses sixteen symbols, ranging from 0 to 9 and then A to F.

The hexadecimal system is good at representing very big numbers. It is vital in the context of UUID because it can contain a number that’s over 340 billion. One undecillion is a number equal to 1, followed by 36 zeros.

By using the hexadecimal system, we can shorten the representation of 340 undecillion from to .

Hexadecimal numbers are often prefixed with to indicate that they use the hexadecimal system.

To make it more readable, we store UUIDs using dashes that divide them into five groups, such as . In the hexadecimal notation, both uppercase and lowercase are valid and represent the same values, but UUIDs usually use lowercase.

UUIDs are globally unique

There are various algorithms we can use to generate the UUID. Some consider aspects such as the current time and the machine’s MAC address.

The MAC (Media Access Control) address is a unique identifier assigned to every device that connects to the network.

However, the most common specification for generating UUIDs is labeled version 4 (v4) and generates IDs using pseudo-random numbers.

Most computer systems generate pseudo-random numbers rather than truly random numbers, due to the deterministic nature of computers.

While it is theoretically possible to generate the same UUID more than once, the chances are low enough to be ignored by most applications. If we generate 103 trillion v4 UUIDs, the chance of finding a duplicate is approximately one in a billion. This is thanks to the huge number of possible values that the v4 UUID algorithm can generate.

Benefits of UUIDs

Since UUIDs are designed to be globally unique, we won’t find duplicates across different tables, databases, and even systems. This has several benefits, such as the possibility to merge data from multiple sources without worrying about colliding IDs. This also means that various distributed systems can generate UUIDs independently without the risk of duplication.

UUIDs don’t reveal any information about our data, such as the number of records, as opposed to incremental numeric IDs. This makes it practically impossible for attackers to guess the ID of a particular record. While relying solely on security by obscurity is not a good practice, some might consider this a benefit.

Thanks to the UUIDs being unique across all systems, they can make debugging and tracing more straightforward. If we see a particular UUID in our logs, we can find the associated database row even if we don’t know which database table it comes from.

Downsides of UUIDs

Unfortunately, a single UUID takes 16 bytes and is larger than a traditional integer ID that typically takes 4 or 8 bytes. This can lead to more storage usage and potentially hurt our performance. Also, generating UUIDs requires more computational resources than generating regular sequential IDs.

Besides the above, UUIDs can be harder to read because they are longer and random as opposed to sequentially generated IDs.

Implementing UUIDs with Prisma

To start using UUID with Prisma, we must define the primary key as a string and set up its default value using the function.

The primary key is a unique identifier of each record in the table. No two rows can have the same values as the primary key.

schema.prisma

Let’s generate a migration that creates the table for our model.

If you want to know more about migrations with Prisma, check out API with NestJS #115. Database migrations with Prisma

migration.sql

The migration shows us that the UUID values are not generated on the database level because they are not mentioned in the migration. Instead, they are generated by the Prisma’s query engine.

We also need to use strings instead of numbers in our controller when we expect the user to provide the ID.

articles.controller.ts

We need to make sure to adjust the types in the service as well.

When we make a request to create the article, we can see that Prisma generates a valid UUID for us.

Generating UUIDs through PostgreSQL

So far, we’ve relied on Prisma to generate the UUID value. Instead, we can let PostgreSQL do that for us. For it to work, we need to use the pgcrypto extension.

schema.prisma

Once we’ve got that, we can use to generate UUIDs through PostgreSQL. Let’s add to change the type of the column from to to make it more storage-efficient.

schema.prisma

Let’s now generate the migration to apply the above changes.

Unfortunately, Prisma generates a migration that removes the existing column and recreates it.

migration.sql

This would cause our IDs to be recreated. Let’s rewrite our migration to prevent that.

migration.sql

With the above approach, we reuse the existing IDs instead of recreating them.

There are a few benefits of generating UUIDs through PostgreSQL. By handling it at the database level, we ensure consistency if more than one application connects to the database. This also applies when we interact with our database through pgAdmin or raw queries.

This way, we don’t have to provide the ID value manually.

Summary

In this article, we explored the Universally Unique Identifiers (UUIDs) as an alternative to traditional numerical sequences. We learned how they are generated and provided examples using NestJS, Prisma, and PostgreSQL. This included generating UUIDs through Prisma and, alternatively, through PostgreSQL. Thanks to discussing their advantages and disadvantages, we now know when and if UUIDs are worth implementing.

Series Navigation<< API with NestJS #138. Filtering records with PrismaAPI with NestJS #140. Using multiple PostgreSQL schemas with Prisma >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments