How to interface with PostgreSQL database in Node.js

In this article, we'll be walking through how to interface with a PostgreSQL database in a Node.js application using pg-pool, a PostgreSQL connection pool library. A connection pool allows you to efficiently manage and reuse database connections, which can significantly improve the performance of your application. We'll cover how to set up the project and database, and how to perform SELECT, INSERT, UPDATE, and DELETE operations.

Set Up Your Project

Before you start working with PostgreSQL in TypeScript and Node.js, make sure you have the following prerequisites:

1. Node.js and npm installed.

2. A PostgreSQL server installed and running.

3. A TypeScript project set up, or you can create one using 'tsc --init'.

Once you have your project ready, you need to install the required packages:

npm install pg pg-pool @types/pg

1. pg: The PostgreSQL client library.

2. pg-pool: The connection pool library.

3. @types/pg: TypeScript type definitions for pg.

Create a Database Configuration

Create a configuration file for your PostgreSQL connection. This file should contain the database connection parameters. You can store this in a separate file like dbConfig.ts:

// dbConfig.ts

import { Pool } from 'pg';

const pool = new Pool({
  user: 'your_username',
  host: 'localhost',
  database: 'your_database',
  password: 'your_password',
  port: 5432, // Default PostgreSQL port
});

export default pool;

Replace 'your_username', 'your_database', and 'your_password' with your PostgreSQL credentials.

Perform Database Operations

Now, you can create functions to perform SELECT, INSERT, UPDATE, and DELETE operations using the connection pool. Here are examples for each operation:

// dbOperations.ts

import pool from './dbConfig';

// SELECT operation
async function getUsers(): Promise<any[]> {
  const client = await pool.connect();
  try {
    const result = await client.query('SELECT * FROM users');
    return result.rows;
  } finally {
    client.release();
  }
}

// INSERT operation
async function addUser(username: string, email: string): Promise<void> {
  const client = await pool.connect();
  try {
    await client.query('INSERT INTO users (username, email) VALUES ($1, $2)', [username, email]);
  } finally {
    client.release();
  }
}

// UPDATE operation
async function updateUser(id: number, newUsername: string): Promise<void> {
  const client = await pool.connect();
  try {
    await client.query('UPDATE users SET username = $1 WHERE id = $2', [newUsername, id]);
  } finally {
    client.release();
  }
}

// DELETE operation
async function deleteUser(id: number): Promise<void> {
  const client = await pool.connect();
  try {
    await client.query('DELETE FROM users WHERE id = $1', [id]);
  } finally {
    client.release();
  }
}

export { getUsers, addUser, updateUser, deleteUser };

In these functions:

• We use 'await pool.connect()' to acquire a connection from the pool.

• We execute the SQL queries using 'client.query()'.

• We release the connection back to the pool using 'client.release()' in a 'try...finally' block to ensure it's always released, even if an error occurs.

Use the Database Functions

You can now use these functions in your application logic:

import { getUsers, addUser, updateUser, deleteUser } from './dbOperations';

async function main() {
  try {
    const users = await getUsers();
    console.log('All users:', users);

    await addUser('newuser', 'newuser@example.com');
    console.log('User added.');

    await updateUser(1, 'updateduser');
    console.log('User updated.');

    await deleteUser(2);
    console.log('User deleted.');
  } catch (error) {
    console.error('Error:', error);
  } finally {
    pool.end(); // Close the connection pool when done
  }
}

main();

This code imports the database functions and demonstrates how to use them to perform database operations.

Remember to handle errors appropriately in your application, and always release connections back to the pool to avoid resource leaks. Additionally, make sure to close the connection pool when your application exits.

If you still have any questions, schedule a call with an expert for free.