How we use Supabase at Brick
Tags: supabase, flutter, dart, postgres
Published: 2022-12-06 01:00:00

---
title: How we use Supabase at Brick
published: true
description: Why we've choosen Supabase and examples on how we use it 
tags: supabase, flutter, dart, postgres
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h1e5er2spozqhicjbn3j.png
# Use a ratio of 100:42 for best results.
published_at: 2022-12-06 1:00 +0000
---

This post is part of today's Supabase Content Storm ⛈️ in preparation for their upcoming 6th [#SupaLaunchWeek](https://supabase.com/launch-week).

## What is Brick

[Brick](https://brickapp.net) 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](https://flutter.dev) app which works on [Android](https://play.google.com/store/apps/details?id=net.brickapp.android), [iOS](https://apps.apple.com/de/app/brick-immobilienkalkulation/id1625073581) and [Web](https://app.brickapp.net).

## What is Supabase

[Supabase](https://supabase.com) markets themselves as an open source Firebase alternative. They combine and extend existing open source software like [PostgreSQL](https://www.postgresql.org/), [GoTrue](https://github.com/supabase/gotrue), [Kong](https://github.com/Kong/kong) and [Deno](https://deno.land/) 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](https://supabase.com/docs).

## Why not Firebase?

One of the first BaaS (Backend-as-a-Service) on the market and the most popular is [Firebase](https://firebase.google.com) 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](https://cloud.google.com/firestore/docs/query-data/order-limit-data) is not possible in Firebase's database [Firestore](https://firebase.google.com/products/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](https://supabase.com/docs/guides/database/extensions) to, well, extend the database with even more functionalities, which aren't part of Postgres by default. One of them is [PostGis](https://postgis.net/), which allows you to query geometric types. Such as filtering by the distance between two objects. Firebase offers a brand-new [extension marketplace](https://firebase.google.com/docs/extensions/) 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.


![Excerpt from our profiles table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lge8p6kpbp4rcgsrbfbk.png)

### 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](https://www.postgresql.org/docs/current/datatype-json.html) 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](https://supabase.com/blog/sql-or-nosql-both-with-postgresql) about this approach. 

![Excerpt from our objects table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t293d7zq7x6053t10a3w.png)

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:
```json
{
	"rent":{
		"value": 5000
		"unit": "euro"
	}
}
```

We can query it in SQL via the `->` and `->>` operators.

```sql
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](https://supabase.com/storage) currently for three things:

1. 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.
2. 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.
3. 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 table `objects`. Each document has a specific publication type. A document is either `private`, meaning only the object's owner can see it, `public`, meaning everyone can view it, or `listed`. This hides the document in the app, but can still be downloaded when having the correct link. Much like YouTube's [unlisted video feature](https://support.google.com/youtube/answer/157177#unlisted). Since `publication_type` can only have three different values, we came using the [ENUM](https://www.postgresql.org/docs/current/datatype-enum.html) type:

 ```sql
CREATE TYPE public.document_publication_type AS ENUM
    ('private', 'public', 'listed');
 ```


![Excerpt from our object_documents table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qiaddflhnij3jttc3dj5.png)




### Edge Functions

We currently don't make use of [Supabase Edge Functions](https://supabase.com/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](https://deno.com/deploy) to deploy functions to the edge in Typescript. With Deno now providing [npm compatibility](https://deno.com/blog/v1.28#using-npm) this will be easy to implement. No more need for [Firebase Functions](https://firebase.google.com/products/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](https://supabase.com/launch-week), 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.