planetScaleMode = true is not preserved during db pull

[emk]

Bug description

I'm trying to find a way to support a Postgres server that uses Citus, a distributed Postgres extension. Citus supports FOREIGN KEY constraints on distributed tables, but they can be extremely cumbersome to set up (especially when they involve "reference" tables with circular relationships). So I'm trying to make Prisma and db pull work without any FOREIGN KEY constraints.

(This bug report was requested by @janpio on Slack.)

How to reproduce

1. Set up a PostgreSQL connection to a database with no declared FOREIGN KEY constraints.

   generator client {
     provider        = "prisma-client-js"
     previewFeatures = ["planetScaleMode"]
   }
   
   datasource db {
     provider        = "postgresql"
     url             = "postgres://postgres@localhost:15432/postgres"
     planetScaleMode = true
   }
   
   model Parent {
     id      Int   @id
     child   Child @relation(fields: [childId], references: [id])
     childId Int
   }
   
   model Child {
     id     Int      @id
     Parent Parent[]
   }
   

2. Run prisma db push to migrate the schema

3. Run prisma db pull.

   Result: The datasource will lose planetScaleMode = true:

   generator client {
     provider        = "prisma-client-js"
     previewFeatures = ["planetScaleMode"]
   }
   
   datasource db {
     provider = "postgresql"
     url      = "postgres://postgres@localhost:15432/postgres"
   }
   
   model Parent {
     id      Int @id
     childId Int
   }
   
   model Child {
     id Int @id
   }
   

   (The schema also looses all relations, but that is covered in Re-introspection should not remove relations when foreign keys are not allowed #9147)

Expected behavior

Prisma should preserve planetScaleMode = true when prisma db pull is run.

Prisma information

See above.

Environment & setup

• OS: Linux
• Database: PostgreSQL Citus
• Node.js version: v14.17.4

Prisma Version

prisma                : 2.30.3
@prisma/client        : 2.30.3
Current platform      : debian-openssl-1.1.x
Query Engine (Binary) : query-engine b8c35d44de987a9691890b3ddf3e2e7effb9bf20 (at node_modules/@prisma/engines/query-engine-debian-openssl-1.1.x)
Migration Engine      : migration-engine-cli b8c35d44de987a9691890b3ddf3e2e7effb9bf20 (at node_modules/@prisma/engines/migration-engine-debian-openssl-1.1.x)
Introspection Engine  : introspection-core b8c35d44de987a9691890b3ddf3e2e7effb9bf20 (at node_modules/@prisma/engines/introspection-engine-debian-openssl-1.1.x)
Format Binary         : prisma-fmt b8c35d44de987a9691890b3ddf3e2e7effb9bf20 (at node_modules/@prisma/engines/prisma-fmt-debian-openssl-1.1.x)
Default Engines Hash  : b8c35d44de987a9691890b3ddf3e2e7effb9bf20
Studio                : 0.423.0
Preview Features      : planetScaleMode

[janpio]

The relations being removed is probably expected right now, our issue that will solve this is #9147

That planetScaleMode = true is being removed is unexpected (to me) and we will try to reproduce that.

[janpio]

I can confirm that planetScaleMode = true is also removed when running db pull on a schema for MySQL, so this is definitely a problem that needs to be fixed in the next iteration of this feature.

(I updated the issue above to only mention this problem, as everything else was covered in #9147)

[janpio]

planetScaleMode = true will be superseded by something called referenitalIntegrity = "prisma". That is already available in the dev version of Prisma, but there the same problem applies currently:

1. Create folder and go into it
2. npm init -y and npm install prisma@dev
3. npx prisma init --datasource-provider mysql, update connection string in .env to valid one
4. Update the schema.prisma to this:

   // This is your Prisma schema file,
   // learn more about it in the docs: https://pris.ly/d/prisma-schema
   
   datasource db {
     provider = "mysql"
     url      = env("DATABASE_URL")
     referentialIntegrity = "prisma"
   }
   
   generator client {
     provider = "prisma-client-js"
     previewFeatures = ["referentialIntegrity"]
   }
   
   model Parent {
     id      Int   @id
     child   Child @relation(fields: [childId], references: [id])
     childId Int
   }
   
   model Child {
     id     Int      @id
     Parent Parent[]
   }

5. npm prisma db push to create schema online
6. npm prisma db pull updates the schema to the following:

   generator client {
     provider        = "prisma-client-js"
     previewFeatures = ["referentialIntegrity"]
   }
   
   datasource db {
     provider = "mysql"
     url      = env("DATABASE_URL")
   }
   
   model Parent {
     id      Int   @id
     child   Child @relation(fields: [childId], references: [id])
     childId Int
   }
   
   model Child {
     id     Int      @id
     Parent Parent[]
   }

   (Note how the referentialIntegrity = "prisma" is gone in the datasource but the relation information is still present)