COSC203 Web, Databases, and Networks
Toggle Dark/Light/Auto modeToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeBack to homepage

Lab 12: Web Services: RESTful API

🎯 Lab Objective

In this lab, you will build a proper back-end for the chat application

This lab teaches the following concepts:

  1. Use PocketBase as a backend
  2. Use Node/Express/MySQL as a backend
  3. Communicate with MySQL from express.js

1. Frontend vs. Backend

In a web application, the frontend presents information to the user. While the back-end powers the logic and data interactions.

The front-end, often referred to as the client-side powers the UI, and handles user interaction. Usually HTML, CSS, and JavaScript.

frontend and backend

The back-end (or server-side) manages the logic, processing, and database interactions. It receives HTTP requests from the front-end, processes them, and sends back any results. These interactions often occur through a RESTful API that supports CRUD (Create, Read, Update, Delete) operations on a database, to store, retrieve, update, or delete data as needed.

In this lab we will build 2 backends. The first with PocketBase, and the second we will build form scratch using Node, Express, and MySQL.

2. The PocketBase Backend

PocketBase is a prebuilt backend and Content Management System. It’s not super powerful, but great for small projects.

πŸ“ Task 1: Download PocketBase
  1. Download PocketBase
  2. Extract the zip file somewhere on your computer
    • WarpDrive recommended for lab machines J:\...\lab12\
  3. Run PocketBase from a terminal
    • ./pocketbase serve
  4. DONE!
    • You can now visit the admin page
    • http://localhost:8090/_
    • and create a local admin account - you can enter fake details for the account since this isn’t being sent to any real service
./pocketbase serve

serve from terminal

Creating an admin account http://localhost:8090/_ pocket base admin account

For our chat application we want our backend to store and manage chat messages. Each message needs an “author”, “content”, and “timestamp”. PocketBase will automatically generate a unique ID (primary key) for each message, and a “created” field which we can use as the timestamp.

πŸ“ Task 2: Create 'messages' collection
  1. Click: + New Collection
    • Name: messages
    • Fields:
      • author (Plain Text)
      • content (Plain Text)
  2. Under API Rules
    • Unlock all the rules by clicking the green padlock icon.
    • All 5 should be unlocked (List, View, Create, Update, Delete)
      • If we were serious about security we would also set up proper authentication, but not today.
  3. Click create to create the collection
Create collection pocketbase new collection
Unlock ALL collection rules pocketbase unlock collection
πŸ“ Task 3: Create records
  • Create a few database records, just make up some data.
    • Click + New Record
    • Fill in the fields (you can leave the ID blank)
    • Click Create
Create records pocketbase new record

Next let’s interact with this data using a RESTful API.


Thunder Client

Thunder Client is a VS Code extension for testing APIs (Similar to Postman).

πŸ“ Task 4: Testing the API
  1. Install Thunder Client
    • Open Extension in VSCode to find/install Thunder Client
  2. You will see a new icon in the side bar that looks like a lightning bolt with a circle around it — that is Thunder Client. Click the button.
  3. Create a new request
    • Click New Request
  4. Configure the request
    • URL: http://localhost:8090/api/collections/messages/records/
    • Method: GET
    • Click Send Request
  5. Voila
    • Status Code: 200 OK
    • Response Body: { "JSON": "data" }
      • with your data you made earlier

Thunder Client

GET /api/collections/messages/records/ thunder client
POST /api/collections/messages/records/ thunder client
πŸ“ Task 5: Testing POST/PATCH/DELETE requests

Let’s add a new message via a POST operation:

  1. Create a new request
  2. Configure the request
    • Method: POST
    • URL: http://localhost:8090/api/collections/messages/records/
  3. Add the below data to the body (JSON tab)
{
    "author": "Polly",
    "content": "Hello World"
}
  1. Click Send Request
  2. If you got 200 OK
    • Refresh the PocketBase admin portal to verify the new record was created
  3. Test the PATCH and DELETE requests too
    • DELETE is for deleting a record
    • PATCH is for updating a record
    • Both require requires a valid message id in the URL e.g.
      • localhost:8090/api/collections/messages/records/6ntavtxifi1d65c
    • DELETE doesn’t need a body
    • PATCH needs a body with the data to update
{
    "content" : "this message has been edited"
}
  1. Verify you can Create, Update, and Delete records through API requests.
