CRUD Operations with PostgreSQL

Introduction

PostgreSQL is a powerful, open-source relational database management system that supports advanced data types and complex queries. It is widely used for developing data-driven applications because of its reliability, performance, and feature set. When combined with Node.js, PostgreSQL can handle large datasets while maintaining the efficiency and scalability of your application.

In this post, we will explore how to perform CRUD operations—Create, Read, Update, and Delete—with PostgreSQL in Node.js. We will use the pg library, a Node.js PostgreSQL client, to interact with the PostgreSQL database. By the end of this tutorial, you will be able to build an application that can perform all basic database operations with PostgreSQL.

---

Prerequisites

Before we dive into the code, you’ll need the following:

• Node.js installed on your machine.
• PostgreSQL installed or a PostgreSQL cloud instance like Heroku or ElephantSQL.
• The pg library, which is a PostgreSQL client for Node.js.

Installing PostgreSQL

You can download and install PostgreSQL from the official PostgreSQL Downloads Page.

If you prefer a cloud database, services like Heroku or ElephantSQL provide free PostgreSQL databases for development purposes.

Installing the pg Package

To interact with PostgreSQL in Node.js, you need to install the pg package. This package allows Node.js to communicate with your PostgreSQL database.

In your project folder, run the following command to install the pg library:

npm install pg

This will install the PostgreSQL client that we will use to perform CRUD operations.

---

Step 1: Setting Up PostgreSQL in Node.js

1.1 Connecting to PostgreSQL

To connect your Node.js application to PostgreSQL, we need to set up a connection using the pg package. Below is an example of how to do this.

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

// Define the connection parameters
const client = new Client({
  host: 'localhost',          // PostgreSQL server host
  port: 5432,                // Default port for PostgreSQL
  database: 'your_database', // Your PostgreSQL database name
  user: 'your_username',     // Your PostgreSQL username
  password: 'your_password', // Your PostgreSQL password
});

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

Explanation:

• Client: We import the Client object from the pg package to create a connection to PostgreSQL.
• Connection Parameters: Replace localhost, your_database, your_username, and your_password with your PostgreSQL server’s details.
• client.connect(): Establishes a connection to the PostgreSQL database. If successful, it logs a connection message; otherwise, an error is thrown.

---

Step 2: Performing CRUD Operations with PostgreSQL

Now that we’ve established a connection to PostgreSQL, we can perform the basic CRUD operations.

2.1 Creating New Rows (Insert)

To insert new records into a PostgreSQL table, we use the INSERT INTO SQL query.

Example: Inserting a User into the Database

First, let’s assume we have a simple users table with the following structure:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100),
  age INTEGER
);

Here’s how you can insert a new user into the users table using the pg client.

const { Client } = require('pg');
const client = new Client({
  host: 'localhost',
  port: 5432,
  database: 'your_database',
  user: 'your_username',
  password: 'your_password',
});

client.connect();

const insertUser = async () => {
  const query = `
INSERT INTO users (name, email, age)
VALUES ($1, $2, $3)
RETURNING *;
  `;

  const values = ['John Doe', '[email protected]', 30];

  try {
const res = await client.query(query, values);
console.log('User Inserted:', res.rows[0]);
  } catch (err) {
console.error('Error inserting user', err.stack);
  }
};

insertUser()
  .finally(() => client.end());

Explanation:

• SQL Query: The INSERT INTO users SQL query inserts a new record into the users table.
• $1, $2, $3: These placeholders prevent SQL injection and bind user-provided data.
• RETURNING *: This part of the query returns the newly inserted record, which is useful for getting the inserted row.

---

2.2 Reading Data (Select)

To retrieve data from a PostgreSQL table, we use the SELECT statement. We can retrieve all rows or specific rows based on a condition.

Example: Fetching All Users

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

fetchUsers()
  .finally(() => client.end());

Explanation:

• SELECT * FROM users: This SQL query retrieves all rows from the users table.
• res.rows: The pg library returns an array of rows as the result of the query.

Example: Fetching a User by ID

const fetchUserById = async (id) => {
  const query = 'SELECT * FROM users WHERE id = $1;';
  
  try {
const res = await client.query(query, [id]);
if (res.rows.length > 0) {
  console.log('User Found:', res.rows[0]);
} else {
  console.log('User not found');
}
  } catch (err) {
console.error('Error fetching user by ID', err.stack);
  }
};

fetchUserById(1)
  .finally(() => client.end());

Explanation:

• SELECT * FROM users WHERE id = $1: This query fetches a single user based on the provided id.
• [id]: The id is passed as a parameter, preventing SQL injection.

---

2.3 Updating Data (Update)

To update existing records, you use the UPDATE SQL query.

Example: Updating a User’s Age

const updateUserAge = async (id, newAge) => {
  const query = 'UPDATE users SET age = $1 WHERE id = $2 RETURNING *;';
  
  try {
const res = await client.query(query, [newAge, id]);
console.log('User Updated:', res.rows[0]);
  } catch (err) {
console.error('Error updating user', err.stack);
  }
};

updateUserAge(1, 35)
  .finally(() => client.end());

Explanation:

• UPDATE users SET age = $1 WHERE id = $2: This query updates the age column for a specific user.
• RETURNING *: This returns the updated row, similar to the INSERT operation.

---

2.4 Deleting Data (Delete)

To delete records, we use the DELETE FROM SQL query.

Example: Deleting a User by ID

const deleteUserById = async (id) => {
  const query = 'DELETE FROM users WHERE id = $1 RETURNING *;';
  
  try {
const res = await client.query(query, [id]);
if (res.rows.length > 0) {
  console.log('User Deleted:', res.rows[0]);
} else {
  console.log('User not found');
}
  } catch (err) {
console.error('Error deleting user', err.stack);
  }
};

deleteUserById(1)
  .finally(() => client.end());

Explanation:

• DELETE FROM users WHERE id = $1: This query deletes a user by their id.
• RETURNING *: This returns the deleted row, allowing you to verify the deleted data.

---

Step 3: Handling Errors and Closing Connections

3.1 Error Handling

It is important to handle errors when interacting with a database to prevent crashes and ensure that the application behaves as expected. You can handle errors in PostgreSQL operations using try-catch blocks as shown in the examples above.

3.2 Closing the Connection

It is also important to close the connection after all operations are complete. The client.end() method is used to close the connection to the PostgreSQL database.

client.end()
  .then(() => console.log('Connection closed'))
  .catch(err => console.error('Error closing connection', err.stack));

---

Step 4: Using Connection Pools

For production environments, it’s recommended to use connection pooling, which allows the reuse of database connections, improving performance. pg supports connection pooling through the Pool class.

Example: Using Pool for Multiple Queries

const { Pool } = require('pg