Migrate: Fail reverting migrations when many to many relation exist on Postgresql

[2013-11390]

Bug description

Report ID: 3289

1. Create prisma schema and migrate to postgresql.

model Post {
  id         Int        @id @default(autoincrement())
  categories Category[] @relation(references: [id])
}
model Category {
  id    Int    @id @default(autoincrement())
  posts Post[] @relation(references: [id])
}

1. It fails migrations down after up.

How to reproduce

Steps to reproduce the behavior:

1. Create prisma schema as below.(Which is the same as schema written in https://www.prisma.io/docs/reference/tools-and-interfaces/prisma-schema/relations#many-to-many-relations)
2. npx prisma migrate save --name initSchema --experimental
3. npx prisma migrate up --experimental
4. npx prisma migrate down --experimental
5. See Error, In real database, tables still exist in the database.
   Failure during a migration command: Connector error. (error: Error querying the database: Error querying the database: Error querying the database: db error: ERROR: cannot drop
   table "Post" because other objects depend on it)

1. After above error occured, I try migrate down with below command.
   npx prisma migrate down --experimental DEBUG="*"

Then another error occured.

Environment variables loaded from ./prisma/.env
Error: Invalid migration step NaN

After some tries of migration.

• npx prisma migrate up --experimental => No statements inferrable. Done
• npx prisma migrate down --experimental DEBUG="*" => Error: Invalid migration step NaN
• npx prisma migrate down --experimental => Rolling back migration 20200410171929-init-schema
• npx prisma migrate down --experimental => No migration to roll back

However, there are tables with single column: id.

Expected behavior

migration undo success without Error

Prisma information

datasource postgresql {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
model Post {
  id         Int        @id @default(autoincrement())
  categories Category[] @relation(references: [id])
}
model Category {
  id    Int    @id @default(autoincrement())
  posts Post[] @relation(references: [id])
}

Environment & setup

• OS: Mac OS
• Database: PostgreSQL

• Prisma version:
  @prisma/cli : 2.0.0-beta.2
  Current platform : darwin
  Query Engine : query-engine 76857c35ba1e1764dd5473656ecbbb2f739e1822
  Migration Engine : migration-engine-cli 76857c35ba1e1764dd5473656ecbbb2f739e1822
  Introspection Engine : introspection-core 76857c35ba1e1764dd5473656ecbbb2f739e1822

• Node.js version: v13.11.0

[pantharshit00]

I can confirm this bug. Thanks for reporting!

[albertoperdomo]

We think it may be fixed. Can you please try reproducing again @pantharshit00 ?

[pantharshit00]

Yes, this is already fixed in 2.9.0

╰─ npx prisma migrate down --experimental                                                                                                                                                                                          ─╯
Environment variables loaded from prisma/.env
Prisma Schema loaded from prisma/schema.prisma
Rolling back migration 20201015083605-init-schema
🚀   Done with down in 101ms

Closing..