Can't set a json default value on MySQL

[hcoura]

Describe the bug
After mysql 8.0.13 we can set DEFAULT values for JSON columns like

DEFAULT ('{a: 1}')

We can't do this via the default option from the @Property but we can via defaultRaw something like:

  @Property({
    type: 'json',
    defaultRaw: `('${JSON.stringify({
      orderTextUpdates: false,
      driver: NotificationDriverSettingEnum.TextAndEmail,
    })}')`,
  })
  notificationSettings: NotificationSettingsType;

will generate this schema change

import { Migration } from '@mikro-orm/migrations';

export class Migration20231114174123_AddsPatientNotificationSettings extends Migration {
  async up(): Promise<void> {
    this.addSql(
      'alter table `patients` add `notification_settings` json not null default  (\'{"orderTextUpdates":false,"driver":"text_and_email"}\');',
    );
  }

  async down(): Promise<void> {
    this.addSql('alter table `patients` drop `notification_settings`;');
  }
}

All good with mysql and works as expected, but if we generate a schema diff again we will get:

import { Migration } from '@mikro-orm/migrations';

export class Migration20231114174809_AddsPatientNotificationSettings extends Migration {
  async up(): Promise<void> {
    this.addSql(
      'alter table `patients` modify `notification_settings` json not null default (\'{"orderTextUpdates":false,"driver":"text_and_email"}\');',
    );
  }

  async down(): Promise<void> {
    this.addSql(
      'alter table `patients` modify `notification_settings` json not null default _utf8mb4\'\'{"orderTextUpdates":false,"driver":"text_and_email"}\'\';',
    );
  }
}

The changes from #3503 won't help because this is on the default/defaultRaw fields.

Versions

Dependency	Version
node	20.5.0
typescript	5.1.3
mikro-orm	5.9.3
your-driver	mysql

[B4nan]

Why do you have the parens around the JSON, is that required for the query to work? My guess is that's the problem. You need to provide the exact same value as what gets inferred from the database with defaultRaw.

Try using MIKRO_ORM_VERBOSE env var when running the CLI, it will print a lot of debug info, including why the values are considered different.

# you can use schema:update for testing this, its what the migrator uses under the hood
MIKRO_ORM_VERBOSE=1 npx mikro-orm schema:update -d

[B4nan]

What if you do this?

  @Property({
    type: 'json',
    defaultRaw: `_utf8mb4'{"orderTextUpdates":false,"driver":"text_and_email"}'`,
  })
  notificationSettings: NotificationSettingsType;

(you might have to adjust the escaping, this is just a copy paste, tried to clean it up a bit but idk)

[hcoura]

If I do

defaultRaw: `_utf8mb4\\\'{"orderTextUpdates":false,"driver":"text_and_email"}\\\'`,

Mikro won't see the diff anymore, but this would only "work" if I set the migration manually to something like

this.addSql(
      'alter table `patients` add `notification_settings` json null default (\'{"orderTextUpdates":false,"driver":"text_and_email"}\');',
    );

That is, if I generate the migration with that defaultRaw value mysql will throw a syntax error

[B4nan]

Got it, will try to look into this later today and hopefully make it work with what you tried initially, it should be possible to strip both the _utf8mb4 thing as well as the parens from what is inferred from the schema.

[B4nan]

this will help 53c3911?w=1

[hcoura]

Awesome! Thanks a lot!

[hcoura]

@B4nan Your fix/release does work in the sense that it doesn't detect the change anymore.

That said, the diff is still there somewhere haha, today I made some unrelated change to the schema and when I generate a new schema I get this on the migration

  async down(): Promise<void> {
    // ...
    this.addSql('alter table `patients` modify `notification_settings` json not null default _utf8mb4\'\'{"orderTextUpdates":false,"driver":"text_and_email"}\'\';');
  }

Interestingly it's only on down and if I remove it and run the migration and try to generate again no differences will be detected.

[B4nan]

Your fix/release does work in the sense that it doesn't detect the change anymore.

But what change? It won't detect changes in whitespaces, I'd say that's a good enough solution.

edit: maybe I misunderstood what you are trying to say. Anyway, repro welcome, I can't fix things based on a generated migration.

[hcoura]

I will try to build a simple repro later today or tomorrow, but what I am trying to say is that for some unrelated change, the schema generator still thinks something should happen on down.

[B4nan]

Oh right, got that reproduced in the test I made already.

[B4nan]

cccc756 should help