Tutorial: Nodejs/Express CSV Upload Download to MySQL/PostgreSQL – Multer, Fast-CSV, Json2Csv, Sequelize
In the tutorial, I will introduce how to build a Nodejs/Express RestAPIs application to upload/import and download/extract data from multiple CSV files to MySQL or PostgreSQL database by:
Express
is used to build Nodejs RestApisMulter
is used to upload/download CSV filesSequelize
ORM is used to manipulate data with MySQL/PostgreSQLJson2csv
is used to save data objects to CSV filefast-csv
is used to parse CSV file to data objects
Related posts:
Overview – Nodejs/Express Upload Download CSV Files
Architecture

We build Nodejs RestAPIs on the top of Express framework and use the Multer library to handle upload multiple CSV files. For handling the business processing of our application, We implement a Controller.js
that uses fast-csv
and json2csv
libraries to write/read data objects to CSV files. And for doing CRUD operations with MySQL and PostgreSQL database, we use Sequelizez ORM to finalize the jobs.
Goal
Project Structure

config
package is used to setup database configuration with Sequelize ORM and Multer’s configuration for uploading filemodels
package is used to define Sequelize Customer modelrouters
package is used to define Express router for Nodejs RestAPIscontrollers
is used to implement coding to read/write CSV files and interact with database (storing/retrieving)view
package is used to define a html view page for upload/download CSV filesresource/static/js
is used to implement Ajax Javascript code to upload multiples CSV files
Results

Check MySQL database:

