Build a Gym Reservation Backend API

Build a REST API with Node.js, Express, and MySQL for gym event bookings.

Introduction

30 Second Summary

Think about how a school gymnasium gets used. Basketball practice on Monday, a seminar on Wednesday, a community event on Friday. Someone has to keep track of all those bookings and make sure two groups don't show up at the same time.

In this project, you will build the backend engine that powers a gymnasium event reservation system. You'll create a server that stores events, checks for scheduling conflicts, and responds to requests from any app that connects to it.

What You'll Build

When you're done, you can open Postman (or use curl in your terminal), send a request to book the gymnasium for next Tuesday at 2 PM, then query your API to see that reservation appear in a clean JSON list of upcoming events.

By the end of this project, you'll have:

  • A running REST API where you can submit a reservation with event details (name, organizer, date, time) and get back a confirmation with a unique event ID.
  • Full CRUD control over reservations. You can create, view, update, or cancel any event through simple HTTP requests.
  • Built-in conflict detection that automatically rejects double-bookings. Try to reserve a time slot that's already taken and watch the API respond with a clear error.
  • Secret Mission: Add an approval workflow where new events start as "pending" and an admin endpoint can approve or reject them.

Are there any prerequisites?

You'll need Python, MySQL, and VS Code installed on your machine. Step 1 walks you through verifying your setup and installing the project's packages.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters.

You are building the backend engine for a gymnasium event reservation system. This is the server that handles all the data logic: storing events, checking for conflicts, and responding to requests. Every app you use (Instagram, Uber, Google Calendar) has a backend like this behind the scenes.

Set Up the Project

Before you can write any server code, you need a project folder with the right packages installed.

In this step, you'll create a Python project, install the packages your API depends on, set up a MySQL database, and write a minimal server that proves everything is connected.

In this step, get ready to:
  • Create the project folder and install FastAPI, uvicorn, mysql-connector-python, and python-dotenv.
  • Create the MySQL database that will store reservation data.
  • Write a minimal FastAPI server that connects to MySQL and responds to requests.
Create the project and install dependencies

Every Python project starts with a folder and a virtual environment that keeps your packages isolated from other projects.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Visual Studio Code and press Enter to open it.
  • Open a terminal inside VS Code by pressing Ctrl+` (both macOS and Windows).
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir gym-reservation-api
cd gym-reservation-api
  • Create a virtual environment by running:
python3 -m venv venv
  • Activate the virtual environment by running:

macOS

source venv/bin/activate

Windows

venv\Scripts\activate

Linux

source venv/bin/activate

You should see (venv) appear at the start of your terminal prompt. This confirms the virtual environment is active.

What is a virtual environment?

A virtual environment is an isolated folder where Python installs packages just for this project. It prevents conflicts between projects that need different versions of the same package.

  • Install the four packages your API needs by running:
pip install fastapi uvicorn mysql-connector-python python-dotenv

What are these packages?

  • fastapi is the web framework that handles HTTP requests and routes.
  • uvicorn is the ASGI server that runs your FastAPI application.
  • mysql-connector-python is the driver that lets Python talk to your MySQL database.
  • python-dotenv loads environment variables from a .env file so you don't hardcode credentials.

You should see output confirming packages were installed successfully, ending with a line like Successfully installed fastapi-0.139.2 uvicorn-0.51.0 ....

Seeing permission errors?

Make sure you activated the virtual environment first. You should see (venv) in your terminal prompt. If not, re-run the activate command above.

If you see command not found: python3, Python may not be installed or not in your PATH. Help me fix Python not being found.

Now you need a .env file to store your database connection details securely.

  • Create a new file in VS Code by pressing Cmd+N (macOS) or Ctrl+N (Windows).
  • Copy the following configuration into the file:
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=
DB_NAME=gym_reservations
PORT=8000

What about DB_PASSWORD?

The DB_PASSWORD field is left empty because many local MySQL installations use no password for the root user. If you set a password when you installed MySQL, type it after the = sign.

  • Save the file as .env by pressing Cmd+S (macOS) or Ctrl+S (Windows).
  • In the save dialog, navigate to the gym-reservation-api folder on your Desktop.
  • Name the file .env and click Save.
  • Confirm your project files exist by running:
ls

You should see venv and .env in the output.

Create the MySQL database

Your server needs a database to write to. You'll create an empty database now and add tables in the next step.

  • Open a new terminal tab in VS Code by pressing Ctrl+Shift+` (both macOS and Windows).
  • Log into MySQL by running:
mysql -u root -p

What will happen next?

