A web app using postgresql locally

Creating a web app using postgresql with Docker

Creating a web app with a PostgreSQL backend - Docker version

It is very common to have a web application that uses some sort of database backend. A good choice for an SQL based database is PostgreSQL. PostgreSQL is an advanced open source relational database. It is capable of handling a large amount of data and is used by large companies including Apple, Instagram and Reddit. Therefore, it is a good database to learn about if you want experience with a relational database.

Developing the backend first

Once you start thinking about using a database backend for your web application, it is important to think about the correct order of developing the application. If there is a database backend, you need to work on that backend first. This will lead to the easiest debugging and development process. Here are some steps to think about.

Decide on which database you will use. For this first example, we will be using PostgreSQL.
Decide on how to provide the database service. That is, will it be installing the database service on a server, or will you provide the service with something like a Docker container. Or will it be a service through some cloud provider.
Regardless of how you decide to provide the database service, it will be easier to learn the ins and outs of using a database backend if you start with a local provider of the database service.
Set up a local database service. Create a database on that service, and populate that database with some initial data. This will involve creating an initial table or tables, and inserting some test data into that table or tables.
Provide a way to connect to the database you created. If your web application will be using JavaScript and HTML, this means using the appropriate Node.js packages that provides that connection. For example, Express is a Node package that provides a web application framework for allowing selecting data from the database, as well as inserting data, editing data and deleting data from the database. In addition, you will need a database-specific driver. For PostgreSQL, this would be the pg package.
Using Express and the database driver, set up routes that provide an API for interacting with the database. Test these routes using tools like curl and Postman.
Only after a route has been tested, should you start developing the front-end of the web application (using JavaScript and HTML).

By developing the backend first, testing the backend, then going to the front end, you will minimize errors. This will lead to shorter development times as errors will be fixed as you go along. You don’t need to develop every route that your application needs before starting to work on the front end. But, any route that you are going to work with in the front end, must be tested first. Only after checking to make sure the route works, should you attempt to use that route in your front end of the web application. An important part of this lesson is to show how to perform this kind of process. Web applications are relatively complex applications. So, the correct development process is critical to the success of your project.

Options for PostgreSQL servers

There are several options for providing a PostgreSQL server to work with. If you are on Ubuntu (or any other Debian-based Linux), you can install a PostgreSQL server and client. You can do this locally, for test purposes and for becoming familiar with working with a backend database. You can also do this by using a hosted server (like on Digital Ocean for example) to host PostgreSQL for a web app that runs on the World Wide Web. You can also provide a PostreSQL server using Docker (Docker Docs). One advantage of using a *Docker container containing PostgreSQL, is that you don’t need to actually install PostgreSQL on your computer. Still another option to provide a PostgreSQL server is to use a cloud service such as Supabase (Supabase pricing). Supabase offers a free version of their service that can easily be used as your backend if you are just learning about creating web apps.

PHP (PHP home) is a scripting language that is suitable for web applications. PHP can be used with JavaScript to make more dynamic web pages than can be created with PHP alone. Since PHP has built-in capabilities for connecting to a PostgreSQL database, creating a simple web application with PHP is one of the most straightforward ways to create such an application. Since, I am mainly interested in talking about JavaScript, PHP is outside of the scope of these lessons. But, to be sure, PHP is used in some form for a majority of applications on the World Wide Web. So, if you want to do a lot more with web applications, you should consider learning PHP.

Using Docker to provide PostgreSQL

For the first attempt at using PostgreSQL as a backend database, I will use Docker to create a container that provides the PostgreSQL service. This means that I have to install Docker. Here are the steps that I took on my Ubuntu 24.04 machine.

Installing Docker on Ubuntu 24.04

These steps assume that you have curl installed. If you don’t have curl installed, you can install it using the following command:

$ sudo apt install curl

Once curl has been installed, you can install Docker.

curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"

Hit <enter> to continue

sudo apt update
sudo apt install docker-ce

I ran the following command to add my username to the docker group:

sudo usermod -aG docker <username>

Substitute your username for <username>. Log out, and then log back in. Then the following command should work:

docker image ls

This should show the following headings:

REPOSITORY   TAG       IMAGE ID   CREATED   SIZE

Nothing else will be listed at this point, as you don’t have any Docker images yet.

Creating a Docker image

Here are the steps I did in order to set up a Docker image that would provide the PostgreSQL service:

Creating the Dockerfile

Docker uses a file named Dockerfile to create any image. Here is a Dockerfile that will be used to get PostgreSQL version 16 for the image. In addition, the nano editor will be placed on that image so that you can do some simple editing.

/path/to/postgres/Dockerfile

FROM postgres:16
RUN apt-get update -y && apt-get install -y nano

Making a script to create the image from the Dockerfile

For the purposes of this lesson, I am putting all my working files inside a folder called postgres. I will refer to this location as /path/to/postgres.

/path/to/postgres/create_image.sh

#!/bin/bash

docker build -t pg_img .

Note the . at the end of the command, to build the image based on the Dockerfile in the current directory. So, make sure you run this script in the same directory as the one you have your Dockerfile stored in.

After running create_img.sh, you can run docker image ls again:

$ docker image ls
REPOSITORY   TAG       IMAGE ID       CREATED         SIZE
pg_img       latest    5145bb1ce1de   5 seconds ago   460MB

So, you can see that the image pg_img has been created.

Creating the Docker container

Now that we have the Docker image, we can use that image to create a Docker container. Here is a script that is used to create the container:

/path/to/postgres/create_container.sh

#!/bin/bash

docker container run -it --name=pg_cont --volume $(pwd)/docs:/docs \
--env POSTGRES_PASSWORD='admin_pass' pg_img

Be sure to create a directory called docs inside the directory that you have the scripts and Dockerfile in. Then, you can run the command. This will make the admnistrator password for the postgres user whatever you set. Use your own password here. Note that this will create a container called pg_cont.

After running create_container.sh, you should see the container being started up. Hit CTRL+C to kill the container. Then, from this point on, you will start the container using the following command:

docker start pg_cont

Once the container starts, you run the following command to get a root console inside the pg_cont container:

$ docker exec -it pg_cont bash

The exec command means that you are running a command. The -it means you want an interactive terminal. pg_cont is the name of the container you want to use, and bash is the name of the command you want to run. That is, you want to get a bash terminal. Running this will give you the following prompt:

root@149a902b6686:/#

Note the user is root and the prompt ends in #. These are both signs that this is a root terminal. So, you run commands from this terminal without having to enter sudo first, because this is a root account inside that container.

If you type ls at the prompt, you will see that one of the directories is named docs. This is the directory that you specified to be shared with the host environment. What this means is that you can place files inside docs that you want to have access to from inside the Docker container. Just be sure to create those files in the host, so that the files are not owned by root in the container. This is where you can place SQL scripts to create and populate your database.

If you type exit, you will exit from the console inside the Docker container.

Connecting to the PostgreSQL server in the Docker container from the host

If you want to connect to the PostgreSQL service from the host, you can do so if you have psql installed. On Ubuntu 24.04, this means you need to install postgresql-client-common and postgresql-16. Here is the command to do this:

$ sudo apt install postgresql-client-common postgresql-16

Determining the IP address for the PostgreSQL service inside the Docker container

We need to determine the IP address for the PostgreSQL service inside the Docker container. This can be done using the docker inspect command. This is how to do that:

$ docker inspect pg_cont | grep -i ipaddress
            "SecondaryIPAddresses": null,
            "IPAddress": "172.17.0.2",
                    "IPAddress": "172.17.0.2",

By using grep you can filter out a lot of the output. As you can see, the IP address we want is 172.17.0.2.

Using psql to connect to PostgreSQL from the host

Now we can connect to Postgresql inside the Docker container from the host:

$ psql -h 172.17.0.2 -U postgres -d postgres

Here -h is for the host, -U is to specify the user and -d specifies the name of the database to connect to.

Fixing a security vulnerability

The way that PostgreSQL is configured inside the Docker container is not very secure. That is because PostgreSQL will not even prompt for a password, and just connect to the database. So, if you ran the command above, you would connect to the postgres database without being prompted for a password. We can fix this by opening a root terminal inside the Docker container and editing the file /var/lib/postgresql/data/pg_hba.conf. Here are the steps to do this:

$ docker exec -it pg_cont bash

This will get you to the root prompt inside the Docker container. From there you use nano to edit /var/lib/postgresql/data/pg_hba.conf

root@b28549e574c6:/# nano /var/lib/postgresql/data/pg_hba.conf

Inside nano, type ALT+G and type 117. This will take you to line 117, and you should see lines that looks like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust

You need to modify that last line by commenting it out and replacing trust with md5 like this:

#local   all             all                                     trust
local   all     all     md5

Then type CTRL+O and hit enter to save the file. Then type CTRL+X to exit from nano. Then, type exit to exit the root prompt. To make these changes effective, you must stop and start the Docker container

$ docker stop pg_cont
$ docker start pg_cont

Now, if you try to connect from the host to PostgreSQL inside the Docker container, you will see that you are prompted for a password. The following shows that failing to enter a password by just hitting enter results in an error message:

$ psql -h 172.17.0.2 -U postgres -d postgres
Password for user postgres:
psql: error: connection to server at "172.17.0.2", port 5432 failed: fe_sendauth: no password supplied
vern@msi-lianli-build:~/Desktop/javascript_webpages/practical_computing/docker_area/postgres

$ psql -h 172.17.0.2 -U postgres -d postgres
Password for user postgres:
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1))
Type "help" for help.

postgres=#

Notice that the second attempt worked as I entered the password that I specified in the create_container.sh script. So, the security vulnerability has been dealt with.

Managing the PostgreSQL database in the Docker container

The first step to managing the PosgreSQL server inside Docker is to create your own superuser account. Then you can change the postgres user account so that anyone finding the create_container.sh script can’t use the password there. So, here are the steps to create your own superuser account:

$ psql -h 172.17.0.2 -U postgres -d postgres
Password for user postgres:
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1))
Type "help" for help.

