Using Knex directly and expected date type behaviour

[petergledhill]

When we use the instance of Knex which comes with MikroOrm (6.2.5), dates are no longer converted to javascript Dates but are returned as strings.

const knex = orm.em.getKnex();
const users = await knex("user").select("*");
users[0].birthday
// assuming birthday is a postgres 'timestamptz' column this used to return a javascript date, but is now a string

This used to work in 5.x and was handy for our use case. We use MikroOrm for mutating state and the query builder for some simple queries but we have many views and more complex queries against those are just written in sql and executed with knex.raw.

I was going to raise this as a bug but perhaps it's not something MikroOrm supports any longer as I can see that this change has been made quite deliberately.

If so I could manage my own instance of Knex but then I think i'd lose connection pooling by having 2 different instances.

Is this use case something MikroOrm supports? Any suggestions on how best to execute raw SQL and get the results I expect?

[B4nan]

Yes, this is done on purpose, dates are mapped in the ORM during result mapping (since the way the drivers do it is very inconsistent, e.g. it does not work with dates inside JSON properties, and it differs greatly among the SQL drivers). You can create your own knex instance that will map things the default way if you need it, the one the ORM creates has the date processing disabled completely. Not sure if you can reuse the same connection pool in this case, that's more of a question for knex maintainers. You could also maybe override the types mapping on a knex QB level.

I would personally just map the results via em.map, or if you want to skip hydration to actual entity instances, use em.getDriver().mapResult() which is exactly the place that handles date conversion (and you end up with POJOs instead of entities). Or create your own helper that will just check if the value looks like an ISO date string and do the mapping yourself.

Note that you shouldn't await knex.raw, you should use em.execute instead, that way you get logging of those queries. But that itself is not gonna help, it just executes the query without any mapping.

[petergledhillinclusive]

Thanks for the quick reply. So I can either:

1. use 'map' and use MikroOrm Entities
2. use 'mapResult' with POJO input and output
3. do the transformation myself

Regarding 1:
Most of this raw queries are being used for CSV generation where we convert the knex result to a stream and pipe it into a csv stringifier. I don't think using Mikro Entities is going to play nicely with this process so I think that rules that one out.

Regarding 2:
I can't figure out how mapResult would help us take the POJOs returned from a knex as it requires
meta: EntityMetadata<T> which I won't have. So I must have misunderstood the process you're describing here.

For now I have gone with option 3, doing the transformation myself using the 'postgres-date' package.

P.S. good to know about awaiting execute rather than awaiting knex.raw

[B4nan]

You can get the metadata for any entity via em.getMetadata(Type).

[B4nan]

Btw since you are serializing to CSV, isnt the requirement to get Date objects artificial in the first place? You will end up with the ISO string there anyway, or?

[petergledhillinclusive]

Yes I believe postgres returns a valid ISO date string but it's different format to the one returned by calling 'toISOString' on date.

"2022-01-01 00:00:00+00" // postgres
"2022-01-01T00:00:00.000Z" //javascript Date.toISOString():

Regardless of weather the result is serialised to JSON or CSV I would like to have a consistent format.

I'll have a look into 'getMetadata' function thanks