Introduction

For those that dont know Supabase: its an open-source Firebase alternative. You can self-host it, but most people use the official hosting. There is a limited free tier and a $25 per project per month paid plan. That price can be steep when starting out with a small side project. But it becomes great value as the project grows.

In this article, I'll write about the experiences I had when using Supabase to buildRailtrack. Thats why Ill only talk about using Supabase for web development with Next.js with TypeScript thats hosted on Vercel.

Im also not going to write about the real-time feature of Supabase because I havent worked with it. Having said that, you might have different needs that require real-time. If thats the case you should probably disregard much of what I say here. Instead, do your own research about the topic.

The same goes for Supabase edge functions. I deploy my sites on Vercel and use their backend hosting. So I just havent needed them. What I will write about are the database and authentication services of Supabase.

Query limitations

You cannot execute raw SQL queries from the Supabase client. Im sure there are reasons for this. But I often find myself wanting to write queries that the Supabase client does not support. Or that would at least be far more efficient when using proper SQL.

The solution for this is to create a named Postgres stored procedure which you can then call from the client.

const { data, error } = await supabase.rpc("hello_world");

I dont like this because the code for it is stowed away in the database. This means that every time I want to read or change it I need to leave my editor and go to the Supabase GUI. And it also means I now have logic inside my DB and thus outside of my codebase. Another option would be using aPostgres view. But that comes with similar drawbacks.

Ephemeral database structure

I like my database structure to be ephemeral. This means that I can tear it down and set it up again at any time (assuming there is no data). The source of truth for its structure should be in my codebase.

When using an ORM like Prisma thats the case. All my tables are defined in the Prisma schema. All I need to do to set up a fresh DB is configure the DB connection string and run one Prisma command. Et voil, I have a new DB with all my tables and fields.

If youre relying on the Supabase client, thats not possible. You dont define a schema in your client. Instead, you set it up in the Supabase GUI or via raw SQL statements. Sure, I can write SQL scripts to define my schema. Those can be re-run whenever I need to redo the DB setup. But to me, thats less ergonomic than having a Prisma-like schema.

The problem of going directly from DB to frontend

In real-world apps, you often want to aggregate data before sending it to the frontend. Thats what a proper backend is for. You can run intensive operations there without putting unnecessary load on the client.

In Railtrack I run complex calculations that couldnt be done within SQL. So I need to fetch the data, run the calculations in JavaScript and then display the result.

Now assume you use the Supabase client and go directly from DB to frontend. In that case, youd have to fetch huge amounts of data to the browser and run the calculations there just to display one calculated number. Thats not practical. So instead I have a proper backend that can run the calculations with ease.

I think Supabase edge functions could handle this use-case well. I havent tried them out since Im happy with Next.js API routes hosted on Vercel.

Typesafety

The Supabase JS client in combination with TypeScript is adventurous. There is the option togenerate types from your DB schema. However, this isnt very ergonomic since you need to use a third-party CLI tool for it. Its worth mentioning that Supabase justannounced the V2 JS client which comes with better type support. I havent tried it out yet so I cant say how it compares to Prisma. What I can say is that the experience of working with Prisma is leagues ahead of the current Supabase client when it comes to types.

Authentication

Authentication is by far the greatest thing about Supabase, if you ask me. With their $25 a month plan you get 100,000 monthly active users. One hundred thousand. Auth0 doesnt even list a price for that volume. You need to go through their enterprise sales channel. I assume youd be paying hundreds a month for it.

The developer experience of integrating auth is pretty good too. Its not like you only have to call one login function and boom your auth is done. Theres a bit more work to it if you want to have protected pages and access to the authed user on Next.js API routes. But everything is well documented and easy to implement.

Authorization

Per default, your tables have no protection whatsoever. We dont want that. So we need to enable the row level security (RLS) feature. Its defaults block all access from the Supabase client.

I like how they use RLS for authorization. Its a native Postgres feature instead of some proprietary system. But it comes with the same drawbacks we already talked about. You have to define authorization directly in your DB. And not in your source code.

Im someone whos used to the classic way of defining authorization. In my backend source code. So it was a big shift for me of trying to achieve the same with RLS. Dont get me wrong. RLS is a very powerful system. Its just very different from what Im used to doing for authorization.

Pick the good parts of Supabase while ignoring the rest

Im not a fan of using the Supabase client for fetching data. Yes, it may be great for beginners who just want some data in their frontend. Without running their own backend. Solutions that are so simple and try to abstract the backend rarely scale well to real-world use cases. You spend far more time creating workarounds than what the easy solution saves you. Another example of this is Hasura. It may look great for simple CRUD demos. But as soon as you try to solve actual problems you start having to fight its limitations.

Those critique points dont mean Supabase is bad. Not at all. Their auth service is great. It provides arguably the best value for money of any auth service. The same goes for the database. Its great if you treat it like any other Postgres DB. It makes far more sense to use it with something like Prisma and your own backend. You can modify your queries on the backend before sending them to your frontend. And thanks to tRPC you can establish end-to-end type safety. From DB all the way to your frontend code. Thats something the Supabase client cannot do. And thats precisely the stack I use forRailtrack.

I also love their admin interface. It allows me to see all my tables and users directly from the browser. Without needing a clunky SQL viewer on my computer.