Upload and Store Images in MySQL using Node.Js, Express, Express-FileUpload & Express-Handlebars

By Raddy in NodeJs ·

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.

If you are looking for a 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 init

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
πŸ“œ 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.

// Require NPM Packages
const express = require('express');
const exphbs = require('express-handlebars');
const fileUpload = require('express-fileupload');
const mysql = require('mysql');

// Create express server and set the port to 5000
const app = express();
const port = process.env.PORT || 5000;

// default option for Express-FileUpload
app.use(fileUpload(
// This is where you add your fileupload settings. Refer to the documentation
));

// Static Files
// Static files help us to easily include things such as CSS, Images and JavaScript from the root directory
app.use(express.static('public'));
app.use(express.static('upload'));

// Templating engine
// We are setting up Handlebars as our view engine and changing the file extinction from .handlebars to .hbs for short.
app.engine('hbs', exphbs({ extname: '.hbs' }));
app.set('view engine', 'hbs');

// Connection Pool
// Create a MySQL connection pool and set a connection limit
const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'userprofile'
});

// Check if you can connect to MySQL
pool.getConnection((errconnection) => {
  if (err) throw err; // not connected
  console.log('Connected!');
});

// Our GET router for the homepage and a simple SELECT MySQL Query
// We also render the page by doing res.render
app.get('', (reqres) => {
  pool.getConnection((errconnection) => {
    if (err) throw err; // not connected
    console.log('Connected!');

    connection.query('SELECT * FROM user WHERE id = "1"', (errrows) => {
      // Once done, release connection
      connection.release();

      if (!err) {
        res.render('index', { rows });
      }

    });
  });
});

// This is where we upload a new image, we move it to the uploads folder with the mv function and we also record that to our database.
app.post('', (reqres) => {
  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);

    pool.getConnection((errconnection) => {
      if (err) throw err; // not connected
      console.log('Connected!');

      connection.query('UPDATE user SET profile_image = ? WHERE id ="1"', [sampleFile.name], (errrows) => {
        // Once done, release connection
        connection.release();

        if (!err) {
          res.redirect('/');
        } else {
          console.log(err);
        }

      });
    });

    // res.send('File uploaded!');
  });
});



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

That’s it. The clean code is available on the GitHub link below with less explanation comments.

Download

Thank you for reading this article. Please consider subscribing to my YouTube Channel.

Leave a Reply

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