A simple example to access a PostgreSQL database in Node.js

To access a PostgreSQL database from a Node.js application, you can use the pg library, which is a popular PostgreSQL client for Node.js.

Here’s a simple example demonstrating how to connect to a PostgreSQL database, execute a query, and retrieve results using the pg library.

Step 1: Install the pg Library

First, you need to install the pg library:

npm install pg

Step 2: Write Node.js Code to Access PostgreSQL

Create a file named postgres-example.js and add the following code:

File: postgres-example.js

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

// Define your PostgreSQL database connection details
const client = new Client({
  user: 'your-username',
  host: 'localhost',
  database: 'your-database',
  password: 'your-password',
  port: 5432, // Default PostgreSQL port
});

// Connect to the PostgreSQL database
client.connect()
  .then(() => {
    console.log('Connected to PostgreSQL database');
    
    // Define a SQL query
    const query = 'SELECT NOW() AS current_time';
    
    // Execute the query
    return client.query(query);
  })
  .then((res) => {
    // Print the results
    console.log('Query Results:', res.rows);
  })
  .catch((err) => {
    console.error('Error:', err);
  })
  .finally(() => {
    // Close the database connection
    client.end();
  });

Explanation

1. Import pg Library: Import the Client class from the pg library.
2. Configure Database Connection: Create a new Client instance with the connection details, including the user, host, database, password, and port. Adjust these values based on your PostgreSQL setup.
3. Connect to Database: Use client.connect() to establish a connection to the PostgreSQL database.
4. Execute a Query: Define and execute a SQL query using client.query(). In this example, we’re retrieving the current time from the database.
5. Handle Results: Log the query results. The results are returned as an array of objects in res.rows.
6. Error Handling: Catch and log any errors that occur during the connection or query execution.
7. Close Connection: Ensure the database connection is closed with client.end() after completing the operations.

Running the Script

Execute the script with Node.js:

node postgres-example.js

Notes

• Replace Placeholder Values: Make sure to replace 'your-username', 'your-database', and 'your-password' with your actual PostgreSQL credentials.
• Database Schema: Adjust the SQL query to match your database schema and requirements.
• Environment Variables: For security, consider using environment variables or a configuration file to store your database credentials instead of hardcoding them into your script.

This example provides a basic setup for connecting to and querying a PostgreSQL database in a Node.js application.