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:

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

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:

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

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

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

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

const { Pool } = require('pg');
require('dotenv').config();

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: {
    rejectUnauthorized: false
  }
});

module.exports = pool;

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:

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

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

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

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

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

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

const { Pool } = require('pg');
require('dotenv').config();

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: {
    rejectUnauthorized: false
  }
});

module.exports = pool;

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

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

POST /events : Create a new event

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

app.get('/events', async (req, res) => {
  // code to be written
})

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

try {
  // code to be written
} catch (e) {
  console.error(e);
  res.status(500).json({error: "Database error."}); // 500 = Internal Server Error
}

• 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("SELECT * FROM events");

• 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(200).json(result.rows); // 200 = OK

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

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

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:

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

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

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

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

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

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

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:

const { Pool } = require('pg');
require('dotenv').config();

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: {
    rejectUnauthorized: false
  }
});

module.exports = pool;

POST /event/:id/register

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

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

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

app.get('/events', async (req, res) => {
  // code to be written
})

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

try {
  // code to be written
} catch (e) {
  console.error(e);
  res.status(500).json({error: "Database error."}); // 500 = Internal Server Error
}

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

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

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/

The above is the detailed content of Create your own Event Scheduling API using Express and Supabase. For more information, please follow other related articles on the PHP Chinese website!