So you've built a Node.js Express API, but storing data in an in-memory array just isn't cutting it anymore. To build a real, stateful application, you need a database. **PostgreSQL** (often called Postgres) is a powerful, open-source, and highly popular relational database that's perfect for Node.js applications.

This tutorial will guide you through every step of connecting your Node.js app to a PostgreSQL database. We will use the popular `node-postgres` (or `pg`) library to perform full **CRUD** (Create, Read, Update, Delete) operations.

Prerequisites

  • You have Node.js and `npm` installed.
  • You have a running PostgreSQL server. For local development, we recommend Postgres.app for Mac or the official Windows/Linux installers.
  • You are familiar with building a basic Express server.

Step 1: Project and Database Setup

First, set up a new Node project and install the necessary dependencies.

mkdir node-postgres-api
cd node-postgres-api
npm init -y
npm install express pg dotenv
  • express: Our web server framework.
  • pg: The Node.js driver for PostgreSQL.
  • dotenv: To manage our database credentials securely.

Next, connect to your PostgreSQL server using `psql` or a GUI tool and run the following SQL commands to create a database and a table for our users.

CREATE DATABASE mydatabase;
\c mydatabase

CREATE TABLE users (
    ID SERIAL PRIMARY KEY,
    name VARCHAR(30),
    email VARCHAR(30)
);

Step 2: Configure the Database Connection

We'll store our sensitive database credentials in an environment file. Create a `.env` file in your project root:

# .env file
DB_USER=your_postgres_user
DB_PASSWORD=your_postgres_password
DB_HOST=localhost
DB_PORT=5432
DB_DATABASE=mydatabase

Now, create a file called `db.js` to manage our connection pool. A connection pool is much more efficient than opening and closing a new connection for every query.

// db.js
require('dotenv').config();
const { Pool } = require('pg');

const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_DATABASE,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT,
});

module.exports = {
  query: (text, params) => pool.query(text, params),
};

This module exports a single `query` function that we can use throughout our application to interact with the database.

Step 3: Build the Express Server and CRUD Routes

Now let's create our main application file, `index.js`, and build the API endpoints.

// index.js
const express = require('express');
const db = require('./db'); // Import our database query function
const app = express();
const port = 3000;

app.use(express.json());

// GET all users
app.get('/users', async (req, res) => {
  try {
    const { rows } = await db.query('SELECT * FROM users');
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET a single user by ID
app.get('/users/:id', async (req, res) => {
  const { id } = req.params;
  try {
    const { rows } = await db.query('SELECT * FROM users WHERE id = $1', [id]);
    if (rows.length === 0) {
      return res.status(404).send('User not found');
    }
    res.json(rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// POST a new user
app.post('/users', async (req, res) => {
  const { name, email } = req.body;
  try {
    const { rows } = await db.query(
      'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
      [name, email]
    );
    res.status(201).json(rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// PUT (update) a user
app.put('/users/:id', async (req, res) => {
  const { id } = req.params;
  const { name, email } = req.body;
  try {
    const { rows } = await db.query(
      'UPDATE users SET name = $1, email = $2 WHERE id = $3 RETURNING *',
      [name, email, id]
    );
    if (rows.length === 0) {
        return res.status(404).send('User not found');
    }
    res.json(rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// DELETE a user
app.delete('/users/:id', async (req, res) => {
  const { id } = req.params;
  try {
    const { rows } = await db.query('DELETE FROM users WHERE id = $1 RETURNING *', [id]);
    if (rows.length === 0) {
      return res.status(404).send('User not found');
    }
    res.send(`User with ID ${id} deleted.`);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.listen(port, () => {
  console.log(`Server listening at http://localhost:${port}`);
});

Important Security Note: Parameterized Queries

Notice the use of `$1`, `$2`, etc. in our SQL commands. This is called a **parameterized query**. The `pg` library takes our array of values (e.g., `[name, email]`) and safely inserts them into the query. This is the **single most important thing** you can do to prevent SQL Injection attacks. Never use template strings like `` `SELECT * FROM users WHERE id = ${id}` `` to build queries with user input!

Step 4: Running and Testing the API

You're all set! Start your server from the terminal:

node index.js

Now, open Postman and test your endpoints:

  • **POST** to http://localhost:3000/users with a JSON body like {"name": "Rohit Patil", "email": "rohit@example.com"} to create a new user.
  • **GET** from http://localhost:3000/users to see a list of all users.
  • **GET** from http://localhost:3000/users/1 to fetch the user with ID 1.
  • **PUT** to http://localhost:3000/users/1 with a new name or email to update the user.
  • **DELETE** from http://localhost:3000/users/1 to remove the user.

Conclusion

Congratulations! You have successfully connected a Node.js Express application to a PostgreSQL database. You've learned how to set up a secure connection pool, write clean and safe queries for all CRUD operations, and build a robust API with persistent data storage. This is a massive step up from in-memory data and is the foundation for building almost any real-world backend service.