Upload/store images in MySQL using Node.js, Express & Multer

In this tutorial, I will show you how to upload/store image in MySQL database using Node.js with the help of Multer and Express.

Related Posts:
How to upload multiple files in Node.js
Upload & resize multiple images in Node.js using Express, Multer, Sharp
How to upload/store images in MongoDB using Node.js, Express & Multer

More Practice:
Node.js Rest APIs example with Express, Sequelize & MySQL
Node.js & JWT – Token Based Authentication & Authorization example


Node.js upload/store image in MySQL overview

We’re gonna make a Node.js application like this:

upload-image-mysql-node-js-express-example-ui

Click on Submit button, the file will be uploaded to MySQL database:

upload-image-mysql-node-js-express-example-database

We also store the uploaded image in upload folders before saving its data to MySQL.
Then to check MySQL image data, we save the result data in tmp folder.

upload-image-mysql-node-js-express-example-resources

Project Structure

Let’s look at our project directory tree:

upload-image-mysql-node-js-express-example-project-structure

db.config.js exports configuring parameters for MySQL connection & Sequelize.
models/index.js: uses configuration above to initialize Sequelize, models/image.model.js for Sequelize model Image.
views/index.html: contains HTML form for user to upload images.
routes/web.js: defines routes for endpoints that is called from views, use controllers to handle requests.
controllers:

  • home.js returns views/index.html
  • upload.js handles upload & store images with middleware function.

middleware/upload.js: initializes Multer Storage engine and defines middleware function.
server.js: initializes routes, runs Express app.

Setup Node.js modules

Open command prompt, change current directory to the root folder of our project.
Install Express, Multer, Sequelize, Mysql2 with the following command:

npm install express multer sequelize mysql2

The package.json file will look like this:

{
  "name": "upload-multiple-files-mysql",
  "version": "1.0.0",
  "description": "Node.js upload images to MySQL database",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "node",
    "upload",
    "image",
    "mysql"
  ],
  "author": "bezkoder",
  "license": "ISC",
  "dependencies": {
    "express": "^4.17.1",
    "multer": "^1.4.2",
    "mysql2": "^2.1.0",
    "sequelize": "^5.21.7"
  }
}

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.images = require("./image.model.js")(sequelize, Sequelize);

module.exports = db;

We’re gonna define Image model in the next step.

Define the Sequelize Model

In models folder, create image.model.js file like this:

module.exports = (sequelize, DataTypes) => {
  const Image = sequelize.define("image", {
    type: {
      type: DataTypes.STRING,
    },
    name: {
      type: DataTypes.STRING,
    },
    data: {
      type: DataTypes.BLOB("long"),
    },
  });

  return Image;
};

This Sequelize Model represents images table in MySQL database. These columns will be generated automatically: id, type, name, data, createdAt, updatedAt.

The data field has BLOB type. A BLOB is binary large object that can hold a variable amount of data.

Create middleware for uploading & storing image

Inside middleware folder, create upload.js file with the following code:

const multer = require("multer");

const imageFilter = (req, file, cb) => {
  if (file.mimetype.startsWith("image")) {
    cb(null, true);
  } else {
    cb("Please upload only images.", false);
  }
};

var storage = multer.diskStorage({
  destination: (req, file, cb) => {
    cb(null, __basedir + "/resources/static/assets/uploads/");
  },
  filename: (req, file, cb) => {
    cb(null, `${Date.now()}-bezkoder-${file.originalname}`);
  },
});

var uploadFile = multer({ storage: storage, fileFilter: imageFilter });
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 images to pass.

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 Images

controllers/upload.js

const fs = require("fs");

const db = require("../models");
const Image = db.images;

const uploadFiles = async (req, res) => {
  try {
    console.log(req.file);

    if (req.file == undefined) {
      return res.send(`You must select a file.`);
    }

    Image.create({
      type: req.file.mimetype,
      name: req.file.originalname,
      data: fs.readFileSync(
        __basedir + "/resources/static/assets/uploads/" + req.file.filename
      ),
    }).then((image) => {
      fs.writeFileSync(
        __basedir + "/resources/static/assets/tmp/" + image.name,
        image.data
      );

      return res.send(`File has been uploaded.`);
    });
  } catch (error) {
    console.log(error);
    return res.send(`Error when trying upload images: ${error}`);
  }
};

module.exports = {
  uploadFiles,
};

Now look at the uploadFiles function:
– First we get and check file upload from req.file.
– Next we use Sequelize model create() method to save an Image object (type, name, data) to MySQL database.
data is gotten from uploads folder (that middleware function stored the image).
– If the process is successful, we save write the image data to tmp folder.
– To read/write data, we use fs.readFileSync('/path/to/file') and fs.writeFileSync('/path/to/file', image.data) functions of Node.js fs module.

Create Controller for the view

controllers/home.js

const path = require("path");

const home = (req, res) => {
  return res.sendFile(path.join(`${__dirname}/../views/index.html`));
};

module.exports = {
  getHome: home
};

Create View for uploading image