postgres=# create role myadmin superuser createdb createrole login;
CREATE ROLE
postgres=# alter user myadmin encrypted password 'your_password';
ALTER ROLE
postgres=# create database my_db owner myadmin;
CREATE DATABASE
postgres=# \q

With your new superuser account, you can create a regular user named 'joe'. Here are the steps to follow:

$ psql -h 172.17.0.2 -U myadmin -d my_db
Password for user myadmin:
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1))
Type "help" for help.

my_db=# create role joe login;
CREATE ROLE
my_db=# alter user joe encrypted password 'joepass';
ALTER ROLE
my_db=# grant joe to myadmin;
GRANT ROLE
my_db=# create database joe_db owner joe;
CREATE DATABASE
my_db=# \q

On line 1, I connected to the PostgreSQL server from the host, and I connected to the my_db database that is owned by myadmin. On line 6, I created a regular user named joe. You can see that this is a regular user, because the only privilege granted to that user is login. On line 8, I created the password for joe. You should use your own password here, but I will use this one for my examples to follow. Line 10 grants joe to myadmin. This is so that on line 12, I can create a database and make joe the owner. Without the grant statement on line 10, I would not be able to make joe the owner of joe_db.

Setting up the joe_db database.

The way that I create tables and add test data to the tables is by using SQL scripts. This makes it very easy to set up the equivalent database on another computer. Also, this makes it very easy to reset the tables to contain just the test data. This makes it straightforward when you are developing the front end of the web application.

In the host, I will change to the docs directory where I have my postgresql files. Then, I will create the following SQL script to create the students table.

/path/to/postgres/docs/init.sql

drop table if exists students cascade;

create table students (
    id serial,
    first_name text,
    last_name text,
    major text,
    primary key (id)
);

Next, I will get a root prompt inside the Docker container and run the SQL script as the joe user:

$ docker exec -it pg_cont bash
root@b28549e574c6:/# cd docs
root@b28549e574c6:/docs# ls
init.sql
root@b28549e574c6:/docs# psql -U joe -d joe_db
Password for user joe:
psql (16.9 (Debian 16.9-1.pgdg120+1))
Type "help" for help.

joe_db=> \i init.sql
psql:init.sql:1: NOTICE:  table "students" does not exist, skipping
DROP TABLE
CREATE TABLE
joe_db=> \dt
         List of relations
 Schema |   Name   | Type  | Owner
--------+----------+-------+-------
 public | students | table | joe
(1 row)

On line 1, I get a root prompt inside the Docker container. On line 2, I change into the docs directory. On line 3, I use the ls command to check if init.sql is located inside that directory. On line 5, I connect to the joe_db database as joe. It is very important to do this as joe and not myadmin. That way, all the tables will be owned by joe.

One line 10 I use \i to include the init.sql file. After that runs, I display the tables using the \dt command on line 14. As you can see, the students table has been created, and that table is owned by joe. If I ever need to reset my students table, I can always include init.sql again.

Adding some test data

Next, I added some test data for the students table. I create the file add_test_data.sql inside the same docs directory.

/path/to/postgres/docs/add_test_data.sql

insert into students (first_name, last_name, major) values ('Jane', 'Doe', 'Biology');
insert into students (first_name, last_name, major) values ('John', 'Doe', 'Chemistry');
insert into students (first_name, last_name, major) values ('Bob', 'Simmons', 'Physics');

Here are the steps to include this file, starting from getting a root prompt:

$ docker exec -it pg_cont bash
root@b28549e574c6:/# cd docs
root@b28549e574c6:/docs# ls
add_test_data.sql  init.sql
root@b28549e574c6:/docs# psql -U joe -d joe_db
Password for user joe:
psql (16.9 (Debian 16.9-1.pgdg120+1))
Type "help" for help.

joe_db=> \i add_test_data.sql
INSERT 0 1
INSERT 0 1
INSERT 0 1
joe_db=> select * from students;
 id | first_name | last_name |   major
----+------------+-----------+-----------
  1 | Jane       | Doe       | Biology
  2 | John       | Doe       | Chemistry
  3 | Bob        | Simmons   | Physics
(3 rows)

joe_db=> \q
root@b28549e574c6:/docs# exit
exit

On line 1, I get a root prompt. On line 2 and 3, I change into the docs directory and check to make sure my SQL script is there. On line 5, I connect to the joe_db database as joe. On line 10, I use \i to include add_test_data.sql to the database. As you can see on lines 11-13, three INSERT queries are performed. On line 14, I run a simple SELECT query to see my test data.

Line 22 shows me entering \q to quit out of psql. Line 23 shows me typing exit to exit the root prompt to return to the host.

Installing Node packages.

Now that I have a database with a table and some data, it is time to start developing the routes to interact with the database. This will require installing several Node.js packages.

Although there are some languages, PHP for example, that are made to directly interact with databases, JavaScript is not one of them. So, to connect to a database, a web app using JavaScript and HTML can make use of postgresql clients that are Node.js packages. For PostgreSQL, the name of the Node package is pg. We need to install this as well as the express and cors packages.

Setting up the database and populating it with some test data is considered the backend of the application. The routes that we will develop to interact with the database can be considered as middleware. Once the routes have been tested, then we can go on to develop the frontend web application. So, always think of the development process as going from the back to the middle and then finally to the front. If we need to add some new feature later on, we follow the same procedure. That is, we start with the backend (the database), modify the middleware (the routes) and test them. Once the middleware has been tested, then the frontend (the web page) can be modified.

This is what I did to install the packages: express, pg and cors

$ cd /path/to/postgresql
$ npm install express pg cors

Creating routes

Now that we have a database that has a table with some test data in it, let’s start working on the routes. A route is used to designate a URL that can be used to interact with the backend database. We will start with something very simple first, just to make sure our first route is working. Here is the file index.js:

/path/to/postgres/index.js

const express = require('express');
const bodyParser = require('body-parser');
const app = express();
const port = 3000;

app.use(bodyParser.json());
app.use(
   bodyParser.urlencoded({
      extended: true
   })
);
app.get('/', (req, resp) => {
   resp.json({ info: 'Express and Postgresql' });
});

app.listen(port, () => {
   console.log('App running on port 3000');
});

Line 1 makes the express module available. Line 2 makes the body-parser module that is part of express available. Line 3 uses express() to create a reference to app. Here app represents the framework for interacting with the backend database. Line 4 sets the listening port to be 3000.

Line 6 makes it so that our app will assume that the body of the incoming request is formatted as JSON. Lines 7-11, make it so that express parses the incoming request with URL-encoded data. This is the kind of data that is associated with HTML forms used to set up POST requests. A POST request sends a parameter or parameters that are needed to carry out the request.

Lines 12-14 define our first route. This is for a GET request, which is a request that does not send any data parameters. The first argument to get() is '/'. This helps to define the URL that is used to make this request. Specifically, this means that the URL to use will be:

*http://localhost:3000/*

The host, since we are running locally, will just be localhost. The port as specified on line 4, and used when the listen() command is given on line 16, will be set to 3000. Finally, tacked on to the end of this URL is the '/'.

Line 13 provides the response to the request. In this case, it is just a JSON string that reads { info: 'Express and Postgresql'}. Lines 16-18 will launch the app and display 'App running on port 3000' to the console. Here is what I did to run the index.js script:

$ cd /path/to/postgresql$ node index.js
App running on port 3000

You can see the message from line 17 is shown in the console.

With app running, you can open up a browser and visit http://localhost:3000/

Here is a screen shot of the view in the browser.

As you can see, our test route works. However, this is just a test route that does not interact with the backend database. So, the next route will be one that interacts with the backend database.

A route to retrieve all the students

Querying the database to return all the students is a useful query to perform. So, the next route will get all of the students. This will be another GET route, and as such does not require the posting of any data parameters in the request. To facilitate the creation of this route, I created another file, queries.js, that will handle the details of connecting to the backend database and querying that database. Here is the first version of queries.js:

/path/to/postgres/queries.js

const Pool = require('pg').Pool;
const pool = new Pool({
   user: 'joe',
   host: '172.17.0.2',
   database: 'joe_db',
   password: 'joepass',
   port: 5432
});
function getStudents(req,resp) {
   pool.query('select * from students order by id asc',
      (error, results) => {
      if (error) {
         throw error;
      }
      resp.status(200).json(results.rows);
   });
}

module.exports = { getStudents };

Line 1 makes use of a pooled connection. This is the type of connection that will be required when connecting to an online cloud database like Supabase. Since that is something that we eventually want to do, we start with this type of connection. Lines 2-8 configure our pooled connection called pool. Line 3 specifies the user for the database. Line 4 specifies the IP address to use for the host. Remember that we determined this IP address here when we tested connecting to the Docker container (pg_cont). Line 5 specifies the name of the database and line 6 specifies the password for joe connecting to the joe_db database. Line 7 species the port to listen to for PostgreSQL.

Lines 9-17 define the getStudents() function. Note that this function (as with all functions that interact with the backend) has the parameters: req and resp. These parameters refer to the request and response, respectively. Lines 10-15 run the query and throw an error if the query fails. If the query is successful, line 15 formats the response to have a status of 200 (successful response) and a body that consists of a JSON string containing all the rows of the query results returned. Note on line 10, that the SELECT query is defined as 'select * from students order by id asc'. This means to return all fields for all the rows in the students table, sorted by the id field in ascending order.

Line 19 exports the getStudents() function, so that it can be used in any JavaScript module that requires queries.js.

We need to modify index.js to make use of queries.js. Here is the new version of index.js:

/path/to/postgres/index.js

const express = require('express');
const bodyParser = require('body-parser');
const db = require('./queries.js');
const app = express();
const port = 3000;

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

app.get('/', (req, resp) => {
   resp.json({ info: 'Express and Postgresql' });
});
app.get('/students',db.getStudents);

app.listen(port, () => {
   console.log('App running on port 3000');
});

The new lines are 3 and 17. Line 3 makes the contents of queries.js available as the reference db (think database). This reference is used on lin 17 to call the getStudents() function. Note that this is why the getStudents() function in queries.js needed to be exported.

Testing the route to get all the students

