Enum with enumName in two tables fails on second synchronize

[santiher]

Issue Description

When using the same enum with the same enumName in two different tables, the second time syncing the code to the database breaks.

Expected Behavior

Using an enum twice in different tables should not break when syncing the database for a second time.

Actual Behavior

Using an enum twice in different tables breaks when creating a connection with synchronize: true more than once (or calling synchronize).

I get the following error:

QueryFailedError: cannot drop type enum_status_old because other objects depend on it
    at new QueryFailedError (/data/gitlab/every/pruebas_node/enum_attempt/src/error/QueryFailedError.ts:9:9)
    at PostgresQueryRunner.<anonymous> (/data/gitlab/every/pruebas_node/enum_attempt/src/driver/postgres/PostgresQueryRunner.ts:228:19)
    at step (/data/gitlab/every/pruebas_node/enum_attempt/node_modules/tslib/tslib.js:141:27)
    at Object.throw (/data/gitlab/every/pruebas_node/enum_attempt/node_modules/tslib/tslib.js:122:57)
    at rejected (/data/gitlab/every/pruebas_node/enum_attempt/node_modules/tslib/tslib.js:113:69)
    at processTicksAndRejections (node:internal/process/task_queues:94:5) {
  length: 276,
  severity: 'ERROR',
  code: '2BP01',
  detail: 'column fromAccountStatus of table emitted_check depends on type enum_status_old',
  hint: 'Use DROP ... CASCADE to drop the dependent objects too.',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'dependency.c',
  line: '971',
  routine: 'reportDependentObjects',
  query: 'DROP TYPE "enum_status_old"',
  parameters: []
}

Steps to Reproduce

Basic code example:

import {createConnection, Connection} from "typeorm";
import {
  Check,
  Column,
  Entity,
  Index,
  JoinColumn,
  ManyToOne,
  OneToMany,
  PrimaryGeneratedColumn,
  Unique,
} from "typeorm";

enum Status {
  OK = "OK",
  VERY_OK = "VERY_OK",
  ERROR = "ERROR",
}

@Entity()
class Account {

  @PrimaryGeneratedColumn({ type: "int" })
  public id!: number;

  @Column({
    type: "enum",
    enum: Status,
    enumName: "enum_status",
  })
  public status!: Status;

}

@Entity()
class EmittedCheck {

  @PrimaryGeneratedColumn()
  public id!: number;

  @Column({
    type: "enum",
    enum: Status,
    enumName: "enum_status",
    nullable: true,
  })
  public status!: Status | null;

}

async function main() {
  const connection: Connection = await createConnection({
      type: "postgres",
      host: "localhost",
      username: "testuser",
      password: "testpassword",
      database: "testdatabase",
      entities: [Account, EmittedCheck],
      // synchronize: true,
  });
  await connection.synchronize();
  await connection.synchronize();
}

main()
  .then(() => {
  })
  .catch((error) => {
    console.log(error);
    process.exit(1);
  });

Code example with full context of what I want to do:

import {createConnection, Connection} from "typeorm";
import {
  Check,
  Column,
  Entity,
  Index,
  JoinColumn,
  ManyToOne,
  OneToMany,
  PrimaryGeneratedColumn,
  Unique,
} from "typeorm";

enum Status {
  OK = "OK",
  VERY_OK = "VERY_OK",
  ERROR = "ERROR",
}

@Entity()
@Unique("account_id_and_status_unique_index", ["id", "status"])  // Needed for foreign composite key
class Account {

  @PrimaryGeneratedColumn({ type: "int" })
  public id!: number;

  @Column({
    type: "enum",
    enum: Status,
    enumName: "enum_status",
  })
  public status!: Status;

  @Column({ type: "text" })
  public owner!: string;

  @OneToMany(
    (type) => EmittedCheck,
    (check) => check.fromAccount
  )
  public checks?: EmittedCheck[];

}

@Entity()
@Check(
  `"fromAccountStatus" IN ('${Status.OK}', '${Status.VERY_OK}')`
)
class EmittedCheck {

  @PrimaryGeneratedColumn()
  public id!: number;

  @Column({ type: "int" })
  public amount!: number;

  @Column({ type: "int", nullable: true })
  public fromAccountId!: number | null;

  @Column({
    type: "enum",
    enum: Status,
    enumName: "enum_status",
    nullable: true,
  })
  public fromAccountStatus!: Status | null;

