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

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.