Node.js: Upload CSV file data to Database

A CSV (comma-separated values) file is a plain text file that contains data which format is described in RFC4180. In this tutorial, I will show you how to upload/import CSV file data into MySQL Database using Node.js Express & fast-csv.

Excel file instead: Node.js: Upload/Import Excel file data into Database

Contents

Node.js Rest APIs for uploading CSV Files
Node.js Rest API returns CSV File
Technology
Project Structure
Setup Node.js Upload CSV File project
Configure MySQL database & Sequelize
Initialize Sequelize
Define the Sequelize Model
Create middleware for uploading & storing CSV file
Create Controller for uploading/importing CSV file
Define Routes for uploading CSV File
Create Express app server
Run & Check
Conclusion
Further Reading
Source Code

Node.js Rest APIs for uploading CSV Files

Assume that we have an .csv file that contains Tutorial data as following:

We’re gonna create a Node.js Application that provides APIs for:

uploading CSV File to the Node.js Express Server & storing data in MySQL Database
getting list of items from MySQL table
downloading MySQL table data as CSV file

After the CSV file is uploaded successfully, tutorials table in MySQL database will look like this:

If we get list of Tutorials, the Node.js Rest Apis will return:

Node.js Rest API returns CSV File

If you send request to /api/csv/download, the server will return a response with a CSV file tutorials.csv that contains data in MySQL table:

How to do this?
You need to set the HTTP header:

"Content-disposition" : "attachment; filename=[yourFileName]" 
"Content-Type" : "text/csv"

You can find step by step for downloading CSV file in the tutorial:
Node.js Download CSV file example

These are APIs to be exported:

Methods	Urls	Actions
POST	/api/csv/upload	upload a CSV File
GET	/api/csv/tutorials	get List of items in db table
GET	/api/csv/download	download db data as CSV file

Technology

express 4.17.1
multer 1.4.2
mysql2 2.2.5
fast-csv 4.3.2
sequelize 6.3.5

Project Structure

This is the project directory that we’re gonna build:

– db.config.js exports configuring parameters for MySQL connection & Sequelize.
– models/index.js: uses configuration above to initialize Sequelize, models/tutorial.model.js for Sequelize Tutorial data model.
– middleware/upload.js: initializes Multer Storage engine and defines middleware function to save CSV file in uploads folder.
– csv.controllers.js:

use fast-csv to read CSV file in uploads folder, then save data to MySQL database with Sequelize Model.
export functions for retrieving all tutorials in database table

– routes/tutorial.routes.js: defines routes for endpoints that is called from HTTP Client, use controllers (along with middleware) to handle requests.
– server.js: initializes routes, runs Express app.

Setup Node.js Upload CSV File project

Open command prompt, change current directory to the root folder of our project.
Install Express, Multer, Sequelize, Mysql2, Fast-Csv with the following command:

npm install express multer sequelize mysql2 fast-csv

The package.json file will look like this:

{
  "name": "node-js-upload-csv-files",
  "version": "1.0.0",
  "description": "Node.js Upload csv file to MySQL database - Rest Api",
  "main": "src/server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "node js",
    "upload",
    "import",
    "download",
    "csv",
    "file",
    "mysql",
    "database",
    "rest",
    "api"
  ],
  "author": "bezkoder",
  "license": "ISC",
  "dependencies": {
    "express": "^4.17.1",
    "fast-csv": "^4.3.2",
    "multer": "^1.4.2",
    "mysql2": "^2.2.5",
    "sequelize": "^6.3.5"
  }
}

Configure MySQL database & Sequelize

In the src folder, we create a separate config folder for configuration with db.config.js file like this:

module.exports = {
  HOST: "localhost",
  USER: "root",
  PASSWORD: "123456",
  DB: "testdb",
  dialect: "mysql",
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};

First five parameters are for MySQL connection.
pool is optional, it will be used for Sequelize connection pool configuration:

max: maximum number of connection in pool
min: minimum number of connection in pool
idle: maximum time, in milliseconds, that a connection can be idle before being released
acquire: maximum time, in milliseconds, that pool will try to get connection before throwing error

For more details, please visit API Reference for the Sequelize constructor.

Initialize Sequelize

Now we initialize Sequelize in src/models folder.

Create src/models/index.js with the following code:

const dbConfig = require("../config/db.config.js");

const Sequelize = require("sequelize");
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
  host: dbConfig.HOST,
  dialect: dbConfig.dialect,
  operatorsAliases: false,

  pool: {
    max: dbConfig.pool.max,
    min: dbConfig.pool.min,
    acquire: dbConfig.pool.acquire,
    idle: dbConfig.pool.idle
  }
});

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;

db.tutorials = require("./tutorial.model.js")(sequelize, Sequelize);

module.exports = db;

We’re gonna define Tutorial model in the next step.

Define the Sequelize Model

In models folder, create tutorial.model.js file like this:

module.exports = (sequelize, Sequelize) => {
  const Tutorial = sequelize.define("tutorial", {
    title: {
      type: Sequelize.STRING
    },
    description: {
      type: Sequelize.STRING
    },
    published: {
      type: Sequelize.BOOLEAN
    }
  });

  return Tutorial;
};

This Sequelize Model represents tutorials table in MySQL database. These columns will be generated automatically: id, title, description, published, createdAt, updatedAt.

After initializing Sequelize, we don’t need to write CRUD functions, Sequelize supports all of them:

create a new Tutorial: create(object)
create multiple Tutorials: bulkCreate(objects)
find a Tutorial by id: findByPk(id)
get all Tutorials: findAll()
update, remove Tutorials…

We’re gonna use bulkCreate() and findAll() in our Controller.

