Node.js RestApi File Upload (Download) Multiple Files/Images to MySQL/PostgreSQL database – Multer + Sequelize + Ajax

Nodejs RestAPI File Upload to MySQL-PostgreSQL with Multer Sequelize and Ajax Frontend

In the tutorial, I will introduce how to create a “Node.js RestApi File Upload” (and Node.js Rest Api File Download) Application with Ajax client to upload/download single or multiple files/images to MySQL/PostgreSQL database with Sequelize engine an Multer middleware.

– We use Express framework to create a Nodejs RestAPIs.
– We use Multer middleware for uploading images/files to Nodejs server.
– We use Sequelize ORM to store file data to database MySQL/PostgreSQL.

Related posts:


Here is a to-do-list:

– I draw a full diagram architecture of Nodejs RestAPI Upload Files
– I configure Sequelize ORM and Multer for Uploading files
– I build Nodejs Express RestApi to upload/download files
– I implement fontend with Jquery Ajax RestAPI client.

Youtube Video Guide – Nodejs Upload Files

Overview – How to build Node.js RestApi File Upload to MySQL/PostgreSQL

upload-download-file-image-to-mysql-postgresql-database-using-multer-sequelize
Upload Download File Image to Mysql/Postgresql using Multer/Sequelize

For handling the uploading file from rest clients to Node.js Express application, we use multer library. And we use Sequelize library to do CRUD operations with MySQL or PostgreSQL database.

Here is the structure of our project:

Nodejs Project structure
Nodejs Project structure

Goal:

Upload Web Page Result
Upload Web Page Result

We create 2 functions:

  • Upload Files
    • Upload Single File
    • Upload Multiple Files
  • Download Files
    • List out uploaded files
    • Download File
Download Files
Download Files

Multer

Multer is a node.js middleware for handling multipart/form-data, which is primarily used for uploading files. It is written on top of busboy for maximum efficiency.

NOTE: Multer will not process any form which is not multipart (multipart/form-data).

Install multer by cmd:

$ npm install --save multer

Multer adds a body object and a file or files object to the request object. The body object contains the values of the text fields of the form, the file or files object contains the files uploaded via the form.

Basic usage example:

Don’t forget the enctype="multipart/form-data" in your form.

<form action="/profile" method="post" enctype="multipart/form-data">
  <input type="file" name="avatar" />
</form>
let express = require('express')
let multer  = require('multer')
let upload = multer({ dest: 'uploads/' })
 
let app = express()
 
app.post('/profile', upload.single('avatar'), function (req, res, next) {
  // req.file is the `avatar` file
  // req.body will hold the text fields, if there were any
})
 
app.post('/photos/upload', upload.array('photos', 12), function (req, res, next) {
  // req.files is array of `photos` files
  // req.body will contain the text fields, if there were any
})

Implementation – How to code it?

Create Nodejs Application

– Create a folder Nodejs-Upload-Download-Files, cd to the folder. Init a nodejs project with cmd npm init. Then installing the needed dependencies:

npm install await sequelize express multer mysql2 --save

– After all, check the package.json file:

{
  "name": "nodejs-upload-download-files",
  "version": "1.0.0",
  "description": "Nodejs Upload Download Files/Images to MySQL/PostgreSQL  database",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "Nodejs"
  ],
  "author": "https:/loizenai.com",
  "license": "ISC",
  "dependencies": {
    "await": "^0.2.6",
    "express": "^4.17.1",
    "multer": "^1.4.2",
    "mysql2": "^2.1.0",
    "sequelize": "^5.21.12"
  }
}

Build Nodejs/Express Upload/Download RestAPIs Backend

Configuration

Sequelize configuration

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more.

For interacting with database, we need to configure Sequelize ORM.
– Create a db.config.js file as below:

const env = require('./env.js');
 
const Sequelize = require('sequelize');
const sequelize = new Sequelize(env.database, env.username, env.password, {
  host: env.host,
  dialect: env.dialect,
  operatorsAliases: false,
 
  pool: {
    max: env.max,
    min: env.pool.min,
    acquire: env.pool.acquire,
    idle: env.pool.idle
  }
});

const db = {};
 
db.Sequelize = Sequelize;
db.sequelize = sequelize;
 
db.files = require('../models/file.model.js')(sequelize, Sequelize);
 
module.exports = db;

– For MySQL database, Create a env.js file as below:

const env = {
  database: 'loizenaidb',
  username: 'root',
  password: '12345',
  host: 'localhost',
  dialect: 'mysql',
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};
 
module.exports = env;

