migration:generate continuously updates default values

[iCrawl]

Issue type:

[x?] question [x] bug report [ ] feature request [ ] documentation issue

Database system/driver:

[ ] cordova [ ] mongodb [ ] mssql [ ] mysql / mariadb [ ] oracle [x] postgres [ ] sqlite [ ] sqljs [ ] react-native [ ] expo

TypeORM version:

[x] latest 0.2.8 [ ] @next [ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

Somewhat relevant other issue #2943 (?) Maybe this is just me misunderstanding how postgresql works, but since (seen in the screenshots below) the default values are correctly set. If the default values are really just supposed to be '{}' and ARRAY[] please feel free to close this issue 🙇

Playlists.ts

import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';

@Entity('playlists')
export class Playlist {
	@PrimaryGeneratedColumn()
	id!: number;

	@Column({ type: 'bigint' })
	user!: string;

	@Column({ type: 'bigint' })
	guild!: string;

	@Column({ type: 'text' })
	name!: string;

	@Column({ type: 'text' })
	description!: string | null;

	@Column({ type: 'text', array: true, default: () => "ARRAY[]::text[]" })
	songs!: string[];

	@Column({ default: 0 })
	plays!: number;
}

Settings.js

import { Entity, Column, PrimaryColumn } from 'typeorm';

@Entity('settings')
export class Setting {
	@PrimaryColumn({ type: 'bigint' })
	guild!: string;

	@Column({ type: 'jsonb', default: () => "'{}'::jsonb" })
	settings: any;
}

1541719937415-Init.ts

import {MigrationInterface, QueryRunner} from "typeorm";

export class Init1541719937415 implements MigrationInterface {

    public async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query(`CREATE TABLE "playlists" ("id" SERIAL NOT NULL, "user" bigint NOT NULL, "guild" bigint NOT NULL, "name" text NOT NULL, "description" text NOT NULL, "songs" text array NOT NULL DEFAULT ARRAY[]::text[], "plays" integer NOT NULL DEFAULT 0, CONSTRAINT "PK_a4597f4189a75d20507f3f7ef0d" PRIMARY KEY ("id"))`);
        await queryRunner.query(`CREATE TABLE "settings" ("guild" bigint NOT NULL, "settings" jsonb NOT NULL DEFAULT '{}'::jsonb, CONSTRAINT "PK_5f773ba35a6f8ef6aa49aa8e72b" PRIMARY KEY ("guild"))`);
    }

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

}

Every subsequent migration file:

import {MigrationInterface, QueryRunner} from "typeorm";

export class Test1541720101513 implements MigrationInterface {

    public async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query(`ALTER TABLE "playlists" ALTER COLUMN "songs" SET DEFAULT ARRAY[]::text[]`);
        await queryRunner.query(`ALTER TABLE "settings" ALTER COLUMN "settings" SET DEFAULT '{}'::jsonb`);
    }

    public async down(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query(`ALTER TABLE "settings" ALTER COLUMN "settings" SET DEFAULT '{}'`);
        await queryRunner.query(`ALTER TABLE "playlists" ALTER COLUMN "songs" SET DEFAULT ARRAY[]`);
    }

}

Edit: I actually just tried it out, changing the array to just ARRAY[] will produce: QueryFailedError: cannot determine type of empty array with a hint to: 'Explicitly cast to the desired type, for example ARRAY[]::integer[].'

Changing '{}'::jsonb to '{}' produces the same result as having '{}'::jsonb (so that's good) and it also does not show up in the migration file anymore (like above)

The array however still does since that one cannot be simply ARRAY[] as mentioned above.

Edit2: Went ahead and tried the other notation for postgres to initialize an empty array '{}' (funnily enough) @Column({ type: 'text', array: true, default: '{}' }) Which produces this query in the initial migration: "songs" text array NOT NULL DEFAULT '{}'::text[] (looks correct so far)

But in every subsequent migration it will again try to change it:

import {MigrationInterface, QueryRunner} from "typeorm";

export class Init21541721690470 implements MigrationInterface {

    public async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query(`ALTER TABLE "playlists" ALTER COLUMN "songs" SET DEFAULT '{}'::text[]`);
    }

    public async down(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query(`ALTER TABLE "playlists" ALTER COLUMN "songs" SET DEFAULT '{}'`);
    }

}

Which means I am pretty much out of ideas at this point for defaulting to empty arrays.

[Alain1405]

I have a similar issue where with a migration I've set the default for an integer to 0, yet at each migration the following line is added: await queryRunner.query(ALTER TABLE "photo" ALTER COLUMN "views" SET DEFAULT 0);

