Improve handling of unexecutable migrations in Prisma Migrate

[albertoperdomo]

Problem

In certain cases, Prisma Migrate can't fully figure out how to make a certain migration happen.
Example: adding a new required field without a default value to a table with pre-existing records.

Prisma Migrate would usually create the column with the not-null constraint, but since there is no default value this would fail. Prisma Migrate currently fails with an error message.

Example of error message:

Error:
⚠️ We found changes that cannot be executed:

  • Step 0 Added the required column `tags` to the `Post` table without a default value. There are 1 rows in this table, it is not possible to execute this migration.

Problems:

• We could have a more obvious prompt to resolve this by adding a default value in the Prisma schema.
• It's not obvious to developers from the error message that --create-only is an option in this case.
• Developers can use migrate dev --create-only to at least create a migration file, even if the migration will fail to run without manually modifying to deal with the underlying issues
• The generated migration uses a single step and having the right SQL/DDL statements will require significant effort from developers to get right, as they have to throw away some of the logic and replace it with their own

Possible Solutions

For the specific example described above there are multiple ways to migrate the database schema:

1. Add a default value to the field definition in Prisma schema
2. Introduce the column first, populate the column of existing rows with values, then add the not-null constraint. All of this in the same migration.
3. Same as 2 but using one migration for adding the column and another for adding the constraint.

Approach 1 can be addressed by the developer but may not always be a desirable or feasible solution depending on the use case
Approach 2 can can be implemented by the developer in the migration file, but Prisma Migrate errors and does not generate any migration file unless using --create-only. The migration script currently renders a single statement to add the column with the constraint and and to make the migration work the developer will need to do heavy editing to turn this into several steps and be able to intertwine the migration themselves.
Approach 3 Might be a simpler approach from the user's POV and adding an UPDATE statement to the first migration even if non-trivial requires less fiddling than in solution 2.

Suggested solution

[albertoperdomo]

Let's improve the output as a first step.

[Jolg42]

I'm going for

You can use ${getCommandWithExecutor(
        'prisma migrate dev --create-only --preview-feature',
      )} to create the migration file, and manually modify it to address the underlying issue(s).
Then run ${getCommandWithExecutor(
        'prisma migrate dev --preview-feature',
      )} to apply it and verify it works.\n`)

Happy to edit it you have suggestions, (you can also do it in the PR here #5738)