Problem Description #

I'm using TypeORM v0.2.18 with Node.js v12.7.0 to run migrations on an SQLite database. I have a country table and a workflow table, where workflow has a foreign key relationship to the name field of the country table. I need to delete the name field from the country table.

In DB Browser for SQLite, executing the following statements works perfectly to remove the field:

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;

But when I put this logic into the up function of a TypeORM migration, execution fails with:

Error during migration run: QueryFailedError: SQLITE_CONSTRAINT: FOREIGN KEY constraint failed...

The error triggers when running DROP TABLE country, as if the PRAGMA foreign_keys=OFF statement is being ignored. I've tried adjusting the placement of PRAGMA foreign_keys and PRAGMA legacy_alter_table statements (inside/outside the up function) but nothing fixes the issue. Why does this work in DB Browser but not in TypeORM?

Solution #

Root Cause #

TypeORM automatically enables foreign key constraints for SQLite connections by default, and each SQL statement in a migration might execute in a separate sub-connection or transaction. This means your PRAGMA foreign_keys=OFF setting doesn't apply to the subsequent DROP TABLE command.

SQLite PRAGMA settings are session-specific, but TypeORM's migration execution logic may create independent sessions for each query. Additionally, TypeORM's SQLite driver might enforce enabling foreign keys on connection initialization, overriding your manual settings.

Fixes to Try #

1. Wrap All Operations in a Single Transaction

Use the queryRunner parameter provided in the migration to manually control a transaction, ensuring all SQL runs in the same session:

import { MigrationInterface, QueryRunner } from "typeorm";

export class RemoveCountryName1234567890 implements MigrationInterface {
    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.startTransaction();
        try {
            // Disable foreign keys for this session
            await queryRunner.query("PRAGMA foreign_keys=OFF");
            
            // Create backup table
            await queryRunner.query(`CREATE TEMPORARY TABLE country_backup(id, createdAt, updatedAt, enabled, codeIso2)`);
            // Copy data to backup
            await queryRunner.query(`INSERT INTO country_backup SELECT id, createdAt, updatedAt, enabled, codeIso2 FROM country`);
            // Drop original table
            await queryRunner.query(`DROP TABLE country`);
            // Recreate table without the name column
            await queryRunner.query(`CREATE TABLE country(id, createdAt, updatedAt, enabled, codeIso2)`);
            // Restore data from backup
            await queryRunner.query(`INSERT INTO country SELECT id, createdAt, updatedAt, enabled, codeIso2 FROM country_backup`);
            // Clean up backup table
            await queryRunner.query(`DROP TABLE country_backup`);
            
            // Re-enable foreign keys
            await queryRunner.query("PRAGMA foreign_keys=ON");
            await queryRunner.commitTransaction();
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        }
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.startTransaction();
        try {
            await queryRunner.query("PRAGMA foreign_keys=OFF");
            
            await queryRunner.query(`CREATE TEMPORARY TABLE country_backup(id, createdAt, updatedAt, enabled, codeIso2, name)`);
            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, name)`);
            await queryRunner.query(`INSERT INTO country SELECT id, createdAt, updatedAt, enabled, codeIso2, name FROM country_backup`);
            await queryRunner.query(`DROP TABLE country_backup`);
            
            await queryRunner.query("PRAGMA foreign_keys=ON");
            await queryRunner.commitTransaction();
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        }
    }
}

2. Disable Auto Foreign Key Enforcement in TypeORM Config

Add SQLite-specific settings to your ormconfig.js or data-source.ts to turn off foreign keys during migration runs:

// ormconfig.js
module.exports = {
    // ... other configs
    type: "sqlite",
    database: "your-db.sqlite",
    extra: {
        "foreign_keys": "OFF"
    },
    // For TypeORM 0.2.x, you might need this instead:
    options: {
        foreignKeys: false
    }
};

⚠️ Note: This disables foreign keys globally. Remember to re-enable them after the migration completes, either by updating the config or adding a PRAGMA foreign_keys=ON statement at the end of your migration.

3. Use legacy_alter_table PRAGMA

SQLite's legacy_alter_table makes table structure changes behave more like traditional databases, which can help avoid foreign key conflicts. Add this at the start of your migration:

await queryRunner.query("PRAGMA legacy_alter_table=ON");

Key Notes #

• SQLite doesn't support direct column deletion (ALTER TABLE DROP COLUMN) until version 3.35.0+, so your backup-and-recreate approach is correct—you just need to ensure all steps run in the same session.
• Isolated calls to queryRunner.query() might use separate connections, which is why wrapping everything in a transaction is critical for applying PRAGMA settings consistently.

内容的提问来源于stack exchange，提问作者chick3n0x07CC