Export MySQL data to CSV file using Node.js

In previous tutorial, we’ve known how to use Node.js to import CSV data into MySQL Database table. Today you’re gonna look at way to do the opposite: Export MySQL data to CSV file. The process has 2 steps:

Read MySQL data using mysql module
Export MySQL data to CSV file using one of three modules: fast-csv, csv-writer or fs

Rest API: Node.js Download CSV file from MySQL Database example

Contents

Read MySQL data
Export MySQL data to CSV file using fast-csv
Export MySQL data to CSV file using csv-writer
Export MySQL data to CSV file using fs
Conclusion
Further Reading
Source Code

Read MySQL data

Assume that we have a table in database named category:

mysql> SELECT * FROM category;
+----+------------+------------------------------------------------+------------+
| id | name       | description                                    | created_at |
+----+------------+------------------------------------------------+------------+
|  1 | Node.js    | JavaScript runtime environment                 | 2019-09-03 |
|  2 | Vue.js     | JavaScript Framework for building UI           | 2019-09-06 |
|  3 | Angular.js | Platform for building mobile & desktop web app | 2019-09-09 |
+----+------------+------------------------------------------------+------------+

First, we need to use mysql module to connect and get data from MySQL database.
So run the command: npm install mysql.

The code below is the way we create connection to MySQL database and query data:

const mysql = require("mysql");

// Create a connection to the database
const connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "123456",
  database: "testdb"
});

// open the MySQL connection
connection.connect(error => {
  if (error) throw error;

  // query data from MySQL
  connection.query("SELECT * FROM category", function(error, data, fields) {
    if (error) throw error;

    const jsonData = JSON.parse(JSON.stringify(data));
    console.log("jsonData", jsonData);

    // TODO: export to CSV file
  });
});

If we run the code, console will show jsonData value:

jsonData [ { id: 1,
    name: 'Node.js',
    description: 'JavaScript runtime environment',
    created_at: '2019-09-02T17:00:00.000Z' },
  { id: 2,
    name: 'Vue.js',
    description: 'JavaScript Framework for building UI',
    created_at: '2019-09-05T17:00:00.000Z' },
  { id: 3,
    name: 'Angular.js',
    description: 'Platform for building mobile & desktop web app',
    created_at: '2019-09-08T17:00:00.000Z' } ]

In the next steps, you will see 3 ways to export MySQL data to a CSV file that use:
– fast-csv module
– csv-writer module
– fs module

An overview of CSV file format can be found at:
Import CSV data into MySQL using Node.js.

Export MySQL data to CSV file using fast-csv

To use fast-csv module, run the command first: npm install fast-csv.

Then import the module and use it:

const fastcsv = require("fast-csv");
const fs = require("fs");
const ws = fs.createWriteStream("bezkoder_mysql_fastcsv.csv");

const jsonData = ...;

fastcsv
  .write(jsonData, { headers: true })
  .on("finish", function() {
    console.log("Write to bezkoder_mysql_fastcsv.csv successfully!");
  })
  .pipe(ws);

This is the full code:

const mysql = require("mysql");
const fastcsv = require("fast-csv");
const fs = require("fs");
const ws = fs.createWriteStream("bezkoder_mysql_fastcsv.csv");

// Create a connection to the database
const connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "123456",
  database: "testdb"
});

// open the MySQL connection
connection.connect(error => {
  if (error) throw error;

  // query data from MySQL
  connection.query("SELECT * FROM category", function(error, data, fields) {
    if (error) throw error;

    const jsonData = JSON.parse(JSON.stringify(data));
    console.log("jsonData", jsonData);

    fastcsv
      .write(jsonData, { headers: true })
      .on("finish", function() {
        console.log("Write to bezkoder_mysql_fastcsv.csv successfully!");
      })
      .pipe(ws);
  });
});

Run the code above, csv file bezkoder_mysql_fastcsv.csv will be generated with content like this:

id,name,description,created_at
1,Node.js,JavaScript runtime environment,2019-09-02T17:00:00.000Z
2,Vue.js,JavaScript Framework for building UI,2019-09-05T17:00:00.000Z
3,Angular.js,Platform for building mobile & desktop web app,2019-09-08T17:00:00.000Z

Export MySQL data to CSV file using csv-writer

In this way, we also need to install csv-writer module with command:
npm install csv-writer

