node.js | Replacing DB client with Prisma keeping preserving DB structure - issue with uuid_generate_v4()

Question

I am trying to add the Prisma DB client to the existing node.js project while preserving the DB structure.

Postgresql Prisma 4.7.1

1. I've set up the initial Prisma configuration (env vars, etc.).
2. I've used the command npx prisma db pull to generate prisma.schema file according to the existing DB structure
3. I create the initial migration by using some empty DB npx prisma migrate dev

At this point it is expected that migration would create DB structure, but the command fails with the following error

✗ npx prisma migrate dev              
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "service_prisma", schema "public" at "127.0.0.1:5432"

PostgreSQL database service_prisma created at 127.0.0.1:5432

✔ Enter a name for the new migration: … init
Applying migration `20221221095823_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: 20221221095823_init

Database error code: 42883

Database error:
ERROR: function uuid_generate_v4() 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 uuid_generate_v4() 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: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("parse_func.c"), line: Some(521), routine: Some("ParseFuncOrColumn") }

Follow up plan would be to:

4. Set DB back to the original one containing tables and data
5. Then mark initial migration as applied with the following command npx prisma migrate resolve --applied 20221221095823_init

So, main problem is that IDs in existing tables use uuid_generate_v4() to generate random UUID for new entries. The support on DB level is definitly there, because it simple works normally with slonik DB client.

model SomeTable {
  id    String   @id @default(dbgenerated("uuid_generate_v4()")) @db.Uuid
}

Any idea how to solve this? Thanks in advance!

Answer 1

I struggled a bit, but then found a solution shortly after posting the question above...

I've found some tips but they were a bit unclear to me initially.

So if anyone runs into a similar problem, then there is a solution:

1. Ignore the error in step 3 - the migration file gets created anyway
2. Thenn add the CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; into the first line of the created migration file.
3. Run npx prisma migrate dev again, this time it should run without errors
4. Continue with step 4 of the list mentioned above.

Hope this helps :)