A web app using postgresql locally

Creating a web app using postgresql with Supabase

Making the web application work with Supabase as the PostgreSQL provider

Assuming that you have had a chance to create the web application in this lesson: A web app using postgresql with Docker, using Supabase instead of a Docker container is relatively straightforward.

Supabase (Supabase pricing) is a cloud provider that uses PostgreSQL as its database. They have a free tier that is good enough for you to make a test project. The free tier projects will be paused if they don’t show activity for more than 7 days. You can unpause the project within 90 days by going to their site. If you don’t want to be bothered with this, you can upgrade to their Pro tier.

Creating a free tier account on Supabase

If you click on the Start for Free button on their pricing page, you can create a free account. I used one of my email addresses as my username and came up with a suitable password.

You can then create a New project. You are limited to 2 active projects on the free tier. You can put down something for the Organization name. Set the Project name and a Database Password. Set the Region to the server closest to where you are. Then, click on the Create new project button.

When the new project is created, you will need to know how to connect to your project’s database. Start by clicking on the Connect button to the right of your project’s name in the main title bar of your project.

For the free tier, you need to scroll down until you see the Session pooler connection settings. Click on the View parameters drop-down:

This will bring up the connection parameters:

So, you can see in my case the following parameters:

host: aws-0-us-west-1.pooler.supabase.com
port: 5432
database: postgres
user: postgres.spqtufsxiyawahodnugp

Your host and user (username) will be different.

Installing psql and postgresql-16

Assuming you are on Ubuntu 24.04, you can install the following packages so that you can connect to your Supabase database using psql

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

Then, you can connect using the following:

$ psql -h aws-0-us-west-1.pooler.supabase.com -p 5432 -U postgres.spqtufsxiyawahodnugp -d postgres
Password for user postgres.spqtufsxiyawahodnugp:
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1), server 17.4)
WARNING: psql major version 16, server major version 17.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=>

Initializing the database with the students table

Assuming that my working directory is supabase_area, the path to this directory will be /path/to/supabase_area. You can create the following files in that directory:

/path/to/supabase_area/init.sql

-- init.sql
drop table if exists students cascade;

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

Line 1 is a comment stating the name of this file. Line 2 drops the students table if it exists. This makes it so that including init.sql will always clear out everything in the students table. Lines 4-10 define the students table. Note that PostgreSQL uses the text data type. This is a very flexible text type as it is like varchar of a very large size. The text type does not have an adverse effect on performance, so if you have any string type data, it is a good choice. The id field is declared as the primary key field, so that field will consist of unique integers.

In that same directory, you can create the file add_test_data.sql to supply some initial test data for the students table. Here is the code for that file:

/path/to/supabase_area/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');

So, running the following commands let me connect to and initialize my database on Supabase:

$ psql -h aws-0-us-west-1.pooler.supabase.com -p 5432 -U postgres.spqtufsxiyawahodnugp -d postgres
Password for user postgres.spqtufsxiyawahodnugp:
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1), server 17.4)
WARNING: psql major version 16, server major version 17.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=> \i init.sql
psql:init.sql:2: NOTICE:  table "students" does not exist, skipping
DROP TABLE
CREATE TABLE
postgres=> \i add_test_data.sql
INSERT 0 1
INSERT 0 1
INSERT 0 1
postgres=> select * from students;
 id | first_name | last_name |   major
----+------------+-----------+-----------
  1 | Jane       | Doe       | Biology
  2 | John       | Doe       | Chemistry
  3 | Bob        | Simmons   | Physics
(3 rows)

postgres=> \q

Adjusting the middleware

Now that the database has been set up, I just needed to adjust the middleware.

/path/to/supabase_area/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');
});

No changes are needed for index.js. Here is the new version of queries.js:

/path/to/supabase_area/queries.js

const Pool = require('pg').Pool;
const pool = new Pool({
   user: 'postgres.spqtufsxiyawahodnugp',
   host: 'aws-0-us-west-1.pooler.supabase.com',
   database: 'postgres',
   password: 'your_own_pass',
   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 };

Lines 3-6 are the lines that need to be changed. Note that you should substitute in all your own values for host, user and password.

Testing the middleware

A simple test can be run on the middleware. Here is what I did:

$ cd /path/to/supabase_area
$ node index.js

In another terminal I ran the following curl command:

$ 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"}]

Note that this will probably run slower than when PostgreSQL was provided locally, as this has to go out to the Supabase site.

Testing the front end application

I can use the same code for 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>

With node index.js running in another terminal, I can open another terminal and use http-server to serve out students_app.html:

$ cd /path/to/supabase_area
$ node index.js

In another terminal

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

Here is a screen shot of a browser displaying having added Gary Grant a Music Performance major:

After adding Gary Grant, I ran the curl command again:

$ 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"},{"id":4,"first_name":"Gary","last_name":"Grant","major":"Music Performance"}]

Because node index.js was running in my supabase_area directory, this connected to the database on Supabase. So, the web application works if the database is served out by Supabase.