10 ms·

Migrating from Supabase

382 points by stevekrouse 3y ago

dbmikus 3y ago
Personally, I had a really easy time getting Supabase to work locally. However, we use `dbmate` to manage our migrations instead of built-in Supabase migrations. Also curious to hear from others on this: > After a bit of sleuthing, it ended up that Supabase was taking a database backup that took the database fully offline every night, at midnight. This seems like a terrible design decision if true. Why not just backup via physical or logical replication? And totally hear the issues here with database resizing and vacuuming and other operations. That stuff is a big pain when it breaks.
- crooked-v 3y ago
  Supabase daily backups just use pg_dump. If their database was going offline, then something else was broken.
  - kiwicopple 3y ago
    (supabase ceo) To give context, Val Town have a particularly write-heavy setup, storing a lot of json strings. The nightly backups were causing write-contention, even at their relatively small size. We didn’t detect errors because they were application-level. We should have moved them to PITR as soon as they mentioned it since the timing was so obviously coinciding with backups. We’re investigating moving everyone to PITR (including the free tier). At the very least, we’ll add more control for backups - allowing users to change the maintenance window, or possibly disabling backups completely if they are managing it themselves.
    - aeyes 3y ago
      how does a backup cause write contention? are you backing up to the same disk? also why are backups using pg_dump? that’s not a backup.
      - nfm 3y ago
        https://www.postgresql.org/docs/current/app-pgdump.html https://www.postgresql.org/docs/current/app-pgdump.html > pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently.
      - paulryanrogers 3y ago
        Possibly MVCC keeping a snapshot so the whole dump is consistent.
omeze 3y ago
The local development & database migration story is Supabase's biggest weakness. I hate having to do migrations live in prod. The admin dashboard is just so much better than any alternative Postgres tooling that it's been worth using despite that. Takes care of the stuff I'd normally be sweating over when writing migrations like nullable fields / FK constraints / JSON formatting for default fields. Would be great if Supabase allowed for a "speculative migration" in its UX where it spit out a file you could use locally to test beforehand.
- crooked-v 3y ago
  If you use the CLI, `supabase start` spins up a Docker instance built from all your migration .sql files [1]. If anything, I think the admin dashboard encouraging directly doing operations on the database is the biggest weakness of Supabase. I would much prefer being able to lock it down to purely CI-driven migrations. [1]: https://supabase.com/docs/guides/getting-started/local-development#database-migrations https://supabase.com/docs/guides/getting-started/local-devel...
- kiwicopple 3y ago
  Please don't use the Dashboard to edit your database in production. We're working on Preview Databases which will help enforce this. For now this fits into our Shared Responsibility Model: https://supabase.com/docs/guides/platform/shared-responsibility-model https://supabase.com/docs/guides/platform/shared-responsibil... You are responsible for a workflow that's suitable for your application. Once you get into production, you should be using Migrations for every database change. I have a more thorough response here: https://news.ycombinator.com/item?id=36006018 https://news.ycombinator.com/item?id=36006018
  - searchableguy 3y ago
    > Please don't use the Dashboard to edit your database in production. You should make the default editor read only and allow switching to write mode with a big warning. This would discourage people from writing SQL or using UI to modify in production. The dashboard has always screamed "use me to edit" and I have used supabase in the beginning and very recently too. Nothing has changed to discourage it so far. Maybe something like mode button which is present at top and you can click to switch between development and production mode? This would also change a couple more things which you do not want to touch in production by accident.
armatav 3y ago
It definitely needs DB branching.
- Ken_At_EM 3y ago
  Why is this comment getting downvoted?
exac 3y ago
> Local development was tough > Unfortunately, we just couldn’t get it to work Every time I read one of these migration stories, I find myself waiting with baited breath for the part the team couldn't achieve. After finding it, the remainder of the story becomes difficult to read. It isn't necessarily the team's fault, the developer experience clearly has room for improvement. Props to Val Town for being so honest, it is difficult to do.
- bitdivision 3y ago
  Can you elaborate on why it becomes difficult to read? Was there something obvious they missed?
pbreit 3y ago
The options for spinning up CRUD apps (ie, 95% of projects) are still quite miserable.
- rco8786 3y ago
  Assuming you’re talking about Supabase, I kind of disagree. There’s an initial learning curve with the row level security stuff, but once you get a good grasp of it and come up with a few patterns that suit your needs it’s insanely fast to develop on. You’re trading the time it takes to build and manage an api for the time it takes to setup RLS.
- reducesuffering 3y ago
  https://github.com/t3-oss/create-t3-app https://github.com/t3-oss/create-t3-app hosted on Vercel + Planetscale / Railway DB is very easy, I would be surprised if you were miserable doing that. Mostly that solves setting up Auth and Prisma SQL ORM to your DB, but Next.js App directory with the Prisma setup (2 files / 50 LOC) done is even smoother.
- tr3ntg 3y ago
  I'd say Supabase is great at spinning up CRUD apps. If anything, this article could be summarized as "Because Val Town is much more than a CRUD app, they had a harder time with Supabase than the average."
gajus 3y ago
Curious why have you decided for Drizzle over Kysely. I was recently exploring the space, and Kysely came on top as a framework with broader adoption. https://npmtrends.com/drizzle-orm-vs-kysely https://npmtrends.com/drizzle-orm-vs-kysely
- Akkuma 3y ago
  You can actually integrate both as well if you really want to leverage Drizzle for schema building and migrations: https://github.com/drizzle-team/drizzle-orm/tree/main/drizzle-orm/src/kysely https://github.com/drizzle-team/drizzle-orm/tree/main/drizzl...
- reducesuffering 3y ago
  You should consider the similar reasons why you chose Kysely over Prisma. Prisma has far broader adoption. https://npmtrends.com/drizzle-orm-vs-kysely-vs-prisma https://npmtrends.com/drizzle-orm-vs-kysely-vs-prisma
- tmcw 3y ago
  Sure! I think Kysely is great too, but went with Drizzle for a few different reasons: Kysely is a little more established than Drizzle, which I think is one of the major reason why it has broader adoption. My bet is that Drizzle is moving really fast, gaining adoption, and might catch up at some point. It's also - in terms of performance - super fast, and nicely layers on top of fast database clients. Some of the differences that I liked about Drizzle were the extra database drivers being core and developed as part of the main project. It supports prepared statements, which is awesome. The Drizzle API also covers an impressive percentage of what you can do in raw SQL, and when there's something missing, like a special column type, it's been pretty straightforward to add. I prefer the way that it lets us write parts of queries, and compose them - like you import expressions like "and" and "eq" and you can write and(eq(users.id, 'x'), eq(users.name, 'Tom')) and you can actually stringify that to the SQL it generates. Or you can do a custom bit of SQL and use the names of table columns in that, like `COUNT(${users.name})`. I can't say scientifically that this is superior, and it's almost a little weird, but I've really found it a nice way to compose and debug queries. That said, Kysely is also a great project and it'd be possible to build great products with it, too. I just found the momentum, API, and philosophy of Drizzle to be pretty compelling.
joshghent 3y ago
Echo all the words from the author here, and kudos for being transparent. I’ve faced exactly the same problems building my new product. But, on the other hand, Supabase was incredibly easy to setup, and meant I could worry about infrastructure later. Pros and cons like with everything, and always wise to understand the flaws of the tech you’re using.
doctorpangloss 3y ago
> The CLI manages the Supabase stack locally: Postgres, gotrue, a realtime server, the storage API, an API gateway, an image resizing proxy, a restful API for managing Postgres, the Studio web interface, an edge runtime, a logging system, and more – a total of 11 Docker containers connected together. Can Supabase author a set of Kubernetes manifests similar to what they run in production, and perhaps distribute those?
- ahachete 3y ago
  This is not from Supabase, but as a community contribution. See upthread [1]: "at StackGres we have built a Runbook [2] and companion blog post [3] to help you run Supabase on Kubernetes." [1]: https://news.ycombinator.com/item?id=36006308 https://news.ycombinator.com/item?id=36006308 [2]: https://stackgres.io/doc/latest/runbooks/supabase-stackgres/ https://stackgres.io/doc/latest/runbooks/supabase-stackgres/ [3]: https://stackgres.io/blog/running-supabase-on-top-of-stackgres/ https://stackgres.io/blog/running-supabase-on-top-of-stackgr...
