Database Migration Analysis
Prior to using Supabase, we were developing the Rhapsodies app using Firebase’s Firestore and Authentication. After running into annoyances while developing, the team coherently made a call to migrate to an alternative more fitting to the project. The chosen solution ended up being Supabase. This wiki further explains how we got to this resolution by analyzing our options.
Our reasoning for wanting to migrate had mostly to do with, in our opinion, the database-structure not matching our application’s. Our application is extremely structured, with no room for data-inconsistency. Firebase’s Firestore uses NoSQL, enforcing the normality of inconsistent data and not enforcing the need for relationships. We saw this as too messy for our uptight application enforcements. We also ran into the problem while developing when adding/removing an attribute, having to do so for every document in a collection or having to clear the collection fully. This made the agile work process we uphold more bothersome and inefficient than it needed to be.
For these reasons, we thought it would be worth analyzing whether SQL or NoSQL would better support our project.
The biggest impact between NoSQL and SQL for this project was found regarding data consistently and handling relationships efficiently. Where NoSQL starts to lack is when interacting between collections (for SQL; tables). NoSQL offers two solutions, copying over the data from doc A into doc B or storing the reference-ID from document B in document A (or vice-versa/both). Both aren’t ideal to us, as explained below.
NoSQL: Copying Data
Using Firebase and copying over the data from one document over to another as an attribute, makes fetching a document simple as it takes one single call. However, is rases the question of how to update or delete data. It also makes it very unsure whether all data is consistent. Do we know for sure that the copied data and original data always gets updated/deleted when the other version does? Besides that, does copying over data also bloat documents with duplicate data.
We did use this solution for a bit, but felt quite unsure about it as it seemed prone to inconsistent data and overengineered frontend code trying to safe-keep relations.
NoSQL: Storing Reference-IDs
Then there’s the option of storing reference-IDs in documents. Because there’s no double data, reference-data stays consistent. However, we did notice that Firebase doesn’t delete the reference-ID when the document it refers to gets deleted. Meaning possible errors when fetching data if this isn’t handled properly everywhere in the frontend.
With this solution, fetching data becomes increasing more complex with every added relationship. Let’s say that a document contains multiple reference-IDs. After fetching the document, a call for every relationship must also be done to fetch all documents referenced in the main document as well. This means more database calls, costing more resources (request-limits and time) and cluttering up the code. It creates an entry point for bugs every time a call happens, instead of one for fetching the desired object at ones.
SQL: Relationships
Relations between tables is a key strong suit of SQL-databases. With one single query we can fetch anything we need, even creating custom data-models on the spot. It is however very important to properly set-up and maintain relationships and create rules on how to extend over CRUD operations between connected tables.
Other mentions SQL
NoSQL isn’t perfect as seen in its lack regarding relationships, but SQL isn’t either. Generally speaking, NoSQL read and write operations are much faster than SQL. Which is definitely an important trade-off to consider. However in this project we haven't been able to detect a difference, as the data-set isn't and won't be that large.
Because it’s a comparable solution to Firebase. They’re both open source, have a good free-tier option and offer similar services like authentication.
Supabase allows us to write and call SQL-functions to add custom claims to JWTs, combining and integrating multiple features seamlessly. e.g., a claim granting specific user-permissions in the frontend, such as converting suggestions to repertoire songs.
Before fully deciding, we realized some fair considerations, worth evaluating before moving further. After acknowledging these, we decided to move further with them in mind.
- Migrating takes time and resources, which could be considered potentially wasteful of our limited time, as this is quite a short project. Not only is there the task of migrating, new skills to understand Supabase need to be learned.
- Supabase isn’t as large of a platform as Firebase, the community is smaller, meaning there is less support available. This could be an inconvenience, because there might be less solutions already shared online for certain problems.
- With SQL, relationship tables are needed (best-practice) between tables that are related, meaning extra tables, whereas collections (NoSQL) don’t have relationship-collections ‘cluttering up’ the database.
Because of the advantage in SQL regarding relationships we did migrate, despite that there were also benefits to not migrating (as discussed). We’ve been very happy with this decision so far and have been meeting our application’s needs with Supabase and due to SQL, all its rules it may enforce to uphold consistency and security.
More information about Supabase can be found in the set-up guide for this project.