MySQL will prompt you for a password. If you never set one, just press Enter to submit an empty password. You'll see a mysql> prompt when you're connected.

  • Create the database by running this SQL command:
CREATE DATABASE gym_reservations;

You should see Query OK, 1 row affected confirming the database was created.

  • Verify the database exists by running:
SHOW DATABASES;

You should see gym_reservations in the list of databases.

  • Exit the MySQL terminal by typing:
EXIT;

Can't connect to MySQL?

Make sure the MySQL service is running. On macOS, check System Preferences > MySQL. On Windows, open Services and confirm MySQL is started.

If you get an access denied error, double-check the password you're entering matches what you set during MySQL installation. Help me troubleshoot my MySQL connection.

Write the minimal server with database connection

Now you'll write the actual server code. This file imports your packages, connects to MySQL, and starts listening for HTTP requests.

  • Create a new file in VS Code by pressing Cmd+N (macOS) or Ctrl+N (Windows).
  • Save it immediately as main.py inside the gym-reservation-api folder.
  • Add the imports, app setup, and database connection by copying this code into main.py:
from fastapi import FastAPI
from fastapi.middleware.cors import CORSMiddleware
import mysql.connector
import os
from dotenv import load_dotenv

# Load environment variables from .env
load_dotenv()

app = FastAPI()

# Middleware to allow cross-origin requests
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_methods=["*"],
    allow_headers=["*"],
)

# MySQL connection configuration
db_config = {
    "host": os.getenv("DB_HOST"),
    "user": os.getenv("DB_USER"),
    "password": os.getenv("DB_PASSWORD"),
    "database": os.getenv("DB_NAME"),
}

What does this code do?

  • load_dotenv() reads your .env file and makes those values available through os.getenv().
  • CORSMiddleware allows other apps (like a future frontend) to call your API from a different domain.
  • db_config stores your database connection details in a dictionary. You'll pass this to mysql.connector.connect() whenever you need to query the database.
  • Add the test route below the db_config dictionary:
# Test route
@app.get("/")
def root():
    return {"message": "Gym Reservation API is running"}

# Test database connection
@app.get("/health")
def health_check():
    try:
        conn = mysql.connector.connect(**db_config)
        conn.close()
        return {"status": "healthy", "database": "connected"}
    except mysql.connector.Error as err:
        return {"status": "unhealthy", "error": str(err)}

What does this code do?

  • @app.get("/") creates a route that responds to GET requests at the root URL. It returns a JSON message confirming the API is alive.
  • @app.get("/health") tests the MySQL connection by opening and immediately closing a connection. If it fails, you get a clear error message.
  • Save main.py by pressing Cmd+S (macOS) or Ctrl+S (Windows).

✔️ Awesome, I've got everything!

Great. Make sure you've saved main.py before running it.

ⓧ I'd like to double check the full code

Scroll up and confirm your main.py contains both chunks in this order:

  • Chunk 1: Imports, load_dotenv(), app creation, CORS middleware, and db_config dictionary.
  • Chunk 2: Test route (@app.get("/")) and health check route (@app.get("/health")).

Your .env should contain:

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=
DB_NAME=gym_reservations
PORT=8000

Before you run the server, what do you think will happen? Will it connect to MySQL successfully, or will it error out?

  • Switch back to your first terminal tab (the one inside gym-reservation-api).
  • Start the server by running:
uvicorn main:app --reload

What does this command do?

uvicorn main:app tells uvicorn to look in main.py for the app object. The --reload flag auto-restarts the server whenever you save a file, so you don't have to stop and restart manually.

You should see output including a line like:

INFO:     Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)
  • Open your browser and navigate to http://127.0.0.1:8000.

You should see {"message":"Gym Reservation API is running"} in the browser.

  • Now visit http://127.0.0.1:8000/health to test the database connection.

You should see {"status":"healthy","database":"connected"}. Both the server and MySQL are working together.

Seeing a database connection error?

  • Check that your MySQL service is running.
  • Verify the DB_PASSWORD in your .env matches your actual MySQL root password.
  • Confirm the database gym_reservations exists by logging back into MySQL and running SHOW DATABASES;.

Help me debug my MySQL connection error in FastAPI.

Your environment is ready. You have a running server that proves Python and MySQL are talking to each other. Next up, you'll design and create the events table that holds all the reservation data.

Design and Create the Events Table

Your FastAPI server is running and connected to MySQL. But right now, the database is completely empty.

Without a table to store event data, the API has no way to save or retrieve reservations. In this step, you'll design a table that captures everything a gymnasium event needs: who's organizing it, when it's happening, and what its approval status is.

In this step, get ready to:
  • Design and create the events table in MySQL.
  • Verify the table structure and test it with sample data.
