Database Integration PostgreSQL

Last Updated : Oct 2026

Connecting Express to PostgreSQL with pg (Connection Pool)

To get started with real code, install the required packages in your Express project:

Step 1: Install Dependencies

npm install pg dotenv

Step 2: Environment Configuration (.env)

Create a .env file in your project root to store your database credentials securely:

PORT=3000
DB_HOST=localhost
DB_USER=postgres
DB_PASSWORD=your_password
DB_NAME=my_express_db
DB_PORT=5432

Step 3: Database Connection

Create a folder called lib in the root of your project and create a db.js file in it:

import pg from "pg";
const { Pool } = pg;

let pool;

export const connectDb = async () => {
  try {
    if (pool) return pool;

    pool = new Pool({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME,
      port: Number(process.env.DB_PORT) || 5432,
      max: 10,
      idleTimeoutMillis: 30000,
    });

    // Test connection
    const client = await pool.connect();
    console.log("Connected to PostgreSQL");
    client.release(); // Return the client to the pool

    return pool;
  } catch (error) {
    console.error(`Error connecting to PostgreSQL: ${error.message}`);
    process.exit(1); // Exit process with failure
  }
};

export const getPool = () => pool;

Step 4: Server Setup (server.js)

import express from "express";
import dotenv from "dotenv";
import { connectDb } from "./lib/db.js";

dotenv.config();

const app = express();

app.use(express.json());

await connectDb();

app.get("/", (req, res) => {
  res.send("API is running");
});

app.listen(process.env.PORT, () => {
  console.log(`Server running on http://localhost:${process.env.PORT}`);
});

Step 5: Start the Server

node --watch server.js

You should see the following message in the console:

Connected to PostgreSQL
Server running on http://localhost:3000

Basic CRUD Operations

pg provides a simple and easy-to-use API for interacting with PostgreSQL databases. Here's how you can perform basic CRUD (Create, Read, Update, Delete) operations using pg.

Note: Always use $1, $2, etc., placeholders for parameter substitution. pg automatically escapes values to prevent SQL injection vulnerabilities.

Create a Table

Run a custom script to create a table in PostgreSQL. Create utils/initDb.js:

import { getPool, connectDb } from "../lib/db.js";
import dotenv from "dotenv";

dotenv.config();

const init = async () => {
  try {
    await connectDb();
    const pool = getPool();
    console.log("Running database setup...");

    await pool.query(`
      CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) NOT NULL UNIQUE,
        role VARCHAR(50) DEFAULT 'user',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );
    `);

    console.log("Tables created successfully.");
    process.exit(0);
  } catch (error) {
    console.error("Migration failed:", error.message);
    process.exit(1);
  }
};

init();

Add an NPM script to package.json:

"scripts": {
  "start": "node server.js",
  "dev": "node --watch server.js",
  "db:init": "node utils/initDb.js"
}

Now, run npm run db:init manually to run table setups.

Create a User

import { getPool } from "./lib/db.js";

export const createUser = async (name, email) => {
  const pool = getPool();
  const query = `
    INSERT INTO users (name, email) 
    VALUES ($1, $2) 
    RETURNING id, name, email, created_at
  `;

  // pool.query returns a result object where result.rows contains returned records
  const result = await pool.query(query, [name, email]);

  // result.rows[0] contains the newly created record
  return result.rows[0];
};

Read a User

import { getPool } from "./lib/db.js";

// Fetch all users
export const getAllUsers = async () => {
  const pool = getPool();
  const query = `SELECT id, name, email, created_at FROM users`;
  const result = await pool.query(query);
  return result.rows;
};

// Fetch single user by ID
export const getUserById = async (id) => {
  const pool = getPool();
  const query = `SELECT id, name, email, created_at FROM users WHERE id = $1`;
  const result = await pool.query(query, [id]);

  // Returns undefined if user is not found
  return result.rows[0];
};

Update a User

import { getPool } from "./lib/db.js";

export const updateUser = async (id, name, email) => {
  const pool = getPool();
  const query = `
    UPDATE users 
    SET name = $1, email = $2 
    WHERE id = $3 
    RETURNING id, name, email
  `;

  const result = await pool.query(query, [name, email, id]);

  // result.rowCount indicates if any row was updated
  if (result.rowCount === 0) {
    return null; // User not found
  }

  return result.rows[0];
};

Delete a User

import { getPool } from "./lib/db.js";

export const deleteUser = async (id) => {
  const pool = getPool();
  const query = `DELETE FROM users WHERE id = $1`;

  const result = await pool.query(query, [id]);

  return result.rowCount > 0; // Returns true if deleted, false if not found
};

Report An Issue

← Previous Next →