如何替换uuid_generate_v4()？解决NestJS迁移跨PG/SQLite兼容问题

问题场景 #

在NestJS项目中使用typeorm:generate-migration生成迁移脚本时，脚本依赖PostgreSQL的uuid_generate_v4()函数，导致迁移在SQLite测试环境执行时报错，无法实现跨数据库兼容。

用户实体代码 #

@Entity()
export class User {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column()
  name: string;
}

生成的迁移脚本 #

export class initUser1667668869578 implements MigrationInterface {
    name = 'initUser1667668869578'

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`CREATE TABLE "user" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "name" character varying NOT NULL, CONSTRAINT "PK_cace4a159ff9f2512dd42373760" PRIMARY KEY ("id"))`);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`DROP TABLE "user"`);
    }
}

报错信息 #

Migration "initUser1667668869578" failed, error: SQLITE_ERROR: near "(": syntax error

解决方案 #

方案1：动态适配数据库的迁移脚本 #

在迁移脚本中通过queryRunner判断当前数据库类型，生成对应适配的SQL语句：

export class initUser1667668869578 implements MigrationInterface {
    name = 'initUser1667668869578'

    public async up(queryRunner: QueryRunner): Promise<void> {
        let idDefaultClause = 'DEFAULT uuid_generate_v4()';
        // 判断是否为SQLite数据库
        if (queryRunner.connection.options.type === 'sqlite') {
            // SQLite模拟UUIDv4生成逻辑
            idDefaultClause = `DEFAULT (lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-' || '4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1,1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6))))`;
        }

        await queryRunner.query(`CREATE TABLE "user" ("id" uuid NOT NULL ${idDefaultClause}, "name" character varying NOT NULL, CONSTRAINT "PK_cace4a159ff9f2512dd42373760" PRIMARY KEY ("id"))`);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`DROP TABLE "user"`);
    }
}

说明：SQLite无内置UUID生成函数，上述代码用SQLite原生函数拼接出符合UUIDv4规范的字符串。

方案2：为不同数据库单独生成迁移脚本 #

使用TypeORM的--database参数分别为PostgreSQL和SQLite生成适配的迁移脚本：

# 生成PostgreSQL专属迁移
npx typeorm-ts-node-commonjs migration:generate ./src/migrations/initUserPostgres -d ./src/data-source.ts --database postgres
# 生成SQLite专属迁移
npx typeorm-ts-node-commonjs migration:generate ./src/migrations/initUserSqlite -d ./src/data-source.ts --database sqlite

运行迁移时，根据当前环境变量选择对应的迁移脚本执行，比如在package.json中配置脚本：

{
  "scripts": {
    "migrate:dev": "npx typeorm-ts-node-commonjs migration:run -d ./src/data-source.ts -t postgres",
    "migrate:test": "npx typeorm-ts-node-commonjs migration:run -d ./src/data-source.ts -t sqlite"
  }
}

方案3：应用层生成UUID，脱离数据库依赖 #

修改实体定义，使用uuid库在应用层生成UUID，避免依赖数据库函数：

import { v4 as uuidv4 } from 'uuid';
import { Entity, PrimaryColumn, Column } from 'typeorm';

@Entity()
export class User {
  @PrimaryColumn()
  id: string = uuidv4();

  @Column()
  name: string;
}

对应的迁移脚本会生成普通字符串类型的ID字段，无需数据库默认值：

export class initUser1667668869578 implements MigrationInterface {
    name = 'initUser1667668869578'

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`CREATE TABLE "user" ("id" character varying NOT NULL, "name" character varying NOT NULL, CONSTRAINT "PK_cace4a159ff9f2512dd42373760" PRIMARY KEY ("id"))`);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`DROP TABLE "user"`);
    }
}

优点：完全兼容所有数据库；注意：需先安装uuid库（npm install uuid），并在TypeScript配置中开启esModuleInterop以支持导入。

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