To start testing the route, I made use of the curl command. This kind of testing is useful before trying to test the route in a browser. That’s because using curl from the command line will usually show better diagnostics in case an error occurs. Here is how to test the route using curl.

$ curl http://localhost:3000/students

So, if you forgot to start up the express server, you would see this error message:

$ curl http://localhost:3000/students
curl: (7) Failed to connect to localhost port 3000 after 0 ms: Couldn't connect to server

You can tell this is the express server as it is referencing port 3000. If you start up the express server (by running node index.js), you could get the following error if the Docker container, pg_cont, is not running:

$ curl http://localhost:3000/students
curl: (52) Empty reply from server

The reply is empty, because pg_cont is not running and therefore cannot return any results. Also, you will see that the express server has stopped with this error message:

App running on port 3000
/home/vern/Desktop/javascript_webpages/practical_computing/docker_area/postgres/queries.js:13
         throw error;
         ^

Error: connect EHOSTUNREACH 172.17.0.2:5432
    at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1636:16) {
  errno: -113,
  code: 'EHOSTUNREACH',
  syscall: 'connect',
  address: '172.17.0.2',
  port: 5432
}

If you start up pg_cont (docker start pg_cont), start up the express server and run the curl command again, you should see this:

$ curl http://localhost:3000/students
[{"id":1,"first_name":"Jane","last_name":"Doe","major":"Biology"},{"id":2,"first_name":"John","last_name":"Doe","major":"Chemistry"},{"id":3,"first_name":"Bob","last_name":"Simmons","major":"Physics"}]

Now that the curl command succeeded, I checked the route in the browser by following these steps:

$ cd /path/to/postgresql
$ docker start pg_cont
$ node index.js

So, I changed into the directory that contains index.js and made sure that the Docker container, pg_cont, was running. Then, I started up the express server using node index.js. After seeing the server start up, I visited localhost:3000/students in a browser. Here is a screen shot of what you should see. Note that I checked to box for Pretty-print.

Starting the front end application

Now that we have tested the new route, we can start working on the front end of the application. This is the HTML file that will be the web page that the user interacts with to run the application. Here is the code for students_app.html:

/path/to/postgres/students_app.html

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <script src="https://unpkg.com/axios/dist/axios.min.js"></script>
    <script>
      document.addEventListener('DOMContentLoaded', init);
      let students_data = [];

      async function getStudents() {
        try {
          students_data = await axios({
            method: 'get',
            url: 'http://localhost:3000/students'
          });
          console.log(students_data);
        }
        catch (error) {
          console.log(error);
        }
      }

      function init() {
        getStudents();
      }
    </script>
  </head>
  <body></body>
</html>

Line 4 is added to ensure that the page will be encoded so that the UTF-8 character set is used. This will be important for web pages that gather user input that will be displayed. Line 5 includes a reference to a CDN source for the Axios library. Axios works directly in a web browser to provide the ability to send requests and receive responses from Node.js through express. So, to build a front end that works with express, we will usually make use of Axios.

Lines 6-26 define the <script> element that drives our web app. Line 7 makes it so that when the DOM content finishes loading, the init() function will be called. Line 8 defines students_data as an array. This array will be used to store the students data. Lines 23-25 define the init() function. At this point, all init() does is call the getStudents() function.

Lines 10-21 define the getStudents() function. This function is declared as async as we need to await the axios request to the database. Lines 11-17 define a try block that awaits an axios call to the express server. For a simple GET request like this, there are only two parameters that are needed. The first parameter, method, specifies the type of request. In this case, the type is 'get'. The second parameter is the URL that connects to the route set up by express. This is the URL that we have already tested. Lines 18-20 will catch any errors encountered in the axios request, and display those errors in the console.

Make sure that you are running the express server (node index.js) and that the Docker container, pg_cont, is running. Then, you can open a browser to the students_app.html page. The following screen shot shows that a CORS error has occurred.

Dealing with the CORS error

CORS is an acronymn for Cross-Origin Resource Sharing. A CORS error means that the browser has blocked resources coming from an origin outside the domain of the server that is serving out the web page. In our case, this is the express server that is listening on port 3000. So, any request coming from a domain outside of localhost:3000 will result in a CORS error, as we can see.

A quick fix to this can be made by editing the index.js file. Here is a modified version of index.js:

/path/to/postgres/index.js

const express = require('express');
const bodyParser = require('body-parser');
const db = require('./queries.js');
const cors = require('cors');
const app = express();
const port = 3000;

app.use(cors());
app.use(bodyParser.json());
app.use(
   bodyParser.urlencoded({
      extended: true
   })
);

app.get('/', (req, resp) => {
   resp.json({ info: 'Express and Postgresql' });
});
app.get('/students',db.getStudents);

app.listen(port, () => {
   console.log('App running on port 3000');
});

The lines are 4 and 8. Line 4 makes the cors package available. Line 8 makes it so that the server uses cors to allow cross-origin requests.

You need to stop and then restart the express server. So, hit CTRL+C to stop that server and then run node index.js to start it up again. The following screen shot shows that the CORS error no longer occurs. The data has been expanded in the console so you can see the contents:

This is not a very good fix. The way that we made use of the CORS package would allow the requests to come in from any domain. To fix this, we need to serve out our web application so that we allow only requests coming from the domain our web application is being served from.

Using http-server

The simplest way to do this is to use the Node.js package, http-server. If you have not already installed this, you can install it globally from the command line like this:

npm install -g http-server

Then, you want to run the following commands:

$ cd /path/to/posgresql
$ http-server -a localhost -p 8000

This will allow serving out our web application using the following URL: localhost:3000/students_app.html

Before we do this, let’s modify index.js:

/path/to/postgres/index.js

const express = require('express');
const bodyParser = require('body-parser');
const db = require('./queries.js');
const cors = require('cors');
const app = express();
const port = 3000;

app.use(cors({
  origin: 'http://localhost:8000'
}));
app.use(bodyParser.json());
app.use(
   bodyParser.urlencoded({
      extended: true
   })
);

app.get('/', (req, resp) => {
   resp.json({ info: 'Express and Postgresql' });
});
app.get('/students',db.getStudents);
app.listen(port, () => {
   console.log('App running on port 3000');
});

The new lines are 8-10. Line 9 specifies that the domain that will be allowed to make requests is http://localhost:8000. Requests from any other domain will result in a CORS error.

Here are the steps to follow:

Stop index.js. Restart again using: node index.js
Change into the directory you have students_app.html stored in. The run the following:

$ http-server -a localhost -p 8000
Starting up http-server, serving ./

http-server version: 14.1.1

http-server settings:
CORS: disabled
Cache: 3600 seconds
Connection Timeout: 120 seconds
Directory Listings: visible
AutoIndex: visible
Serve GZIP Files: false
Serve Brotli Files: false
Default File Extension: none

Available on:
  http://localhost:8000
Hit CTRL-C to stop the server

Now you can view the page localhost:8000/students_app.html in the browser. Here is a screen shot.

Using a HTML table in the web application

Now that we can get the students data, let’s make it so that our web application puts the students data into a HTML table. Here is the new version of students_app.html:

/path/to/postgres/students_app.html

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <script src="https://unpkg.com/axios/dist/axios.min.js"></script>
    <script>
      document.addEventListener('DOMContentLoaded', init);
      let students_data = [];
      let students_tbody;

      async function getStudents() {
        try {
          const students = await axios({
            method: 'get',
            url: 'http://localhost:3000/students'
          });
          students_data = students.data;
          console.log(students_data);
          updateStudentsTable();
        }
        catch (error) {
          console.log(error);
        }
      }

      function updateStudentsTable() {
        for (let i = 0; i < students_data.length; i++) {
          const student = students_data[i];
          let tr = document.createElement('tr');
          let td = document.createElement('td');
          let content = document.createTextNode(student.first_name);
          td.appendChild(content);
          tr.appendChild(td);
          td = document.createElement('td');
          content = document.createTextNode(student.last_name);
          td.appendChild(content);
          tr.appendChild(td);
          td = document.createElement('td');
          content = document.createTextNode(student.major);
          td.appendChild(content);
          tr.appendChild(td);
          students_tbody.appendChild(tr);
        }
      }

      function init() {
        students_tbody = document.getElementById('students_tbody');
        getStudents();
      }
    </script>
  </head>
  <body>
    <h1>Students Table</h1>
    <table border="1">
      <thead>
        <tr>
          <th>First Name</th>
          <th>Last Name</th>
          <th>Major</th>
        </tr>
      </thead>
      <tbody id="students_tbody"></tbody>
    </table>
  </body>
</html>

The new lines are 9, 13, 17, 19, 26-44, 47 and 53-63. Lines 53-63 add the markup for a heading and a HTML table. Line 53 just puts a <h1> heading before the students table. Lines 54-63 define the students table. Lines 55-61 define the <thead> section and gives the columns heading names. Line 61 defines a <tbody> with an id="students_tbody" attribute. This is where the data rows for the students will be placed.

Line 9 defines students_tbody which will hold a reference to the <tbody> element with that id attribute. Line 47, gets that reference and stores it.

Line 13 was modified so that the results returned from the axios call are stored in the constant students. If you look back at the previous screen shots, you can see that the actual array holding the students data is in the data property of the object that was returned. So, on line 17, we now correctly assign that data to the students_data array. Line 19 calls the updateStudentsTable() function. Since we have obtained the data and stored it inside of students_data, we can now fill in the HTML table.

Lines 26-44 define the updateStudentsTable() function. Lines 27-43 define an index-based for loop that will populate the students table. Line 28 just stores students_data[i] inside of student for easier reference. Line 29 creates a <tr> element. Line 30 creates a <td> element. Line 31 creates a Text Node from the student’s first_name. Line 32 puts this Text Node into to the <td> element and line 33 puts that <td> element into the <tr> element. Lines 34-41 do the same thing for the student’s last_name and major, respectively. Finally, on line 42, the <tr> element is placed inside the <tbody id="students_tbody"> element. This will cause all the students to be displayed in the HTML table.

Here is a screen shot of the web application now:

Adding a new student

Now that we know that we can read from the database, let’s put in the ability to add a new student. The database table is already set up, so we can start by working on the middleware. That is, let’s make a route to add a student. Here is the new version of queries.js that adds the function createStudent() that will add a new student to the students table:

