Node.js File Upload Example Using Multer and MySQL

This Nodejs example helps to upload files using multer framework and MySQL. I will create a template to upload file using ejs. MySQL uses to save files and information into the database.

This is a very common feature that has every application. You can use file upload for images, pdf, excel, or any docs.

Multer is a node.js middleware for handling multipart/form-data, which is primarily used for uploading files. You can upload single or multiple files using multer. It’s very easy and simple to integrate with nodejs.

We will achieve the following functionality in this application:

• Create Nodejs app using package.json.
• Create Nodejs connection with MySQL.
• Upload file HTML view using nodejs ejs.
• File upload using Multer nodejs framework.

Checkout Other NodeJS tutorials,

• Node.js File Upload Example Using Multer and MySQL
• Amazon S3 Image Upload using Nodejs
• Nodejs Example to Upload and Store Image into MySQL Using Express
• NodeJS tutorial to Add, Edit and Delete Record Using MySQL

The following files and folders will participate in this application:

• package.json: This file will have all package information of this nodejs application.
• app.js: This is the entry file of nodejs application. We will have all logic here from the upload files to save data.
• views/index.ejs: Contains HTML view to upload a file.
• uploads/: Contains HTML view to upload a file.

Single/Multiple File Upload Using Multer and MySQL

Let’s implement nodejs file upload with multer and MySQL.I am assuming you have nodejs > 6 and npm > 5. You also have a MySQL server. Let’s create file-upload-example/ folder into any drive.

Create MySQL table Into Database

Now open MySQL query language, We will create a file table using the below sql script.This file will have a name, type, description, and size column.

--
-- Table structure for table `file`
--

CREATE TABLE `file` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `type` varchar(100) NOT NULL,
  `size` int(11) NOT NULL,
  `updated_by` varchar(255) NOT NULL,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Install Required Dependencies for File Upload

Create package.json file into file-upload-example/ folder.

{
  "name": "nodejs-upload-image-mysql-multer",
  "version": "1.0.0",
  "description": "",
  "main": "app.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": {
    "name": "Adam",
    "email": "[email protected]",
    "url": "http://js-tutorials.com/"
  },
  "license": "MIT",
  "dependencies": {
    "body-parser": "^1.16.1",
    "ejs": "^2.5.6",
    "express": "^4.14.1",
    "multer": "^1.3.1",
    "mysql": "^2.13.0"
  },
  "devDependencies": {
    "nodemon": "^1.18.4"
  }
}

Run the below command to install dependencies:

d:\file-upload-example>npm install

• express: We will develop our API using ExpressJs
• ejs: Embedded JavaScript templates for nodejs view.
• nodemon: nodemon is a tool that helps develop node.js in development env.
• mysql: use to connect nodejs with mysql server.
• multer: Node.js middleware for handling multipart/form-data.

File Upload HTML Using Bootstrap

I am using bootstrap 3 to create an awesome upload file view, let’s create a view folder under nodejs application and index.ejs file, We will add the below code into this file.

<div id="mainform" class="container col-sm-12">
<div id="signupbox" class="mainbox col-md-6 col-md-offset-3 col-sm-8 col-sm-offset-2" style="margin-top: 50px;">
<div class="panel panel-info">
<div class="panel-heading">
<div class="panel-title">Upload Image</div>
</div>
<div class="panel-body"><form class="form-horizontal" role="form" action="/api/v1/upload" enctype="multipart/form-data" method="post">&lt;% if (message.length &gt; 0) { %&gt;
<div class="alert alert-<%= status %> col-sm-12">&lt;%= message %&gt;</div>
&lt;% } %&gt;
<div class="form-group"><label class="col-md-3 control-label" for="image">Profile Image</label>
<div class="col-sm-9"><input class="form-control" accept="" name="profile" type="file"></div>
</div>
<div class="form-group"><!-- Button -->
<div class="col-md-offset-3 col-md-9"><button id="btn-signup" class="btn btn-info" type="submit"><i class="icon-hand-right"></i> &nbsp; Upload</button></div>
</div>
</form></div>
</div>
</div>
</div>

We have created an HTML form and added action ‘/api/v1/upload’, that endpoints will use when the HTML form will post.

You will get form data into '/api/v1/upload' endpoints, Let’s create this rest API into app.js file.

Create Node App and Start Express Server

Create nodejs application and express server:

var express = require('express')
  , routes = express.Router()
  , http = require('http')
  , path = require('path')
  , app = express()
  , multer = require('multer')
  , mysql      = require('mysql')
  , bodyParser=require("body-parser");
  
const DIR = './uploads';

var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '',
  database : 'test'
});
 
connection.connect();
 
global.db = connection;
 
let storage = multer.diskStorage({
    destination: function (req, file, callback) {
      callback(null, DIR);
    },
    filename: function (req, file, cb) {
      cb(null, file.fieldname + '-' + Date.now() + path.extname(file.originalname));
    }
});

let upload = multer({storage: storage});

// all environments
const PORT = process.env.PORT || 8010;
app.set('port', process.env.PORT || 8080);
app.set('views', __dirname + '/views');
app.set('view engine', 'ejs');
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());
app.use(express.static(path.join(__dirname, 'public')));
 app.listen(PORT, function () {
  console.log('Node.js server is running on port ' + PORT);
});
//Middleware
app.listen(8080)

We have included all package information and created objects.The MySQL configuration object needs MySQL hostname, username, pass, and database name.

We have defined the upload file destination folder is ‘uploads/’. The ejs files will be stored into 'views/' folder.

We are generating a new filename using the uploaded filename and current timestamp.

Now run 'node app.js', you will get app server is running on 8010.

Render ejs template View Using Router

Let’s create an index route, that will show the file upload view on the load of the application 'http://localhost:8010'.

message =''
app.get('/', function(req, res) {
    res.render('index', message);
});

Single File Upload API

Let’s create an API to upload a single file into app.js file, The upload file API would be POST type.

app.post('/api/v1/upload',upload.single('profile'), function (req, res) {
  message : "Error! in image upload."
    if (!req.file) {
        console.log("No file received");
          message = "Error! in image upload."
        res.render('index',{message: message, status:'danger'});
    
      } else {
        console.log('file received');
        console.log(req);
        var sql = "INSERT INTO `file`(`name`, `type`, `size`) VALUES ('" + req.file.filename + "', '"+req.file.mimetype+"', '"+req.file.size+"')";

                var query = db.query(sql, function(err, result) {
                   console.log('inserted data');
                });
        message = "Successfully! uploaded";
        res.render('index',{message: message, status:'success'});

      }
});

On upload of files, We are storing uploaded file information into the database and sending a success message, if the file is empty or not uploaded, then you will get an error message.