Comprehensive Guide to node-pg: PostgreSQL Client for Node.js

Welcome to our detailed guide on node-pg, a powerful PostgreSQL client for Node.js. Whether you’re managing a large-scale application or a small project, node-pg provides a wide range of features and APIs to interact with your PostgreSQL database efficiently.

Getting Started with node-pg

First, you need to install the node-pg library:

npm install pg

Connecting to a PostgreSQL Database

Here’s a basic example of how to establish a connection:

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

const client = new Client({
  user: 'your_username',
  host: 'localhost',
  database: 'my_database',
  password: 'your_password',
  port: 5432,
});

client.connect()
  .then(() => console.log('Connected successfully'))
  .catch(e => console.error('Connection error', e.stack));

Executing SQL Queries

To execute SQL queries, you can use the query method:

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

Parameterized Queries

To prevent SQL injection attacks, you can use parameterized queries:

const text = 'INSERT INTO users(name, email) VALUES($1, $2) RETURNING *';
const values = ['John Doe', 'john.doe@example.com'];

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

Using Pool for Multiple Clients

If your application needs to handle multiple database connections, consider using a connection pool:

const { Pool } = require('pg');
const pool = new Pool({
  user: 'your_username',
  host: 'localhost',
  database: 'my_database',
  password: 'your_password',
  port: 5432,
});

pool.query('SELECT * FROM users;', (err, res) => {
  if (err) {
    console.error(err);
  } else {
    console.log(res.rows);
  }
  pool.end();
});

Transaction Management

node-pg also supports transactions to help you execute a series of SQL statements dependably:

const runTransaction = async () => {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const queryText = 'INSERT INTO users(name, email) VALUES($1, $2) RETURNING id';
    const res = await client.query(queryText, ['Brianna', 'brianna@example.com']);
    const userId = res.rows[0].id;

    const queryText2 = 'INSERT INTO orders(user_id, item) VALUES($1, $2)';
    await client.query(queryText2, [userId, 'Headphones']);

    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
};

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

Complete Application Example

Lastly, here’s a complete example for a simple application interacting with a PostgreSQL database:

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

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

const createUser = (name, email) => {
  return pool.query('INSERT INTO users(name, email) VALUES($1, $2) RETURNING *', [name, email]);
};

const getUserById = (id) => {
  return pool.query('SELECT * FROM users WHERE id = $1', [id]);
};

const updateUser = (id, name, email) => {
  return pool.query('UPDATE users SET name = $1, email = $2 WHERE id = $3 RETURNING *', [name, email, id]);
};

const deleteUser = (id) => {
  return pool.query('DELETE FROM users WHERE id = $1 RETURNING *', [id]);
};

const main = async () => {
  try {
    console.log('Creating user...');
    const newUser = await createUser('Alice', 'alice@example.com');
    console.log('User created:', newUser.rows[0]);

    console.log('Getting user by ID...');
    const user = await getUserById(newUser.rows[0].id);
    console.log('User found:', user.rows[0]);

    console.log('Updating user...');
    const updatedUser = await updateUser(newUser.rows[0].id, 'Alice Doe', 'alice.doe@example.com');
    console.log('User updated:', updatedUser.rows[0]);

    console.log('Deleting user...');
    const deletedUser = await deleteUser(updatedUser.rows[0].id);
    console.log('User deleted:', deletedUser.rows[0]);
  } catch (err) {
    console.error('Error:', err);
  } finally {
    pool.end();
  }
};

main();

By following the above examples, you can efficiently manage your PostgreSQL databases using the node-pg library. Ensure to handle errors properly and maintain your code for scalability and maintainability.

Hash: 1d8e0777cfba09ec4e1394efe0bab322e5e99c4b3238ab04726157dfdac06d22