migrate dev can not be used to fix "invalid" migrations over time

[janpio]

#5782 means that in 2.26.0 with referentialActions enable we still by default create invalid migrations for SQL Server. When applying it, you get an error message that let's you (potentially) understand what you need to do - add a onUpdate: Cascade to a @relation in your schema.

Unfortunately you can not use migrate dev to iterate on that, as when you do the change and run migrate dev --create-only again, it fails with a shadow database error as the first migration can not be applied to the shadow database :(

C:\Users\Jan\Documents\throwaway\sqlServerCycles>npx prisma migrate deploy
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db" - SQL Server

1 migration found in prisma/migrations
Error: P3018

A migration failed to apply. New migrations can not 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: 20210704123327_init

Database error code: 1785

Database error:
Introducing FOREIGN KEY constraint 'FK__Comment__postId' on table 'Comment' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.


### edit the schema here


C:\Users\Jan\Documents\throwaway\sqlServerCycles>npx prisma migrate dev --create-only
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db" - SQL Server

Error: P3006

Migration `20210704123327_init` failed to apply cleanly to the shadow database. 
Error:
Database error
Error querying the database: 'Introducing FOREIGN KEY constraint 'FK__Comment__postId' on table 'Comment' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.' on server EC2AMAZ-HEFESC9 executing  
on line 57 (code: 1785, state: 0, class: 16)
   0: sql_migration_connector::validate_migrations
             at migration-engine\connectors\sql-migration-connector\src\lib.rs:322
   1: migration_core::api::DevDiagnostic
             at migration-engine\core\src\api.rs:89

Instead I have to delete the old migration, and create a new one that does everything in 1 step.

[tomhoule]

Instead I have to delete the old migration, and create a new one that does everything in 1 step.

I think that's pretty much the intended workflow — that or edit the existing migration.

[janpio]

Might this be a "hole" in the feature set to make handling errors, which can just happen, easier?

In the example above, the next step would be to change the schema and then create another migration. In other cases, it might be setting a datasource property (plantScaleMode/noForeignKeys) or something similar to that. The current instructions are not super helpful in this case and basically are "deal with it manually, good luck".