Create your own Event Scheduling API using Express and Supabase

Ever needed a platform to manage your club's events? Or perhaps schedule meetings in your office? But in search of affordable platforms, you get lost in the numerous options presented to you? Or maybe you just want to organize your life better and schedule when you have to attend which events?

Follow this post through the end, and you'll end up with a basic event scheduling API where all basic features like event creation and registration will be available.

The GitHub repository for this project is at and log into or create an account, and then create a new project with any name you see fit. I have named it Gatherly (obviously).

CREATE TABLE events (

  id SERIAL PRIMARY KEY,

  title VARCHAR(255) NOT NULL,

  description TEXT,

  start_time TIMESTAMP NOT NULL,

  end_time TIMESTAMP,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

Connecting database to Express

• Go to your project folder and create a file named .env. Write DATABASE_URL= and then paste the Connection String you copied earlier (Supabase setup: Step-3) and enclose it within double quotes. For example:
  

DATABASE_URL="postgresql://username:password@host:port/dbname"

• Create another JS file db.js to set up and connect the PostgreSQL database.
  

const { Pool } = require('pg');

require('dotenv').config();



const pool = new Pool({

  connectionString: process.env.DATABASE_URL,

  ssl: {

    rejectUnauthorized: false

  }

});



module.exports = pool;

• Now lastly, we need to import this connected database to our main index.js file.
  

const pool = require('./db');

Congratulations, you have successfully connected your Supabase database to your index.js file. We are now ready to start building the actual API endpoints.

API Endpoints

GET /events : Fetch all events

• Create a new GET method as follows:
  

app.get('/events', async (req, res) => {

  // code to be written

})

• Inside this function, we will write the actual code that will fetch us the data. First of all, let's implement a try-catch block for better error handling.
  

try {

  // code to be written

} catch (e) {

  console.error(e);

  res.status(500).json({error: "Database error."}); // 500 = Internal Server Error

}

• This format will be maintained for all the endpoints we will be building. Within the try block, we will write the code for the required feature.


• To fetch all the events in the database, we need to query the database and store it in a variable. Since we are following an asynchronous approach, so we need to use await to store the data correctly.
  

const result = await pool.query("SELECT * FROM events");

• The output of this query, i.e. result, has an array of objects called 'rows'. Here, we need to return all the events, so we will just return the entire 'rows'.
  

res.status(200).json(result.rows); // 200 = OK

• With this, our first endpoint is ready! To test it out, you can go to the Table Editor at Supabase project's dashboard and add 2 or 3 events for testing purposes. The entire code for this endpoint is:
  

app.get('/events', async (req, res) => {

    try {

        // Getting all events

        const result = await pool.query("SELECT * FROM events");

        res.status(200).json(result.rows); // 200 = OK

    } catch (e) {

        console.error(e);

        res.status(500).json({error: 'Database error'}); // 500 = Internal Server Error

    }

})

POST /events : Create a new event

• First of all, let's set up a basic boilerplate of an endpoint:
  

app.post("/events", async (req, res) => {

  try {

    // code to be written

  } catch (e) {

    console.error(e);

    res.status(500).json({error: "Failed to create event."}); // 500 = Internal Server Error

  }

})

• In this case, as we have some data we need from the user, we can define those outside of the try-catch block.
  

const { title, description, start_time, end_time } = req.body;

try {

.....

• Now within the try block, we need to write the query to insert a row in a table. The query() method lets you denote variable values in the string query as $1, $2 etc. and then provide those variables in order in an array. This will be how we add our variable inputs from user to the query.
  

const result = await pool.query(

  "INSERT INTO events (title, description, start_time, end_time) VALUES ($1, $2, $3, $4) RETURNING *",

  [title, description, start_time, end_time]

);

• Again, like the previous time, we will print the result rows. But this time, we only need to print the first element of the 'rows' array, which will be the row we just inserted.
  

res.status(201).json(result.rows[0]); // 201 = Resource Created

• Hurray, we've built our endpoint to add new events! Here's the entire code:
  

app.post('/events', async (req, res) => {

    const { title, description, start_time, end_time } = req.body;  // Destructuring the input data



    try {

        // Insert the event details into a new row in the table

        const result = await pool.query(

            'INSERT INTO events (title, description, start_time, end_time) VALUES ($1, $2, $3, $4) RETURNING *',

            [title, description, start_time, end_time]

        );

        res.status(201).json(result.rows[0]); // 201 = Resource Created

    } catch (e) {

        console.error(e);

        res.status(500).json({error: 'Failed to create event.'}); // 500 = Internal Server Error

    }

})

GET /event/:id : Fetch details of an individual event

• I'm sure you are clever enough to be able to set up a basic function for any endpoint, so I will not be showing that every time.


• Here, our goal is to create a dynamic endpoint, where the value of 'id' will keep changing. So, we've marked it as :id, and we can access its value as follows:
  

const { id } = req.params;

This can also be done outside the try-catch, just like the input values in the previous endpoint.

• Now within the try block, we need to write the query for selecting rows where the 'id' field will be equal to the id provided. If there's no results found, that means the event with this id doesn't exist, so we can return a 404 error.
  

const result = await pool.query("SELECT * FROM events WHERE id = $1", [id]);

if (result.rows.length === 0) {  // event not found

  return res.status(404).json({error: "Event not found."});

}

• If this isn't the case, that means the event exists. But since 'rows' is an array, so even if it contains one element, we need to access it by rows[0]. So the required row is in rows[0].
  

res.status(200).json(result.rows[0]);

• And voila! You can now successfully get a specific event's details! Here's the full code:
  

app.get('/event/:id', async (req, res) => {

    const { id } = req.params; // Destructuring the URL parameters



    try {

        // Get the current event row

        const result = await pool.query('SELECT * FROM events where id = $1', [id]);

        if (result.rows.length === 0) {  // length 0 means no rows returned, i.e. event isn't found

            res.status(404).json({error: 'No event found.'}); // 404 = Resource Not Found

        } else {

            res.status(200).json(result.rows[0]); // 200 = OK

        }

    } catch (e) {

        console.error(e);

        res.status(500).json({error: 'Database error.'}); // 500 = Internal Server Error

    }

})

User Registration

Supabase

For implementing this feature, you will need to create a new table in Supabase first.

Go to SQL Editor and run the following query:

CREATE TABLE registrations (

  id SERIAL PRIMARY KEY,

  event_id INTEGER NOT NULL,

  name VARCHAR(255) NOT NULL,

  country VARCHAR(100) NOT NULL,

  specialty VARCHAR(255) NOT NULL,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE

);

POST /event/:id/register

• We can take the input values as well as the parameter value outside the try-catch.
  

const { id } = req.params;

const { name, country, specialty } = req.body;

try {

.....

• Now we will first check whether the event with 'id' exists. For this, we will follow the approach of GET /event/:id, and check if rows.length is nonzero, i.e. it has some results.
  

const event = await pool.query("SELECT * FROM events WHERE id = $1", [id]);

if (event.rows.length === 0) {

  return res.status(404).json({error: "Event not found.'});

}

• Now that the event exists, we can write the query for actually adding a registration to the database to the new table we just created, i.e. 'registrations'.
  

const registration = await pool.query(

  "INSERT INTO registrations (event_id, name, country, specialty) VALUES ($1, $2, $3, $4) RETURNING *",

  [id, name, country, specialty]

);

res.status(201).json({msg: "Registration successful!", result: registration.rows[0]);

• Thus we have implemented a feature for registering users for an event as well! Here's the entire code:
  

app.post('/event/:id/register', async (req, res) => {

    const {id} = req.params;  // Destructuring URL parameters

    const {name, dept, college, country, laptop} = req.body;  // Destructuring input data



    try {

        // Checking if event exists

        const event = await pool.query('SELECT * FROM events where id = $1', [id]);

        if (event.rows.length === 0) {  // length 0 means no rows returned, i.e. event isn't found

            return res.status(404).json({error: 'Event not found.'}); // 404 = Resource Not Found

        }



        // Insert new row for new registration

        const registration = await pool.query(

            'INSERT INTO registrations (event_id, name, dept, college, country, laptop) VALUES ($1, $2, $3, $4, $5, $6) RETURNING *',

            [id, name, dept, college, country, laptop]

        )



        res.status(201).json({msg: 'Registration successful!', result: registration.rows[0]}); // 201 = Resource Created

    } catch (e)

        console.error(e);

        res.status(500).json({error: 'Failed to register for event.'}); // 500 = Internal Server Error

    }

})

GET /event/:id/registrations : Fetch all registrations for a given event

This one is a homework for you guys. (Don't be mad, if you couldn't do it even after trying, the GitHub code is always available)

A hint: You may check if the event exists in the same way as we did in POST /event/:id/register. After that, you need to write a SELECT query for registrations table to fetch all rows with the given event_id.

Similarly, you may also try and build an endpoint for deleting a particular event, like DELETE /event/:id .

Wrapping Up

Congratulations! You have successfully created your own API for scheduling events and managing user registrations. You have come a long way.

You can add more features, like adding a cron job so that events whose end_time have passed are deleted automatically.

If you liked this post, drop a like, and comment if you have any doubts or just want to chat related to this. Also follow me on LinkedIn: https://www.linkedin.com/in/amartya-chowdhury/