'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
andprod
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 thisinitialMigration.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 |