Database-generated human resource IDs

I previously wrote about my preferred ID format consisting of a small type prefix and a KSUID, and explained why this ID format feels superior to other strategies. I have since wrote and published a small Node.js package called resource-id to make generating and manipulating these IDs a little easier.

Something which isn’t so nice, however, is the need to manually generate an ID every time you want to insert a row. While this is ideal for performance in situations where you’re inserting a deep graph of objects or are otherwise experiencing extreme levels of load, for the vast majority of products out there this is an unnecessary chore.

The problem stems from the fact that no database engine¹ offers a native KSUID generation function. If you use UUIDs in Postgres all you need to do is install the uuid-ossp extension and start defaulting columns to uuid_generate_v4() to your heart’s content, but you’re out of luck if you’re going down the path of using KSUIDs.

Fortunately, someone has written a Postgres function for generating KSUIDs which we can copy and paste into a migration file. First make sure you’ve installed the pgcrypto extension, and then grab the implementation from here. I personally renamed the function to ksuid_generate to align a bit better with the UUID generation functions.

From here you can easily automate the generation of human-friendly IDs in your database, like in the following Prisma schema:

model User {  id    String @id @default(dbgenerated("CONCAT('user_', ksuid_generate())"))  name  String  email String  // ...}

While a little unwieldy-looking in the schema, this is super easy and removes a chore you would otherwise need to do by hand. I like it.