Hacker News Reader

Comment by freedomben | Hacker News Reader

A very interesting idea to be sure, but IME the biggest downside (which tbf is mentioned in the article) is the contract. If you have clients with knowledge of and dependency on the schema, you can't change it in a breaking way unless you update all the client's code.

I've tried various patterns in the past like one that just exposes database columns as an API, and this pain point always comes calling and it hurts. Keeping your data model simple and lean as possible is an important part of limiting complexity, which directly correlates with maintainability.

The only pattern/approach that I consistently return to is the Rails pattern (I use Elixir/Phoenix now but same pattern). It is certainly not the most sexy, and having a client be able to graphql exactly what they need can be really helpful, but at least for me it has rarely turned out to be worth the tradeoffs.

View on HN

This is basically how Firebase is designed and it drives me nuts. I've freelanced with several teams, primarily comprised of front-end devs, who decided to use Firebase for their product. When they first told me they query the database directly from both their website and their mobile app, I immediately was like "so...what happens if you need to change the structure of the database"?

Crickets.

Crickets, really? It's very obvious how to do as recommended by Google - you use a Cloud Function (aka Cloud Functions for Firebase) to do a "migration", while simultaneously handling both cases in client code until this is done.

You must have been working with a bunch of amateurs if such an obvious solution (even without reading the documentation) didn't come to mind apparently by you or your clients (since you're the freelancer). This particular problem isn't even Firebase specific - you have to do this for any store that doesn't strictly have a schema.

This issue you're describing isn't even in the top 5 biggest issues about using Firebase.

As I stated in my comment, I was working with front end developers, who had little-to-no experience working with backends and hadn’t considered migrations. That documentation really didn’t help because it’s such a laborious and error-prone process, especially when dealing with mobile applications that live on users devices. My implied point was that an API layer resolves the need for any of that bullshit to begin with.

And yeah, there are tons of other issues with Firebase, but this was the first warning siren that went off in my mind. I hate Firebase.

The solution to this is the same as with APIs: versioning. Instead of naming your table "my_foo", you name it "my_foo_v1". Then, when you want to make a breaking change to the schema, you:

1. Create a new table "my_foo_v2" with your desired schema

2. Modify write queries for "my_foo_v1" so that they also write to "my_foo_v2"

3. Copy over existing data in "my_foo_v1" to "my_foo_v2" with a migration script

4. Modify read queries for "my_foo_v1" so that they read from "my_foo_v2" instead

5. Remove all write queries to "my_foo_v1"

6. Drop the "my_foo_v1" table

Holy hell does that sound awful. In practice, it doesn't even solve the core problem as you'd have to deploy new versions of all binaries precisely in sync to avoid data incorrect because some clients know about the v2 table and others don't. It becomes indistinguishable whether a row is missing because it was intentionally deleted or because it was written by a client who didn't know any the new table version. There are ways to account for this but it's layers of crap on top of crap.

> it doesn't even solve the core problem as you'd have to deploy new versions of all binaries precisely in sync to avoid data incorrect because some clients know about the v2 table and others don't

its painful but this approach does work as long as every client is migrated between each step.

after 1: v1 is valid, reading v1, writing v1

after 2: v1 is valid, reading v1, writing v1 and v2

after 3: v1 and v2 are valid, reading v1, writing v1 and v2

after 4: v1 and v2 are valid, reading v2, writing v1 and v2

after 5: v2 is valid, reading v2, writing v2

after 6: v2 is valid, reading v2, writing v2

at each point, both the current and the previous version are reading a valid table and writing to a table whose values will make it into v2.

Good luck migrating every single client in the real world (unless you are willing to disable old clients & force an upgrade if they get too old)

These are the same problems you have with any API or package you ship.

Is there really any alternative? Data migrations are always going to be terrible. You need to slowly roll out the incremental changes so that everything is backwards compatible until you the threshold point where you can finally delete the historical route.

With an API, you can often create facades and other patterns to avoid having to migrate the underlying data

You can often also accomplish this with a database view.

In a sensible approach schema migrations are totally decoupled from the API.

With postgres, simple views are updatable, so you can often do this pattern without copying tables or dual writes. It's particularly useful for renaming columns, but you can also do some other things. You also don't have to use version numbers on all your tables since you only need the view temporarily.

    ALTER TABLE my_foo RENAME TO my_foo_tmp;
    ALTER TABLE my_foo_tmp <your desired change here>;
    CREATE VIEW my_foo AS SELECT <how you make your new table seem like your old table>;
    COMMIT;
    <update your apps to use my_foo_tmp>
    DROP VIEW my_foo;
    ALTER TABLE my_foo_tmp RENAME TO my_foo;
    CREATE VIEW my_foo_tmp as select * from my_foo;
    COMMIT;
    <update your apps to use my_foo>
    DROP VIEW my_foo_tmp; COMMIT;

We actually used a similar tactic when transitioning our data to supporting soft deletion. We have a limited number of queries that update data or present data for administrative review and processing - but a lot of wild ones around reporting. We'd rename table `foo` to `foowithdeleted` and create a new `foo` view that excludes any soft-deleted rows. Our reporting queries keep on trucking like normal and read out of the `foo` table (now a view - but they don't care) and we only need to adjust the administrative view to show soft-deleted rows for undeletion operations.

Shell-gaming tables with views can be incredibly useful in postgres.

You know, I've been toying with this very idea in my head for a while, as a way to make safe schema migrations. But I also know very little about databases so had no idea if this was a stupid idea or not. Glad to know I'm not alone.

One issue I've considered is fk relationships - that could get complicated depending on the approach.

Schema migrations are a solved problem. You make backward-incompatible changes in multiple steps.

https://engineering.edx.org/django-migration-donts-f4588fd11...

Imagine you have Android & iOS apps which some of your users won't ever update. You'll never be able to drop any old version's tables, and you'll need to keep all table versions in sync more or less in real-time. Only when the last user has updated from a certain client version will you be able to remove that version's tables.

I'm sorry that is insane. That approach wouldn't pass the first sniff test of any sensible technical design review.

You'd be surprised how many companies do exactly this stuff and not just with DB schema changes. They're called bridging changes.

I think about this with Postgrest/Supabase which has a similar problem (autogenerated code based on the db schema is inherently going to conflict with api stability guarantees). I think that this approach is just fundamentally at odds with making a stable versioned controlled API.

However: I think the best thing to do if you want a setup like this and want to have your cake and eat it too is something like this:

Use your autogenned client for your “internal” api. This is for your clients with the autogenned schema that you directly control only so that you can ship changes and not have to worry about versioning/backwards compatibility.

Then for external users that have their own clients, you have that slimmed down more traditional API that offers less functionality but it’s properly versioned, tested etc

I think this kind of hybrid setup can work well for SaaS setups where you have a cloud product that does internal stuff plus things external that end users need to operate on. You get the benefit of being able to iterate quickly without breaking your clients and since your external API is smaller it’s less maintenance overhead to keep it updated and versioned

PostgREST docs have always recommended to use only views + functions in a dedicated schema for the API: https://postgrest.org/en/v10.2/schema_structure.html

Huh, interesting, since at least with Supabase the default setup kind of pushes you towards or at least doesn’t discourage you from using tables instead

They mention one partial "solution" to this: have your clients query a view, instead of tables directly.

When you need to change the schema, also update the view accordingly.

Of course, there are still limitations. For one: you can't "massage" data with a view as you could in the backend with a full featured programming language.

PS: I don't think this is a good solution, just mentioning it.

If you are in the situation where you are shipping a web app where one dev team controls the backend and the one and only client and ships both together, is this a big issue?

Reply on news.ycombinator.com