prisma migrate dev fails with P3005 error on second run (empty db)

[di-sukharev]

Bug description

When running npx prisma migrate dev for the second time on a non-empty database, the command fails with the error

Error: P3005 The database schema is not empty.

and the __prisma_migrations table gets deleted.

Checked that I am using the latest version of Prisma.
Enabled debug logs and checked them for more detailed error messages.
Reset the development database using npx prisma migrate reset.
Checked that the Prisma schema and database schema are in sync.
Manually dropped and recreated the database.
Validated the database connection using other tools.
Reinstalled node modules.

None of these steps resolved the issue.

How to reproduce

Initialize a new Prisma project with a new database.
Run npx prisma migrate dev - this works as expected.
Run npx prisma migrate dev a second time - this results in the Error: P3005 The database schema is not empty.

Expected behavior

npx prisma migrate dev should be able to be run multiple times without errors, as long as the Prisma schema has not changed.

Prisma information

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

datasource db {
  provider          = "postgresql"
  url               = env("POSTGRES_PRISMA_URL")
  directUrl         = env("POSTGRES_URL_NON_POOLING")
  shadowDatabaseUrl = env("POSTGRES_URL_NON_POOLING")
}

model User {
  id            String         @id @default(uuid())
  createdAt     DateTime       @default(now()) @map("created_at")
  updatedAt     DateTime       @updatedAt @map("updated_at")
}

Environment & setup

• OS: macOS
• Database: PostgreSQL
• Node.js version: v18.14.0

Prisma Version

Prisma version: 4.14

Full error log

prisma:engines  binaries to download libquery-engine, migration-engine +0ms
  prisma:loadEnv  project root found at /Users/my-user/Desktop/repositories/me/example-repo/package.json +0ms
  prisma:tryLoadEnv  Environment variables loaded from /Users/my-user/Desktop/repositories/me/example-repo/.env +0ms
  prisma:getConfig  Using getConfig Wasm +0ms
  prisma:getConfig  config data retrieved without errors in getConfig Wasm +2ms
  prisma:loadEnv  project root found at /Users/my-user/Desktop/repositories/me/example-repo/package.json +3ms
  prisma:tryLoadEnv  Environment variables loaded from /Users/my-user/Desktop/repositories/me/example-repo/.env +3ms
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
  prisma:getConfig  Using getConfig Wasm +2ms
  prisma:getConfig  config data retrieved without errors in getConfig Wasm +1ms
Datasource "db": PostgreSQL database "enigma-local-db", schema "public" at "localhost:5432"

  prisma:validate  Using validate Wasm +0ms
  prisma:getConfig  Using getConfig Wasm +1ms
  prisma:getConfig  config data retrieved without errors in getConfig Wasm +1ms
  prisma:getConfig  Using getConfig Wasm +0ms
  prisma:getConfig  config data retrieved without errors in getConfig Wasm +1ms
  prisma:migrateEngine:rpc  starting migration engine with binary: /Users/my-user/Desktop/repositories/me/example-repo/node_modules/@prisma/engines/migration-engine-darwin-arm64 +0ms
  prisma:migrateEngine:rpc  SENDING RPC CALL {"id":1,"jsonrpc":"2.0","method":"devDiagnostic","params":{"migrationsDirectoryPath":"/Users/my-user/Desktop/repositories/me/example-repo/prisma/migrations"}} +2ms
  prisma:migrateEngine:stderr  {"timestamp":"2023-05-14T06:44:52.224116Z","level":"INFO","fields":{"message":"Starting migration engine RPC server","git_hash":"d9a4c5988f480fa576d43970d5a23641aa77bc9c"},"target":"migration_engine"} +0ms
  prisma:migrateEngine:stderr  {"timestamp":"2023-05-14T06:44:52.248954Z","level":"INFO","fields":{"message":"Connecting to user-provided shadow database."},"target":"sql_schema_connector::flavour::postgres","span":{"name":"DevDiagnostic"},"spans":[{"name":"DevDiagnostic"}]} +25ms
  prisma:migrateEngine:stderr  {"timestamp":"2023-05-14T06:44:52.248971Z","level":"INFO","fields":{"message":"Resetting schema(s)","schemas_to_reset":"[\"public\"]"},"target":"sql_schema_connector::flavour::postgres","span":{"name":"DevDiagnostic"},"spans":[{"name":"DevDiagnostic"}]} +0ms
  prisma:migrateEngine:stderr  {"timestamp":"2023-05-14T06:44:52.3156Z","level":"INFO","fields":{"message":"Connecting to user-provided shadow database."},"target":"sql_schema_connector::flavour::postgres","span":{"namespaces":"None","name":"validate_migrations"},"spans":[{"name":"DevDiagnostic"},{"namespaces":"None","name":"validate_migrations"}]} +66ms
  prisma:migrateEngine:stderr  {"timestamp":"2023-05-14T06:44:52.31562Z","level":"INFO","fields":{"message":"Resetting schema(s)","schemas_to_reset":"[\"public\"]"},"target":"sql_schema_connector::flavour::postgres","span":{"namespaces":"None","name":"validate_migrations"},"spans":[{"name":"DevDiagnostic"},{"namespaces":"None","name":"validate_migrations"}]} +0ms
  prisma:migrate:dev  {
  devDiagnostic: '{\n  "action": {\n    "tag": "createMigration"\n  }\n}'
} +0ms
  prisma:migrateEngine:rpc  SENDING RPC CALL {"id":2,"jsonrpc":"2.0","method":"applyMigrations","params":{"migrationsDirectoryPath":"/Users/my-user/Desktop/repositories/me/example-repo/prisma/migrations"}} +135ms
  prisma:migrateEngine:rpc  {
  jsonrpc: '2.0',
  error: {
    code: 4466,
    message: 'An error happened. Check the data field for details.',
    data: {
      is_panic: false,
      message: 'The database schema is not empty. Read more about how to baseline an existing production database: https://pris.ly/d/migrate-baseline',
      meta: null,
      error_code: 'P3005'
    }
  },
  id: 2
} +1ms
Error: Error: P3005