In views folder, create index.html file with the HTML and Javascript code as below:

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta http-equiv="X-UA-Compatible" content="ie=edge" />
    <title>Node.js upload images</title>
    <link
      rel="stylesheet"
      href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
    />
    <style>
      div.preview-images > img {
        width: 30%;
      }
    </style>
  </head>
  <body>
    <div class="container">
      <div class="row">
        <div class="col-sm-8 mt-3">
          <h4>Node.js upload images - bezkoder.com</h4>

          <form
            class="mt-4"
            action="/upload"
            method="POST"
            enctype="multipart/form-data"
          >
            <div class="form-group">
              <input
                type="file"
                name="file"
                id="input-files"
                class="form-control-file border"
              />
            </div>
            <button type="submit" class="btn btn-primary">Submit</button>
          </form>
        </div>
      </div>
      <hr />
      <div class="row">
        <div class="col-sm-12">
          <div class="preview-images"></div>
        </div>
      </div>
    </div>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.bundle.min.js"></script>
    <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
    <script>
      $(document).ready(function() {
        let imagesPreview = function(input, placeToInsertImagePreview) {
          if (input.files) {
            let filesAmount = input.files.length;
            for (i = 0; i < filesAmount; i++) {
              let reader = new FileReader();
              reader.onload = function(event) {
                $($.parseHTML("<img>"))
                  .attr("src", event.target.result)
                  .appendTo(placeToInsertImagePreview);
              };
              reader.readAsDataURL(input.files[i]);
            }
          }
        };
        $("#input-files").on("change", function() {
          imagesPreview(this, "div.preview-images");
        });
      });
    </script>
  </body>
</html>

For HTML part, we create a form with following elements:

  • action="/upload"
  • method="POST"
  • enctype="multipart/form-data"

You also need to notice the input tag with the name="file" attribute that we use in the middleware.

The jQuery script shows preview of the chosen images.
We also use Bootstrap to make the UI more comfortable to read.

Define routes

In routes folder, define routes in web.js with Express Router.

const express = require("express");
const router = express.Router();
const homeController = require("../controllers/home");
const uploadController = require("../controllers/upload");
const upload = require("../middleware/upload");

let routes = (app) => {
  router.get("/", homeController.getHome);

  router.post("/upload", upload.single("file"), uploadController.uploadFiles);

  return app.use("/", router);
};

module.exports = routes;

There are 2 routes:
– GET: Home page for the upload form.
– POST "/upload" to call the upload controller. This is for action="/upload" in the view.

The single() function with the parameter is the name of input tag (in html view: <input type="file" name="file">) will store the single file in req.file.

Create Express app server

Finally, we create an Express server.

server.js

const express = require("express");
const app = express();
const db = require("./src/models");
const initRoutes = require("./src/routes/web");

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 = 3000;
app.listen(port, () => {
  console.log(`Running at localhost:${port}`);
});

In the code above, we 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 result

First we need to create tmp and uploads folder with the path resources/static/assets.
On the project root folder, run this command: node src/server.js

The console shows:

Running at localhost:3000

Open your browser with url: http://localhost:3000.

Conclusion

Today we’ve learned how to upload and store image in MySQL database using express, multer & sequelize modules. We also built a Rest API with routes for showing the upload form and uploading images with middleware.

Happy learning! See you again.

Further Reading

Upload & resize multiple images in Node.js using Express, Multer, Sharp
How to upload/store images in MongoDB using Node.js, Express & Multer

Source Code

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

22 thoughts to “Upload/store images in MySQL using Node.js, Express & Multer”

  1. Hi thanks a lot for the tutorial. Can you also show instruction on how to make upload file request from Angular Client using Angular Service? Thanks a lot for your help.

    1. Actually I figured it out myself, but I’m struggling on downloading the file. Can you make a tutorial on downloading file from Blob data ?

  2. Thank you for this article, it’s explained really good.
    A question regarding the association, whenever I want to connect the entity of Images with another entity, example:

    User and Image entities, the column which is supposed to say user_id at Image entity won’t appear.
    It does appear without the the imgUpload controller, I’m guessing it is because the controller?

    Any solution to this?

      1. Thank you from the bottom of my heart! You’ve been such a great help, I love your articles, beautifully explained.

  3. I have loved all the tutorials you put out so far. Much appreciated. Just a quick question what is the reason for having a temp and a uploads folder when their both holding the same files?

  4. Hi, thanks for this. Can you show how to upload the image to cloudinary and save the response in MySQL database using same?

  5. Hi I’m having trouble when coming to testing everything.
    I get the error Error: Cannot find module ‘./src/models’.
    Does anyone know how to fix it,
    Thanks.

      1. I figuired it out. Server.js is suppose to be in the root folder of the site not the src folder. So move it one folder up and change the start command to node server.js.

        Thanks
        Robert

  6. Hi! Thanks for it !
    I would like to use REACT for the front end part and i must say i am struggling to figure it out…i’am new to it. Thanks a lot !

Leave a Reply

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