Populate invalid cast date

[karol71927]

Describe the bug

Hi, I found a Bug with populate. When I used find entity with populate records, records has date for example 2024-05-15T17:51:35.000Z but when I find record without populate same record has date like this 2024-05-15T15:51:35.000Z.
I am using timezone +02:00

Reproduction

https://github.com/karol71927/mikro-orm-populate-issue

In example I am finding recipe with ingredients and then I am geting Flour date is createdAt: 2024-05-15T17:51:35.000Z. Then i wanto to get same ingredient and date is createdAt: 2024-05-15T15:51:35.000Z,
In the first case I am using populate on recipe. In second case I am fetching ingredients with find

    const em = this.orm.em.fork();

    const recipe = await em.findOne(
      Recipe,
      { title: 'My first recipe' },
      { populate: ['ingredients'] },
    );

    const withPopulate = recipe.ingredients
      .getItems()
      .find((x) => x.name === 'Flour');

    console.log(withPopulate);

    const ingredients = await em.find(Ingredient, {
      recipe: { id: recipe.id },
    });

    const withoutPopulate = ingredients.find((x) => x.name === 'Flour');
    console.log(withoutPopulate);

What driver are you using?

@mikro-orm/mysql

MikroORM version

6.2.6

Node.js version

20

Operating system

mac os

Validations

• Read the Contributing Guidelines.
• Read the docs.
• Check that there isn't already an issue that reports the same bug to avoid creating a duplicate.
• Check that this is a concrete bug. For Q&A open a GitHub Discussion or join our Slack.
• The provided reproduction is a minimal reproducible example of the bug.

[karol71927]

Hi,

To clarify the unexpected behaviour more

In MikroORM 5.x, with a configuration of TIMEZONE + 2 (although it can be any +/- hour), when I saved an entity with a date (stored as a datetime field in MySQL), for example, 2024-05-16T08:05:21.743Z, it was correctly saved in the database as 2024-05-16 10:05:21, which is expected.

Later, when I retrieved this entity using orm.em.findOne or a dedicated repository, the date on this entity remained as 2024-05-16T08:05:21.743Z, as expected.

This behavior persists in MikroORM 6.x.

However, the unexpected behavior occurs when retrieving an entity with its children. In 5.x, if the children have dates associated with them, the behavior mirrors that of the parent entity. That is, when saving the main entity with children and associated dates, such as 2024-05-16T08:05:21.743Z, the dates on the children were correctly saved in the database as 2024-05-16 10:05:21. Upon retrieval via populate, the dates on the children matched the timezone-adjusted time of the main entity, appearing as 2024-05-16T08:05:21.743Z.

However, in MikroORM 6.x, performing the same actions results in incorrectly calculated or casted dates on the children. Even though the main entity's date is properly saved in the database as 2024-05-16 10:05:21, when using findOne on the main entity and populating its children, the dates on the children are displayed as 2024-05-16T10:05:21.743Z. This discrepancy means that the child entity's time does not match the timezone-adjusted time of the main entity, resulting in a difference of additional two hours (2024-05-16 12:05:21 instead of 2024-05-16 10:05:21).

[B4nan]

Can you simplify the repro? It should be a simple script, one file, no webservers needed. I want to just run it and see what you see.

https://github.com/mikro-orm/reproduction

[B4nan]

Tried your repro again and it seems to be working fine on my end, all the dates are same.

Here is a simplified version, which also works fine:

import { Collection, Entity, ManyToOne, MikroORM, OneToMany, PrimaryKey, Property } from '@mikro-orm/mysql';

@Entity()
class Recipe {

  @PrimaryKey()
  id!: number;

  @Property()
  title: string;

  @Property({ onCreate: () => new Date() })
  createdAt!: Date;

  @OneToMany(() => Ingredient, ingredient => ingredient.recipe)
  ingredients = new Collection<Ingredient>(this);

  constructor(title: string) {
    this.title = title;
  }

}

@Entity()
class Ingredient {

  @PrimaryKey()
  id!: number;

  @Property()
  name: string;

  @Property()
  quantity: number;

  @Property({
    onCreate: () => new Date(),
  })
  createdAt!: Date;

  @ManyToOne(() => Recipe)
  recipe!: Recipe;

  constructor(name: string, quantity: number) {
    this.name = name;
    this.quantity = quantity;
  }

}

let orm: MikroORM;

beforeAll(async () => {
  orm = await MikroORM.init({
    entities: [Recipe],
    dbName: '5577',
    port: 3308,
    timezone: '+02:00',
    debug: true,
  });
  await orm.schema.refreshDatabase();
});

afterAll(() => orm.close(true));

test('5577', async () => {
  const r = new Recipe('My first recipe');
  r.ingredients.add(new Ingredient('Flour', 500));
  r.ingredients.add(new Ingredient('Sugar', 200));
  r.ingredients.add(new Ingredient('Milk', 200));
  orm.em.persist(r);
  await orm.em.flush();
  orm.em.clear();

  const recipe = await orm.em.findOneOrFail(
    Recipe,
    { title: 'My first recipe' },
    { populate: ['ingredients'] },
  );

  const withPopulate = recipe.ingredients
    .getItems()
    .find(x => x.name === 'Flour');

  console.log(withPopulate);

  const ingredients = await orm.em.find(Ingredient, {
    recipe: { id: recipe.id },
  });

  const withoutPopulate = ingredients.find(x => x.name === 'Flour');
  console.log(withoutPopulate);
});

[B4nan]

Yeah, the root entities are mapped this way which respects the timezone option:

https://github.com/mikro-orm/mikro-orm/blob/master/packages/core/src/utils/EntityComparator.ts#L288

But the joined relations are handled dynamically elsewhere:

https://github.com/mikro-orm/mikro-orm/blob/master/packages/knex/src/AbstractSqlDriver.ts#L372

And there we ignore the explicit timezone.

[B4nan]

And now I understand why the repro is passing, I am also in UTC+2. Setting the timezone option to another value makes it fail.

[B4nan]

How do you plan to deal with DST? If the absolute timezone shift changes you would need to change the ORM config option and reload the app for it to have an effect, that doesn't sound very maintainable. I guess that's why most people just enforce UTC on the server and deal with timezones when presenting the data instead.

[B4nan]

This should do it: 7f9bb0b

[karol71927]

For me using timezone +2 is fine. I'm not changing it. It's only for backend but frontend never shows dates. Enforcing UTC seems to be a good idea. Thank you for fix 😄