PostgreSQL with pg-promise

PostgreSQL is a powerful, open-source relational database system. In the Node.js ecosystem, pg-promise is a popular library for interacting with PostgreSQL databases. It provides a powerful set of features for querying and managing data in PostgreSQL, leveraging promises for asynchronous operations.

Setting Up a New Node.js Project

1.Create a new directory for your project and navigate into it.

				
					mkdir pg-promise-example
cd pg-promise-example

2.Initialize a new Node.js project.

				
					npm init -y

3.Install necessary packages.

				
					npm install pg-promise

Creating a PostgreSQL Database

Open your PostgreSQL client and create a new database.

				
					CREATE DATABASE pg_promise_example;

Installing and Configuring pg-promise

Installing Packages

We have already installed the pg-promise package in the previous section. If you haven’t, run:

				
					npm install pg-promise

Configuring pg-promise

Create a new file config/database.js to set up the pg-promise configuration.

				
					const pgp = require('pg-promise')();
const db = pgp({
    host: 'localhost',
    port: 5432,
    database: 'pg_promise_example',
    user: 'your_username',
    password: 'your_password'
});

module.exports = db;

Replace 'your_username' and 'your_password' with your PostgreSQL username and password.

Basic CRUD Operations

Create

Configuring pg-promise

Let’s add a new user to the database.

Create a new file models/user.js:

				
					const db = require('../config/database');

async function createUser(name, email) {
    try {
        const result = await db.one('INSERT INTO users(name, email) VALUES($1, $2) RETURNING *', [name, email]);
        console.log(result);
    } catch (error) {
        console.error('Error creating user:', error);
    }
}

module.exports = { createUser };

Create a new file scripts/createUser.js to run the createUser function:

				
					const { createUser } = require('../models/user');

createUser('John Doe', 'john.doe@example.com');

Run the script to add a user:

				
					node scripts/createUser.js

Read

Retrieve all users from the database.

Update models/user.js:

				
					async function getAllUsers() {
    try {
        const result = await db.any('SELECT * FROM users');
        console.log(result);
    } catch (error) {
        console.error('Error fetching users:', error);
    }
}

module.exports = { createUser, getAllUsers };

Create a new file scripts/getAllUsers.js to run the getAllUsers function:

				
					const { getAllUsers } = require('../models/user');

getAllUsers();

Run the script to fetch all users:

				
					node scripts/getAllUsers.js

Update

Update a user’s email.

Update models/user.js:

				
					async function updateUser(id, email) {
    try {
        const result = await db.one('UPDATE users SET email = $1 WHERE id = $2 RETURNING *', [email, id]);
        console.log(result);
    } catch (error) {
        console.error('Error updating user:', error);
    }
}

module.exports = { createUser, getAllUsers, updateUser };

Create a new file scripts/updateUser.js to run the updateUser function:

				
					const { updateUser } = require('../models/user');

updateUser(1, 'john.new@example.com');

Run the script to update the user:

				
					node scripts/updateUser.js

Delete

Delete a user from the database.

Update models/user.js:

				
					async function deleteUser(id) {
    try {
        const result = await db.result('DELETE FROM users WHERE id = $1', [id]);
        console.log(result.rowCount);
    } catch (error) {
        console.error('Error deleting user:', error);
    }
}

module.exports = { createUser, getAllUsers, updateUser, deleteUser };

Create a new file scripts/deleteUser.js to run the deleteUser function:

				
					const { deleteUser } = require('../models/user');

deleteUser(1);

Run the script to delete the user:

				
					node scripts/deleteUser.js

Advanced Querying

Parameterized Queries

Parameterized queries help prevent SQL injection attacks by separating SQL code from data.

				
					async function findUserByEmail(email) {
    try {
        const result = await db.oneOrNone('SELECT * FROM users WHERE email = $1', [email]);
        console.log(result);
    } catch (error) {
        console.error('Error finding user:', error);
    }
}

module.exports = { createUser, getAllUsers, updateUser, deleteUser, findUserByEmail };

Create a new file scripts/findUserByEmail.js to run the findUserByEmail function:

				
					const { findUserByEmail } = require('../models/user');

findUserByEmail('john.doe@example.com');

Run the script to find the user:

				
					node scripts/findUserByEmail.js

Using Helpers

pg-promise provides helper functions to simplify query building.

				
					const { helpers } = require('pg-promise');

async function bulkInsertUsers(users) {
    try {
        const columns = new helpers.ColumnSet(['name', 'email'], { table: 'users' });
        const query = helpers.insert(users, columns) + ' RETURNING *';
        const result = await db.any(query);
        console.log(result);
    } catch (error) {
        console.error('Error bulk inserting users:', error);
    }
}

module.exports = { createUser, getAllUsers, updateUser, deleteUser, findUserByEmail, bulkInsertUsers };

Create a new file scripts/bulkInsertUsers.js to run the bulkInsertUsers function:

				
					const { bulkInsertUsers } = require('../models/user');

bulkInsertUsers([
    { name: 'Alice', email: 'alice@example.com' },
    { name: 'Bob', email: 'bob@example.com' }
]);

Run the script to insert multiple users:

				
					node scripts/bulkInsertUsers.js

Transactions

Transactions ensure that a series of operations are executed atomically, meaning either all operations succeed or none do.

