Episode 511: Ant Wilson on Supabase (Postgres as a Service)

Ant Wilson on Supabase

In this episode of Software Engineering Radio, Jeremy Jung interviews Ant Wilson, the co-founder and CTO of Supabase, to discuss the development of an open-source alternative to Firebase using PostgreSQL. They delve into various aspects of Supabase, comparing it to Firebase, exploring its architecture, and touching on topics like API development with PostgREST, authentication using GoTrue, row-level security, forking open-source projects, implementing real-time updates with the write-ahead log, provisioning and monitoring databases, user support, incidents, and open-source licenses.

Transcript provided by IEEE Software magazine.

Jeremy Jung: 00:00:22 Hello, everyone, and welcome to Software Engineering Radio. I’m your host, Jeremy Jung. Today, I’m excited to talk to Ant Wilson, the co-founder and CTO of Supabase. Ant, welcome to Software Engineering Radio.

Ant Wilson: 00:00:32 Thanks so much. Great to be here.

Jeremy Jung: 00:00:35 When I hear about Supabase, I always hear about it in relation to two other products. The first is Postgres, which is an open-source relational database. We’ve got four shows on it that our audience can check out. And second is Firebase, which is a back-end as a service product from Google Cloud that provides a NoSQL data store. It provides authentication and authorization. It has a function as a service component. So, it’s really meant to be a replacement for you needing to have your own server, create your own backend. You can have that all be done from Firebase. I think a good place for us to start would be walking us through what Supabase is and how it relates to those two products.

Ant Wilson: 00:01:25 Yeah, so we brand ourselves as the open-source Firebase alternative. That came primarily from the fact that we ourselves used it as the alternative to Firebase. So my co-founder Paul, in his previous startup, was using Firestore, and as they started to scale, they hit certain limitations — technical scaling limitations — and he’d always been a huge Postgres fan. So he swapped it out for Postgres and then just started plugging in the bits that were missing, like the real-time streams, and he used a tool called PostgREST with a T for the CRUD APIs. And so he just built the open-source Firebase alternative on PostgREST, and that’s kind of where the tagline came from. But the main difference obviously is that it’s a relational database and not a NoSQL database, which means that it’s not actually a drop-in replacement, but it does mean that it kind of opens the door to a lot more functionality actually, which is hopefully an advantage for us.

Jeremy Jung: 00:02:27 And it’s a hosted form of Postgres. So, you mentioned that Firebase is different. It’s a NoSQL, people are putting in their JSON objects and things like that. So when people are working with Supabase, is the experience of, is it just I’m connecting to a Postgres database, I’m writing SQL? And in that regard, it’s kind of not really similar to Firebase at all. Is that kind of right?

Ant Wilson: 00:02:53 Yeah. I mean, the other important thing to notice is that you can communicate with Supabase directly from the client, which is what people love about Firebase is you just like put the credentials in the client, you write some security rules, and then you just start sending your data. Obviously, with Supabase, you do need to create your schema because it’s relational. But apart from that, the experience of client-side development is very much the same or very similar. The interface, obviously the API is a little bit different, but it’s similar in that regard. But I think, like I said, we are just a database company actually. And the tagline just explained really well, kind of the concept of what it is: like, a back-end as a service. It has the real-time streams. It has the OT layer. It has the also generated APIs. So, I don’t know how long we’ll stick with the tagline. I think we’ll probably outgrow it at some point, but it does do a good job of communicating roughly what the service is.

Jeremy Jung: 00:03:53 So when we talk about it being similar to Firebase, the part that’s similar to Firebase is that you could be a person building the front-end part of the website, and you don’t need to necessarily have a back-end application because all of that could talk to Supabase, and Supabase can handle the authentication, the real-time notifications, all those sorts of things, similar to Firebase, where basically you only need to write the front-end part and then you have to know how to set up Supabase in this case.

Ant Wilson: 00:04:27 Yeah, exactly. And some of the other — we love Firebase, by the way — we’re not building an alternative to try and destroy it. It’s kind of like, we’re just building the SQL alternative, and we take a lot of inspiration from it. And the other thing we love is that you can administer your database from the browser. So you go into Firebase and you can see the object tree, and when you’re in development, you can edit some of the documents in real-time. And so we took that experience and effectively built like a spreadsheet view inside of our dashboard. And also obviously have a SQL editor in there as well, and trying to create a similar developer experience because that’s where Firebase just excels, is the DX is incredible. And so we take a lot of inspiration from it in those respects as well.

Jeremy Jung: 00:05:15 And to make it clear to our listeners, as well, when you talk about this interface that’s kind of like a spreadsheet and things like that, I suppose it’s similar to somebody opening up PgAdmin, I suppose, and going in and editing the rows, but maybe you’ve got like another layer on top that just makes it a little more user friendly, a little bit more like something you would get from Firebase, I guess.

Ant Wilson: 00:05:39 Yeah. And we take a lot of inspiration from PgAdmin. PgAdmin is also open-source, so I think we’ve contributed a few things in, or trying to upstream a few things into PgAdmin. The other thing that we took a lot of inspiration from, for the table editor, what we call it is Airtable. And because Airtable is effectively a relational database that you can just come in and, you know, click to add your columns, click to add a new table. And so we just want to reproduce that experience, but again, backed up by a full Postgres dedicated database.