– For PostgreSQL database, creating a env.js file as below:

const env = {
  database: 'loizenai',
  username: 'postgres',
  password: '123',
  host: 'localhost',
  dialect: 'postgres',
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};
 
module.exports = env;
Multer configuration

For memory storage, we configure Multer as below:

const multer = require('multer');

var storage = multer.memoryStorage()
var upload = multer({storage: storage});

module.exports = upload;

Create Sequelize Models

We create a File Sequelize model as below code:

module.exports = (sequelize, Sequelize) => {
	const File = sequelize.define('file', {
	  type: {
			type: Sequelize.STRING
	  },
	  name: {
			type: Sequelize.STRING
	  },
	  data: {
			type: Sequelize.BLOB('long')
	  }
	});
	
	return File;
}

The created File model has 3 defined attributes:

  • type has String type
  • name has String type
  • data has Blob type

Create Express Router

We create a router file file.router.js with 4 restapis:

  • /api/file/upload is used to upload a single file
  • /api/file/multiple/upload is used to upload multiple files
  • /api/file/info is used to get information of uploaded files
  • /api/file/:id is used to download a file with given id
let express = require('express');
let router = express.Router();
let upload = require('../config/multer.config.js');
 
const fileWorker = require('../controllers/file.controller.js');

let path = __basedir + '/views/';

router.get('/', (req,res) => {
    res.sendFile(path + "index.html");
});

router.post('/api/file/upload', upload.single("file"), fileWorker.uploadFile);
router.post('/api/file/multiple/upload', upload.array('files', 4), fileWorker.uploadMultipleFiles);
 
router.get('/api/file/info', fileWorker.listAllFiles);
 
router.get('/api/file/:id', fileWorker.downloadFile);
 
module.exports = router;

Implement Express Controller

Now we implement upload/download controllers for above router. All are in .js file:

  • Upload File
    • exports.uploadFile = (req, res) is used to upload a single file
    • exports.uploadMultipleFiles = async (req, res) is used to upload multiple file
  • Download File
    • exports.listAllFiles = (req, res) is used to list out all files’ information
    • exports.downloadFile = (req, res) is used to download a file from MySQL via a given id
Upload Controllers
Upload Single File

Coding:

var stream = require('stream');
var await = require('await')

const db = require('../config/db.config.js');
const File = db.files;


exports.uploadFile = (req, res) => {
	File.create({
		type: req.file.mimetype,
		name: req.file.originalname,
		data: req.file.buffer
	}).then(file => {
		console.log(file);

		const result = {
			status: "ok",
			filename: req.file.originalname,
			message: "Upload Successfully!",
			downloadUrl: "http://localhost:8080/api/file/" + file.dataValues.id,
		}

		res.json(result);
	}).catch(err => {
		console.log(err);

		const result = {
			status: "error",
			error: err
		}
		res.json(result);
	});
}

We use created Sequelize File model to persistence a single uploaded file to database. After saving done, we construct a returned javascript object with 4 attributes {status, filename, message, downloadUrl} to return back to called client.

Upload Multiple Files

Coding:

exports.uploadMultipleFiles = async (req, res) => {
	const messages = [];

	for (const file of req.files) {
		const uploadfile = await File.create({
								type: file.mimetype,
								name: file.originalname,
								data: file.buffer
							});

        // It will now wait for above Promise to be fulfilled and show the proper details
        console.log(uploadfile);

	    if (!uploadfile){
			const result = {
				status: "fail",
				filename: file.originalname,				
				message: "Can NOT upload Successfully",
			}

			messages.push(result);
		} else {
			const result = {
				status: "ok",
				filename: file.originalname,
				message: "Upload Successfully!",
				downloadUrl: "http://localhost:8080/api/file/" + uploadfile.dataValues.id,
			}

			messages.push(result);
		}
	}

	return res.json(messages);
}

We use created Sequelize model File to save each uploaded file to database. For after each saving, we contruct a returned javascript object and add it to a returned message array.

We use async and wait functions to handle the multiple file uploading.

Download Controllers

For downloading files from Node.js server, we create 2 functions;

  • exports.listAllFiles = (req, res) is used to list out all uploaded files’ information
  • exports.downloadFile = (req, res) is used to download an uploaded file via given id
Get all uploaded files’ information
exports.listAllFiles = (req, res) => {
	File.findAll({attributes: ['id', 'name']}).then(files => {

		const fileInfo = [];

		console.log(files);
	  
		for(let i=0; i<files.length; i++){
			fileInfo.push({
				filename: files[i].name,
				url: "http://localhost:8080/api/file/" + files[i].dataValues.id
			})
		}

	    res.json(fileInfo);
	}).catch(err => {
		console.log(err);
		res.json({msg: 'Error', detail: err});
	});
}

