SQLite returns timestamp for date fields defined by t.datetime in a populated entity

[piotr-cz]

Describe the bug

This occurs only with @mikro-orm/sqlite driver, @mikro-orm/postgresql works as expected.

Tested on v6.2.5, v6.2.0, v6.1.0, v6.0.0
Might be related to #4362

Reproduction

• use reflect-metadata provider
• define property with @Property({ type: t.datetime }) or @Property({ type: DateTimeType }) (recommended in docs)
• load entity via relationship

Full reproduction case available here: https://stackblitz.com/edit/mikro-orm-getting-started-guide-cp-1-rcgxpm (test with npm start)

Here is short summary:

// ./models/user/user.entity.ts
import { Entity, Collection, OneToMany, PrimaryKey, Property, t } from '@mikro-orm/core';
import { Article } from '../article/article.entity.js';

@Entity()
export class User {
  @PrimaryKey({ type: t.integer })
  id!: number;

  @Property({ type: t.string })
  fullName!: string;

  @Property({ type: t.datetime })
  tdatetime: Date;

  @Property({ type: 'datetime' })
  qdatetime: Date;

  @Property({ type: 'Date' })
  qDate: Date;

  @Property({ type: Date })
  Date: Date;

  @OneToMany(() => Article, (article: Article) => article.author)
  articles = new Collection<Article>(this);

  constructor(fullName: string, date: Date) {
    this.fullName = fullName;

    this.tdatetime = date;

    this.qdatetime = date;
    this.qDate = date;

    this.Date = date;
  }
}

// ./models/article/article.entity.ts
import { Entity, PrimaryKey, Property, Ref, ManyToOne, t } from '@mikro-orm/core';
import { User } from '../user/user.entity.js';

@Entity()
export class Article {
  @PrimaryKey({ type: t.integer })
  id!: number;

  @Property({ type: t.string })
  title: string;

  @Property({ type: t.datetime })
  tdatetime: Date;

  @Property({ type: 'datetime' })
  qdatetime: Date;

  @Property({ type: 'Date' })
  qDate: Date;

  @Property({ type: Date })
  Date: Date;

  @ManyToOne(() => User)
  author: Ref<User>;

  constructor(author: Ref<User>, title: string, date: Date) {
    this.author = author;
    this.title = title;

    this.tdatetime = date;

    this.qdatetime = date;
    this.qDate = date;

    this.Date = date;
  }
}

const user = new User('Foo Bar', new Date());

user.articles.add(new Article(user, 'hello world', new Date()));

const em = orm.em.fork();

await em.persist(user).flush();

const em2 = orm.em.fork();

const dbUser = await em2.findOne(User, { id: 1 }, { populate: ['articles'] });

console.log(dbUser)

In above example:

• all related User object property values are instances of Date
• all related Article object property values are instances of Date, except tdatetime which is number:

User {
  fullName: 'Foo Bar',
  tdatetime: 2024-05-08T19:43:30.571Z,
  qdatetime: 2024-05-08T19:43:30.571Z,
  qDate: 2024-05-08T19:43:30.571Z,
  Date: 2024-05-08T19:43:30.571Z,
  id: 1,
  articles: Collection<Article> {
    '0': Article {
      title: 'hello world',
      tdatetime: 1715197410571,
      qdatetime: 2024-05-08T19:43:30.571Z,
      qDate: 2024-05-08T19:43:30.571Z,
      Date: 2024-05-08T19:43:30.571Z,
      author: [Ref<User<1>>],
      id: 1
    },
    initialized: true,
    dirty: false
  }
}

What driver are you using?

@mikro-orm/sqlite

MikroORM version

6.2.5

Node.js version

Node 18.18.2, Typescript 5.2.2

Operating system

Windows/ Stackblitz

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.

[B4nan]

define property with @Property({ type: t.datetime }) or @Property({ type: DateTimeType }) (recommended in docs)

Where do you read it's "recommended"? I would actually discourage it, since it means the hydration will use the custom type convertor methods, resulting in a (small) perf hit. I would love to find a way around this, but some types need the convertors (and users can extend the type, in which case we also need to respect it).

edit: just to be clear, I would love to find a way around the perf issue, what you are reporting here is something we will surely find a fix for regardless of that

[piotr-cz]

By "recommended" I mean that I understood that types may be defined by t. constants:

There is also a types map (exported also as just t for brevity):

import { t } from '@mikro-orm/core'; // `t` or `types`

@Property({ type: t.text })
bio = '';

So that's why I'm using t.datetime for date and time type.

If you discourage it, what is the proper way of defining it?

Other ways ('datetime'|'Date'|Date) work fine, but I haven't seen these in docs, except Upgrading from v5 to v6 > Changes in Date property mapping

[B4nan]

Other ways ('datetime'|'Date'|Date) work fine

Any of those. When you use the types map, you use the custom type, which in fact does nothing (but the hydration functions still need to call its convertor methods, as it does not know they are in fact just identity functions).

If you are not using a union type (as in Date | null), you don't need anything, as the type will be inferred via reflect metadata. Equivalent to that is adding type: 'Date', but all the other variants should converge to the same - the problematic part is only when you use the type implementation.

---

Again, this is rather something to improve than to document, I don't want people to think about this.

[piotr-cz]

Thank you!