CUID and UUID with PostgreSQL and Foreign Keys #21489
Replies: 3 comments
-
Hi @LFCavalcanti 👋
To generate model CompanyGroup {
id String @id @default(cuid()) @db.VarChar(30)
}
When using foreign keys (FKs) with model Session {
id String @id @default(cuid()) @db.VarChar(30)
sessionToken String @unique
userId String @db.VarChar(30)
expires DateTime
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
}
Using the correct data type is important for both performance and data integrity. For If this answers your question, it would be great if you could mark this Discussion as answered to indicate that it has been resolved. Otherwise please let us know how else we can help you further or close the Discussion if it was resolved in some other way 🙏 |
Beta Was this translation helpful? Give feedback.
-
Hi @ludralph ! Thanks for the information. I'm still puzzled with the native binary types and what Prisma currently support or not. I've read this blog about ULIDs: https://blog.daveallie.com/ulid-primary-keys There's implementations for UUIDv7 or ULID as custom extensions, but if Prisma doesn't support the types I't a moot point. |
Beta Was this translation helpful? Give feedback.
-
@LFCavalcanti CUID is not format-compatible with UUID, so you can't store it in an optimized UUID format in PostgreSQL, unfortunately. If you use 128-bit ULID format, it is compatible with UUID and could be stored in an optimized UUID format. E. g. in CockroachDB this works:
Now PostgreSQL (as opposed to CockroachDB) doesn't natively support ULID generation, so you would need to install an extension, similar to https://github.com/pksunkara/pgx_ulid, in order to be able to generate them. But if you have the extension, |
Beta Was this translation helpful? Give feedback.
-
Question
Hello everyone!
So, I'm having difficulty to setup UUID and CUIDs on my App using Prisma on PostgreSQL.
Currently I have the PK using CUID in some tables to provide better control and scalability, the schema is defined as:
Or in case of more security I'm using UUId's like so:
I read that UUIDs should be stored in a specific binary type so indexing is done correctly.
Searching Prisma Docs I did found a way to deal with UUIDs with the native type:
https://www.prisma.io/docs/concepts/database-connectors/postgresql#native-type-mappings
https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#uuid
So I believe the correct model for the User table would be:
But I didn't find a way to store CUID, I tried using the cuid() function with the @db.Uuid type mapping but it fails because the string structure is different.
Another assumption is that FKs should use the same native type, so in example the session table should be:
My questions are:
How to reproduce (optional)
Expected behavior (optional)
No response
Information about Prisma Schema, Client Queries and Environment (optional)
Prisma 5.4.2
@prisma/client 5.4.2
The app is using Next.js 13.5.4
I just updated all dependencies yesterday 😄
Beta Was this translation helpful? Give feedback.
All reactions