Nodejs Example to Upload and Store Image into MySQL Using Express

This tutorial help to upload an image into a folder and store the image path into a MySQL table using express-fileupload nodejs module. Image is very common media type to show depict information into web application.

I am creating a nodejs application using MySQL, express and ejs template. I am skipping basic node js tutorial ie. Simple Node js Example for beginners?, How to connect node js with MySQL Database?

We will gone through following steps into this node js tutorial:

How to create database and table into MySQL server
Upload Image and Store into MySQL
Get image path from Database table and display image into profile view

Update : Angular File Upload Using Angular 1.x and PHP

Simple Example of Node js to Upload and Store Image into MySQL

We will create user registration page using node.js ejs. We will take some parameters and ask to user for upload image into registration page, We will make POST node js HTTP request to insert registration data into MySQL table.

After Successfully insert record into MySQL table, We will redirect to profile page with userId and display image into it.

Checkout Other NodeJS tutorials,

I am using following files and folder

I am creating a folder ‘nodejs_image_upload_example’. This is our nodejs project name.

views folder: This folder will contains all node js ejs template files.
public folder: This folder will contains all external CSS and js files.
routes folder: This folder will contains all controller file.
public\images\upload_images folder: This folder will contains all uploaded image files.
app.php: This file will use to create node js application server and routes URL.
node_modules folder: This folder will contains all node js packages.

Create Database and Table Structure for User Authentication

I am using MySQL server for store user registration information into MySQL table. We will create a ‘test’ name database into the MySQL server. We will create the ‘users_image’ table into the ‘test’ MySQL database using below script.

CREATE TABLE IF NOT EXISTS `users_image` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `image` varchar(255) NOT NULL,
  `mob_no` int(11) NOT NULL,
  `user_name` varchar(20) NOT NULL,
  `password` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Create Package.json file

We will create package.json file for this node.js application into root path of node js application ‘nodejs_image_upload_example’. This file define what libraries or modules will be use in this nodejs project example. You need to add below code into package.json file.

{
  "name": "nodejs-store-image-mysql",
  "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",
    "express-fileupload": "^0.1.1",
    "mysql": "^2.13.0",
    "req-flash": "0.0.3",
    "then-busboy": "^1.4.0"
  }
}

Let’s install the node js module using the npm command, now open the cmd window and go to the path of your node application, like c:/nodejs_image_upload_example and run the below example.

c:/nodejs_image_upload_example> npm install

Above command will install all dependency node js modules into node_modules folder.

Upload And Store Image Using Nodejs And MySQL

We will create app.js file for main entry point of nodejs application and HTTP request to upload image into folder and store image name into MySQL table. We will create Registration HTML view file.

Step 1: Created a new nodejs_image_upload_example/app.js file. This file will use to instantiate all modules and create connection with MySQL. You need to add below code into app.js file.

/**
* Module dependencies.
*/
var express = require('express')
  , routes = require('./routes')
  , http = require('http')
  , path = require('path'),
	busboy = require("then-busboy"),
	fileUpload = require('express-fileupload'),
	app = express(),
	mysql      = require('mysql'),
	bodyParser=require("body-parser");
	
var connection = mysql.createConnection({
	host     : 'localhost',
	user     : 'root',
	password : '',
	database : 'test'
});
 
connection.connect();
 
global.db = connection;
 
// all environments
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.use(fileUpload());
 
// development only
 
app.get('/', routes.index);//call for main index page
app.post('/', routes.index);//call for signup post 
//Middleware
app.listen(8080)

We have created routing URLs that are using an index controller file.

Step 2: Created nodejs_image_upload_example/index.js file. This file will handle all GET and POST request type to view and post data to store user information MySQL.

/*
* GET home page.
*/
 
exports.index = function(req, res){
    message = '';
   if(req.method == "POST"){
      var post  = req.body;
      var name= post.user_name;
      var pass= post.password;
      var fname= post.first_name;
      var lname= post.last_name;
      var mob= post.mob_no;

	  if (!req.files)
				return res.status(400).send('No files were uploaded.');

		var file = req.files.uploaded_image;
		var img_name=file.name;

	  	 if(file.mimetype == "image/jpeg" ||file.mimetype == "image/png"||file.mimetype == "image/gif" ){
                                 
              file.mv('public/images/upload_images/'+file.name, function(err) {
                             
	              if (err)

	                return res.status(500).send(err);
      					var sql = "INSERT INTO `users_image`(`first_name`,`last_name`,`mob_no`,`user_name`, `password` ,`image`) VALUES ('" + fname + "','" + lname + "','" + mob + "','" + name + "','" + pass + "','" + img_name + "')";

    						var query = db.query(sql, function(err, result) {
    							 res.redirect('profile/'+result.insertId);
    						});
					   });
          } else {
            message = "This format is not allowed , please upload file with '.png','.gif','.jpg'";
            res.render('index.ejs',{message: message});
          }
   } else {
      res.render('index');
   }
 
};

