3,046 reads

Build a Simple HTML Website with Postgres Database Connectivity

by Abel RoyMay 5th, 2024

Too Long; Didn't Read

This tutorial guides you through creating an HTML website connected to a Postgres database using JavaScript (Node JS, Express JS). It covers setting up the database, creating the HTML structure, connecting with Express JS, and fetching data from Postgres to display on the webpage.

featured image - Build a Simple HTML Website with Postgres Database Connectivity

Get Ready, Future Web Developers! Let's create a simple HTML website that connects to a Postgres Database.

For this example, we will be using JavaScript (Node JS, and Express JS) for its middleware functionalities. Whether you want to showcase your portfolio, run a blog, or complete a due mini project on web development, web development is a skill that's still in demand.

The Learning Path Ahead

We'll dive into the process of building a simple HTML website from scratch. We'll explore how to integrate a Postgres database, using JavaScript as middleware to handle API requests. To spice things up, we can also use Bootstrap5 to design the webpage! But that will be covered in a different blog.

Technologies Used:

HTML5
Postgres (Database)
Express JS (Backend)

Prerequisites

Make sure Postgres is downloaded and ready to use. Also, download Node JS for installing npm packages like Express JS, Body Parser and Postgres APIs. Download Postgres here Download NodeJS here

Step 1: Setup Postgres Database

Create a new Postgres Database either using psql, which is the CLI for running SQL commands or a graphical user interface like pgAdmin (which comes pre-installed). For this demonstration, we will be using psql for creating the database.

Open psql and enter the credentials to get started.

Create a database called webapp and connect to it! (You can use any name you want for your project!)

CREATE database webapp;

\c webapp

Start building your table in this database by CREATE TABLE command. You can use Postgres docs to guide you with the commands. For this demonstration, we are creating a student database that stores Name, Year and Branch. (Add more attributes if you want)

CREATE TABLE student 
(name varchar(20), semester int, branch varchar(20));

\d student

Now, we can start uploading some dummy data to the table via INSERT command

INSERT INTO student VALUES
('Abel', 4, 'CE'), 
('John', 6, 'ME'), 
('Doe', 2, 'ETC');

Once we are done, adding the dummy data, we can view the Student table with the below command

SELECT * FROM student;

If you followed the steps till now, you would get the below student table!

Step 2: Create a simple HTML Website

Below is the Boilerplate HTML code for a simple website:

<!DOCTYPE html>
<html lang='en'>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width">
  <title> Student Management System </title>
</head>
<body>
  <h1> Student Management System </h1>
</body>
</html>

Let's start adding the custom div blocks as per our needs:

...
<body>
  <h1> Student Management System </h1>
  <p> Here, are the student details as below: </p>
  <div id='students_list'></div>
...

students_list div block is for showing the list of students that have been registered to the database. We will be using Express JS to fetch the data from Postgres and showcase it in the block.

Step 3: Connecting HTML to Postgres via Express JS

Install the necessary libraries first in your Command Prompt or Terminal

npm install express body-parser pg

Let's start building the script.js file for connecting HTML and PostgreSQL. Firstly, we require importing the necessary libraries for handling requests and establishing a connection

// Import required modules
const express = require('express');
const { Pool } = require('pg');
const path = require('path');
const bodyParser = require('body-parser');

express works as a backend service, for parsing requests from the HTML webapp pg is a Postgres API for establishing the connection path provides utilities for managing directory paths body-parser is a middle-ware for extracting data from POST requests (We will be understanding them in depth as we move along)

Let's now create an express application connection and also, define the port at which the server will be listening to.

// Connect and Create an Express Application
const app = express();
const port = 3000; // By default, its 3000, you can customize

Establish a connection to Postgres by creating an instance of the Pool object. Add the necessary values to match your Postgres setup.

// Create a Postgres Connection
const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'webapp',
  password: 'password', // Change to your password
  port: 5432, // Default Port
});

Add functionalities to register static files via express.static() middleware. It specifies the root directory from which to serve static files

app.use(express.static(path.join('')));

Now, we have to parse HTML requests sent from the app. In simple words, it's a middleware used to recieve data from users, such as forms or uploads

app.use(bodyParser.urlencoded({ extended: false }));

(Don't add the above line if you are not planning to take input from users. We will be adding a registeration form in the next blog and that's why we require body-parser)

Set up a Route handler for the root URL ('/'). So that, when a GET request is made to the root URL, the server responds by sending the "index.html" file located in the same directory

// Setup Route handler
app.get('/', (req, res) => {
  res.sendFile(path.join(__dirname, '', 'index.html'));
});

Now, comes the main part! We will now be setting up a route handler for '/students' URL with the HTTP GET method. This handler retrieves student data (from query) from the Postgres Database.

// Route handler for GET student data
app.get('/students', (req, res) => {
  const query = 'SELECT * FROM student;';

  pool.query(query, (error, result) => {
    if (error) {
      console.error('Error occurred:', error);
      res.status(500).send('An error occurred while retrieving data from the database.');
    } else {
      const students = result.rows;
      res.json(students);
    }
  });
});

(Make sure the brackets are closed properly)

Now, let's specify the line that listens to the server and when the time is due, it responds with its requests. As the below command listens, it logs a message to console.

// Listening to Requests
app.listen(port, () => {
  console.log(`Server listening on port ${port}`);
});

(Good for debugging purposes)

Done! script.js is finally complete. Let's make the changes in the index.html file for showcasing the necessary details.

...
  <div id='students_list'></div>
  <script>
  // Fetch data via requests
  fetch('/students')
    .then(response => response.json())
    .then(data => {
    const studentList = data.map(
      student => `<li> About ${student.name} : ${student.branch} - ${student.semester}th Sem</li>`
    ).join('');
    document.getElementById('students_list').innerHTML = `<ul>${studentList}</ul>`;
  })
    .catch(error => console.error('Error occurred:', error));
  </script>
...

Run the Website

Open Terminal and go to the directory where index.html and script.js is stored and run the following command:

node script.js

If everything works all right, it should display the content "Server listening on port 3000"

Now, you need to go to http://localhost:3000/ where you can see a simple HTML website showcasing the data you had entered!

Voila! That's it!

Now, use your creativity and knowledge to explore these web development concepts and create more interesting websites!

I will also be adding another blog on how to create a registration system and how to design it, which will be coming soon. Follow for more and stay tuned!

Until then, Keep Coding!