Upload and Store Images in MySQL using Node.Js, Express, Express-FileUpload & Express-Handlebars
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 init
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:
<!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">
{{#each rows}}
{{#if this.profile_image}}
<img class="card__image" src="{{this.profile_image}}" alt="User Profile">
{{else}}
<img class="card__image" src="/img/default.jpg" alt="User Profile">
{{/if}}
<h1 class="card__title">{{this.name}}</h1>
<div class="card__job">{{this.job_title}}</div>
<p class="card__about">
{{this.description}}
</p>
<button type="button" class="btn btn-primary">View Profile</button>
{{/each}}
</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:
Download
Thank you for reading this article. Please consider subscribing to my YouTube Channel.
More Resources:
Author
Hi! I’m Raddy. I am a web developer, often making YouTube Videos, Blog Posts and side projects. Enquiries: [email protected]
