Node Js MySQL CSV File Upload REST API Tutorial

Sometimes you manage your data in the CSV files; in a CSV (comma-separated values) file, data is kept in rows format; every row has specific values.

If you want to work with CSV in Node js, this tutorial is for you.

In this comprehensive tutorial, we will profoundly learn how to create CSV file upload to the MySQL database REST API using the Node and Express js packages.

To import the CSV records into MySQL will require us to use specific npm packages such as fast-csv, Express, multer, body-parser, mysql, and nodemon.

The Fast-csv is a super valuable library specially developed for Node; it parses and formats CSVs or any other delimited value file in the node environment.

How to Import or Upload CSV Records in MySQL Database using Express REST API in Node Js

• Step 1: Generate Node App
• Step 2: Install NPM Dependencies
• Step 3: Create Database Table
• Step 4: Build Server File
• Step 5: Serve Node Application

Generate Node App

Creating a brand new folder is easy; you can do it manually or invoke a single command from the command prompt.

Make sure to open the terminal on your system and then straight head over to the terminal to run the given command.

mkdir node-vlog

Step inside the project folder, here we will create the project files.

cd node-vlog

We need to make the package.json file, in this file all the project related scripts, and modules information resides.

npm init

Crete the app.js file in the node project.

We need to register this script name into the package.json file’s scripts section to start the node server.

{
  "main": "app.js",
}

Install NPM Dependencies

We have previously discussed that we need specific modules in order to build this small feature.

Here is the command that you have to execute to install the required packages

npm install express body-parser multer mysql fast-csv nodemon

Create Database Table

Using the SQL table query, create a table name for its users; in this table, we will add name and email values that will help correspond to your CSV file data.

You have to run this command from the SQL query tab.

CREATE TABLE `users` (
  `id` bigint(11) NOT NULL,
  `name` varchar(150) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE= InnoDB DEFAULT CHARSET=utf8

Build Server File

There are a couple of things going on in this file, and we are enabling the MySQL database connection using the mysql module.

Creating the api using express to send the csv data to mysql database, the file uploading part is being handled by multer module.

Do not forget to create an uploads directory in the node app.

You need to make the app.js file, in this file you have to insert the given code.

const express = require('express')
const multer = require('multer')
const mysql = require('mysql')
const fs = require('fs')
const bodyparser = require('body-parser')
const path = require('path')
const csv = require('fast-csv')

const app = express()
app.use(express.static('./public'))
app.use(bodyparser.json())
app.use(
  bodyparser.urlencoded({
    extended: true,
  }),
)
const db = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'test',
})
db.connect(function (err) {
  if (err) {
    return console.error('error: ' + err.message)
  }
  console.log('Database connected.')
})
var storage = multer.diskStorage({
  destination: (req, file, callBack) => {
    callBack(null, './uploads/')
  },
  filename: (req, file, callBack) => {
    callBack(
      null,
      file.fieldname + '-' + Date.now() + path.extname(file.originalname),
    )
  },
})
var upload = multer({
  storage: storage,
})
app.get('/', (req, res) => {
  res.sendFile(__dirname + '/index.html')
})
app.post('/api/uploadcsv', upload.single('uploadcsv'), (req, res) => {
  csvToDb(__dirname + '/uploads/' + req.file.filename)
  res.json({
    msg: 'File successfully inserted!',
    file: req.file,
  })
})
function csvToDb(csvUrl) {
  let stream = fs.createReadStream(csvUrl)
  let collectionCsv = []
  let csvFileStream = csv
    .parse()
    .on('data', function (data) {
      collectionCsv.push(data)
    })
    .on('end', function () {
      collectionCsv.shift()
      db.connect((error) => {
        if (error) {
          console.error(error)
        } else {
          let query = 'INSERT INTO users (id, name, email) VALUES ?'
          db.query(query, [collectionCsv], (error, res) => {
            console.log(error || res)
          })
        }
      })
      fs.unlinkSync(csvUrl)
    })
  stream.pipe(csvFileStream)
}
const PORT = process.env.PORT || 5555
app.listen(PORT, () => console.log(`Node app serving on port: ${PORT}

Serve Node Application

You now may run the node script through the suggested command.

nodemon

Here is the URL that you can use as an API to upload the CSV file in mysql database using node js.

http://localhost:5555/api/uploadcsv 

Conclusion

In this guide, you have learned how to create REST API using Express js; we used these APIs in Node js for saving or importing CSV file records into the MySQL database.

We also used the Fast CSV module and Multer client in our Node app to manage CSV file data.

We truly believe this tutorial must have helped you and cleared your doubts about handling the CSV delimited values in Node js.