Here's the field in db:

EDIT:

I've just noticed that both the up and down migration contain the same query:

import {MigrationInterface, QueryRunner} from "typeorm";

export class GeolocaationSpeed1548947417834 implements MigrationInterface {

    public async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query(`ALTER TABLE "photo" ALTER COLUMN "views" SET DEFAULT 0`);
    }

    public async down(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query(`ALTER TABLE "photo" ALTER COLUMN "views" SET DEFAULT 0`);
    }

}

[kaboing]

👍

[skylight-ity]

Same here

[dbuls]

Same here

[rmainwork]

Is there any update on this?

My migration looks like this:

import { MigrationInterface, QueryRunner } from 'typeorm';

export class ViewTable1562766869548 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query('CREATE TABLE "view" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "createdAt" TIMESTAMP NOT NULL DEFAULT now(), "updatedAt" TIMESTAMP NOT NULL DEFAULT now(), "eppn" character varying NOT NULL, "columns" character varying array NOT NULL DEFAULT \'{}\'::varchar[], CONSTRAINT "PK_86cfb9e426c77d60b900fe2b543" PRIMARY KEY ("id"))');
  }

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

and every subsequent migration contains this:

import { MigrationInterface, QueryRunner } from 'typeorm';

export class Foo1565623082602 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<any> {
    await queryRunner.query('ALTER TABLE "view" ALTER COLUMN "columns" SET DEFAULT \'{}\'::varchar[]');
  }

  public async down(queryRunner: QueryRunner): Promise<any> {
    await queryRunner.query('ALTER TABLE "view" ALTER COLUMN "columns" SET DEFAULT \'{}\'');
  }
}

[LachlanStuart]

I've done some digging. I can't find an elegant solution, so I'm giving up and leaving my findings here.

There are two compounding issues:

1. Postgres rewrites ("cooks") column default expressions and AFAICT doesn't store the uncooked version anywhere. This means that even though you SET DEFAULT '{}', it actually stores something like '{}'::text[], which is what TypeORM sees when it reads back the schema. It's even worse for dates - (now() at time zone 'utc') gets converted to timezone('utc'::text[], now())
2. TypeORM tries to remove the type annotations and sometimes truncates them. Because of the regex ::.*, timezone('utc'::text[], now()) is changed to timezone('utc'. This code was added to fix #1883.

The avenues I see for moving forward, in order from most to least likely to succeed:

1. Stop trying to clean up Postgres's type annotations, and instead require that defaults are written in Postgres' preferred style (i.e. @Column(default: () => "'{}'::text[]") instead of @Column(default: () => "'{}'")), either logging a warning or throwing an error whenever a migration writes a default value and Postgres decides to change it. This would be quite unfriendly for projects that support multiple DBMSs, but IMO it's the cleanest solution because it doesn't require much code and covers 100% of possible default values.
2. Store the user-supplied default values somewhere (e.g. as extra data in the TypeORM migrations table), so that instead of syncing against Postgres's schema, TypeORM can sync the defaults against the saved schema. This is what some other ORMs do for their entire schema, e.g. Entity Framework 6.
3. Continue what was started with the previous fix and apply more hacky regexes or even a parser so that TypeORM can compare user-supplied and postgres-supplied values and see whether they're equivalent. IMO this would just add a big chunk of messy code and would never solve 100% of cases unless Postgres's cookDefault is fully reimplemented in TypeORM.
4. Keep investigating Postgres & consider proposing a change to keep the raw value. I've only poked around INFORMATION_SCHEMA and taken a glance at the source code - it's possible I missed something. It's also possible that I didn't miss anything and Postgres would need to be changed to support this.

[jrmacks]

This has been very problematic for me as well. Any update?

[ghost]

Having the same problem on MySQL; migration:generate is generating pointless alterations. I'm unsure if this is related to the Postgres issue with table information being read incorrectly by TypeORM.

[Ponjimon]

Same issue here using MySQL. No information yet?

[csvwolf]

The same problem, I want to set default value -1 in postgres.

I tried（each example run generate twice）:

  @Column('int', { default: -1 })
  stock!: number

It will generate '-1'::integer in db and -1 in migration file.

So I changed the default schema value like that:

  @Column('int', { default: () => `'-1'::integer` })
  stock!: number

It will still generate repeatedly.

import {MigrationInterface, QueryRunner} from "typeorm";

export class AutoMigration1596601350330 implements MigrationInterface {
    name = 'AutoMigration1596601350330'

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`ALTER TABLE "product" ALTER COLUMN "stock" SET DEFAULT '-1'::integer`);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`ALTER TABLE "product" ALTER COLUMN "stock" SET DEFAULT '-1'`);
    }

}

