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 fileSubmit
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-jsonLet’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: