Connecting PostgreSQL with Node.js in the PERN Stack

Introduction

In the previous article, we successfully set up our PERN stack development environment by installing:

• Node.js

• PostgreSQL

• React

• Express.js

Now it is time to move into real backend development by connecting PostgreSQL with Node.js.

This is one of the most important steps in the PERN stack because databases are responsible for storing and managing application data.

In this article, we will learn:

• How PostgreSQL works with Node.js

• Creating a database

• Creating tables

• Connecting Express server with PostgreSQL

• Writing SQL queries

• Performing basic CRUD operations

---

Understanding Database Connectivity

In the PERN stack:

• React handles frontend UI

• Express and Node.js handle backend logic

• PostgreSQL stores data permanently

The backend communicates with PostgreSQL using the pg package.

Flow of Data

React → Express API → PostgreSQL Database

Whenever a user submits data:

1. React sends request

2. Express receives request

3. Node.js processes logic

4. PostgreSQL stores or retrieves data

5. Response returns to frontend

---

Step 1: Install PostgreSQL Driver

Inside your server folder install the PostgreSQL package:

npm install pg

The pg package allows Node.js to communicate with PostgreSQL.

---

Step 2: Create a PostgreSQL Database

Open:

• pgAdmin
  or

• PostgreSQL terminal (psql)

Create a database named:

CREATE DATABASE pern_app;

---

Step 3: Create a Table

Now connect to the database and create a table.

Example: Student table

CREATE TABLE students(
    student_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(50),
    age INTEGER
);

---

Understanding Table Structure

Column	Description
student_id	Unique ID
name	Student name
course	Course name
age	Student age

---

Step 4: Create Database Connection File

Inside the server folder create:

db.js

Add the following code:

const { Pool } = require('pg');

const pool = new Pool({
    user: 'postgres',
    password: 'your_password',
    host: 'localhost',
    port: 5432,
    database: 'pern_app'
});

module.exports = pool;

---

Understanding the Connection Code

Pool

The Pool object manages multiple database connections efficiently.

---

Connection Parameters

Parameter	Purpose
user	PostgreSQL username
password	PostgreSQL password
host	Database server
port	PostgreSQL port
database	Database name

---

Step 5: Setup Express Server

Open your index.js file.

Add this code:

const express = require('express');
const cors = require('cors');
const pool = require('./db');

const app = express();

app.use(cors());
app.use(express.json());

app.get('/', (req, res) => {
    res.send('Database Connected Successfully');
});

app.listen(5000, () => {
    console.log('Server running on port 5000');
});

---

Step 6: Test Database Connection

Now run the server:

npx nodemon index.js

If everything is correct, your server will run without errors.

---

Step 7: Insert Data into Database

Now let’s create our first API to insert student data.

Add this route inside index.js:

app.post('/students', async (req, res) => {
    try {
        const { name, course, age } = req.body;

        const newStudent = await pool.query(
            'INSERT INTO students(name, course, age) VALUES($1, $2, $3) RETURNING *',
            [name, course, age]
        );

        res.json(newStudent.rows[0]);

    } catch (err) {
        console.error(err.message);
    }
});

---

Understanding the Insert Query

INSERT INTO students(name, course, age)
VALUES($1, $2, $3)
RETURNING *

• $1, $2, $3 are placeholders

• Prevents SQL injection attacks

• RETURNING * returns inserted data

---

Step 8: Test API Using Thunder Client or Postman

Request Type

POST

URL

http://localhost:5000/students

JSON Body

{
    "name": "Shivam",
    "course": "BCA",
    "age": 20
}

---

Step 9: Fetch Data from Database

Now create API to get all students.

app.get('/students', async (req, res) => {
    try {

        const allStudents = await pool.query(
            'SELECT * FROM students'
        );

        res.json(allStudents.rows);

    } catch (err) {
        console.error(err.message);
    }
});

---

Step 10: Get Single Student

app.get('/students/:id', async (req, res) => {
    try {

        const { id } = req.params;

        const student = await pool.query(
            'SELECT * FROM students WHERE student_id = $1',
            [id]
        );

        res.json(student.rows[0]);

    } catch (err) {
        console.error(err.message);
    }
});

---

Step 11: Update Student Data

app.put('/students/:id', async (req, res) => {
    try {

        const { id } = req.params;
        const { name, course, age } = req.body;

        await pool.query(
            'UPDATE students SET name=$1, course=$2, age=$3 WHERE student_id=$4',
            [name, course, age, id]
        );

        res.json('Student Updated');

    } catch (err) {
        console.error(err.message);
    }
});

---

Step 12: Delete Student Data

app.delete('/students/:id', async (req, res) => {
    try {

        const { id } = req.params;

        await pool.query(
            'DELETE FROM students WHERE student_id = $1',
            [id]
        );

        res.json('Student Deleted');

    } catch (err) {
        console.error(err.message);
    }
});

---

Understanding CRUD Operations

CRUD stands for:

Operation	Meaning
Create	Insert data
Read	Fetch data
Update	Modify data
Delete	Remove data

CRUD operations form the foundation of almost every web application.

---

Why Async/Await is Used

Database operations take time.

Using:

async/await

helps Node.js handle operations efficiently without freezing the server.

---

Common Errors and Solutions

1. Password Authentication Failed

Check:

• PostgreSQL password

• Username

• Database name

---

2. Database Does Not Exist

Run:

CREATE DATABASE pern_app;

---

3. Table Does Not Exist

Check spelling and create table again.

---

4. Port Already in Use

Change port inside:

app.listen(5000)

---

Real-World Uses of Database APIs

These APIs are used in:

• Student management systems

• Test series platforms

• E-commerce websites

• Banking systems

• Hospital management software

---

Best Practices

Use Environment Variables

Instead of writing passwords directly:

password: 'your_password'

Use .env files for security.

---

Validate User Input

Always check:

• Empty fields

• Invalid values

• SQL injection prevention

---

Organize Project Structure

Create separate folders for:

• Routes

• Controllers

• Database

• Middleware

This makes large projects manageable.

---

Conclusion

You have now successfully connected PostgreSQL with Node.js and created your first backend APIs using the PERN stack.

You learned:

• Database connection

• SQL queries

• CRUD operations

• API creation

• Express integration

This is the foundation of backend development in modern web applications.