Obviously, typeorm generator checked and generated wrong file for default value -1.

[datskos]

Having the same issue; TypeORM is regenerating the same migration because, in certain cases, despite determining the correct value when outputting the migration, it uses an invalid value when comparing the Column default value with the in-database value. TypeORM is not being consistent.

Seems like this has gone unaddressed for a while. Any solution besides forking and modifying the default-value comparison code?

[powersjcb]

Are there any versions for which this problem is not occurring?

[yporodko]

I have the same case with Postgres.

Tried to find out why it is happening and found that the problem is in this file: https://github.com/typeorm/typeorm/blob/master/src/driver/postgres/PostgresDriver.ts#L881

|| (!tableColumn.isGenerated && this.lowerDefaultValueIfNecessary(this.normalizeDefault(columnMetadata)) !== tableColumn.default) // we included check for generated here, because generated columns already can have default values

left part of the condition this.lowerDefaultValueIfNecessary(this.normalizeDefault(columnMetadata)) evaluates to 'null' and right part tableColumn.default is undefined

in my case, the column has the following config:

@Column({ default: null, type: 'varchar' })
performedBy: string | null = null;

and tableColumn object has following data:

comment:''
isArray:false
isGenerated:false
isNullable:true
isPrimary:false
isUnique:false
length:''
name:'performedBy'
type:'character varying'
unsigned:false
zerofill:false

[phbdemorais]

When I use a function, for default values, it dosen't update values, after the first migration. I return all values as string, too.

@Column('int', { default: () => '0' }) value!: number;

For Typescript enum types, I don't use function:

@Column('enum', { enum: TypeEnum, default: TypeEnum.Type1 }) value!: TypeEnum;

[mastilver]

A hack is to set the default value as value generated by typeorm

So:

1. add you field

	@Column({ type: 'jsonb', default: () => "'{}'::jsonb" })
	settings: any;

2. Generate a migration
3. Run the migration
4. Generate a second migration (don't run it), in your case it will be:

import {MigrationInterface, QueryRunner} from "typeorm";

export class Init21541721690470 implements MigrationInterface {

    public async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query(`ALTER TABLE "playlists" ALTER COLUMN "settings" SET DEFAULT '{}'::text[]`);
    }

    public async down(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query(`ALTER TABLE "playlists" ALTER COLUMN "settings" SET DEFAULT '{}'`);
    }

}

5. Copy the default generated by typeorm in the down method and replace it in your entiry:

-	@Column({ type: 'jsonb', default: () => "'{}'::jsonb" })
+	@Column({ type: 'jsonb', default: () => "'{}'" })
	settings: any;

6. delete the second migration

[4doge]

@mastilver @csvwolf you can use this dirty hack as a reference until typeorm collaborators decide to do something with this stupid defaults normalization. 🤦🏿‍♂️

  @Column({
    default: () => "'-1'",
  })
  field_name_here: number;

[ranjan-purbey]

@mastilver @csvwolf you can use this dirty hack as a reference until typeorm collaborators decide to do something with this stupid defaults normalization. 🤦🏿‍♂️

  @Column({
    default: () => "'-1'",
  })
  field_name_here: number;

And similarly, for array columns set it to default: () => "'{}'" P.S. you don't need to cast it explicitly to int[] or float[]. And if you do add explicit casting, the same problem will occur

[l3v1k]

Just simple overview.

I think we should consider default which comes from database as RAW and keep it untouched until moment of comparing with entity value. Or do same regex replace on defaultValue from entity, but this seems like not a bug fix but workaround.

I have such case: defaultValue: () => "NOW() + (60 ||' minutes')::interval", which is casted to (now() + ((60 || ' minutes'::text))::interval) in postgres schema. I could just set defaultValue: () => "(now() + ((60 || ' minutes'::text))::interval)" and this could be a fix, but PostgresQueryRunner.loadTables does replace with regexp on retrieved default value there https://github.com/typeorm/typeorm/blob/d27dd2af2ca320e74a17b3ab273cd3bf55d01923/src/driver/postgres/PostgresQueryRunner.ts#L1645-L1646 and it becomes (now() + ((60 || ' minutes'.

So I consider this as a bug, which could be resolved by keeping retrieved default values from DB untouched before matching. Or maybe adding a new flag to meta, like "raw: true", which will prevent from "normalization" in QueryRunner.

