Generated migrations are very noisy with lots of duplicated or otherwise useless queries

[rmainwork]

Issue type:

• question
• bug report
• feature request
• documentation issue

Database system/driver:

• cordova
• mongodb
• mssql
• mysql / mariadb
• oracle
• postgres
• cockroachdb
• sqlite
• sqljs
• react-native
• expo

TypeORM version:

• latest
• @next
• 0.2.18 (or put your version here)

Steps to reproduce or a small repository showing the problem:

I previously had a many to many join between two tables. I am replacing this with a join entity in order to add additional columns to it.

import { MigrationInterface, QueryRunner } from 'typeorm';

export class FacultyTable1561005425013 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query('CREATE TYPE "faculty_category_enum" AS ENUM(\'ladder\', \'non_ladder\')');
    await queryRunner.query('CREATE TABLE "faculty" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "createdAt" TIMESTAMP NOT NULL DEFAULT now(), "updatedAt" TIMESTAMP NOT NULL DEFAULT now(), "firstName" character varying NOT NULL DEFAULT \'\', "lastName" character varying NOT NULL DEFAULT \'\', "HUID" character varying NOT NULL, "category" "faculty_category_enum" NOT NULL DEFAULT \'non_ladder\', CONSTRAINT "PK_635ca3484f9c747b6635a494ad9" PRIMARY KEY ("id"))');
    await queryRunner.query('CREATE INDEX "IDX_37a534d3bdf13e458a16d00e31" ON "faculty" ("HUID") ');
    await queryRunner.query('CREATE TABLE "faculty_course_instances_course_instance" ("facultyId" uuid NOT NULL, "courseInstanceId" uuid NOT NULL, "order" int NOT NULL, CONSTRAINT "PK_43da76a139c24e501bcb78c45e2" PRIMARY KEY ("facultyId", "courseInstanceId"))');
    await queryRunner.query('CREATE INDEX "IDX_65d45c5857d419295ee55c16de" ON "faculty_course_instances_course_instance" ("facultyId") ');
    await queryRunner.query('CREATE INDEX "IDX_7d5d1b3b6714381a6e7ded5f63" ON "faculty_course_instances_course_instance" ("courseInstanceId") ');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" ADD CONSTRAINT "FK_65d45c5857d419295ee55c16de5" FOREIGN KEY ("facultyId") REFERENCES "faculty"("id") ON DELETE CASCADE ON UPDATE NO ACTION');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" ADD CONSTRAINT "FK_7d5d1b3b6714381a6e7ded5f63c" FOREIGN KEY ("courseInstanceId") REFERENCES "course_instance"("id") ON DELETE CASCADE ON UPDATE NO ACTION');
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" DROP CONSTRAINT "FK_7d5d1b3b6714381a6e7ded5f63c"');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" DROP CONSTRAINT "FK_65d45c5857d419295ee55c16de5"');
    await queryRunner.query('DROP INDEX "IDX_7d5d1b3b6714381a6e7ded5f63"');
    await queryRunner.query('DROP INDEX "IDX_65d45c5857d419295ee55c16de"');
    await queryRunner.query('DROP TABLE "faculty_course_instances_course_instance"');
    await queryRunner.query('DROP INDEX "IDX_37a534d3bdf13e458a16d00e31"');
    await queryRunner.query('DROP TABLE "faculty"');
    await queryRunner.query('DROP TYPE "faculty_category_enum"');
  }
}

From there, if I run orm migration:generate typeorm generates the following migration with loads of create/drop indexes, columns etc. in

import { MigrationInterface, QueryRunner } from 'typeorm';

