Mapping parsed/transformed database results into Entities

[darkbasic]

I was looking at the EntityManager map() implementation and I started having headaches at getResultMapper and the Function() Constructor :)
I want to map a PostgreSQL database result (not from QueryBuilder and not from knex, it's from Slonik) to an Entity and I've always used em.map(Entity, row). I've recently started to use Slonik Interceptors and in particular one which automatically transforms field names to camel case (user_id -> userId).
If I understood the source code correctly mapResult() converts the field names into property names while merge() makes it managed. So I guess I can simply skip mapResult? Something like em.merge(Entity, row) would do the trick? What's the refresh prop for?
I've also added type parsers in Slonik (for example timestamptz -> Date), would that be a problem for merge? I've seen a convertCustomTypes prop, so I guess that should be false and MAYBE for the built-in types it is smart enough to figure out if they are already parsed or not?

[B4nan]

Yes, em.merge should work. Refreshing is for when the entity is already in context - merge can update the entity state of such too, like if you would flush, but you need refresh: true for that, otherwise it will just give you back the managed entity instance (it won't update it anyhow). Dates are expected to be already converted from the driver, custom types are not used for that, I'd rather not disable that flag unless you see an actual reason.

[darkbasic]

Refreshing is for when the entity is already in context - merge can update the entity state of such too, like if you would flush, but you need refresh: true for that, otherwise it will just give you back the managed entity instance (it won't update it anyhow)

So with refresh: true If I give it a different entity than the one in context it will update the managed entity but it won't persist it, so when I run em.flush() it won't fire an update query, right?

custom types are not used for that, I'd rather not disable that flag unless you see an actual reason.

I want to disable it because I parse custom types in Slonik as well: for example Postgis Point.

Also what happens to joined props (references)? I have two different cases to cover: in the first one I have just the id of the referenced entity and in the second one the whole entity has been prefixed in the query result.

Book:

id: number,
name: string,
author?: IdentifiedReference<User>;
publisher: IdentifiedReference<Publisher>;

Query result (after Slonik parsing):

row = {
  id: 7,
  name: 'abcd',
  authorId: 3,
  publisherId: 2,
  publisherName: 'qwerty',
  publisherField: 'something',
  [...]
}

const myBook = em.merge<Book>('book', row, { convertCustomTypes: false, refresh: true });

Does it create a reference for the author? Or should I create it with myBook.author = em.getReference(User, row.authorId)) ?

If it creates a reference for the publisher I'm pretty sure it won't be loaded, so here is what I do:

// mergeFromPrefix takes all the fields starting with the prefix, strips it out and makes the first
// character lowercase in order to obtain a query result matching the Publisher shape
// which then gets fed into merge()

const bookPublisher = mergeFromPrefix<Publisher>('publisher', row, { convertCustomTypes: false, refresh: true, prefix: 'publisher' });

myBook.publisher = em.getReference(Publisher, row.publisherId)); // Not sure if it automatically creates refs

At this point I should be able to cast myBook to Loaded<Book, "publisher"> since merging bookPublisher ensures me to have it in the context, rigtht?

Is there any sugar syntax to create the reference directly form the entity I want to reference? Like myBook.publisher = em.toRef(bookPublisher)?

[B4nan]

So with refresh: true If I give it a different entity than the one in context it will update the managed entity but it won't persist it, so when I run em.flush() it won't fire an update query, right?

persist is only about marking new entities, those that should produce insert queries. you won't get any query after you call em.merge as that always saves the entity snapshot. if the entity is already loaded in the context, you need to use refresh: true for that, otherwise it will just give you back the entity instance found in the map.

I want to disable it because I parse custom types in Slonik as well: for example Postgis Point.

Can't say I like this, you will face edge cases, as you want to do something there and something there. Mapped types are not just for internals like date or point, they can be used explicitly and you will disable that too. I don't think you should.

Does it create a reference for the author? Or should I create it with myBook.author = em.getReference(User, row.authorId)) ?