The function uses the defined Sequelize model File to retrieve all the records in database with 2 attributes: id and name. After processing, it constructs a result in an array of objects with 2 attributes for each: filename and url then returns back to called client.

Download File

Coding:

exports.downloadFile = (req, res) => {
	File.findByPk(req.params.id).then(file => {
		var fileContents = Buffer.from(file.data, "base64");
		var readStream = new stream.PassThrough();
		readStream.end(fileContents);
		
		res.set('Content-disposition', 'attachment; filename=' + file.name);
		res.set('Content-Type', file.type);

		readStream.pipe(res);
	}).catch(err => {
		console.log(err);
		res.json({msg: 'Error', detail: err});
	});
}

The download file function uses defined sequelize model File to find a database’s record with a given id and use stream to build an attachment file and returns back to client.

Create server.js

We define a nodejs/express server that listens on 8080 port:

const express = require('express');
const app = express();
 
global.__basedir = __dirname;
 
const db = require('./app/config/db.config.js');
  
// force: true will drop the table if it already exists
db.sequelize.sync({force: true}).then(() => {
  console.log('Drop and Resync with { force: true }');
}); 

let router = require('./app/routers/file.router.js');
app.use(express.static('resources'));
app.use('/', router);

// Create a Server
const server = app.listen(8080, function () {
 
  let host = server.address().address
  let port = server.address().port
 
  console.log("App listening at http://%s:%s", host, port); 
})

Build JQuery Ajax Client

We build an HTML page for uploading single file, multiple files, list out of uploaded files’ information and downloading uploaded files with Jquery Ajax.

Create Upload/Download html page

Html Upload Download View
Html Upload Download View

Coding:

<!DOCTYPE html>
<html lang="en">
<head>
<title>Upload Download File Examples</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js"></script>
<script type="text/javascript" src="/static/js/uploaddownloadfiles.js"></script>
</head>
<body>
	<div class="container">
		<div class="row">
			<div class="col-sm-7" style="background-color:#e6fffa; padding:10px; border-radius:3px">
				<h3>Upload Single File</h3>
				<form id="uploadSingleFileForm">
					<div class="form-group">
						<label class="control-label" for="uploadfile">Choose a File:</label>
						<input type="file" class="form-control" 
								placeholder="Choose a upload file" name="file" required></input>
					</div>
					<button type="submit" class="btn btn-danger" id="btnUploadSingleFileSubmit">Submit</button>
				</form>
				<div id="response" style="display:none">
				</div>
			</div>
		</div>
		<hr>		
		<div class="row">
			<div class="col-sm-7" style="background-color:#e6fffa; padding:10px; border-radius:3px">
				<h3>Upload Multiple Files</h3>
				<form id="uploadMultipleFilesForm">
					<div class="form-group">
						<label class="control-label" for="uploadfiles">Choose Files:</label>
						<input type="file" class="form-control" 
								placeholder="Choose upload files" name="files" multiple required></input>
					</div>
					<button type="submit" class="btn btn-danger" id="btnUploadMultipleFilesSubmit">Submit</button>
				</form>
				<div id="responses" style="display:none">
				</div>
			</div>
			
		</div>
		<hr>
		<div id="uploadedfiles" class="row">
			<div class="col-sm-7">
				<button type="button" class="btn btn-primary" id="btnGetFiles">All Files</button>
				<div id="allfiles">
				</div>
			</div>
		</div>
	</div>
</body>
</html>

Implement Upload/Download Javascript

