BUG: TypeORM does not generate correct migrations.

[atstoyanov]

Describe the bug
After upgrading to nestJS 8 and typeORM 0.2.35 the generated migrations contain a lot of differences.

To Reproduce
Steps to reproduce the behavior:

1. Open the server project
2. Create a new schema
3. run npm run migration:run
4. run npm run migration:generate <name for the migration>
5. Open the generated migration.
6. The newly generated migration contains many queries which are updating indexes, PKs and FKs.
7. The newly generated migration contains the schema name.

Expected behavior
The newly migration should contains empty UP and Down methods
The newly generated migration should not contain the schema name.

Additional context
Sometimes the typeorm doesn't take into account the env variables from the .env file.
The generated FKs, PKs and indexies are different than existing one, so the issue maybe related to changes in our model.

This two queries are from the first and last migrations. The SQL from the last migration has been amended (removed the schema name, and escape backslashes) to look like the one from the first.

ALTER TABLE `application_questions` ADD CONSTRAINT `FK_8495fae86f13836b0745642baa8` FOREIGN KEY (`applicationId`) REFERENCES `application`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION

ALTER TABLE `application_questions` ADD CONSTRAINT `FK_8495fae86f13836b0745642baa8` FOREIGN KEY (`applicationId`) REFERENCES `application`(`id`) ON DELETE CASCADE ON UPDATE CASCADE

It seems like we updated the ON UPDATE action without generating a new query. Unfortunately there is no change in the code related to the ON UPDATE action, so the problem seems related to the TypeORM and changes they've made between 0.2.32 and 0.2.35 versions

[atstoyanov]

There is a merged PR that should resolve the issue with the database name in the migrations: typeorm/typeorm#8038 which is still not in a release.

[atstoyanov]

After merging of typeorm/typeorm#5714 they've change the default ON UPDATE from NO ACTION to CASCADE in the Junction Metadata builder which results in different result in the migration builder. As result our database looks like has many changes, without changing anything. This change was released in version 0.2.33. We were stuck with version 0.2.32 until our upgrade to nestJS 8, when we had to upgrade to typeorm 0.2.35 since then the generated migration are putting a lot of changes.

Possible Solutions:

1. Executing migrations and checking for data loss.
2. Amending the ON UPDATE cascades in the code so no changes will be introduced. More work, but as result we wouldn't lose any data.

There are some disturbing queries in the migrations:
UP:

`ALTER TABLE \`alkemio2\`.\`credential\` DROP COLUMN \`id\``
`ALTER TABLE \`alkemio2\`.\`credential\` ADD \`id\` char(36) NOT NULL PRIMARY KEY`

DOWN:

`ALTER TABLE \`alkemio2\`.\`credential\` DROP COLUMN \`id\``
`ALTER TABLE \`alkemio2\`.\`credential\` ADD \`id\` varchar(36) NOT NULL`
`ALTER TABLE \`alkemio2\`.\`credential\` ADD PRIMARY KEY (\`id\`)`

This migrations are dropping and recreating the ID column for a few tables. MySQL doesn't have a mechanism to generate UUID ids automatically out of the box and additional code might be required.

[atstoyanov]

I've tested solution 2 briefly and it might not work. :(

[techsmyth]

Option 2 sounds like the better option. Suprised there is not an option in their config that we can set what the default for ON UPDATE should be.

Re the migrations that are dropping / recreating ids, are these in our existing migrations or in the new ones that are being generated?

[atstoyanov]

The new ones.

[techsmyth]

typeorm/typeorm#8167

[techsmyth]

New release is out that seems to have some updates in migrations: https://github.com/typeorm/typeorm/releases/tag/0.2.38
Though at first glance not enough

[techsmyth]

and another release: https://github.com/typeorm/typeorm/releases/tag/0.2.39 - with another at least one migration fix...

[techsmyth]

and two more releases...https://github.com/typeorm/typeorm/releases/tag/0.2.41

[Wintereise]

typeorm/typeorm#8038 from 0.2.38 also breaks pretty much anyone with existing data generated by an older TypeORM version (0.2.37 in this case).

It is now attempting to recreate tables that already exist. I imagine a fix would be to dump -> drop -> recreate -> reimport, but that's very high effort and requires a real maint window.