Raddy Website Design & Development Tutorials

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

By Raddy in NodeJs ·

In this article, we will explore the Express-FileUpload NPM package by creating a super simple user profile card and integrate our card with a MySQL database to retain the record.

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 project all you have to do is to create a new project folder, let’s call it “upload-files” and then run the following command below in Command line or PowerShell. Make sure that you CD to your project directory. 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.

This will create a new package.json file for you.

Dependencies Installation

There are a few dependencies that we need to install to get started. Here is the list:

[x] express
[x] express-ejs-layouts
[x] mysql

Let’s do that by opening the terminal / powershell and install the dependencies by typing the following command:

npm i express express-ejs-layouts express-fileupload mysql

Now let’s install Nodemon which will help us restart the server every time we make a change. Note that Nodemon is a development dependency so we have to install it by adding the –save-dev flag.

npm i --save-dev nodemon

Your packages.json file should look similar to this:

{
  "name": "nodejs-user-profile",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "nodemon app.js"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "ejs": "^3.1.6",
    "express": "^4.17.1",
    "express-ejs-layouts": "^2.5.0",
    "express-fileupload": "^1.2.1",
    "mysql": "^2.18.1"
  },
  "devDependencies": {
    "nodemon": "^2.0.7"
  }
}

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
📂 upload
📂 views
  📂 layouts
   📜 main.ejs
 📜 index.ejs
📜 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.

const express = require('express');
const expressLayouts = require('express-ejs-layouts');

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.use(expressLayouts);
app.set('layout', './layouts/main');
app.set('view engine', 'ejs');

// Templating Engine

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

// Our GET router for the homepage and a simple SELECT MySQL Query
// We also render the page by doing res.render
app.get('', (req, res) => {
  
    connection.query('SELECT * FROM user WHERE id = "1"', (error, rows) => {
      // Once done, release connection
      if (error) throw error;

      if (!error) {
        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);
        }

      });

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

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

In our Views -> Layouts folder we need to create our EJS template. The <%- body -%> is where our index.ejs page will render:

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Document</title>
  <link rel="stylesheet" href="/css/main.css">
</head>
<body>
  <div class="wrapper">
    <%%- body -%%>
  </div>
</body>
</html>

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

<form action="/" method="POST" encType="multipart/form-data">
  <h3>Upload Profile Photo</h3>
  <input type="file" name="sampleFile" accept="image/*" />
  <input type="submit" class="btn btn-primary">
</form>

<div class="card">

  <%% for(var i=0; i < rows.length; i++) { %%>

    <img class="card__image" src="<%%- rows[i].profile_image %%>" loading="lazy" alt="User Profile">

    <h1 class="card__title"><%%- rows[i].name %%></h1>
    <div class="card__job"><%%- rows[i].job_title %%></div>
    <p class="card__about">
      <%%- rows[i].description %%>
    </p>
    <button type="button" class="btn btn-primary">View Profile</button>

  <%% } %%>

</div>

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

Demo Video Loop

Demo of uploading an image and saving the record to a MySQL Database:

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

More Resources:

  1. Frederick Thavhana says:

    Its says ejs layouts but it looks like it is using hbs?

    1. Raddy says:

      For some reason, I had handlebars in packages.json, but it’s all EJS. Thanks for this, Frederick I’ve updated the article. I need to upload this to Github as well.

  2. Jacques says:

    You’re awesome dude! Seriously!

    1. Raddy says:

      Thanks, Jacques! I appreciate it! 👊😎

  3. Samuelbi11 says:

    Thank you a lot, buddy! This is what I just needed! 😎💪

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.