Create a simple HTML website with Postgres Database
Tags: webdev, beginners, html, postgres
Published: 2023-05-18 20:58:08

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 it's 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. 

### Learning Path Ahead

We'll dive in to 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 be using 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](https://www.postgresql.org/download/)
Download NodeJS [here](https://nodejs.org/en/download)

## 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.
![Startup Page of PSQL](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lpkr1k79kmthp6t962f1.png)

Create a database called `webapp` and connect to it!
_(You can use any name you want for your project!)_
```sql
CREATE database webapp;
```
```sql
\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)_

```sql
CREATE TABLE student 
(name varchar(20), semester int, branch varchar(20));
```
```sql
\d student
```

Now, we can start uploading some dummy data to the table via INSERT command
```sql
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

```sql
SELECT * FROM student;
```

If you followed the steps till now, you would get the below `student` table!
![Result of SELECT command](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2fbv0akj085j8vkkjwg7.png)


## Step 2 : Create a simple HTML Website

Below is the Boilerplate HTML code for a simple website
```html
<!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
```html
...
<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
```shell
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
```js
// 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.
```js
// 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.
```js
// 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
```js
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
```js
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)_

Setup 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 same directory
```js
// 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

```js
// 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.
```js
// 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.
```html
...
  <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 the directory where `index.html` and `script.js` is stored and run the following command
```shell
node script.js
```
If everything works alright, it should display the contents "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!

![Image of Simple Website](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qebfr79gdnw6p2o4rc9p.png)

> 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, which will be coming soon. Follow for more and stay tuned! 
Until then, Keep Coding!