Using a Custom-Generated Nanoid as a Primary Key in Prisma with Postgres

This tutorial demonstrates how to use a custom PostgreSQL function to generate a nanoid as a primary key for a table in Prisma.

I wanted to explore the possibility of using alternative keys, such as nanoids, instead of UUIDs. This tutorial assumes you already have a PostgreSQL instance running locally and have completed the initial prisma setup. Our goal is to add a custom function to the PostgreSQL instance by creating a blank migration using:

npx prisma migrate dev --create-only

You can name this migration 'nanoid'. Open the file created by the migration and paste the nanoid function, I found this one here:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE OR REPLACE FUNCTION nanoid(size int DEFAULT 21)
RETURNS text AS $$
DECLARE
  id text := '';
  i int := 0;
  urlAlphabet char(64) := 'ModuleSymbhasOwnPr-0123456789ABCDEFGHNRVfgctiUvz_KqYTJkLxpZXIjQW';
  bytes bytea := gen_random_bytes(size);
  byte int;
  pos int;
BEGIN
  WHILE i < size LOOP
    byte := get_byte(bytes, i);
    pos := (byte & 63) + 1; -- + 1 because substr starts at 1 for some reason
    id := id || substr(urlAlphabet, pos, 1);
    i = i + 1;
  END LOOP;
  RETURN id;
END
$$ LANGUAGE PLPGSQL STABLE;

Then you can run this migration using:

npx prisma migrate dev

Did it work? That's it! Once you've enabled the nanoid() function then you can create a new table that uses it like this:

model test {
  id String @id @default(dbgenerated("nanoid()")) @db.VarChar(22)
}

👏👏👏 You're done!

Here's what they look like:

I was originally getting some errors because I tried to use the pg module directly to run the function, this was before I found out I could use prisma's migrations directly, this worked much better. Here are some of the errors I was seeing:

Error: P3006

Migration `20230124043715_init` failed to apply cleanly to the shadow database. 
Error:
db error: ERROR: function nanoid() does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
   0: sql_migration_connector::validate_migrations
           with namespaces=None
             at migration-engine/connectors/sql-migration-connector/src/lib.rs:301
   1: migration_core::state::DevDiagnostic
             at migration-engine/core/src/state.rs:269

and

➜  node-prisma-postgres-nanoid npx prisma migrate dev --name init
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "nanoid-test", schema "public" at "localhost:5432"

Applying migration `20230124043715_init`
Error: P3018

A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve

Migration name: 20230124043715_init

Database error code: 42883

Database error:
ERROR: function nanoid() does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Position:
  0
  1 -- CreateTable
  2 CREATE TABLE "test" (
  3     "id" VARCHAR(11) NOT NULL DEFAULT nanoid(),

DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E42883), message: "function nanoid() does not exist", detail: None, hint: Some("No function matches the given name and argument types. You might need to add explicit type casts."), position: Some(Original(76)), where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("parse_func.c"), line: Some(636), routine: Some("ParseFuncOrColumn") }

• nanoid on prisma schema
• Customize a migration to include an unsupported feature