How to Connect to PostgreSQL in a NodeJS Application

Introduction

The versatility of the open source database management system, PostgreSQL, is appreciated by novice and seasoned developers alike, especially when it comes to the seamless integration of scripting languages such as JavaScript. With NodeJS, in particular, the applications you build for the network can be scalable. What’s more, NodeJS saves computer resources by putting itself into sleep mode when it’s not busy.

Now, there is NodeJS Express. It is lightweight and ideal for developing powerful applications for mobile and the web. Learn how to connect PostgreSQL NodeJS Express server API. This tutorial will show you how so that you can reap additional benefits of the JavaScript runtime that is event-driven.

Prerequisites

  • Download and install for your OS, PostgresSQL.

  • Download and install for your OS, NodeJS. For Linux Mint, other Ubuntu distros, and Ubuntu, before you install, first run a repositories package update with the sudo-apt command:

sudo apt update
  • Next, install NodeJS with this command:
sudo apt-get install nodejs
  • Use Homebrew to install NodeJS on a macOS. Go to a terminal window and use this command:
brew install node
  • When finished, check that the installation was successful with this command:
node -v
  • You should see a similar result like this one:
v12.10.0

Perform a PostgreSQL installation

  • Input this command to install the database management system, PostgreSQL:
sudo apt install postgresql
  • When you’re done, log in:
sudo su - postgres
  • Enter the PostgreSQL interface command line:
psql

Make a new PostgreSQL NodeJS project

To make a PostgreSQL NodeJS project, you will need to get the right module to connect PostgreSQL NodeJS. In a few minutes, we will use the Node Package Manager (npm) to accomplish this.

Express Server Setup

  • Make a directory for your sample:
mkdir node-sample-postgres
  • Navigate to the directory with the cd command:
cd node-sample-postgres
  • Use the npm command to make a file package.json:
npm init -y
  • The next command npm installs both the Express server and the library pg for PostgreSQL and NodeJS.
npm install express pg
  • Alternatively, use the command i to do the installation:
npm i pg express

>NOTE: A sub-folder should show up in your directory. It will have all of the required dependencies within the package.json. The node_modules will have the needed dependencies as well to connect PostgreSQL NodeJS.

Construct a sample database in PostgreSQL

Make a new sample database in PostgreSQL to test out the examples in this tutorial.

  • At the command line, create a new database like this:
CREATE DATABASE db_name;
  • Connect the database with the \c command:
\c db_name;

Construct a table in the PostgreSQL database

  • Use the example syntax to make a table with fields:
CREATE TABLE tbl_name(column_name + DATA TYPE + constraints IF any);
  • Here are some details for the table:
CREATE TABLE department(dept_id INT NOT NULL PRIMARY KEY, dept_name text, dept_email text);

Add the PostgreSQL database table values

  • Insert the columns’ corresponding values inside the table:
INSERT INTO tbl_name(column1, column2, column3) VALUES(1, 2, 3);
  • Here are some columns with values that you can use:
INSERT INTO department(dept_id, dept_name, dept_email)VALUES(1, 'HR', 'hr-dept@gmail.com');

Make a NodeJS server file and connect

  • Construct a JavaScript server file to connect PostgreSQL NodeJS. It must have the .js extension. For example, connect.js
const express = require('express')
const bodyParser = require('body-parser')
const app = express()
const port = 3000

app.use(bodyParser.json())
app.use(
bodyParser.urlencoded({
extended: true,
})
)
  • Make a request using GET for a JSON response like this:
app.get('/', (request, response) => { response.json({ info: 'Request Accepted!' }) })
  • The app will listen to the port you set and successfully connect to it like this:
app.listen(port, () => {
console.log(`Connection Sucess! on port ${port}.`)
})
  • Try out the server connection with this command in PostgreSQL:
node connect.js
  • Check it out. Go to your server’s port localhost:3000 by opening a tab in your browser.

Conclusion

In this tutorial, you learned how to create a sample database and table, and then make a successful connection to the server. Developing powerful mobile and web APIs fast and complication-free is the main advantage of NodeJS. There are so many other benefits too. When you connected PostgreSQL NodeJS Express, you added an extra functional layer that doesn’t obstruct the common features of NodeJS that you are used to. Thus, you unleashed an enhancement that gives you even more application development flexibility.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.