Jeremy Jung: 00:06:14 So when you’re working with a Postgres database, normally you need some kind of layer in front of it, right? That the person can’t open up their website and connect directly to Postgres from their browser. And you mentioned PostgREST before. I wonder if you could explain a little bit about what that is and how it works.

Ant Wilson: 00:06:34 Yeah, definitely. So yeah, PostgREST has been around for a while. It’s basically a server that you connect to your Postgres database and it introspects your schemas and generates an API for you based on the table names, the column names. And then you can basically then communicate with your Postgres database via this RESTful API. So you can do pretty much most of the filtering operations that you can do in SQL quality filters. You can even do full-text search over the API. So it just means that whenever you obviously add a new table or a new schema or a new column, the API just updates instantly. So you don’t have to worry about writing that middle layer, which was always the drag, right? Whenever you start a new project, it’s like, okay, I’ve got my schema, I’ve got my clients. Now I have to do all the connecting code in the middle, which is kind of no developer should need to write that layer in 2022.

Jeremy Jung: 00:07:36 So this the layer you’re referring to when I think of a traditional web application, I think of having to write routes, controllers, and create this sort of structure where I know all the tables in my database, but the controllers I create may not map one to one with those tables. And so you mentioned a little bit about how PostgREST looks at the schema and starts to build an API automatically. And I wonder if we could explain a little bit about how it does those mappings or if you’re writing those yourself.

Ant Wilson: 00:08:10 Yeah. It basically does them automatically by default, it will, you know, map every table, every column when you want to start restricting things. Well, there’s two parts to this. There’s one thing which I’m sure we’ll get into, which is how is this secure since you are communicating direct from the client. But the other part is what you mentioned giving like a reduced view of a particular bit of data. And for that, we just use Postgres views. So you define a view which might be, you know, it might have joins across a couple of different tables, or it might just be a limited set of columns on one of your tables. And then you can choose to just expose that view.

Jeremy Jung: 00:08:51 So it sounds like when you would typically create a controller and create a route, instead you create a view within your Postgres database and then PostgREST can take that view and create an endpoint for it, map it to that.

Ant Wilson: 00:09:06 Yeah, exactly.

Jeremy Jung: 00:09:08 And PostgREST is an open-source project. Right. I wonder if you could talk a little bit about sort of what its history was, how did you come to choose it?

Ant Wilson: 00:09:18 Yeah, I think Paul probably read about it on Hacker News at some point. Anytime it appears on Hacker News, it just gets voted to the front page because it’s so awesome. And we got connected to the maintainer, Steve Chavez at some point, I think he just took an interest in, or we took an interest in Postgres and we kind of got acquainted. And then we found out that, you know, Steve was open to work and this kind of like probably shaped a lot of the way we think about building out Supabase as a project and as a company in that we then decided to employ Steve full time, but just to work on PostgREST because it’s obviously a huge benefit for us. We are very reliant on it. We want it to succeed because it helps our business. And then as we started to add the other components, we decided that we would then always look for existing tools, existing open-source projects that exist before we decided to build something from scratch. So as we’re starting to try and replicate the features of Firebase, we would, and, or there’s a great example. We did a full audit of what are all the authorization and authentication, open-source tools that are out there and which one was, if any, would fit best. And we found a, Netlify built a library called GoTrue written in Go, which did pretty much exactly what we needed. So we just adopted that. And now obviously we just have a lot of people on the team contributing to GoTrue as well.

Jeremy Jung: 00:10:47 You touched on this a little bit earlier. Normally when you connect to a Postgres database, your user has permission to basically everything I guess, by default anyways. And so how does that work when you want to restrict people’s permissions, make sure they only get to see records they’re allowed to see, how is that all configured in PostgREST and what’s happening, you know, behind the scenes.

Ant Wilson: 00:11:11 Yeah. The great thing about PostgREST is it’s got this concept of role-level security, which actually, I don’t think I even rarely looked at until we were building out this OT feature where the security rules live in your database as SQL. So you do like a create policy query and you say, anytime someone tries to select or insert or update, apply this policy. And then how it all fits together is our server GoTrue. Someone will basically make a request to sign in or sign up with email and password. We create that user inside the database. They get issued a UUID and they get issued a JSON Web Token, a JWT, which when they have it on the client side, proves that they are this UUID that have access to this data. Then when they make a request via PostgREST, they send the JWT in the authorization header.

Ant Wilson: 00:12:10 Then PostgREST will pull out that JWT, check the sub claim, which is the UUID. And compare it to any rows in the database, according to the policy that you wrote. So, the most basic one is you say, in order to access this row, it must have a column UUID and it must match whatever is in the JWT. So, we basically push the authorization down into the database, which actually has, a lot of other benefits and that as you write new clients, you don’t need to have it live on an API layer or on the client. It’s kind of just, everything is managed from the database.

Jeremy Jung: 00:12:49 So the UUID, you mentioned that represents the user, correct?

Ant Wilson: 00:12:54 Yeah.

Jeremy Jung: 00:12:55 And then does that map to a user in PostgREST or is there some other way that you’re mapping its permissions?