How to use Binary UUIDs in Prisma with MySQL

#StandWithUkraine
Today, 20th March 2023, Ukraine is still bravely fighting for democratic values, human rights and peace in whole world. Russians ruthlessly kill all civilians in Ukraine including childs and destroy their cities. We are uniting against Putinโ€™s invasion and violence, in support of the people in Ukraine. You can help by donating to Ukrainian's army.

UUIDs is an awesome way to create high-scalable, offline-compatible, and secure applications. That is why a lot of developers all over the world use it even in SQL relational databases like MySQL.

Prisma ORM has 2 default ways to store UUIDs:

Anyway, they both are stored in MySQL as the string when Binary UUID takes only 16 bytes! According to researches, You can achieve more performance and less disk usage when you store them in binary 16 bytes format.

In PostgreSQL they are stored in 16 bytes with best efficiency and native translation to readable representation. So if you are kickstarting new project we reccomend just using Postgre. No more steps are needed

So MultiFo200 created a GitHub demo with Prisma BINARY UUIDS.

It has a pretty boilerplate project with hooks who translate UUIDs:

prisma.$use(async (params, next) => {
console.log('HOOK PARAMS': params);
if (params.action === 'create') {
if (params.args.data.uuid === undefined) { // if no uuid value passed to serialize
params.args.data.uuid = short.generate();
}
if (!Buffer.isBuffer(params.args.data.uuid)) {
const suuid = params.args.data.uuid.toString();
const uuid = shortTranslator.toUUID(suuid);
const bin = toBinaryUUID(uuid);
params.args.data.uuid = bin;
}
}
const result = await next(params);
if (Array.isArray(result)) {
return result.map((row) => {
if (row.uuid) {
const uuid = fromBinaryUUID(row.uuid);
const suuid = shortTranslator.fromUUID(uuid);
row.uuid = suuid;
}
return row;
});
} else {
if (result.uuid) {
const uuid = fromBinaryUUID(result.uuid);
const suuid = shortTranslator.fromUUID(uuid);
result.uuid = suuid;
}
return result;
}
})

To generate short string UUID we use short-uuid package

const short = require('short-uuid');

Short UUID is a string format with extended Alphabet 73WakrfVbNJBaAmhQtEeDv It takes 22 bytes so it looks shorter for users but still fully unique (same collision probability as in plain UUID, we juuse wider Alphabet so that's why it is shorter).

We are using shorts everywhere apart from databases:

So our translator in the demo translates SHORT UUID to BINARY and vice versa. Though you can easily adapt it to classical UUIDS.

Translation mechanism created with another package, called binary-uuid

const { toBinaryUUID, fromBinaryUUID } = require('binary-uuid');

To test it you can also open the migrations folder and check there:

-- CreateTable
CREATE TABLE `PostUUID` (
`uuid` BINARY(16) NOT NULL,
PRIMARY KEY (`uuid`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Couple of links about UUIDs performance:

Binary UUIDs in Prisma

#node #prisma #uuid
7
Ivan Borshchov profile picture
Apr 22, 2021
by Ivan Borshchov
Did it help you?
Yes !
No

Best related