Comprehensive Guide to node-pg Powerful PostgreSQL Integration for Node.js

Introduction to node-pg

The node-pg is a popular PostgreSQL client for the Node.js runtime. It is highly efficient, providing a wide range of features for database manipulation. In this guide, we will explore various aspects of node-pg making use of detailed API explanations and code snippets for each.

Getting Started

First, install the library using npm:

npm install pg

Creating a Client Instance

Establish a connection to a PostgreSQL database using the Client class:

  const { Client } = require('pg');
  const client = new Client({
    user: 'your-username',
    host: 'localhost',
    database: 'your-database',
    password: 'your-password',
    port: 5432,
  });

  client.connect();

Query Example

Executing a simple query:

  client.query('SELECT NOW()', (err, res) => {
    if (err) {
      console.error(err);
    } else {
      console.log(res.rows);
    }
    client.end();
  });

Parameterized Queries

Using parameterized queries to prevent SQL injection:

  const text = 'INSERT INTO users(name, email) VALUES($1, $2) RETURNING *';
  const values = ['brianc', 'brian.m.carlson@gmail.com'];

  client.query(text, values, (err, res) => {
    if (err) {
      console.error(err.stack);
    } else {
      console.log(res.rows[0]);
    }
    client.end();
  });

Pooling Connections

Using a connection pool to manage multiple clients:

  const { Pool } = require('pg');
  const pool = new Pool({
    user: 'your-username',
    host: 'localhost',
    database: 'your-database',
    password: 'your-password',
    port: 5432,
  });

  pool.query('SELECT NOW()', (err, res) => {
    console.log(err, res);
    pool.end();
  });

Transactions

Managing transactions in PostgreSQL:

  async function runTransaction() {
    const client = await pool.connect();
    try {
      await client.query('BEGIN');
      const res = await client.query('SELECT id FROM users WHERE name = $1', ['brianc']);
      const insertText = 'INSERT INTO posts(user_id, body) VALUES ($1, $2)';
      await client.query(insertText, [res.rows[0].id, 'some post content']);
      await client.query('COMMIT');
    } catch (e) {
      await client.query('ROLLBACK');
      throw e;
    } finally {
      client.release();
    }
  }

  runTransaction().catch(e => console.error(e.stack));

Event Handling

Listening to events:

  client.on('error', (err) => {
    console.error('Unexpected error on idle client', err);
    process.exit(-1);
  });

App Example

Combining the above techniques, let’s create a sample application:

  const express = require('express');
  const { Pool } = require('pg');

  const app = express();
  const pool = new Pool({
    user: 'your-username',
    host: 'localhost',
    database: 'your-database',
    password: 'your-password',
    port: 5432,
  });

  app.get('/users', async (req, res) => {
    try {
      const result = await pool.query('SELECT * FROM users');
      res.json(result.rows);
    } catch (err) {
      console.error(err);
      res.status(500).send('Internal Server Error');
    }
  });

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

With these samples and API explanations, you’re now ready to effectively use node-pg for your Node.js applications.

Hash: 1d8e0777cfba09ec4e1394efe0bab322e5e99c4b3238ab04726157dfdac06d22