/path/to/postgres/queries.js

const Pool = require('pg').Pool;
const pool = new Pool({
   user: 'joe',
   host: '172.17.0.2',
   database: 'joe_db',
   password: 'joepass',
   port: 5432
});
function getStudents(req,resp) {
   pool.query('select * from students order by id asc',
      (error, results) => {
      if (error) {
         throw error;
      }
      resp.status(200).json(results.rows);
   });
}
function createStudent(req, resp) {
   const { first_name, last_name, major } = req.body;
   pool.query('insert into students (first_name, last_name, major) ' +
      'values ($1, $2, $3)', [first_name, last_name, major],
      (error, results) => {
         if (error) {
            throw error;
         }
         resp.status(201).send('Student added');
      }
   );
}

module.exports = { getStudents, createStudent };

The lines are 18-29 and 31. Lines 18-29 define the createStudent() function. This function will insert a new student into the students table. Line 19 takes the request body and assigns it to the variables first_name, last_name and major. This means that the request will have passed along these parameters in that order. Lines 20-28 run the INSERT query. In this case we are using data binding to perform the insert. This means that we set up the query string and use placeholder variables ($1, $2 and $3). That query string is followed by an array that contains the values to be substituted into those placeholders. That is, the data is bound to the query in this step. This prevents SQL Injection, and is the recommended way to insert any values that come from the user. Lines 22-27 form an anonymous function that has two parameters: error and results. If an error occurs, then line 24 will throw that error and nothing is inserted into the database table. If no error occurs, line 26 sets the status to 201 and sends the message 'Student added' to the console. So, if we test the createStudent() function using curl or Postman, we should be able to see that response if the student is successfully added.

To make this work, we need to modify index.js to set a route that uses the createStudent() function. Here is the new version of index.js:

/path/to/postgres/index.js

const express = require('express');
const bodyParser = require('body-parser');
const db = require('./queries.js');
const cors = require('cors');
const app = express();
const port = 3000;

app.use(cors({
  origin: 'http://localhost:8000'
}));
app.use(bodyParser.json());
app.use(
   bodyParser.urlencoded({
      extended: true
   })
);

app.get('/', (req, resp) => {
   resp.json({ info: 'Express and Postgresql' });
});
app.get('/students',db.getStudents);
app.post('/students',db.createStudent)

app.listen(port, () => {
   console.log('App running on port 3000');
});

The new line is 22. This specifies a POST request. This is a POST request as the request is sending data to the express server. The route specified means the URL that will be used is http://localhost:3000/students. This looks like the same URL as for getting the students, but remember that this is a POST request, not a GET request.

Testing new route with curl

Let’s test this new route using curl. Make sure that the pg_cont container is running and that you have started up the express server using node index.js. Then, run the following curl command in a different terminal (from the one that you ran node index.js)

$ curl -d "first_name=Carol&last_name=Sanders&major=Chemistry" http://localhost:3000/students
Student added

Let’s double check by using psql to connect to the joe_db database from the host:

$ psql -h 172.17.0.2 -U joe -d joe_db
Password for user joe:
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1))
Type "help" for help.

joe_db=> select * from students;
 id | first_name | last_name |   major
----+------------+-----------+-----------
  1 | Jane       | Doe       | Biology
  2 | John       | Doe       | Chemistry
  3 | Bob        | Simmons   | Physics
  4 | Carol      | Sanders   | Chemistry
(4 rows)

As you can see, the new student was added. If you are still running the http-server for the front end application, you could see what is shown in the following screen shot.

Testing the route using Postman

You can download the Linux (x64) version of Postman here. The link on that page will download a .tar.gz file that can be expanded. If you open the executable file, you can just use the free lightweight API, as that is good enough for our purposes. Here is a screen shot showing how we can add a student using Postman.

The first thing you need to do is select POST as the type of request. Then for Params, use Body and the x-www-form-urlencoded type. Enter in the keys and values as shown, then press Send. You will see the response 'Student added' down below.

If you query the students table using psql, you will see this now:

joe_db=> select * from students;
 id | first_name | last_name |   major
----+------------+-----------+-----------
  1 | Jane       | Doe       | Biology
  2 | John       | Doe       | Chemistry
  3 | Bob        | Simmons   | Physics
  4 | Carol      | Sanders   | Chemistry
  5 | Jim        | Reynolds  | Biology
(5 rows)

Modifying the web application to allow adding a student

Now that we have tested the middleware, we can modify students_app.html to allow adding a student from this web application. Here is a brief outline of the steps that I carried out to do this:

I made the changes going from the bottom of the HTML file and moved to the top. That means that I modified the markup first, then went above to add the JavaScript code to make use of the modified markup. Doing things this way allows looking at the results of the markup changes before any JavaScript is added..
Add a <button> for adding a student above the table header. This button must be referenced by JavaScript, so it needs to have an id attribute.
Added the markup for a <dialog> element that will allow input of the first_name, last_name and major. This <dialog> element also contains a Cancel button and an Ok button. All three of the <input> elements will need id attributes. The Cancel and Ok buttons will also need id attributes.
Modified the init() function inside of the <script> element. The modifications involve getting references to the new <button> elements. Those references are used to add Event Listeners (functions) that will handle clicking on those <button> elements.
Added an async function called handleAdd() that will gather the user input from the add dialog box, and use these to form an Axios request. That request will use the new route created in middleware. If adding the student is successful, the updateStudentsTable() function will be called to display all the students including the newly added one.
Created a function called removeChildren() that will empty out the <tbody id="students_tbody"> element before updating the students table. This prevents the previous table rows from being shown with all the current table rows from the students table.

Here is the new version of students_app.html:

/path/to/postgres/students_app.html

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <script src="https://unpkg.com/axios/dist/axios.min.js"></script>
    <script>
      document.addEventListener('DOMContentLoaded', init);
      let students_data = [];
      let students_tbody;
      let add_dlg;

      async function getStudents() {
        try {
          const students = await axios({
            method: 'get',
            url: 'http://localhost:3000/students'
          });
          students_data = students.data;
          console.log(students_data);
          updateStudentsTable();
        }
        catch (error) {
          console.log(error);
        }
      }

      function handleAdd() {
        console.log('handleAdd called');
      }

      function updateStudentsTable() {
        for (let i = 0; i < students_data.length; i++) {
          const student = students_data[i];
          let tr = document.createElement('tr');
          let td = document.createElement('td');
          let content = document.createTextNode(student.first_name);
          td.appendChild(content);
          tr.appendChild(td);
          td = document.createElement('td');
          content = document.createTextNode(student.last_name);
          td.appendChild(content);
          tr.appendChild(td);
          td = document.createElement('td');
          content = document.createTextNode(student.major);
          td.appendChild(content);
          tr.appendChild(td);
          students_tbody.appendChild(tr);
        }
      }

      function init() {
        students_tbody = document.getElementById('students_tbody');
        add_dlg = document.getElementById('add_dlg');
        const add_button = document.getElementById('add_button');
        add_button.addEventListener('click', () => { add_dlg.showModal(); });
        const add_cancel = document.getElementById('add_cancel');
        add_cancel.addEventListener('click', () => { add_dlg.close(); });
        const add_ok = document.getElementById('add_ok');
        add_ok.addEventListener('click', handleAdd);
        getStudents();
      }
    </script>
  </head>
  <body>
    <h1>Students Table</h1>
    <button id="add_button">Add student</button><br>
    <dialog id="add_dlg">
      First Name:
      <input type="text" id="add_fn_box"><br>
      Last Name:
      <input type="text" id="add_ln_box"><br>
      Major:
      <input type="text" id="add_maj_box"><br>
      <br>
      <button id="add_cancel">Cancel</button>
      <button id="add_ok">Ok</button>
    </dialog>
    <table border="1">
      <thead>
        <tr>
          <th>First Name</th>
          <th>Last Name</th>
          <th>Major</th>
        </tr>
      </thead>
      <tbody id="students_tbody"></tbody>
    </table>
  </body>
</html>

The new lines are 10, 27-29, 54-59 and 66-77. Lines 66 accomplishes step 2 from above by adding the add button with an id="add_button" attribute. Lines 67-77 accomplish step 3 from above by creating the <dialog> element with id values for all the elements that need to be referenced by JavaScript.

Lines 54-59 accomplish step 4 from above. Line 54 gets a reference to the add button and line 55 makes it so that the add dialog box is shown when the add button is clicked. Line 56 gets a reference to the add cancel button and line 57 makes it so that the add dialog box is closed when that cancel button is clicked. Line 58 gets a reference to the add ok button and line 59 makes it so that the handleAdd() function is called when that ok button is clicked.

Lines 27-29 define an early version of the handleAdd() function. This function will have to be declared as async eventually, but for now, all it does is display 'handleAdd called' to the console.

Line 10 defines the add_dlg variable that is used to hold a reference to the add dialog box element.

Here is a screen shot of the web application that shows the console after the add ok button was clicked.

Now that these modifications have been tested, it is time to modify the handleAdd() function so that it gathers the user input from the add dialog box and makes an Axios call to use the route we created in the middleware. That is step 5 from above. Finally, we will create the removeChildren() function that clears out the <tbody> element before adding all the students from the database back in. Here is the new version of students_app that does that.

