Migrate: Migrate requires CREATE SCHEMA permission

[julien1619]

Bug description

Starting from the final version of Prisma 2, the migrate up command tries to create a new schema.

https://github.com/prisma/prisma-engines/blob/219a8d806a3568142dee4c8673e767750e9d0136/migration-engine/connectors/sql-migration-connector/src/flavour.rs#L195

On some managed Cloud database you don't have the permission to create any schema, even if you try to create an existing one, failing the migration command.

How to reproduce

Steps to reproduce the behavior:

1. Setup a CloudDB without CREATE SCHEMA permission (mine is an OVH CloudDB)
2. Run prisma migrate up
3. See error : Error: undefined: Unknown error occured: Failure during a migration command: Connector error. (error: Error querying the database: Error querying the database: db error: ERROR: permission denied for database jda-preprod )

Expected behavior

Migration should work.

Environment & setup

• OS: macOS
• Database: PostgreSQL 12
• Prisma version: 2.1.0
• Node.js version: 14.5.0

[tomhoule]

We currently have CREATE SCHEMA IF NOT EXISTS in the initialization step of the migration engine. I think it's a totally legitimate problem and we should at least offer to disable this. I'll raise the issue to the product team, we're doing a lot of work on migrations currently and re-thinking migrate, let's make sure we take this into account.

[albertoperdomo]

We should explore if we can check if the schema is present before running the CREATE SCHEMA. Or check of the user has permissions.

[tomhoule]

https://github.com/prisma/prisma-engines/pull/1073 simplifies things: when the CLI prompts you to create the database, if it is not there, it will try to create the schema as well, but we no longer try to create the schema on startup. I think this is much more consistent, and it should help with this issue.