How to Use PostgreSQL with Node.js: Complete Guide with Examples

Using PostgreSQL with Node.js: Setup and Guide

Connecting PostgreSQL with Node.js enables you to develop scalable applications with a powerful database backend. This guide explains how to configure PostgreSQL for Node.js, manage database connections, perform CRUD operations, and offers helpful examples using the popular pg Node.js library.

Step 1: Install pg Library

The pg package is a widely-used PostgreSQL client for Node.js. Install it with npm:

npm install pg

Step 2: Set Up PostgreSQL Database Configuration

Create a configuration file to store the connection details. Here’s an example of how to set up a basic connection in index.js:

Code:

// Import the 'pg' library
const { Client } = require('pg');

// Set up database connection details
const client = new Client({
  user: 'your_username',      // PostgreSQL username
  host: 'localhost',           // Database host
  database: 'your_database',   // Database name
  password: 'your_password',   // PostgreSQL password
  port: 5432,                  // PostgreSQL default port
});

// Connect to the database
client.connect()
  .then(() => console.log('Connected to PostgreSQL'))
  .catch(err => console.error('Connection error', err.stack));

Step 3: Create a Table Using Node.js

With the database connection set up, let’s create a table in PostgreSQL:

Code:

// Define a SQL query to create a table
const createTableQuery = `
  CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );
`;

// Execute the query
client.query(createTableQuery)
  .then(() => console.log('Table created successfully'))
  .catch(err => console.error('Error creating table', err.stack));

Step 4: CRUD Operations with PostgreSQL and Node.js

Django uses migrations to apply model changes to the database schema. Run the following commands to create and apply migrations.

Insert Data

Code:

// Function to insert a new user
const insertUser = async (name, email) => {
  const insertQuery = `INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *`;
  try {
    const res = await client.query(insertQuery, [name, email]);
    console.log('User inserted:', res.rows[0]);
  } catch (err) {
    console.error('Error inserting user', err.stack);
  }
};

// Example usage
insertUser(‘Jeff Tresha’, ' [email protected]');

Query Data

Code:

// Function to fetch all users
const fetchUsers = async () => {
  try {
    const res = await client.query('SELECT * FROM users');
    console.log('All users:', res.rows);
  } catch (err) {
    console.error('Error fetching users', err.stack);
  }
};

// Example usage
fetchUsers();

Update Data

Code:

// Function to update a user’s email
const updateUser = async (id, newEmail) => {
  const updateQuery = `UPDATE users SET email = $1 WHERE id = $2 RETURNING *`;
  try {
    const res = await client.query(updateQuery, [newEmail, id]);
    console.log('User updated:', res.rows[0]);
  } catch (err) {
    console.error('Error updating user', err.stack);
  }
};

// Example usage
updateUser(1, '[email protected]');

Delete Data

Code:

// Function to delete a user by ID
const deleteUser = async (id) => {
  const deleteQuery = `DELETE FROM users WHERE id = $1 RETURNING *`;
  try {
    const res = await client.query(deleteQuery, [id]);
    console.log('User deleted:', res.rows[0]);
  } catch (err) {
    console.error('Error deleting user', err.stack);
  }
};

// Example usage
deleteUser(1);

Explanation of Code:

• Install pg: Installs the Node.js PostgreSQL library for managing database connections.
• Database Configuration: Configures database credentials, such as username, password, and host.
• Table Creation: Creates a table with columns for user ID, name, email, and timestamp.
• CRUD Functions: Defines separate functions for INSERT, SELECT, UPDATE, and DELETE operations on the users table. Each function uses parameterized queries to prevent SQL injection.

Additional Information:

• Connection Pooling: For larger applications, use Pool instead of Client from the pg library for optimized connection handling.
• Error Handling: Use try-catch blocks to manage errors gracefully and avoid exposing sensitive information.
• Environment Variables: Store database credentials in environment variables for better security.

All PostgreSQL Questions, Answers, and Code Snippets Collection.