How to stream query result with fastify and mysql2

<p class="wp-block-paragraph">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.</p> <p class="wp-block-paragraph"><em><strong>Important note on client side</strong></em>: 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 <code>application/json</code></p> <p class="wp-block-paragraph">The main goal here is to use less memory.</p> <p class="wp-block-paragraph">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</p> <figure class="wp-block-image size-full"><img src="https://smartango.com/wp-content/uploads/2022/10/image.png" alt="" class="wp-image-416"/><figcaption class="wp-element-caption">Stream version VS buffered version</figcaption></figure> <p class="wp-block-paragraph">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.</p> <p class="wp-block-paragraph">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).</p> <p class="wp-block-paragraph">The code is just a bit more complicated, but not too much</p> <pre class="wp-block-preformatted">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}); } })</pre> <h2 class="wp-block-heading">I will never finish to write this …</h2> <p class="wp-block-paragraph">Actually in streamed version I am using <code>rowsAsArray</code> option <code>true</code>, switching to false things get worse in term of time to serve, memory consumption stay stable anyway.</p> <p class="wp-block-paragraph">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.</p> <h2 class="wp-block-heading">Disappointed by @fastify/compress</h2> <p class="wp-block-paragraph">I am just a bit disappointed by the unfulfilled promise of fastify/compress module, here:</p> <p class="wp-block-paragraph"><a href="https://github.com/fastify/fastify-compress#replycompress">https://github.com/fastify/fastify-compress#replycompress</a></p> <p class="wp-block-paragraph">There is an example of use of <code>reply.compress()</code>, sure, but no, that method does not exists.</p> <p class="wp-block-paragraph">It is strange, I look at the code, it should work, really, I am going to ask for some hint…</p> <p class="wp-block-paragraph">UPDATE: Clarification arrived from upstream dev about fastify and compress, in short time and well explained, rif. fastify 4 porting</p> <p class="wp-block-paragraph">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.</p>

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