Node.js Upload Image to Postgresql Example

Tutorial “Node.js Upload Image to Postgresql Example – Node.js RestApi File Upload (Download) Multiple Files/Images to PostgreSQL database – Multer + Sequelize + Ajax”

In the tutorial, I will introduce how to create a “Node.js Upload Image to Postgresql Example” (and Node.js Rest Api File Download) Application with Ajax client to upload/download single or multiple files/images to 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.

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 – Node.js Upload Image to Postgresql Example

Overview – Node.js Upload Image to Postgresql Example

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:

Goal:

We create 2 functions:

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

Nodejs Express Upload Image with Multer – Node.js Upload Image to Postgresql Example

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
})

Create Nodejs Express Application – Node.js Upload Image to Postgresql Example

– 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"
  }
}

Nodejs Express Sequelize Configuration – Node.js Upload Image to Postgresql Example

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;

Nodejs Express 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;

Nodejs Express 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

Nodejs Express Create RestAPI Router – Node.js Upload Image to Postgresql Example

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;

Nodejs Express Implement RestAPI Controller – Node.js Upload Image to Postgresql Example

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 Single Image Files – Node.js Upload Image to Postgresql Example

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.

Nodejs Express Upload Multiple Image 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.

Nodejs Express Download Image Controller

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 Image Files’s 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.

Nodejs Express Download Image 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.

Nodejs Express 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); 
})

Nodejs Express Create Upload/Download Html Page

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>

Nodejs Expres Implement Upload/Download Image with Ajax Javascript – Node.js Upload Image to Postgresql Example

In the tutorial “Node.js Upload Image to Postgresql Example”, 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 = "
";
			
			for(let i=0; i" 
									+ "    Link -> "
									+ response[i].url 
									+ ""
			}
			
			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");
		});
	});
})

Nodejs Express Testcase with Rest-Client

1. Testcase 1 – Upload Single File:

Check database’s record:

2. Testcase 2 – Upload Multiple Files:

Check MySQL database’s records:

3. Testcase 3 – Retrieve all Files’ information:

4. Testcase 4 – Download an uploaded file:

Read More

Nodejs Express Testcase with HTML view + Ajax Upload Image

1. Nodejs Express Upload Single Image File – Node.js Upload Image to Postgresql Example:

2. Nodejs Express Upload Multiple Image Files:

3. Nodejs Express Download a uploaded Image File – Node.js Upload Image to Postgresql Example:

Sourcecode – Node.js Upload Image to Postgresql Example

I include a running sourcecode for the tutorial Node.js Upload Image to Postgresql Example. 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