Migrating from Supabase

Within the final couple weeks, we have migrated Val Town away from Supabase to a less complicated database setup at Render. We’ve gotten just a few questions on this, so we needed to share what motivated the transfer, what we realized, and the way we pulled it off. Supabase is an enormously profitable product with a variety of completely satisfied customers, however we ended up having a variety of issues attending to scale to our staff’s wants. Your mileage might range, however we hope our expertise is a helpful knowledge level.

Val Town was began by Steve Krouse in July 2022 as a website to put in writing, run, deploy, and share snippets of server-side JavaScript. Our customers describe us as “Codepen for the backend”. Of us use us to make little APIs, schedule cron jobs, and make little integrations between companies, like downtime detectors, price watchers, programmatic notification services, and so forth.

Steve constructed the preliminary model of Val City on Supabase. I (Tom MacWright) joined in Jan 2023.

Supabase generally is a technique to construct your whole utility backend with out writing a backend server. Supabase is a database, however it additionally offers you methods to manage entry with row security policies. Then it allows you to question your database proper out of your frontend, by utilizing PostgREST. They combine gotrue as an authentication layer and let customers join your service. And eventually there’s a full-fledged UI that permits you to administrate the database and the entire associated companies with out writing a line of code or utilizing a CLI.

In impact, Supabase turns your database into a whole backend utility. It does so by utilizing each Postgres trick within the e book: not simply row-level safety, however we have been utilizing triggers, materialized views, database roles, and a number of schemas. Postgres is a tremendously highly effective platform: you may go so far as writing JavaScript embedded in SQL to put in writing logic within the database.

When it labored nicely, it was spectacular: it was the pace of utilizing Firebase, which I had heard about so many occasions, however with a fast-moving open supply stack.

The most important downside we encountered utilizing Supabase was native growth. After I joined, all of our growth occurred in manufacturing: everybody linked to the manufacturing database, all of our “migrations” the place carried out by modifying the reside database schema. We’d check migrations by duplicating tables in manufacturing and migrating them earlier than doing it for actual. Generally we’d use the online interface to vary issues like column sorts and indexes, which is the scariest of all – it doesn’t present a SQL preview of what it’s about to do, and typically what it does is unexpected.

This, to me, was fairly scary. Normally engineering groups work with native growth environments and staging environments, and solely cautiously and briefly contact the manufacturing database.

Fortunately, Supabase has been growing a toolchain for native growth: the Supabase CLI. The CLI manages the Supabase stack domestically: Postgres, gotrue, a realtime server, the storage API, an API gateway, a picture resizing proxy, a restful API for managing Postgres, the Studio net interface, an edge runtime, a logging system, and extra – a complete of 11 Docker containers linked collectively.

Sadly, we simply couldn’t get it to work. I hit every part from broken Docker containers, to a database migration system that couldn’t handle customized roles, to missing CLI help. We weren’t capable of get a neighborhood growth setting working for greater than a day at a time: the CLI would break, or migrations have been generated incorrectly and couldn’t be utilized.

A part of the difficulty with utilizing the CLI was that the documentation isn’t fairly written but. The command supabase db remote commit is documented as “Commit Distant Modifications As A New Migration”. The command supabase features new is documented as “Create A New Perform Regionally.” The documentation web page is gorgeous, however the phrases in it simply aren’t completed. These are essential instructions to doc: db distant commit truly impacts your native database and tweaks migrations. It’s actually essential to know what it’ll do earlier than operating it.

Sadly, the documentation on different elements is not a lot better: for instance, the Supabase Remix integration has an honest tutorial, however is lacking any conceptual or API reference documentation. Solely after spending a day or two implementing the Remix integration did I notice that it could require a reset of all our consumer periods as a result of utilizing it meant switching from localStorage to cookie-based authentication.

Then got here the downtime. Val City is answerable for operating scheduled bits of code: you may write a fast TypeScript perform, click on the Clock icon, and schedule it to run each hour from then on. We seen that vals would cease working each evening round midnight. After a little bit of sleuthing, it ended up that Supabase was taking a database backup that took the database absolutely offline each evening, at midnight. That is form of comprehensible, however what’s much less pretty is that it took a full week to get them to cease taking these backups and taking us offline.

Now, to be honest, Val city presently pummels databases. It’s a write-heavy utility that makes use of a variety of json columns and has a really giant desk through which we retailer all previous evaluations. And Supabase was very useful of their help, even serving to us rearchitect among the database schema. The applying that we’re operating is, in a manner, a stress check for database configuration.