ilrwbwrkhv 3y ago
I also had the same experience with Supabase. Even though it looks like a great product initially, it has a lot or errors and bugs when you are trying to actually build something more robust than a toy app. Local development is a massive pain with random bugs. The response time of the database also varies all over the place. But the most important problem that we faced, was having so much of application logic in the database. Row level security is their "foundational piece", but there is a reason why we moved away from database functions and application logic in database over a decade ago: that stuff in unmaintainable. There is also really poor support and at the end of the day, the whole platform felt like a hack. I think now, for most apps with up to 500_000 users (with 10_000 concurrent realtime connections) PocketBase is the best PaaS out there having tested a bunch of them. A single deployable binary which PocketBase provides is a breath of fresh air. Anything more than that, just directly being on top of bare metal or AWS / GCP is much better.
- xrd 3y ago
  I agree. I would love to see more articles on pocketbase. It's phenomenal and ganigeorgiev is an animal about responding to bugs and discussions. He's got to be a hybrid human and ChatGPT robot.
  - ilrwbwrkhv 3y ago
    What's also really cool is that you can also just use PocketBase as a Go library and just build your app around it like any normal web framework, while still having a great UI for quick prototyping. And when you need more custom behaviour instead of database functions, you just write some Go code while still compiling everything down to a single binary that you can copy over.
- dingledork69 3y ago
  I don't use supabase, but am a big postgres fan: > that stuff in unmaintainable Wrong. Version your functions and use something like liquibase to apply migrations.
  - 3y ago