/path/to/postgres/students_app.html

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <script src="https://unpkg.com/axios/dist/axios.min.js"></script>
    <script>
      document.addEventListener('DOMContentLoaded', init);
      let students_data = [];
      let students_tbody;
      let add_dlg;

      function removeChildren(elem) {
        while (elem.childNodes.length > 0) {
          elem.removeChild(elem.childNodes[0]);
        }
      }

      async function getStudents() {
        try {
          const students = await axios({
            method: 'get',
            url: 'http://localhost:3000/students'
          });
          students_data = students.data;
          console.log(students_data);
          updateStudentsTable();
        }
        catch (error) {
          console.log(error);
        }
      }

      async function handleAdd() {
        const add_fn_box = document.getElementById('add_fn_box');
        let first_name = add_fn_box.value.trim();
        const add_ln_box = document.getElementById('add_ln_box');
        let last_name = add_ln_box.value.trim();
        const add_maj_box = document.getElementById('add_maj_box');
        let major = add_maj_box.value.trim();
        try {
          await axios({
            method: 'post',
            url: 'http://localhost:3000/students',
            data: {
              first_name: first_name,
              last_name: last_name,
              major: major
            }
          });
          getStudents();
          add_dlg.close();
        }
        catch (error) {
        }
      }

      function updateStudentsTable() {
        removeChildren(students_tbody);
        for (let i = 0; i < students_data.length; i++) {
          const student = students_data[i];
          let tr = document.createElement('tr');
          let td = document.createElement('td');
          let content = document.createTextNode(student.first_name);
          td.appendChild(content);
          tr.appendChild(td);
          td = document.createElement('td');
          content = document.createTextNode(student.last_name);
          td.appendChild(content);
          tr.appendChild(td);
          td = document.createElement('td');
          content = document.createTextNode(student.major);
          td.appendChild(content);
          tr.appendChild(td);
          students_tbody.appendChild(tr);
        }
      }

      function init() {
        students_tbody = document.getElementById('students_tbody');
        add_dlg = document.getElementById('add_dlg');
        const add_button = document.getElementById('add_button');
        add_button.addEventListener('click', () => { add_dlg.showModal(); });
        const add_cancel = document.getElementById('add_cancel');
        add_cancel.addEventListener('click', () => { add_dlg.close(); });
        const add_ok = document.getElementById('add_ok');
        add_ok.addEventListener('click', handleAdd);
        getStudents();
      }
    </script>
  </head>
  <body>
    <h1>Students Table</h1>
    <button id="add_button">Add student</button><br>
    <dialog id="add_dlg">
      First Name:
      <input type="text" id="add_fn_box"><br>
      Last Name:
      <input type="text" id="add_ln_box"><br>
      Major:
      <input type="text" id="add_maj_box"><br>
      <br>
      <button id="add_cancel">Cancel</button>
      <button id="add_ok">Ok</button>
    </dialog>
    <table border="1">
      <thead>
        <tr>
          <th>First Name</th>
          <th>Last Name</th>
          <th>Major</th>
        </tr>
      </thead>
      <tbody id="students_tbody"></tbody>
    </table>
  </body>
</html>

The new lines are 12-16, 34-54 and 58. Lines 12-16 define the removeChildren() function. This is used to remove all the children of any HTML container element. This function is used to clear the <tbody> element each time we get the data from the backend database to populate the table that <tbody> resides in.

Lines 34-54 provide the functionality for the handleAdd() function. Lines 34-39 get references to the input text boxes and read the values from those boxes into the variables first_name, last_name and major, respectively. Lines 40-52 define a try block that attempts to use Axios to send a POST request. Since it is a POST request, you need to include the data property and place the values of all the parameters you want to pass as part of the POST test. (A GET request does not send anything, so does not have a data property.) If the POST request succeeds, then the getStudents() function is called. Remember that *getStudents() by calling the updateStudentsTable() function. So, this will update the students table on the page. Finally, on line 51, the add dialog box is closed. Lines 53-54 define a catch block. This would come into play if the user hits the Cancel button, or if somehow the insert into the students table does not work. You could display the error to the console, but if an error occurs, the express server will show an error and stop functioning correctly. So, if adding the student does not work, make sure you check the terminal where node index.js was running. That will give you an idea of what is going wrong.

Resetting the students table

Since the number of students has increased since the beginning, I assumed that I am in the situation where I wanted to reset the database before actually adding a student in the web application. So, here is what I did to reset the students table.

$ cd /path/to/postgres/docs$ psql -h 172.17.0.2 -U joe -d joe_db
Password for user joe:
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1))
Type "help" for help.

joe_db=> \i init.sql
DROP TABLE
CREATE TABLE
joe_db=> \i add_test_data.sql
INSERT 0 1
INSERT 0 1
INSERT 0 1
joe_db=>

So, now students is back to the original three students. Let’s add the student George Brown who is majoring in Math. The following is a screen shot showing the add dialog box before hitting Ok.

The next screen shot shows that this student was added and is shown in the updated table:

Editing an existing student

Now that we can add a student we have part of the main functionality of a web page that uses a backend database. This main functionality is often referred to the the acronymn CRUD. C is for create (i.e. add a student), R is for read (i.e. get students), U is for update (i.e. editing a student) and D is for delete (deleting a student). So, we have the C and R implemented, so now let’s add the U or update feature.

To edit a student, we need to have a way of selecting the student to be edited. If you look back at the Loan calculator project, you can see that this is done by adding Event Listeners to respond to clicking on a row. Clicking on a row is a way of selecting which row (which student) that is to be edited.

Modifying the middleware

What we need to do is go back to the middleware and add two routes. One route will be used to get a student based on the student’s id. The other route will be to perform an UPDATE query on the students table.

Let’s start with the route that will return a student record based on the student’s id. This route will be used to help display the current field values for a student. When you click on a table row, that event will return the student’s id. So, if we can query the database to get the student record based on that id, that will contain the current student’s properties for editing. Here is the modified version of queries.js that adds a function that performs such a query:

/path/to/postgres/queries.js

const Pool = require('pg').Pool;
const pool = new Pool({
   user: 'joe',
   host: '172.17.0.2',
   database: 'joe_db',
   password: 'joepass',
   port: 5432
});
function getStudents(req,resp) {
   pool.query('select * from students order by id asc',
      (error, results) => {
      if (error) {
         throw error;
      }
      resp.status(200).json(results.rows);
   });
}
function createStudent(req, resp) {
   const { first_name, last_name, major } = req.body;
   pool.query('insert into students (first_name, last_name, major) ' +
      'values ($1, $2, $3)', [first_name, last_name, major],
      (error, results) => {
         if (error) {
            throw error;
         }
         resp.status(201).send('Student added');
      }
   );
}
function getStudentById(req, resp) {
  const id = Number(req.params.id);
  pool.query('select * from students where id=$1', [id],
     (error, results) => {
        if (error) {
           throw error;
        }
        resp.status(200).json(results.rows);
     }
   );
}
module.exports = { getStudents, createStudent, getStudentById };

The new lines are 30-40 and 41. Lines 30-40 define the getStudentById() function. Line 31 will look at the request parameters for the id. When you see the actual route defined later in index.js, you will see that this id parameter will be part of the URL. This is different from the previous POST request that used the request body to pass the data used in that POST request. Note on line 31 that the Number() function is used to convert the id which is passed as a string, into a number. Lines 32-39 define the SELECT query to be performed. Note that this starts with the SQL string that contains a $1 as a placeholder variable. Following that string is an array that contains the values to be substituted into the placeholder variable(s). This data binding prevents SQL injection. Lines 33-38 define an anonymous function that will throw an error if an error occurs. If no error occurs, line 37 returns the student record in JSON format and sets the STATUS to 200.

Line 41 adds the getStudentById() function to the list of exported functions.

To make use of the new queries.js, we need to modify the index.js function. Here is the new version of index.js:

/path/to/postgres/index.js

const express = require('express');
const bodyParser = require('body-parser');
const db = require('./queries.js');
const cors = require('cors');
const app = express();
const port = 3000;

app.use(cors({
  origin: 'http://localhost:8000'
}));
app.use(bodyParser.json());
app.use(
   bodyParser.urlencoded({
      extended: true
   })
);

app.get('/', (req, resp) => {
   resp.json({ info: 'Express and Postgresql' });
});
app.get('/students',db.getStudents);
app.post('/students',db.createStudent);
app.get('/students/:id', db.getStudentById);

app.listen(port, () => {
   console.log('App running on port 3000');
});

The new line is line 23. Note that this is a GET request. The route specifies that the URL to be used will look like this: http://localhost:3000/students/2, if we want to get the student with an id = 2. So, req.params are values that are passed as part of the URL. That is a requirement of a GET request. If you need to pass multiple values for a request, you need to make a POST request and the values are passed in req.body in the data property.

We can test this using curl. Make sure that you stop and then restart node index.js. Then, you can run the following:

$ curl http://localhost:3000/students/2
[{"id":2,"first_name":"John","last_name":"Doe","major":"Chemistry"}]

As you can see, the route works. Therefore, we can work on the route that involves running the UPDATE query when you have finished editing a student’s record. Here is the new version of queries.js:

/path/to/postgres/queries.js

const Pool = require('pg').Pool;
const pool = new Pool({
   user: 'joe',
   host: '172.17.0.2',
   database: 'joe_db',
   password: 'joepass',
   port: 5432
});
function getStudents(req,resp) {
   pool.query('select * from students order by id asc',
      (error, results) => {
      if (error) {
         throw error;
      }
      resp.status(200).json(results.rows);
   });
}
function createStudent(req, resp) {
   const { first_name, last_name, major } = req.body;
   pool.query('insert into students (first_name, last_name, major) ' +
      'values ($1, $2, $3)', [first_name, last_name, major],
      (error, results) => {
         if (error) {
            throw error;
         }
         resp.status(201).send('Student added');
      }
   );
}
function getStudentById(req, resp) {
  const id = Number(req.params.id);
  pool.query('select * from students where id=$1', [id],
     (error, results) => {
        if (error) {
           throw error;
        }
        resp.status(200).json(results.rows);
     });
}
function updateStudent(req, resp) {
  const id = Number(req.params.id);
  const { first_name, last_name, major } = req.body;
  pool.query('update students set first_name=$1, last_name=$2, ' +
     'major=$3 where id=$4',[first_name, last_name, major, id],
     (error, results) => {
        if (error) {
           throw error;
        }
        resp.status(200).send('Student updated');
     }
  );
}

module.exports = { getStudents, createStudent, getStudentById, updateStudent };