export class Foo1565638728727 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<any> {
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" DROP CONSTRAINT "FK_7d5d1b3b6714381a6e7ded5f63c"');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" DROP CONSTRAINT "FK_65d45c5857d419295ee55c16de5"');
    await queryRunner.query('DROP INDEX "IDX_65d45c5857d419295ee55c16de"');
    await queryRunner.query('DROP INDEX "IDX_7d5d1b3b6714381a6e7ded5f63"');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" ADD "uuid" uuid NOT NULL');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" DROP CONSTRAINT "PK_43da76a139c24e501bcb78c45e2"');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" ADD CONSTRAINT "PK_4de4e8db71f9ff92b37c3c35376" PRIMARY KEY ("facultyId", "courseInstanceId", "uuid")');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" ALTER COLUMN "courseInstanceId" DROP NOT NULL');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" DROP CONSTRAINT "PK_4de4e8db71f9ff92b37c3c35376"');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" ADD CONSTRAINT "PK_54859f6624da4fda96193a14bc5" PRIMARY KEY ("facultyId", "uuid")');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" ALTER COLUMN "facultyId" DROP NOT NULL');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" DROP CONSTRAINT "PK_54859f6624da4fda96193a14bc5"');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" ADD CONSTRAINT "PK_b532c5a1d158dd0e414356ad9cd" PRIMARY KEY ("uuid")');
    await queryRunner.query('ALTER TABLE "view" ALTER COLUMN "columns" SET DEFAULT \'{}\'::varchar[]');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" ADD CONSTRAINT "FK_7d5d1b3b6714381a6e7ded5f63c" FOREIGN KEY ("courseInstanceId") REFERENCES "course_instance"("id") ON DELETE NO ACTION ON UPDATE NO ACTION');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" ADD CONSTRAINT "FK_65d45c5857d419295ee55c16de5" FOREIGN KEY ("facultyId") REFERENCES "faculty"("id") ON DELETE NO ACTION ON UPDATE NO ACTION');
  }

  public async down(queryRunner: QueryRunner): Promise<any> {
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" DROP CONSTRAINT "FK_65d45c5857d419295ee55c16de5"');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" DROP CONSTRAINT "FK_7d5d1b3b6714381a6e7ded5f63c"');
    await queryRunner.query('ALTER TABLE "view" ALTER COLUMN "columns" SET DEFAULT \'{}\'');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" DROP CONSTRAINT "PK_b532c5a1d158dd0e414356ad9cd"');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" ADD CONSTRAINT "PK_54859f6624da4fda96193a14bc5" PRIMARY KEY ("facultyId", "uuid")');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" ALTER COLUMN "facultyId" SET NOT NULL');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" DROP CONSTRAINT "PK_54859f6624da4fda96193a14bc5"');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" ADD CONSTRAINT "PK_4de4e8db71f9ff92b37c3c35376" PRIMARY KEY ("facultyId", "courseInstanceId", "uuid")');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" ALTER COLUMN "courseInstanceId" SET NOT NULL');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" DROP CONSTRAINT "PK_4de4e8db71f9ff92b37c3c35376"');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" ADD CONSTRAINT "PK_43da76a139c24e501bcb78c45e2" PRIMARY KEY ("facultyId", "courseInstanceId")');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" DROP COLUMN "uuid"');
    await queryRunner.query('CREATE INDEX "IDX_7d5d1b3b6714381a6e7ded5f63" ON "faculty_course_instances_course_instance" ("courseInstanceId") ');
    await queryRunner.query('CREATE INDEX "IDX_65d45c5857d419295ee55c16de" ON "faculty_course_instances_course_instance" ("facultyId") ');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" ADD CONSTRAINT "FK_65d45c5857d419295ee55c16de5" FOREIGN KEY ("facultyId") REFERENCES "faculty"("id") ON DELETE CASCADE ON UPDATE NO ACTION');
    await queryRunner.query('ALTER TABLE "faculty_course_instances_course_instance" ADD CONSTRAINT "FK_7d5d1b3b6714381a6e7ded5f63c" FOREIGN KEY ("courseInstanceId") REFERENCES "course_instance"("id") ON DELETE CASCADE ON UPDATE NO ACTION');
  }
}

[Kononnable]

Please provide schema before and after changes which this migration was made from.

[rmainwork]

@Kononnable I don't quite understand your question. Do you want me to post the before/after of the model classes?

[osdiab]

This is happening to me too.

Here's an example with check constraints. I don't change anything about my models at all, I just call migration:generate and I get the following kind of behavior:

Models

Helpers:

