Integration of PostgreSQL and node.js

This is tutorial of making an application that simulates transaction software. In this post, I would cover how to integrate PostgreSQL and node.js. Here the tools and skills we are going to use:

• express
• PostgreSQL

Middlewares

• cors: connects server-side with client-side
• pg: connects server with database

Prerequisite for this tutorial

• basic SQL
• basic HTTP REQUEST

Setup

Installation

mkdir server
cd server
npm i express pg cors
touch index.js

1. Create a server directory inside of project folder
2. Install express, pg, cors
3. create index.js file where there is server-side code.

Basic backend setup

We are going to use express as a backend framework, this is pretty much standard for node.js.

index.js

const express = require('express') // import express
const app = express() // instantiate express module
app.listen(5000, () => { // specify localhost port to listen to
  console.log('server started on port 5000')
})

To run the server at http://localhost:5000/ type nodemon index on your terminal, to use nodemon you will need to npm i -g nodemon.

Create Database

Create a SQL file in the server directory

database.sql

CREATE DATABASE bank;
CREATE TABLE transaction(
  transaction_id SERIAL PRIMARY KEY,
  amount INTEGER,
  payment_method VARCHAR,
  number_of_items INTEGER
);

Login to psql on command line, create database then connect to the database

psql -U postgres
\c [dbname]

• \l to list all db
• \c connect to the db
• \dt to list tables in the db
• \d to list rows in the table

create a table by copying and pasting the SQL file to command line like so:

Database configuration

create db.js in the server directory

db.js

const Pool = require('pg').Pool // import pg pool instance
// pg database configuration
const pool = new Pool({
  user: 'postgres', // username 'postgres' by default
  password: '1srtg845', // password of psql
  host: 'localhost',  // host name
  port: 5432, // port number, 5432 by default
  database: 'bank', // database name
});
// export the configuration
module.exports = pool;

import the configuration to index.js

index.js

const cors = require('cors') // this is req.body
const pool = require('./db') // importing pg config
app.use(cors()) // connecting to client-side
app.use(express.json()) // this allows us to access req.body in json

By importing pg configuration, we can now send a query to the query!

File directories

At this point, you will have these files and directories in your server folder. Ignore eslintrc.json and prettier.config.js

• node_modules
• databse.sql
• db.js
• index.js
• package.json
• package-lock.json

directories

Sending a POST request to a server

POST HTTP request

Sending POST request will store data coming from the browser. The basic syntax of post request is app.post(path, callback)

Testing POST request

You can test to see if the request works okay in Postman. We will send the body to the path /transactions, make sure the text type to be in JSON and method is POST.

If you click enter with the body, you will get the response like so:

What you need is just a “rows” from the json, so you can respond with res.json(payment_method.rows[0]).

Sending GET request to a server

You can get all the transaction information or just specific one using its id.

Getting list of transactions

an example of get method handling in express

Getting a list of transactions is easy, just select all rows by SELECT * FROM <table_name>.

Getting a single transaction with id

an example of get method handling in express

Notice there is :id as a parameter at the end of path in the get method above, in the query, we will add conditional clause, WHERE. SELECT * FROM <table_name> WHERE <column> = $1 followed by array of value [id] which plugged into $<number>.

Note that it is not necessary to add RETURNING * in query when it comes to GET method.

Sending a PUT request to a server

When you update and delete a item, the server needs to recognise which one you would like to update or delete, so id parameter should be involved.

Query for Updating

UPDATE <table> SET <column_to_update> = $1 WHERE <column> = $2,
[value corresponding to $1, value corresponding to $2]

Sending a DELETE request to a server

Query for Deleting

'DELETE FROM <table> WHERE <column> = $1', [id]

So now, we are able to make crud functionality! Thanks for reading!