Javascript | Client Libraries | Postgresql Tutorial

Introduction

Explanation: This tutorial covers how to use PostgreSQL with JavaScript. It includes instructions on setting up the environment, connecting to a PostgreSQL database, and performing CRUD operations using Node.js.

Setting Up the Environment

Step 1: Install PostgreSQL

Follow the instructions for installing PostgreSQL on your operating system. Make sure the server is running.

Step 2: Install Node.js and npm

Ensure you have Node.js and npm installed on your system. You can download them from nodejs.org.

Step 3: Initialize a Node.js Project

Create a new directory for your project and initialize it:


mkdir pg-js-tutorial
cd pg-js-tutorial
npm init -y

Step 4: Install pg Module

The pg module is a PostgreSQL client for Node.js. Install it using npm:


npm install pg

Connecting to PostgreSQL

Step 1: Create a Connection

Create a JavaScript file (e.g., index.js) and add the following code to establish a connection:


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

const client = new Client({
    user: 'yourusername',
    host: 'localhost',
    database: 'yourdbname',
    password: 'yourpassword',
    port: 5432,
});

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

Connected to PostgreSQL

Performing CRUD Operations

Creating a Table

Create a new table in your database:


const createTableQuery = `
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    joining_date DATE NOT NULL
)`;

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

Table created successfully

Inserting Data

Insert data into the table:


const insertQuery = `
INSERT INTO employees (name, department, joining_date) VALUES 
    ('Alice', 'HR', '2022-01-10'),
    ('Bob', 'IT', '2022-02-15'),
    ('Charlie', 'Finance', '2022-03-20')
`;

client.query(insertQuery)
    .then(res => console.log('Data inserted successfully'))
    .catch(err => console.error('Error inserting data', err.stack));

Data inserted successfully

Reading Data

Read data from the table:


const selectQuery = 'SELECT * FROM employees';

client.query(selectQuery)
    .then(res => {
        res.rows.forEach(row => {
            console.log(ID: ${row.id}, Name: ${row.name}, Department: ${row.department}, Joining Date: ${row.joining_date});
        });
    })
    .catch(err => console.error('Error reading data', err.stack));

ID: 1, Name: Alice, Department: HR, Joining Date: 2022-01-10
ID: 2, Name: Bob, Department: IT, Joining Date: 2022-02-15
ID: 3, Name: Charlie, Department: Finance, Joining Date: 2022-03-20

Updating Data

Update data in the table:


const updateQuery = `
UPDATE employees
SET department = 'Marketing'
WHERE name = 'Alice'
`;

client.query(updateQuery)
    .then(res => console.log('Data updated successfully'))
    .catch(err => console.error('Error updating data', err.stack));

Data updated successfully

Deleting Data

Delete data from the table:


const deleteQuery = 'DELETE FROM employees WHERE name = \'Charlie\'';

client.query(deleteQuery)
    .then(res => console.log('Data deleted successfully'))
    .catch(err => console.error('Error deleting data', err.stack));

Data deleted successfully

Closing the Connection

Always close the database connection when you're done:


client.end()
    .then(() => console.log('PostgreSQL client disconnected'))
    .catch(err => console.error('Error during disconnection', err.stack));

PostgreSQL client disconnected

Exception Handling

Handle exceptions gracefully to ensure your application doesn't crash unexpectedly:


client.connect()
    .then(() => {
        // Database operations
    })
    .catch(err => console.error('Connection error', err.stack))
    .finally(() => {
        client.end()
            .then(() => console.log('PostgreSQL client disconnected'))
            .catch(err => console.error('Error during disconnection', err.stack));
    });

Conclusion

By following this tutorial, you have learned how to connect to a PostgreSQL database using JavaScript and perform basic CRUD operations. You also learned the importance of handling exceptions and closing database connections properly.

Using PostgreSQL with JavaScript