We create 3 Ajax functions for upload/download files to nodejs server:

  • $("#uploadSingleFileForm").submit(function(evt) is used to upload a single file
  • $("#uploadMultipleFilesForm").submit(function(evt) is used to upload multiple files
  • $( "#btnGetFiles").click(function() is used to list out the uploaded files’ information

Coding:

/**
 * Copyright by https://loizenai.com
 * Author: loizenai.com 
 */

$(document).ready(function() {
	
	/**
	 * Upload single file to SpringBoot 
	 * at RestAPI: /api/upload/file/single
	 */
	$("#uploadSingleFileForm").submit(function(evt) {
		evt.preventDefault();
		
		let formData = new FormData($(this)[0]);
		
		$.ajax({
			url : '/api/file/upload',
			type : 'POST',
			data : formData,
			async : false,
			cache : false,
			contentType : false,
			enctype : 'multipart/form-data',
			processData : false,
			success : function(response) {
				$("#response").empty();
				if(response.status !== "error"){
					let displayInfo = response.filename + " : " + response.message + "
"; $("#response").append(displayInfo); let downloadLink = response.downloadUrl; let downloadAt = "    -> Download File: " + "" + downloadLink + ""; $("#response").append(downloadAt); // add some css $("#response").css("display", "block"); $("#response").css("background-color", "#e6e6ff"); $("#response").css("border", "solid 1px black"); $("#response").css("border-radius", "3px"); $("#response").css("margin", "10px"); $("#response").css("padding", "10px"); }else{ $("#response").css("display", "none"); let error = response.error; alert(error); } }, error: function(e){ alert("Fail! " + e); } }); return false; }); /** * Upload Multiple Files to SpringBoot RestAPI */ $("#uploadMultipleFilesForm").submit(function(evt) { evt.preventDefault(); let formData = new FormData($(this)[0]); $.ajax({ url : '/api/file/multiple/upload', type : 'POST', data : formData, async : false, cache : false, contentType : false, enctype : 'multipart/form-data', processData : false, success : function(response) { $("#responses").empty(); let displayInfo = "
    "; for(let i=0; i" + response[i].downloadUrl + ""; displayInfo += "
    " + downloadAt; } displayInfo += ""; } $("#responses").append(displayInfo + "
"); $("#responses").css("display", "block"); // add some css $("#responses").css("background-color", "#e6e6ff"); $("#responses").css("border", "solid 1px black"); $("#responses").css("border-radius", "3px"); $("#responses").css("margin", "10px"); $("#responses").css("padding", "10px"); }, error: function(e){ alert("Fail! " + e); } }); return false; }); /** * Get all uploaded files and download-links */ $( "#btnGetFiles").click(function() { $.get('/api/file/info', function (response, textStatus, jqXHR) { // success callback let files = ""; console.log(files); $("#allfiles").append(files); console.log("--end--"); // add some css $("#uploadfiles").css("background-color", "#e6e6ff"); $("#uploadfiles").css("border", "solid 1px black"); $("#uploadfiles").css("border-radius", "3px"); $("#uploadfiles").css("margin", "10px"); $("#uploadfiles").css("padding", "10px"); }); }); })

Run & Check results

Testcase with Rest-Client

Testcase 1 – Upload Single File
Test Case - Upload Single File
Test Case – Upload Single File

Check database’s record:

Check MySQL database - Testcase 1 - Upload a Single File
Check MySQL database – Testcase 1 – Upload a Single File
Testcase 2 – Upload Multiple Files
Testcase 2 - Upload Multiple File to Nodejs
Testcase 2 – Upload Multiple File to Nodejs

Check MySQL database’s records:

Testcase 2 - Check MySQL database
Testcase 2 – Check MySQL database
Testcase 3 – Retrieve all Files’ information
Testcase 3 - Retrieve all uploaded files' information
Testcase 3 – Retrieve all uploaded files’ information
Testcase 4 – Download an uploaded file
Testcase 4 - Download a file
Testcase 4 – Download a file

Testcase with HTML view + Ajax

Upload Single File
Testcase 1 – Ajax Client – Upload Single File – Successfully[/caption]

Test case 1 - Ajax Client - Upload Single File
Test case 1 – Ajax Client – Upload Single File
Upload Multiple Files
Testcase 2 - Upload Multiple File - Successfully
Testcase 2 – Upload Multiple File – Successfully
Test case 2 - Ajax Client - Upload Multiple File - network logs
Test case 2 – Ajax Client – Upload Multiple File – network logs
Download a uploaded file
Testcase 3 - Ajax Client - Download File - network logs
Testcase 3 – Ajax Client – Download File – network logs
Testcase 3 - Ajax Client - Download File - successfully
Testcase 3 – Ajax Client – Download File – successfully

Sourcecode

I include a running sourcecode for the tutorial Nodejs Upload/Download Multiple Files/Images. All implemented features of coding:

  • Build Nodejs/Express Upload/Download RestAPIs Backend
    • Create Sequelize Models
    • Create Express Router
    • Implement Express Upload/Download Controller
  • Build JQuery Ajax Client
    • Implement Html Upload/Download view
    • Implement Upload/Download Ajax client

Nodejs-Upload-Download-Files

– Github Sourcecode:

Nodejs-Upload-Download-Files – Github Sourcecode

12 thoughts on “Node.js RestApi File Upload (Download) Multiple Files/Images to MySQL/PostgreSQL database – Multer + Sequelize + Ajax”

Leave a Reply

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