PATCH thunder client
DELETE thunder client

3. Chat App 3.0

We have a fully functioning back-end! And simply by sending a few HTTP requests we can create, read, update, and delete data at will.

βœ… Tip

These are also known as CRUD operations.

  • Create a message
  • Read all the messages
  • Update a message
  • Delete a message

Frontend Code

Since this lab is focused on backend development (server-side), we have provided a complete frontend (modified from lab 5). The main differences are the ‘edit’ πŸ“ and ‘delete’ πŸ—‘οΈ buttons, as seen in the screenshot below.

Reminder about the server architecture we are building.

frontend and backend

front end errors
πŸ“ Task 6: Setup the frontend
  1. Download from here. https://altitude.otago.ac.nz/cosc203/code/lab12-frontend
  2. To host the frontend as a website, either…
    • Open an instance of VS Code and use the Live Server extension
    • From a terminal run python -m http.server
  3. You should be able to load messages AND send messages
    • Editing or deleting should give errors in the dev console

Update and Delete requests

To Update/Delete messages from the frontend we need to send PATCH and DELETE requests to the server using the Fetch API. Most of the code already in chat-app-v3.js. You just need to finish it.

πŸ“ Task 7: Editing and Deleting
  1. DELETE request
    • In chat-app-v3.js find function deleteMessage(...)
      • change the value of endpoint to API_URL + message_id
      • change the value of options.method to 'DELETE'
      • in the response handler function:
        • if response.status == 204 then
        • delete the message div via: chatLogDiv.removeChild(messageElement);
  2. PATCH request
    • In chat-app-v3.js find function editMessage(...)
      • change the value of endpoint to API_URL + message_id
      • change the value of options.method to 'PATCH'
      • change the value of options.body to JSON.stringify({ content: newContent })
      • in the response handler function:
        • if response.status == 200 then
        • console.log("message edited");
  3. Test the code
    • send a message
    • edit the message
    • delete the message
    • check PocketBase to ensure it definitely changed

4. A Proper Backend

In this final task we are going to throw away PocketBase and roll our own API server with MySQL as a database.

βœ… Tip
To close PocketBase, press Ctrl+C in the terminal that it is running on.