  @ManyToOne(
    (type) => Account,
    (account) => account.checks,
    {
      onDelete: "RESTRICT",
      nullable: false,
    }
  )
  @JoinColumn([
    { name: "fromAccountId", referencedColumnName: "id" },
    { name: "fromAccountStatus", referencedColumnName: "status" },
  ])
  public fromAccount?: Account;

}

async function main() {
  const connection: Connection = await createConnection({
      type: "postgres",
      host: "localhost",
      username: "testuser",
      password: "testpassword",
      database: "testdatabase",
      entities: [Account, EmittedCheck],
      // synchronize: true,
  });
  await connection.synchronize();
  await connection.synchronize();
}

main()
  .then(() => {
  })
  .catch((error) => {
    console.log(error);
    process.exit(1);
  });

I did two connection.synchronize() at the end, but it could be running twice a script doing only once.

My Environment

Dependency	Version
Operating System	linux 5.10.18-1-lts
Node.js version	v15.10.0
TypeORM version	0.2.31

Additional Context

The reason for bumping into this problem and the code example is because I needed a composite foreign key between two tables, an one of the columns is an enum.

Relevant Database Driver(s)

• aurora-data-api
• aurora-data-api-pg
• better-sqlite3
• cockroachdb
• cordova
• expo
• mongodb
• mysql
• nativescript
• oracle
• postgres
• react-native
• sap
• sqlite
• sqlite-abstract
• sqljs
• sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time, and I know how to start.
• Yes, I have the time, but I don't know how to start. I would need guidance.
• No, I don't have the time, although I believe I could do it if I had the time...
• No, I don't have the time and I wouldn't even know how to start.

I don't know how much time it would take ¯\(ツ)/¯

[zonofthor]

Exactly same issue here.

Version 0.2.31 implemented support for this kind of synchronisation of enums but seemingly is not working.

[AlexMesser]

same as #5648. fixed in #7419. The fix will be available in the next release (0.2.32).

[santiher]

Awesome news, thanks, really appreciate it!

[AlexMesser]

fix now available in version 0.2.32. Please update your TypeORM version and reopen this issue if you run into the error again.

[rbideau]

The bug still happen with embedded entity

The entities:

import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';

enum TestEnum {
  test = 'test',
}

class TestAEmbedded {
  @Column({
    type: 'enum',
    enum: TestEnum,
    enumName: 'test_enum',
  })
  testEnumA: TestEnum;
}

@Entity()
export class TestA {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column(() => TestAEmbedded, { prefix: true })
  embedded: TestAEmbedded;
}
@Entity()
export class TestB {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({
    type: 'enum',
    enum: TestEnum,
    enumName: 'test_enum',
  })
  testEnumB: TestEnum;
}

The generated migration:

import { MigrationInterface, QueryRunner } from "typeorm";

export class MyMigrationName1674574030711 implements MigrationInterface {
    name = 'MyMigrationName1674574030711'

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`
            CREATE TYPE "public"."test_enum" AS ENUM('test')
        `);
        await queryRunner.query(`
            CREATE TABLE "test_a" (
                "id" uuid NOT NULL DEFAULT uuid_generate_v4(),
                "embedded_test_enum_a" "public"."test_enum" NOT NULL,
                CONSTRAINT "PK_16eb42d7d3d65ae555af54a3cd9" PRIMARY KEY ("id")
            )
        `);
        await queryRunner.query(`
            CREATE TYPE "public"."test_enum" AS ENUM('test')
        `);
        await queryRunner.query(`
            CREATE TABLE "test_b" (
                "id" uuid NOT NULL DEFAULT uuid_generate_v4(),
                "test_enum_b" "public"."test_enum" NOT NULL,
                CONSTRAINT "PK_25d8d88d8a1ae98ad35e8ebada8" PRIMARY KEY ("id")
            )
        `);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`
            DROP TABLE "test_b"
        `);
        await queryRunner.query(`
            DROP TYPE "public"."test_enum"
        `);
        await queryRunner.query(`
            DROP TABLE "test_a"
        `);
        await queryRunner.query(`
            DROP TYPE "public"."test_enum"
        `);
    }

}

Tested with typeorm version 0.3.11

[rbideau]

Our current workaround is to have a script that parse the migration with regex and wrap CREATE/ALTER/DROP statement on enum with DO $$ BEGIN ${statement} EXCEPTION WHEN dependent_objects_still_exist THEN null; END $$; but this is very ugly

Here is the gist of the full script

@AlexMesser, would you be willing to re-open this issue ?

[eyalrin]

I see this being raised again and again (and now I encountered it too)… Can it be addressed?