CODEWITHSUNDEEP
postgresqlprismasupabase
jean-smaug
jean-smaug

Reputation: 411

Prisma migration fail with supabase

I'm trying to generate some migrations using Prisma.
I'm using Supabase which is using Postgres under the hood.
Also, I tried to run the following command with the local emulator and with the "real project".

When I run prisma db push it's working, so the communication between prisma and supabase can be established. But when I try to run prisma migrate dev I get the following error

Error: db error: ERROR: no such database: prisma_migrate_shadow_db_b2ce3e4e-c5ef-41f6-830f-2203a082f1db
   0: sql_migration_connector::flavour::postgres::sql_schema_from_migration_history
             at migration-engine/connectors/sql-migration-connector/src/flavour/postgres.rs:367
   1: migration_core::api::DevDiagnostic
             at migration-engine/core/src/api.rs:108

Supabase CLI : 0.15.3
Prisma : 3.6.0

Upvotes: 1

Views: 5454

Answers (3)

Hitit
Hitit

Reputation: 478

Environment

  • Framework: Next JS
  • ORM: Prisma
  • DB: Supabase(Cloud)

Problem

Error: db error: FATAL: bouncer config error
   0: migration_core::state::DevDiagnostic
             at migration-engine/core/src/state.rs:269

Reason

  1. According to Prisma documentation, the prisma migrate command requires second, temporary database called Shadow Database in order to detect problems such as schema drift.

    Some development-focused commands for relational databases of Prisma Migrate use a second, temporary database:

    • prisma migrate dev
    • prisma migrate reset

    The shadow database is created and deleted automatically* each time you run a development-focused command and is primarily used to detect problems such as schema drift.

  2. Therefore, Supabase needs to create additional DB temporarily.

  3. According to the same doc if you are using a cloud-hosted database for development, you need to create the shadow database manually.

Solution

  1. create another DB in Supabase Cloud.

  2. copy newly created Supabase Cloud URL

  3. Add new SHADOW_DATABASE_URL field in .env.

  4. paste Supabase Cloud URL to SHADOW_DATABASE_URL

  5. Add shadowDatabaseUrl = env("SHADOW_DATABASE_URL") in schema.prisma:

    datasource db {
  provider          = "postgresql"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}

  6. save files and run npx prisma db migrate

Upvotes: 0

GatesKennedy
GatesKennedy

Reputation: 725

  1. Prisma Prisma will try to drop the db during the migration and SupaBase doesn't like that so Prisma requires you to make a "Shadow Database"

Prisma Docs: Shadow Databases

  1. SupaBase To create the shadow db and get its connection string you have to connect to the SupaBase db with psql, create a new database and modify your connection string to point to the new db. (because prisma IS allowed to delete this second db during migration)

SupaBase Docs: Prisma (Shadow db)

Upvotes: 0

jean-smaug
jean-smaug

Reputation: 411

I also asked this question on Prisma repo : https://github.com/prisma/prisma/issues/10575

The solution is to create a shadow database as mentionned in the documentation https://www.prisma.io/docs/concepts/components/prisma-migrate/shadow-database#cloud-hosted-shadow-databases-must-be-created-manually

Upvotes: 2

Related Questions