How to Use PostgreSQL with Node.js

Introduction

PostgreSQL is a powerful, open source relational database. Combining it with Node.js allows web developers to build scalable, efficient applications with robust data storage. In this guide, we’ll walk you through connecting Node.js to PostgreSQL, performing CRUD operations, and ensuring best practices for security and performance.

Why Use PostgreSQL with Node.js?

• Open Source & Free: PostgreSQL offers enterprise-level features with zero licensing cost.
• Advanced Features: Full ACID compliance, JSON support, and powerful indexing.
• Scalability: Easily manages large datasets and concurrent requests typical in web apps.

Getting Started: Prerequisites

• Basic knowledge of JavaScript and Node.js.
• PostgreSQL installed (Download here).
• Node.js and npm installed (Get Node.js).

Step 1: Setting Up PostgreSQL Database

1. Start PostgreSQL server.
2. Create a new database and user:

   psql -U postgres
   CREATE DATABASE mydb;
   CREATE USER devuser WITH ENCRYPTED PASSWORD 'devpass';
   GRANT ALL PRIVILEGES ON DATABASE mydb TO devuser;
   

Step 2: Install the ‘pg’ Node.js Library

In your Node.js project folder, install the PostgreSQL client for Node.js:

npm install pg

Step 3: Connecting Node.js to PostgreSQL

Use the pg library to connect to your PostgreSQL database. Below is a simple configuration using a connection pool:

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

const pool = new Pool({
  user: 'devuser',
  host: 'localhost',
  database: 'mydb',
  password: 'devpass',
  port: 5432,
});

// Testing the connection
pool.query('SELECT NOW()', (err, res) => {
  if (err) {
    console.error('Connection error', err.stack);
  } else {
    console.log('Connected:', res.rows[0]);
  }
  pool.end();
});

Step 4: Performing CRUD Operations

Create

// Inserting a row
pool.query('INSERT INTO users(name, email) VALUES($1, $2)', ['Alice', 'alice@example.com']);

Read

// Fetching users
pool.query('SELECT * FROM users', (err, res) => {
  console.log(res.rows);
});

Update

// Updating user email
pool.query('UPDATE users SET email = $1 WHERE name = $2', ['alice@fulldev.pl', 'Alice']);

Delete

// Deleting a user
pool.query('DELETE FROM users WHERE name = $1', ['Alice']);

Best Practices

• Use Environment Variables: Never hardcode credentials in your source code. Use packages like dotenv to manage sensitive information.
• Handle Errors Gracefully: Always check for errors in query callbacks or use try/catch with async/await.
• Close Connections: Prevent memory leaks by properly closing or reusing database connections.

Example Project Structure

project/
  |-- db.js      // PostgreSQL connection setup
  |-- index.js   // App entry point
  |-- models/    // DB query functions

Conclusion

Integrating PostgreSQL with Node.js is straightforward and enables the creation of efficient, scalable web applications. With tools like pg, you can effortlessly manage your data and build modern web services.

Further Reading

• node-postgres Documentation
• Official PostgreSQL Documentation