How to stream query result with fastify and mysql2

The aim is to serve a data on a REST API as a stream as soon are available. Instead of completely get the data set from the dbms, compress it, send back to the caller, those steps can be part of a stream pipeline.

Important note on client side: if a regular client, like ajax by ReactJs, is used, it just cache all streamed data, then render it when ready. To overcome this behaviour, the content-type should be something else than application/json

The main goal here is to use less memory.

By now this is just an idea of the result of the same request made by buffering a whole result set VS streaming out result as soon it comes from database

As of now difference in time is less than 2 seconds, but a big difference in memory usage (about 300MB VS 30MB), and this is sending 8300 records.

This tests are done with NodeJS service (v18.10.0), fastify stream (4.9.2 https://fastify.io/), node-mysql2 module (v2.3.3 https://www.npmjs.com/package/mysql2), objstream ( https://www.npmjs.com/package/objstream because fastify reply.send() accepts a stream of bytes, no object mode).

The code is just a bit more complicated, but not too much

const streamResult = async (pool, request, reply) => {
  const {query, data} = request.body;
  const getPrepared = (query,data) => {...
  const {prepared, params} = getPrepared(query,data);
  
  return new Promise((resolve, reject) => {
    
    pool.getConnection( (err, conn) => {
      if(err) {
        throw new Error(err);
      }
      let transform = new ObjStream();

      let outStream = conn.execute({sql: prepared, rowsAsArray: true}, params).stream();
      
      let piped = outStream.pipe(transform)
      reply.send(piped);
      piped.on('error', (err)=> {
        reject(err);
        pool.releaseConnection(conn);
      });
      piped.on('finish', ()=> {
        pool.releaseConnection(conn);
        console.log("out stream finish");
        resolve(1)
      });
      
    })
  });
  
}

fastify.post('/sql-stream', async (req,reply) => {
    try {
        await streamResult(pool, req, reply);
        printUsage();
        console.log("END");
    } catch (err) {
        console.log("STREAM error:", err);
        reply.send({error: true});
    }
})

I will never finish to write this …

Actually in streamed version I am using rowsAsArray option true, switching to false things get worse in term of time to serve, memory consumption stay stable anyway.

Also I am not sure about the node module, and the code is so small that I should have the opportunity to rewrite it with rocket something, I think, still using stream.

Disappointed by @fastify/compress

I am just a bit disappointed by the unfulfilled promise of fastify/compress module, here:

https://github.com/fastify/fastify-compress#replycompress

There is an example of use of reply.compress(), sure, but no, that method does not exists.

It is strange, I look at the code, it should work, really, I am going to ask for some hint…

UPDATE: Clarification arrived from upstream dev about fastify and compress, in short time and well explained, rif. fastify 4 porting

UPDATE Nov 2nd, 2025: I implemented the service that way, and it really works. But I am not satisfied by this approach for a number of reasons.