Postgres: Access user-defined database functions during migration (nanoid)

[pliant ruin]

I have a migration where I exchange the primary key from being a number to a nanoid. I have registered the postgres-nanoid method in my database. As the migration is executed in a shadow database, I don't have access to my nanoid method:

ALTER TABLE "Label" ADD COLUMN "new_id" CHAR(21) NOT NULL DEFAULT nanoid();

I tried to define viascom/nanoid-postgres inside the migration, but there I cannot activate the crypto postgres extension.

Is there a way to use nanoid() or other user defined postgres functions inside migrations?

The error message during migration:

Migration `20231008101221_nanoid_and_version_for_label` 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_schema_connector::validate_migrations
           with namespaces=None
             at schema-engine/connectors/sql-schema-connector/src/lib.rs:312
   1: schema_core::state::DevDiagnostic
             at schema-engine/core/src/state.rs:270

[pliant ruin]

When including nanoid-postgres inside the migration file, I get

Database error code: 42883

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

DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E42883), message: "function gen_random_bytes(integer) 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(Internal { position: 10, query: "bytes := gen_random_bytes(step)" }), where_: Some("PL/pgSQL function nanoid_optimized(integer,text,integer,integer) line 14 at assignment\nPL/pgSQL function nanoid(integer,text,double precision) line 29 at RETURN"), schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("parse_func.c"), line: Some(629), routine: Some("ParseFuncOrColumn") }

[pliant ruin]

Postgres: Access user-defined database functions during migration (nanoid)

[teal wind]

Hi @pliant ruin 👋

Can you share how you are including the nanoid-postgres in the migration file and also your schema file?

[pliant ruin]

Sure. I changed the schema from

model Label {
  organisationId Int
  id             Int
  name           String
  color          String
  description    String?
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt

  organisation         Organisation              @relation(fields: [organisationId], references: [id], onDelete: Cascade)
  memberLabels         OrganisationMemberLabel[]
  formLabels           FormLabel[]

  @@id([organisationId, id])
  @@unique([organisationId, name])
}

to

model Label {
  id String @id @default(dbgenerated("nanoid()")) @db.Char(21)

  organisationId Int
  name           String
  color          String
  description    String?
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt
  version        Int      @default(0)

  organisation         Organisation              @relation(fields: [organisationId], references: [id], onDelete: Cascade)
  memberLabels         OrganisationMemberLabel[]

  @@unique([organisationId, name])
}

But to prevent data loss and keep existing relations, I need to modify the migration.

First, I copied the code from https://github.com/viascom/nanoid-postgres/blob/main/nanoid.sql into the top of my migration file.

After that, the migration looks like this:

-- Step 1: Create a new column new_id in the main table and fill it with the new string IDs
ALTER TABLE "Label" ADD COLUMN "new_id" CHAR(21) NOT NULL DEFAULT nanoid();
ALTER TABLE "Label" ADD COLUMN "version" INTEGER NOT NULL DEFAULT 0;

-- Step 2: Update the related tables to use this new ID
ALTER TABLE "OrganisationMemberLabel" ADD COLUMN "new_labelId" CHAR(21) NOT NULL DEFAULT nanoid();
UPDATE "OrganisationMemberLabel" SET "new_labelId" = (SELECT "new_id" FROM "Label" WHERE "Label"."id" = "OrganisationMemberLabel"."labelId");

-- Step 3 Drop the foreign key constraints in the related tables that reference the current primary key
ALTER TABLE "OrganisationMemberLabel" DROP CONSTRAINT "OrganisationMemberLabel_organisationId_labelId_fkey";

-- Step 4: Update the label table to use the new id column as the primary key
ALTER TABLE "Label" DROP CONSTRAINT "Label_pkey";
ALTER TABLE "Label" DROP COLUMN "id";
ALTER TABLE "Label" RENAME "new_id" TO "id";
ALTER TABLE "Label" ADD CONSTRAINT "Label_pkey" PRIMARY KEY ("id");

-- Step 5: Related tables where using the old ID as part of the primary key need to be updated
ALTER TABLE "OrganisationMemberLabel" DROP CONSTRAINT "OrganisationMemberLabel_pkey";
ALTER TABLE "OrganisationMemberLabel" DROP COLUMN "labelId";
ALTER TABLE "OrganisationMemberLabel" RENAME "new_labelId" TO "labelId";
ALTER TABLE "OrganisationMemberLabel" ADD CONSTRAINT "OrganisationMemberLabel_pkey" PRIMARY KEY ("organisationId", "organisationMemberId", "labelId");

-- Step 6: Add back the foreign key constraints in the related tables, but now referencing the new primary key
ALTER TABLE "OrganisationMemberLabel" ADD CONSTRAINT "OrganisationMemberLabel_labelId_fkey" FOREIGN KEY ("labelId") REFERENCES "Label"("id") ON DELETE CASCADE ON UPDATE CASCADE;

I guess the first effective nanoid() call causes the error. Im using PostgreSQL 15 with the postgres user.

i changed the function definitions of postgres nanoid to not be leakproof

[sick cosmos]

I think you could try using the postgresqlExtensions and use it like

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["postgresqlExtensions"]
}

datasource db {
  provider   = "postgresql"
  url        = env("DATABASE_URL")
  extensions = [pgcrypto]
}

https://www.prisma.io/docs/guides/migrate/developing-with-prisma-migrate/enable-native-database-functions

[pliant ruin]

@sick cosmos when adding this previewFeature the migration engine wants to reset the whole schema. I want to avoid data loss.

In another guide the prisma docs describe exactly this approach where postgres extensions are installed as part of a migration
https://www.prisma.io/docs/guides/migrate/developing-with-prisma-migrate/enable-native-database-functions#how-to-install-a-postgresql-extension-as-part-of-a-migration

[pliant ruin]

I cannot reproduce this behavior in a clean environment.