Prisma with psql db - incompatible types: text and uuid

Question

I am trying to learn how to use prisma with a psql database.

I'm running into an issue using references where the id is a uuid string.

I have a user model with:

model User {
  id        String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  request   Request?
  createdAt DateTime @default(now()) @db.Timestamptz(6)
  updatedAt DateTime @default(now()) @updatedAt @db.Timestamptz(6)
}

model Request {
  id                Int @id @default(autoincrement())
  user              User   @relation(fields: [id], references: [id])
  // I also tried making the relation field userId
  createdAt         DateTime @default(now()) @db.Timestamptz(6)
  updatedAt         DateTime @default(now()) @updatedAt @db.Timestamptz(6)
}

When I try to migrate this, I get an error that says:

failed to apply cleanly to the shadow database. Error: db error: ERROR: foreign key constraint "Request_userId_fkey" cannot be implemented DETAIL: Key columns "userId" and "id" are of incompatible types: text and uuid.

The prisma documents dont show an example using uuid.

The example they do give has a second parameter in the Profile model which has a userId as an Int. I tried adding this to my Request model (as an int, as a string and as a uuid). None of these worked.

model User {
  id      Int      @id @default(autoincrement())
  email   String   @unique
  name    String?
  role    Role     @default(USER)
  posts   Post[]
  profile Profile?
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String
  user   User   @relation(fields: [userId], references: [id])
  userId Int
}

How can I reference a userId when it is generated using uuid?

This segment of the prisma documentation suggests (if I have understood it correctly), that any of String or Int or enum should work to recognise a uuid:

Relational databases Corresponding database type: PRIMARY KEY

Can be annotated with a @default() value that uses functions to auto-generate an ID:

autoincrement() cuid() uuid() Can be defined on any scalar field (String, Int, enum)

When I try adding the pgcrypto extension to psql, I try to run the migration again and get an error that has less verbose messaging, but still similar issue:

Error parsing attribute "@relation": The type of the field id in the model Request is not matching the type of the referenced field id in model User.

I have seen this discussion which suggests somehow lying to prisma. I am not clever enough to understand the gist of what the lie is supposed to be or how to do it.

Someone on github suggested using this referencing syntax in the request model:

user                   User                      @relation(fields: [userId], references: [id])
userId                 String                    @unique    @db.Uuid

I tried it as above, and without the @unique flag, but I still get a migration error that says that uuid and text are incompatible references. I can't find a section of the prisma documentation that addresses how to make uuid references compatible with relation models.

fyi: the migration file for the attempt above shows the following:

CREATE TABLE "Request" (
    "id" SERIAL NOT NULL,
    "userId" UUID NOT NULL,
    
    CONSTRAINT "Request_pkey" PRIMARY KEY ("id")
);

Answer 1

You will have to use the annotation @db.Uuid on the reference column userId, read more about it here.

Example:

model Request {
  id                Int @id @default(autoincrement())
  user              User   @relation(fields: [userId], references: [id])
  userId            String @db.Uuid // <-- the difference is here
  ...your other stuff
}

Answer 2

I'd just like to add, for anyone else who might find themselves in this situation that, if you come across this issue and are seemingly doing everything correct, it's quite possible you have artifacts from the failed migration present in your project.

Don't forget to also delete the failed migration

Answer 3

In your Request model is missing the foreign key userId with @db.Uuid this will make postgreSql use the uuid type on a column and @relation the field name must be the same as the foreign key like this @relation(fields: [userId]). The complete code should look like this:

model User {
  id        String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  request   Request?
  createdAt DateTime @default(now()) @db.Timestamptz(6)
  updatedAt DateTime @default(now()) @updatedAt @db.Timestamptz(6)
}

model Request {
  id        Int      @id @default(autoincrement())
  user      User     @relation(fields: [userId], references: [id]) <-ADD userId here
  userId    String   @db.Uuid <-ADD THIS
  createdAt DateTime @default(now()) @db.Timestamptz(6)
  updatedAt DateTime @default(now()) @updatedAt @db.Timestamptz(6)
}

You can do this with other types, here are some examples: https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#string

Answer 4

I had a similar error and it was because I did not change the type of the relation's ID from Int to String from the example.

If you update the Request model to look like this, does it work?

model Request {
  id                Int @id @default(autoincrement())
  user              User   @relation(fields: [id], references: [id])
  userId            String // <- this was missing
  createdAt         DateTime @default(now()) @db.Timestamptz(6)
  updatedAt         DateTime @default(now()) @updatedAt @db.Timestamptz(6)
}

Answer 5

I think that the Felix Hagspiel answer is the solution. I could add you should remove the failed generated migration sql (by removing the folder) and retry it again after fixing the problem.

Answer 6

So just incase you are still facing this issue, What worked for me was that I updated the prisma config like this :

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["postgresqlExtensions"]
}
datasource db {
  provider   = "postgresql"
  url        = env("DATABASE_URL")
  extensions = [pgcrypto]
}

and also followed the solutions that was added on this post. i.e

propertyId String?   @db.Uuid
property   Property? @relation(fields: [houseId], references: [id])

You should also delete the old migrations, and run this:

 npx prisma migrate dev --create-only --name enable_pgcrypto