Export data to CSV file in node.js express MySQL; Through this tutorial, you will learn how to export data from MySQL database into CSV file in Node js + express. And as well as learn how to download the csv file from the database in node js + express.
If you want to export or download all data of your MySQL database table. So this tutorial will show you step by step on how you can export or download MySQL database data into csv file format using Node js + express.
Export and Download MySQL Database data to CSV file using Node js + Express
Let’s follow the following 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
- Step 2 – Connect App to Database
- Step 3 – Install Node Required Modules
- Step 4 – Create Server.js File
- Step 5 – Start Node Express Js App Server
Step 1 – Create Node Express js App
Execute the following command on terminal to create node js app:
mkdir myApp cd myApp npm init -y
Step 2 – Connect App to Database
Create database.js file into your app root directory add the following code into it to connect your node js express app to the database:
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
Execute the following command on 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
- Express — Express is a minimal and flexible Node.js web application framework that provides a robust set of features for web and mobile applications.
- json2csv — A JSON to CSV and CSV to JSON converter that natively supports sub-documents and auto-generates the CSV heading.
- MySQL — MySQL an open-source relational database management system (RDBMS).
- body-parser — Express body-parser is an npm library used to process data sent through an HTTP request body. It exposes four express middlewares for parsing text, JSON, url-encoded and raw data set through an HTTP request body.
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.
