Support UUID v7

[kibertoad]

Problem

Efficient cursor-based pagination relies on lexicographical and chronological sorting support, which is not provided by older UUID versions.

Suggested solution

UUID v7 fills a big gap in the database space by addressing the need for the lexicographically and chronologically sortable identifiers. CUID and ULID are strong contenders, but they come with a significant adoption friction involved, due to not being UUID-compatible, and hence not qualifying as a drop-in replacement. Also Prisma currently doesn't support ULID at all, and for CUID only deprecated version 1 is supported.

Implementing UUID v7 support on Prisma level would have a huge impact for addressing this need and enabling the adoption of the new standard. Since it is storage-compatible with UUID v4, it could already be adopted in existing databases with very little changes.

UUID v7 was already published as an RFC, see https://www.rfc-editor.org/info/rfc9562, so it should be safe to implement it already.

There is an existing Rust implementation: https://crates.io/crates/uuid7 - it implements the published RFC, and can be used to provide support.

Usage syntax could be identical to the existing CUID support:

  id   String @id @default(uuidv7())  @db.Uuid

Alternatives

• Implement new spec-compliant v7 implementation from a scratch - that would be extra work for unclear benefits.
• Wait till databases implement UUID v7 support natively - since approval is going to happen past the PostgreSQL feature freeze period, it is likely to take at least two years until this is going to happen.

Additional context

[sergeyprokhorenko]

UUIDv7 with exactly the same structure as the existing implementation in Rust, has already been implemented in ClickHouse DBMS.

But ClickHouse ensures monotonicity even for multithreading, which is impossible to achieve in Rust in isolation from the DBMS. The PostgreSQL proposal does not provide monotonicity for multithreading due to complexity.

I recommend using an existing implementation in Rust. Someday in the future it will be possible to replace it with some implementation that ensures monotonicity for multithreading.

For generation on the client side, I recommend using this implementation from the same author.

[kibertoad]

rfc-9562 was published (https://www.rfc-editor.org/info/rfc9562), I've updated the ticket accordingly

[kibertoad]

https://crates.io/crates/uuid7 was updated to clarify being in sync with the published RFC, I've edited the issue description accordingly.

[mcuelenaere]

I've taken a stab at this at prisma/prisma-engines#4877