Schema与数据库不匹配致TypeORM迁移问题及外键命名需求

问题背景 #

未修改Schema或数据库的情况下，TypeORM自动生成了删除一批外键的迁移文件，推测是之前直接修改数据库但未同步Schema，导致Schema与数据库状态不一致。现在需要按指定名称（如email_notifications_settings_droid_id_fkey、push_notifications_settings_account_id_fkey）创建PostgreSQL外键约束。

解决方案 #

1. 给实体Schema明确指定外键约束名称（根源解决） #

要让TypeORM生成迁移时使用你指定的外键名称，必须在实体类的@JoinColumn装饰器里配置foreignKeyConstraintName参数。这样既保证迁移生成的外键名称符合要求，也能避免Schema与数据库不一致导致的无效自动迁移。

以EmailNotificationsSettings实体为例：

import { Entity, Column, ManyToOne, JoinColumn } from "typeorm";
import { Account } from "./Account";
import { Droid } from "./Droid";

@Entity("email_notifications_settings")
export class EmailNotificationsSettings {
    // 其他字段定义...

    @Column({ name: "account_id" })
    accountId: string;

    @Column({ name: "droid_id" })
    droidId: string;

    @ManyToOne(() => Account, { onDelete: "CASCADE" })
    @JoinColumn({
        name: "account_id",
        foreignKeyConstraintName: "email_notifications_settings_account_id_fkey" // 明确指定外键名称
    })
    account: Account;

    @ManyToOne(() => Droid, { onDelete: "CASCADE" })
    @JoinColumn({
        name: "droid_id",
        foreignKeyConstraintName: "email_notifications_settings_droid_id_fkey" // 明确指定外键名称
    })
    droid: Droid;
}

对PushNotificationsSettings、DevicesSettings、MapSettings等其他需要指定外键名称的实体，做同样的配置，把每个外键的foreignKeyConstraintName设置为你需要的名称。

2. 调整现有迁移文件（处理当前生成的无效迁移） #

如果这个自动生成的迁移还没执行，直接修改它的up方法，确保删除旧外键后重新添加指定名称的外键；如果已经执行了，就创建新的迁移来修正。

修改现有迁移文件示例：

import {MigrationInterface, QueryRunner} from "typeorm";

export class asdf1696256327292 implements MigrationInterface {
    name = 'asdf1696256327292'

    public async up(queryRunner: QueryRunner): Promise<void> {
        // 先检查约束是否存在再删除，避免执行报错
        await queryRunner.query(`DO $$ BEGIN IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'devices_settings_account_id_fkey') THEN ALTER TABLE "devices_settings" DROP CONSTRAINT "devices_settings_account_id_fkey"; END IF; END $$;`);
        await queryRunner.query(`DO $$ BEGIN IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'email_notifications_settings_account_id_fkey') THEN ALTER TABLE "email_notifications_settings" DROP CONSTRAINT "email_notifications_settings_account_id_fkey"; END IF; END $$;`);
        await queryRunner.query(`DO $$ BEGIN IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'email_notifications_settings_droid_id_fkey') THEN ALTER TABLE "email_notifications_settings" DROP CONSTRAINT "email_notifications_settings_droid_id_fkey"; END IF; END $$;`);
        await queryRunner.query(`DO $$ BEGIN IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'push_notifications_settings_account_id_fkey') THEN ALTER TABLE "push_notifications_settings" DROP CONSTRAINT "push_notifications_settings_account_id_fkey"; END IF; END $$;`);
        await queryRunner.query(`DO $$ BEGIN IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'push_notifications_settings_droid_id_fkey') THEN ALTER TABLE "push_notifications_settings" DROP CONSTRAINT "push_notifications_settings_droid_id_fkey"; END IF; END $$;`);
        await queryRunner.query(`DO $$ BEGIN IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'map_settings_droid_id_fkey') THEN ALTER TABLE "map_settings" DROP CONSTRAINT "map_settings_droid_id_fkey"; END IF; END $$;`);

        // 重新添加指定名称的外键
        await queryRunner.query(`ALTER TABLE "devices_settings" ADD CONSTRAINT "devices_settings_account_id_fkey" FOREIGN KEY ("account_id") REFERENCES "accounts"("account_id") ON DELETE CASCADE ON UPDATE NO ACTION`);
        await queryRunner.query(`ALTER TABLE "email_notifications_settings" ADD CONSTRAINT "email_notifications_settings_account_id_fkey" FOREIGN KEY ("account_id") REFERENCES "accounts"("account_id") ON DELETE CASCADE ON UPDATE NO ACTION`);
        await queryRunner.query(`ALTER TABLE "email_notifications_settings" ADD CONSTRAINT "email_notifications_settings_droid_id_fkey" FOREIGN KEY ("droid_id") REFERENCES "droids"("droid_id") ON DELETE CASCADE ON UPDATE NO ACTION`);
        await queryRunner.query(`ALTER TABLE "push_notifications_settings" ADD CONSTRAINT "push_notifications_settings_account_id_fkey" FOREIGN KEY ("account_id") REFERENCES "accounts"("account_id") ON DELETE CASCADE ON UPDATE NO ACTION`);
        await queryRunner.query(`ALTER TABLE "push_notifications_settings" ADD CONSTRAINT "push_notifications_settings_droid_id_fkey" FOREIGN KEY ("droid_id") REFERENCES "droids"("droid_id") ON DELETE CASCADE ON UPDATE NO ACTION`);
        await queryRunner.query(`ALTER TABLE "map_settings" ADD CONSTRAINT "map_settings_droid_id_fkey" FOREIGN KEY ("droid_id") REFERENCES "droids"("droid_id") ON DELETE CASCADE ON UPDATE NO ACTION`);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        // 回滚时删除指定名称的外键
        await queryRunner.query(`ALTER TABLE "map_settings" DROP CONSTRAINT "map_settings_droid_id_fkey"`);
        await queryRunner.query(`ALTER TABLE "push_notifications_settings" DROP CONSTRAINT "push_notifications_settings_droid_id_fkey"`);
        await queryRunner.query(`ALTER TABLE "push_notifications_settings" DROP CONSTRAINT "push_notifications_settings_account_id_fkey"`);
        await queryRunner.query(`ALTER TABLE "email_notifications_settings" DROP CONSTRAINT "email_notifications_settings_droid_id_fkey"`);
        await queryRunner.query(`ALTER TABLE "email_notifications_settings" DROP CONSTRAINT "email_notifications_settings_account_id_fkey"`);
        await queryRunner.query(`ALTER TABLE "devices_settings" DROP CONSTRAINT "devices_settings_account_id_fkey"`);
    }
}

