How to store images in MySQL database using node JS?

Hello all: I am working on this node, express, mysql program that needs to upload an image and store its name in MySQL. The image gets uploaded just fine, but I cannot do the insert into MySQL. Sometimes I do not get an error, but the file name is not inserted into the database. Other times, I get the error that filename is undefined.

Here is my index.html:

    This is the webpage
    
    
    
        Select file

        
        

        
        Submit
    
 

This is my server.js:

//setting up an express server.

//load express
const express = require('express');
//load multer
const multer = require('multer');
const app = express();
const path = require('path');
// const fs = require('fs');


//mysql stuff
mysql = require('mysql')
bodyParser = require("body-parser");
app.set('views', __dirname + '/views');
app.set('view engine', 'ejs');
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());

var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : 'pass123!',
  database : 'images_db'
});

connection.connect();
 
global.db = connection;

//endf of mysql stuff
const PORT = process.env.PORT || 3001;
app.use(express.static(path.join(__dirname, "public")));

const storage = multer.diskStorage({
  destination: function (req, file, cb) {
    cb(null, 'images/')
  },
  filename: function (req, file, cb) {
    cb(null, file.originalname)
    // cb(null, file.fieldname + '-' + Date.now() + path.extname(file.originalname));
  },  
});

const limits = {
    fileSize : 4000000
}

//fileFilter function controls which files should be uploaded. req = request being made. file = contains file info. cb = callback function to tell multer when we are done filtering the file. send back an error message to the client with cb.
const fileFilter =(req, file, cb) => {
  //if the file is not a jpg, jpeg, or png file, do not upload it multer; reject it.
  if (!file.originalname.match(/\.(jpg|jpeg|png)$/)) {
      return cb(new Error('File must be of type JPG, JPEG, or PNG and nore more than 2MB in size'))
  }
  //undefined = nothing went wrong; true = that is true, nothing went wrong, accept the upload.
  cb(undefined, true)
}

//set up the multer middleware
const upload = multer({
    storage: storage,
    limits: limits,
    fileFilter: fileFilter
    // filename: filename
  })

// ROUTES
app.get('/',function(req,res){
    res.sendFile(__dirname + '/index.html');
  });

  //app.use('/image', express.static('./upload')); // shared uploaded folder with users so users can access the profile images though image folder path


//upload image post route: localhost:port/upload
  app.post("/upload",upload.single('upload'), (req, res) => {
 

    // res.send();
    //mysql stuff
    // var sql = "INSERT INTO `file`(`name`, `type`, `size`) VALUES ('" + req.file.filename + "', '"+req.file.mimetype+"', '"+req.file.size+"')";
    var sql = "INSERT INTO `file`(`name`) VALUES ('" + req.file.filename + "')";
 
    var query = db.query(sql, function(err, result) {
       console.log('inserted data');
    });
    message = "Successfully! uploaded";
    //res.render('index',{message: message, status:'success'});
    //end of mysql stuff
     
    res.redirect('./');
   
}), (error, req, res, next) => {
    res.status(400).res.send("You have successfully uploaded the file!");
    // res.redirect('/');
}



app.listen(PORT, () => {
    console.log(`App listening on port ${PORT}!`);
})

Today we are going to explore the Express-FileUpload NPM package as we create a super simple user profile card and integrate our card with a MySQL database to retain the record.

This article has been updated. – Removed Connection Pool

Express-Handlebars code has been updated from 5.3.4 to 6.0.1 – 6.0.2. You can read the full Express-Handlebars Migration 6.0.1, 6.0.2 article here.

If you are looking for an in-depth explanation please watch the full video tutorial.

What you need:

To complete this project you will need a MySQL Database & Node installed. I usually use XAMPP for local development, but you can use whatever you wish as long as the database is MySQL of course.

Create a new project

To create a new Node.js project all you have to do is to create a new project folder “user-management-system” and then run the Command line or PowerShell in the same directory. Once you do that to initialise a new project simply put the following command:

npm install express express-handlebars express-fileupload mysql

This will initialise a new project for you and it’s going to ask you a few questions about your project. The most important one is to give your package a name and then you can just keep pressing enter until the installation is over.

Project Structure

Let’s create the following folders and files, leaving node_modules, readme.md, package-lock and package-json as that should have been automatically generated by now.

You can structure your project the way you like. I am just going to keep it simple and add everything into our app.js file

📂 node_modules
📂 public
 📂 css
  📜 main.css
  📜 main.scss
 📂 img
  🖼 default.jpg
📂 views
  📂 layouts
   📜 main.hbs
 📜 index.hbs
📜 README.md
⚓ .env
🌍 app.js
📜 package-lock.json
📜 package-json

Let’s create our app.js file and each block of code will be explained with comments.

(I didn’t use the connection pool correctly. Update coming soon – code still works)

const express = require('express');
const exphbs = require('express-handlebars'); // updated to 6.0.X
const fileUpload = require('express-fileupload');
const mysql = require('mysql');

const app = express();
const port = process.env.PORT || 5000;

// default option
app.use(fileUpload());

// Static Files
app.use(express.static('public'));
app.use(express.static('upload'));

// Templating engine
// app.engine('hbs', exphbs({ extname: '.hbs' })); // v5.3.4
// app.set('view engine', 'hbs'); // v5.3.4

// Update to 6.0.X
const handlebars = exphbs.create({ extname: '.hbs',});
app.engine('.hbs', handlebars.engine);
app.set('view engine', '.hbs');


// Connection Pool
var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'userprofile'
});


app.get('', (req, res) => {
    connection.query('SELECT * FROM user WHERE id = "1"', (err, rows) => {
      if (!err) {
        res.render('index', { rows });
      }
    });
});


app.post('', (req, res) => {
  let sampleFile;
  let uploadPath;

  if (!req.files || Object.keys(req.files).length === 0) {
    return res.status(400).send('No files were uploaded.');
  }

  // name of the input is sampleFile
  sampleFile = req.files.sampleFile;
  uploadPath = __dirname + '/upload/' + sampleFile.name;

  console.log(sampleFile);

  // Use mv() to place file on the server
  sampleFile.mv(uploadPath, function (err) {
    if (err) return res.status(500).send(err);

      connection.query('UPDATE user SET profile_image = ? WHERE id ="1"', [sampleFile.name], (err, rows) => {
        if (!err) {
          res.redirect('/');
        } else {
          console.log(err);
        }
      });
    });
});

app.listen(port, () => console.log(`Listening on port ${port}`));

In our Views -> Layouts folder we need to create our Handlebars template. The {{{body}}} is where our index.hbs page will render:

  
  
  
  Document
  


  

    {{{body}}}
  

Now let’s create our index.hbs page, with our form and a loop to display the record from the database:

How can we store image in MySQL database?

How to insert image in MySQL database using Javascript?

How do I store an image in node JS?

How to upload file in MySQL database using Node js?