Nest.js TypeORM Streaming Query to CSV File — Perf not so good

TypeORM have the stream method on the createQueryBuilder then we can chain this to some processing like newLineToSpace , make the CSV and send back to the response.

The code may look like below

import { ReadStream } from 'typeorm/platform/PlatformTools';
import { pipeline } from 'stream/promises';


// ... Nest.js Controller ...

const manager = this.datasource.createEntityManager();
const queryStream: ReadStream = await manager
  .createQueryBuilder()
  .select(`*`)
  .from(tableName, tableName)
  .where(whereClause)
  .stream();

res.set('Content-type', 'application/csv');
res.attachment(`${tableName}.csv`);

await pipeline(
  queryStream,
  newLineToSpace,
  csv.stringify({ header: true, quoted: true }),
  res,
);

The output

Output of TypeORM stream() will be object by object

For processing any stream, you need transform stream which easy to write in form of Async Generator like below

const newLineToSpace = async function* (source) {
  for await (const chunk of source) {
    yield _.mapValues(chunk, (value) =>
      typeof value === 'string' ? value.replace(/\n/g, ' ') : value,
    );
  }
};

Limitation

It will not support convert to an entity, you will always get the collections of object.

If you want to use Nest.js StreamableFile

According to Nest.js doc, there is streamable file

https://docs.nestjs.com/techniques/streaming-files

@Res({passthrough: true}) res: Response

Because you need both manipulation of res and internal mechanism of Nest.js to handle the StreamableFile so you need passthrough: true

Like the doc say so https://docs.nestjs.com/controllers

On Performance

Initially I think it going to be better.

Unfortunately, this is worse on the performance.

I guess this is becuase the TypeORM “object by object” fetching as “chunk” that make it counter productive.

Stream is intended to process on Buffer ,UInt8Array or something similar which is not one-by-one iterate through like this case.