This module has createObjectCsvWriter function that returns a CsvWriter object.
To get the object, we pass 2 parameters to the function: path & header.

const createCsvWriter = require("csv-writer").createObjectCsvWriter;

const jsonData = ...;

const csvWriter = createCsvWriter({
  path: "bezkoder_mysql_csvWriter.csv",
  header: [
    { id: "id", title: "id" },
    { id: "name", title: "name" },
    { id: "description", title: "description" },
    { id: "created_at", title: "created_at" }
  ]
});

csvWriter
  .writeRecords(jsonData)
  .then(() =>
    console.log("Write to bezkoder_mysql_csvWriter.csv successfully!")
  );

We use writer’s writeRecords() method to write data to csv file.
Full code is like this:

const mysql = require("mysql");
const createCsvWriter = require("csv-writer").createObjectCsvWriter;

// Create a connection to the database
const connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "123456",
  database: "testdb"
});

// open the MySQL connection
connection.connect(error => {
  if (error) throw error;

  // query data from MySQL
  connection.query("SELECT * FROM category", function(error, data, fields) {
    if (error) throw error;

    const jsonData = JSON.parse(JSON.stringify(data));
    console.log("jsonData", jsonData);

    const csvWriter = createCsvWriter({
      path: "bezkoder_mysql_csvWriter.csv",
      header: [
        { id: "id", title: "id" },
        { id: "name", title: "name" },
        { id: "description", title: "description" },
        { id: "created_at", title: "created_at" }
      ]
    });

    csvWriter
      .writeRecords(jsonData)
      .then(() =>
        console.log("Write to bezkoder_mysql_csvWriter.csv successfully!")
      );
  });
});

Export MySQL data to CSV file using fs

Now go to the last way, we don’t need to install fs because it exists in core modules.
But another module is required: json2csv.
So, run the command: npm install json2csv.

The json2csv module has Parser class that we can use parse() method to get the CSV formated data as a string. Then we use fs writeFile() function to write that string to CSV file:

const Json2csvParser = require("json2csv").Parser;
const fs = require("fs");

const jsonData = ...;

const json2csvParser = new Json2csvParser({ header: true});
const csv = json2csvParser.parse(jsonData);

fs.writeFile("bezkoder_mysql_fs.csv", csv, function(error) {
  if (error) throw error;
  console.log("Write to bezkoder_mysql_fs.csv successfully!");
});

The code below shows full implementation:

const mysql = require("mysql");
const Json2csvParser = require("json2csv").Parser;
const fs = require("fs");

// Create a connection to the database
const connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "123456",
  database: "testdb"
});

// open the MySQL connection
connection.connect(error => {
  if (error) throw error;

  // query data from MySQL
  connection.query("SELECT * FROM category", function(error, data, fields) {
    if (error) throw error;

    const jsonData = JSON.parse(JSON.stringify(data));
    console.log("jsonData", jsonData);

    const json2csvParser = new Json2csvParser({ header: true});
    const csv = json2csvParser.parse(jsonData);

    fs.writeFile("bezkoder_mysql_fs.csv", csv, function(error) {
      if (error) throw error;
      console.log("Write to bezkoder_mysql_fs.csv successfully!");
    });
  });
});

Conclusion

Today we’ve known 3 ways to export MySQL data to a CSV file using fast-csv, csv-writer or fs module. Before that, we also read MySQL table using mysql module.

You can find how to do the opposite in the previous tutorial:
Import CSV data into MySQL using Node.js

Or build a Rest API:
Node.js Download CSV file from MySQL Database example

Happy learning! See you again.

Source Code

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

4 thoughts to “Export MySQL data to CSV file using Node.js”

Ritwika says:

January 9, 2020 at 10:12 am

hi..currently my csv is being created in the same folder as my project. I wish to put it on the server, how can i give the default file path for user to download?
ntm1406 says:

December 2, 2019 at 5:43 pm

Hi there,
I use your method of generating a .csv file in a post request of Express js, but it does not generate csv file. Without the post request, I can do it successfully.
1. bezkoder says:
  
  December 4, 2019 at 1:24 am
  
  Hi, I will write a tutorial with Apis for generating a .csv file in the future.
izilouis says:

November 21, 2019 at 9:43 am

Thank you, please make more post about Node.js & MySQL, thanks!