创建新迁移文件的步骤：

1. 生成空迁移：

typeorm migration:create ./src/migrations/FixForeignKeyNames

2. 在新迁移里编写添加指定名称外键的逻辑（如果需要删除旧的自动生成的外键，也要加上判断）：

import {MigrationInterface, QueryRunner} from "typeorm";

export class FixForeignKeyNames1696xxxxxx implements MigrationInterface {
    name = 'FixForeignKeyNames1696xxxxxx'

    public async up(queryRunner: QueryRunner): Promise<void> {
        // 示例：删除旧的外键（如果存在）
        // await queryRunner.query(`DO $$ BEGIN IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'old_email_notif_droid_fk') THEN ALTER TABLE "email_notifications_settings" DROP CONSTRAINT "old_email_notif_droid_fk"; END IF; END $$;`);
        
        // 添加指定名称的外键
        await queryRunner.query(`ALTER TABLE "email_notifications_settings" ADD CONSTRAINT "email_notifications_settings_droid_id_fkey" FOREIGN KEY ("droid_id") REFERENCES "droids"("droid_id") ON DELETE CASCADE ON UPDATE NO ACTION`);
        await queryRunner.query(`ALTER TABLE "push_notifications_settings" ADD CONSTRAINT "push_notifications_settings_account_id_fkey" FOREIGN KEY ("account_id") REFERENCES "accounts"("account_id") ON DELETE CASCADE ON UPDATE NO ACTION`);
        // 其他需要添加的外键...
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        // 回滚时删除外键
        await queryRunner.query(`ALTER TABLE "email_notifications_settings" DROP CONSTRAINT "email_notifications_settings_droid_id_fkey"`);
        await queryRunner.query(`ALTER TABLE "push_notifications_settings" DROP CONSTRAINT "push_notifications_settings_account_id_fkey"`);
        // 其他对应的删除操作...
    }
}

3. 执行迁移并验证 #

执行迁移：

typeorm migration:run

迁移完成后，用PostgreSQL语句验证外键名称是否正确：

SELECT conname AS foreign_key_name
FROM pg_constraint
WHERE conrelid = 'email_notifications_settings'::regclass
AND contype = 'f';

后续预防措施 #

避免直接修改数据库结构，所有表结构变更都通过TypeORM迁移完成：

• 修改实体Schema后，用typeorm migration:generate ./src/migrations/YourMigrationName生成迁移
• 执行typeorm migration:run应用迁移

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