Nope, there is no such property in your entity definition as authorId so that field will be ignored completely.

What's the point of all this?

[darkbasic]

Can't say I like this, you will face edge cases, as you want to do something there and something there. Mapped types are not just for internals like date or point, they can be used explicitly and you will disable that too. I don't think you should.

Another option would be to leave convertCustomTypes to true and add the logic directly into the Mikro-ORM custom type:

type JSType = Point | undefined;
type DBType = string | undefined | Point; // It's Point if it has already been parsed by slonik

export class PointType extends Type<JSType, DBType> {
  convertToJSValue(value: DBType): JSType {
    if (value == null) {
      return value;
    }

    // Already parsed by slonik
    if (value instanceof Point) {
      return value;
    }

    const parsed = JSON.parse(value);

    [...]
  }

  [...]
}

What's the point of all this?

Taking advantage of PostgreSQL specific features not supported by either knew or Query Builder: my only option is to use plain SQL.
Also since these complex queries are dynamically constructed depending on the GraphQL query parameters I use Slonik to do so.
I have just a few of these very complex queries which require Slonik, but I want to use Mikro-ORM everywhere else so I turn the result back into entities.
I also don't want to waste any precious data I've already got for free from mandatory joins, because it could be requested down the graph and I want to spare the additional query, so I turn those into entities as well and I create the reference.

From the previous book example let's say the frontend queried the following:

{
  books(authorName: 'Mark') {
    name
    author: {
      id
      name
    }
    publisher: {
      id
      vatId
    }
  }
}

Let's say this was a very complex query and I had to use Slonik because of otherwise unsupported Postgres specific features.
Because of the authorName parameter I have to JOIN Book with Author when querying for books and I get the author for free.
I turn each book into entities and I return them. Before returning them I also turn the author into an entity and I assign a reference to it into each book. We do the same for the publisher, but we simply assign a reference because we have no results to turn into entities.
Down the graph once the first resolver returns the books another resolver will get a book as input and will be in charge to resolve its author. We've got a reference to it from the previous resolver, so we have to await book.author.load() which will return immediately without firing a query because there is already an entity in the cache for it.
When it gets to the publisher resolver await book.publisher.load() will indeed fire a query before there is nothing in the cache.
If we didn't provide the authorName parameter both author and publisher would fire a query.

Let's have a look at a slightly more complex example:

GraphQL query 1

{
  chats(oderBy: 'lastMessage') {
    id
    propOne
    propTwo
  }
}

For the first query we are forced to join with messages, order them and retrieve the last message. I could theoretically throw away the last message, but have a look at query 2:

GraphQL query 2

{
  chats(oderBy: 'lastMessage') {
    [...]
    lastMessage: {
      [...]
    }
  }
}

We still have to retrieve the last message in the chats resolver because of the ordering, but the next resolver will have to fetch the lastMessage so we shouldn't throw it away. We can convert it to an entity but we can't store it into book.lastMessage because that's not a valid database field (it's only a valid GraphQL field with no direct match to database columns). So I additionally return an helper object with the lastMessage entity that later resolvers can use, if available.

GraphQL Query 3

{
  chats() {
    [...]
    lastMessage: {
      [...]
    }
  }
}

In this case the chats resolvers won't have to fetch the messages and so the lastMessage resolver will have to actually fire a query to return it.

Slonik queries are typed (it uses zod) and (while I still don't do so) one of the reasons why I'd want to use Slonik field transformers is to more easily generate the zod types (which are also used to validate the query results) from the Mikro-ORM schema so I have a single source of truth.
The ZodChat type would be autogenerated from the Chat schema and for the previous example I will have to simply do ZodChat.extend({lastMessage: ZodMessage}) to get the proper typings for the Slonik query, which will also validate the result (will throw if lastMessage is missing or if a field is of a different type). I already do so but right now my zod "base" types are manually generated instead of inferred from the mikro-orm schema.
I also want the slonik parsers because sometimes I need to further process the query results and it's easier if you have a Point object instead of a string.