πŸ“ Task 8: Setup Backend
  1. Download this Git repo
  2. Open (another) instance VS Code
    • If you’re using Live Server for the front end, it’s best to leave that running for now.
  3. Open the terminal within VS Code
    • Ctrl + Shift + `
    • Terminal > New Terminal
  4. run npm install
  5. run npm run start
    • The backend is now live!
  6. With a web browser visit http://localhost:1337/

The backend will serve index.html if you visit http://localhost:1337/

This page contains important documentation that should understand before moving on.


Server Logic (api_server_.js)

The main file we will be editing is api_server_.js, it is the server-side logic that will

  1. connect to the database
  2. repond to HTTP requests
  3. perform CRUD operations

Your new new backend could use the same URL/endpoints as PocketBase did.

/api/collections/messages/records/

This will mean less work changing the frontend.

Take a moment to read through api_server_.js and see if you can figure out what everything does (or ask ChatGPT to explain it).

πŸ“ Task 9: Update your frontend

Update the frontend to use the new API URL.

  • The new backend should be on port 1337, not 8089
const API_URL = 'http://localhost:1337/api/collections/messages/records/';

Cross Origin Resource Sharing (CORS)

The below code is the CORS Policy. It informs web browsers which websites are allowed to access your backend web service. It’s largely a protection against random websites hotlinking your resources, and spamming your server, wasting valuable bandwidth and CPU time, and offers your users some protection against a form of security attack known as Cross Site Request Forgery.

// CORS policy headers: allow all domains
app.options('*', (request, response) => {
	response.header('Access-Control-Allow-Origin', '*');
	response.header('Access-Control-Allow-Methods', 'GET, POST, PATCH, DELETE');
	response.header('Access-Control-Allow-Headers', 'Content-Type');
	response.send();
})

This is a very permissive policy and doesn’t do much to prevent CSRF attacks or protect against random third-parties misusing your service since it is pretty much allowing anyone to do anything that they want with the service. We are using a permissive policy since we don’t really care about CORS for this project — we just want it to work.

βœ… Tip

To make the policy more restrictive and and allow access to the backend service from only your frontend, you would replace the * with the domain name that hosts your front end. e.g. www.example.com

response.header('Access-Control-Allow-Origin', 'www.example.com');

5. Setup MySQL

You have 3 options:

  1. Install MySQL on your own computer and skip to section 5.1
  2. Use the VirtualBox cosc203VM from labs 7-10
  3. Docker (the easiest solution if you already have Docker Desktop installed)

5.1 MySQL on your own computer

  1. Open a terminal in the lab12-backend folder.

  2. Login to your database (if you remember the password)

    • mysql -u <username> -p
  3. Run the provided script sql/db_setup.sql

source sql/db_setup.sql

If you install MySQL locally it’s HOST:PORT should be 127.0.0.1:3306


5.2 MySQL on Virtual Box

To use the VirtualBox cosc203VM, you need to change some settings.

  1. Power off the VM
  2. Settings
  3. Network
  4. Adapter 2
  5. Attached to: Host-only Adapter

VM network setup

Test this by pinging the VM from your host machine.

ping cosc203VM.local

VM network test

Run the provided script sql/db_setup.sql

mysql> source sql/db_setup.sql

5.3 MySQL on Docker

Option #3 Docker: More simple than VirtualBox, and more clean than installing MySQL locally. Docker is a popular tool for lightweight virtual machines (but no GUI).

The provided backend code contains docker-compose.yml, which is setup for MySQL.

  • Docker on lab computers
    1. Launch Docker Desktop (it will give you an error)
    2. From a shell run
      • wsl --install -d Ubuntu
      • wsl --update
      • If/when windows requires admint privileges, click cancel
    3. Launch Docker Desktop again
  • Your own computer
    1. Install Docker Desktop, then run the following commands:
cd lab12-backend
docker compose up --detach
docker exec -it cosc203_lab12_mysql mysql -u root -p
mysql> source sql/db_setup.sql

The password is: password


6. Connecting to MySQL

πŸ“ Task 10: Connect to MySQL
  1. Use one of the three methods above to get a running instance of MySQL
mysql> source sql/db_setup.sql

Lets connect our backend to the database. There is a node package called mysql2 which can connections and queries easily.

πŸ“ Task 11: Install mysql2
  1. First, kill the backend server
    • Ctrl + c
  2. run npm install mysql2
  3. Restart the server with npm run start
  4. Connect to MySQL
    • copy the below code into api_server_.js
    • paste it under the imports (the three require calls at the top of the file)
    • but before everything else
// Database
const mysql2 = require('mysql2');

const pool = mysql2.createPool({
    // host: 'cosc203VM.local', // Lab machine Virtual Box
    host: '127.0.0.1', // local MySQL or Docker
    // port: 3306,
    user: 'cosc203',
    password: 'password',
    database: 'chatapp',
    charset: 'utf8mb4',
    waitForConnections: false,
    connectionLimit: 10,
    queueLimit: 0,
    multipleStatements: true,
});

If using the cosc203VM, you’ll need to change the DB connection’s host

host: 'cosc203VM.local', // Lab machine Virtual Box

βœ… Tip
When you save api_server_.js the server auto-restarts. This is because of the node package nodemon which monitors file changes, and restarts the project.

Reading Messages with GET

Let’s start with the Read requests. api_server_.js already includes a GET endpoint for requesting messages: /api/collections/messages/records/. However, it only returns a single hard coded message.

Let’s replace the boring hard coded response with a database query!

πŸ“ Task 12: Retrieve data from the DB
  1. Replace the hard coded response with a database query
    • modify the existing app.get(...) with the code below
app.get('/api/collections/messages/records/', async (request, response) => {
    // acquire a DB connection
    const db = pool.promise();

    // query the database
    const [rows, fields] = await db.query("select * from messages");

    // set HTTP response headers
    response.header('Access-Control-Allow-Origin', '*');
    response.header('Content-Type', 'application/json');

    // send messages as JSON
    response.status(200);
    response.send(JSON.stringify({ items: rows }));
})

If done correctly, the chat app can now receive data from the database!


Create Message

In lab 5 we created new messages by sending a POST request to the endpoint /send-message/. In this lab, we have renamed the endpoint /api/collections/messages/records/

Here’s the front-end code sets up the POSTS request.

const options = {
    method: 'POST',
    body: JSON.stringify({
        'author': userName,
        'content': msgInput.value,
    }),
    headers: { 'Content-Type': 'application/json' }
}

As you can see above (and may remember from lab 5) we put the message data in the body of the POST request. On the server side, we can access the contents of the body with request.body.

πŸ“ Task 13: Create Messages

Create new messages in the database

  1. In api_server_.js find app.post('/api/create-message', ... )
    • remove:
response.status(500);
response.send();
  1. Replace it with the following code:
app.post('/api/collections/messages/records/', async (request, response) => {
	const { author, content } = request.body;
	const db = pool.promise();
	const stmt = "insert into messages (author, content) values (?, ?)";
	const [rows, fields] = await db.query(stmt, [author, content]);

	response.header('Access-Control-Allow-Origin', '*');
	response.header('Content-Type', 'application/json');

	response.status(200);
	response.send(JSON.stringify({ rows }));
})
  1. Test that you can send messages from the front-end to the DB
    • send a message
    • check MySQL to ensure it’s definitely there

Delete Message

Next we will add the ability to delete messages.

πŸ“ Task 14: Delete Messages
  1. Add the following code to api_server_.js
// CRUD: delete message
app.delete('/api/collections/messages/records/:id', async (request, response) => {
	const message_id = Number(request.params.id);
	const db = pool.promise();
	const [rows, fields] = await db.query("delete from messages where id = ?", [message_id]);

	response.header('Access-Control-Allow-Origin', '*');
	response.status(204)
	response.send();
})
  1. Test the code
    • send a message
    • delete the message
    • check MySQL to ensure it definitely changed

Edit Message

πŸ“ Task 15: Edit Messages
  1. Add the following code to api_server_.js
// CRUD: update message
app.patch('/api/collections/messages/records/:id', async (request, response) => {
	const message_id = Number(request.params.id);
	const content = request.body.content;
	const db = pool.promise();
	const stmt = "update messages set content = ? where id = ?";
	const [rows, fields] = await db.query(stmt, [content, message_id]);

	response.header('Access-Control-Allow-Origin', '*');
	response.header('Content-Type', 'application/json');

	response.status(200);
	response.send(JSON.stringify({ rows }));
})
  1. Test the code
    • send a message
    • edit the message
    • check MySQL to ensure it definitely changed

7. Storing Database Connection Credentials

βœ… Tip
This is an optional task. It’s not required for the lab, but it’s good practice.

It’s good practice to keep authentication data (like passwords) separate from your actual code. Keep them secret and protected. For example, storing passwords within a Git repo is a security breach. We could instead store passwords as environment variables.

πŸ“ Task 16: Environment Variables
  1. Create a file called .env in the root directory of your backend.
DB_USER=admin
DB_PASS=MoreSecurePasswordHere
  1. install the dot-env package: npm install dotenv
  2. Add the following code to api_server_.js
const dotenv = require('dotenv');
dotenv.config();
  1. access the user/password
const db_user = process.env.DB_USER;
const db_pass = process.env.DB_PASS;
  1. Create a new database user with a secure password
CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'MoreSecurePasswordHere';
GRANT ALL ON *.* TO 'dbuser'@'localhost' WITH GRANT OPTION;
  1. Update the database connection code to use the new variables
  2. Update the database schema in db_setup.sql to use the above statements to create the user
  3. Update .gitignore to ignore your .env file


8. Marking Off

This lab is worth marks. be sure to get signed off.