The new lines are 40-51 and 54. Lines 40-51 define the updateStudent() function. Line 41 obtains the id passed as part of the URL. Note that the Number() function is used to convert that string to a number. Line 42 gets all the parameter passed as data from the request body and stores them in the constants, first_name, last_name and major. So, for an update request, the request body is used to pass the edited values for the student, and the request params is used to pass the id of the student to be updated. Lines 43-51 define an UPDATE query that consists of the SQL string that contains placeholder variables for all the values coming from the user. That string is followed by an array that contains the values that must be substituted into that SQL string placeholder variables. Lines 45-50 define an anonymous function that will throw any error that occurs, and send a message to the console, 'Student updated', if no error occurs. Line 54 adds the updateStudent function to the list of exported functions.

To make use of the updateStudent() function, we need to modify index.js. Here is the new version of index.js:

/path/to/postgres/index.js

const express = require('express');
const bodyParser = require('body-parser');
const db = require('./queries.js');
const cors = require('cors');
const app = express();
const port = 3000;

app.use(cors({
  origin: 'http://localhost:8000'
}));
app.use(bodyParser.json());
app.use(
   bodyParser.urlencoded({
      extended: true
   })
);

app.get('/', (req, resp) => {
   resp.json({ info: 'Express and Postgresql' });
});
app.get('/students',db.getStudents);
app.post('/students',db.createStudent);
app.get('/students/:id', db.getStudentById);
app.put('/students/:id', db.updateStudent);

app.listen(port, () => {
   console.log('App running on port 3000');
});

The new line is 24. This line adds a PUT route that specifies that the URL to be used will look like: http://localhost:3000/students/2, if we want to update the student with an id=2.

This route can be tested using curl, but it is easier to test using Postman. So, that is what is shown next:

Note that the type of request is set to PUT and the URL used is http://localhost:3000/students/2. The params are placed in the Body and they are formatted as x-www-form-urlencoded. We need to list all three keys and I changed the first_name and major. Finally, clicking on Send resulted in the message 'Student updated'. If you have the express server running (node index.js) and the http-server running, then you should see the following view for the web application:

=== Modifying the web front-end

Now that the new *route* has been tested and found to work, let's modify *students_app.html* to add the editing feature.  Here are the steps that I followed:

Add to the markup to create a <dialog id="edit_dlg"> that has the appropriate <input> elements and <button> elements for editing a student.
Add a global variable, edit_dlg, that will contain the reference to the edit dialog box.
Modify the init() function to get the reference to the edit dialog box. In addition, get references to the edit Cancel button and edit Ok button and assign functions to be called when clicking on them. For the edit Ok button, the handler will be a new function, handleEdit().
Modify the updateStudentsTable() function to add Event Listeners to each <tr> element. In addition, use the student’s id from the database as the id attribute for the <tr> element. Associate showing the edit dialog box with clicking on a <tr> element.
Create the handleEdit() function. Initially, just make this print to the console, 'handleEdit called'.

Here is what the new version of students_app.html looks like:

/path/to/postgres/students_app.html

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <script src="https://unpkg.com/axios/dist/axios.min.js"></script>
    <script>
      document.addEventListener('DOMContentLoaded', init);
      let students_data = [];
      let students_tbody;
      let add_dlg;
      let edit_dlg;

      function removeChildren(elem) {
        while (elem.childNodes.length > 0) {
          elem.removeChild(elem.childNodes[0]);
        }
      }

      async function getStudents() {
        try {
          const students = await axios({
            method: 'get',
            url: 'http://localhost:3000/students'
          });
          students_data = students.data;
          console.log(students_data);
          updateStudentsTable();
        }
        catch (error) {
          console.log(error);
        }
      }

      async function handleAdd() {
        const add_fn_box = document.getElementById('add_fn_box');
        let first_name = add_fn_box.value.trim();
        const add_ln_box = document.getElementById('add_ln_box');
        let last_name = add_ln_box.value.trim();
        const add_maj_box = document.getElementById('add_maj_box');
        let major = add_maj_box.value.trim();
        try {
          await axios({
            method: 'post',
            url: 'http://localhost:3000/students',
            data: {
              first_name: first_name,
              last_name: last_name,
              major: major
            }
          });
          getStudents();
          add_dlg.close();
        }
        catch (error) {
        }
      }

      function handleEdit() {
        console.log('handleEdit called');
      }

      function updateStudentsTable() {
        removeChildren(students_tbody);
        for (let i = 0; i < students_data.length; i++) {
          const student = students_data[i];
          let tr = document.createElement('tr');
          tr.addEventListener('click', () => { edit_dlg.showModal(); });
          const id = student.id;
          tr.setAttribute("id", id);
          let td = document.createElement('td');
          let content = document.createTextNode(student.first_name);
          td.appendChild(content);
          tr.appendChild(td);
          td = document.createElement('td');
          content = document.createTextNode(student.last_name);
          td.appendChild(content);
          tr.appendChild(td);
          td = document.createElement('td');
          content = document.createTextNode(student.major);
          td.appendChild(content);
          tr.appendChild(td);
          students_tbody.appendChild(tr);
        }
      }

      function init() {
        students_tbody = document.getElementById('students_tbody');
        add_dlg = document.getElementById('add_dlg');
        const add_button = document.getElementById('add_button');
        add_button.addEventListener('click', () => { add_dlg.showModal(); });
        const add_cancel = document.getElementById('add_cancel');
        add_cancel.addEventListener('click', () => { add_dlg.close(); });
        const add_ok = document.getElementById('add_ok');
        add_ok.addEventListener('click', handleAdd);
        edit_dlg = document.getElementById('edit_dlg');
        const edit_cancel = document.getElementById('edit_cancel');
        edit_cancel.addEventListener('click', () => { edit_dlg.close(); });
        const edit_ok = document.getElementById('edit_ok');
        edit_ok.addEventListener('click', handleEdit);
        getStudents();
      }
    </script>
  </head>
  <body>
    <h1>Students Table</h1>
    <button id="add_button">Add student</button><br>
    <dialog id="add_dlg">
      First Name:
      <input type="text" id="add_fn_box"><br>
      Last Name:
      <input type="text" id="add_ln_box"><br>
      Major:
      <input type="text" id="add_maj_box"><br>
      <br>
      <button id="add_cancel">Cancel</button>
      <button id="add_ok">Ok</button>
    </dialog>
    <dialog id="edit_dlg">
      First Name:
      <input type="text" id="edit_fn_box"><br>
      Last Name:
      <input type="text" id="edit_ln_box"><br>
      Major:
      <input type="text" id="edit_maj_box"><br>
      <br>
      <button id="edit_cancel">Cancel</button>
      <button id="edit_ok">Ok</button>
    </dialog>
    <table border="1">
      <thead>
        <tr>
          <th>First Name</th>
          <th>Last Name</th>
          <th>Major</th>
        </tr>
      </thead>
      <tbody id="students_tbody"></tbody>
    </table>
  </body>
</html>

The new lines are 11, 58-60, 66-69, 95-99 and 118-128. Lines 118-128 add the markup for an edit dialog box. Line 95 gets the reference to the edit dialog box. Line 96 gets a reference to the edit Cancel button and line 97 makes it so that clicking on that button will close the edit dialog. Line 98 gets a reference to the edit Ok button and line 99 makes it so that clicking on that button will call the handleEdit() function.

Line 67 adds an Event Listener that makes it so what the edit dialog box is shown, when clicking on a <tr> element. Line 68 gets the student’s id is obtained from the database record and line 69 assigns this to the id attribute for the <tr> element. That id will be used to pull up the correct record from the database so that it can be edited.

Line 11 defines the variable, edit_dlg, that is used to hold the reference to the edit dialog box.

The following screen shot shows that a table row has been clicked and the edit Ok button was clicked.

Note that 'handleEdit called' shows up in the console. Also, note that clicking on the Cancel button would have disposed of the edit dialog box.

Enabling the handleEdit function

Now that the web application works up until this point, we can modify the handleEdit() function so that it actually updates the student’s record. At this point, I can see that instead of just displaying the edit dialog box when clicking on a <tr> element, I need another function that will fill in the <input> elements with the values from the database that could be edited. So, I will define a function called showEditDlg() that does this. Then, I will modify handleEdit() so that it takes care of the actual update to the record. I will do this in a couple of steps. For the first step, I will modify students_app.html so that the showEditDlg() function is called when clicking on a <tr>. Then, I will start the showEditDlg() function by obtaining the id from the event.currentTarget and use axios to call the route that gets the student by id. I will merely display the returned student to get just this much to work.

