Cascaded table data is dropped on column add during migration

[dominic-simplan]

Issue type:

[x] question [x] bug report [ ] feature request [ ] documentation issue

Database system/driver:

[x] cordova [ ] mongodb [ ] mssql [ ] mysql / mariadb [ ] oracle [ ] postgres [ ] sqlite [ ] sqljs [ ] react-native [ ] expo

TypeORM version:

[x] latest [ ] @next [ ] 0.x.x (or put your version here)

I have a tableA which has ON CASCADE DELETE foreign key contstraint on tableB. Now I add a column to tableB during a migration, and all data of tableA is gone, as the migration drops and recreates tableB:

await queryRunner.addColumn("Project", new TableColumn({
            name: "CustomVersion",
            type: "varchar",
            isNullable: true
        }));

query:  CREATE TABLE "temporary_Project" (...)
query:  INSERT INTO "temporary_Project"...
query:  DROP TABLE "Project"

Shouldn't the foreign key constraints be handled/disabled before dropping the table? Or am I doing it wrong?

[dominic-simplan]

Also seems like await queryRunner.query("PRAGMA foreign_keys=OFF;"); as a workaround has no effect but I don't know why :-(

[dominic-simplan]

The workaround is to change the foreign_key before starting the migration:

await connection.query("PRAGMA foreign_keys=OFF;");
await connection.runMigrations();
await connection.query("PRAGMA foreign_keys=ON;");

The foreign_key cannot be changed in the migration itself as migrations are executed in a transaction and foreign_key cannot be changed within a transaction:

It is not possible to enable or disable foreign key constraints in the middle of a multi-statement transaction (when SQLite is not in autocommit mode). Attempting to do so does not return an error; it simply has no effect. Source: https://www.sqlite.org/foreignkeys.html

[pleerock]

Interesting issue...

I'm not completely sure but we might try to disable foreign keys before table recreation here in recreateTable method. Can you add it on your own and test everything, and if it works create a PR?

[dominicdesu]

@pleerock As far as I can see, the recreateTable method is executed within a transaction? Then it is not possible to change the foreign_key pragma, that needs to be done before the transaction is created...

[sinopsysHK]

Should be closed with #9775