Node.js Download CSV file example

A CSV (comma-separated values) file is a plain text file that contains data which format is described in RFC4180. In this tutorial, I will show you how to build Node.js Express Rest API to download CSV file from a table in MySQL Database using json2csv.

Related Posts:
– Node.js: Upload CSV file data into Database
– Export MySQL data to CSV file using Node.js
– Export PostgresSQL data to CSV file using Node.js
– Export MongoDB collection to CSV file using Node.js

Download Excel file instead:
Node.js Download Excel file example with exceljs

Node.js Download CSV File overview

Assume that we have a tutorials table in MySQL database like this:

We’re gonna create a Node.js Application that provides APIs for downloading MySQL database table data as CSV file with following content:

If you send request to /api/csv/download, the server will return a response with a CSV file tutorials.csv that contains data in MySQL table:

How to do this?
You need to set the HTTP header:

"Content-disposition" : "attachment; filename=[yourFileName]" 
"Content-Type" : "text/csv"

We’re gonna apply the information above later in this tutorial.

You can also find how to upload CSV File to the Node.js Express Server & store data in MySQL Database in the post:
Node.js: Upload CSV file data into Database

Technology

• express 4.17.1
• mysql2 2.2.5
• json2csv 5.0.3
• sequelize 5.21.13

Project Structure

This is the project directory that we’re gonna build:

– db.config.js exports configuring parameters for MySQL connection & Sequelize.
– models/index.js: uses configuration above to initialize Sequelize, models/tutorial.model.js for Sequelize Tutorial data model.
– csv.controllers.js: get data from database table, then uses json2csv to write out and return CSV file.
– routes/tutorial.routes.js: defines routes for endpoints that is called from HTTP Client, use controllers to handle requests.
– server.js: initializes routes, runs Express app.

Setup Node.js Download CSV File project

Open command prompt, change current directory to the root folder of our project.
Install Express, Multer, Sequelize, Mysql2, Json2CSV with the following command:

npm install express sequelize mysql2 json2csv

The package.json file will look like this:

{
  "name": "node-js-download-csv-file",
  "version": "1.0.0",
  "description": "Node.js Download CSV File from MySQL Database with Express Rest API",
  "main": "src/server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "node js",
    "rest",
    "api",
    "express",
    "download",
    "csv",
    "file",
    "export",
    "mysql",
    "database"
  ],
  "author": "bezkoder",
  "license": "ISC",
  "dependencies": {
    "express": "^4.17.1",
    "json2csv": "^5.0.3",
    "mysql2": "^2.2.5",
    "sequelize": "^6.3.5"
  }
}

Configure MySQL database & Sequelize

In the src folder, we create a separate config folder for configuration with db.config.js file like this:

module.exports = {
  HOST: "localhost",
  USER: "root",
  PASSWORD: "123456",
  DB: "testdb",
  dialect: "mysql",
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};

First five parameters are for MySQL connection.
pool is optional, it will be used for Sequelize connection pool configuration:

• max: maximum number of connection in pool
• min: minimum number of connection in pool
• idle: maximum time, in milliseconds, that a connection can be idle before being released
• acquire: maximum time, in milliseconds, that pool will try to get connection before throwing error

For more details, please visit API Reference for the Sequelize constructor.

Initialize Sequelize

Now we initialize Sequelize in src/models folder.

Create src/models/index.js with the following code:

const dbConfig = require("../config/db.config.js");

const Sequelize = require("sequelize");
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
  host: dbConfig.HOST,
  dialect: dbConfig.dialect,
  operatorsAliases: false,

  pool: {
    max: dbConfig.pool.max,
    min: dbConfig.pool.min,
    acquire: dbConfig.pool.acquire,
    idle: dbConfig.pool.idle
  }
});

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;

db.tutorials = require("./tutorial.model.js")(sequelize, Sequelize);

module.exports = db;

We’re gonna define Tutorial model in the next step.

Define the Sequelize Model

In models folder, create tutorial.model.js file like this:

module.exports = (sequelize, Sequelize) => {
  const Tutorial = sequelize.define("tutorial", {
    title: {
      type: Sequelize.STRING
    },
    description: {
      type: Sequelize.STRING
    },
    published: {
      type: Sequelize.BOOLEAN
    }
  });

  return Tutorial;
};

This Sequelize Model represents tutorials table in MySQL database with columns: id, title, description, published, createdAt, updatedAt.

After initializing Sequelize, we don’t need to write CRUD functions, Sequelize supports all of them, including retrieving all Tutorials with findAll() method that we’re gonna use in our Controller.

Create Controller for downloading CSV file

controllers/tutorial/csv.controller.js

const db = require("../../models");
const Tutorial = db.tutorials;

const CsvParser = require("json2csv").Parser;

const download = (req, res) => {
  Tutorial.findAll().then((objs) => {
    let tutorials = [];

    objs.forEach((obj) => {
      const { id, title, description, published } = obj;
      tutorials.push({ id, title, description, published });
    });

    const csvFields = ["Id", "Title", "Description", "Published"];
    const csvParser = new CsvParser({ csvFields });
    const csvData = csvParser.parse(tutorials);

    res.setHeader("Content-Type", "text/csv");
    res.setHeader("Content-Disposition", "attachment; filename=tutorials.csv");

    res.status(200).end(csvData);
  });
};

module.exports = {
  download
};

Now look at the download function:
– First we use Sequelize Model findAll() method to return all Tutorials stored in the tutorials table.
– Then we use json2csv to write data to CSV file:

• create a new Workbook
• add a new CsvParser object with initial Fields as Header
• call CsvParser.parse() with array of objects you want to add as parameter
• set Header "Content-Type" and "Content-Disposition" for the HTTP response

Define Routes for downloading CSV File

When a client sends request for an endpoint using HTTP request (GET excel file), we need to determine how the server will response by setting up the routes.

This is the route we need to setup: GET /api/csv/download.

Create a tutorial.routes.js inside routes folder with content like this:

const express = require("express");
const router = express.Router();
const csvController = require("../controllers/tutorials/csv.controller");

let routes = (app) => {
  router.get("/download", csvController.download);

  app.use("/api/csv", router);
};

module.exports = routes;

You can see that we use a controller from csv.controller.js.

Create Express app server

Finally, we create an Express server.

server.js

const express = require("express");
const app = express();
const db = require("./models");
const initRoutes = require("./routes/tutorial.routes");

app.use(express.urlencoded({ extended: true }));
initRoutes(app);

db.sequelize.sync();

let port = 8080;
app.listen(port, () => {
  console.log(`Running at localhost:${port}`);
});

In the code above, we initialize Express Router and call Sequelize sync() method.

db.sequelize.sync();

Run the Node.js Download CSV File App

Run the Node.js App with command: node src/server.js.
Now you can use browser or a HTTP Client to send GET request to http://localhost:8080/api/csv/download.

Conclusion

Today we’ve built a Rest API using Node.js Express to download CSV file that contains table data in MySQL database.

We also see how to use json2csv to write data to CSV file, Sequelize to retrieve items in database table without need of boilerplate code.

You can also find how to upload CSV File to the Node.js Server & store data in MySQL Database in the post:
Node.js: Upload CSV file data into Database

Or download Excel file instead:
Node.js Download Excel file example with exceljs

Happy learning! See you again.

Further Reading

• https://www.npmjs.com/package/express
• https://sequelize.org/v3/api/model/
• json2csv

Source Code

You can find the complete source code for this example on Github.