'TypeORM migration results in PostgreSQL error

I am having an issue running migrations with TypeORM (with the intent of establishing my database for use in local development). In running a migration on the initialMigration.ts file (seen below), I encounter an error (also seen below).

Technologies:

  • TypeORM
  • PostgreSQL (Dockerized)

Notes:

  • All migrations run successfully in both staging and prod environments.
  • If I remove the initialMigration.ts file and run subsequent migrations, I encounter similar errors.
  • debt_creditcard_bank_enum does not figure outside of this initialMigration.ts file: it is not included in the database schema as modelled by TypeORM entities.

Output Error

query failed: CREATE TYPE "debt_creditcard_bank_enum" AS ENUM('anz', 'bnz', 'kiwi_bank', 'asb', 'westpac')
error: error: type "debt_creditcard_bank_enum" already exists
    at Parser.parseErrorMessage (/Users/slackermorris/Code/my-life-time/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/Users/slackermorris/Code/my-life-time/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/Users/slackermorris/Code/my-life-time/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (/Users/slackermorris/Code/my-life-time/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (events.js:400:28)
    at Socket.emit (domain.js:475:12)
    at addChunk (internal/streams/readable.js:293:12)
    at readableAddChunk (internal/streams/readable.js:267:9)
    at Socket.Readable.push (internal/streams/readable.js:206:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23) {
  length: 105,
  severity: 'ERROR',
  code: '42710',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'typecmds.c',
  line: '1125',
  routine: 'DefineEnum'
}
query: ROLLBACK

initialMigration.ts

import { MigrationInterface, QueryRunner } from 'typeorm'

export class initialMigration1626152356992 implements MigrationInterface {
  name = 'initialMigration1626152356992'

  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `CREATE TABLE "asset_simpleasset" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "value" integer NOT NULL, "name" character varying NOT NULL, "imageName" character varying, "assetId" uuid NOT NULL, CONSTRAINT "REL_70b9aad22d76bd4ac0e4740f0d" UNIQUE ("assetId"), CONSTRAINT "PK_6749983df9277b0a95dec81e5f2" PRIMARY KEY ("id"))`
    )
    await queryRunner.query(
      `CREATE TYPE "debt_creditcard_bank_enum" AS ENUM('anz', 'bnz', 'kiwi_bank', 'asb', 'westpac')`
    )
    await queryRunner.query(
      `CREATE TABLE "debt_creditcard" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "bank" "debt_creditcard_bank_enum" NOT NULL, "amountOwing" integer NOT NULL, "creditLimit" integer NOT NULL, "dueDate" TIMESTAMP WITH TIME ZONE NOT NULL, "interestRate" numeric(4,2) NOT NULL, "debtId" uuid, CONSTRAINT "REL_dfe04163e90cdb6983e1cc45d1" UNIQUE ("debtId"), CONSTRAINT "PK_326498a6cfa1cac005bd0636008" PRIMARY KEY ("id"))`
    )
    await queryRunner.query(
      `CREATE TABLE "debt_simpledebt" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "value" integer NOT NULL, "name" character varying NOT NULL, "provider" character varying NOT NULL, "interestRate" numeric(5,2), "fixedUntil" date, "imageName" character varying, "debtId" uuid NOT NULL, CONSTRAINT "REL_7dec9b430b4ea2eb4835394a9c" UNIQUE ("debtId"), CONSTRAINT "PK_d05e9683d592e8565da6cdccf68" PRIMARY KEY ("id"))`
    )
    await queryRunner.query(
      `CREATE TABLE "debt_debt" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "createdDate" TIMESTAMP NOT NULL DEFAULT now(), "updatedDate" TIMESTAMP NOT NULL DEFAULT now(), "deletedDate" TIMESTAMP, "userId" uuid, CONSTRAINT "PK_021fb92ef0ae668fc22dae7e5d2" PRIMARY KEY ("id"))`
    )
    await queryRunner.query(
      `CREATE TABLE "goal_goal" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "title" character varying NOT NULL, "amount" integer, "targetDate" date, "imageName" character varying, "notes" character varying, "completedAt" TIMESTAMP, "createdDate" TIMESTAMP NOT NULL DEFAULT now(), "updatedDate" TIMESTAMP NOT NULL DEFAULT now(), "deletedDate" TIMESTAMP, "userId" uuid, CONSTRAINT "PK_2d199148876db1d4ff34ac2b0d5" PRIMARY KEY ("id"))`
    )
    await queryRunner.query(
      `CREATE TABLE "netwealth_netwealthgoal" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "goalAmount" integer NOT NULL, "targetDate" date, "createdDate" TIMESTAMP NOT NULL DEFAULT now(), "userId" uuid, CONSTRAINT "REL_22f64c344357799eed3396aa37" UNIQUE ("userId"), CONSTRAINT "PK_b6c41aa3e29a7fb6af707e13874" PRIMARY KEY ("id"))`
    )
    await queryRunner.query(
      `CREATE TABLE "user_session" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "refreshToken" character varying NOT NULL, "accessToken" character varying NOT NULL, "accessExpiry" TIMESTAMP NOT NULL, "userId" uuid, CONSTRAINT "PK_adf3b49590842ac3cf54cac451a" PRIMARY KEY ("id"))`
    )
    await queryRunner.query(
      `CREATE TABLE "user_user" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "authUserID" character varying NOT NULL, "email" character varying NOT NULL, "firstName" character varying NOT NULL, "lastName" character varying NOT NULL, "phone" character varying, "address" character varying, "clientID" character varying NOT NULL, "imageName" character varying, "confirmed" boolean NOT NULL DEFAULT false, "pendingUpdateApproval" boolean NOT NULL DEFAULT false, "createdDate" TIMESTAMP NOT NULL DEFAULT now(), "updatedDate" TIMESTAMP NOT NULL DEFAULT now(), "seenGoalIntroAt" TIMESTAMP, "seenWelcomeIntroAt" TIMESTAMP, CONSTRAINT "PK_758b8ce7c18b9d347461b30228d" PRIMARY KEY ("id"))`
    )
    await queryRunner.query(
      `CREATE TABLE "asset_asset" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "createdDate" TIMESTAMP NOT NULL DEFAULT now(), "updatedDate" TIMESTAMP NOT NULL DEFAULT now(), "deletedDate" TIMESTAMP, "userId" uuid NOT NULL, CONSTRAINT "PK_2a48e81afa7729ed31c2c7b18ed" PRIMARY KEY ("id"))`
    )
    await queryRunner.query(
      `ALTER TABLE "asset_simpleasset" ADD CONSTRAINT "FK_70b9aad22d76bd4ac0e4740f0d8" FOREIGN KEY ("assetId") REFERENCES "asset_asset"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`
    )
    await queryRunner.query(
      `ALTER TABLE "debt_creditcard" ADD CONSTRAINT "FK_dfe04163e90cdb6983e1cc45d1f" FOREIGN KEY ("debtId") REFERENCES "debt_debt"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`
    )
    await queryRunner.query(
      `ALTER TABLE "debt_simpledebt" ADD CONSTRAINT "FK_7dec9b430b4ea2eb4835394a9c9" FOREIGN KEY ("debtId") REFERENCES "debt_debt"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`
    )
    await queryRunner.query(
      `ALTER TABLE "debt_debt" ADD CONSTRAINT "FK_897cfe7be6a1ae85d817a08ab81" FOREIGN KEY ("userId") REFERENCES "user_user"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`
    )
    await queryRunner.query(
      `ALTER TABLE "goal_goal" ADD CONSTRAINT "FK_19edfdaa3a24fea90abc2517632" FOREIGN KEY ("userId") REFERENCES "user_user"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`
    )
    await queryRunner.query(
      `ALTER TABLE "netwealth_netwealthgoal" ADD CONSTRAINT "FK_22f64c344357799eed3396aa37f" FOREIGN KEY ("userId") REFERENCES "user_user"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`
    )
    await queryRunner.query(
      `ALTER TABLE "user_session" ADD CONSTRAINT "FK_b5eb7aa08382591e7c2d1244fe5" FOREIGN KEY ("userId") REFERENCES "user_user"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`
    )
    await queryRunner.query(
      `ALTER TABLE "asset_asset" ADD CONSTRAINT "FK_e892ac65f522d96347a0e3bc0ce" FOREIGN KEY ("userId") REFERENCES "user_user"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`
    )
    await queryRunner.query(`CREATE VIEW "asset_netasset" AS 
  SELECT
  SUM(simpleasset.value) AS value,
  asset. "userId" AS "userId"
FROM
  asset_simpleasset "simpleasset"
  LEFT JOIN asset_asset "asset" ON "simpleasset"."assetId" = "asset"."id"
WHERE
  asset. "deletedDate" IS NULL
GROUP BY
  "asset"."userId"
`)
    await queryRunner.query(
      `INSERT INTO "typeorm_metadata"("type", "schema", "name", "value") VALUES ($1, $2, $3, $4)`,
      [
        'VIEW',
        'public',
        'asset_netasset',
        'SELECT\n  SUM(simpleasset.value) AS value,\n  asset. "userId" AS "userId"\nFROM\n  asset_simpleasset "simpleasset"\n  LEFT JOIN asset_asset "asset" ON "simpleasset"."assetId" = "asset"."id"\nWHERE\n  asset. "deletedDate" IS NULL\nGROUP BY\n  "asset"."userId"'
      ]
    )
    await queryRunner.query(`CREATE VIEW "debt_netdebt" AS 
  SELECT
  SUM(simpleDebt.value) AS value,
  debt. "userId" AS "userId"
FROM
  debt_simpledebt "simpledebt"
  LEFT JOIN debt_debt "debt" ON "simpledebt"."debtId" = "debt"."id"
WHERE
  debt. "deletedDate" IS NULL
GROUP BY
  "debt"."userId"
`)
    await queryRunner.query(
      `INSERT INTO "typeorm_metadata"("type", "schema", "name", "value") VALUES ($1, $2, $3, $4)`,
      [
        'VIEW',
        'public',
        'debt_netdebt',
        'SELECT\n  SUM(simpleDebt.value) AS value,\n  debt. "userId" AS "userId"\nFROM\n  debt_simpledebt "simpledebt"\n  LEFT JOIN debt_debt "debt" ON "simpledebt"."debtId" = "debt"."id"\nWHERE\n  debt. "deletedDate" IS NULL\nGROUP BY\n  "debt"."userId"'
      ]
    )
    await queryRunner.query(`CREATE VIEW "netWealth_netWealth" AS 
SELECT
    "userId",
    SUM("value") as "value"
FROM (
    SELECT
        "value",
        "userId"
  FROM
        asset_netasset
  UNION ALL
  SELECT
        -"value",
        "userId"
    FROM
        debt_netdebt) "userId"
GROUP BY
    "userId"
`)
    await queryRunner.query(
      `INSERT INTO "typeorm_metadata"("type", "schema", "name", "value") VALUES ($1, $2, $3, $4)`,
      [
        'VIEW',
        'public',
        'netWealth_netWealth',
        'SELECT\n\t"userId",\n\tSUM("value") as "value"\nFROM (\n\tSELECT\n\t\t"value",\n\t\t"userId"\n  FROM\n\t\tasset_netasset\n  UNION ALL\n  SELECT\n\t\t-"value",\n\t\t"userId"\n\tFROM\n\t\tdebt_netdebt) "userId"\nGROUP BY\n\t"userId"'
      ]
    )
  }
  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `DELETE FROM "typeorm_metadata" WHERE "type" = 'VIEW' AND "schema" = $1 AND "name" = $2`,
      ['public', 'netWealth_netWealth']
    )
    await queryRunner.query(`DROP VIEW "netWealth_netWealth"`)
    await queryRunner.query(
      `DELETE FROM "typeorm_metadata" WHERE "type" = 'VIEW' AND "schema" = $1 AND "name" = $2`,
      ['public', 'debt_netdebt']
    )
    await queryRunner.query(`DROP VIEW "debt_netdebt"`)
    await queryRunner.query(
      `DELETE FROM "typeorm_metadata" WHERE "type" = 'VIEW' AND "schema" = $1 AND "name" = $2`,
      ['public', 'asset_netasset']
    )
    await queryRunner.query(`DROP VIEW "asset_netasset"`)
    await queryRunner.query(
      `ALTER TABLE "asset_asset" DROP CONSTRAINT "FK_e892ac65f522d96347a0e3bc0ce"`
    )
    await queryRunner.query(
      `ALTER TABLE "user_session" DROP CONSTRAINT "FK_b5eb7aa08382591e7c2d1244fe5"`
    )
    await queryRunner.query(
      `ALTER TABLE "netwealth_netwealthgoal" DROP CONSTRAINT "FK_22f64c344357799eed3396aa37f"`
    )
    await queryRunner.query(
      `ALTER TABLE "goal_goal" DROP CONSTRAINT "FK_19edfdaa3a24fea90abc2517632"`
    )
    await queryRunner.query(
      `ALTER TABLE "debt_debt" DROP CONSTRAINT "FK_897cfe7be6a1ae85d817a08ab81"`
    )
    await queryRunner.query(
      `ALTER TABLE "debt_simpledebt" DROP CONSTRAINT "FK_7dec9b430b4ea2eb4835394a9c9"`
    )
    await queryRunner.query(
      `ALTER TABLE "debt_creditcard" DROP CONSTRAINT "FK_dfe04163e90cdb6983e1cc45d1f"`
    )
    await queryRunner.query(
      `ALTER TABLE "asset_simpleasset" DROP CONSTRAINT "FK_70b9aad22d76bd4ac0e4740f0d8"`
    )
    await queryRunner.query(`DROP TABLE "asset_asset"`)
    await queryRunner.query(`DROP TABLE "user_user"`)
    await queryRunner.query(`DROP TABLE "user_session"`)
    await queryRunner.query(`DROP TABLE "netwealth_netwealthgoal"`)
    await queryRunner.query(`DROP TABLE "goal_goal"`)
    await queryRunner.query(`DROP TABLE "debt_debt"`)
    await queryRunner.query(`DROP TABLE "debt_simpledebt"`)
    await queryRunner.query(`DROP TABLE "debt_creditcard"`)
    await queryRunner.query(`DROP TYPE "debt_creditcard_bank_enum"`)
    await queryRunner.query(`DROP TABLE "asset_simpleasset"`)
  }
}


Solution 1:[1]

Try to check your ormconfig file...And, make sure synchronize: false, migrationsRun: false is there for production. As,synchronize: false will make sure there is no data loss in production and migrationRun:false will make sure your connection won't try to run your migration query 2 times when you are running runMigration() function.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Amit Kumar gouda