Implementation – How to code it?
Backend Development
Create Nodejs project
Install express
, multer
, fast-csv
, json2csv
, sequelize
, mysql2
by cmd:
npm install --save express multer fast-csv json2csv sequelize mysql2
Check the package.json
file, you see:
{
"name": "nodejs-upload-csv-files",
"version": "1.0.0",
"description": "Nodejs Upload Download CSV File to MySQL/PostgreSQL database",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [
"Nodejs",
"Multer",
"Sequelize",
"Upload-File",
"Download-CSV-File",
"MySQL"
],
"author": "https://loizenai.com",
"license": "ISC",
"dependencies": {
"await": "^0.2.6",
"express": "^4.17.1",
"fast-csv": "^4.3.0",
"json2csv": "^5.0.1",
"multer": "^1.4.2",
"mysql2": "^2.1.0",
"path": "^0.12.7",
"sequelize": "^5.21.13"
}
}
Node.js Configuration
Database Config
We define a Sequelize setting in db.config.js
file for interacting with database:
const env = require('./env.js');
const Sequelize = require('sequelize');
const sequelize = new Sequelize(env.database, env.username, env.password, {
host: env.host,
dialect: env.dialect,
operatorsAliases: false,
pool: {
max: env.max,
min: env.pool.min,
acquire: env.pool.acquire,
idle: env.pool.idle
}
});
const db = {};
db.Sequelize = Sequelize;
db.sequelize = sequelize;
db.Customer = require('../models/customer.model.js')(sequelize, Sequelize);
module.exports = db;
– For MySQL database, we add the below configured environment’s properties in env.js
:
const env = {
database: 'loizenaidb',
username: 'root',
password: '12345',
host: 'localhost',
dialect: 'mysql',
pool: {
max: 5,
min: 0,
acquire: 30000,
idle: 10000
}
};
module.exports = env;
– For PostgreSQL, we change the env.js
as below:
const env = {
database: 'loizenai',
username: 'postgres',
password: '123',
host: 'localhost',
dialect: 'postgres',
pool: {
max: 5,
min: 0,
acquire: 30000,
idle: 10000
}
};
module.exports = env;
Multer Config
const multer = require('multer');
const storage = multer.diskStorage({
destination: (req, file, cb) => {
cb(null, __basedir + '/uploads/')
},
filename: (req, file, cb) => {
cb(null, file.fieldname + "-" + Date.now() + "-" + file.originalname)
}
});
const upload = multer({storage: storage});
module.exports = upload;
We use multer to create a uploading engine with storage’s path at __basedir + '/uploads/'
.
Create Sequelize Data Model
I create a Sequelize model for customer entity with 4 properties:
id
is a primary key havingINTEGER
typename
property hasSTRING
typeaddress
property hasSTRING
typeage
property hasINTEGER
type
module.exports = (sequelize, Sequelize) => {
const Customer = sequelize.define('customer', {
id: {
type: Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true
},
name: {
type: Sequelize.STRING
},
address: {
type: Sequelize.STRING
},
age: {
type: Sequelize.INTEGER
}
});
return Customer;
}
Define CSV Router
We create 4 RestAPIs for upload/download CSV files:
GET
Api at/
returns the upload/download formPOST
Api at/api/file/upload
is used to upload single CSV filePOST
Api at/api/file/multiple/upload
is used to upload multiple CSV filesGET
Api at/api/file
is used to download CSV file
Coding:
let express = require('express');
let router = express.Router();
let upload = require('../config/multer.config.js');
const csvWorker = require('../controllers/csv.controller.js');
let path = __basedir + '/views/';
router.get('/', (req,res) => {
console.log("__basedir" + __basedir);
res.sendFile(path + "index.html");
});
router.post('/api/file/upload', upload.single("file"), csvWorker.uploadFile);
router.post('/api/file/multiple/upload', upload.array('files', 4), csvWorker.uploadMultipleFiles);
router.get('/api/file', csvWorker.downloadFile);
module.exports = router;
Implement CSV Controller
For proccesing Upload/Download CSV files, we implement csv.controller.js
file with 3 functions:
exports.uploadFile = (req, res)
is used to upload single CSV fileexports.uploadMultipleFiles = async (req, res)
is used to upload multiple CSV filesexports.downloadFile = (req, res)
is used to download CSV file
Upload CSV Files
Parse CSV File to data object
For parsing Data Objects from CSV file rows, we use fast-csv
library as below:
import * as fs from 'fs';
import * as path from 'path';
import * as csv from 'fast-csv';
fs.createReadStream(path.resolve(__dirname, 'assets', 'parse.csv'))
.pipe(csv.parse({ headers: true }))
.on('error', error => console.error(error))
.on('data', row => console.log(row))
.on('end', (rowCount: number) => console.log(`Parsed ${rowCount} rows`));
Upload Single CSV File
exports.uploadFile = (req, res) => {
try{
const customers = [];
fs.createReadStream(__basedir + "/uploads/" + req.file.filename)
.pipe(csv.parse({ headers: true }))
.on('error', error => {
console.error(error);
throw error.message;
})
.on('data', row => {
customers.push(row);
console.log(row);
})
.on('end', () => {
// Save customers to MySQL/PostgreSQL database
Customer.bulkCreate(customers).then(() => {
const result = {
status: "ok",
filename: req.file.originalname,
message: "Upload Successfully!",
}
res.json(result);
});
});
}catch(error){
const result = {
status: "fail",
filename: req.file.originalname,
message: "Upload Error! message = " + error.message
}
res.json(result);
}
}
After uploading, CSV file is saved to path __basedir + "/uploads/" + req.file.filename
.
We use fast-csv
to parse the CSV file to data objects.
Then we use function Customer.bulkCreate(customers)
to save an array customer objects to database.
After all, we build a result object and return it to client with simple format as below:
...
const result = {
status: "fail",
filename: req.file.originalname,
message: "Upload Error! message = " + error.message
}
res.json(result);
...
Upload Multiple CSV Files
We create a function exports.uploadMultipleFiles = async (req, res)
for uploading multiple CSV files:
exports.uploadMultipleFiles = async (req, res) => {
const messages = [];
for (const file of req.files) {
try{
// Parsing CSV Files to data array objects
const csvParserStream = fs.createReadStream(__basedir + "/uploads/" + file.filename)
.pipe(csv.parse({ headers: true }));
var end = new Promise(function(resolve, reject) {
let customers = [];
csvParserStream.on('data', object => {
customers.push(object);
console.log(object);
});
csvParserStream.on('end', () => {
resolve(customers);
});
csvParserStream.on('error', error => {
console.error(error);
reject
}); // or something like that. might need to close `hash`
});
await (async function() {
let customers = await end;
// save customers to MySQL/PostgreSQL database
await Customer.bulkCreate(customers).then(() => {
const result = {
status: "ok",
filename: file.originalname,
message: "Upload Successfully!",
}
messages.push(result);
});
}());
}catch(error){
console.log(error);
const result = {
status: "fail",
filename: file.originalname,
message: "Error -> " + error.message
}
messages.push(result);
}
}
return res.json(messages);
}
What exactly does the function do?
– For each uploading file, the function uses fs.createReadStream()
to extract data to a list object. Then it uses a function .bulkCreate()
of Sequelize to save a list object to database. After all, it returns an array messages to rest client.
Download CSV File
Write data objects to CSV file
For writing data objects to CSV file, we use const Json2csvParser = require('json2csv').Parser;
to do it:
const jsonCustomers = JSON.parse(JSON.stringify(objects));
const csvFields = ['Id', 'Name', 'Address', 'Age'];
const json2csvParser = new Json2csvParser({ csvFields });
const csvData = json2csvParser.parse(jsonCustomers);
Downlad CSV File
We implement a function exports.downloadFile = (req, res)
to get data from database and save it to CSV file then returns back to client:
exports.downloadFile = (req, res) => {
Customer.findAll({attributes: ['id', 'name', 'address', 'age']}).then(objects => {
const jsonCustomers = JSON.parse(JSON.stringify(objects));
const csvFields = ['Id', 'Name', 'Address', 'Age'];
const json2csvParser = new Json2csvParser({ csvFields });
const csvData = json2csvParser.parse(jsonCustomers);
res.setHeader('Content-disposition', 'attachment; filename=customers.csv');
res.set('Content-Type', 'text/csv');
res.status(200).end(csvData);
});
}
Frontend Development
Implement Html Upload Form
We use Bootstrap framework to build a html upload forms as below structure:

