[multiSchema previewFeature] The database schema is not empty error on prisma migrate dev

[aqrln]

Original issue: #21003

Relevant details surfaced in discussion: the behaviour is different depending on whether multiSchema is enabled. The error does not happen without the preview feature.

Discussed in #21005

^{Originally posted by reza-ebrahimi September 7, 2023}

Bug description

Here is my schema, after having a fesh schema and table in postgres prisma migrate dev throws following error:
The database schema is not empty

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

datasource db {
  provider  = "postgresql"
  url       = env("POSTGRES_PRISMA_URL")
  directUrl = env("POSTGRES_URL_NON_POOLING")
  schemas   = ["app"]
}

model NewTable {
  id BigInt @id

  @@schema("app")
}

Reproduce:

$ manually created an schema and a table in pgAdmin
$ npx prisma db pull
$ npx prisma migrate dev
....
Error: P3005

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

How to reproduce

$ manually created an schema and a table in pgAdmin
$ npx prisma db pull
$ npx prisma migrate dev
....
Error: P3005

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

Expected behavior

No response

Prisma information

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

datasource db {
  provider  = "postgresql"
  url       = env("POSTGRES_PRISMA_URL")
  directUrl = env("POSTGRES_URL_NON_POOLING")
  schemas   = ["app"]
}

model NewTable {
  id BigInt @id

  @@schema("app")
}

Environment & setup

• OS: Ubuntu
• Database: PostgreSQL
• Node.js version: v20.3.0

Prisma Version

$ npx prisma version
prisma                  : 5.2.0
@prisma/client          : 5.2.0
Current platform        : debian-openssl-1.1.x
Query Engine (Node-API) : libquery-engine 2804dc98259d2ea960602aca6b8e7fdc03c1758f (at ../../node_modules/.pnpm/@prisma+engines@5.2.0/node_modules/@prisma/engines/libquery_engine-debian-openssl-1.1.x.so.node)
Schema Engine           : schema-engine-cli 2804dc98259d2ea960602aca6b8e7fdc03c1758f (at ../../node_modules/.pnpm/@prisma+engines@5.2.0/node_modules/@prisma/engines/schema-engine-debian-openssl-1.1.x)
Schema Wasm             : @prisma/prisma-schema-wasm 5.2.0-25.2804dc98259d2ea960602aca6b8e7fdc03c1758f
Default Engines Hash    : 2804dc98259d2ea960602aca6b8e7fdc03c1758f
Studio                  : 0.494.0
Preview Features        : multiSchema

[leppaott]

> prisma migrate deploy
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "dabatase", schemas "shared, service-schema" at "postgres:5432"
1 migration found in prisma/migrations
Error: P3005 
The database schema is not empty. Read more about how to baseline an existing production database: https://pris.ly/d/migrate-baseline

Yep same thing for prisma migrate deploy - we do not expect this error as the migration script doesn't reference the shared multischema tables at all. With multischema we are trying to add a shared schema that is only read-only to this service so we do not need the ability to migrate it or touch it at all.

Migrator should detect somehow if it tries to migrate multischemas or it should be prohibited completely at least without a flag on the CLI like prisma migrate deploy --schemas .... which I think someone has suggested.

[Druue]

Steps:

1. Creating schema and table in pg

// psql
CREATE SCHEMA "app";
CREATE TABLE "app"."NewTable" (
    "id" BIGINT PRIMARY KEY
);

2. Defining schema

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

datasource db {
  provider = "postgresql"
  url      = env("TEST_POSTGRES_URI")
  schemas  = ["app"]
}

3. Run prisma db pull

❯ pnpm prisma db pull
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "tests", schemas "app" at "localhost:5432"

✔ Introspected 1 model and wrote it into prisma/schema.prisma in 47ms
      
Run prisma generate to generate Prisma Client.

4. See new model added to schema

model NewTable {
  id BigInt @id

  @@schema("app")
}

5. Run prisma migrate dev

❯ pnpm prisma migrate dev
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "tests", schemas "app" at "localhost:5432"

Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

If you are running this the first time on an existing database, please make sure to read this documentation page:
https://www.prisma.io/docs/guides/database/developing-with-prisma-migrate/troubleshooting-development

[+] Added tables
  - NewTable

? We need to reset the following schemas: "app" at "localhost:5432"
Do you want to continue? All data will be lost. › (y/N)

This was performed on the following version:

"devDependencies": {
	"prisma": "5.10.2"
}

Switching to "prisma": "5.2.0" does not yield a difference in error messaging for me

❯ pnpm prisma db pull
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "tests", schemas "app" at "localhost:5432"

✔ Introspected 1 model and wrote it into prisma/schema.prisma in 60ms
      
Run prisma generate to generate Prisma Client.

❯ pnpm prisma migrate dev
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "tests", schemas "app" at "localhost:5432"

Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

If you are running this the first time on an existing database, please make sure to read this documentation page:
https://www.prisma.io/docs/guides/database/developing-with-prisma-migrate/troubleshooting-development

[+] Added tables
  - NewTable

? We need to reset the following schemas: "app" at "localhost:5432"
Do you want to continue? All data will be lost. › (y/N)

[Druue]

Based on the process defined and what I followed above, I feel like I'm missing something as I cannot reproduce the error you outlined:

Error: P3005

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

@leppaott can you help me understand more of your setup going into encountering this error?

Edit:
Testing with migrate deploy, I can reproduce it - still confused about migrate dev though

❯ pnpm prisma migrate deploy
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "tests", schemas "app" at "localhost:5432"

No migration found in prisma/migrations

Error: P3005

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

[Druue]

I've split the issue with migrate deploy off into a separate issue as that is reproducible and seems to be different to migrate dev :)

#23327

[Druue]

This is the schema I used to test without multiSchema

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

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

As far as this being fixed when you remove the the multiSchema preview feature, what does that look like?

Are you then directly including app in your connection URL? as otherwise I run into the following error :)

❯ pnpm prisma db pull       
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "tests", schema "public" at "localhost:5432"

✖ Introspecting based on datasource defined in prisma/schema.prisma
Error: 
P4001 The introspected database was empty: 

prisma db pull could not create any models in your schema.prisma file and you will not be able to generate Prisma Client with the prisma generate command.

To fix this, you have two options:

- manually create a table in your database.
- make sure the database connection URL inside the datasource block in schema.prisma points to a database that is not empty (it must contain at least one table).

Then you can run prisma db pull again. 

If I directly include the schema in the connection url by appending the following ?schema=app

We can correctly introspect the database

❯ pnpm prisma db pull
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "tests", schema "app" at "localhost:5432"

✔ Introspected 1 model and wrote it into prisma/schema.prisma in 75ms
      
Run prisma generate to generate Prisma Client.

model NewTable {
  id BigInt @id
}

However, I still run into a drift error on migrate dev (which is expected, it's just not the error you ran into)

❯ pnpm prisma migrate dev   
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "tests", schema "app" at "localhost:5432"

Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

If you are running this the first time on an existing database, please make sure to read this documentation page:
https://www.prisma.io/docs/guides/database/developing-with-prisma-migrate/troubleshooting-development

[+] Added tables
  - NewTable

? We need to reset the "app" schema at "localhost:5432"
Do you want to continue? All data will be lost. › (y/N)

[Druue]

cc: @reza-ebrahimi

[Jolg42]

@reza-ebrahimi do you still need help here?

[SevInf]

Hey @reza-ebrahimi,

With the lack of information and our inability to reproduce this issue, we're now closing it. Feel free to comment if you have any updates about it.

Thank you 🙏