How to connect Nodejs with PostgreSQL

  • September 19, 2019
  • Suraj Roy

There are many databases available today which is used as per the requirement of the application, And as a web developer requirement may come to work on an application using PostgreSQL database. So the first thing comes in our mind that how the databases will be connected and basic queries with it. For the task pg package of NPM will  be used. So here, In the below sample application, I will import a CSV file into PostgreSQL database.

 

PostgreSQL Database

PostgreSQL, commonly referred to as Postgres, is a free and open-source relational database management system. You might be familiar with a few other similar database systems, such as MySQL, Microsoft SQL Server, or MariaDB, which compete with PostgreSQL.

 

Let's Get Started

Let's have a look on project folder structure below:

Nodejs  Postgres folder structure

 

1. Create Project folder and package.json file inside project folder

 

 
{
"name": "backend",
"version": "1.0.0",
"description": "",
"main": "src/app.js",
"scripts": {
"start": "nodemon src/app.js"
},
"author": "shekhar",
"license": "ISC",
"dependencies": {
"async": "^2.6.1",
"body-parser": "^1.18.3",
"csvtojson": "^2.0.8",
"express": "^4.16.4",
"glob": "^7.1.3",
"nodemon": "^1.18.4",
"path": "^0.12.7",
"pg": "^7.8.0",
"uuid": "^3.3.2"
}
}
 

 

2. Create app.js inside src folder file and add below code inside it.

 

 
let express = require('express')
let app = express();
let path = require("path");
let glob = require('glob');
let bodyParser = require('body-parser');
let port = process.env.PORT || 3001;
 
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());
 
// view engine setup
 
let initRoutes = () => {
glob("./routes/*.js", { cwd: path.resolve("./src/") }, (err, routes) => {
if (err) {
return;
}
routes.forEach((routePath) => {
require(routePath).default(app);
});
});
}
 
initRoutes(app);
 
app.listen(port, () => {
console.log("Server is running on port " + port);
});
 

 

2. Create utils folder(inside src) and a file db.js for database connection and queries.

 

 
const { Client } = require("pg");
 
const client = new Client({
user: "postgres",
host: "localhost",
database: "newdb",
password: "root",
port: 5432
});
client.connect();
 
const getClient = (req) => new Promise((resolve, reject) => {
client.query(`SELECT clientid FROM clients WHERE clientid='${req.clientId}'`)
.then(client => resolve(client.rowCount))
.catch(err => reject(err))
})
 
const createOrder = (req) => new Promise((resolve, reject) => {
client.query(`INSERT INTO orders (orderId, clientId,request,duration) VALUES ('${req.orderId}','${req.clientId}','${req.requests}','${req.duration}')`)
.then(client => resolve(client))
.catch(err => reject(err))
})
 
const getOrder = (req) => new Promise((resolve, reject) => {
client.query(`SELECT orderId FROM orders WHERE orderId='${req.orderId}'`)
.then(client => resolve(client.rowCount))
.catch(err => reject(err))
})
 
module.exports = {
getClient,
createOrder,
getOrder
};
 

 

3. Create routes folder(inside src) and a file named client.js

 

 
let clientTest = require('../controller/clientTest');
 
exports.default = (app) => {
// root path
app.get('/', (req, res) => {
return res.status(200).send({
'Please use following routes': {
'Read CSV File': 'http://localhost:3001/readCSVFile',
},
'method': 'get'
});
});
 
// route to upload CSV
app.get('/readCSVFile', clientTest.readCSVFile);
 
return (app)
}
 

 

4. Create a folder named controller(inside src) and file name clientTest.js

 

const csv = require("csvtojson");
const db = require("../utils/db");
const async = require("async");
 
/**
* Method to read CSV File
* @param {*} req
* @param {*} res
*/
let readCSVFile = (req, res) => {
csv()
// reading static file in given location
.fromFile('public/requests.csv')
.then(requests => {
async.eachSeries(requests, async (request) => {
// reading each row of CSV Sheet
let clientRequests = await db.getClient({ clientId: request.clientId });
// checking if client exists in DB or not
if (clientRequests) {
let order = await db.getOrder({ orderId: request.orderId });
if (!order) await db.createOrder(request)
}
}, (err) => {
if (err) res.send("Something went wrong")
// return as CSV successfully saved
else res.status(200).send("Data imported successfully");
})
}).catch(() => res.send("Something went wrong"));
}
 
module.exports = {
readCSVFile
}

 

5. Run the App

Now after completing all the required task run the app with nodemon src/app.js and type the URL over browser "http:localhost:3001/readCSVFile"Once the API cll from the url then all the data available inside requests.csv file(inside the ic folder) will be imported into the PostgreSQL database.

Output on browser:

nodejs-postgres-data-imported

Data imported in db:

Nodejs Postgres db image

Note:

Find Complete working source code with db schema after downloading code from below.

 

 

Conclusion:

Connecting PostgreSQL and querying data with nodejs is very easy. It is very similar to the connection with Mysql.

If you are new to Node.js then find Nodejs Sample Application to start the app for enterprise-level application

Let me know your thoughts over the email demo.jsonworld@gmail.com. I would love to hear them and If you like this article, share with your friends.

Thank You!

You can download complete code from here. Download Code


Find other similar demo here: