Invalid down migration when renaming the column

[mandrysek]

Describe the bug
I have renamed and added a few date columns. Up migration is correct, but down migration drops the column instead of renaming back. This could cause some problems.

Migration:

import { Migration } from '@mikro-orm/migrations';

export class Migration20231112110050 extends Migration {
  async up(): Promise<void> {
    this.addSql('alter table "ad_delivery" add column "completed_at" timestamptz(0) null, add column "canceled_at" timestamptz(0) null;');
    this.addSql('alter table "ad_delivery" rename column "delivered_at" to "arrived_at";');
  }

  async down(): Promise<void> {
    this.addSql('alter table "ad_delivery" add column "delivered_at" timestamptz(0) null;');
    this.addSql('alter table "ad_delivery" drop column "arrived_at";');
    this.addSql('alter table "ad_delivery" drop column "completed_at";');
    this.addSql('alter table "ad_delivery" drop column "canceled_at";');
  }
}

Stack trace
N/A

To Reproduce
Steps to reproduce the behavior:

1. Create entity
2. Rename column
3. Create migration

Expected behavior
Column should be renamed back instead of dropping

Additional context
N/A

Versions

Dependency	Version
node	20.9.0
typescript	5.2.2
mikro-orm	5.9.3
postgresql	5.9.3

[B4nan]

The problem here is that we only consider something as renamed if there is exactly one candidate column (same type, different name), but you are adding two and renaming one, and when you do it the other way, there are 3 candidate columns for the removed one. I am not sure if it's a good idea to lift this restriction and always do it for the first match - it wouldn't be much better I guess, as this would be dependent on the column order and you might not rename the first one.

I will try to respect the up schema diff when detecting the renamings in down diff, I guess that could work for this particular case.