Design the events table schema

Every gymnasium event needs several pieces of information to be useful. Think about what you'd want to know when looking at a reservation calendar.

  • An ID to uniquely identify each event.
  • The event name and who's organizing it.
  • A date, start time, and end time for scheduling.
  • A description for extra details.
  • A status field to track whether the event is pending, approved, or rejected.
  • A timestamp recording when the reservation was created.
  • Create a new file called schema.sql in your gym-reservation-api folder by running this command in your VS Code terminal:
touch schema.sql
  • Open schema.sql and add the following SQL that defines the table structure:
CREATE DATABASE IF NOT EXISTS gym_reservations;
USE gym_reservations;

CREATE TABLE events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  event_name VARCHAR(255) NOT NULL,
  organizer_name VARCHAR(100) NOT NULL,
  organizer_email VARCHAR(100),
  event_date DATE NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  description TEXT,
  status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

What does this SQL do?

The first two lines ensure the database exists and select it. The CREATE TABLE statement defines each column with a specific data type.

  • INT AUTO_INCREMENT PRIMARY KEY gives each event a unique, auto-generated ID.
  • VARCHAR(255) stores text up to 255 characters. NOT NULL means the field is required.
  • ENUM restricts the status column to only three valid values.
  • TIMESTAMP DEFAULT CURRENT_TIMESTAMP automatically records when each row is created.
  • Save schema.sql.

Now you need to run this SQL in your MySQL terminal to actually create the table.

  • Open a new terminal tab in VS Code and connect to MySQL by running:
mysql -u root -p

No password set?

If your MySQL root user has no password, just press Enter when prompted. If you set a password during installation, type it now.

  • Select the database and create the table by running these two commands in the MySQL prompt:
USE gym_reservations;

CREATE TABLE events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  event_name VARCHAR(255) NOT NULL,
  organizer_name VARCHAR(100) NOT NULL,
  organizer_email VARCHAR(100),
  event_date DATE NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  description TEXT,
  status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

You should see Query OK, 0 rows affected after each command. This confirms MySQL created the table successfully.

Seeing an error?

  • If you see Table 'events' already exists, you may have run the command twice. Run DROP TABLE events; first, then re-run the CREATE TABLE statement.
  • If you see No database selected, make sure you ran USE gym_reservations; first.

Need more help?

Verify the table structure
  • In your MySQL terminal, confirm all columns exist by running:
DESCRIBE events;

You should see a table listing all 10 columns with their types, including id, event_name, organizer_name, event_date, start_time, end_time, status, and created_at. Each row shows the Field name, Type, whether it allows NULL, any Key designation, its Default value, and any Extra info like auto_increment.

Now insert a test row to prove data flows in and out correctly.

  • Insert a sample event by running:
INSERT INTO events (event_name, organizer_name, organizer_email, event_date, start_time, end_time, description)
VALUES ('Basketball Tournament', 'Jane Smith', 'jane@example.com', '2026-08-01', '10:00:00', '12:00:00', 'Annual inter-department basketball tournament');

You should see Query OK, 1 row affected. Notice you didn't set id, status, or created_at. MySQL fills those in automatically based on the defaults you defined.

Before you run the next query, what do you think the status column will show for this event?

  • Retrieve the event by running:
SELECT * FROM events;

You should see your Basketball Tournament row with an auto-generated id of 1, a status of pending, and a created_at timestamp. The table is working.

  • Clean up the test data so you start fresh in the next step by running:
DELETE FROM events WHERE id = 1;

Don't see your row?

  • If the INSERT failed, check that your column names match exactly. Common typos: organizer_name vs organizer, event_date vs date.
  • If you see Table 'events' doesn't exist, the CREATE TABLE command from the previous substep didn't run successfully. Go back and re-run it.

Help me debug my events table

✔️ Awesome, I've got everything!

Great. Double check you saved schema.sql in VS Code before moving on.

ⓧ I'd like to double check the full code

CREATE DATABASE IF NOT EXISTS gym_reservations;
USE gym_reservations;

CREATE TABLE events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  event_name VARCHAR(255) NOT NULL,
  organizer_name VARCHAR(100) NOT NULL,
  organizer_email VARCHAR(100),
  event_date DATE NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  description TEXT,
  status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Your database now has a proper home for event data. Next up, you'll build the API endpoints that let outside applications create and read events from this table.

Build the Create and Read Endpoints

Your database table is ready and waiting for data. Right now, though, the API has no way to put events into the table or read them back out.

The core purpose of a reservation system is letting organizers submit events and letting anyone view what's scheduled. In this step, you will build three REST API endpoints that handle creating and reading event data.