Using Transactions

				
					async function createUserWithProfile(user, profile) {
    try {
        await db.tx(async t => {
            const newUser = await t.one('INSERT INTO users(name, email) VALUES($1, $2) RETURNING *', [user.name, user.email]);
            profile.userId = newUser.id;
            const newProfile = await t.one('INSERT INTO profiles(bio, user_id) VALUES($1, $2) RETURNING *', [profile.bio, profile.userId]);
            return { newUser, newProfile };
        });
    } catch (error) {
        console.error('Error in transaction:', error);
    }
}

module.exports = { createUser, getAllUsers, updateUser, deleteUser, findUserByEmail, bulkInsertUsers, createUserWithProfile };

Create a new file scripts/createUserWithProfile.js to run the createUserWithProfile function:

				
					const { createUserWithProfile } = require('../models/user');

createUserWithProfile(
    { name: 'Charlie', email: 'charlie@example.com' },
    { bio: 'Bio for Charlie' }
);

Run the script to create a user with a profile:

				
					node scripts/createUserWithProfile.js

Data Validation

Validating Data Before Insertion

Use libraries like joi for data validation before interacting with the database.

Install joi:

				
					npm install joi

Update models/user.js to validate data:

				
					const Joi = require('joi');

const userSchema = Joi.object({
    name: Joi.string().min(3).max(30).required(),
    email: Joi.string().email().required()
});

async function createUser(name, email) {
    const { error } = userSchema.validate({ name, email });
    if (error) {
        console.error('Validation error:', error.details);
        return;
    }

    try {
        const result = await db.one('INSERT INTO users(name, email) VALUES($1, $2) RETURNING *', [name, email]);
        console.log(result);
    } catch (error) {
        console.error('Error creating user:', error);
    }
}

module.exports = { createUser, getAllUsers, updateUser, deleteUser, findUserByEmail, bulkInsertUsers, createUserWithProfile };

Create a new file scripts/createValidatedUser.js to run the createUser function with validation:

				
					const { createUser } = require('../models/user');

createUser('Jane Doe', 'jane.doe@example.com'); // Valid input
createUser('J', 'j@example.com'); // Invalid input

Run the script to create users:

				
					node scripts/createValidatedUser.js

Connection Pools and Initialization Options

pg-promise provides options to manage connection pools efficiently.

Configuring Connection Pools

Update config/database.js to include connection pool options:

				
					const pgp = require('pg-promise')({
    capSQL: true, // capitalize all generated SQL
    query(e) {
        console.log('QUERY:', e.query);
    },
    error(err, e) {
        if (e.cn) {
            console.error('Connection error:', err.message);
        }
    }
});

const db = pgp({
    host: 'localhost',
    port: 5432,
    database: 'pg_promise_example',
    user: 'your_username',
    password: 'your_password',
    max: 30, // use up to 30 connections
    idleTimeoutMillis: 30000 // 30 seconds before a connection is closed
});

module.exports = db;

Reusing Database Connections

Ensure all modules use the same db instance from config/database.js to manage connections efficiently.

Error Handling

Proper error handling ensures your application can recover gracefully from failures.

Handling Errors in Queries

Use try-catch blocks to handle errors in asynchronous functions.

				
					async function findUserByEmail(email) {
    try {
        const result = await db.oneOrNone('SELECT * FROM users WHERE email = $1', [email]);
        if (!result) {
            throw new Error('User not found');
        }
        console.log(result);
    } catch (error) {
        console.error('Error finding user:', error);
    }
}

module.exports = { createUser, getAllUsers, updateUser, deleteUser, findUserByEmail, bulkInsertUsers, createUserWithProfile };

Centralized Error Handling

Create a centralized error handler to manage errors across your application.

Create a new file utils/errorHandler.js:

				
					function errorHandler(error, req, res, next) {
    console.error('Error:', error.message);
    res.status(500).json({ error: error.message });
}

module.exports = errorHandler;

Update your server setup to use the error handler:

				
					const express = require('express');
const errorHandler = require('./utils/errorHandler');
const app = express();

// ... your routes here ...

app.use(errorHandler);

app.listen(3000, () => {
    console.log('Server is running on port 3000');
});

Performance Tips

Use Prepared Statements

Prepared statements improve performance and security by pre-compiling SQL queries.

				
					const createUserQuery = 'INSERT INTO users(name, email) VALUES($1, $2) RETURNING *';

async function createUser(name, email) {
    try {
        const result = await db.one(createUserQuery, [name, email]);
        console.log(result);
    } catch (error) {
        console.error('Error creating user:', error);
    }
}

module.exports = { createUser, getAllUsers, updateUser, deleteUser, findUserByEmail, bulkInsertUsers, createUserWithProfile };

Batch Insertion

Use batch insertion to insert multiple rows in a single query, reducing the overhead of multiple round-trips to the database.

				
					async function bulkInsertUsers(users) {
    try {
        const columns = new pgp.helpers.ColumnSet(['name', 'email'], { table: 'users' });
        const query = pgp.helpers.insert(users, columns) + ' RETURNING *';
        const result = await db.any(query);
        console.log(result);
    } catch (error) {
        console.error('Error bulk inserting users:', error);
    }
}

module.exports = { createUser, getAllUsers, updateUser, deleteUser, findUserByEmail, bulkInsertUsers, createUserWithProfile };

pg-promise is a powerful library for interacting with PostgreSQL databases in Node.js applications. This chapter has provided a comprehensive guide on setting up and using pg-promise, covering basic and advanced topics.Happy coding !❤️

Table of Contents

Home
Roadmap
AI
AI
Playground
Resources