node-js-upload-csv-file-database-mysql-fast-csv-feature-image

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.

Related Posts:
Upload/store images in MySQL using Node.js, Express & Multer
How to upload/store images in MongoDB using Node.js, Express & Multer
Import CSV data into PostgreSQL using Node.js
Import CSV file into MongoDB collection using Node.js

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


Node.js Rest APIs for uploading CSV Files

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

node-js-upload-csv-file-database-express-fast-csv-data

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:

node-js-upload-csv-file-database-mysql-fast-csv-table-data

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

node-js-upload-csv-file-database-mysql-fast-csv-retrieve-data

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:

node-js-upload-csv-file-database-mysql-fast-csv-download-file

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:

MethodsUrlsActions
POST/api/csv/uploadupload a CSV File
GET/api/csv/tutorialsget List of items in db table
GET/api/csv/downloaddownload 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:

node-js-upload-csv-file-database-mysql-fast-csv-project-structure

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.

node-js-upload-csv-file-database-mysql-fast-csv-post-request

The result in MySQL tutorials table:

node-js-upload-csv-file-database-mysql-fast-csv-table-data

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.

Further Reading

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

Source Code

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

Leave a Reply

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