Supabase has a form of uncommon scheme for database dimension. As an alternative of pre-allocating a big database and filling it over time, databases begin off small and are auto-resized as they develop. Your database begins out at 8GB, then will get upgraded as soon as it hits 90% of that to a database 50% bigger. Sadly, there was a fluke on this system: one Sunday, their system didn’t resize our database and as an alternative we have been put in read-only mode with the disk 95% full. In case you’ve handled methods like this earlier than, you may guess what occurs subsequent.

In case you get that near your most disk dimension, you get a form of catch-22: every part that you just need to do to scale back the dimensions on disk requires somewhat further momentary house, house that you just don’t have. Perhaps you need to VACUUM a desk to chop down on dimension – nicely, the VACUUM operation itself requires somewhat further storage, simply sufficient to place you over 100% of disk utilization and trigger a restart. Need to try to save just a few bytes by altering the kind of a column? You will hit 100% utilization and restart.

To make issues worse, the Supabase net consumer interface closely depends on the database itself – so the administration interface would crash when the database crashes. It’s good and preferable to have separate methods: one which runs the administration interface, one other that’s the factor being administrated.

Anyway, after a panicked Sunday afternoon four-alarm fireplace, I discovered a desk that we have been now not utilizing, which freed up 5 gigabytes of storage and allow us to get out of read-only mode. Just a few hours later the help staff responded with an replace.

A part of the ethical of the story is that databases are arduous. You can make an organization of simply operating databases reliably and achieve success if you happen to will be dependable and scalable. There are entire corporations like that, like CrunchyData. Database administration is a tough and unforgiving job. However this is the reason we pay managed suppliers to be the clever specialists who know methods to tune shared_buffers and take backups with out interrupting service. Positive, it’s good to have an incredible consumer interface and additional options, however a rock strong database must be the muse.

Below the hood, Supabase is simply Postgres. If you wish to join their service, by no means use the online consumer interface, and construct your utility as if Supabase was merely a Postgres database, you could possibly. Many individuals do, actually, as a result of they’re one of many few suppliers with a free tier.

The arduous half, although, is that if you happen to use Supabase as a “Firebase different” – if you happen to attempt to construct a variety of your utility layer into the database by utilizing triggers, saved procedures, row-level safety, and so forth – you’ll hit some locations the place your typical Postgres instruments don’t perceive Supabase, and vice-versa.

For instance, there are many nice methods for managing migrations. Within the TypeScript ecosystem, Prisma and drizzle-orm have been on the high of our record. However these migrations don’t help triggers, or row degree safety, so we might have a tough time evolving our database in a structured manner whereas nonetheless following the Supabase technique. So migrations could be powerful. Querying is hard too – querying whereas sustaining row-level-security is a subject of discussion in Prisma however it isn’t clear the way you’d do it in Kysely or drizzle-orm.

The identical kinds of disconnects stored occurring after we tried utilizing our database as Postgres after which administrating it typically with Supabase. For instance, we’d have a json column (not jsonb), however as a result of the Studio interface was opinionated, it wouldn’t correctly show the column type. Or we’d have JSON values in a desk, and be unable to export them due to broken CSV export within the net interface. We’d see points with composite international keys being displayed incorrectly and be afraid of points the place modifying the schema through the UI ran sudden and destructive queries. Plenty of these points have been mounted – the Studio now reveals varchar and json sorts as an alternative of a clean choose field, and may export CSVs appropriately.

In each instructions, it felt like there have been disconnects, that neither system was actually capturing 100% of its pair. There have been too many issues that the everyday database migration question instruments couldn’t perceive, and in addition issues that we might do the database instantly that wouldn’t be appropriately dealt with by the Supabase net interface.

Sadly, among the limitations of the Supabase technique trickled into our utility design. For instance, we have been constructing the entire controls round knowledge entry with Row-Stage Safety, which because the identify implies, is row-level. There is not a transparent technique to prohibit entry to columns in RLS, so when you’ve got a “customers” desk with a delicate column like “e-mail” that you don’t need everybody to have entry to, you’ve got a bunch of powerful options.

Perhaps you may create a database view of that desk, however it’s straightforward to shoot your self within the foot and by chance make that publicly-readable. We ended up having three consumer tables – Supabase’s inner desk, auth.customers, which is inaccessible to the frontend, a private_users desk, which was accessible to the backend, and a customers desk, which was queryable from the frontend.