kiwicopple 3y ago
hey hn, supabase ceo here the Val Town team were kind enough to share this article with me before they released it. Perhaps you know from previous HN threads that we take customer feedback very seriously. Hearing feedback like this is hard. Clearly the team at Val Town wanted Supabase to be great and we didn’t meet their expectations. For me personally, that hurts. A few quick comments 1. Modifying the database in production: I’ve published a doc on Maturity Models[0]. Hopefully this makes it clear that developers should be using Migrations once their project is live (not using the Dashboard to modify their database live). It also highlights the options for managing dev/local environments. This is just a start. We’re building Preview Databases into the native workflow so that developers don’t need to think about this. 2. Designing for Supabase: Our goal is to make all of Postgres easy, not obligatory. I’ve added a paragraph[1] in the first page in our Docs highlighting that it’s not always a good idea to go all-in on Postgres. We’ll add examples to our docs with “traditional” approaches like Node + Supabase, Rails + Supabase, etc. There are a lot of companies using this approach already, but our docs are overly focused on “the Supabase way” of doing things. There shouldn’t be a reason to switch from Supabase to any other Postgres provider if you want “plain Postgres”. 3. That said, we also want to continue making “all of Postgres” easy to use. We’re committed to building an amazing CLI experience. Like any tech, we’re going to need a few iterations. W’re building tooling for debugging and observability. We have index advisors coming[2]. We recently added Open Telemetry to Logflare[3] and added logging for local development[4]. We’re making platform usage incredibly clear[5]. We aim to make your database indestructible - we care about resilience as much as experience and we’ll make sure we highlight that in future product announcements. I’ll finish with something that I think we did well: migrating away from Supabase was easy for Val Town, because it’s just Postgres. This is one of our core principles, “everything is portable” (https://supabase.com/docs/guides/getting-started/architecture#everything-is-portable https://supabase.com/docs/guides/getting-started/architectur...). Portability forces us compete on experience. We aim to be the best Postgres hosting service in the world, and we’ll continue to focus on that goal even if we’re not there yet. [0] Maturity models: https://supabase.com/docs/guides/platform/maturity-model https://supabase.com/docs/guides/platform/maturity-model [1] Choose your comfort level: https://supabase.com/docs/guides/getting-started/architecture#choose-your-comfort-level https://supabase.com/docs/guides/getting-started/architectur... [2] Index advisor: https://database.dev/olirice/index_advisor https://database.dev/olirice/index_advisor [3] Open Telemetry: https://github.com/Logflare/logflare/pull/1466 https://github.com/Logflare/logflare/pull/1466 [4] Local logging: https://supabase.com/blog/supabase-logs-self-hosted https://supabase.com/blog/supabase-logs-self-hosted [5] Usage: https://twitter.com/kiwicopple/status/1658683758718124032?s=20 https://twitter.com/kiwicopple/status/1658683758718124032?s=...
motoxpro 3y ago
Great read. Similar to my experience with Hasura. Migrations we’re better there but the row level security was a nightmare. Went to just a custom node backend with prisma and it’s a dream. No more writing tons of json rules and multiple views just to not query the email field. Seems like these types of services are good for basic large scale crud applications, probably why you have Hasura pivoting to enterprise. The quote at the end of going back the future is exactly how I felt. Will never use a Hasura/Supabase/etc again. Just makes things more difficult.
- danielrhodes 3y ago
  Had a similar experience with Hasura. They have done some amazing things leveraging Postgres and GraphQL. But there were just too many things that got really questionable. Things like migrations becoming inconsistent with metadata, schema lock in, poor ability to do rate limiting, having to use stored procedures for everything, weird SQL that had performance issues, unexplained row level locking, and so on. Local development was a total mess. Ultimately we were making architectural decisions to please Hasura, not because it was in the best interests of what or how we were building.
  - motoxpro 3y ago
    100%. Same things we ran into. Definitely a trap.
simonw 3y ago
The documentation section here applies to so many products I've battled in the past. > The command supabase db remote commit is documented as "Commit Remote Changes As A New Migration". The command supabase functions new is documented as "Create A New Function Locally." The documentation page is beautiful, but the words in it just aren't finished. Great documentation is such a force multiplier for a product. It's so worthwhile investing in this. Don't make your most dedicated users (the ones who get as far as consulting your documentation) guess how to use your thing!
- kiwicopple 3y ago
  this is very fair criticism. We hired a Head of Docs in March. I hope the improvements are evident since he joined, both in content and in usability. We have a long way to go, but we're working on it.
  - pluto_modadic 3y ago
    yeah, most golang/rust/API documentation in products seems to think that "the function name is documentation", which.... no it's not. that's a tooltip in an IDE, not a docs website.
aguynamedben 3y ago
I love Steve Krouse!!!
ahachete 3y ago
A mid way could be self-hosting Supabase, whether you use more or less Supabase features. I know self-hosting might be challenging, specially getting a production-ready Postgres backend for it. That's why at StackGres we have built a Runbook [1] and companion blog post [2] to help you run Supabase on Kubernetes. All required components are fully open source, so you are more than welcome to try it and give feedback if you are looking into this alternative. [1]: https://stackgres.io/doc/latest/runbooks/supabase-stackgres/ https://stackgres.io/doc/latest/runbooks/supabase-stackgres/ [2]: https://stackgres.io/blog/running-supabase-on-top-of-stackgres/ https://stackgres.io/blog/running-supabase-on-top-of-stackgr... update: edit
jackconsidine 3y ago
Nice read. I run 5-6 projects on Supabase currently. I have also run into the local development / migration obstacles. It's otherwise been pretty great for our needs
- SOLAR_FIELDS 3y ago
  The CLI could use some love for sure. I think the migrations experience is also where I’ve felt the most pain. I will say, the CLI very heavily assumes that you are using the cloud product as the remote, which I guess is absolutely intentional (it’s a path to get users onto the product) but it was kind of annoying to figure that out halfway into a POC like I did. Don’t go in expecting you can point the CLI at some self hosted remote. It’s not possible without forking, making significant changes and rebuilding the CLI, at least at the time I was doing this a few months ago.
  - sweatybridge 3y ago
    Hello, I work on CLI full time. Things have certainly improved over the last few months on using this tool for migrating self-hosted databases. Currently all supabase db and migration commands support --db-url flag [1] which allows you to point the CLI to any Postgres database by a connection string. If there's any use case I missed, please feel free to open a GitHub issue and I will look into it promptly. [1] https://supabase.com/docs/reference/cli/supabase-db https://supabase.com/docs/reference/cli/supabase-db
Ken_At_EM 3y ago
This echos my experience with Supabase exactly. We migrated to a similar solution for the same reasons.
tonerow 3y ago
Supabase is also great for auth. Did you reimplement auth yourself or switch to another auth service or framework?
- tmcw 3y ago
  We switched to Clerk.dev. Thankfully we had only supported magic link auth, so there wasn't much information to migrate over. Clerk has been pretty good - they have a great Remix integration and solid admin experience.
  - doodlesdev 3y ago
    Any specific reason to go with it instead of alternatives?
- doodlesdev 3y ago
  PSA: Supabase Auth is based on their fork [0] of Netlify's Gotrue [1]. If you are migrating out of Supabase completely you can just drop in Gotrue for authentication. [0]: https://github.com/supabase/gotrue https://github.com/supabase/gotrue [1]: https://github.com/netlify/gotrue https://github.com/netlify/gotrue
PKop 3y ago
Interesting statement here: "We rewrote our data layer to treat the database as a simple persistence layer rather than an application. We eliminated all the triggers, stored procedures, and row-level security rules. That logic lives in the application now." Reminds me of the article and discussion here[0] over whether to put logic in the database or not and to what degree. [0] https://news.ycombinator.com/item?id=35643432 https://news.ycombinator.com/item?id=35643432 "Use Databases Without Putting Domain Logic in Them"
- bodecker 3y ago
  Also reminds me of this Martin Fowler post [0]: "The situation becomes interesting when the vast majority of your data sits in a single logical database. In this case you have two primary issues to consider. One is the choice of programming language: SQL versus your application language. The other is where the code runs, SQL at the database, or in memory. SQL makes some things easy, but other things more difficult. Some people find SQL easy to work with, others find it horribly cryptic. The teams personal comfort is a big issue here. I would suggest that if you go the route of putting a lot of logic in SQL, don't expect to be portable - use all of your vendors extensions and cheerfully bind yourself to their technology. If you want portability keep logic out of SQL." [0] https://martinfowler.com/articles/dblogic.html https://martinfowler.com/articles/dblogic.html
Ethan_Mick 3y ago
How do people on HN like Row Level Security? Is it a better way to handle multi-tenant in a cloud SaaS app vs `WHERE` clauses in SQL? Worse? Nicer in theory but less maintainable in practice? fwiw, Prisma has a guide on how to do RLS with it's client. While the original issue[0] remains open they have example code[1] with the client using client extensions[2]. I was going to try it out and see how it felt. [0]: https://github.com/prisma/prisma/issues/12735 https://github.com/prisma/prisma/issues/12735 [1]: https://github.com/prisma/prisma-client-extensions/blob/main/row-level-security/script.ts https://github.com/prisma/prisma-client-extensions/blob/main... [2]: https://www.prisma.io/docs/concepts/components/prisma-client/client-extensions#example-use-cases-for-extended-clients https://www.prisma.io/docs/concepts/components/prisma-client...
- atonse 3y ago
  It is highly appealing to have that defense in depth. However, when building a prototype or a product, not having experience in it causes me to worry that we will end up being stuck with a choice where it's very hard to pull ourselves out of. So instead we've stuck to having that filtering logic in the application side. The main concern is how user auth/etc works in Postgres. (lack of knowledge, not lack of trust). Because we also have complex filtering like, "let me see all the people in my team if I have this role, but if i'm a public user, only show this person" etc
- crooked-v 3y ago
  The main issue we've had with it is that it's just plain slow for a lot of use cases, because Postgres will check the security for all rows before filtering on the joins, doing anything with WHERE clauses, doing anything to even tentatively take LIMIT into account, etc. Imagine a 1-million-row table and a query with `WHERE x=y` that should result in about 100 rows. Postres will do RLS checks on the full 1 million rows before the WHERE clause is involved at all.
  - kiwicopple 3y ago
    With PostgREST you can use the pre-fetch method to solve this: https://postgrest.org/en/stable/references/transactions.html?highlight=pre-fetch#pre-request https://postgrest.org/en/stable/references/transactions.html... You can use that to inject your ACL/permissions into a setting - set_config('permissions', '{"allowed":true}'). Then in your RLS rules you can pluck them out - current_setting('permissions'::jsonb). This should make your RLS faster than most other options, in theory, because of data co-location
throwawaymaths 3y ago
Amazed they used scp instead of rsync
- stevekrouse 3y ago
  I'm not great at shell scripting tbh. It's not a coincidence that I built a cloud scripting platform
kdrag0n 3y ago
Honestly, I want to like Supabase but a lot of this resonates with me even for a fairly small project. I also ended up with 3 user tables due to RLS limitations: auth users, public user profile info, and private user info (e.g. Stripe customer IDs). PostgREST's limitations also had me going back to an API server architecture because I definitely didn't want to write logic in database functions. The only reason I haven't migrated yet is because I'd have to rewrite the data layer to use Prisma/Drizzle instead of Supabase's PostgREST client, and considering that this is a side project, the problems aren't quite big enough to justify that.
- steve-chavez 3y ago
  Definitely check out "choose your comfort level"[1]. > PostgREST's limitations also had me going back to an API server architecture because I definitely didn't want to write logic in database functions. Because of PostgREST's philosophy[2], you're expected to write database functions(not necessarily SQL, since PostgreSQL offers many PLs). So if you're not comfortable with that, you can treat PostgreSQL just as a data store and pair it up with your favorite ORM. Supabase doesn't force you to use PostgREST. [1]: https://supabase.com/docs/guides/getting-started/architecture#choose-your-comfort-level https://supabase.com/docs/guides/getting-started/architectur... [2]: https://postgrest.org/en/latest/#database-as-single-source-of-truth https://postgrest.org/en/latest/#database-as-single-source-o...
bodecker 3y ago
(Significantly edited after discussion) I also had a tough time working w/ an app someone else built on Supabase. We kept bumping up against what felt like "I know feature X exists in postgres, but it's 'coming soon' in Supabase." IIRC the blocker was specific to the trigger/edge function behavior. However after reflecting more, I don't remember enough to make a detailed case. Perhaps the issue was with our use of the product.
- kiwicopple 3y ago
  (supabase ceo) > "I know feature X exists in postgres, but it's 'coming soon' in Supabase." There is no feature that exists in postgres that doesn't already exist in Supabase. In case it's not clear, supabase is just Postgres. We build extensions, we host it for you, and we build tooling around the database. Our Dashboard is one of those tools, but there is always an escape hatch - you can use it like any other postgres database, with all the existing tooling you're most comfortable with.
  - bodecker 3y ago
    Thanks for the response. I do recall hitting some product limitations (a webhooks "beta" that we tried to use but hit a blocker). Reflecting more, I don't recall the supporting details specifically enough though. Edited original post and apologies for the added noise.
AlchemistCamp 3y ago
Render.com is pricey, but very underrated. If your business model isn’t broken by their pricing model, I really don’t know an easier/more time-efficient choice.
neximo64 3y ago
Can someone explain a bit better what the issues are. What exactly are the issues with migration if you use an SQL script to do the migration instead of the supabase interface?
- kiwicopple 3y ago
  That's definitely our recommendation beyond prototyping. I shared more thoughts here: https://news.ycombinator.com/item?id=36006754 https://news.ycombinator.com/item?id=36006754 For developers who have worked with databases before, SQL migrations might be obvious. But for many of our audience it's not. We'll adapt the interface to make this pattern more front-and-center. We also need to improve our CLI to catch up with other migrations tools because a lot of our audience haven't used established tools before (flyway, sqitch, alembic, etc)
t1mmen 3y ago
I hadn’t touched SQL for almost 7 years, but dipped my toes back in to build a PoC using Supabase. Despite some initial pains around RLS, I’ve grown to love it. Sure, Supabase has some awkward quirks and issue, and author has some good points. But when it works like it should, it’s pretty awesome. I think of it as a powerful wrapper around solid services that make for great DX, in _most_ cases. If Supabase could provide a great way to handle migrations and RLS, that’d be the biggest improvement to most people’s workflows, I’d bet. I really wish I could just define my scheme, tables, functions, triggers, policies etc as typescript, then have migrations generated from that.
- jononomo 3y ago
  > I really wish I could just define my scheme, tables, functions, triggers, policies etc as typescript, then have migrations generated from that. I've recently been using the Django ORM, which automatically generates migrations, and it is absolutely awesome.
  - t1mmen 3y ago
    Thanks for the tip, that sounds exactly what I’m looking for (outside of node ecosystem, but I’ll take what I can get)
atsjie 3y ago
Wait, so unknown hyped tech X didn't work out and you went back to stuff that has been around for 30 years? I'm shocked.
xwowsersx 3y ago
> we just wanted a database. ... > Render Preview Environments are amazing: they spin up an entire clone of our whole stack — frontend remix server, node api server, deno evaluation server, and now postgres database — for every pull request. So they wanted more than a database then, no? Are they saying they really just needed a DB and the other stuff was a nice bonus? If they really wanted just a DB, are there not cheaper, and possibly simpler, options than Render?
- jononomo 3y ago
  They just want to think about it as just a database when they are in building-the-application mindset. But they want it to have lots of conveniences and features and easy management when they are in building-the-company-and-team mindset.
- tmcw 3y ago
  [op]: Render is a web host, on which we host other applications. They offer a managed Postgres version, which is in my experience pretty similar to Heroku, RDS, or other managed databases. Maybe the sentence makes that confusing - we're using other stuff on Render, which are basically "web servers" in the Heroku-ish sense, and we're also using their managed database, which is just a database. And it's nice that Render, like some other managed hosting providers, lets you boot up and connect those services. I guess it's more than a database in some sense because it networks to our web servers and can be booted up in a preview environment, but it is mostly just a database. There are cheaper options that would be more work to wire up in such a convenient way, but the pricing difference between a database on AWS and one on Render is not the highest priority right now.
  - xwowsersx 3y ago
    Thanks, that does clear it up. I was missing the context about Render's full offering. Makes sense re the current priority; I can see many situations or phases of a company where using Render could make a lot of sense even if there are cheaper way to get a PG database.
atentaten 3y ago
When using Supabase, I haven’t had any issues with the webui because I mainly use dbeaver to work with Postgres instead.
jononomo 3y ago
I have just begun playing with Supabase and have a habit of running `brew upgrade` several times per week. It bugs me that the Supabase CLI is updated every single time I run `brew upgrade`. I suspect that if I were to run `brew upgrade` twice a day, it would probably still update every single time. It makes me feel like I'm trying to swing a bat around, except it's made of water.
joelrwilliams1 3y ago
What was most shocking to me was that it took a week to migrate 40GB. I once migrated 1TB from RDS Oracle to RDS Aurora MySQL in 6 hours. I'm not familiar with Supabase, maybe there's a lot more to the data migration process?
- burggraf 3y ago
  Supabase developer here. It shouldn't (and doesn't) take a week to migrate 40GB, I'm sure most of that time was strategizing, analyzing, and testing things. Supabase is pure Postgres running on AWS, so migrations are pretty straightforward. Things mostly depend on where you're migrating to/from, and the network latency between the source and destination. 40GB should take minutes in most cases.