The database schema is not empty. Read more about how to baseline an existing production database: https://pris.ly/d/migrate-baseline

    at Object.<anonymous> (/Users/my-user/Desktop/repositories/me/example-repo/node_modules/prisma/build/index.js:99361:25)
    at MigrateEngine.handleResponse (/Users/my-user/Desktop/repositories/me/example-repo/node_modules/prisma/build/index.js:99209:36)
    at LineStream2.<anonymous> (/Users/my-user/Desktop/repositories/me/example-repo/node_modules/prisma/build/index.js:99312:16)
    at LineStream2.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Readable.push (node:internal/streams/readable:234:10)
    at LineStream2._pushBuffer (/Users/my-user/Desktop/repositories/me/example-repo/node_modules/prisma/build/index.js:98988:17)
    at LineStream2._transform (/Users/my-user/Desktop/repositories/me/example-repo/node_modules/prisma/build/index.js:98982:8)
    at Transform._write (node:internal/streams/transform:175:8)

[di-sukharev]

happens to be that it fails with this config:

datasource db {
  provider          = "postgresql"
  url               = env("POSTGRES_PRISMA_URL")
  directUrl         = env("POSTGRES_URL_NON_POOLING")
  shadowDatabaseUrl = env("POSTGRES_URL_NON_POOLING")
}

and doesnt fail like this:

datasource db {
  provider          = "postgresql"
  url               = env("POSTGRES_PRISMA_URL")
  directUrl         = env("POSTGRES_URL_NON_POOLING")
}

so the issue is somewhere in shadowDatabaseUrl

POSTGRES_URL_NON_POOLING and POSTGRES_PRISMA_URL are 2 different DBs, but it also doesnt work if DB is same

[di-sukharev]

if i do npx prisma migrate reset and then run npx prisma migrate dev it works, but when i call npx prisma migrate dev for the second time it fails again with the same error:

Error: P3005 The database schema is not empty.

[tomi-mercado]

Same here! Also I run npx prisma migrate reset and works but then fails with npx prisma migrate dev

[charliewhu]

I also have this issue (works fine without shadowDatabaseUrl)

[AgileNix]

It's not a bug.

The issue is that you're using the same DB as a directUrl and as a shadowDbUrl. If you just copy/pasted the config from Vercel, then they got it wrong. Here's a quote from Prisma docs:

Important: Do not use the same values for url and shadowDatabaseUrl.

https://www.prisma.io/docs/concepts/components/prisma-migrate/shadow-database

[janpio]

@charliewhu @tomi-mercado @di-sukharev Please see @AgileNix' comment, they are correct. With the same database URL for both directUrl and shadowDatabaseUrl, your existing data will be removed when migrate dev tries to identify the diff between your schema and database on the next migrate dev run.

Seems we have to prioritize this issue soon: #16628

Where did you get your Prisma schema datasource blocks from? Is it Vercel as @AgileNix assumed? Do you still know the URLs so we can get this fixed?

I suggest you remove the shadowDatabaseUrl and see if that fixes the problem.
If you get another error message that the database can not be created in that moment, you will have to provide another database in that property.

[sukharev-deel]

@janpio yep, it was vercel postgres with prisma default setup

[douglasrcjames]

Bumping this, also setup using the "beta" Vercel postgres setup

[mikemoone]

The Vercel Postgres + Prisma starter repo should be updated, I got around the error by creating a shadow database directly on Neon (https://neon.tech) and adding a new env variable pointing to it:

datasource db {
  provider          = "postgresql"
  url               = env("POSTGRES_PRISMA_URL") // uses connection pooling
  directUrl         = env("POSTGRES_URL_NON_POOLING") // uses a direct connection
  shadowDatabaseUrl = env("NEON_URL_SHADOW") // used for migrations
}

[Meedux]

My solution to this is instead use the postgres server as the shadowDatabaseUrl

datasource db {
  provider = "postgresql"
  url = env("POSTGRES_PRISMA_URL") // uses connection pooling
  directUrl = env("POSTGRES_URL_NON_POOLING") // uses a direct connection
  shadowDatabaseUrl = env("DATABASE_URL") // using the local database as a shadow database
}

assuming you setup your DATABASE_URL as your local db's url

[KeisukeNagakawa]

@janpio Thanks for great info. Can I ask if there are any downsides to removing the shadow database URL?

[janpio]

No, it is only supposed to be used if the shadow database can not be created automatically. If your database allows that, you should not use shadowDatabaseUrl and let Prisma handle that.

[shimekukuri]

Also bumping this. Even when removing the shadowDatabaseUrl I'm still getting the same error.

[janpio]

Can you open a new issue and fill all the information the template asks for @shimekukuri? That should not be happening and does not match what others are reporting here, so we would like to investigate. Thanks.

[aysark]

Ran into this, many hours wasted. Thanks for soln.

[janpio]

Hey everyone, Vercel Postgres now supports Prisma Migrate better, so you can remove the shadowDatabaseUrl from your Prisma schema. Only url and directUrl is still needed, and Migrate should take care of the rest under the hood 🪄

Can you please let me know if this works for you? Then we can push to get the documentation updated accordingly. Thanks!

[janpio]

This is now fixed. Do not use shadowDatabaseUrl with Vercel Postgres any more, especially not with the same value as url or directUrl. The shadow database can be created dynamically.