Create middleware for uploading & storing CSV file

Inside middleware folder, create upload.js file with the following code:

const multer = require("multer");

const csvFilter = (req, file, cb) => {
  if (file.mimetype.includes("csv")) {
    cb(null, true);
  } else {
    cb("Please upload only csv file.", false);
  }
};

var storage = multer.diskStorage({
  destination: (req, file, cb) => {
    cb(null, __basedir + "/resources/static/assets/uploads/");
  },
  filename: (req, file, cb) => {
    console.log(file.originalname);
    cb(null, `${Date.now()}-bezkoder-${file.originalname}`);
  },
});

var uploadFile = multer({ storage: storage, fileFilter: csvFilter });
module.exports = uploadFile;

In the code above, we’ve done these steps:
– First, we import multer module.
– Next, we configure multer to use Disk Storage engine.
– We also define a filter to only allow file with CSV format.

You can see that we have two options here:
– destination determines folder to store the uploaded files.
– filename determines the name of the file inside the destination folder.
– We add the [timestamp]-bezkoder- prefix to the file’s original name to make sure that the duplicates never occur.

Create Controller for uploading/importing CSV file

Now we need to import 2 necessary modules: fs & fast-csv.

controllers/tutorial/csv.controller.js

const db = require("../../models");
const Tutorial = db.tutorials;

const fs = require("fs");
const csv = require("fast-csv");

const upload = async (req, res) => {
  try {
    if (req.file == undefined) {
      return res.status(400).send("Please upload a CSV file!");
    }

    let tutorials = [];
    let path = __basedir + "/resources/static/assets/uploads/" + req.file.filename;

    fs.createReadStream(path)
      .pipe(csv.parse({ headers: true }))
      .on("error", (error) => {
        throw error.message;
      })
      .on("data", (row) => {
        tutorials.push(row);
      })
      .on("end", () => {
        Tutorial.bulkCreate(tutorials)
          .then(() => {
            res.status(200).send({
              message:
                "Uploaded the file successfully: " + req.file.originalname,
            });
          })
          .catch((error) => {
            res.status(500).send({
              message: "Fail to import data into database!",
              error: error.message,
            });
          });
      });
  } catch (error) {
    console.log(error);
    res.status(500).send({
      message: "Could not upload the file: " + req.file.originalname,
    });
  }
};

const getTutorials = (req, res) => {
  Tutorial.findAll()
    .then((data) => {
      res.send(data);
    })
    .catch((err) => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving tutorials.",
      });
    });
};

module.exports = {
  upload,
  getTutorials
};

Now look at the upload function:
– First we get and check file upload from req.file.
– Next we create a ReadStream from csv file in uploads folder, use fast-csv module to parse the data by 2 events: on('data') and on('end'):

'data' is emitted when a row is parsed, so we will append the row (data) into tutorials array in the handler function.
'end' is emitted after the parsing is done. At this time, we have all rows, so we will use Sequelize model bulkCreate() method to save the tutorials array (id, title, description, published) to MySQL database here.

The getTutorials() function uses findAll() method to return all Tutorials stored in the tutorials database table.

Define Routes for uploading CSV File

When a client sends request for an endpoint using HTTP request (POST CSV file, GET tutorials), we need to determine how the server will response by setting up the routes.

These are our routes:

/api/csv/upload: POST
/api/csv/tutorials: GET

Create a tutorial.routes.js inside routes folder with content like this:

const express = require("express");
const router = express.Router();
const csvController = require("../controllers/tutorials/csv.controller");
const upload = require("../middlewares/upload");

let routes = (app) => {
  router.post("/upload", upload.single("file"), csvController.upload);
  router.get("/tutorials", csvController.getTutorials);

  app.use("/api/csv", router);
};

module.exports = routes;

You can see that we use a controller from csv.controller.js.

Create Express app server

Finally, we create an Express server.

server.js

const express = require("express");
const app = express();
const db = require("./models");
const initRoutes = require("./routes/tutorial.routes");

global.__basedir = __dirname + "/..";

app.use(express.urlencoded({ extended: true }));
initRoutes(app);

db.sequelize.sync();
// db.sequelize.sync({ force: true }).then(() => {
//   console.log("Drop and re-sync db.");
// });

let port = 8080;
app.listen(port, () => {
  console.log(`Running at localhost:${port}`);
});

In the code above, we initialize Express Router and call Sequelize sync() method.

db.sequelize.sync();

In development, you may need to drop existing tables and re-sync database. Just use force: true as following code:


db.sequelize.sync({ force: true }).then(() => {
  console.log("Drop and re-sync db.");
});

Run & Check

First we need to create uploads folder with the path resources/static/assets.
On the project root folder, run this command: node src/server.js

Let’s use Postman to make HTTP POST request with a CSV file.

The result in MySQL tutorials table:

Conclusion

Today we’ve built a Rest CRUD API using Node.js Express to upload data from CSV file to Mysql database table.

We also see how to use fast-csv to read data from CSV file, Sequelize to retrieve items in database table without need of boilerplate code.

If you want to add Pagination while getting data from MySQL table, you can find the instruction at:
Server side Pagination in Node.js with Sequelize & MySQL

For downloading CSV File:
Node.js Download CSV file example

Happy learning! See you again.

Source Code

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

For uploading Excel file:
Node.js: Upload/Import Excel file data into Database

One thought to “Node.js: Upload CSV file data to Database”

Rick Levy says:

October 28, 2020 at 9:10 pm

thank you for this, I found it very useful and got it to work. Do you have any tips for hooking it up to a front end? I am stuck on how to structure the client side API to send a file to the server

Comments are closed to reduce spam. If you have any question, please send me an email.