How to create a Custom Unique Primary Key using prisma?

Question

In my application i want to create a default primary key where the key must start with BL- and another rest 9 digits character must be random.

Schema

model myModel {
// try 1
id String @id @default(dbgenerated("BL-"))
// try 2
id  String @id @default(dbgenerated("BL-" + substring(uuid(), 1, 8)))

has context menu
Compose
// other fields
}

Migrations

-- AlterTable
ALTER TABLE "businessLoanOffers" ALTER COLUMN "id" SET DEFAULT BLO;

ERROR

Database error code: 0A000

 

Database error:
ERROR: cannot use column reference in DEFAULT expression

 

DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E0A000), message: "cannot use column reference in DEFAULT expression", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("parse_expr.c"), line: Some(530), routine: Some("transformColumnRef") }

My other Possible way of solving it would be using JS to create the primary-key of my choice but i wanted to solve this issue using prisma and postresql.

score 0 · Answer 1 · answered Apr 17 '23 at 20:30

As you've discovered, you can't use dbgenerated() for your use case because it's limited to what the database supports for default expressions, and custom functions or concatenations like the ones you're trying to use are not supported.

However, you can achieve your desired primary key format by using a custom function in your application code to generate the key when creating a new record.

You can use this function as a reference to create random primary key

function generateCustomId(prefix = 'BL-', length = 9) {
  const characters = '0123456789';
  let result = '';

  for (let i = 0; i < length; i++) {
    result += characters.charAt(Math.floor(Math.random() * characters.length));
  }

  return prefix + result;
}

How to create a Custom Unique Primary Key using prisma?

1 Answers1