In this step, get ready to:
  • Build the POST endpoint to create new reservations.
  • Build the GET endpoints to retrieve event data.
  • Test the full create-and-read flow with curl.
Build the POST endpoint to create a reservation

Every reservation starts with an organizer submitting event details. The POST endpoint accepts a JSON request body, validates the required fields using a Pydantic model, and inserts the event into your MySQL table.

  • In main.py, find the @app.get("/health") route near the bottom of the file.
  • Add the following Pydantic model and POST route below the health check:
from pydantic import BaseModel
from typing import Optional
from fastapi import HTTPException
from fastapi.responses import JSONResponse

# Request model for creating an event
class EventCreate(BaseModel):
    event_name: str
    organizer_name: str
    organizer_email: Optional[str] = None
    event_date: str
    start_time: str
    end_time: str
    description: Optional[str] = None

What is a Pydantic model?

Pydantic validates incoming JSON automatically. If a request is missing event_name or sends it as the wrong type, FastAPI returns a clear 422 error without you writing any validation logic.

Fields marked Optional with a default of None are not required. Everything else must be present in the request body.

  • Add the POST route below the Pydantic model:
# POST create event
@app.post("/api/events", status_code=201)
def create_event(event: EventCreate):
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor()

    insert_sql = """INSERT INTO events
        (event_name, organizer_name, organizer_email,
         event_date, start_time, end_time, description, status)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"""
    values = (
        event.event_name, event.organizer_name,
        event.organizer_email, event.event_date,
        event.start_time, event.end_time,
        event.description, "pending"
    )

    cursor.execute(insert_sql, values)
    conn.commit()
    new_id = cursor.lastrowid
    cursor.close()
    conn.close()

    return {
        "id": new_id,
        "event_name": event.event_name,
        "organizer_name": event.organizer_name,
        "event_date": event.event_date,
        "start_time": event.start_time,
        "end_time": event.end_time,
        "status": "pending"
    }

What does this code do?

  • The event: EventCreate parameter tells FastAPI to parse and validate the JSON body using the Pydantic model. Missing fields return a 422 error automatically.
  • The %s placeholders in the SQL prevent SQL injection by keeping user input separate from the query structure.
  • On success, the response includes the auto-generated id from MySQL and a 201 status code meaning "created".
  • Save main.py. Because you started uvicorn with --reload, the server restarts automatically.

Why no manual restart?

The --reload flag you passed to uvicorn watches your files for changes. Every time you save, the server restarts on its own. You'll see a WARNING: WatchFiles detected changes message in the terminal confirming the reload.

  • Test your new POST endpoint by running this curl command in a second terminal window:
curl -X POST http://127.0.0.1:8000/api/events \
  -H "Content-Type: application/json" \
  -d '{
    "event_name": "Team Seminar",
    "organizer_name": "Alex Johnson",
    "organizer_email": "alex@example.com",
    "event_date": "2026-07-25",
    "start_time": "14:00",
    "end_time": "16:00",
    "description": "Quarterly team seminar"
  }'

You should see a JSON response containing your event data and an auto-generated id of 1.

Windows users

Single quotes around the JSON body do not work in PowerShell. Use curl.exe instead of curl and swap single quotes for double quotes (escaping the inner ones with backslashes). Alternatively, open http://127.0.0.1:8000/docs in your browser to use FastAPI's built-in interactive docs.

🙋‍♀️ Seeing a 422 or 500 error?

  • A 422 means your JSON body is missing a required field or has a typo in a field name. Check that all field names match the Pydantic model exactly.
  • A 500 usually means a MySQL connection issue. Check your terminal for the full error traceback.
  • If you see Method Not Allowed, make sure you're sending a POST request, not a GET.

Still stuck? Help me debug why my POST /api/events endpoint is not working.

Build the GET endpoints to list and fetch events

Now that you can create events, you need a way to retrieve them. You'll add two GET endpoints: one that returns all events, and one that fetches a single event by its ID.

  • Add the following two GET routes below your POST route in main.py:
# GET all events (with optional status filter)
@app.get("/api/events")
def get_events(status: Optional[str] = None):
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor(dictionary=True)

    if status:
        cursor.execute(
            "SELECT * FROM events WHERE status = %s"
            " ORDER BY event_date ASC, start_time ASC",
            (status,)
        )
    else:
        cursor.execute(
            "SELECT * FROM events"
            " ORDER BY event_date ASC, start_time ASC"
        )

    events = cursor.fetchall()
    cursor.close()
    conn.close()
    return events

