typeorm
migration:generate does not automatically drop tables
Issue Description
When I create an entity, generate a migration, and run said migration, it creates a table for the entity in the mysql database as expected. When I delete the entity, and attempt to generate a new migration on top of the old one, the typeorm does not find any changes.
Expected Behavior
I expected typeorm to recognize that the entity did not exist and so the table could be dropped. Typeorm should have made a new migration to drop the table whose entity was deleted.
Actual Behavior
I made the following entity:
import {
Entity,
PrimaryGeneratedColumn,
} from 'typeorm';
@Entity()
export class Foo {
@PrimaryGeneratedColumn()
id: number;
}
I ran typeorm migration:generate -- -n addFoo which created the following migration:
import {MigrationInterface, QueryRunner} from "typeorm";
export class addFoo1624984985251 implements MigrationInterface {
name = 'addFoo1624984985251'
public async up(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query("CREATE TABLE `foo` (`id` int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB");
}
public async down(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query("DROP TABLE `foo`");
}
}
I ran typeorm migration:run which ran the migration and updated the database as expected.
I deleted the file in which foo was defined.
I ran typeorm migration:generate -- -n removeFoo add got the following output:
> [email protected] typeorm /usr/src/app
> node --require ts-node/register ./node_modules/typeorm/cli.js "migration:generate" "-n" "removeFoo"
No changes in database schema were found - cannot generate a migration. To create a new empty migration use "typeorm migration:create" command
npm ERR! code ELIFECYCLE
npm ERR! errno 1
npm ERR! [email protected] typeorm: `node --require ts-node/register ./node_modules/typeorm/cli.js "migration:generate" "-n" "removeFoo"`
npm ERR! Exit status 1
npm ERR!
npm ERR! Failed at the [email protected] typeorm script.
npm ERR! This is probably not a problem with npm. There is likely additional logging output above.
npm ERR! A complete log of this run can be found in:
npm ERR! /root/.npm/_logs/2021-06-29T16_41_52_271Z-debug.log
Steps to Reproduce
The entity and migrations above are the files in their entirety, so the entity can be used to reproduce.
- Create an entity
- Generate a migration
- Run migrations
- Delete entity
- Generate a migration
My Environment
| Dependency | Version | | Operating System | macOS Big Sur v11.4 | | Node.js version | v16.2.0 | | Typescript version | v4.2.4 | | TypeORM version | v0.2.32 |
Additional Context
I first found this issue when joining tables in many-to-many relationships were not being dropped automatically. This open issue also has this bug https://github.com/typeorm/typeorm/issues/5463 This issue occurs both for regular tables for declared entities and for joining tables. Typeorm has no problem automatically changing everything else such as foreign keys, column names, etc.
Relevant Database Driver(s)
- [x]
mysql
Are you willing to resolve this issue by submitting a Pull Request?
- [x] No, I don't have the time and I wouldn't even know how to start.
Has there been any movement on this or a workaround outside of using a manually edited migration script with the drop in it?
@mariuszbeltowski if you want to remove migration firstly you should revert it, delete the reverted file, remove the directory with the built project and build it again, and finally run the project. it should work
I think this is intended behavior. There could be other tables in the database that were not generated through TypeORM, and you don't want your migration:generate to always generate DROP for these every time.
I think you safest bet is to create new empty migration (migration:create -n "DropTable"), and manually copy the down part of the previous migration you want to "revert" to the up part and vice versa (of course just with the entities you want to delete, there could be more in the migration).
Hope this helps.
If you have dev database, and can't find previous migration that up and down the table.
First, I will directly drop table in dev database by using GUI or sql, then run npm run migration:generate -n='RemoveArtistScheduleTable' to create new migration. It will generate below queryRunner migration code.
import {MigrationInterface, QueryRunner} from "typeorm";
export class RemoveArtistScheduleTable1668646897289 implements MigrationInterface {
name = 'RemoveArtistScheduleTable1668646897289'
public async up(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`CREATE TABLE "artists_schedules" ("artist_schedule_id" SERIAL NOT NULL, "name_zh" character varying(128) NOT NULL, "name_en" character varying(128) NOT NULL, "start_date" TIMESTAMP NOT NULL, "end_date" TIMESTAMP NOT NULL, "note" character varying(128), CONSTRAINT "PK_afe7d234982880815818a709f42" PRIMARY KEY ("artist_schedule_id"))`);
await queryRunner.query(`CREATE TABLE "artists_schedules_j" ("artist_id" uuid NOT NULL, "artist_schedule_id" integer NOT NULL, CONSTRAINT "PK_49265248f108d71b1ce9ecd2f21" PRIMARY KEY ("artist_id", "artist_schedule_id"))`);
await queryRunner.query(`CREATE INDEX "IDX_63c4b28c4b8133e39341a9603f" ON "artists_schedules_j" ("artist_id") `);
await queryRunner.query(`CREATE INDEX "IDX_aa80b7ae66291fccde2e4b3182" ON "artists_schedules_j" ("artist_schedule_id") `);
await queryRunner.query(`ALTER TABLE "artists_schedules_j" ADD CONSTRAINT "FK_63c4b28c4b8133e39341a9603f4" FOREIGN KEY ("artist_id") REFERENCES "artists"("artist_id") ON DELETE SET NULL ON UPDATE CASCADE`);
await queryRunner.query(`ALTER TABLE "artists_schedules_j" ADD CONSTRAINT "FK_aa80b7ae66291fccde2e4b31823" FOREIGN KEY ("artist_schedule_id") REFERENCES "artists_schedules"("artist_schedule_id") ON DELETE CASCADE ON UPDATE CASCADE`);
}
public async down(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`ALTER TABLE "artists_schedules_j" DROP CONSTRAINT "FK_aa80b7ae66291fccde2e4b31823"`);
await queryRunner.query(`ALTER TABLE "artists_schedules_j" DROP CONSTRAINT "FK_63c4b28c4b8133e39341a9603f4"`);
await queryRunner.query(`DROP TABLE "artists_schedules_j"`);
await queryRunner.query(`DROP TABLE "artists_schedules"`);
}
}
Second, manually run the sql to create table again. In this case, execute below sql to re-create table.
CREATE TABLE "artists_schedules" ("artist_schedule_id" SERIAL NOT NULL, "name_zh" character varying(128) NOT NULL, "name_en" character varying(128) NOT NULL, "start_date" TIMESTAMP NOT NULL, "end_date" TIMESTAMP NOT NULL, "note" character varying(128), CONSTRAINT "PK_afe7d234982880815818a709f42" PRIMARY KEY ("artist_schedule_id"))
CREATE TABLE "artists_schedules_j" ("artist_id" uuid NOT NULL, "artist_schedule_id" integer NOT NULL, CONSTRAINT "PK_49265248f108d71b1ce9ecd2f21" PRIMARY KEY ("artist_id", "artist_schedule_id"))
CREATE INDEX "IDX_63c4b28c4b8133e39341a9603f" ON "artists_schedules_j" ("artist_id")
CREATE INDEX "IDX_aa80b7ae66291fccde2e4b3182" ON "artists_schedules_j" ("artist_schedule_id")
ALTER TABLE "artists_schedules_j" ADD CONSTRAINT "FK_63c4b28c4b8133e39341a9603f4" FOREIGN KEY ("artist_id") REFERENCES "artists"("artist_id") ON DELETE SET NULL ON UPDATE CASCADE
ALTER TABLE "artists_schedules_j" ADD CONSTRAINT "FK_aa80b7ae66291fccde2e4b31823" FOREIGN KEY ("artist_schedule_id") REFERENCES "artists_schedules"("artist_schedule_id") ON DELETE CASCADE ON UPDATE CASCADE
Finally, switch queryRunner method. Then delete your entity, associated code and run npm run migration:run on your dev and prod database.
import {MigrationInterface, QueryRunner} from "typeorm";
export class RemoveArtistScheduleTable1668646897289 implements MigrationInterface {
name = 'RemoveArtistScheduleTable1668646897289'
public async up(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`ALTER TABLE "artists_schedules_j" DROP CONSTRAINT "FK_aa80b7ae66291fccde2e4b31823"`);
await queryRunner.query(`ALTER TABLE "artists_schedules_j" DROP CONSTRAINT "FK_63c4b28c4b8133e39341a9603f4"`);
await queryRunner.query(`DROP TABLE "artists_schedules_j"`);
await queryRunner.query(`DROP TABLE "artists_schedules"`);
}
public async down(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`CREATE TABLE "artists_schedules" ("artist_schedule_id" SERIAL NOT NULL, "name_zh" character varying(128) NOT NULL, "name_en" character varying(128) NOT NULL, "start_date" TIMESTAMP NOT NULL, "end_date" TIMESTAMP NOT NULL, "note" character varying(128), CONSTRAINT "PK_afe7d234982880815818a709f42" PRIMARY KEY ("artist_schedule_id"))`);
await queryRunner.query(`CREATE TABLE "artists_schedules_j" ("artist_id" uuid NOT NULL, "artist_schedule_id" integer NOT NULL, CONSTRAINT "PK_49265248f108d71b1ce9ecd2f21" PRIMARY KEY ("artist_id", "artist_schedule_id"))`);
await queryRunner.query(`CREATE INDEX "IDX_63c4b28c4b8133e39341a9603f" ON "artists_schedules_j" ("artist_id") `);
await queryRunner.query(`CREATE INDEX "IDX_aa80b7ae66291fccde2e4b3182" ON "artists_schedules_j" ("artist_schedule_id") `);
await queryRunner.query(`ALTER TABLE "artists_schedules_j" ADD CONSTRAINT "FK_63c4b28c4b8133e39341a9603f4" FOREIGN KEY ("artist_id") REFERENCES "artists"("artist_id") ON DELETE SET NULL ON UPDATE CASCADE`);
await queryRunner.query(`ALTER TABLE "artists_schedules_j" ADD CONSTRAINT "FK_aa80b7ae66291fccde2e4b31823" FOREIGN KEY ("artist_schedule_id") REFERENCES "artists_schedules"("artist_schedule_id") ON DELETE CASCADE ON UPDATE CASCADE`);
}
}
I know this is a little bit complicated, but I always do this way. Hope this can help.
I think this should be reconsider, the main use-case is when you update your data model and you remove an entity, you want the migration to remove the table in your schema. I think migrations should always be in sync with registered entities for TypeORM.
Happy to discuss this topic or help debug to find a way to improve this.
