CODEWITHSUNDEEP
sqliteforeign-keystypeorm
chick3n0x07CC
chick3n0x07CC

Reputation: 798

Can "PRAGMA foreign_keys=OFF" SQLite statement be used inside a TypeORM migration?

I'm using TypeORM (v0.2.18) with Node.js (v12.7.0) to perform migrations in my SQLite database.

This is my situation: I have a table named country and a table named workflow. I want to remove one of the columns, named name, of country but workflow references country through this column.

Playing with DB Browser for SQLite I could remove the column successfully with these statements:

PRAGMA foreign_keys=OFF;
CREATE TEMPORARY TABLE country_backup(id, createdAt, updatedAt, enabled, codeIso2);
INSERT INTO country_backup SELECT id, createdAt, updatedAt, enabled, codeIso2 FROM country;
DROP TABLE country;
CREATE TABLE country(id, createdAt, updatedAt, enabled, codeIso2);
INSERT INTO country SELECT id, createdAt, updatedAt, enabled, codeIso2 FROM country_backup;
DROP TABLE country_backup;
PRAGMA foreign_keys=ON;

Which I used in a TypeORM migration like this:

...
public async up(queryRunner: QueryRunner): Promise<any> {
    await queryRunner.query("PRAGMA foreign_keys=OFF");
    await queryRunner.query("CREATE TEMPORARY TABLE country_backup(id, createdAt, updatedAt, enabled, codeIso2)");
    await queryRunner.query("INSERT INTO country_backup SELECT id, createdAt, updatedAt, enabled, codeIso2 FROM country");
    await queryRunner.query("DROP TABLE country");
    await queryRunner.query("CREATE TABLE country(id, createdAt, updatedAt, enabled, codeIso2)");
    await queryRunner.query("INSERT INTO country SELECT id, createdAt, updatedAt, enabled, codeIso2 FROM country_backup");
    await queryRunner.query("DROP TABLE country_backup");
    await queryRunner.query("PRAGMA foreign_keys=ON");
}
...

But I get this error:

Error during migration run:
QueryFailedError: SQLITE_CONSTRAINT: FOREIGN KEY constraint failed
    at new QueryFailedError (/.../api/src/error/QueryFailedError.ts:9:9)
    at Statement.handler (/.../src/driver/sqlite/SqliteQueryRunner.ts:53:26)
    at Statement.replacement (/.../api/node_modules/sqlite3/lib/trace.js:19:31)
    at Statement.replacement (/.../api/node_modules/sqlite3/lib/trace.js:19:31) {
  message: 'SQLITE_CONSTRAINT: FOREIGN KEY constraint failed',
  errno: 19,
  code: 'SQLITE_CONSTRAINT',
  name: 'QueryFailedError',
  query: 'DROP TABLE country',
  parameters: []
}

Why did it work in DB Browser for SQLite but not with TypeORM? It's like it ignored the PRAGMA statement.

Based on this, I tried with both PRAGMA foreign_keys and PRAGMA legacy_alter_table (setting them OFF and ON (before any action) and ON and OFF (after any action), respectively).

I tried using the PRAGMA's both inside the up() function and outside it. Outside it means I put the statements before and after await typeOrmConnection.runMigrations() in my main.ts file, like await typeOrmConnection.query("PRAGMA foreign_keys=OFF").

Upvotes: 3

Views: 5524

Answers (1)

Mitchell Sullivan
Mitchell Sullivan

Reputation: 51

The sqlite docs say that you can't set PRAGMA foreign_keys=off while executing multiple statements, so I tried this and got it work by separating the PRAGMA statements from the others.

A transaction has been started already for the query runner instance passed to the up() function. Therefore, end the existing transaction immediately, then use an outside-of-transaction query to turn off the checks, and then start another transaction.

Write the rest of your non-PRAGMA code within the new transaction.

Right before the end of the function, commit the inner transaction, then use a regular single-statement query to turn the key checks back on, and then start another transaction (so that there will be something to automatically close after up() runs, otherwise you get an error).

public async up(queryRunner: QueryRunner): Promise<any> {
    await queryRunner.commitTransaction();
    await queryRunner.query('PRAGMA foreign_keys=off');
    await queryRunner.startTransaction();

    // the rest of your queries

    await queryRunner.commitTransaction();
    await queryRunner.query('PRAGMA foreign_keys=on');
    await queryRunner.startTransaction();
}

Upvotes: 5

Related Questions