How to Connect a Node.js App to a PostgreSQL Database

When moving from frontend to backend development, the most critical leap is connecting your API to a persistent database. PostgreSQL (often just called Postgres) is the undisputed king of open-source relational databases. It is incredibly robust, strict with data integrity, and scales beautifully.

In this guide, we will connect a Node.js Express server to a Postgres database using the industry-standard pg library (node-postgres). We will focus on two critical production patterns: Connection Pooling and Parameterized Queries.

Step 1: Installation & Environment Setup

First, install the required packages. We need `pg` to talk to the database and `dotenv` to keep our database credentials secure.

npm install pg dotenv

Create a .env file in the root of your project. Never hardcode database passwords in your JavaScript files!

DB_USER=your_postgres_username
DB_PASSWORD=your_super_secret_password
DB_HOST=localhost
DB_PORT=5432
DB_NAME=my_app_database

Step 2: Creating a Connection Pool

Beginners often use `new Client()` to connect to the database. This opens a single TCP connection. If two users hit your API at the exact same time, the second user has to wait. In production, we use a Pool. A pool opens multiple connections (e.g., 10) and hands them out to incoming requests simultaneously, drastically improving API performance.

Create a new file called db.js:

// 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_NAME,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT,
});

// A helper function to export the query execution
module.exports = {
  query: (text, params) => pool.query(text, params),
};

Step 3: Writing Secure Queries (Preventing SQL Injection)

Now, let's use our pool in an Express route to fetch users. Let's say we want to search for a user by their email address.

To safely insert variables into our SQL, we use Parameterized Queries. We put $1 placeholders in our SQL string, and pass the actual variables in an array. The `pg` library safely sanitizes them for us.

// server.js
const express = require('express');
const db = require('./db'); // Import our pool

const app = express();
app.use(express.json());

// READ: Fetch a user by email safely
app.get('/users/search', async (req, res) => {
  try {
    const { email } = req.query;
    
    // $1 is safely replaced by the first item in the array
    const result = await db.query(
      'SELECT id, username, email FROM users WHERE email = $1',
      [email]
    );

    res.json(result.rows);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: "Internal Server Error" });
  }
});

// CREATE: Insert a new user
app.post('/users', async (req, res) => {
  try {
    const { username, email } = req.body;
    
    // RETURNING * sends back the newly created row
    const result = await db.query(
      'INSERT INTO users (username, email) VALUES ($1, $2) RETURNING *',
      [username, email]
    );

    res.status(201).json(result.rows[0]);
  } catch (error) {
    res.status(500).json({ error: "Failed to create user" });
  }
});

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

Conclusion

By separating your database configuration into a dedicated module, utilizing connection pools, and strictly enforcing parameterized queries, your Node.js backend is now ready for production traffic. Postgres is a massive engine, and the pg library is the perfect, lightweight conduit to access its power.