migrate dev does not detect drift between database schema and migration history for multiSchema

[janpio]

1. Create a PostgreSQL database
2. Run the following SQL:

   CREATE SCHEMA schema1;
   
   CREATE TABLE schema1.foo (
   	id uuid NOT NULL,
   	CONSTRAINT foo_pkey PRIMARY KEY (id)
   );
   
   CREATE SCHEMA schema2;
   
   CREATE TABLE schema2.bar (
   	id uuid NOT NULL,
   	CONSTRAINT bar_pkey PRIMARY KEY (id)
   );
   ALTER TABLE schema2.bar ADD CONSTRAINT bar_id_fkey FOREIGN KEY (id) REFERENCES schema1.foo(id);

3. Set up a Prisma project
4. Modify the schema file minimally:

   generator client {
     provider        = "prisma-client-js"
     previewFeatures = ["multiSchema"]
   }
   
   datasource db {
     provider = "postgresql"
     url      = env("DATABASE_URL")
     schemas  = ["schema1", "schema2"]
   }

5. Run npx prisma db pull to fill the schema
6. Result of that:

   generator client {
     provider        = "prisma-client-js"
     previewFeatures = ["multiSchema"]
   }
   
   datasource db {
     provider = "postgresql"
     url      = env("DATABASE_URL")
     schemas  = ["schema1", "schema2"]
   }
   
   model foo {
     id  String @id @db.Uuid
     bar bar?
   
     @@schema("schema1")
   }
   
   model bar {
     id  String @id @db.Uuid
     foo foo    @relation(fields: [id], references: [id], onDelete: NoAction, onUpdate: NoAction)
   
     @@schema("schema2")
   }
   

7. Now run npx prisma migrate dev
8. Observe:

   PS C:\Users\Jan\Documents\throwaway\crossSchemaIntrospection\docker> npx prisma migrate dev
   Environment variables loaded from .env
   Prisma schema loaded from prisma\schema.prisma
   Datasource "db": PostgreSQL database "postgres", schema "public" at "localhost:5438"
   
   √ Enter a name for the new migration: ... init
   Applying migration `20221205172823_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: 20221205172823_init
   
   Database error code: 42P07
   
   Database error:
   ERROR: relation "foo" already exists
   
   DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E42P07), message: "relation \"foo\" already exists", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("heap.c"), line: Some(1146), routine: Some("heap_create_with_catalog") }
   

I would have 8) to given me a message instead that it detected drift between my history and the the database schema, and that I need to reset before I can continue. Instead it created a valid migration:

-- CreateSchema
CREATE SCHEMA IF NOT EXISTS "schema1";

-- CreateSchema
CREATE SCHEMA IF NOT EXISTS "schema2";

-- CreateTable
CREATE TABLE "schema1"."foo" (
    "id" UUID NOT NULL,

    CONSTRAINT "foo_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "schema2"."bar" (
    "id" UUID NOT NULL,

    CONSTRAINT "bar_pkey" PRIMARY KEY ("id")
);

-- AddForeignKey
ALTER TABLE "schema2"."bar" ADD CONSTRAINT "bar_id_fkey" FOREIGN KEY ("id") REFERENCES "schema1"."foo"("id") ON DELETE NO ACTION ON UPDATE NO ACTION;

And then failed when executing that, as both schemas and tables of course already exist.

[floelhoeffel]

I can reproduce this with @dev

[pimeys]

Should be fixed in prisma/prisma-engines#3481