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
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)
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
// 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 the directory where index.html and script.js is stored and run the following command
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!
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!
Top comments (2)
Server listening on port 3000
Error occurred: error: ������������ "PC" �� ������ �������� ����������� (�� ������)
at Parser.parseErrorMessage (D:\practice_2_course\www\node_modules\pg-protocol\dist\parser.js:287:98)
at Parser.handlePacket (D:\practice_2_course\www\node_modules\pg-protocol\dist\parser.js:126:29)
at Parser.parse (D:\practice_2_course\www\node_modules\pg-protocol\dist\parser.js:39:38)
at Socket. (D:\practice_2_course\www\node_modules\pg-protocol\dist\index.js:11:42)
at Socket.emit (node:events:514:28)
at addChunk (node:internal/streams/readable:324:12)
at readableAddChunk (node:internal/streams/readable:297:9)
at Readable.push (node:internal/streams/readable:234:10)
at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
length: 114,
severity: '�����',
code: '28P01',
detail: undefined,
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'auth.c',
line: '329',
routine: 'auth_failed'
}
I have this error, what is mean?
You need to change password in "script.js" where you create a Postgres connection