Connecting to Postgres with Nodejs

In this tutorial, we have provided a thorough, step-by-step guide for creating a connection to a PostgreSQL database using Node.js. We will use the pg package for establishing a connection to the Postgres database.

Let’s get started

Step 1 - Install the pg package in your project using npm. To install it, run this command in your terminal

npm install pg

Step 2 - Initialize the pg package in your Node.js script and get the Client from it.

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

Make sure you initialize the pg package at the top of program

Step 3 - Create a PostgreSQL client object consisting of essential Postgres database credentials.

// Load environment variables from .env file
// Install with: npm install dotenv
require('dotenv').config();

const client = new Client({
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  database: process.env.DB_NAME,
});

Make sure to create a .env file in your project root with your database credentials:

DB_USER=your_username
DB_PASSWORD=your_password
DB_HOST=your_host
DB_PORT=your_port
DB_NAME=your_database

Never commit the .env file to your repository. Add it to your .gitignore file to prevent accidental exposure of sensitive credentials.

Step 4 - Setup a connection with the database using the connect method with the manually created client object

client
  .connect()
  .then(() => {
    console.log("Connected to PostgreSQL database");
  })
  .catch((err) => {
    console.error("Error connecting to PostgreSQL database", err);
  });

Step 5 - Execute your desired SQL query to get the data. You can use the query method to run the SQL query

client.query("SELECT * FROM customers", (err, result) => {
  if (err) {
    console.error("Error executing query", err);
  } else {
    console.log("Query result:", result.rows);
  }
});

Step 6 - Close the connection after the whole work is done. Use the end method to close the connection

client
  .end()
  .then(() => {
    console.log("Connection to PostgreSQL closed");
  })
  .catch((err) => {
    console.error("Error closing connection", err);
  });

Here’s the complete code to connect to the Postgres database with Node.js

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

// Load environment variables from .env file
require('dotenv').config();

// Database connection configuration using environment variables
const dbConfig = {
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  database: process.env.DB_NAME,
};

// Create a new PostgreSQL client
const client = new Client(dbConfig);

// Connect to the database
client
  .connect()
  .then(() => {
    console.log("Connected to PostgreSQL database");

    // Execute SQL queries here

    client.query("SELECT * FROM employees", (err, result) => {
      if (err) {
        console.error("Error executing query", err);
      } else {
        console.log("Query result:", result.rows);
      }

      // Close the connection when done
      client
        .end()
        .then(() => {
          console.log("Connection to PostgreSQL closed");
        })
        .catch((err) => {
          console.error("Error closing connection", err);
        });
    });
  })
  .catch((err) => {
    console.error("Error connecting to PostgreSQL database", err);
  });

In this example, we have demonstrated how you can read the data, but you can also perform other operations of Postgres database also like Insert, Update, and Create Table

Insert statements

client
  .connect()
  .then(() => {
    const insert = "INSERT INTO employees(column1, column2) VALUES (value1, value2)";
    const values = ["value1", "value2"];

    client.query(insert, values, (err, result) => {
      if (err) {
        console.error("Error inserting data", err);
      } else {
        console.log("Data inserted successfully");
      }

      client.end();
    });
  })
  .catch((err) => {
    console.error("Error connecting to PostgreSQL database", err);
  });

Update statements

const update = "UPDATE employees SET column1 = value1 WHERE column2 = value2";
const values = ["updated_value", "criteria_value"];

client.query(update, values, (err, result) => {
  if (err) {
    console.error("Error updating data", err);
  } else {
    console.log("Data updated successfully");
  }

  client.end();
});

DDL statements like Create Table

const createTable = `
  CREATE TABLE employees(
    id serial PRIMARY KEY,
    column1 datatype1,
    column2 datatype2
  );
`;

client.query(createTable, (err, result) => {
  if (err) {
    console.error("Error creating table", err);
  } else {
    console.log("Table created successfully");
  }

  client.end();
});

Conclusion

In this guide, we discussed the step-by-step process to connect to the Postgres database with Node.js.

We highly suggest you to read our guides to understand the multiple operations you can perform with the Postgres database.

Also, check our highly informative blog posts to learn about our useful extensions.