/path/to/postgres/students_app.html

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <script src="https://unpkg.com/axios/dist/axios.min.js"></script>
    <script>
      document.addEventListener('DOMContentLoaded', init);
      let students_data = [];
      let students_tbody;
      let add_dlg;
      let edit_dlg;

      function removeChildren(elem) {
        while (elem.childNodes.length > 0) {
          elem.removeChild(elem.childNodes[0]);
        }
      }

      async function getStudents() {
        try {
          const students = await axios({
            method: 'get',
            url: 'http://localhost:3000/students'
          });
          students_data = students.data;
          console.log(students_data);
          updateStudentsTable();
        }
        catch (error) {
          console.log(error);
        }
      }

      async function handleAdd() {
        const add_fn_box = document.getElementById('add_fn_box');
        let first_name = add_fn_box.value.trim();
        const add_ln_box = document.getElementById('add_ln_box');
        let last_name = add_ln_box.value.trim();
        const add_maj_box = document.getElementById('add_maj_box');
        let major = add_maj_box.value.trim();
        try {
          await axios({
            method: 'post',
            url: 'http://localhost:3000/students',
            data: {
              first_name: first_name,
              last_name: last_name,
              major: major
            }
          });
          getStudents();
          add_dlg.close();
        }
        catch (error) {
        }
      }

      async function showEditDlg(event) {
        const id = Number(event.currentTarget.id);
        console.log(id);
        try {
          const results = await axios({
             method: 'get',
             url: `http://localhost:3000/students/${id}`,
          });
          console.log(results);
        }
        catch (error) {
          console.log(error);
        }
      }

      function handleEdit() {
        console.log('handleEdit called');
      }

      function updateStudentsTable() {
        removeChildren(students_tbody);
        for (let i = 0; i < students_data.length; i++) {
          const student = students_data[i];
          let tr = document.createElement('tr');
          tr.addEventListener('click', showEditDlg);
          const id = student.id;
          tr.setAttribute("id", id);
          let td = document.createElement('td');
          let content = document.createTextNode(student.first_name);
          td.appendChild(content);
          tr.appendChild(td);
          td = document.createElement('td');
          content = document.createTextNode(student.last_name);
          td.appendChild(content);
          tr.appendChild(td);
          td = document.createElement('td');
          content = document.createTextNode(student.major);
          td.appendChild(content);
          tr.appendChild(td);
          students_tbody.appendChild(tr);
        }
      }

      function init() {
        students_tbody = document.getElementById('students_tbody');
        add_dlg = document.getElementById('add_dlg');
        const add_button = document.getElementById('add_button');
        add_button.addEventListener('click', () => { add_dlg.showModal(); });
        const add_cancel = document.getElementById('add_cancel');
        add_cancel.addEventListener('click', () => { add_dlg.close(); });
        const add_ok = document.getElementById('add_ok');
        add_ok.addEventListener('click', handleAdd);
        edit_dlg = document.getElementById('edit_dlg');
        const edit_cancel = document.getElementById('edit_cancel');
        edit_cancel.addEventListener('click', () => { edit_dlg.close(); });
        const edit_ok = document.getElementById('edit_ok');
        edit_ok.addEventListener('click', handleEdit);
        getStudents();
      }
    </script>
  </head>
  <body>
    <h1>Students Table</h1>
    <button id="add_button">Add student</button><br>
    <dialog id="add_dlg">
      First Name:
      <input type="text" id="add_fn_box"><br>
      Last Name:
      <input type="text" id="add_ln_box"><br>
      Major:
      <input type="text" id="add_maj_box"><br>
      <br>
      <button id="add_cancel">Cancel</button>
      <button id="add_ok">Ok</button>
    </dialog>
    <dialog id="edit_dlg">
      First Name:
      <input type="text" id="edit_fn_box"><br>
      Last Name:
      <input type="text" id="edit_ln_box"><br>
      Major:
      <input type="text" id="edit_maj_box"><br>
      <br>
      <button id="edit_cancel">Cancel</button>
      <button id="edit_ok">Ok</button>
    </dialog>
    <table border="1">
      <thead>
        <tr>
          <th>First Name</th>
          <th>Last Name</th>
          <th>Major</th>
        </tr>
      </thead>
      <tbody id="students_tbody"></tbody>
    </table>
  </body>
</html>

The new lines are 58-71 and 82. Line 82 makes it so that the click handler for a <tr> is now the showEditDlg() function. Lines 58-71 define that showEditDlg() function. Line 59 gets the student’s id from event.currentTarget, and stores this as id. Line 60 just prints this id out, so that I can see if the correct id is being obtained. Lines 61-67 define the try block that will get the student’s information from the database. It makes use of one of the routes we just created (and tested). I store the return value in results and on line 66, I print out those results.

Here is a screen shot showing what I see at this point:

As you can see, results is an object with a number of properties. The property we are interested in is data. It turns out that data is an array. This is because there could be (although there should not be) more than one student with the same id. Since id is a primary key field in the database, this should not happen, but the SELECT query does not take that into account. So, what we really want to do is something like this:

*const student = results.data[0];*

So, I will take this into account in the next version of students_app.html. With the actual student record in hand, I can now populate the <input> elements in the dialog box, before displaying the edit dialog box. Then, I can modify handleEditDlg() so that it retrieves the user input from the <input> elements and then use an axios call to the route that updates the students table. Here is the new version of students_app.html that does these things:

/path/to/postgresql/students_app.html

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <script src="https://unpkg.com/axios/dist/axios.min.js"></script>
    <script>
      document.addEventListener('DOMContentLoaded', init);
      let students_data = [];
      let students_tbody;
      let add_dlg;
      let edit_dlg;
      let student_id;

      function removeChildren(elem) {
        while (elem.childNodes.length > 0) {
          elem.removeChild(elem.childNodes[0]);
        }
      }

      async function getStudents() {
        try {
          const students = await axios({
            method: 'get',
            url: 'http://localhost:3000/students'
          });
          students_data = students.data;
          console.log(students_data);
          updateStudentsTable();
        }
        catch (error) {
          console.log(error);
        }
      }

      async function handleAdd() {
        const add_fn_box = document.getElementById('add_fn_box');
        let first_name = add_fn_box.value.trim();
        const add_ln_box = document.getElementById('add_ln_box');
        let last_name = add_ln_box.value.trim();
        const add_maj_box = document.getElementById('add_maj_box');
        let major = add_maj_box.value.trim();
        try {
          await axios({
            method: 'post',
            url: 'http://localhost:3000/students',
            data: {
              first_name: first_name,
              last_name: last_name,
              major: major
            }
          });
          getStudents();
          add_dlg.close();
        }
        catch (error) {
        }
      }

      async function showEditDlg(event) {
        const id = Number(event.currentTarget.id);
        student_id = id;
        try {
          const results = await axios({
             method: 'get',
             url: `http://localhost:3000/students/${id}`,
          });
          const student = results.data[0];
          const edit_fn_box = document.getElementById('edit_fn_box');
          edit_fn_box.value = student.first_name;
          const edit_ln_box = document.getElementById('edit_ln_box');
          edit_ln_box.value = student.last_name;
          const edit_maj_box = document.getElementById('edit_maj_box');
          edit_maj_box.value = student.major;
          edit_dlg.showModal();
        }
        catch (error) {
          console.log(error);
        }
      }

      async function handleEdit() {
        const edit_fn_box = document.getElementById('edit_fn_box');
        let first_name = edit_fn_box.value.trim();
        const edit_ln_box = document.getElementById('edit_ln_box');
        let last_name = edit_ln_box.value.trim();
        const edit_maj_box = document.getElementById('edit_maj_box');
        let major = edit_maj_box.value.trim();
        await axios({
          method: 'put',
          url: `http://localhost:3000/students/${student_id}`,
          data: {
            first_name: first_name,
            last_name: last_name,
            major: major
          }
        });
        getStudents();
        edit_dlg.close();
      }

      function updateStudentsTable() {
        removeChildren(students_tbody);
        for (let i = 0; i < students_data.length; i++) {
          const student = students_data[i];
          let tr = document.createElement('tr');
          tr.addEventListener('click', showEditDlg);
          const id = student.id;
          tr.setAttribute("id", id);
          let td = document.createElement('td');
          let content = document.createTextNode(student.first_name);
          td.appendChild(content);
          tr.appendChild(td);
          td = document.createElement('td');
          content = document.createTextNode(student.last_name);
          td.appendChild(content);
          tr.appendChild(td);
          td = document.createElement('td');
          content = document.createTextNode(student.major);
          td.appendChild(content);
          tr.appendChild(td);
          students_tbody.appendChild(tr);
        }
      }

      function init() {
        students_tbody = document.getElementById('students_tbody');
        add_dlg = document.getElementById('add_dlg');
        const add_button = document.getElementById('add_button');
        add_button.addEventListener('click', () => { add_dlg.showModal(); });
        const add_cancel = document.getElementById('add_cancel');
        add_cancel.addEventListener('click', () => { add_dlg.close(); });
        const add_ok = document.getElementById('add_ok');
        add_ok.addEventListener('click', handleAdd);
        edit_dlg = document.getElementById('edit_dlg');
        const edit_cancel = document.getElementById('edit_cancel');
        edit_cancel.addEventListener('click', () => { edit_dlg.close(); });
        const edit_ok = document.getElementById('edit_ok');
        edit_ok.addEventListener('click', handleEdit);
        getStudents();
      }
    </script>
  </head>
  <body>
    <h1>Students Table</h1>
    <button id="add_button">Add student</button><br>
    <dialog id="add_dlg">
      First Name:
      <input type="text" id="add_fn_box"><br>
      Last Name:
      <input type="text" id="add_ln_box"><br>
      Major:
      <input type="text" id="add_maj_box"><br>
      <br>
      <button id="add_cancel">Cancel</button>
      <button id="add_ok">Ok</button>
    </dialog>
    <dialog id="edit_dlg">
      First Name:
      <input type="text" id="edit_fn_box"><br>
      Last Name:
      <input type="text" id="edit_ln_box"><br>
      Major:
      <input type="text" id="edit_maj_box"><br>
      <br>
      <button id="edit_cancel">Cancel</button>
      <button id="edit_ok">Ok</button>
    </dialog>
    <table border="1">
      <thead>
        <tr>
          <th>First Name</th>
          <th>Last Name</th>
          <th>Major</th>
        </tr>
      </thead>
      <tbody id="students_tbody"></tbody>
    </table>
  </body>
</html>

The new lines are 12, 61, 67-74 and 81-98. Line 12 adds the variable, student_id, that will be used to store the student’s id when the showEditDlg() is called. That storage takes place on line 61. Line 67 obtains the student record from the results. Lines 68-73 use the data from the student record to fill in the input boxes with the current information. Line 74 displays the edit dialog box.

On line 81, we declare the handleEdit() function to be async since we are making an axios call. Lines 82-98 form the new definition of the handleEdit() function. Line 82-87 get references to the <input> elements and get the values from those elements. Lines 88-96 make an axios call that uses the route for updating the students table. Line 89 defines the method as 'put'. Line 90 creates the URL by attaching on the student_id on the end. Lines 91-95 define the data property that contains the updated values for first_name, last_name and major. One the axios call completes, line 97 calls getStudents() (and that calls updateStudentsTable()). Finally, on line 98, the edit dialog box is closed.

Here is a screen shot showing Jack Doe’s information being edited.

The next screen shot shows that the students table has been updated with the changes made in the edit dialog box:

Deleting a student

Now, we can work on the final capability for a CRUD application. That is, deleting a record. We need to start with the middleware again, and set up a route that can be used to delete a record based on the record’s id. So, here is the new version of queries.js:

/path/to/postgres/queries.js

