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 at the project folder structure below:
1. Create Project folder and package.json file inside the 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 the src folder file and add the 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:
Data imported in db:
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!