– Details html source code in ./views/index.html
file:
<!DOCTYPE html>
<html lang="en">
<head>
<title>Upload Download File Examples</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js"></script>
<script type="text/javascript" src="/static/js/uploaddownloadfiles.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-sm-7" style="background-color:#e6fffa; padding:10px; border-radius:3px">
<h3>Upload Single File</h3>
<form id="uploadSingleFileForm">
<div class="form-group">
<label class="control-label" for="uploadfile">Choose a File:</label>
<input type="file" class="form-control"
placeholder="Choose a upload file" name="file" required></input>
</div>
<button type="submit" class="btn btn-danger" id="btnUploadSingleFileSubmit">Submit</button>
</form>
<div id="response" style="display:none">
</div>
</div>
</div>
<hr>
<div class="row">
<div class="col-sm-7" style="background-color:#e6fffa; padding:10px; border-radius:3px">
<h3>Upload Multiple Files</h3>
<form id="uploadMultipleFilesForm">
<div class="form-group">
<label class="control-label" for="uploadfiles">Choose Files:</label>
<input type="file" class="form-control"
placeholder="Choose upload files" name="files" multiple required></input>
</div>
<button type="submit" class="btn btn-danger" id="btnUploadMultipleFilesSubmit">Submit</button>
</form>
<div id="responses" style="display:none">
</div>
</div>
</div>
<hr>
<div id="downloadfiles" class="row">
<div class="col-sm-7">
<a href="/api/file" class="btn btn-info" role="button">Download CSV File</a>
</div>
</div>
</div>
</body>
</html>
Implement Ajax Upload CSV files
We implement Ajax function to upload Excel files to Nodejs server in file resources/static/js/uploaddownloadfiles.js
with 2 functions:
$("#uploadSingleFileForm").submit(function(evt)
is used to upload single CSV file$("#uploadMultipleFilesForm").submit(function(evt)
is used to upload multiple CSV files

Upload single CSV File
– Coding:
$("#uploadSingleFileForm").submit(function(evt) {
evt.preventDefault();
let formData = new FormData($(this)[0]);
$.ajax({
url : '/api/file/upload',
type : 'POST',
data : formData,
async : false,
cache : false,
contentType : false,
enctype : 'multipart/form-data',
processData : false,
success : function(response) {
$("#response").empty();
if(response.status !== "error"){
let displayInfo = response.filename + " : " + response.message + "<br>";
$("#response").append(displayInfo);
// add some css
$("#response").css("display", "block");
$("#response").css("background-color", "#e6e6ff");
$("#response").css("border", "solid 1px black");
$("#response").css("border-radius", "3px");
$("#response").css("margin", "10px");
$("#response").css("padding", "10px");
}else{
$("#response").css("display", "none");
let error = response.error;
alert(error);
}
},
error: function(e){
alert("Fail! " + e);
}
});
return false;
});
Upload Multiple CSV Files
– Coding:
$("#uploadMultipleFilesForm").submit(function(evt) {
evt.preventDefault();
let formData = new FormData($(this)[0]);
$.ajax({
url : '/api/file/multiple/upload',
type : 'POST',
data : formData,
async : false,
cache : false,
contentType : false,
enctype : 'multipart/form-data',
processData : false,
success : function(response) {
$("#responses").empty();
let displayInfo = "<ul>";
for(let i=0; i<response.length; i++){
displayInfo += "<li>" + response[i].filename + " : " + response[i].message;
displayInfo += "</li>";
}
$("#responses").append(displayInfo + "</ul>");
$("#responses").css("display", "block");
// add some css
$("#responses").css("background-color", "#e6e6ff");
$("#responses").css("border", "solid 1px black");
$("#responses").css("border-radius", "3px");
$("#responses").css("margin", "10px");
$("#responses").css("padding", "10px");
},
error: function(e){
alert("Fail! " + e);
}
});
return false;
});
Testing
Backend APIs Testing
Testcase 1 – Upload Single CSV File

Check MySQL database:

Testcase 2 – Upload Multiple CSV Files

Check database records:

Testcase 3 – Download CSV File

Frontend Testing
Testcase 1 – Upload Single CSV File


Testcase 2 – Upload Multiple CSV File


Testcase 3 – Download CSV File



Sourcecode
I include a running sourcecode for the tutorial with the implemented features:
– Github sourcecode:
Nodejs/Express CSV Upload Download Files – GitHub SourceCode