- stevekrouse 3y ago
  There were a couple of factors why it took a week: 1. We wanted to avoid downtime, so the pg dump was slowed down because it was happening alongside production use of the db 2. We abuse postgres in a couple of ways (too many large json columns) which makes it harder to export and import 3. We were moving between cloud regions and cloud providers. 4. I'm a bit of a database ops noob (part of why supabase was appealing in the first place) so I had to learn how to do all these things. Like burggraf said, a lot of that week was planning, trial and error, test runs, mistakes that would cost full days, etc.
dpflan 3y ago
Wondering if the supabase CEO or any customers here can discuss scale. What “size” applications are doing really well on supabase? Are there any customers with TBs (or more) of data? What sort of performance are they achieving? Any customers with previous experience at a larger scale that are now using supabase and similar or larger scale, how are things going? What’s the average development team size of customers?
lastangryman 3y ago
Having worked on a Baas type offering, this is all very familiar. Over the years, I've come to believe the approach of trying define a service layer with these magic abstractions is fundamentally flawed and will always lead to the problems in this article: poor performance, poor local development experience, no transparency in to what is going on under the hood. They are great for fast proof of concepts, but not sustainable, long term product development.
jononomo 3y ago
I'm currently contracted on a greenfield Django REST framework app and if the decision had been up to me I probably would have gone with Supabase right off the bat. But honestly I'm absolutely loving Django REST framework over vanilla Postgres. It took me a while to get the hang of views and serializers and validation, etc, but now that I do it feels incredibly flexible and powerful. One thing I'm loving is how easy it has been for me to write management commands and build a comprehensive test suite, and that's one aspect of building a web app that I don't hear talked about much with Supabase.
tacker2000 3y ago
I mean i get that using such a service will somewhat speed up the initial “time to release”, but i still dont understand why i would use such a layer on top of a normal DB instead of just using a programming framework with an ORM or even a direct DB connection. After some time you just run into limitations and have to maneuver the around weird stuff that somehow the platform has imposed on you, things that just wont happen if you just use the vanilla DB. The passage in the article about the DB going offline during a backup every day at midnight is just insane to be honest. Also these services typically cost much more than just self hosting a DB. And why the hell would i ever put any amount of substantial business logic in the DB itself? Yes there are maybe speed benefits but in most cases the added burden of doing this is not necessary, compared with using actual code.
iowahansen 3y ago
Real-world "things we ran into" stories like this are super helpful when choosing a service or technology. Unfortunately, I have a similar experience with Firebase, where I wish I would have known that: * Don't like the text of your Firebase Auth SMS verification message that we send on your behalf -> tough luck * Your app name is longer than 15 characters? We are not going to include that hash in your Firebase Auth SMS message that is required by Android to perform an automatic login. * Global Firebase Auth SMS pricing does not work you economically? Welcome to implement the whole thing yourself anyways. * Dealing with development environments is flakey, as Firebase's emulators work 98% similar to production, but you will regularly hit things that are different. * You can't completely automate environment creation/tear down, as not everything is covered by Terraform or Google's own APIs, so you will end up doing manual things in their admin interface. * Real-time subscriptions in Firestore end up not being worth the tight schema coupling between client and server, as you can't control when the updates fire and you end up with a lot more unintended side effects than what this technology benefits you. So after a year of workarounds you finally end up deeply understanding the trade-offs involved in Firebase and make the decision that its downsides exceed its out of the box benefits. :(

382 points by stevekrouse 3y ago

Personally, I had a really easy time getting Supabase to work locally. However, we use `dbmate` to manage our migrations instead of built-in Supabase migrations. Also curious to hear from others on this: > After a bit of sleuthing, it ended up that Supabase was taking a database backup that took the database fully offline every night, at midnight. This seems like a terrible design decision if true. Why not just backup via physical or logical replication? And totally hear the issues here with database resizing and vacuuming and other operations. That stuff is a big pain when it breaks.

crooked-v 3y ago
Supabase daily backups just use pg_dump. If their database was going offline, then something else was broken.
- kiwicopple 3y ago
  (supabase ceo) To give context, Val Town have a particularly write-heavy setup, storing a lot of json strings. The nightly backups were causing write-contention, even at their relatively small size. We didn’t detect errors because they were application-level. We should have moved them to PITR as soon as they mentioned it since the timing was so obviously coinciding with backups. We’re investigating moving everyone to PITR (including the free tier). At the very least, we’ll add more control for backups - allowing users to change the maintenance window, or possibly disabling backups completely if they are managing it themselves.
  - aeyes 3y ago
    how does a backup cause write contention? are you backing up to the same disk? also why are backups using pg_dump? that’s not a backup.
    - nfm 3y ago
      https://www.postgresql.org/docs/current/app-pgdump.html https://www.postgresql.org/docs/current/app-pgdump.html > pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently.
    - paulryanrogers 3y ago
      Possibly MVCC keeping a snapshot so the whole dump is consistent.

omeze 3y ago

The local development & database migration story is Supabase's biggest weakness. I hate having to do migrations live in prod. The admin dashboard is just so much better than any alternative Postgres tooling that it's been worth using despite that. Takes care of the stuff I'd normally be sweating over when writing migrations like nullable fields / FK constraints / JSON formatting for default fields. Would be great if Supabase allowed for a "speculative migration" in its UX where it spit out a file you could use locally to test beforehand.

crooked-v 3y ago
If you use the CLI, `supabase start` spins up a Docker instance built from all your migration .sql files [1]. If anything, I think the admin dashboard encouraging directly doing operations on the database is the biggest weakness of Supabase. I would much prefer being able to lock it down to purely CI-driven migrations. [1]: https://supabase.com/docs/guides/getting-started/local-development#database-migrations https://supabase.com/docs/guides/getting-started/local-devel...
kiwicopple 3y ago
Please don't use the Dashboard to edit your database in production. We're working on Preview Databases which will help enforce this. For now this fits into our Shared Responsibility Model: https://supabase.com/docs/guides/platform/shared-responsibility-model https://supabase.com/docs/guides/platform/shared-responsibil... You are responsible for a workflow that's suitable for your application. Once you get into production, you should be using Migrations for every database change. I have a more thorough response here: https://news.ycombinator.com/item?id=36006018 https://news.ycombinator.com/item?id=36006018
- searchableguy 3y ago
  > Please don't use the Dashboard to edit your database in production. You should make the default editor read only and allow switching to write mode with a big warning. This would discourage people from writing SQL or using UI to modify in production. The dashboard has always screamed "use me to edit" and I have used supabase in the beginning and very recently too. Nothing has changed to discourage it so far. Maybe something like mode button which is present at top and you can click to switch between development and production mode? This would also change a couple more things which you do not want to touch in production by accident.

armatav 3y ago

It definitely needs DB branching.

Ken_At_EM 3y ago
Why is this comment getting downvoted?

exac 3y ago

> Local development was tough > Unfortunately, we just couldn’t get it to work Every time I read one of these migration stories, I find myself waiting with baited breath for the part the team couldn't achieve. After finding it, the remainder of the story becomes difficult to read. It isn't necessarily the team's fault, the developer experience clearly has room for improvement. Props to Val Town for being so honest, it is difficult to do.

bitdivision 3y ago
Can you elaborate on why it becomes difficult to read? Was there something obvious they missed?

pbreit 3y ago

The options for spinning up CRUD apps (ie, 95% of projects) are still quite miserable.

rco8786 3y ago
Assuming you’re talking about Supabase, I kind of disagree. There’s an initial learning curve with the row level security stuff, but once you get a good grasp of it and come up with a few patterns that suit your needs it’s insanely fast to develop on. You’re trading the time it takes to build and manage an api for the time it takes to setup RLS.
reducesuffering 3y ago
https://github.com/t3-oss/create-t3-app https://github.com/t3-oss/create-t3-app hosted on Vercel + Planetscale / Railway DB is very easy, I would be surprised if you were miserable doing that. Mostly that solves setting up Auth and Prisma SQL ORM to your DB, but Next.js App directory with the Prisma setup (2 files / 50 LOC) done is even smoother.
tr3ntg 3y ago
I'd say Supabase is great at spinning up CRUD apps. If anything, this article could be summarized as "Because Val Town is much more than a CRUD app, they had a harder time with Supabase than the average."

gajus 3y ago

Curious why have you decided for Drizzle over Kysely. I was recently exploring the space, and Kysely came on top as a framework with broader adoption. https://npmtrends.com/drizzle-orm-vs-kysely https://npmtrends.com/drizzle-orm-vs-kysely

Akkuma 3y ago
You can actually integrate both as well if you really want to leverage Drizzle for schema building and migrations: https://github.com/drizzle-team/drizzle-orm/tree/main/drizzle-orm/src/kysely https://github.com/drizzle-team/drizzle-orm/tree/main/drizzl...
reducesuffering 3y ago
You should consider the similar reasons why you chose Kysely over Prisma. Prisma has far broader adoption. https://npmtrends.com/drizzle-orm-vs-kysely-vs-prisma https://npmtrends.com/drizzle-orm-vs-kysely-vs-prisma
tmcw 3y ago
Sure! I think Kysely is great too, but went with Drizzle for a few different reasons: Kysely is a little more established than Drizzle, which I think is one of the major reason why it has broader adoption. My bet is that Drizzle is moving really fast, gaining adoption, and might catch up at some point. It's also - in terms of performance - super fast, and nicely layers on top of fast database clients. Some of the differences that I liked about Drizzle were the extra database drivers being core and developed as part of the main project. It supports prepared statements, which is awesome. The Drizzle API also covers an impressive percentage of what you can do in raw SQL, and when there's something missing, like a special column type, it's been pretty straightforward to add. I prefer the way that it lets us write parts of queries, and compose them - like you import expressions like "and" and "eq" and you can write and(eq(users.id, 'x'), eq(users.name, 'Tom')) and you can actually stringify that to the SQL it generates. Or you can do a custom bit of SQL and use the names of table columns in that, like `COUNT(${users.name})`. I can't say scientifically that this is superior, and it's almost a little weird, but I've really found it a nice way to compose and debug queries. That said, Kysely is also a great project and it'd be possible to build great products with it, too. I just found the momentum, API, and philosophy of Drizzle to be pretty compelling.

joshghent 3y ago

Echo all the words from the author here, and kudos for being transparent. I’ve faced exactly the same problems building my new product. But, on the other hand, Supabase was incredibly easy to setup, and meant I could worry about infrastructure later. Pros and cons like with everything, and always wise to understand the flaws of the tech you’re using.

doctorpangloss 3y ago

> The CLI manages the Supabase stack locally: Postgres, gotrue, a realtime server, the storage API, an API gateway, an image resizing proxy, a restful API for managing Postgres, the Studio web interface, an edge runtime, a logging system, and more – a total of 11 Docker containers connected together. Can Supabase author a set of Kubernetes manifests similar to what they run in production, and perhaps distribute those?

ahachete 3y ago
This is not from Supabase, but as a community contribution. See upthread [1]: "at StackGres we have built a Runbook [2] and companion blog post [3] to help you run Supabase on Kubernetes." [1]: https://news.ycombinator.com/item?id=36006308 https://news.ycombinator.com/item?id=36006308 [2]: https://stackgres.io/doc/latest/runbooks/supabase-stackgres/ https://stackgres.io/doc/latest/runbooks/supabase-stackgres/ [3]: https://stackgres.io/blog/running-supabase-on-top-of-stackgres/ https://stackgres.io/blog/running-supabase-on-top-of-stackgr...

ilrwbwrkhv 3y ago

I also had the same experience with Supabase. Even though it looks like a great product initially, it has a lot or errors and bugs when you are trying to actually build something more robust than a toy app. Local development is a massive pain with random bugs. The response time of the database also varies all over the place. But the most important problem that we faced, was having so much of application logic in the database. Row level security is their "foundational piece", but there is a reason why we moved away from database functions and application logic in database over a decade ago: that stuff in unmaintainable. There is also really poor support and at the end of the day, the whole platform felt like a hack. I think now, for most apps with up to 500_000 users (with 10_000 concurrent realtime connections) PocketBase is the best PaaS out there having tested a bunch of them. A single deployable binary which PocketBase provides is a breath of fresh air. Anything more than that, just directly being on top of bare metal or AWS / GCP is much better.

xrd 3y ago
I agree. I would love to see more articles on pocketbase. It's phenomenal and ganigeorgiev is an animal about responding to bugs and discussions. He's got to be a hybrid human and ChatGPT robot.
- ilrwbwrkhv 3y ago
  What's also really cool is that you can also just use PocketBase as a Go library and just build your app around it like any normal web framework, while still having a great UI for quick prototyping. And when you need more custom behaviour instead of database functions, you just write some Go code while still compiling everything down to a single binary that you can copy over.
dingledork69 3y ago
I don't use supabase, but am a big postgres fan: > that stuff in unmaintainable Wrong. Version your functions and use something like liquibase to apply migrations.
- 3y ago

kiwicopple 3y ago

hey hn, supabase ceo here the Val Town team were kind enough to share this article with me before they released it. Perhaps you know from previous HN threads that we take customer feedback very seriously. Hearing feedback like this is hard. Clearly the team at Val Town wanted Supabase to be great and we didn’t meet their expectations. For me personally, that hurts. A few quick comments 1. Modifying the database in production: I’ve published a doc on Maturity Models[0]. Hopefully this makes it clear that developers should be using Migrations once their project is live (not using the Dashboard to modify their database live). It also highlights the options for managing dev/local environments. This is just a start. We’re building Preview Databases into the native workflow so that developers don’t need to think about this. 2. Designing for Supabase: Our goal is to make all of Postgres easy, not obligatory. I’ve added a paragraph[1] in the first page in our Docs highlighting that it’s not always a good idea to go all-in on Postgres. We’ll add examples to our docs with “traditional” approaches like Node + Supabase, Rails + Supabase, etc. There are a lot of companies using this approach already, but our docs are overly focused on “the Supabase way” of doing things. There shouldn’t be a reason to switch from Supabase to any other Postgres provider if you want “plain Postgres”. 3. That said, we also want to continue making “all of Postgres” easy to use. We’re committed to building an amazing CLI experience. Like any tech, we’re going to need a few iterations. W’re building tooling for debugging and observability. We have index advisors coming[2]. We recently added Open Telemetry to Logflare[3] and added logging for local development[4]. We’re making platform usage incredibly clear[5]. We aim to make your database indestructible - we care about resilience as much as experience and we’ll make sure we highlight that in future product announcements. I’ll finish with something that I think we did well: migrating away from Supabase was easy for Val Town, because it’s just Postgres. This is one of our core principles, “everything is portable” (https://supabase.com/docs/guides/getting-started/architecture#everything-is-portable https://supabase.com/docs/guides/getting-started/architectur...). Portability forces us compete on experience. We aim to be the best Postgres hosting service in the world, and we’ll continue to focus on that goal even if we’re not there yet. [0] Maturity models: https://supabase.com/docs/guides/platform/maturity-model https://supabase.com/docs/guides/platform/maturity-model [1] Choose your comfort level: https://supabase.com/docs/guides/getting-started/architecture#choose-your-comfort-level https://supabase.com/docs/guides/getting-started/architectur... [2] Index advisor: https://database.dev/olirice/index_advisor https://database.dev/olirice/index_advisor [3] Open Telemetry: https://github.com/Logflare/logflare/pull/1466 https://github.com/Logflare/logflare/pull/1466 [4] Local logging: https://supabase.com/blog/supabase-logs-self-hosted https://supabase.com/blog/supabase-logs-self-hosted [5] Usage: https://twitter.com/kiwicopple/status/1658683758718124032?s=20 https://twitter.com/kiwicopple/status/1658683758718124032?s=...

motoxpro 3y ago

Great read. Similar to my experience with Hasura. Migrations we’re better there but the row level security was a nightmare. Went to just a custom node backend with prisma and it’s a dream. No more writing tons of json rules and multiple views just to not query the email field. Seems like these types of services are good for basic large scale crud applications, probably why you have Hasura pivoting to enterprise. The quote at the end of going back the future is exactly how I felt. Will never use a Hasura/Supabase/etc again. Just makes things more difficult.

danielrhodes 3y ago
Had a similar experience with Hasura. They have done some amazing things leveraging Postgres and GraphQL. But there were just too many things that got really questionable. Things like migrations becoming inconsistent with metadata, schema lock in, poor ability to do rate limiting, having to use stored procedures for everything, weird SQL that had performance issues, unexplained row level locking, and so on. Local development was a total mess. Ultimately we were making architectural decisions to please Hasura, not because it was in the best interests of what or how we were building.
- motoxpro 3y ago
  100%. Same things we ran into. Definitely a trap.

simonw 3y ago

The documentation section here applies to so many products I've battled in the past. > The command supabase db remote commit is documented as "Commit Remote Changes As A New Migration". The command supabase functions new is documented as "Create A New Function Locally." The documentation page is beautiful, but the words in it just aren't finished. Great documentation is such a force multiplier for a product. It's so worthwhile investing in this. Don't make your most dedicated users (the ones who get as far as consulting your documentation) guess how to use your thing!

kiwicopple 3y ago
this is very fair criticism. We hired a Head of Docs in March. I hope the improvements are evident since he joined, both in content and in usability. We have a long way to go, but we're working on it.
- pluto_modadic 3y ago
  yeah, most golang/rust/API documentation in products seems to think that "the function name is documentation", which.... no it's not. that's a tooltip in an IDE, not a docs website.

aguynamedben 3y ago

I love Steve Krouse!!!

ahachete 3y ago

A mid way could be self-hosting Supabase, whether you use more or less Supabase features. I know self-hosting might be challenging, specially getting a production-ready Postgres backend for it. That's why at StackGres we have built a Runbook [1] and companion blog post [2] to help you run Supabase on Kubernetes. All required components are fully open source, so you are more than welcome to try it and give feedback if you are looking into this alternative. [1]: https://stackgres.io/doc/latest/runbooks/supabase-stackgres/ https://stackgres.io/doc/latest/runbooks/supabase-stackgres/ [2]: https://stackgres.io/blog/running-supabase-on-top-of-stackgres/ https://stackgres.io/blog/running-supabase-on-top-of-stackgr... update: edit

jackconsidine 3y ago

Nice read. I run 5-6 projects on Supabase currently. I have also run into the local development / migration obstacles. It's otherwise been pretty great for our needs

SOLAR_FIELDS 3y ago
The CLI could use some love for sure. I think the migrations experience is also where I’ve felt the most pain. I will say, the CLI very heavily assumes that you are using the cloud product as the remote, which I guess is absolutely intentional (it’s a path to get users onto the product) but it was kind of annoying to figure that out halfway into a POC like I did. Don’t go in expecting you can point the CLI at some self hosted remote. It’s not possible without forking, making significant changes and rebuilding the CLI, at least at the time I was doing this a few months ago.
- sweatybridge 3y ago
  Hello, I work on CLI full time. Things have certainly improved over the last few months on using this tool for migrating self-hosted databases. Currently all supabase db and migration commands support --db-url flag [1] which allows you to point the CLI to any Postgres database by a connection string. If there's any use case I missed, please feel free to open a GitHub issue and I will look into it promptly. [1] https://supabase.com/docs/reference/cli/supabase-db https://supabase.com/docs/reference/cli/supabase-db

Ken_At_EM 3y ago

This echos my experience with Supabase exactly. We migrated to a similar solution for the same reasons.

tonerow 3y ago

Supabase is also great for auth. Did you reimplement auth yourself or switch to another auth service or framework?

tmcw 3y ago
We switched to Clerk.dev. Thankfully we had only supported magic link auth, so there wasn't much information to migrate over. Clerk has been pretty good - they have a great Remix integration and solid admin experience.
- doodlesdev 3y ago
  Any specific reason to go with it instead of alternatives?
doodlesdev 3y ago
PSA: Supabase Auth is based on their fork [0] of Netlify's Gotrue [1]. If you are migrating out of Supabase completely you can just drop in Gotrue for authentication. [0]: https://github.com/supabase/gotrue https://github.com/supabase/gotrue [1]: https://github.com/netlify/gotrue https://github.com/netlify/gotrue

PKop 3y ago

Interesting statement here: "We rewrote our data layer to treat the database as a simple persistence layer rather than an application. We eliminated all the triggers, stored procedures, and row-level security rules. That logic lives in the application now." Reminds me of the article and discussion here[0] over whether to put logic in the database or not and to what degree. [0] https://news.ycombinator.com/item?id=35643432 https://news.ycombinator.com/item?id=35643432 "Use Databases Without Putting Domain Logic in Them"

bodecker 3y ago
Also reminds me of this Martin Fowler post [0]: "The situation becomes interesting when the vast majority of your data sits in a single logical database. In this case you have two primary issues to consider. One is the choice of programming language: SQL versus your application language. The other is where the code runs, SQL at the database, or in memory. SQL makes some things easy, but other things more difficult. Some people find SQL easy to work with, others find it horribly cryptic. The teams personal comfort is a big issue here. I would suggest that if you go the route of putting a lot of logic in SQL, don't expect to be portable - use all of your vendors extensions and cheerfully bind yourself to their technology. If you want portability keep logic out of SQL." [0] https://martinfowler.com/articles/dblogic.html https://martinfowler.com/articles/dblogic.html

Ethan_Mick 3y ago

How do people on HN like Row Level Security? Is it a better way to handle multi-tenant in a cloud SaaS app vs `WHERE` clauses in SQL? Worse? Nicer in theory but less maintainable in practice? fwiw, Prisma has a guide on how to do RLS with it's client. While the original issue[0] remains open they have example code[1] with the client using client extensions[2]. I was going to try it out and see how it felt. [0]: https://github.com/prisma/prisma/issues/12735 https://github.com/prisma/prisma/issues/12735 [1]: https://github.com/prisma/prisma-client-extensions/blob/main/row-level-security/script.ts https://github.com/prisma/prisma-client-extensions/blob/main... [2]: https://www.prisma.io/docs/concepts/components/prisma-client/client-extensions#example-use-cases-for-extended-clients https://www.prisma.io/docs/concepts/components/prisma-client...

atonse 3y ago
It is highly appealing to have that defense in depth. However, when building a prototype or a product, not having experience in it causes me to worry that we will end up being stuck with a choice where it's very hard to pull ourselves out of. So instead we've stuck to having that filtering logic in the application side. The main concern is how user auth/etc works in Postgres. (lack of knowledge, not lack of trust). Because we also have complex filtering like, "let me see all the people in my team if I have this role, but if i'm a public user, only show this person" etc
crooked-v 3y ago
The main issue we've had with it is that it's just plain slow for a lot of use cases, because Postgres will check the security for all rows before filtering on the joins, doing anything with WHERE clauses, doing anything to even tentatively take LIMIT into account, etc. Imagine a 1-million-row table and a query with `WHERE x=y` that should result in about 100 rows. Postres will do RLS checks on the full 1 million rows before the WHERE clause is involved at all.
- kiwicopple 3y ago
  With PostgREST you can use the pre-fetch method to solve this: https://postgrest.org/en/stable/references/transactions.html?highlight=pre-fetch#pre-request https://postgrest.org/en/stable/references/transactions.html... You can use that to inject your ACL/permissions into a setting - set_config('permissions', '{"allowed":true}'). Then in your RLS rules you can pluck them out - current_setting('permissions'::jsonb). This should make your RLS faster than most other options, in theory, because of data co-location

throwawaymaths 3y ago

Amazed they used scp instead of rsync

stevekrouse 3y ago
I'm not great at shell scripting tbh. It's not a coincidence that I built a cloud scripting platform

kdrag0n 3y ago

Honestly, I want to like Supabase but a lot of this resonates with me even for a fairly small project. I also ended up with 3 user tables due to RLS limitations: auth users, public user profile info, and private user info (e.g. Stripe customer IDs). PostgREST's limitations also had me going back to an API server architecture because I definitely didn't want to write logic in database functions. The only reason I haven't migrated yet is because I'd have to rewrite the data layer to use Prisma/Drizzle instead of Supabase's PostgREST client, and considering that this is a side project, the problems aren't quite big enough to justify that.

steve-chavez 3y ago
Definitely check out "choose your comfort level"[1]. > PostgREST's limitations also had me going back to an API server architecture because I definitely didn't want to write logic in database functions. Because of PostgREST's philosophy[2], you're expected to write database functions(not necessarily SQL, since PostgreSQL offers many PLs). So if you're not comfortable with that, you can treat PostgreSQL just as a data store and pair it up with your favorite ORM. Supabase doesn't force you to use PostgREST. [1]: https://supabase.com/docs/guides/getting-started/architecture#choose-your-comfort-level https://supabase.com/docs/guides/getting-started/architectur... [2]: https://postgrest.org/en/latest/#database-as-single-source-of-truth https://postgrest.org/en/latest/#database-as-single-source-o...

bodecker 3y ago

(Significantly edited after discussion) I also had a tough time working w/ an app someone else built on Supabase. We kept bumping up against what felt like "I know feature X exists in postgres, but it's 'coming soon' in Supabase." IIRC the blocker was specific to the trigger/edge function behavior. However after reflecting more, I don't remember enough to make a detailed case. Perhaps the issue was with our use of the product.

kiwicopple 3y ago
(supabase ceo) > "I know feature X exists in postgres, but it's 'coming soon' in Supabase." There is no feature that exists in postgres that doesn't already exist in Supabase. In case it's not clear, supabase is just Postgres. We build extensions, we host it for you, and we build tooling around the database. Our Dashboard is one of those tools, but there is always an escape hatch - you can use it like any other postgres database, with all the existing tooling you're most comfortable with.
- bodecker 3y ago
  Thanks for the response. I do recall hitting some product limitations (a webhooks "beta" that we tried to use but hit a blocker). Reflecting more, I don't recall the supporting details specifically enough though. Edited original post and apologies for the added noise.

AlchemistCamp 3y ago

Render.com is pricey, but very underrated. If your business model isn’t broken by their pricing model, I really don’t know an easier/more time-efficient choice.

neximo64 3y ago

Can someone explain a bit better what the issues are. What exactly are the issues with migration if you use an SQL script to do the migration instead of the supabase interface?

kiwicopple 3y ago
That's definitely our recommendation beyond prototyping. I shared more thoughts here: https://news.ycombinator.com/item?id=36006754 https://news.ycombinator.com/item?id=36006754 For developers who have worked with databases before, SQL migrations might be obvious. But for many of our audience it's not. We'll adapt the interface to make this pattern more front-and-center. We also need to improve our CLI to catch up with other migrations tools because a lot of our audience haven't used established tools before (flyway, sqitch, alembic, etc)

t1mmen 3y ago

I hadn’t touched SQL for almost 7 years, but dipped my toes back in to build a PoC using Supabase. Despite some initial pains around RLS, I’ve grown to love it. Sure, Supabase has some awkward quirks and issue, and author has some good points. But when it works like it should, it’s pretty awesome. I think of it as a powerful wrapper around solid services that make for great DX, in _most_ cases. If Supabase could provide a great way to handle migrations and RLS, that’d be the biggest improvement to most people’s workflows, I’d bet. I really wish I could just define my scheme, tables, functions, triggers, policies etc as typescript, then have migrations generated from that.

jononomo 3y ago
> I really wish I could just define my scheme, tables, functions, triggers, policies etc as typescript, then have migrations generated from that. I've recently been using the Django ORM, which automatically generates migrations, and it is absolutely awesome.
- t1mmen 3y ago
  Thanks for the tip, that sounds exactly what I’m looking for (outside of node ecosystem, but I’ll take what I can get)

atsjie 3y ago

Wait, so unknown hyped tech X didn't work out and you went back to stuff that has been around for 30 years? I'm shocked.

xwowsersx 3y ago

> we just wanted a database. ... > Render Preview Environments are amazing: they spin up an entire clone of our whole stack — frontend remix server, node api server, deno evaluation server, and now postgres database — for every pull request. So they wanted more than a database then, no? Are they saying they really just needed a DB and the other stuff was a nice bonus? If they really wanted just a DB, are there not cheaper, and possibly simpler, options than Render?

jononomo 3y ago
They just want to think about it as just a database when they are in building-the-application mindset. But they want it to have lots of conveniences and features and easy management when they are in building-the-company-and-team mindset.
tmcw 3y ago
[op]: Render is a web host, on which we host other applications. They offer a managed Postgres version, which is in my experience pretty similar to Heroku, RDS, or other managed databases. Maybe the sentence makes that confusing - we're using other stuff on Render, which are basically "web servers" in the Heroku-ish sense, and we're also using their managed database, which is just a database. And it's nice that Render, like some other managed hosting providers, lets you boot up and connect those services. I guess it's more than a database in some sense because it networks to our web servers and can be booted up in a preview environment, but it is mostly just a database. There are cheaper options that would be more work to wire up in such a convenient way, but the pricing difference between a database on AWS and one on Render is not the highest priority right now.
- xwowsersx 3y ago
  Thanks, that does clear it up. I was missing the context about Render's full offering. Makes sense re the current priority; I can see many situations or phases of a company where using Render could make a lot of sense even if there are cheaper way to get a PG database.

atentaten 3y ago

When using Supabase, I haven’t had any issues with the webui because I mainly use dbeaver to work with Postgres instead.

jononomo 3y ago

I have just begun playing with Supabase and have a habit of running `brew upgrade` several times per week. It bugs me that the Supabase CLI is updated every single time I run `brew upgrade`. I suspect that if I were to run `brew upgrade` twice a day, it would probably still update every single time. It makes me feel like I'm trying to swing a bat around, except it's made of water.

joelrwilliams1 3y ago

What was most shocking to me was that it took a week to migrate 40GB. I once migrated 1TB from RDS Oracle to RDS Aurora MySQL in 6 hours. I'm not familiar with Supabase, maybe there's a lot more to the data migration process?

burggraf 3y ago
Supabase developer here. It shouldn't (and doesn't) take a week to migrate 40GB, I'm sure most of that time was strategizing, analyzing, and testing things. Supabase is pure Postgres running on AWS, so migrations are pretty straightforward. Things mostly depend on where you're migrating to/from, and the network latency between the source and destination. 40GB should take minutes in most cases.
stevekrouse 3y ago
There were a couple of factors why it took a week: 1. We wanted to avoid downtime, so the pg dump was slowed down because it was happening alongside production use of the db 2. We abuse postgres in a couple of ways (too many large json columns) which makes it harder to export and import 3. We were moving between cloud regions and cloud providers. 4. I'm a bit of a database ops noob (part of why supabase was appealing in the first place) so I had to learn how to do all these things. Like burggraf said, a lot of that week was planning, trial and error, test runs, mistakes that would cost full days, etc.

