Support ULID

[p6l-richard]

Problem

UUID can be suboptimal for many use-cases because:

It isn't the most character efficient way of encoding 128 bits of randomness
UUID v1/v2 is impractical in many environments, as it requires access to a unique, stable MAC address
UUID v3/v5 requires a unique seed and produces randomly distributed IDs, which can cause fragmentation in many data structures
UUID v4 provides no other information than randomness which can cause fragmentation in many data structures

From the ULID spec.

The Lexicographically sortability would especially be useful for cursor-based pagination which requires sorting by a unique, sequential column.

Suggested solution

Support ULID in the prisma schema, by defining:
@db.Ulid

[mbrookson]

It would be great to see this feature supported!

Since ULID is designed to be the same 128-bits as UUID it should work with the underlying database type, but using this means Prisma throws the error because it is validating the string length which is only 26 characters for ULID.

From some further research into this however it seems that Postgres itself doesn't really support ULID as a datatype yet and it's difficult to achieve. Maybe something to consider for the future if this does ever become a feature.

[rafaell-lycan]

I've found this thread as my company uses ULID via a Golang lib and generates the @id on the backend instead of relying on the DB and now I was searching for a way to do the same with Prisma (my ORM of choice).

I've asked about CUID and the backends have answered they did research but options and ULID was the "best option at that time" (~5-6 years ago)

[matus-sabo]

In postgres you can create function to generate ULID (uuid v7 or uuid v8) https://gist.github.com/kjmph/5bd772b2c2df145aa645b837da7eca74

[ikeyan]

@matus-sabo that's cool, but it's UUID, not ULID.

[pksunkara]

I am the author of https://github.com/pksunkara/pgx_ulid and I would be willing to implement this. @janpio and prima people, do I have the go-ahead?

[janpio]

What exactly is even needed here if there is a PostgreSQL extension?
Is https://www.prisma.io/docs/concepts/components/prisma-schema/postgresql-extensions not enough?

[pksunkara]

But @db.Ulid is not supported in the schema types though?

[janpio]

Ah, I see that the extension also adds a new ulid type in PostgreSQL, additionally to the function to create one?
Can a generated value only be stored in such a ulid column, or also as a normal text string?

Currently Prisma does not offer a way for extensions to add native types.

[pksunkara]

Can a generated value only be stored in such a ulid column, or also as a normal text string?

Yes, a generated value can only be stored in such an ulid column. It is visible to end-users as a string, but internally it's stored in a much more compact format which is why a new type was necessary.

Currently Prisma does not offer a way for extensions to add native types.

Yup, which is why I was offering to create a native type Ulid in Prisma query engine. All I need is someone to point me to the specific part of the code.

[janpio]

Yup, which is why I was offering to create a native type Ulid in Prisma query engine. All I need is someone to point me to the specific part of the code.

Ulid is not available on most PostgreSQL databases, only when you install this extension. So we could not merge such an addition of a native type, unless it also depends on the extension being used in the schema. We do not have this mechanism yet at all.

[pksunkara]

I guess the only way to solve this would be use a custom type as specified in #5039? But there's nothing happening on that.

[mbrookson]

Since ULID is designed to be the same 128-bits as UUID it should work with the underlying database type, but using this means Prisma throws the error because it is validating the string length which is only 26 characters for ULID.

I could well be wrong, but referring to previous comment, if ULID is 128-bits can it not just be stored in a standard UUID column?

[pksunkara]

It is actually stored in the same format, but since it needs to be read differently, that is why a new type is needed.

[Falmarri]

Are ULIDs still needed now that v7 UUIDs are coming into the spec? Should probably just use that.

[kibertoad]

I'm linking #24079 here, as it aims to address the same problem in a slightly different way.