PostgreSQL CRUD Tutorials in JavaScript/Node.js: A Step-by-Step Guide

PostgreSQL is a powerful open-source relational database management system that seamlessly integrates with JavaScript and Node.js applications. In this tutorial, we will explore how to perform CRUD (Create, Read, Update, Delete) operations with PostgreSQL using JavaScript and Node.js. By the end of this article, you’ll have a solid understanding of how to interact with a PostgreSQL database from your JavaScript applications.

Prerequisites

1. Node.js installed on your machine.
2. A running PostgreSQL server.
3. Basic knowledge of JavaScript and Node.js.

Step 1: Install Required Packages

Before we start working with PostgreSQL, let’s make sure we have the necessary packages installed. We’ll use the pg package for connecting to the database and executing queries.

npm install pg

Step 2: Set Up the Database Connection

In your Node.js application, you need to establish a connection to the PostgreSQL database. Create a JavaScript file (e.g., db.js) and add the following code:

const { Pool } = require("pg")

const pool = new Pool({
  user: "your-username",
  host: "your-database-host",
  database: "your-database-name",
  password: "your-password",
  port: 5432 // Default PostgreSQL port
})

module.exports = pool

Replace 'your-username', 'your-database-host', 'your-database-name', 'your-password' with your PostgreSQL server details.

Step 3: Create a New Record (Create - C)

Now, let’s create a function to insert data into a PostgreSQL table. Create a JavaScript file (e.g., create.js) and add the following code:

const pool = require("./db")

async function createRecord(name, email) {
  try {
    const result = await pool.query(
      "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id",
      [name, email]
    )
    console.log(`New record added with ID: ${result.rows[0].id}`)
  } catch (err) {
    console.error("Error creating record:", err)
  } finally {
    pool.end()
  }
}

createRecord("John Doe", "[email protected]")

This code defines a function createRecord that inserts a new user into a ‘users’ table and returns the ID of the inserted record.

Step 4: Read Data (Read - R)

To read data from the database, create another JavaScript file (e.g., read.js) with the following code:

const pool = require("./db")

async function readRecords() {
  try {
    const result = await pool.query("SELECT * FROM users")
    result.rows.forEach((row) => {
      console.log(`ID: ${row.id}, Name: ${row.name}, Email: ${row.email}`)
    })
  } catch (err) {
    console.error("Error reading records:", err)
  } finally {
    pool.end()
  }
}

readRecords()

This code defines a function readRecords that retrieves all user records from the ‘users’ table and prints them to the console.

Step 5: Update Data (Update - U)

To update existing records, create a JavaScript file (e.g., update.js) with the following code:

const pool = require("./db")

async function updateRecord(id, newName) {
  try {
    const result = await pool.query(
      "UPDATE users SET name = $1 WHERE id = $2",
      [newName, id]
    )
    console.log(`Record updated: ${result.rowCount} rows affected`)
  } catch (err) {
    console.error("Error updating record:", err)
  } finally {
    pool.end()
  }
}

updateRecord(1, "Jane Doe")

This code defines a function updateRecord that updates a user’s name based on their ID.

Step 6: Delete Data (Delete - D)

To delete records, create a JavaScript file (e.g., delete.js) with the following code:

const pool = require("./db")

async function deleteRecord(id) {
  try {
    const result = await pool.query("DELETE FROM users WHERE id = $1", [id])
    console.log(`Record deleted: ${result.rowCount} rows affected`)
  } catch (err) {
    console.error("Error deleting record:", err)
  } finally {
    pool.end()
  }
}

deleteRecord(1)

This code defines a function deleteRecord that deletes a user record based on their ID.

Conclusion

In this article, we’ve covered the fundamental CRUD operations using PostgreSQL with JavaScript and Node.js. You’ve learned how to establish a database connection, create, read, update, and delete records. This knowledge will empower you to build more complex applications that interact with PostgreSQL databases seamlessly. Remember to handle errors gracefully and adapt these examples to suit your specific project requirements.

If you want to learn more about MySQL, please use our PostgreSQL tutorials and PostgreSQL Reference.