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") }
Top comments (1)
Really helpful 🚀