How to use Binary UUIDs in Prisma with MySQL

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:

• uuid type - classical string 123e4567-e89b-12d3-a456-426614174000 which consumes 32 or 36 bytes
• cuid_type - shorter and clearer to read option 25 bytes cjld2cjxh0000qzrmn831irmy

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:

• in APIs to reduce traffic a little bit and make logs shorter
• in URLs available to users (to improve visual)

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:

• UUIDs are Popular, but Bad for Performance — Let’s Discuss
• Sequential UUID Generators

Best related

Prisma ORM: how to use the great database mapping package

#prisma #nodejs #express #orm #database