Step 1

Open Visual Studio Code: Launch Visual Studio Code (VS Code).
Open the Folder: Navigate to the folder where you want to create the index.js file. You can open a folder in VS Code by either:
- Clicking on File > Open Folder... and selecting your folder, or
- Dragging and dropping your folder onto the VS Code window.

Step 2

Create a New JavaScript File (index.js): To create a new file (index.js) within the opened folder:
- Click on File > New File.
- In the new file that appears, immediately type index.js (VS Code will automatically suggest to name the file).
- Press Enter to confirm the file name. VS Code will then create a new file named index.js in the currently opened folder.

Step 3

Open a New Terminal: To open a new terminal in Visual Studio Code:

Click on Terminal in the top menu.
Then select New Terminal from the dropdown menu.
Alternatively, you can use the keyboard shortcut Ctrl+Shift+` (backtick).

Step 4

Once the terminal is open, you can navigate to your project directory where index.js is located:

cd path/to/your/project/folder

Replace path/to/your/project/folder with the actual path to your project directory.

Step 5

Initialize npm in Terminal:

Open the terminal in Visual Studio Code (if not already open) and navigate to your project directory where index.js is located. Then, initialize npm with the following command:

npm init -y

This command initializes a new npm project with default settings (-y flag skips the interactive setup).

Step 6

Install Express and PG Modules: In the newly opened terminal, execute the following npm command to install Express and PG:

npm install express pg

express: Fast, unopinionated, minimalist web framework for Node.js.
pg: PostgreSQL client for Node.js, which allows you to interact with PostgreSQL databases.
Wait for Installation to Complete: npm will download and install the specified packages and their dependencies. This process may take a few moments depending on your internet connection speed.
Verify Installation: Once the installation is complete, you should see the package names and their versions listed in the terminal output. npm will also update your package.json and package-lock.json files to reflect the newly installed packages and their versions.

npm notice created a lockfile as package-lock.json. You should commit this file.
npm WARN your-project-name@1.0.0 No repository field.

+ express@4.17.2
+ pg@8.8.0

up to date, audited 95 packages in 14s

14 packages are looking for funding
  run `npm fund` for details

found 0 vulnerabilities

By following these steps, you'll have successfully installed Express, EJS, and PG modules in your Node.js project, and you can proceed with developing your application using these libraries.

Step 7

Open pgAdmin:

Launch pgAdmin on your computer. It's typically installed alongside PostgreSQL or can be downloaded separately.
If pgAdmin is not installed then install it by viewing this video: https://youtu.be/0n41UTkOBb0?si=7ZukZPce0fZsYN6b

Connect to PostgreSQL Server:

In pgAdmin, connect to your PostgreSQL server where you want to create the database. This typically involves entering the server credentials (username, password, host, port).
Navigate to Servers: In pgAdmin, on the left-hand panel under Browser or Object Browser, expand Servers > PostgreSQL (or the name of your server).
Log into the Server: Double-click on your PostgreSQL server to log in. You may need to enter your PostgreSQL superuser credentials if prompted.
Create a New Database: Right-click on Databases under your server name and choose Create > Database... from the context menu.

Set Database Properties In the Create Database dialog:

Database: Enter a name for your new database (e.g., mydatabase).
Owner: Optionally, you can specify the owner of the database (typically the superuser or another role with appropriate privileges).
Encoding: Leave it as default unless you have specific requirements.
Template: Leave it as default (template1)

Save the Database:

Click Save or OK to create the database. pgAdmin will create the database with the specified name and properties.

Verify Database Creation:

After creating the database, you should see it listed under Databases in pgAdmin's Object Browser panel.

Step-by-Step Implementation of the Program

1. Importing Required Modules

const express = require('express'); // importing express module
const { Client } = require('pg'); // Importing the Client class from the pg package (PostgreSQL client for Node.js)

The program starts by importing the necessary modules. express is a minimal and flexible Node.js web application framework, while pg is a PostgreSQL client for Node.js.

2. Initializing Express Application

const app = express(); // Creating an Express application instance
const port = 3000; // Setting the port number for the server

Here, an Express application is created and assigned to the app variable. The server is set to listen on port 3000.

3.Configuring PostgreSQL Database Connection

const db = new Client({
    user: 'your_postgres_username',     // Replace with your PostgreSQL username
    host: 'localhost',                  // Replace with your PostgreSQL host
    password: 'your_postgres_password', // Replace with your PostgreSQL password
    database: 'your_database_name',      // Replace with the name of your database
    port: 5432,                         // Replace with your PostgreSQL port if different
});

Instructions for Replacement:

user: Replace 'your_postgres_username' with your actual PostgreSQL username.
host: Replace 'localhost' with your actual PostgreSQL host if it's different.
password: Replace 'your_postgres_password' with your actual PostgreSQL password.
database: Replace 'your_database_name' with the name of the PostgreSQL database you want to connect to.
port: Replace 5432 with your actual PostgreSQL port number if it's different from the default port 5432.

4.Connecting to the PostgreSQL Database

db.connect()
    .then(() => {
        console.log("Database connected successfully");

        // Start server
        app.listen(port, () => {
            console.log(`Server is running on port ${port}`);
        });
    })
    .catch(err => {
        console.error('Database connection error', err.stack);
    });

The db.connect() method is called to establish a connection to the PostgreSQL database. This returns a promise, which, when resolved, logs a success message and starts the Express server. If the connection fails, an error message is logged.

// Route to handle root path "/"
app.get("/", (req, res) => {
    if (db._connected) {
        res.send("<h1>Database connected successfully</h1>");
    } else {
        res.send("<h1>Database connection failure</h1>");
    }
});

Here, app.get("/", ...) defines a route in Express that handles HTTP GET requests to the root path ("/"). When a client sends a request to your server's root path (http://localhost:3000/), this route function executes.

Understandingdb._connected (Misconception):

Misconception: The code assumes that db._connected is a property that indicates the current connection status of the PostgreSQL client (db).
Reality: The pg library does not expose a direct property like db._connected for checking the connection status in this manner.

The state of db._connected (note: db._connected is not an officially documented property for checking connection status in pg, hence this is a conceptual example; in real applications, you should handle this differently, possibly by using a flag like isDbConnected).

If db._connected is true, send a response indicating "Database connected successfully".
If db._connected is false, send a response indicating "Database connection failure".

If we access http://localhost:3000/in a local browser, we should see the desired result indicating that the database connection was successful.

Figure 1: Desired Output.

Code

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

const app = express();
const port = 3000;

// PostgreSQL database connection
const db = new Client({
    user: 'your_user_name',
    host: 'localhost',
    password: 'your_password',
    database: 'your_database_name',
    port: 5432,
});

db.connect()
    .then(() => {
        console.log("Database connected successfully");
    })
    .catch(err => {
        console.error('Database connection error', err.stack);
    });
// Route to handle root path "/"
app.get("/", (req, res) => {
    if (db._connected) {
        res.send(`<h1>Database connected successfully</h1>`);
    } else {
        res.send(`<h1>Database connection failure</h1>`);
    }
});
// Start server
        app.listen(port, () => {
            console.log(`Server is running on port ${port}`);
        });

To start the server and access the route

node index.js

Access the application via http://localhost:3000/ in your browser.

Conclusion

Setup: The application initializes an Express server (app) and configures a PostgreSQL client (db) using the pg module. The PostgreSQL connection details (user, host, password, database, port) are specified in the client configuration.
Database Connection: The application connects to the PostgreSQL database using db.connect(), handling the connection promise to log success or error messages. The server starts listening on a specified port (3000) only after the database connection is successfully established.
Route Handling: A single route ("/") is defined to respond with HTML content based on the current status of the PostgreSQL connection:
If the connection is successful, it sends <h1>Database connected successfully</h1>. If there's a connection failure, it sends <h1>Database connection failure</h1>.
Execution: To run the application, Start the application by running node app.js in the terminal and access http://localhost:3000/ in a web browser to see the appropriate message based on the database connection status.

This setup provides a foundation for building more complex applications using Express.js and PostgreSQL, integrating database connectivity seamlessly with web server functionality. Adjustments can be made to handle more routes, implement middleware, or expand database operations as per specific project requirements.

Easy Steps to Link PostgreSQL and Express

Table of contents