1

I have a NestJS project where I generate the migration with typeorm:generate-migration. Because of uuid_generate_v4() the script is not cross compatible between PostgreSQL (dev/prod) and SQLite (tests). How to make it work?

User Entity :

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

  @Column()
  name: string;
}

Migration :

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"`);
    }

}

The error (caused because SQLite doesn't have uuid_generate_v4()) :

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

user4157124
  • 2,809
  • 13
  • 27
  • 42
Antoine Grenard
  • 1,712
  • 3
  • 21
  • 41

1 Answers1

0

Natively there's nothing you can replace that function call with. Alternatives:

  1. Drop NOT NULL DEFAULT uuid_generate_v4() part and use a trigger after insert instead. Example.
  2. Define your own uuid_generate_v4() - you will need an extension for that.
  3. If dev and prod are on PostgreSQL, it would make sense to run your tests on PostgreSQL as well, unless you're just testing whether it'd be possible to migrate.
  4. Drop the db-side default value part entirely and generate it in the app.
no_frameworks
  • 36
  • 4