Using a Custom-Generated Nanoid as a Primary Key in Prisma with Postgres
Published: 2023-01-24 05:10:48

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](https://www.prisma.io/docs/getting-started/quickstart). Our goal is to add a custom function to the PostgreSQL instance by creating a blank migration using:

```bash
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](https://github.com/Jakeii/nanoid-postgres/blob/main/nanoid.sql):

```sql
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:

```bash
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:

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

👏👏👏 You're done!

Here's what they look like:


![screenshot of terminal that generates nanoid](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/60waxtboqisgw4mhcj03.png)

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](https://github.com/prisma/prisma/discussions/12621)
* [Customize a migration to include an unsupported feature](https://www.prisma.io/docs/guides/database/developing-with-prisma-migrate/include-unsupported-database-features)