Step 3: Created nodejs_image_upload_example/index.ejs view file. This file will display the user registration form.

<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">Register</div>
</div>
<div class="panel-body"><form class="form-horizontal" role="form" action="/" enctype="multipart/form-data" method="post">&lt;% if (message.length &gt; 0) { %&gt;
<div class="alert alert-success col-sm-12">&lt;%= message %&gt;</div>
&lt;% } %&gt;
<div id="signupalert" class="alert alert-danger" style="display: none;">Error:</div>
<div class="form-group"><label class="col-md-3 control-label" for="first_name">First Name</label>
<div class="col-md-9"><input class="form-control" name="first_name" type="text" placeholder="First Name"></div>
</div>
<div class="form-group"><label class="col-md-3 control-label" for="last_name">Last Name</label>
<div class="col-md-9"><input class="form-control" name="last_name" type="text" placeholder="Last Name"></div>
</div>
<div class="form-group"><label class="col-md-3 control-label" for="mob_no">Mobile Number</label>
<div class="col-md-9"><input class="form-control" name="mob_no" type="number" placeholder="Mobile Number"></div>
</div>
<div class="form-group"><label class="col-md-3 control-label" for="mob_no">Profile Image</label>
<div class="col-sm-9"><input class="form-control" accept="" name="uploaded_image" type="file"></div>
</div>
<div class="form-group"><label class="col-md-3 control-label" for="user_name">User Name</label>
<div class="col-md-9"><input class="form-control" name="user_name" type="text" placeholder="User Name"></div>
</div>
<div class="form-group"><label class="col-md-3 control-label" for="password">Password</label>
<div class="col-md-9"><input class="form-control" name="password" type="password" placeholder="Password"></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; Register</button></div>
</div>
</form></div>
</div>
</div>
</div>

Display Stored MySQL Image Into HTML View

We have uploaded the image into public/image/upload_images directory and stored the image name in the MySQL table, Now we will get the user record from the MySQL table and display the stored image in the HTML view file.

Step 1: Created GET request to show profile.ejs file. We will add the route url to app.js file.

app.get('/profile/:id',routes.profile);//to render users profile

Step 2: Added profile method into index.js file to show profile view file, We are passing userId as param which will use to get a record of a particular user.

exports.profile = function(req, res){
	var message = '';
	var id = req.params.id;
    var sql="SELECT * FROM `users_image` WHERE `id`='"+id+"'"; 
    db.query(sql, function(err, result){
	  if(result.length <= 0)
	  message = "Profile not found!";
	  
      res.render('profile.ejs',{data:result, message: message});
   });
};

Step 3: Created a new nodejs_image_upload_example/profile.ejs view file. This file will display the profile page.

<div class="row">
<div class="col-xs-12 col-sm-12 col-md-6 col-lg-6 col-xs-offset-0 col-sm-offset-0 col-md-offset-3 col-lg-offset-3 toppad" style="padding-top: 30px;">&lt;% if (message.length &gt; 0) { %&gt;
<div class="alert alert-danger col-sm-12">&lt;%= message %&gt;</div>
&lt;% } else {%&gt;
<div class="panel panel-info">
<div class="panel-heading">
<h3 class="panel-title">&lt;%=data[0].first_name%&gt;, &lt;%=data[0].last_name%&gt;</h3>
</div>
<div class="panel-body">
<div class="row">
<figure><img class="img-circle img-responsive" src="http://localhost:8080/images/upload_images/<%=data[0].image%>" alt="User Pic"></figure><div class="col-md-3 col-lg-3 " align="center"></div>
<div class=" col-md-9 col-lg-9 ">
<table class="table table-user-information">
<tbody>
<tr>
<td>User Name:</td>
<td>&lt;%=data[0].user_name%&gt;</td>
</tr>
<tr>
<td>Mobile:</td>
<td>&lt;%=data[0].mob_no%&gt;</td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
</div>
&lt;% } %&gt;</div>
</div>

Conclusion

We have covered MySQL connection with MySQL server.Created DB and table into MySQL. We have uploaded image into image folder and save path into MySQL table.We have also get image path from MySQL table and display into profile view file.

You can download the source code from the below link.

Download Source Code

4 thoughts to “Nodejs Example to Upload and Store Image into MySQL Using Express”

Noorullah says:

September 14, 2022 at 4:47 pm

Thank you but how to store a blob to database

1. JsTutorials Team says:
  
  September 20, 2022 at 5:50 pm
  
  you need to define col as blob type
  
John says:

October 3, 2017 at 10:51 am

Good article, keep it up

1. DigiBoi says:
  
  November 25, 2017 at 8:11 pm
  
  hi c: