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

Table of Contents

---

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.

The back-end (or server-side) manages the logic, processing, and database interactions. It recieves 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.

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
   • https://github.com/pocketbase/pocketbase/releases/tag/v0.17.5
   • Windows 10/11
     • pocketbase_0.17.5_**windows_amd64**.zip
   • macOS (Intel)
     • pocketbase_0.17.5_darwin_**amd**64.zip
   • macOS (M1/M2)
     • pocketbase_0.17.5_darwin_**arm**64.zip
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

./pocketbase serve

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

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
   • 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.

Create collection

Unlock ALL collection rules

📝 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

Test the API using a browser localhost:8090/api/collections/messages/records

DONE!

Never has a database setup been so simple!

Next lets interact with this data using a RESTful API. We won’t even have to write any SQL! (yet…)

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. Create a new request
   • Click the thunderbolt icon ⚡
   • Click New Request
3. Configure the request
   • URL: http://localhost:8090/api/collections/messages/records/
   • Method: GET
   • Click Send Request
4. Voila
   • Status Code: 200 OK
   • Response Body: { "JSON": "data" }
     • with your data you made earlier

GET /api/collections/messages/records/

POST /api/collections/messages/records/

📝 Task 5: Testing POST/PATCH/DELETE requests

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

4. Click Send Request
5. If you got 200 OK
   • Refresh the PocketBase admin portal to verify the new record was created
6. 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"
}

7. Verify you can Create, Update, and Delete records through API requests.

PATCH

DELETE

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 opreations.

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

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.

📝 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

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 async 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 async 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

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 runnning on.

📝 Task 8: Setup Backend

1. Download this Git repo
   • https://altitude.otago.ac.nz/cosc203/code/lab12-backend
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.

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/';

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.

It’s not proper security, it won’t stop a bonafide hacker, but it is a requirement for compliant web browsers.

// 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();
})

✅ Tip

To restrict the backend API to only communicate with your frontend, simply replace the * with your domain name. e.g. www.example.com

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

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 imho)

1. Login to your database (if you remember the password)
   • mysql -u <username> -p
2. 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

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

Test this by pinging the VM from your host machine.

ping cosc203VM.local

Run the provided script sql/db_setup.sql

mysql> source sql/db_setup.sql

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 sql
docker compose up -d
docker exec -it cosc203_lab12_mysql mysql -u root -p
mysql> source sql/db_setup.sql

password is password haha

📝 Task 10: no taskName

1. Use one of the three methods above to get a running instance of MySQL
2. Run the provided script sql/db_setup.sql

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 server
   • Ctrl + C (even on macOS it’s 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
   • 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.

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.

Lets 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!

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();

2. 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 }));
})

3. Test that you can send messages from the front-end to the DB
   • send a message
   • check MySQL to ensure it’s definitely there

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();
})

3. Test the code
   • send a message
   • delete the message
   • check MySQL to ensure it definitely changed

📝 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 }));
})

3. Test the code
   • send a message
   • edit the message
   • check MySQL to ensure it definitely changed

✅ 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

2. install the dot-env package: npm install dotenv
3. Add the following code to api_server_.js

const dotenv = require('dotenv');
dotenv.config();

4. access the user/password

const db_user = process.env.DB_USER;
const db_pass = process.env.DB_PASS;

5. Create a new database user with a secure password

CREATE USER 'dbuser' IDENTIFIED BY 'MoreSecurePasswordHere';
GRANT ALL ON *.* TO 'dbuser' WITH GRANT OPTION;

6. Update the database connection code
7. Update .gitignore to ignore your .env file

• Task 1: Download PocketBase
• Task 2: Create 'messages' collection
• Task 3: Create records
• Task 4: Testing the API
• Task 5: Testing POST/PATCH/DELETE requests
• Task 6: Setup the frontend
• Task 7: Editing and Deleting
• Task 8: Setup Backend
• Task 9: Update your frontend
• Task 10: no taskName
• Task 11: Install mysql2
• Task 12: Retrieve data from the DB
• Task 13: Create Messages
• Task 14: Delete Messages
• Task 15: Edit Messages
• Task 16: Environment Variables