# Using UUID Primary Keys with RedwoodJS, Prisma, and PostgreSQL

Our goal is to leverage UUIDs as primary keys for all of our tables. By default, PostgreSQL, and by association, Prisma and RedwoodJS, use an auto incrementing integer column as the primary key. We have a little bit of setup to perform to prepare the database to accept UUIDs instead of integers.

First, we need to decide how we want to generate UUIDs. Prisma offers a helpful function, uuid, which we can define in our Prisma schema as the default value for our id field. Meanwhile, PostgreSQL offers similar functions, such as `gen_random_uuid()` which is made available from the pgcrypto extension.

## Assigning the UUID Data Type in the Prisma Schema

Regardless of which technique generates your UUIDs, you’ll want to define the native UUID database type instead of a string. By default, the Prisma client will store UUIDs as a string column, but PostgreSQL offers a distinct data type specifically for UUIDs. Prisma can enable this by adding `@db.Uuid` as the data type.

```html
id String @id default(uuid()) @db.Uuid
```

‍

## Generating UUIDs with Prisma

Prisma exposes a `uuid()` function which makes it very simple to generate UUIDs for your primary and foreign key fields. In the below example, Prisma defines the `Album` model’s primary key as

- Table column name of `id`
- Javascript type of String
- Using the Prisma `@id` directive to specify the database unique primary key
- With a default version 4 UUID value, generated by Prisma
- PostgreSQL UUID column data type

```html
model Album {
 id String @id @default(uuid()) @db.Uuid
 name String

 songs Song[]
}
```

‍

Defining a foreign key for a related model is very similar.

- Table column name of associated model `albumId`
- Javascript type of String
- PostgreSQL UUID column data type

```html
model Song {
 id String @id @default(uuid()) @db.Uuid
 name String

 albumId String @db.Uuid
 album Album @relation(fields: [albumId], references: [id])
}

```

‍

And it’s that simple. RedwoodJS does not require any special setup and will resolve routes using UUIDs just like with integers.

## Generating UUIDs with PostgreSQL

Alternatively, you can have PostgreSQL generate the UUIDs. However, there is slightly more setup required.

In order to generate UUIDs in PostgreSQL we need to enable a UUID extension and for this example, we will use `pgcrypto`. First, we need to create a RedwoodJS migration to enable the extension.

```html
yarn rw prisma migrate dev --create-only --name enable_pgcrypto

```

‍

In your newly generated `migration.sql` file, add the following

```html
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

```

‍

Using the same example model definition from above, we generate the default id using the Prisma function, `dbgenerated()` to delegate the UUID generation to `pgcrypto`.

```html
model Album {
 id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
 name String

 songs Song[]
}

model Song {
 id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
 name String

 albumId String @db.Uuid
 album Album @relation(fields: [albumId], references: [id])
}

```

‍

Both of the above use version 4 UUID. Alternatively, if you need to use a different version of UUID, you should use the PostgreSQL extension uuid-ossp. This extension enables you to generate multiple versions of UUIDs.

At the time of writing, the Prisma function `uuid()` only generates version 4 UUIDs. If you need to use a different version then you will need to use the database-generated strategy using the `uuid-ossp` extension.

Have a great day and remember to RedwoodJS all the things.

‍

This article has been written in collaboration with Jesse House.

Photo by Jan Antonin Kolar on Unsplash