In this tutorial, you will learn how to export data from the MySQL database into a CSV file in Node js + express using json2csv.
How to Export and Download MySQL Database data to CSV file using Node js + Express
Steps to export or download database data from MySQL database into CSV file in node js with express:
Step 1 – Create Node Express js App
Run the following command on terminal to create node js app:
mkdir myApp cd myApp npm init -y
Step 2 – Connect the App to the Database
Create database.js file in your app root directory, and add the following code in it:
var mysql = require('mysql');
var conn = mysql.createConnection({
host: 'localhost', // Replace with your host name
user: 'root', // Replace with your database username
password: '', // Replace with your database password
database: 'my-node' // // Replace with your database Name
});
conn.connect(function(err) {
if (err) throw err;
console.log('Database is connected successfully !');
});
module.exports = conn;
Step 3 – Install express and required Modules
Run the following commands on the terminal to install express express-validator mysql body-parser jsonwebtoken bcryptjs cors into your node js express app:
npm install express mysql body-parser json2csv --save
Step 4 – Create Server.js File
Create server.js file and import express express-validator mysql body-parser jsonwebtoken bcryptjs cors into your server.js file; as shown below:
var createError = require('http-errors');
var express = require('express');
var path = require('path');
var bodyParser = require('body-parser');
var db = require('./database');
var Json2csvParser = require('json2csv').Parser;
const fs = require('fs');
var app = express();
app.get('/export-csv',function(req,res){
db.query("SELECT * FROM users", function (err, users, fields) {
if (err) throw err;
console.log("users:");
const jsonUsers = JSON.parse(JSON.stringify(users));
console.log(jsonUsers);
// -> Convert JSON to CSV data
const csvFields = ['id', 'name', 'email'];
const json2csvParser = new Json2csvParser({ csvFields });
const csv = json2csvParser.parse(jsonCustomers);
console.log(csv);
res.setHeader("Content-Type", "text/csv");
res.setHeader("Content-Disposition", "attachment; filename=users.csv");
res.status(200).end(csv);
// -> Check 'customer.csv' file in root project folder
});
});
// port must be set to 8080 because incoming http requests are routed from port 80 to port 8080
app.listen(3000, function () {
console.log('Node app is running on port 3000');
});
module.exports = app;
Step 5 – Start Node Express Js App Server
Execute the following command on terminal to start node express js server:
//run the below command nodemon server.js after run this command open your browser and hit http://127.0.0.1:3000/export-csv
Conclusion
Export data to CSV file in node.js + express + MySQL; Through this tutorial, you have learned how to export and download MySQL database to CSV file in Node js + express.
