TypeScript PostgreSQL TypeORM convert raw result to entity

Question

Unfortunately it seems as if TypeORM does not provide a API to convert raw results from either EntityManager.query or QueryBuilder.execute to entities. However, https://github.com/typeorm/typeorm/issues/6803 indicates that you can use TypeORM internal classes like PlainObjectToNewEntityTransformer or RawSqlResultsToEntityTransformer.

I have tried both with little luck.

RawSqlResultsToEntityTransformer

 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: '[email protected]',
        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 []

PlainObjectToNewEntityTransformer

 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 metadata = this.dataSource.getMetadata(User);
    const transformer = new PlainObjectToNewEntityTransformer();

    const updatedUser: User = metadata.create(
      this.dataSource.createQueryRunner(),
    );

    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: '[email protected]',
        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: '[email protected]' }

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;
}

How can I transform raw results from EntityManager.query or a query builder result (update, delete, or insert with QueryBuilder.returning('*') with either PlainObjectToNewEntityTransformer or RawSqlResultsToEntityTransformer?

Answer 1

RawSqlResultsToEntityTransformer is the correct way to do this.

But it's only known to work with SelectQueryBuilder. I suspect the reason you're having trouble in the given example is that you are trying to map the results from an UpdateQueryBuilder, which might not produce the metadata required to perform the mapping.

PlainObjectToNewEntityTransformer is not the right tool. It has a different purpose, which is to turn a plain object representing an entity into a class instance of that entity. This won't do any property<->column name mapping at all, as you observed.

The easiest solution in this scenario is going to be to run the update first, get the IDs of the affected rows, and then use a SelectQueryBuilder to fetch the data from those rows that you want to map to entities.