Using UUID as primary keys with Prisma and PostgreSQL

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 340,000,000,000,000,000,000,000,000,000,000,000,000 to 0xFFC99E3C66FD68D2206F414000000000.

Hexadecimal numbers are often prefixed with 0x 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 ffc99e3c-66fd-68d2-206f-414000000000. 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 uuid() 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.

1model Article {
2  id      String  @id @default(uuid())
3  title   String
4  content String?
5}
6 
7// ...

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

1npx prisma migrate dev --name create-article-table

1-- CreateTable
2CREATE TABLE "Article" (
3    "id" TEXT NOT NULL,
4    "title" TEXT NOT NULL,
5    "content" TEXT,
6 
7    CONSTRAINT "Article_pkey" PRIMARY KEY ("id")
8);

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.

1import {
2  Body,
3  Controller,
4  Delete,
5  Get,
6  Param,
7  Patch,
8  Post,
9} from '@nestjs/common';
10import { ArticlesService } from './articles.service';
11import { CreateArticleDto } from './dto/create-article.dto';
12import { UpdateArticleDto } from './dto/update-article.dto';
13 
14@Controller('articles')
15export default class ArticlesController {
16  constructor(private readonly articlesService: ArticlesService) {}
17 
18  @Get()
19  getAll() {
20    return this.articlesService.getAll();
21  }
22 
23  @Get(':id')
24  getById(@Param('id') id: string) {
25    return this.articlesService.getById(id);
26  }
27 
28  @Post()
29  create(@Body() article: CreateArticleDto) {
30    return this.articlesService.create(article);
31  }
32 
33  @Patch(':id')
34  update(@Param('id') id: string, @Body() article: UpdateArticleDto) {
35    return this.articlesService.update(id, article);
36  }
37 
38  @Delete(':id')
39  async delete(@Param('id') id: string) {
40    await this.articlesService.delete(id);
41  }
42}

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.

1generator client {
2  provider        = "prisma-client-js"
3  previewFeatures = ["postgresqlExtensions"]
4}
5 
6datasource db {
7  provider   = "postgresql"
8  url        = env("DATABASE_URL")
9  extensions = [pgcrypto]
10}
11 
12// ...

Once we’ve got that, we can use dbgenerated("gen_random_uuid()") to generate UUIDs through PostgreSQL. Let’s add @db.Uuid to change the type of the id column from TEXT to UUID to make it more storage-efficient.

1model Article {
2  id      String  @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
3  title   String
4  content String?
5}
6 
7// ...

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

1npx prisma migrate dev --create-only --name generate-uuid-through-database

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

1/*
2  Warnings:
3 
4  - The primary key for the `Article` table will be changed. If it partially fails, the table could be left without primary key constraint.
5  - The `id` column on the `Article` table would be dropped and recreated. This will lead to data loss if there is data in the column.
6 
7*/
8-- CreateExtension
9CREATE EXTENSION IF NOT EXISTS "pgcrypto";
10 
11-- AlterTable
12ALTER TABLE "Article" DROP CONSTRAINT "Article_pkey",
13DROP COLUMN "id",
14ADD COLUMN     "id" UUID NOT NULL DEFAULT gen_random_uuid(),
15ADD CONSTRAINT "Article_pkey" PRIMARY KEY ("id");

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

1-- CreateExtension
2CREATE EXTENSION IF NOT EXISTS "pgcrypto";
3 
4-- AlterTable
5ALTER TABLE "Article"
6ALTER COLUMN "id" TYPE UUID USING id::UUID,
7ALTER COLUMN "id" SET DEFAULT gen_random_uuid();

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.