What does this code do?

  • The status: Optional[str] = None parameter is a query parameter. A request to /api/events?status=pending returns only pending events.
  • cursor = conn.cursor(dictionary=True) makes each row return as a dictionary (like JSON) instead of a plain tuple. This means FastAPI can serialize it directly.
  • Add the single-event GET route below the list endpoint:
# GET single event by ID
@app.get("/api/events/{event_id}")
def get_event(event_id: int):
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor(dictionary=True)

    cursor.execute(
        "SELECT * FROM events WHERE id = %s", (event_id,)
    )
    event = cursor.fetchone()
    cursor.close()
    conn.close()

    if not event:
        raise HTTPException(
            status_code=404, detail="Event not found"
        )
    return event

What does this code do?

  • The {event_id} in the path is a path parameter. FastAPI captures whatever value appears there and passes it to the function as event_id.
  • If the query returns no row, HTTPException sends a 404 response with a clear error message.
  • The int type hint on event_id means FastAPI validates that the path value is a number. A request to /api/events/abc returns a 422 automatically.
  • Save main.py. The server auto-reloads.
  • Test the list endpoint by running:
curl http://127.0.0.1:8000/api/events

You should see a JSON array containing the "Team Seminar" event you created in the previous substep.

  • Before you run the next command, think about what you expect to see. The event you created earlier had id: 1. What do you think happens if you ask for id: 999?
  • Fetch the single event by running:
curl http://127.0.0.1:8000/api/events/1

You should see just the Team Seminar event as a single JSON object (not wrapped in an array).

  • Now test the 404 case by running:
curl http://127.0.0.1:8000/api/events/999

You should see {"detail":"Event not found"} confirming your error handling works.

Getting an empty array or import error?

  • If you see an empty [], re-run the curl POST command from the previous substep to insert a fresh event.
  • If you see ImportError, make sure from pydantic import BaseModel and from fastapi import HTTPException are at the top of your file.
  • Check your uvicorn terminal for the full error traceback.

Need more help? Help me figure out why my GET endpoints are not working.

✔️ Awesome, I've got everything!

Great. Double-check you have saved main.py and that your server reloaded without errors.

ⓧ I'd like to double check the full code

Your main.py should now contain these sections in order. Scroll through your file and confirm each one is present:

  • Imports (fastapi, mysql.connector, dotenv, pydantic).
  • App creation, CORS middleware, and db_config dictionary.
  • Test route: GET / and health check: GET /health.
  • Pydantic model: EventCreate.
  • POST create event: POST /api/events.
  • GET all events: GET /api/events with optional status filter.
  • GET single event: GET /api/events/{event_id} with 404 handling.

Your API can now create events and read them back. Next up, you will add the ability to update and delete reservations so organizers can fix mistakes or cancel events entirely.

Add Update and Delete Endpoints

Your API can create and read events. That's a solid start, but right now organizers are stuck with whatever they submitted.

Real reservation systems need flexibility. Organizers change event times, update descriptions, or cancel bookings entirely. In this step, you'll add the endpoints that let users modify and remove events.

In this step, get ready to:
  • Build a PUT endpoint that updates event details in the database.
  • Build a DELETE endpoint that removes events from the database.
  • Test both endpoints to confirm changes persist and deleted events return a 404.
Add the PUT endpoint for updating events

A PUT request replaces the resource at a given URL with new data. Your endpoint will accept a JSON body with the updated fields, run a parameterized UPDATE query, and return a success message.

  • In main.py, add the following PUT route below your GET single event route:
# PUT update event
@app.put("/api/events/{event_id}")
def update_event(event_id: int, event: EventCreate):
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor()

    update_sql = """UPDATE events SET
        event_name = %s, organizer_name = %s,
        organizer_email = %s, event_date = %s,
        start_time = %s, end_time = %s,
        description = %s
        WHERE id = %s"""
    values = (
        event.event_name, event.organizer_name,
        event.organizer_email, event.event_date,
        event.start_time, event.end_time,
        event.description, event_id
    )

    cursor.execute(update_sql, values)
    conn.commit()
    rows_affected = cursor.rowcount
    cursor.close()
    conn.close()

    if rows_affected == 0:
        raise HTTPException(
            status_code=404, detail="Event not found"
        )
    return {"message": "Event updated successfully"}

What does this code do?

  • The %s placeholders in the SQL keep the query safe from SQL injection attacks. The database driver swaps each %s with a safely escaped value from the tuple.
  • cursor.rowcount tells you how many rows the UPDATE touched. If zero rows were affected, the ID doesn't exist, so the endpoint raises a 404.
  • The event: EventCreate parameter reuses the same Pydantic model from the POST route, so validation is automatic.
  • Save main.py. The server auto-reloads.
  • First, create a fresh event to test with (since you may have deleted your earlier ones):
