Provide better error message when migrate is unable to find shadow database in Supabase

[jean-smaug]

Bug description

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

How to reproduce

1. Download Supabase CLI
2. supabase init
3. supabase start
4. Init prisma
5. Run prisma migrate dev

Expected behavior

Migrations are generated

Prisma information

It's not my real schema which is private, but I tested with this one and it's also failing 😬

generator client {
  provider = "prisma-client-js"
}

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

model User {
  id       String  @id @default(dbgenerated("extensions.uuid_generate_v4()")) @db.Uuid
  name String @unique @db.VarChar(255)

  @@map("users")
}

Environment & setup

• OS: MacOS
• Database: PostgreSQL with Supabase
• Node.js version: v14.17.4

Prisma Version

prisma                  : 3.6.0
@prisma/client          : 3.6.0
Current platform        : darwin
Query Engine (Node-API) : libquery-engine dc520b92b1ebb2d28dc3161f9f82e875bd35d727 (at node_modules/@prisma/engines/libquery_engine-darwin.dylib.node)
Migration Engine        : migration-engine-cli dc520b92b1ebb2d28dc3161f9f82e875bd35d727 (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine    : introspection-core dc520b92b1ebb2d28dc3161f9f82e875bd35d727 (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary           : prisma-fmt dc520b92b1ebb2d28dc3161f9f82e875bd35d727 (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash    : dc520b92b1ebb2d28dc3161f9f82e875bd35d727
Studio                  : 0.440.0

[jean-smaug]

Solution for a tool like Supabase is to create another instance of PostgresSQL as shadow database.

https://www.prisma.io/docs/concepts/components/prisma-migrate/shadow-database#cloud-hosted-shadow-databases-must-be-created-manually

I'm closing this issue now.
Thanks for all of your work !

[janpio]

Great that you found a workaround. Optimally you should have gotten an error message telling you about the workaround though, and not this:

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

We should investigate why that error message is not triggering on Supabase.

[eldarshamukhamedov]

For any future Prisma + Supabase users Googling this, if, like me, you were trying to do fully-local development, literally following these steps from the original post:

1. Download Supabase CLI
2. supabase init
3. supabase start
4. Init prisma
5. Run prisma migrate dev

You probably read the docs here on manually creating a shadow database here, but those docs heavily imply creating a cloud database.

However, AFAIK, there's nothing preventing you from spinning up a local shadow Supabase DB for Prisma to use via, say, docker-compose.yml.

The following worked for me.

Put this in a docker-compose.yml file and run docker-compose up:

version: '3.8'
services:
  # Prisma uses this database to detect schema drift and other dev checks
  # See https://www.prisma.io/docs/concepts/components/prisma-migrate/shadow-database
  supabase-shadow:
    image: supabase/postgres:14.1.0
    command: postgres -c config_file=/etc/postgresql/postgresql.conf
    restart: unless-stopped
    ports:
      - 12345:5432
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    volumes:
      # 👇 Change this volume mapping to something that makes sense for you
      - ~/.docker/storage/data:/var/lib/postgresql/data
      - ~/.docker/storage/init:/docker-entrypoint-initdb.d

Then add the shadow DB URL to your .env file:

DATABASE_URL="postgresql://postgres:postgres@localhost:10150/postgres"
SHADOW_DATABASE_URL="postgresql://postgres:postgres@localhost:12345/postgres"

And finally update your schema.prisma to point shadowDatabaseUrl to your local shadow DB:

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

With that, I'm able to run and generate migrations with Prisma, while running the typical Supabase local dev environment with supabase start.

Hope that helps!

[kevinwcyu]

An alternative method to spinning up a shadow db locally by creating a docker-compose.yml is to create another supabase db locally.

Here are the steps (assuming you have initialized supabase and have a supabase/ directory in the root of your project):

1. From the root of your project cd prisma
2. supabase init
3. Edit the ports in prisma/supabase/config.json. I just changed them to

   "ports": {
     "api": 64321,
     "db": 64322,
     "studio": 64323
   },

4. Add the following to your .env file

    SHADOW_DATABASE_URL="postgresql://postgres:postgres@localhost:64322/postgres"

5. Update your schema.prisma file with the shadowDatabaseUrl

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

6. Run supabase start in the root of your project and run supabase start in prisma/

Note: you don't have to init the supabase project inside of your prisma/ directory, you can choose any other directory. This method also runs everything else that normally runs when you run supabase start instead of just starting a Postgres db.

[tenhaus]

This works for me. Not sure of the side effects, but it works.

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

[janpio]

This will most probably deleted your data sooner or later, don't do that @tenhaus
We actually have an issue that this should not be allowed: #16628