export function einDbType(
  options: Omit<ColumnOptions, "type" | "transformer">
): ColumnOptions {
  return {
    ...options,
    type: "character varying",
    transformer: {
      from: ein => normalizeEin(ein),
      // Outstanding bug in TypeORM: transformer may receive a FindOperator
      // instead of the actual value of the class member.
      // https://github.com/typeorm/typeorm/issues/2390
      // eslint-disable-next-line @typescript-eslint/no-explicit-any
      to: (entityValue: string | FindOperator<any>) => {
        if (typeof entityValue !== "string") {
          return entityValue;
        }

        const normalizedEin = normalizeEin(entityValue);
        if (!normalizedEin) {
          throw new Error(
            `ein provided to transformer was not a valid EIN: "${entityValue}"`
          );
        }

        return normalizedEin;
      }
    }
  };
}

export function einCheckConstraint(colName: string) {
  return `"${colName}" ~* '${EIN_REGEX_STR}'`;
}

Nonprofit:

@Entity()
@Check(einCheckConstraint("ein"))
export class Nonprofit {
  // ...
  @Column(einDbType({ unique: true, nullable: false }))
  public ein!: string;
}

Same code exists in a few other entities, whose tables are nonprofit_metadata and nonprofit_public_metadata.

Generated Migrations

class MyMigration implements MigrationInterface {
    public async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query(`ALTER TABLE "nonprofit" DROP CONSTRAINT "CHK_b3f7d9090dbcc5276b6548c9f3"`);
        await queryRunner.query(`ALTER TABLE "nonprofit_metadata" DROP CONSTRAINT "CHK_68b699c8dea84bf62767bfe15a"`);
        await queryRunner.query(`ALTER TABLE "nonprofit_public_metadata" DROP CONSTRAINT "CHK_a1cb07d86769c652bbd748b9c9"`);
        await queryRunner.query(`ALTER TABLE "nonprofit" ADD CONSTRAINT "CHK_41a3bddb38534fda1bca83276b" CHECK ("ein" ~* '^[0-9]{9}$')`);
        await queryRunner.query(`ALTER TABLE "nonprofit_metadata" ADD CONSTRAINT "CHK_9d267bc1175b85f906c79b94dc" CHECK ("ein" ~* '^[0-9]{9}$')`);
        await queryRunner.query(`ALTER TABLE "nonprofit_public_metadata" ADD CONSTRAINT "CHK_d612fd06767fd686f649b89409" CHECK ("ein" ~* '^[0-9]{9}$')`);
      }

    public async down(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query(`ALTER TABLE "nonprofit_public_metadata" DROP CONSTRAINT "CHK_d612fd06767fd686f649b89409"`);
        await queryRunner.query(`ALTER TABLE "nonprofit_metadata" DROP CONSTRAINT "CHK_9d267bc1175b85f906c79b94dc"`);
        await queryRunner.query(`ALTER TABLE "nonprofit" DROP CONSTRAINT "CHK_41a3bddb38534fda1bca83276b"`);
        await queryRunner.query(`ALTER TABLE "nonprofit_public_metadata" ADD CONSTRAINT "CHK_a1cb07d86769c652bbd748b9c9" CHECK (((ein)::text ~* '^[0-9]{9}$'::text))`);
        await queryRunner.query(`ALTER TABLE "nonprofit_metadata" ADD CONSTRAINT "CHK_68b699c8dea84bf62767bfe15a" CHECK (((ein)::text ~* '^[0-9]{9}$'::text))`);
        await queryRunner.query(`ALTER TABLE "nonprofit" ADD CONSTRAINT "CHK_b3f7d9090dbcc5276b6548c9f3" CHECK (((ein)::text ~* '^[0-9]{9}$'::text))`);
    }
}

In reality all of this is noise and useless and I just have to keep deleting them.

EDIT: right now seeing if it's better if I just give the check constraints names... Giving the check constraints names makes this stop happening, probably an issue with naming confusing the migration generator

[osdiab]

Maybe related to #3280 ?

[osdiab]

And also looks like this is related too, but was closed even though the problem wasn't actually fixed for a number of people: #1686

[imnotjames]

Closing this as a duplicate of #3280