How to Connect to PostgreSQL in Node.js: Simple Guide

To connect to PostgreSQL in Node.js, use the pg library by creating a Client with your database details and calling connect(). This establishes a connection so you can run SQL queries from your Node.js app.

Syntax

Use the pg library's Client class to connect. You create a client with connection info, then call connect() to open the connection. After queries, call end() to close it.

• Client: Represents the connection to the database.
• connect(): Opens the connection.
• end(): Closes the connection.

javascript

import { Client } from 'pg';

const client = new Client({
  user: 'yourUser',
  host: 'localhost',
  database: 'yourDatabase',
  password: 'yourPassword',
  port: 5432,
});

await client.connect();
// Run queries here
await client.end();

Example

This example shows how to connect to PostgreSQL, run a simple query to get the current time, and then close the connection.

javascript

import { Client } from 'pg';

async function run() {
  const client = new Client({
    user: 'postgres',
    host: 'localhost',
    database: 'testdb',
    password: 'password123',
    port: 5432,
  });

  try {
    await client.connect();
    const res = await client.query('SELECT NOW()');
    console.log('Current time from DB:', res.rows[0].now);
  } catch (err) {
    console.error('Error connecting or querying', err);
  } finally {
    await client.end();
  }
}

run();

Output

Current time from DB: 2024-06-01T12:34:56.789Z

Common Pitfalls

• Forgetting to await client.connect() before running queries causes errors.
• Not closing the connection with client.end() can leave connections open.
• Using wrong connection details (user, password, host) will fail the connection.
• Running queries without await can cause unexpected behavior.

javascript

import { Client } from 'pg';

// Wrong: Missing await on connect
const client = new Client({ user: 'user', host: 'localhost', database: 'db', password: 'pass', port: 5432 });
client.connect(); // Should be awaited

// Correct:
await client.connect();

Quick Reference

Method	Purpose
Client(config)	Create a new client with connection info
client.connect()	Open connection to PostgreSQL
client.query(sql)	Run SQL query on the database
client.end()	Close the database connection

Key Takeaways

Use the 'pg' library's Client class to connect to PostgreSQL in Node.js.

Always await client.connect() before running queries to avoid errors.

Close connections with client.end() to free resources.

Check your connection details carefully to ensure successful connection.

Use async/await to handle asynchronous database operations cleanly.