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! 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) 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 here 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. psql psql Open psql and enter the credentials to get started. psql Create a database called webapp and connect to it! (You can use any name you want for your project!) webapp (You can use any name you want for your project!) CREATE database webapp; CREATE database webapp; \c 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 Year Branch (Add more attributes if you want) CREATE TABLE student (name varchar(20), semester int, branch varchar(20)); CREATE TABLE student (name varchar(20), semester int, branch varchar(20)); \d student \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'); 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 Student SELECT * FROM student; SELECT * FROM student; If you followed the steps till now, you would get the below student table! student 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> <!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> ... ... <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. students_list Step 3: Connecting HTML to Postgres via Express JS Install the necessary libraries first in your Command Prompt or Terminal Command Prompt npm install express body-parser pg 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 script.js // Import required modules const express = require('express'); const { Pool } = require('pg'); const path = require('path'); const bodyParser = require('body-parser'); // 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) express pg path body-parser (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 // 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. Pool // Create a Postgres Connection const pool = new Pool({ user: 'postgres', host: 'localhost', database: 'webapp', password: 'password', // Change to your password port: 5432, // Default Port }); // 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 express.static() app.use(express.static(path.join(''))); 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 })); 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) (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')); }); // 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); } }); }); // 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) (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}`); }); // Listening to Requests app.listen(port, () => { console.log(`Server listening on port ${port}`); }); (Good for debugging purposes) (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. script.js index.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> ... ... <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: index.html script.js node script.js 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! http://localhost:3000/ Voila! That's it! 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!