curl -X POST http://127.0.0.1:8000/api/events \
  -H "Content-Type: application/json" \
  -d '{"event_name":"Team Seminar","organizer_name":"Alex Johnson","event_date":"2026-07-25","start_time":"14:00","end_time":"16:00"}'
  • Test the PUT endpoint by running:
curl -X PUT http://127.0.0.1:8000/api/events/1 \
  -H "Content-Type: application/json" \
  -d '{"event_name":"Updated Seminar","organizer_name":"Jane Smith","organizer_email":"jane@example.com","event_date":"2026-07-22","start_time":"15:00","end_time":"17:00","description":"Rescheduled seminar"}'

You should see: {"message":"Event updated successfully"}.

  • Confirm the update persisted by fetching the event:
curl http://127.0.0.1:8000/api/events/1

The response now shows Updated Seminar as the event name and 15:00:00 as the start time.

Seeing "Event not found"?

  • The event ID 1 might not exist if you deleted test rows earlier. Run curl http://127.0.0.1:8000/api/events to see which IDs you have, then use one that exists.
  • Check your uvicorn terminal for error messages. A traceback there reveals the exact problem.

Help me debug my PUT endpoint response.

Add the DELETE endpoint for canceling reservations

When an organizer cancels, the event should disappear from the database entirely. The DELETE endpoint removes a row by its ID and confirms it's gone.

  • In main.py, add the following DELETE route directly below the PUT route:
# DELETE event
@app.delete("/api/events/{event_id}")
def delete_event(event_id: int):
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor()

    cursor.execute(
        "DELETE FROM events WHERE id = %s", (event_id,)
    )
    conn.commit()
    rows_affected = cursor.rowcount
    cursor.close()
    conn.close()

    if rows_affected == 0:
        raise HTTPException(
            status_code=404, detail="Event not found"
        )
    return {"message": "Event deleted successfully"}

What does this code do?

  • The DELETE FROM events WHERE id = %s query removes the matching row permanently from the table.
  • Like the PUT handler, it checks cursor.rowcount to detect whether the ID existed. If no row was deleted, it raises a 404.
  • Save main.py. The server auto-reloads.

Before you test, what do you think will happen if you try to GET the event after deleting it?

  • Delete the event by running:
curl -X DELETE http://127.0.0.1:8000/api/events/1

You should see: {"message":"Event deleted successfully"}.

  • Now try to fetch that same event:
curl http://127.0.0.1:8000/api/events/1

The response is {"detail":"Event not found"} with a 404 status. The event is gone from the database.

Still seeing the event data instead of a 404?

  • Check your uvicorn terminal for a reload confirmation. If you see errors, the old code is still running.
  • Check that the event ID in your DELETE request matches the one you're fetching with GET. Run curl http://127.0.0.1:8000/api/events to list all remaining events and their IDs.

Help me debug my DELETE endpoint.

✔️ Awesome, I've got everything!

Great. Double check you've saved main.py and that your server is running without errors.

ⓧ I'd like to double check the full code

Your main.py should now contain these sections in order. Scroll up and confirm each one is present:

  • Imports (fastapi, mysql.connector, pydantic, dotenv).
  • App creation, CORS middleware, db_config.
  • Test route: GET / and health check: GET /health.
  • Pydantic model: EventCreate.
  • POST /api/events (create event).
  • GET /api/events (all events with optional status filter).
  • GET /api/events/{event_id} (single event).
  • PUT /api/events/{event_id} (update event) — added in this step.
  • DELETE /api/events/{event_id} (delete event) — added in this step.

Your API now handles the full lifecycle of a reservation: create, read, update, and delete. Next up, you'll add the logic that makes this system genuinely useful. What happens when two organizers try to book the gym at the same time?

Add Double-Booking Prevention

Your API can create, read, update, and delete gymnasium events. But there is a critical gap.

What happens if two organizers book the gym for the same time slot? Right now, the API accepts both without question. In this step, you'll add conflict detection so the system rejects overlapping reservations automatically.

In this step, get ready to:
  • Add conflict detection logic to the POST endpoint.
  • Test that overlapping bookings are rejected with a 409 status.
  • Confirm non-overlapping bookings still succeed.
Add conflict detection logic to the POST endpoint

Before writing any fix, let's prove the problem exists. Your current POST endpoint happily accepts overlapping reservations.

  • Make sure your server is running. If it's not, start it by running:
