Loading SportsDB into Supabase

#supabase #sportsdb #database #opensource

What is SportsDB?

In the real world, data is raw and complex, requiring a relational database to store and model it. One example is a complex dataset like SportDB, a sample dataset compiled from multiple sources, encompassing a variety of sports including football, baseball, ice hockey and more.

SportsDB has been designed to model "Sports Reality" as effectively as possible in a relational database context. The schema inherits from the open SportsML standard, adopting its vocabularies and core approach toward commonalities among the sports. It is capable of supporting queries for the most intense of sports data applications, yet is simple enough for use by those with minimal database experience.

SportsDB contains more than 80000 rows of data and more than 100 tables. It is often used to model a sports betting application, and it has been utilized in research projects dealing with sports data. The figure below shows SportsDB's schema and the relationships between the various entities.

What is Supabase?

Supabase is a backend-as-a-service built on top of PostgreSQL that can handle complex datasets like SportsDB and provide you with insights. Its easy-to-use APIs and superior querying capabilities make it the ideal backend-as-a-service for building today's modern applications. Supabase also natively includes security features like authentication and authorization using Row Level Security (RLS), which means more time can be spent developing your app.

Now let's look at how we can load SportsDB data into Supabase.

Loading SportsDB into Supabase

Supabase is built for developers, and you can get started for free using your existing Github account. Once your Supabase account is set up, you will access the Supabase dashboard. From here, go to All Projects > New Project.

Give your project a name and set the database password. You can also choose the region and adjust the pricing plan based on the requirements of your project. Now click the Create new project button.

Your project will take some time to initialize.

Download the SportsDB dataset using this link. The SportDB dataset consists of four tables: player table, team table, league table, and competition table. The table names are self-explanatory, so it is easy to find the table you want to work with. These four tables are related to one another through keys (IDs) which identify the relationships between them.

To run simple queries, we can use the built-in SQL Editor in the Supabase UI. With more complex queries, you can use psql, a terminal-based tool to work with a Postgres database. To learn how to install psql, check the documentation here.

To establish a connection with Supabase, you need a few details like Host, User, and Password or you can use an existing connection string. All these details can be found under Connection info in the Settings > Database section of our Supabase project page.

Database Information:

Connection String:

Here, we’re using the connection string to connect to the database.

Note: The password for the database will be the password that was provided when we created the project.

Once you are connected to the database, use the below command to load the SportsDB dataset.

\i  <path that has the SQL file>

After executing the command, you should see the SportsDB objects getting created and loaded with data.

This may take some time, but once the data loading is finished, you can verify these newly created tables from your Supabase project page by going to the Table Editor tab. You can see all the newly created tables here.

Now go to the API section on your Project page. Here we can see that Supabase has created all the required REST API endpoints to perform the CRUD operations.

Next, let’s make an HTTP request using cURL using anonymous access (key type as “anon”) for simplicity.

Now copy the required cURL command and run it in your terminal. The output should look like the screenshot below:

Here, we have selected all rows from the affiliations table. Similarly, we can perform all the other CRUD operations using the respective APIs.

Conclusion

Whether you have a simple or complex application, Supabase provides an excellent backend-as-a-service allowing you to build apps more easily and faster. With its inbuilt enterprise features such as authentication, file storage, and autogenerated APIs, you can now free up your time and focus more on building critical business app requirements, without worrying about the data management issues.