This post is part of today's Supabase Content Storm βοΈ in preparation for their upcoming 6th #SupaLaunchWeek.
What is Brick
Brick is a Real Estate investment calculation tool with an integrated marketplace to share and find investment objects from others to invest to. We offer calculation support for Buy & Hold, Fix & Flip and new building. There are many Excel templates on the internet, but they don't work great on mobile and are often somewhat limited. So we created Brick, a Flutter app which works on Android, iOS and Web.
What is Supabase
Supabase markets themselves as an open source Firebase alternative. They combine and extend existing open source software like PostgreSQL, GoTrue, Kong and Deno to provide a Postgres database, Authentication, Storage and Edge Functions. They offer official client libraries for JavaScript and Flutter (Dart). Learn more about them in their documentation.
Why not Firebase?
One of the first BaaS (Backend-as-a-Service) on the market and the most popular is Firebase by Google. They provide client libraries for Android, iOS and Web for a long time. Firebase offers much more individual features than Supabase, but the key features Database, Authentication and Storage are most important for the platform decision. We actually started with Firebase because it's known from previous projects and well established as BaaS, but we switched while being still in development to Supabase for a couple of reasons.
Firebase
- Query possibilities are a bit restricted. Such as, filtering one field and do an ordering on another field is not possible in Firebase's database Firestore. For Brick, this was a no-go, since we want to offer a great search experience for the marketplace. For example, you may search for a rent between 1k-3k and order your result by the range to your current user location. This brings me to another restriction: Geo queries are not well-supported by Firestore.
- This is subjective, but I much prefer SQL databases over the NoSQL in Firestore. It may be easier for newbies than SQL, but I much prefer a strict schema, where every field has a fixed type and for example can't be null. In combination with PostgreSQL being relational, I think it's much easier to handle later on.
Supabase
- Supabase builds on top of a pure PostgreSQL database, while still providing you direct access to the database itself. So you don't only get Supabase with it's integrated APIs, but also a fully featured raw SQL database. This offers many more additional possibilities.
- SQL is of course more complex than a NoSQL database, but Supabase makes most use cases painless by providing a great dashboard for e.g. security rules, table and database functions creation.
- Supabase offers many extensions to, well, extend the database with even more functionalities, which aren't part of Postgres by default. One of them is PostGis, which allows you to query geometric types. Such as filtering by the distance between two objects. Firebase offers a brand-new extension marketplace now as well, but the extensions are not as powerful as the Postgres ones.
How we use Supabase
Authentication
Each user does not only have an email and password, which are stored in the auth.users
table, but also a username, full name, website, etc. The auth
schema is managed by Supabase itself, so we don't add new columns into the existing auth.users
table. Instead, we add a new table in the public schema called profiles
. Each row is referenced to the auth.users
table, thus guaranteeing data integrity. We have triggers on the auth.users
table for row deletion and inserting. On insert, an empty row is inserted into the profiles
table in case the profile information insert fails on the client. On deletion, we delete all associated objects and their photos and documents from storage, as described later on.
Database
One advantage of NoSQL databases is their flexibility. You don't have to carefully plan your schema, which can sometimes be very handy. At Brick, we faced such situation: On the one hand, every object has the same attributes like owner
, name
, created_at
, type
. On the other hand, each object type (Buy and Hold, Fix and Flip and new building) has their own individual fields and types.
To combine these requirements, we came up with using the JSONB type for storing flexible data. This gives us the power to store same fields in the schema and flexible fields in a column of type jsonb
. Supabase just published a great blog article about this approach.
But can I use them in a query, one might ask. Of course, you can: Such as querying the rent of each object.
Let's say the data
column has the following format:
{
"rent":{
"value": 5000
"unit": "euro"
}
}
We can query it in SQL via the ->
and ->>
operators.
select (data -> 'rent' ->> 'value')::float from object_data;
->
Extracts the JSON object field with the given key, while ->>
extracts the field as TEXT
.
Storage
We are using Supabase Storage currently for three things:
- Users can upload a profile image like on almost every other social media like platform. These images are stored in a public bucket and associated with the user by their id from the
auth.users
table. - Every object does not only have a descriptive text and financial numbers, but photos as well. These are stored in a private bucket under a directory for each object.
- In addition to photos, objects can have multiple documents. These are saved as well under the object's directory. Documents come with some additional attributes, so they are referenced in another table called
object_documents
referencing the object id from the previous tableobjects
. Each document has a specific publication type. A document is eitherprivate
, meaning only the object's owner can see it,public
, meaning everyone can view it, orlisted
. This hides the document in the app, but can still be downloaded when having the correct link. Much like YouTube's unlisted video feature. Sincepublication_type
can only have three different values, we came using the ENUM type:
CREATE TYPE public.document_publication_type AS ENUM
('private', 'public', 'listed');
Edge Functions
We currently don't make use of Supabase Edge Functions, but plan to do so in the future. A new feature will be generating PDFs from your object's data to take your numbers everywhere you go.
Supabase uses Deno Deploy to deploy functions to the edge in Typescript. With Deno now providing npm compatibility this will be easy to implement. No more need for Firebase Functions.
Wrap up
Beside Edge Functions, we use all core Supabase features and see how well they work together. They are all rooted in the postgres database, allowing to control and view them from the heart of Supabase: The PostgreSQL database. In Firebase, each feature works individually well too, but are not interconnected. Despite the great Dashboard, Supabase still needs SQL to be managed. Each developer has to decide themself whether they are willing to invest that extra effort or not, but for Brick this was a great decision.
In addition, Supabase is by far not finished and still massively developing. Next week Dec 12 - 16 they host their 6th #SupaLaunchWeek, where they ship new features every day. So stay tuned on what they are going to deliver.
Thanks for reading! This was my first article and I hope you enjoyed it.
Top comments (2)
Love the use case of jsonb column! Any plans on open sourcing the app in the future?
Yeah the potential of jsonb columns is great. Iβve made the app for a client and donβt think thatβs planned for the future.