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
Dockerize Node.js Express and MySQL example – Docker Compose


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
Dockerize Node.js Express and MySQL example – Docker Compose

Source Code

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

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

  1. I am trying to implement file upload with node and react but I have not been able to upload the file, you will have an example to see what I am doing wrong

  2. Hi, I am trying to change script code in the HTML to change the image dimensions before displaying them on the screen. Any insight on how to go about doing that?

  3. Thank you so much this tutorial was so helpful.
    I have an issue while trying to connect to a pre-exisiting database, that the id field is inserted with null value, I tried that following while configuring the field in image.mode.js :

    id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true,
    omitNull: true,
    allowNull: false,
    }

    but it’s still inserting null value, can you please help?

  4. hi how can I upload and download file using react and node,js and mysql but files save in project specified directory and it path save in mysql table?

  5. hello bezkoder you are really awesome, this helping a lot while doing my project.
    I have a question is if I want to change database table to another I created, and delete createdAt, updatedAt columns.
    I read code many times, but I can’t figure it out.
    Can you help me to point out where the config?
    Thanks a lot

    1. I found the table name here sequelize.define(“image”, { … });
      but why it will plus ‘s’ whiling complete create the table

  6. bezkoder thank you for these tutorials! You have answered a lot of questions when it comes to me learning how to use stacks.

    If I were to add another page or component that only a “mod” and higher should have access to, what would be the best way to go about that addition?

  7. Hey! There’s one thing I don’t understand. Are the images stored in the upload folder or at the db?

    1. Hi, currently both of them, you can delete the images in uploads folder right after it is written to database.

  8. 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 !

  9. 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

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

  11. 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?

      1. Simply, would like to +1 shall’s comment, much appreciated and had same question. Thanks for the answer, Bezkoder 🙂

  12. 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.

  13. 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 ?

Comments are closed to reduce spam. If you have any question, please send me an email.