uuid missing in drizzle-orm/mysql-core?
There is a uuid method in the drizzle-orm/pg-core package, but none in the mysql-core package. What is the reason behind this? What should we use instead to generate uuids automatically?
10 Replies
We only provide what the underlying dialect provides
Postgres has a uuid column type and MySQL doesn't
oh ok! what is the alternative that i could use?
do you happen to know what prisma does, do they just do a plain
crypto.randomUUID() as a fallback?Yes, something like that I think
interested how you went about implementing this
i use
varchar("id", { length: 191 }).primaryKey().notNull() for most tables and then just generate the id via crypto.randomUUID() on the server side for my insert statementsnice!
first import
import { sql } from "drizzle-orm";
then add For UUIDs in a production MySQL workload, I would recommend a
customType that stores UUIDS as BINARY(16) . But I would also suggest not using random UUIDs as primary keys..Why is it not recommended?
There are a lot of resources online that talk about this, so I would recommend researching to understand and decide if it's right for you
But at a high level, due to the randomness of UUIDs, writes occur all over the place which can lead to poor performance from page splits, in contrast to an ordered PK (e.g. autoincrementing int) where writes are almost always done at the end
UUIDs generated by MySQL are version 1 UUIDs (time based), but they still contain random bits at the front. So unless you store them as binary and specify the
swap_flag which re-orders the timestamp bits to the front, then you will still face the same problem. For reference, UUID_TO_BIN(string_uuid, swap_flag)
This is why there has been such a rise of time sortable UUIDs and the new draft UUID version 7 to address these issues