Fetching Millions of Rows with Streams in Node.js
Tags: tutorial, node, webdev, beginners
Published: 2020-02-13 16:14:03

---
title: Fetching Millions of Rows with Streams in Node.js
published: true
description: Sending database queries of 4 GB to the client with smooth memory allocation.
tags: #tutorial #node #webdev #beginners
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/ahuwngqy29831nho8rtx.jpg
canonical_url: https://patrickgod.com/fetching-millions-of-rows-with-streams-in-node.js
---

Have you ever had to face the challenge of fetching several million rows of data with lots of columns from a database and display them on the web?

Well, I had to do this recently. Although I was not convinced that this would make any sense at all, I still had to do it.

Anyways, here's the solution.

But first, the technology stack: Node.js, Sequelize & MariaDB. The client doesn't matter, because at times the data was almost 4 GB big, so Chrome crashed anyway eventually.

Sequelize was the first big problem.

The solution to getting such big data results is **streaming**. Receiving that data with one big call led to Node crashing. So streaming is the answer, and Sequelize did not support streaming at that point.

Well, I was able to manually add streaming to Sequelize, but the service call takes ages in the end.

Here's a snippet of a usual Sequelize call:
```javascript
await sequelize.authenticate();
const result = await sequelize.query(sql, { type: sequelize.QueryTypes.SELECT });
res.send(result);
```

That's it. Of course, some parts are missing. Like the whole database configuration and the actual definition of the `get()` call (e.g. where does `res` come from?). But I think, you get the idea.

When you run this code, the result is simple. Node crashes. You could give node more memory with `--max-old-space-size=8000`, for instance, but that's not a real *solution*.

As already mentioned, you could kind of *force* Sequelize to stream the data. Now, how would that look like?

```javascript
var Readable = stream.Readable;
var i = 1;
var s = new Readable({
    async read(size) {
        const result = await sequelize.query(
            sql + ` LIMIT 1000000 OFFSET ${(i - 1) * 1000000}`, { type: sequelize.QueryTypes.SELECT });
        this.push(JSON.stringify(result));
        i++;
        if (i === 5) {
            this.push(null);
        }
    }
});
s.pipe(res);
```

In this example, I knew the number of rows I would get back from the database, hence the line with `if (i === 5)`. It was just a test. You have to send `null` to end the stream. You could, of course, get the `count` of the whole result first and modify the code accordingly.

The whole idea behind this is to make *smaller* database calls and return the chunks with the help of the stream. This works, Node does not crash, but it still takes ages - almost 10 minutes for 3.5 GB.

![Streaming with Sequelize](https://dev-to-uploads.s3.amazonaws.com/i/9ngk2en18zi608xrd45x.png)

What's the alternative?

The [MariaDB Node.js connector](https://github.com/mariadb-corporation/mariadb-connector-nodejs).

That's how a usual query would look like:
```javascript
const mariadb = require('mariadb');
const pool = mariadb.createPool({ host: "HOST", user: "USER", password: "PASSWORD", port: 3308, database: "DATABASE", connectionLimit: 5 });
let conn = await pool.getConnection();
const result = await conn.query(sql);
res.send(result);
```

It's much faster. But let me jump right to the streaming code:
```javascript
let conn = await pool.getConnection();
const queryStream = conn.queryStream(sql);
const ps = new stream.PassThrough();
const transformStream = new stream.Transform({
    objectMode: true,
    transform: function transformer(chunk, encoding, callback) {
        callback(null, JSON.stringify(chunk));
    }
});
stream.pipeline(
    queryStream,
    transformStream,
    ps,
    (err) => {
        if (err) {
            console.log(err)
            return res.sendStatus(400);
        }
    })
ps.pipe(res);
```

This may look a bit cryptic, but what happens here is, that you create a *pipeline* where you put stuff through. First, the `queryStream` which is the result of the database query. Then the `transformStream` to send the *stringified* chunks (only strings and buffers are allowed here, hence stringifying the object). And finally the [PassThrough](https://nodejs.org/api/stream.html#stream_class_stream_passthrough) and a function for an error case.

With `ps.pipe(res)` you stream the result to the client.

And here's the result:
![Streaming with MariaDB Node.js connector](https://dev-to-uploads.s3.amazonaws.com/i/tn83kcngckrka4vrfuut.png)

Under 4 minutes for the same data and you won't even notice that Node needs a bit RAM.

So, if you're challenged with a similar task, think about streaming the data.

Or you convince your client, that this kind of requirement is unrealistic for the web.

P.S. Pagination was not an option. We needed the whole data at once.

<small><i>Image created by brgfx on [freepik.com](https://www.freepik.com/free-vector/set-isolated-water-element_4366243.htm).</i></small>

---
### But wait, there’s more!
* Let’s connect on [Twitter](https://twitter.com/_PatrickGod), [YouTube](https://www.youtube.com/channel/UCq8LldVrjqe61KQttZlLW8g), [LinkedIn](https://www.linkedin.com/in/patrickgod) or here on [dev.to](https://dev.to/_patrickgod).
* Get the [5 Software Developer’s Career Hacks](http://patrickgod.com/5-software-developers-career-hacks/) for free.
* Enjoy more valuable articles for your developer life and career on [patrickgod.com](http://patrickgod.com).