How to Connect to PostgreSQL with pg

Table Of Contents

• Problem
• Solution
• Explanation

Problem

You need to connect your Node.js application to a PostgreSQL database to execute SQL queries and manage data operations.

Solution

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

// Basic connection with Pool (recommended)
const pool = new Pool({
  user: 'username',
  host: 'localhost',
  database: 'myapp',
  password: 'password',
  port: 5432,
});

async function queryDatabase() {
  try {
    const result = await pool.query('SELECT NOW()');
    console.log('Connected to PostgreSQL:', result.rows[0]);
  } catch (error) {
    console.error('Database query error:', error);
  }
}

// Environment variables (recommended)
const poolFromEnv = new Pool({
  connectionString: process.env.DATABASE_URL || 'postgresql://username:password@localhost:5432/myapp'
});

// Single client connection
async function singleConnection() {
  const client = new Client({
    user: 'username',
    host: 'localhost',
    database: 'myapp',
    password: 'password',
    port: 5432,
  });
  
  try {
    await client.connect();
    const result = await client.query('SELECT * FROM users LIMIT 1');
    console.log(result.rows);
  } catch (error) {
    console.error('Connection error:', error);
  } finally {
    await client.end();
  }
}

// Basic CRUD operations
async function userOperations() {
  // Create
  const newUser = await pool.query(
    'INSERT INTO users(name, email) VALUES($1, $2) RETURNING *',
    ['John Doe', 'john@example.com']
  );
  
  // Read
  const users = await pool.query('SELECT * FROM users WHERE id = $1', [1]);
  
  // Update
  const updated = await pool.query(
    'UPDATE users SET name = $1 WHERE id = $2 RETURNING *',
    ['Jane Doe', 1]
  );
  
  // Delete
  await pool.query('DELETE FROM users WHERE id = $1', [1]);
  
  return { newUser: newUser.rows[0], users: users.rows };
}

// Graceful shutdown
process.on('SIGINT', async () => {
  await pool.end();
  console.log('PostgreSQL pool closed');
  process.exit(0);
});

Install pg:

npm install pg

Explanation

Use Pool for multiple connections (recommended) or Client for single connections. Always use parameterized queries ($1, $2) to prevent SQL injection.

The Pool manages connections automatically and is more efficient for applications with multiple database operations. Store credentials in environment variables for security.