const Pool = require('pg').Pool;
const pool = new Pool({
   user: 'joe',
   host: '172.17.0.2',
   database: 'joe_db',
   password: 'joepass',
   port: 5432
});
function getStudents(req,resp) {
   pool.query('select * from students order by id asc',
      (error, results) => {
      if (error) {
         throw error;
      }
      resp.status(200).json(results.rows);
   });
}
function createStudent(req, resp) {
   const { first_name, last_name, major } = req.body;
   pool.query('insert into students (first_name, last_name, major) ' +
      'values ($1, $2, $3)', [first_name, last_name, major],
      (error, results) => {
         if (error) {
            throw error;
         }
         resp.status(201).send('Student added');
      }
   );
}
function getStudentById(req, resp) {
  const id = Number(req.params.id);
  pool.query('select * from students where id=$1', [id],
     (error, results) => {
        if (error) {
           throw error;
        }
        resp.status(200).json(results.rows);
     });
}
function updateStudent(req, resp) {
  const id = Number(req.params.id);
  const { first_name, last_name, major } = req.body;
  pool.query('update students set first_name=$1, last_name=$2, ' +
     'major=$3 where id=$4',[first_name, last_name, major, id],
     (error, results) => {
        if (error) {
           throw error;
        }
        resp.status(200).send('Student updated');
     }
  );
}
function deleteStudent(req, resp) {
   const id = Number(req.params.id);
   pool.query('delete from students where id=$1', [id],
      (error, results) => {
         if (error) {
           throw error;
         }
         resp.status(200).send('Student deleted');
      }
   );
}

module.exports = { getStudents, createStudent, getStudentById, updateStudent,
  deleteStudent };

The new lines are 53-63 and 65-66. Line 66 just adds on to line 65 so that the deleteStudent() function is exported. Lines 53-63 define the deleteStudent() function. Line 54 gets the id passed in the URL and converts this to a number. That number is stored as id. Line 55-62 define a DELETE query that starts with the SQL string to delete a record that uses a placeholder variable, $1, for the id. That string is followed by an array containing the variable id. Lines 56-61 define an anonymous function that will throw an error if an error occurs. Otherwise line 60 sets the STATUS to 200 and sends the message 'Student deleted' to the console.

The file index.js is modified next to make use of the deleteStudent() function just added to queries.js. Here is the new version of index.js:

/path/to/postgres/index.js

const express = require('express');
const bodyParser = require('body-parser');
const db = require('./queries.js');
const cors = require('cors');
const app = express();
const port = 3000;

app.use(cors({
  origin: 'http://localhost:8000'
}));
app.use(bodyParser.json());
app.use(
   bodyParser.urlencoded({
      extended: true
   })
);

app.get('/', (req, resp) => {
   resp.json({ info: 'Express and Postgresql' });
});
app.get('/students',db.getStudents);
app.post('/students',db.createStudent);
app.get('/students/:id', db.getStudentById);
app.put('/students/:id', db.updateStudent);
app.delete('/students/:id', db.deleteStudent);

app.listen(port, () => {
   console.log('App running on port 3000');
});

The new line is 25. Line 25 sets up the route to use to delete a student. Note that this is a DELETE query. The URL to be used for this route is http://localhost:3000/students/4 to delete a student with an id=4.

This type of query is easier to test with Postman. Here is a screen shot showing the result of deleting the student with an id=4 from Postman:

As you can see, the message shows 'Student deleted' after I had clicked the Send button. The following screen shot shows what the web application looks like when the browser is refreshed. You can see that the 4th student has been deleted.

Updating the web application to allow for deleting a student

Now that we have tested the new route for deleting a student, we can modify the web application so that deletion can be done using that web interface. Here are the things to be done.

Modify the markup by adding another button to the edit dialog box. This button will be the Delete button. Using the edit dialog box for this purpose makes part of the deletion process easier. We need to be able to select the student that is to be deleted. Since clicking on a student will already bring up the edit dialog box and will have already stored the student’s id, a lot of the work is already done by just adding the delete button to the edit dialog box.
In the init() function, get a reference to the Delete button in the edit dialog. Associate clicking on that button with the function handleDelete().
Create the handleDelete() function. This function will set up an axios call to make of the last route we have created. If that call succeeds, the edit dialog box will be closed.

Here is the new version of students_app.html:

/path/to/postgres/students_app.html

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <script src="https://unpkg.com/axios/dist/axios.min.js"></script>
    <script>
      document.addEventListener('DOMContentLoaded', init);
      let students_data = [];
      let students_tbody;
      let add_dlg;
      let edit_dlg;
      let student_id;

      function removeChildren(elem) {
        while (elem.childNodes.length > 0) {
          elem.removeChild(elem.childNodes[0]);
        }
      }

      async function getStudents() {
        try {
          const students = await axios({
            method: 'get',
            url: 'http://localhost:3000/students'
          });
          students_data = students.data;
          console.log(students_data);
          updateStudentsTable();
        }
        catch (error) {
          console.log(error);
        }
      }

      async function handleAdd() {
        const add_fn_box = document.getElementById('add_fn_box');
        let first_name = add_fn_box.value.trim();
        const add_ln_box = document.getElementById('add_ln_box');
        let last_name = add_ln_box.value.trim();
        const add_maj_box = document.getElementById('add_maj_box');
        let major = add_maj_box.value.trim();
        try {
          await axios({
            method: 'post',
            url: 'http://localhost:3000/students',
            data: {
              first_name: first_name,
              last_name: last_name,
              major: major
            }
          });
          getStudents();
          add_dlg.close();
        }
        catch (error) {
        }
      }

      async function showEditDlg(event) {
        const id = Number(event.currentTarget.id);
        student_id = id;
        try {
          const results = await axios({
             method: 'get',
             url: `http://localhost:3000/students/${id}`,
          });
          const student = results.data[0];
          const edit_fn_box = document.getElementById('edit_fn_box');
          edit_fn_box.value = student.first_name;
          const edit_ln_box = document.getElementById('edit_ln_box');
          edit_ln_box.value = student.last_name;
          const edit_maj_box = document.getElementById('edit_maj_box');
          edit_maj_box.value = student.major;
          edit_dlg.showModal();
        }
        catch (error) {
          console.log(error);
        }
      }

      async function handleEdit() {
        const edit_fn_box = document.getElementById('edit_fn_box');
        let first_name = edit_fn_box.value.trim();
        const edit_ln_box = document.getElementById('edit_ln_box');
        let last_name = edit_ln_box.value.trim();
        const edit_maj_box = document.getElementById('edit_maj_box');
        let major = edit_maj_box.value.trim();
        await axios({
          method: 'put',
          url: `http://localhost:3000/students/${student_id}`,
          data: {
            first_name: first_name,
            last_name: last_name,
            major: major
          }
        });
        getStudents();
        edit_dlg.close();
      }

      async function handleDelete() {
        await axios({
          method: 'delete',
          url: `http://localhost:3000/students/${student_id}`
        });
        getStudents();
        edit_dlg.close();
      }

      function updateStudentsTable() {
        removeChildren(students_tbody);
        for (let i = 0; i < students_data.length; i++) {
          const student = students_data[i];
          let tr = document.createElement('tr');
          tr.addEventListener('click', showEditDlg);
          const id = student.id;
          tr.setAttribute("id", id);
          let td = document.createElement('td');
          let content = document.createTextNode(student.first_name);
          td.appendChild(content);
          tr.appendChild(td);
          td = document.createElement('td');
          content = document.createTextNode(student.last_name);
          td.appendChild(content);
          tr.appendChild(td);
          td = document.createElement('td');
          content = document.createTextNode(student.major);
          td.appendChild(content);
          tr.appendChild(td);
          students_tbody.appendChild(tr);
        }
      }

      function init() {
        students_tbody = document.getElementById('students_tbody');
        add_dlg = document.getElementById('add_dlg');
        const add_button = document.getElementById('add_button');
        add_button.addEventListener('click', () => { add_dlg.showModal(); });
        const add_cancel = document.getElementById('add_cancel');
        add_cancel.addEventListener('click', () => { add_dlg.close(); });
        const add_ok = document.getElementById('add_ok');
        add_ok.addEventListener('click', handleAdd);
        edit_dlg = document.getElementById('edit_dlg');
        const edit_cancel = document.getElementById('edit_cancel');
        edit_cancel.addEventListener('click', () => { edit_dlg.close(); });
        const edit_ok = document.getElementById('edit_ok');
        edit_ok.addEventListener('click', handleEdit);
        const delete_button = document.getElementById('delete_button');
        delete_button.addEventListener('click', handleDelete);
        getStudents();
      }
    </script>
  </head>
  <body>
    <h1>Students Table</h1>
    <button id="add_button">Add student</button><br>
    <dialog id="add_dlg">
      First Name:
      <input type="text" id="add_fn_box"><br>
      Last Name:
      <input type="text" id="add_ln_box"><br>
      Major:
      <input type="text" id="add_maj_box"><br>
      <br>
      <button id="add_cancel">Cancel</button>
      <button id="add_ok">Ok</button>
    </dialog>
    <dialog id="edit_dlg">
      First Name:
      <input type="text" id="edit_fn_box"><br>
      Last Name:
      <input type="text" id="edit_ln_box"><br>
      Major:
      <input type="text" id="edit_maj_box"><br>
      <br>
      <button id="edit_cancel">Cancel</button>
      <button id="edit_ok">Ok</button>
      <button id="delete_button">Delete student</button>
    </dialog>
    <table border="1">
      <thead>
        <tr>
          <th>First Name</th>
          <th>Last Name</th>
          <th>Major</th>
        </tr>
      </thead>
      <tbody id="students_tbody"></tbody>
    </table>
  </body>
</html>

The new lines are 101-108, 148-149 and 178. Line 178 adds the markup for a Delete button in the edit dialog box. Lines 148-149 get a reference to that Delete button and associate the handleDelete() function with clicking on that button. Lines 101-108 define the handleDelete() function. Lines 102-105 make an axios call to the route that deletes a student record. If that succeeds (which it should since we already confirmed this in the middleware testing), then line 106 calls getStudents(). The getStudents() function calls the updateStudentsTable() function to update the table. Finally, line 107 closes the edit dialog box.

So, this is a basic web application with a backend database. It has the basic CRUD functionality. However, besides protecting against SQL injection attacks, it does not provide enough basic security. That is a topic for another lesson.