dpflan 3y ago

Wondering if the supabase CEO or any customers here can discuss scale. What “size” applications are doing really well on supabase? Are there any customers with TBs (or more) of data? What sort of performance are they achieving? Any customers with previous experience at a larger scale that are now using supabase and similar or larger scale, how are things going? What’s the average development team size of customers?

lastangryman 3y ago

Having worked on a Baas type offering, this is all very familiar. Over the years, I've come to believe the approach of trying define a service layer with these magic abstractions is fundamentally flawed and will always lead to the problems in this article: poor performance, poor local development experience, no transparency in to what is going on under the hood. They are great for fast proof of concepts, but not sustainable, long term product development.

jononomo 3y ago

I'm currently contracted on a greenfield Django REST framework app and if the decision had been up to me I probably would have gone with Supabase right off the bat. But honestly I'm absolutely loving Django REST framework over vanilla Postgres. It took me a while to get the hang of views and serializers and validation, etc, but now that I do it feels incredibly flexible and powerful. One thing I'm loving is how easy it has been for me to write management commands and build a comprehensive test suite, and that's one aspect of building a web app that I don't hear talked about much with Supabase.

tacker2000 3y ago

I mean i get that using such a service will somewhat speed up the initial “time to release”, but i still dont understand why i would use such a layer on top of a normal DB instead of just using a programming framework with an ORM or even a direct DB connection. After some time you just run into limitations and have to maneuver the around weird stuff that somehow the platform has imposed on you, things that just wont happen if you just use the vanilla DB. The passage in the article about the DB going offline during a backup every day at midnight is just insane to be honest. Also these services typically cost much more than just self hosting a DB. And why the hell would i ever put any amount of substantial business logic in the DB itself? Yes there are maybe speed benefits but in most cases the added burden of doing this is not necessary, compared with using actual code.

iowahansen 3y ago

Real-world "things we ran into" stories like this are super helpful when choosing a service or technology. Unfortunately, I have a similar experience with Firebase, where I wish I would have known that: * Don't like the text of your Firebase Auth SMS verification message that we send on your behalf -> tough luck * Your app name is longer than 15 characters? We are not going to include that hash in your Firebase Auth SMS message that is required by Android to perform an automatic login. * Global Firebase Auth SMS pricing does not work you economically? Welcome to implement the whole thing yourself anyways. * Dealing with development environments is flakey, as Firebase's emulators work 98% similar to production, but you will regularly hit things that are different. * You can't completely automate environment creation/tear down, as not everything is covered by Terraform or Google's own APIs, so you will end up doing manual things in their admin interface. * Real-time subscriptions in Firestore end up not being worth the tight schema coupling between client and server, as you can't control when the updates fire and you end up with a lot more unintended side effects than what this technology benefits you. So after a year of workarounds you finally end up deeply understanding the trade-offs involved in Firebase and make the decision that its downsides exceed its out of the box benefits. :(