How to Store Image in MySQL Database Using Node.js

In this tutorial, You will learn how to store image in a MySQL database using Node.js & express application. Even you implement it with some simple steps & MVC like model, view & controller. Once you complete all the given steps, you will easily upload single or multiple images to the folder and also store the image path in the database.

how to store image in mysql database using node.js

Contents

Upload Image and Store Path in Database Using Node.js & MySQL
Store Image Path in MySQL Database Using Node.js

Upload Image and Store Path in Database Using Node.js & MySQL

Before upload and store image in the database using Node.js & MySQL, you must set up the following steps

Learn also –

How to Upload Multiple Files Using Multer in Node.js

1. Create New Folders & Files

First of all, Install Express Application and Create the following new folders & files

nodeapp/
  |__controllers/
  |     |__image-controller.js
  |__middlewares/
  |     |_image-middleware.js
  |__models/
  |     |__image-model.js
  |__public/
  |     |__images/
  |__routes/
  |     |__image-route.js
  |__views/
  |     |__upload-form.ejs
  |__app.js
  |__database.js

2. Create MySQL Database & Table

you must create a Database with the name nodeapp

Create a table with the name image into the MySQL database nodeapp

Table Name – images

CREATE TABLE `images` ( 
 `id` int(10) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, 
 `image_name` varchar(255) DEFAULT NULL
)

3. Connect Node.js App to MySQL Database

First, make sure the MySQL module must be installed
Create database.js in the nodeapp and write the Node.js MySQL database connection script

File Name – database.js

var mysql = require('mysql');
var conn = mysql.createConnection({
  host: 'localhost', 
  user: 'root',      
  password: '',     
  database: 'nodeapp'
}); 
conn.connect(function(err) {
  if (err) throw err;
  console.log('Database is connected successfully !');
});
module.exports = conn;

4. Install Multer Module

Multer module helps us to upload images easily. So, you must install it with the help of the following command

nodeapp> npm install --save multer

Store Image Path in MySQL Database Using Node.js

Now, you have to follow all the next steps to upload & store the image in the MySQL database.

1. Create an Image upload form

You have to declare three main attributes within the form tag.

action=”/store-image” – It will redirect to /store-image through the POST method when you submit the form.
method=”POST” – It will send your image information with security.
enctype=”multipart/form-data” – It handles file uploading request.

Even you have to declare three attributes within the input field

type=”file” – It creates a browse Button to select any files from your device
name=”image” – You will need it to access image information
multiple – It allows us to select multiple files/images at a time

File Name – upload-form.ejs

<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
</head>
<body>
    
<form action="/store-image" method="POST" enctype="multipart/form-data">
    <label>Store Image</label><br><br>
    <p><%=(typeof alertMsg!='undefined')? alertMsg:''%></p>
    <input type="file"  name="image" multiple>
    <button type="submit">Upload</button>
</form>

</body>
</html>

2. Store Image Path in Database

You can store images through the Model. So, configure the following steps – –

Create a folder models
Also, Create a file image-model.js
Create a function storeImage with two parameters inputValues & callback. Where inputValues will take image value from the controller method.
After that, write a SQL query to fetch the image name from the database and check the image is already existed or not.
Also, Write a SQL query to store image path in the database
At last, return a message to the callback method

File Name – image-model.js

var db=require('../database');
module.exports={ 
  storeImage:function(inputValues,callback){

  // check unique email address
var sql='SELECT * FROM images WHERE image_name =?';
db.query(sql,inputValues.image_name,function (err, data, fields) {
 if(err) throw err
 if(data.length>1){
     var msg = inputValues.image_name + " is already exist";
 }else{ 
    // save users data into database
    var sql = 'INSERT INTO images SET ?';
   db.query(sql, inputValues, function (err, data) {
      if (err) throw err;
   });
  var msg = inputValues.image_name+ "is uploaded successfully";
 }
 return callback(msg)
})
  }
}

3. Upload Image to the Folder

You also have to upload images to the folder through the Middleware. So, configure the following steps – –

Create a folder middlewares
Also, create a file store-image.js in the middlewares
Write the file upload script in the store-image.js
Declare image folder path in the code cb(null, 'image_folder_path')

File Name – image-moddleware.js

var multer = require('multer');

module.exports.image={
    storage:function(){
        var storage = multer.diskStorage({
        destination: function (req, file, cb) {
          cb(null, 'public/images/')
        },
        filename: function (req, file, cb) {
            cb(null, file.originalname)
        }
      })
      return storage;
},
allowedImage:function(req, file, cb) {
    // Accept images only
    if (!file.originalname.match(/\.(jpg|JPG|jpeg|JPEG|png|PNG|gif|GIF)$/)) {
        req.fileValidationError = 'Only image files are allowed!';
        return cb(new Error('Only image files are allowed!'), false);
    }
    cb(null, true);
}}

4. Upload & store image

You have to upload & store images in both folder & database through the controller. So, configure the following steps –

Include Multer module require('multer')
Also, include a middleware file using require('../middlewares/image-middleware')
Include a model file using require('../models/image-model')
Create a method imageUploadForm to display image upload form
Even create another method storeImage to upload & store image

File Name – image-controller.js

var multer  = require('multer');
var imageMiddleware= require('../middlewares/image-middleware');
var imageModel= require('../models/image-image');

module.exports={
    imageUploadForm:function(req,res){
        res.render('upload-form');
     },
     storeImage:function(req,res){
        var upload = multer({
                    storage: imageMiddleware.image.storage(), 
                    allowedImage:imageMiddleware.image.allowedImage 
                    }).single('image');
        upload(req, res, function (err) {
           if (err instanceof multer.MulterError) {
              res.send(err);
           } else if (err) {
              res.send(err);
           }else{
              // store image in database
               var imageName = req.file.originalname;
               var inputValues = {
                  image_name: imageName
               }
             // call model
             imageModel.storeImage(inputValues, function(data){
               res.render('upload-form',{alertMsg:data})
             })
              
           }
           
        })
        
     }
}

5. Create Routes to upload & store Image

Create two routes to upload & store image using the following steps –

Include a controller file using require('../controllers/image-controller')
Create a route /store-image with the GET method to display the image upload form
Also, Create another route /store-image with the POST method to upload & store Images. This route will be called when you submit the image upload form.

File Name – image-route.js

const express = require('express');
const router = express.Router();
const imageController= require('../controllers/image-controller');

router.get('/store-image',imageController.imageUploadForm);

router.post('/store-image',imageController.storeImage);

module.exports = router;

6. Include Routes in root file app.js

You must include the route file in app.js. If you forget it then your Node app will not work.

File Name – app.js

var imageRouter = require('./routes/image-route');
app.use('/', imageRouter);

7. Run Node.js App to upload & store Image

First of all, Enter the following URL in your web browser and press the ENTER key. then, an image upload form will be shown. After that, select images from your computer and submit the button.

http://localhost:3000/store-image

When you submit the image upload form then /store-image will be called with POST method and images will be saved in the folder & its path will be stored in the database successfully.

Comments

Yeeun Song says
July 16, 2021 at 12:19 pm
Hi perfect explanation and nicely written codes. Thank you so much!
Cesare says
December 1, 2021 at 4:43 pm
Hi, very easy to follow, thank you. Can you do one to download images from MySql?
- Md Nurullah says
  March 12, 2022 at 4:30 pm
  Hello Cesare,
  Keep continue to visit my website, I will definitely share to download images asap