Also, regenerated migration contains invalid query inside down function: await queryRunner.query("ALTER TABLE "user_sessions" ALTER COLUMN "expire_at" SET DEFAULT (now() + ((60 || ' minutes'");

[spmsupun]

Having a major issue.

TypeORM team :

[imnotjames]

Having a major issue.

TypeORM team :

This is not a simple issue to correct. There are a 1200 other open issues.

You're welcome to investigate, design a workaround, implement it, handle regressions, and help us with releasing it.

Instead you found that a better use of your time is to choose to insult folks that are giving you their time.

[helmsonsen]

for enum types within a schema the comparison issue still exists with postgres and typeorm @ 0.2.38 could be fixed by extending regex in PostgresQueryRunner https://github.com/typeorm/typeorm/blob/861573377bb33b73232399c21b1b3a5c07b58036/src/driver/postgres/PostgresQueryRunner.ts#L1803 with adding "." like tableColumn.default = dbColumn["column_default"].replace(/::[\w\s\.\[\]\"]+/g, "");

[WinterSolstices]

Experienced a similar issue with

@Entity()
export class UserToken {
  @Column({ default: () => 'uuid_generate_v4()' })
  token: string
}

An alternative syntax exists in this case where the problem is not present:

@Entity()
export class UserToken {
  @Column()
  @Generate('uuid')
  token: string
}

[darioackermann]

Doing a plus one. We want to define a column which by default should just take now() (as a create date or updated at column)

Keeps us giving the following migration after manually changing the first migration:

import { MigrationInterface, QueryRunner } from "typeorm";

export class automatic1666120427520 implements MigrationInterface {
    name = 'automatic1666120427520'

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`ALTER TABLE "notification" ALTER COLUMN "time_sent" SET DEFAULT 'now()'`);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`ALTER TABLE "notification" ALTER COLUMN "time_sent" SET DEFAULT now()`);
    }

}

Could an option to be given that input in the default option is just inserted into the sql statement raw? Willing to help out!

[raimille1]

for enum types within a schema the comparison issue still exists with postgres and typeorm @ 0.2.38 could be fixed by extending regex in PostgresQueryRunner

https://github.com/typeorm/typeorm/blob/861573377bb33b73232399c21b1b3a5c07b58036/src/driver/postgres/PostgresQueryRunner.ts#L1803

with adding "." like tableColumn.default = dbColumn["column_default"].replace(/::[\w\s\.\[\]\"]+/g, "");

@helmsonsen this seems to be the issue with not only enums but also column defaults that have cast calls with :: ... this is an issue for postgres since even if you try to use cast(generate_id() as text), in "information_schema"."columns"."default" postgres stores it as: (generate_id())::text ... A workaround I'm thinking is to wrap the function into one that casts it. I observed I have this issue with my id default columns, trial period end and secret generator default columns:

• (generate_id())::text
• replace(gen_random_uuid()::text || gen_random_uuid()::text, '-','')
• (now() + '15 days')::interval

Here's an example, dropped the default for trial_period_ends_at which was using :: cast to interval, and tried to use the interval function notation. Postgres stores it with a :: cast anyways ... :

So what I ended up doing, is I just wrapped my functions into functions that return exactly what I need:

• generate_id(): now returns text, i was lazy to regenerate it but I guess I'm paying the price now
• generate_secret(): now internally does: replace(gen_random_uuid()::text || gen_random_uuid()::text, '-','')
• now_plus_days(15): now does exactly that

My defaults now look like this, and the problem is gone:

Tried to generate a migration after migrating everything and saw what I needed:

No changes in database schema were found - cannot generate a migration. To create a new empty migration use "typeorm migration:create" command

I hope this helps someone else, since I spent a few hours scratching my head over this. I have not dug into typeorm internals so I'm not sure why that line in the migration generator (tableColumn.default = dbColumn["column_default"].replace(/::[\w\s\.\[\]\"]+/g, "");) is replacing double :: for an empty string, but clearly for this specific case for postgres it does not work, I'm sure there's a good reason though.

[KharchenkoDmitriy]

I've done some digging. I can't find an elegant solution, so I'm giving up and leaving my findings here.

There are two compounding issues:

1. Postgres rewrites ("cooks") column default expressions and AFAICT doesn't store the uncooked version anywhere. This means that even though you SET DEFAULT '{}', it actually stores something like '{}'::text[], which is what TypeORM sees when it reads back the schema. It's even worse for dates - (now() at time zone 'utc') gets converted to timezone('utc'::text[], now())
2. TypeORM tries to remove the type annotations and sometimes truncates them. Because of the regex ::.*, timezone('utc'::text[], now()) is changed to timezone('utc'. This code was added to fix Synchronization error with default value #1883.

The avenues I see for moving forward, in order from most to least likely to succeed:

1. Stop trying to clean up Postgres's type annotations, and instead require that defaults are written in Postgres' preferred style (i.e. @Column(default: () => "'{}'::text[]") instead of @Column(default: () => "'{}'")), either logging a warning or throwing an error whenever a migration writes a default value and Postgres decides to change it. This would be quite unfriendly for projects that support multiple DBMSs, but IMO it's the cleanest solution because it doesn't require much code and covers 100% of possible default values.
2. Store the user-supplied default values somewhere (e.g. as extra data in the TypeORM migrations table), so that instead of syncing against Postgres's schema, TypeORM can sync the defaults against the saved schema. This is what some other ORMs do for their entire schema, e.g. Entity Framework 6.
3. Continue what was started with the previous fix and apply more hacky regexes or even a parser so that TypeORM can compare user-supplied and postgres-supplied values and see whether they're equivalent. IMO this would just add a big chunk of messy code and would never solve 100% of cases unless Postgres's cookDefault is fully reimplemented in TypeORM.
4. Keep investigating Postgres & consider proposing a change to keep the raw value. I've only poked around INFORMATION_SCHEMA and taken a glance at the source code - it's possible I missed something. It's also possible that I didn't miss anything and Postgres would need to be changed to support this.

I believe solution №1 is the most suitable. It allows users to deal with complex defaults while keep the interface as is. And the adjustment I could see is simple, as @kauz has proposed here - just save a raw default from db (here) and that modify checks here and here

[Woodz]

Encountered this bug with

@Entity()
export class Foo {
  @Column('uuid')
  @Generated('uuid')
  bar: string
}

which I narrowed down to an issue in https://github.com/typeorm/typeorm/blob/83567f533482d0170c35e2f99e627962b8f99a08/src/driver/postgres/PostgresDriver.ts#L1055 - specifically default values and generated values are different concepts (ColumnMetadata has separate fields for generationStrategy/isGenerated and default). So this column has generationStrategy/isGenerated set but default is undefined.

This does not match the implementation of TableColumn (the metadata read from the existing DB) which reads this generated default as default, not generated (https://github.com/typeorm/typeorm/blob/83567f533482d0170c35e2f99e627962b8f99a08/src/schema-builder/table/TableColumn.ts#L26)

So we end up comparing:

ColumnMetadata({ // Metadata extracted from Entity
  isGenerated: true,
  generationStrategy: 'uuid'
});

with

TableColumn({ // Metadata extracted from existing DB table
  default: `<schema>.uuid_generate_v4()`,
  isGenerated: false,
});

so obviously the migration generator thinks that the column has changed.

We should either merge default with generated into a single concept, or correct the logic when reading from PSQL (and maybe other DB types) so that a default value of a function should be read as generated value, not a default

[Woodz]

The workaround that I found worked was:

  @PrimaryGeneratedColumn('uuid', { name: '_id' })

to

  @PrimaryColumn('uuid', {
    name: '_id',
    default: () => '<schema>.uuid_generate_v4()',
  })

[ludgerey]

Same here.

  @Column({ type: "text", default: () => "0" })
  status: TemplateDraftStatus;

typeorm migration:generatewill always create this, even after migration has been executed:

 public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `ALTER TABLE "template-drafts" ALTER COLUMN "status" SET DEFAULT 0`,
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `ALTER TABLE "template-drafts" ALTER COLUMN "status" SET DEFAULT '0'`,
    );
  }

[darioackermann]

Same here.

  @Column({ type: "text", default: () => "0" })
  status: TemplateDraftStatus;

typeorm migration:generatewill always create this, even after migration has been executed:

 public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `ALTER TABLE "template-drafts" ALTER COLUMN "status" SET DEFAULT 0`,
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `ALTER TABLE "template-drafts" ALTER COLUMN "status" SET DEFAULT '0'`,
    );
  }

What happens if you try setting default to "'0'"? Might work if you're lucky :)

[ludgerey]

Same here.

  @Column({ type: "text", default: () => "0" })
  status: TemplateDraftStatus;

typeorm migration:generatewill always create this, even after migration has been executed:

 public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `ALTER TABLE "template-drafts" ALTER COLUMN "status" SET DEFAULT 0`,
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `ALTER TABLE "template-drafts" ALTER COLUMN "status" SET DEFAULT '0'`,
    );
  }

What happens if you try setting default to "'0'"? Might work if you're lucky :)

Nice! Thanks! I didn't consider that. It works.