Nodejs/Express CSV Upload Download to MySQL/PostgreSQL – Multer, Fast-CSV, Json2Csv, Sequelize

NodejsExpress CSV Upload Download to MySQLPostgreSQL

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 RestApis
  • Multer is used to upload/download CSV files
  • Sequelize ORM is used to manipulate data with MySQL/PostgreSQL
  • Json2csv is used to save data objects to CSV file
  • fast-csv is used to parse CSV file to data objects

Related posts:

Related posts:

What will we do?

Overview – Nodejs/Express Upload Download CSV Files

Architecture

Architecture - Upload Download CSV Files to MySQL PostgreSQL
Architecture – Upload Download CSV Files to MySQL PostgreSQL

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

Nodejs Upload Download CSV File - Project Structure
Nodejs Upload Download CSV File – Project Structure
  • config package is used to setup database configuration with Sequelize ORM and Multer’s configuration for uploading file
  • models package is used to define Sequelize Customer model
  • routers package is used to define Express router for Nodejs RestAPIs
  • controllers 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 files
  • resource/static/js is used to implement Ajax Javascript code to upload multiples CSV files

Results

Upload Multiple CSV Files - Results
Upload Multiple CSV Files – Results

Check MySQL database:

Nodejs Upload CSV File Successfully - Check MySQL records
Nodejs Upload CSV File Successfully – Check MySQL records

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 having INTEGER type
  • name property has STRING type
  • address property has STRING type
  • age property has INTEGER 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 form
  • POST Api at /api/file/upload is used to upload single CSV file
  • POST Api at /api/file/multiple/upload is used to upload multiple CSV files
  • GET 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 file
  • exports.uploadMultipleFiles = async (req, res) is used to upload multiple CSV files
  • exports.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:

Html Page View Structure
Html Page View 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
Ajax Upload Download Functions
Ajax Upload Download Functions
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 + "&nbsp; : &nbsp;" + 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

Testcase 1 - backend - upload single CSV file
Testcase 1 – backend – upload single CSV file

Check MySQL database:

Testcase 1 - backend - check database records
Testcase 1 – backend – check database records

Testcase 2 – Upload Multiple CSV Files

Testcase 2 - backend - Upload Multiple CSV Files
Testcase 2 – backend – Upload Multiple CSV Files

Check database records:

Testcase 2 - backend - Check database records
Testcase 2 – backend – Check database records

Testcase 3 – Download CSV File

Testcase3 - Backend - Download CSV File
Testcase3 – Backend – Download CSV File

Frontend Testing

Testcase 1 – Upload Single CSV File

Testcase 1 - Frontend - Upload Single CSV File Successfully
Testcase 1 – Frontend – Upload Single CSV File Successfully
Testcase 1 - Frontend - Upload Single CSV File
Testcase 1 – Frontend – Upload Single CSV File

Testcase 2 – Upload Multiple CSV File

Testcase 2 - Frontend - Upload Multiple CSV File Successfully
Testcase 2 – Frontend – Upload Multiple CSV File Successfully
Testcase 2 - Frontend - Upload Multiple CSV Files
Testcase 2 – Frontend – Upload Multiple CSV Files

Testcase 3 – Download CSV File

Testcase 3 - Frontend - Download CSV File in Browser
Testcase 3 – Frontend – Download CSV File in Browser
Testcase 3 - frontend - Download CSV File Successfully
Testcase 3 – frontend – Download CSV File Successfully
Testcase 3 - Downloaded CSV file
Testcase 3 – Downloaded CSV file

Sourcecode

I include a running sourcecode for the tutorial with the implemented features:

Nodejs-Upload-CSV-Files

– Github sourcecode:

Nodejs/Express CSV Upload Download Files – GitHub SourceCode

Leave a Reply

Your email address will not be published. Required fields are marked *