node server.js
  • Open a second terminal tab so you can run curl commands while the server stays running.
  • Create a "Staff Meeting" event for August 4th, 10:00 to 12:00 by running:
curl -X POST http://localhost:3000/api/events \
  -H "Content-Type: application/json" \
  -d '{"event_name": "Staff Meeting", "organizer_name": "Jane Smith", "event_date": "2025-08-04", "start_time": "10:00", "end_time": "12:00"}'

You should see a 201 response with the event details. Now try booking an overlapping time slot on the same date.

  • Before you run the next command, predict what will happen. Will the API accept or reject a "Yoga Class" from 11:00 to 13:00 on the same day?
  • Create the overlapping event by running:
curl -X POST http://localhost:3000/api/events \
  -H "Content-Type: application/json" \
  -d '{"event_name": "Yoga Class", "organizer_name": "Tom Lee", "event_date": "2025-08-04", "start_time": "11:00", "end_time": "13:00"}'

You get another 201 response. The API accepted both events even though they overlap from 11:00 to 12:00. That is the gap you need to close.

Why is double-booking a problem?

A gymnasium is a physical space. Only one event can occupy it at a time. Without conflict detection, the API is just a database wrapper that stores whatever it receives.

Adding conflict detection makes the system genuinely useful. It enforces a real-world constraint at the server level so no frontend or user error can create impossible schedules.

Time to fix this. The logic you need: before inserting a new event, query the database for any existing event on the same date whose time range overlaps with the requested time.

How does overlap detection work?

Two time ranges overlap when one starts before the other ends AND ends after the other starts. The SQL condition is: existing start_time < new end_time AND existing end_time > new start_time.

For example, if an existing event runs 10:00-12:00 and a new request asks for 11:00-13:00: 10:00 < 13:00 is true AND 12:00 > 11:00 is true. Conflict detected.

  • In server.js, find the // POST create event route.
  • Find this block of code inside the POST handler (everything after the validation check):
  const insertSql = 'INSERT INTO events (event_name, organizer_name, organizer_email, event_date, start_time, end_time, description, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?)';
  const values = [event_name, organizer_name, organizer_email || null, event_date, start_time, end_time, description || null, 'pending'];

  db.query(insertSql, values, (err, result) => {
    if (err) return res.status(500).json({ error: err.message });
    res.status(201).json({
      id: result.insertId,
      event_name,
      organizer_name,
      organizer_email: organizer_email || null,
      event_date,
      start_time,
      end_time,
      description: description || null,
      status: 'pending'
    });
  });
  • Replace that entire block with the conflict detection version below:
  // Check for conflicts
  const conflictSql = 'SELECT * FROM events WHERE event_date = ? AND start_time < ? AND end_time > ?';
  db.query(conflictSql, [event_date, end_time, start_time], (err, conflicts) => {
    if (err) return res.status(500).json({ error: err.message });

    if (conflicts.length > 0) {
      return res.status(409).json({
        error: 'Time slot conflict',
        message: `The gymnasium is already booked for "${conflicts[0].event_name}" from ${conflicts[0].start_time} to ${conflicts[0].end_time} on that date.`
      });
    }

    // No conflict, insert the event
    const insertSql = 'INSERT INTO events (event_name, organizer_name, organizer_email, event_date, start_time, end_time, description, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?)';
    const values = [event_name, organizer_name, organizer_email || null, event_date, start_time, end_time, description || null, 'pending'];

    db.query(insertSql, values, (err, result) => {
      if (err) return res.status(500).json({ error: err.message });
      res.status(201).json({
        id: result.insertId,
        event_name,
        organizer_name,
        organizer_email: organizer_email || null,
        event_date,
        start_time,
        end_time,
        description: description || null,
        status: 'pending'
      });
    });
  });

What does this code do?

  • conflictSql queries for any event on the same date whose time range overlaps with the new request.
  • The parameters are passed as [event_date, end_time, start_time]. The query checks: existing start < new end AND existing end > new start.
  • If conflicts are found, the API returns a 409 status with a message naming the conflicting event.
  • If no conflict exists, the insert proceeds exactly as before.
  • Save server.js.
  • In the terminal where your server is running, press Ctrl+C to stop it, then restart by running:
node server.js

You should see "Server running at http://localhost:3000" and "MySQL Connected!" again.

