Mapping raw query to entities

[shiro]

Issue type:

[ ] bug report [x] feature request [ ] documentation issue

Database system/driver:

[ ] cordova [ ] mongodb [ ] mssql [ ] mysql / mariadb [ ] oracle [x] postgres [ ] cockroachdb [ ] sqlite [ ] sqljs [ ] react-native [ ] expo

TypeORM version:

[ ] latest [x] @next [ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

Hello, I've been using typeorm for a while and I like it a lot, working with entities adds a lot of convenience.
I use a transformer on my entity classes to conveniently map values when reading/writing from the DB, however this only works if entities are obtained from the query builder or entity manager.

Right now there is no way to simply map a raw query to entities, however sometimes raw queries are a valuable escape hatch that will always be required in some cases, some being more common than others.
Right now I need the UNION operator which is fairly commonly used in SQL but not supported by typeorm (#2992).

I can use Object.assign or similar ways to manually map rows (like #1175), but this won't apply my transformers (and possibly other typeorm specific stuff declared by annotations).

I suggest exposing some API which would at least allow for mapping a single row to an entity so that there is some way to keep using typeorm annotations while still being able to use unsupported SQL.

[krazibit]

if the raw query fields maps directly with your entity property names you could do

EntityRepository.create({...raw})

[incompletude]

if the raw query fields maps directly with your entity property names you could do

EntityRepository.create({...raw})

My entities are camelCaseand my database is snake_case. I'm using typeorm-naming-strategies. Just a reminder. Not sure what maps directly means.

[krazibit]

Mapped Directly means for example dbColumnName is clientName and entityColumnName is clientName In your case you'll have to do something like below

import {camelCase} from 'lodash'
yourEntity = new YourEntity()
Object.keys(raw).forEach((snakeCasedKey) => {
  yourEntity[camelCase(snakeCasedKey)] = raw[snakeCasedKey]
})

[chorsnell]

if the raw query fields maps directly with your entity property names you could do

EntityRepository.create({...raw})

@krazibit @incompletude does this still work? Also what version had you used this? I'm on 0.2.32

It just seems to error

Property 'create' does not exist on type '(entity?: Function | EntitySchema<any> | undefined) => ClassDecorator'

[sgarner]

TypeORM has RawSqlResultsToEntityTransformer which can help with this mapping. The catch is that you need to pass a QueryExpressionMap instance to it which might be difficult to build manually.

However if you are able to define all your selected columns and joins etc using QueryBuilder and then modify the resulting query with your custom SQL requirements, then this will provide the QueryExpressionMap for you. I would imagine a UNION would be an applicable use case here if you can build at least one of the component queries using QueryBuilder and use string manipulation to combine them into a UNION.

For example in my use case I needed to use a locking mode in SQL Server that is not currently supported by TypeORM. I was able to create my query using QueryBuilder and then modify the lock expression in the SQL before executing it as a raw query. Then transform that into entities using RawSqlResultsToEntityTransformer.

Here is the code:

import { FindOptionsUtils } from 'typeorm';
import { RelationCountLoader } from 'typeorm/query-builder/relation-count/RelationCountLoader';
import { RelationIdLoader } from 'typeorm/query-builder/relation-id/RelationIdLoader';
import { RawSqlResultsToEntityTransformer } from 'typeorm/query-builder/transformer/RawSqlResultsToEntityTransformer';

const qb = entityManager
  .getRepository(ExampleEntity)
  .createQueryBuilder('e')
  .setLock('pessimistic_write');

FindOptionsUtils.applyOptionsToQueryBuilder(qb, {
  relations: ['foobar'],
  where: {
    foo: 'bar',
  },
  order: {
    id: 'ASC',
  },
});

let [query, parameters] = qb.getQueryAndParameters();

// 👇 Here is where you can modify the SQL for the query to suit your requirements
query = query.replace(/ WITH \(UPDLOCK, ROWLOCK\) /, ' WITH (UPDLOCK, READPAST) ');

// Execute the SQL and get raw results
const rawResults = await entityManager.query(query, parameters);

const queryRunner = entityManager.connection.createQueryRunner();
const relationIdLoader = new RelationIdLoader(
  entityManager.connection,
  queryRunner,
  qb.expressionMap.relationIdAttributes,
);
const relationCountLoader = new RelationCountLoader(
  entityManager.connection,
  queryRunner,
  qb.expressionMap.relationCountAttributes,
);
const rawRelationIdResults = await relationIdLoader.load(rawResults);
const rawRelationCountResults = await relationCountLoader.load(rawResults);
const transformer = new RawSqlResultsToEntityTransformer(
  qb.expressionMap,
  entityManager.connection.driver,
  rawRelationIdResults,
  rawRelationCountResults,
  queryRunner,
);

// Transform the raw results into entities
const examples = transformer.transform(rawResults, qb.expressionMap.mainAlias);

[golubvladimir]

@krazibit How to fix it?

export class AppService {

  constructor(
      @Inject(REQUEST)
      private request: Request,

      @InjectRepository(ArticlesEntity)
      private readonly articles: Repository<ArticlesEntity>,

      @InjectRepository(TagsEntity)
      private readonly tags: Repository<TagsEntity>,

      @InjectRepository(TableUnion)
      private readonly tableUnion: Repository<TableUnion>
  ) {}

  async getHello() {
    const entityManager = getManager();
    const connection = getConnection();

    const tableUn = this.tableUnion.createQueryBuilder('tu')
        .select('id')
        .addSelect('description', 'name')
        .getQuery();

    const tableTg = this.tags.createQueryBuilder('tg')
        .select(['id', 'name'])
        .getQuery();

    const tags = await entityManager.query(`${ tableUn } UNION ${ tableTg }`);

    const a = this.tags.create(tags);

    const result = await a.createQueryBuilder('tg')
        .select(['id', 'name'])
        .getRawMany();

    console.log(result);
  }
}

Error: Property 'createQueryBuilder' does not exist on type 'TagsEntity[]'.

[golubvladimir]

Why after mapping to entities. It doen't have all functions (CreateQueryBuilder etc). How to get it?

[imnotjames]

createQueryBuilder only exists for entities that extend BaseEntity

[golubvladimir]

@imnotjames ok, how to use select (where etc), if I have got result from raw query and created entity by create ?

[falahati]

RawSqlResultsToEntityTransformer instance used by TypeORM should be exposed, or at least add a way to map raw update results returned via returning to an objects (we already have the mappedObjects property, but it is empty).

[incompletude]

I think this feature should be provided out of the box (supporting column mapping and naming strategies). I don't get why js ORMs don't provide a good way to convert from plain to class.

Even if you build an object in the desired format you can't cast it to the desired type because it breaks TypeORM. This creates a nasty bug that the save method create new rows instead of updating the row you obtained using a raw query. Such a mess.

I have been using Prisma for a month now and I think it is smootier because I don't use that many raw queries and you can easily cast an object to a type (no active record and repository pattern in the framework itself, so you have to do it yourself).

[pasalino]

I think this is a necessary feature. There isn't any manner to query a huge quantity of rows. The single way to perform that is to use stream but if I haven't mapped Entity what is the advantage to use ORM? It's a standard feature in many ORMs for instance Prisma. Please think to implement it.

[amiryadid-fb]

Is there any update on this? It doesn't seem like a lot of work, just consider extracting this piece of code to a transform method and expose it but it doesn't seem like this issue got any attention

[divmgl]

Just ran into this. It's especially relevant when iterating through a cursor.

[divmgl]

Ah, so I figured it out. It's actually significantly simpler (now) than the answer in https://github.com/typeorm/typeorm/issues/6803#issuecomment-864681382.

To anyone who is wondering how to do this with TypeORM 0.3.10: you can use PlainObjectToNewEntityTransformer. Say your entity is called MyEntity, your data source is in a variable called dataSource, and your plain object is in a variable called t:

const metadata = dataSource.getMetadata(MyEntity);
const transformer = new PlainObjectToNewEntityTransformer();

const mergeIntoEntity = metadata.create(dataSource.createQueryRunner());
transformer.transform(mergeIntoEntity, t, metadata);

// mergeIntoEntity is now a populated MyEntity

[amiryadid-fb]

Ah, so I figured it out. It's actually significantly simpler (now) than the answer in #6803 (comment).

To anyone who is wondering how to do this with TypeORM 0.3.10: you can use PlainObjectToNewEntityTransformer. Say your entity is called MyEntity, your data source is in a variable called dataSource, and your plain object is in a variable called t:

const metadata = dataSource.getMetadata(MyEntity);
const transformer = new PlainObjectToNewEntityTransformer();

const mergeIntoEntity = metadata.create(dataSource.createQueryRunner());
transformer.transform(mergeIntoEntity, t, metadata);

// mergeIntoEntity is now a populated MyEntity

This does not work for me. My raw result: { TestEntity_id: '1', TestEntity_userId: 'Adam', TestEntity_userNumber: 1, TestEntity_createdAt: 2022-10-31T10:15:58.904Z }

It ends up an object with undefined properties (that's the same problem that happened to me when I tried to map it using the create native entity from object method, it doesn't recognize the concatenated entity alias - which the previous #6803 (comment) does recognize and transform correctly)

[JonasGroenbek]

I have attempted to follow @sgarner example, however whenever I transform the result it returns an empty array.

Does anyone know why this does not work?

 async updateOne(queryConfig: UpdateOne<User>): Promise<User> {
    const query: UpdateQueryBuilder<User> = this.createUpdateQuery(queryConfig);
    const { raw, affected } = await query.execute();

    if (!affected) {
      throw new HttpException('Could not update user', HttpStatus.CONFLICT);
    }

    const queryRunner = this.dataSource.createQueryRunner();

    const relationIdLoader = new RelationIdLoader(
      this.dataSource.manager.connection,
      queryRunner,
      query.expressionMap.relationIdAttributes,
    );

    const relationCountLoader = new RelationCountLoader(
      this.dataSource.manager.connection,
      queryRunner,
      query.expressionMap.relationCountAttributes,
    );

    const rawRelationIdResults = await relationIdLoader.load(raw);

    const rawRelationCountResults = await relationCountLoader.load(raw);

    const transformer = new RawSqlResultsToEntityTransformer(
      query.expressionMap,
      this.dataSource.driver,
      rawRelationIdResults,
      rawRelationCountResults,
    );

    console.log('raw', raw);

    const entities = transformer.transform(raw, query.expressionMap.mainAlias);

    console.log('entities', entities);

    return entities[0] as User;
  }

The console output

  console.log
    raw [
      {
        created_at: 2023-03-15T20:12:41.905Z,
        updated_at: 2023-03-15T20:12:42.003Z,
        id: 1,
        email: 'someone1@email.com',
        first_name: 'user_1',
        last_name: 'user_1',
        organization_id: 1,
        role_id: 1
      }
    ]

      at UserRepository.updateOne (user/user.repository.ts:228:13)

  console.log
    entities []

I have also attempted with the PlainObjectToNewEntityTransformer, however it does not seem to understand the underscores

    const metadata = this.dataSource.getMetadata(User);
    const transformer = new PlainObjectToNewEntityTransformer();

    const updatedUser: User = metadata.create(
      this.dataSource.createQueryRunner(),
    );
    console.log(raw);
    const x = transformer.transform(updatedUser, raw[0], metadata);

    console.log('raw', raw);
    console.log('updatedUser', updatedUser);
    return updatedUser;

Console output

  console.log
    [
      {
        created_at: 2023-03-15T20:37:33.440Z,
        updated_at: 2023-03-15T20:37:33.533Z,
        id: 1,
        email: 'someone1@email.com',
        first_name: 'user_1',
        last_name: 'user_1',
        organization_id: 1,
        role_id: 1
      }
    ]

      at UserRepository.updateOne (user/user.repository.ts:244:13)

  console.log
    x User { id: 1, email: 'someone1@email.com' }

the user entity

@Entity({ name: 'user' })
export class User extends BaseEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: 'varchar', name: 'email', nullable: false })
  email: string;

  @Column({ type: 'varchar', name: 'first_name', nullable: false })
  firstName: string;

  @Column({ type: 'varchar', name: 'last_name', nullable: false })
  lastName: string;

  @Column({ type: 'varchar', name: 'password', nullable: false })
  password: string;

  @Column({ name: 'organization_id', type: 'int', nullable: true })
  organizationId: number;

  @ManyToOne(() => Organization, (organization) => organization.users, {
    onDelete: 'CASCADE',
  })
  @JoinColumn({ name: 'organization_id', referencedColumnName: 'id' })
  organization: Organization;

  @Column({ name: 'role_id', type: 'int', nullable: false })
  roleId: number;

  @ManyToOne(() => Role, (role) => role.users, {
    onDelete: 'CASCADE',
  })
  @JoinColumn({ name: 'role_id', referencedColumnName: 'id' })
  role: Role;
}

[JonasGroenbek]

Posted SO question with 200 bounty for anyone interested in that https://stackoverflow.com/questions/75750051/typescript-postgresql-typeorm-convert-raw-result-to-entity

[andreme]

queryBuilder.getRawAndEntities() returns the entities and the raw rows from the database. That way the extra columns can be accessed.

[pasalino]

queryBuilder.getRawAndEntities() returns the entities and the raw rows from the database. That way the extra columns can be accessed.

This is very useful but doesn't resolve a typical problem about a huge amount of rows. In fact, this method returns a raw array. Should be available a method that maps single entities with async iterator for instance.

In order to do this, I'm using this method:

export const getStreamerForQueryBuilder = async <T extends ObjectLiteral>(
    queryBuilder: SelectQueryBuilder<T>
): Promise<Readonly<[ReadStream, RawEntityTransformer<T>]>> => {
    const em = queryBuilder.connection.manager
    const queryRunner = em.connection.createQueryRunner()
    const transformer = createRawSqlResultsToEntityTransformer<T>(em, queryRunner, queryBuilder)
    const [query, parameters] = queryBuilder.getQueryAndParameters()
    const toRenewalsInsurances = await queryRunner.stream(query, parameters)
    const result: [ReadStream, RawEntityTransformer<T>] = [toRenewalsInsurances, transformer]
    return result
}

[stouch]

It's a standard feature in many ORMs for instance Prisma. Please think to implement it.

Of course, this issue is now among with these 3 other reasons (3 other MAJOR issues in which TypeORM team never gave any serious answer for years...) that explain why I stopped to use Typeorm in any project now :

• https://github.com/typeorm/typeorm/issues/447#issuecomment-298896602
• https://github.com/typeorm/typeorm/issues/156#issuecomment-1934954502
• https://github.com/typeorm/typeorm/issues/9013#issuecomment-1845579103
• https://github.com/typeorm/typeorm/issues/3857#issuecomment-2098895162 (some misc performance issues, that they seem to have resolved over the years.. ) -> Performance issues are often due to INDEX but it's quite hard to debug with typeorm because of their horrific LOGGING mode (in datasource config) that force the user to log ALL their queries insead of being able to printSql() each time we need it.
• (And I can now add this obvious issue)
• And I could also add this one : https://github.com/typeorm/typeorm/issues/9415

My advise is : Stop using typeorm. Use Prisma or any other ORM.

[leephan2k1]

Why do they provide getRaw.... and not provide an API to map to entities. What the hell?

I can't believe this issue has taken several years and it's still not resolved.

Maybe I chose the wrong ORM, but I wrote too much code and couldn't rebuild it, but a few recent queries required raw results.

[SystemDisc]

Solution:

const transformer = new DocumentToEntityTransformer();
const users = rawUsers.map((rawUser) => transformer.transform(rawUser, this.usersRepository.metadata));

The RawSqlResultsToEntityTransformer expects each column to be prefixed with the alias in the table's metadata (e.g. user_id instead of id).

[sgarner]

Beware: DocumentToEntityTransformer is only applicable to MongoDB.

[SystemDisc]

Just found a bug in RawSqlResultsToEntityTransformer, making a PR

[SystemDisc]

https://github.com/typeorm/typeorm/pull/11010

Usage:

    const rawUsers = await this.usersRepository.createQueryBuilder('users').select('users.*')
      /* other stuff */
      .limit(50)
      .offset((page - 1) * 50)
      .getRawMany();
    const transformer = new RawSqlResultsToEntityTransformer(query.expressionMap, this.dataSource.driver, [], []);
    const users = transformer.transform(
      rawUsers.map((u) =>
        Object.keys(u).reduce(
          (aliasedUser, key) => ({ ...aliasedUser, [`${query.expressionMap.mainAlias!.name}_${key}`]: u[key] }),
          {} as { [key: string]: any },
        ),
      ),
      query.expressionMap.mainAlias!,
    );

[SystemDisc]

If you change your query to usersRepository.createQueryBuilder('users').select('users'), you can simply do:

    const transformer = new RawSqlResultsToEntityTransformer(query.expressionMap, this.dataSource.driver, [], []);
    const users = transformer.transform(rawUsers, query.expressionMap.mainAlias!);

[hamsof]

looking to see in prod

If you change your query to usersRepository.createQueryBuilder('users').select('users'), you can simply do:

const transformer = new RawSqlResultsToEntityTransformer(query.expressionMap, this.dataSource.driver, [], []);
const users = transformer.transform(rawUsers, query.expressionMap.mainAlias!);