We additionally architected a variety of denormalized database tables as a result of we couldn’t write environment friendly queries utilizing the default Supabase question shopper. In fact there are at all times tradeoffs between query-time and insert-time efficiency, however we have been caught at a really fundamental degree, unable to do a lot question optimization and due to this fact pushed to both write a variety of small queries or retailer duplicated knowledge in denormalized columns to make it quicker to question.

I believe that there’s a technique to make this work: to put in writing a variety of SQL and rely closely on the database. You are able to do something in Postgres. We might write exams with pgTAP and write JavaScript inside of Postgres functions with plv8. However this could purchase us much more into the concept of an utility in our database, which for us made issues more durable to debug and enhance.

See Also

We’d like know-how that’s much less immersive, no more

Finally, we switched to utilizing a “vanilla” Postgres service at Render.

We didn’t need to self-host Supabase, as a result of the devops points have been solely a part of the issue: we simply needed a database. Render has been internet hosting the remainder of Val Town for just a few months now and has been fairly nice. Render Preview Environments are wonderful: they spin up a whole clone of our entire stack — frontend remix server, node api server, deno analysis server, and now postgres database — for each pull request. The Blueprint Specification system is a pleasant center floor between manually configured infrastructure and the reams of YAML required to configure one thing like Kubernetes.

We thought-about a pair different Postgres hosts, like CrunchyData, neon, RDS, and so forth, however it’s arduous to beat Render’s cohesive and complete feature-set. They’re additionally extraordinarily competent {and professional} engineers; I’ve hosted functions on Render for years and have only a few complaints.

The aim was to have the ability to run the database domestically and check our migrations earlier than making use of them in manufacturing. We rewrote our knowledge layer to deal with the database as a easy persistence layer fairly than an utility. We eradicated all of the triggers, saved procedures, and row-level safety guidelines. That logic lives within the utility now.

We dramatically simplified how we’re utilizing the database and began utilizing drizzle-orm to construct SQL queries. It really works like a dream. Now our database schema is captured in code, we will create pull requests with proposed database migrations, and no person connects to the manufacturing database from their native computer systems. We have been even capable of eradicate just a few tables as a result of we might extra effectively question our knowledge and outline extra correct entry controls.

Migrating the info took per week. The primary problem was how giant our database is: 40gb.

We thought-about taking our service down for a pair hours to do the migration. However we’re a cloud service supplier and we take that duty significantly: if we go down, which means our consumer’s API endpoints and cron jobs cease operating. Downtime was our final resort.

The important thing perception was that 80% of our knowledge was in our tracing desk, which shops the historic analysis of each Val City perform run. This was historic knowledge and isn’t important to operations, so we selected to first migrate our crucial knowledge after which step by step migrate this log-like desk.

The following downside was enhancing the obtain and add speeds. We spun up an ec2 sever subsequent to Supabase in us-east-1 for the obtain and a server in Ohio Render area to be as shut as attainable for the downloads and add,s respectively. After some extra pg_dump optimizations, the obtain of every part however the tracing desk took quarter-hour. We scp-ed it to the Render sever and did a pg_restore from there, which took one other 10 minutes. We then reduce our manufacturing servers over to the brand new Render Postgres database.

We knowledgeable our prospects in regards to the migration and that their tracing knowledge could be restored shortly within the background. There was a handful of recent knowledge that had been created within the intervening ~half-hour. We pulled that diff knowledge manually, and uploaded it to the brand new database. The evaluations desk dump took all evening. Then the scp itself took a pair hours hours and $6 in AWS egress charges. It took one other evening to complete the add of the historic tracing knowledge.

Phrase to the clever: if you’re shifting knowledge into and out of databases, it pays to do it in the identical community because the database servers.

Now that Val Town is buzzing together with a easy Postgres setup on Render, we’re capable of evolve our database with conventional, old style migrations, and develop the appliance domestically. It feels unusually (again to the) future-istic to have migrations that run domestically, in preview branches, and upon merging to major. I shipped likes (the power to ❤️ a val) in a pair hours, and importantly, with out nervousness. Again in our Supabase days, we delayed options like that merely as a result of touching the database schema was scary.

Generally we miss the pleasant desk view of the Supabase interface. We watch their spectacular launch weeks with awe. Utilizing Supabase was like peeking into an alternate, possibly futuristic, technique to construct functions. However now we’re spending our innovation tokens elsewhere, and have been capable of ship quicker due to it.