Seeing a syntax error on startup?

  • Check that you have matching closing brackets. The new code adds an extra layer of nesting (the conflict query callback wraps around the insert). Count your closing }); lines at the end of the POST handler.
  • Make sure the backtick template literal in the message field uses backticks (`) not single quotes (').

Help me debug my conflict detection code in server.js

✔️ Awesome, I've got everything!

Great. Double check you've saved your file and the server restarted without errors.

ⓧ I'd like to double check the full code

Your server.js POST route should now have this structure, in order:

  • Destructure the request body fields.
  • Validate required fields (return 400 if missing).
  • Run the conflict query (conflictSql) checking for same date with overlapping times.
  • If conflicts found, return 409 with the conflict message.
  • If no conflict, run the INSERT query and return 201 with the new event.

All other routes (GET, PUT, DELETE) remain unchanged from the previous step.

Test the conflict detection

The conflict detection is in place. Time to prove it works. You still have those two overlapping events from the earlier test in the database, so any new overlapping event on August 4th should be rejected.

  • Before you run the next command, predict: will the API accept or reject a new event on 2025-08-04 from 11:00 to 13:00?
  • Try to create a conflicting event by running:
curl -X POST http://localhost:3000/api/events \
  -H "Content-Type: application/json" \
  -d '{"event_name": "Dance Class", "organizer_name": "Maria Garcia", "event_date": "2025-08-04", "start_time": "11:00", "end_time": "13:00"}'

You should see a 409 response with a message like:

{
  "error": "Time slot conflict",
  "message": "The gymnasium is already booked for \"Staff Meeting\" from 10:00:00 to 12:00:00 on that date."
}

The API detected the overlap with your earlier "Staff Meeting" and rejected the request. That is exactly the behaviour you need.

  • Now confirm that a non-overlapping event on the same date still succeeds by running:
curl -X POST http://localhost:3000/api/events \
  -H "Content-Type: application/json" \
  -d '{"event_name": "Evening Seminar", "organizer_name": "Alex Chen", "event_date": "2025-08-04", "start_time": "18:00", "end_time": "20:00"}'

You should see a 201 response with the new event details. The time slot 18:00-20:00 does not overlap with anything on that date, so the insert proceeds normally.

Still seeing 201 for the conflicting event?

  • Make sure you restarted the server after saving. The old code is still running until you stop and re-run node server.js.
  • Check that the parameter order matches: [event_date, end_time, start_time]. Swapping end_time and start_time breaks the overlap logic.
  • Verify that your earlier test events are still in the database. Run curl http://localhost:3000/api/events to list all events.

Help me debug my conflict detection query

Your reservation system now enforces a real-world constraint. No two events can occupy the gymnasium at the same time. Next up, you'll take on the Secret Mission and add an approval workflow so events must be approved before they're official.

Secret mission

Add Approval Workflow

Right now, any event submitted to the gym goes straight into the system. In the real world, an admin would need to approve or reject requests first. Can you add an approval workflow with status transitions?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • MySQL database gym_reservations with an events table (local, no cost).
  • Node.js project folder gym-reservation-api with installed npm packages (local, no cost).

Keep everything running

No action needed. Choose this if you're still actively building on this project or plan to add the frontend next.

  • Your MySQL database persists between restarts. The data stays until you manually drop the database.
  • Your project folder and node_modules remain on your Desktop.
  • To start the server again later, open a terminal, navigate to the gym-reservation-api folder, and run node server.js.

Pause - I'll come back to this later

Shut down the running server process to free up your terminal. The database and project files persist until you're ready to return.

  • Stop the Node.js server by pressing Ctrl+C in the terminal where it's running.
  • Your MySQL database stays intact. You don't need to do anything to preserve it.
  • When you come back, navigate to gym-reservation-api and run node server.js to pick up where you left off.

Delete - I don't want to use this again

Remove all project resources and start fresh. This permanently deletes your database and project files.

  • Stop the Node.js server by pressing Ctrl+C in the terminal where it's running.
  • Drop the MySQL database by opening your MySQL terminal and running:
DROP DATABASE gym_reservations;
  • Delete the project folder by running the following command in your terminal:

macOS

cd ~/Desktop
rm -rf gym-reservation-api

Windows

cd ~\Desktop
rmdir /s /q gym-reservation-api
  • Confirm the folder is gone by running ls (macOS) or dir (Windows) on your Desktop. You should no longer see gym-reservation-api listed.

Nice Work!

Nice Work!

Nice work! You just built a fully functional backend API that manages gymnasium event reservations from scratch.

You've learned how to:

  • Build a complete REST API with Express.js and MySQL that handles JSON requests and responds with structured data.
  • Implement full CRUD operations (Create, Read, Update, Delete) backed by parameterized SQL queries that protect against injection.
  • Add conflict detection logic that prevents double-booking the gymnasium by checking for time overlaps before inserting new events.
  • Secret Mission: Added an approval workflow with status transitions so events move from pending to approved or rejected.

Ready to quiz yourself?