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 https://github.com/xerctia/gatherly

What is Express?

Express is a Javascript framework for setting up and building servers to handle various kinds of requests like GET, POST etc. Express is one of the most popularly used backend frameworks, and is also one of the easiest framework for beginners to start with. In this blog, we will be using Express for making our server and setting up the required endpoints.

What is PostgreSQL?

PostgreSQL is an open-source relational database management system (RDBMS), known for its reliability, scalability, and support for complex queries. It offers advanced features like support for JSON data, full-text search, and extensibility, making it versatile for both small projects and large-scale applications. PostgreSQL is popular among developers and is valued for its robust performance.

There are many PostgreSQL providers available on the web that allow usage of a PostgreSQL database, some free of cost and some with a paid plan. In this project, we will be using Supabase and its database as our PostgreSQL.

Setting up the project

• Create a folder for this project. I will name it Gatherly, which is the name I've decided.
• Set up Node and npm: npm init -y
• Install Express and other required packages: npm install express dotenv cors pg Note: pg is the package used for using PostgreSQL in Node.js.
• Now create an index.js file with the following boilerplate code:

const exp = require('express');
const cors = require('cors');

const app = exp();
const PORT = 3000 || process.env.PORT;

app.use(express.json());
app.use(express.urlencoded({extended: true}));
app.use(cors());

app.get("/", (req, res) => {
    res.send("Hello, Gatherly!");
})

app.listen(PORT, () => {
    console.log(`server started on ${PORT}`);
})

Congrats! You have successfully set up a basic server in Express!

Setting up Supabase with your Project

Supabase setup

• Go to https://supabase.com 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).
• Now go to the project dashboard and then navigate to Project Settings -> Database.
• In the starting of the page, a section of "Connection String" will be present. Click the Node.js tab here and copy the connection string and store it somewhere for now.
• Now go to the SQL Editor and run the following query to create an 'events' table:

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/