How to Connect PostgreSQL with NodeJs Application?

Daniel J Abraham
Mar 14, 2019 · 2 min read

Learn how to connect PostgreSQL with NodeJs, While you are using PostgreSQL as database and NodeJs as backend, you need PostgreSQL database packages to connect with nodejs.

There are various packages available but most popular and well documented is node-postgres pg.

Let’s start from scratch.

What’s needed

  • Make sure you have postgresql installed on machine and pgAdmin — postgresql management tool
  • Make sure you have node.js installed

Database Connections on Files

Create Database and use the credentials at connectionStrings in your project files.

const { Client } = require('pg');
var connectionString = "postgres://postgres:postgres@localhost:5432/database";
const client = new Client({
connectionString: connectionString
});

Start your project

  • Create a folder for this project
  • Create a package.json file in your project folder
{
"name": "sample",
"version": "1.0.0",
"description": "Connecting NodeJs & PostgreSQL",
"main": "app.js",
"dependencies": {
"express": "^4.16.4",
"pg": "^7.8.2"
}
}
  • Now add app.js inside your project folder
const express = require('express');
const { Client } = require('pg');
const connectionString = 'postgres://postgres:postgres@localhost:5432/database';
const client = new Client({
connectionString: connectionString
});
client.connect();var app = express();app.set('port', process.env.PORT || 4000);app.get('/', function (req, res, next) {
client.query('SELECT * FROM Employee where id = $1', [1], function (err, result) {
if (err) {
console.log(err);
res.status(400).send(err);
}
res.status(200).send(result.rows);
});
});
app.listen(4000, function () {
console.log('Server is running.. on Port 4000');
});
  • After addingpackage.json and app.js files Create a table & insert some default value in PostgreSQL
CREATE TABLE Employee(
id int not null,
name text not null,
rollnumber int not null
);
INSERT INTO Employee values(1,'John',1001);
  • Type npm install in terminal/console in the source folder where package.json is located
npm install
  • Type node app.js in terminal/console in the source folder where app.js is located
node app.js

Connection String

Executing Queries

Documented is available node-postgres(Doc) pg

Project source Code

To download source code, Here https://github.com/dannibla/nodejs-postgresql

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade