Skip to content

All topics  /  Node.js

How to Store Image in MySQL Database Using Node.js Express?

Node.js ·

Teams applying “How to Store Image in MySQL Database Using Node.js Express?” in a production project can use the official product overview to record implementation, review and debugging time, then compare that effort with tests and shipped functionality instead of treating activity as performance by itself.

Before copying the example into a live application, confirm version-specific behaviour with the official MySQL website and test it against the project’s actual database and runtime.

Hello Friends,

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

Multer is an npm package that makes it easy to handle file uploads. It does it very efficiently, thus it is quite popular. In this article, you will learn how to use Multer to handle multipart/form data using Node. js, Express, and MySQL database.

Step 1: Create Node Express js App


Execute the following command on the terminal to create the node js app:

mkdir my-app
cd my-app
npm init -y

Step 2: Create Table in MySQL Database

Execute the following SQL query to create a table in your database:

CREATE TABLE users_file(id INT(10) NOT NULL AUTO_INCREMENT, file_src TEXT, PRIMARY KEY(id));

Step 3: Install express multer body-parser mysql dependencies

Execute the following command on the terminal to express multi ejs body-parser MySQL dependencies:

npm install express multer body-parser mysql

Step 4: Create image Upload Form

Create a form with a `file input` element that allows us to choose the file and a button to submit the form; So create an index.html file and add the following code into it:

<!DOCTYPE html>
<html lang="en">
    <head>
        <title>How to store image in MySQL database using Node js Express - Nicesnippets.com</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
    </head>
    <body>
        <h1>How to store image in MySQL database using Node js - Nicesnippets.com</h1>
        <form action="/" enctype="multipart/form-data" method="post">
            <input type="file" name="image" accept='image/*' >
            <input type="submit" value="Upload">
        </form>  
    </body>
</html>

Make sure your form must have enctype="multipart/form-data"attribute and the form method should be posted.

Step 5: Create Server.js File

Create server.js file and import express multi body-parser MySQL dependencies in server.js; as shown below:

const express = require('express')
const app = express()
const bodyparser = require('body-parser')
const mysql = require('mysql')
const multer = require('multer')
const path = require('path')

 
//use express static folder
app.use(express.static("./public"))

 
// body-parser middleware use
app.use(bodyparser.json())
app.use(bodyparser.urlencoded({
    extended: true
}))

 
// Database connection
const db = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "test"
})

 
db.connect(function (err) {
    if (err) {
        return console.error('error: ' + err.message);
    }
    console.log('Connected to the MySQL server.');
})

 
//! Use of Multer
var storage = multer.diskStorage({
    destination: (req, file, callBack) => {
        callBack(null, './public/images/')     // './public/images/' directory name where save the file
    },
    filename: (req, file, callBack) => {
        callBack(null, file.fieldname + '-' + Date.now() + path.extname(file.originalname))
    }
})

 
var upload = multer({
    storage: storage
});

 
//! Routes start
//route for Home page
app.get('/', (req, res) => {
    res.sendFile(__dirname + '/index.html');
});

 
//@type   POST
//route for post data
app.post("/post", upload.single('image'), (req, res) => {
    if (!req.file) {
        console.log("No file upload");
    } else {
        console.log(req.file.filename)
        var imgsrc = 'http://127.0.0.1:3000/images/' + req.file.filename
        var insertData = "INSERT INTO users_file(file_src)VALUES(?)"
        db.query(insertData, [imgsrc], (err, result) => {
            if (err) throw err
            console.log("file uploaded")
        })
    }
});

 
//create connection
const PORT = process.env.PORT || 3000
app.listen(PORT, () => console.log(`Server is running at port ${PORT}`))

Note that; The following route in server.js will upload an image into MySQL database and node js express app directory:

//@type   POST
//route for post data
app.post("/post", upload.single('image'), (req, res) => {
    if (!req.file) {
        console.log("No file upload");
    } else {
        console.log(req.file.filename)
        var imgsrc = 'http://127.0.0.1:3000/images/' + req.file.filename
        var insertData = "INSERT INTO users_file(file_src)VALUES(?)"
        db.query(insertData, [imgsrc], (err, result) => {
            if (err) throw err
            console.log("file uploaded")
        })
    }
});

Step 6: Start Node Express Js image Upload App Server

You can use the following command to start uploading images in MySQL using node js express app server:

//run the below command
npm start

After running this command open your browser and hit 

http://127.0.0.1:3000/

More on this topic