Handling breaking schema changes

[eugenefil]

Say I have a flutter app installed on client phones. (I don't have one atm, just wondering.) I see that changing db schema in backward-compatible ways (e.g. adding new fields) doesn't break clients using the older schema - the clients just ignore the new fields.

But what if I want to introduce breaking changes into my schema, say remove a field. That would break my older clients. How would I go about it? I mean, is it all up to me to decide or would there be any recommended way? Should I create my version check endpoint using trailbase typescript api and force older clients to upgrade the app? Other variants?

I don't have a particular use case. Just wondering.

[ignatz]

Generally, there isn't much a backend can do when the consuming code asserts invariants, e.g. requires a specific field to be present. This is also the reason why protobuf version 2 => 3, they abandoned required fields. Similarly, if you only add/remove nullable columns, similarly the client will need to handle the null case explicitly and removal is safe.

Otherwise, breaking changes will require coordination. This is not specific for TrailBase and you can find many opinions and strategies. Typically, it will look something like:

1. update the client to stop requiring the column you're planning to remove effectively marking it obsolete.
2. roll out your changes and wait or otherwise incentivize your customers to update.
3. Once satisfied remove the column/field

As you point out, it's generally a better user-experience if you have a mechanism in place to notify users who didn't make the cut (3), rather than just crashing the app. This requires foresight and a version-check API, just like you said.

Should I create my version check endpoint using trailbase typescript api and force older clients to upgrade the app?

Now that you bring this up, TrailBase could probably provide a generic "latest migration" applied API. Let me think about this...

Great question. Hope I'm making some sense 😅

[eugenefil]

You're totally making sense. Thanks for the expanded answer.

That "latest migration" applied API is probably what I was really asking about. Like can I get some help from TB with versioning or am I totally on my own? Can you tell what you had in mind with that API even if it's just a vague idea. I'd like to hear.

[ignatz]

That "latest migration" applied API is probably what I was really asking about. Like can I get some help from TB with versioning or am I totally on my own? Can you tell what you had in mind with that API even if it's just a vague idea. I'd like to hear.

Sure, let's brainstorm together (really, step on my toes)...

I assume any versioning API would be used by a client to answer a few questions:

1. Am I up-to-date?
2. If I'm not up-to-date, am I still compatible?

Note that (1) doesn't mean that there's a newer version of the client software itself, but merely that the types/schemas have changed since the currently running version was built. Which in turn implies not only that there is an API but also that the client knows against which version of the types/schemas it was built, i.e. amIUpToDate(clientSchemaVersion) and amICompatible(clientSchemaVersion).

What the client should do in response to getting the above questions answered, is fully up to the client and outside the control of TrailBase. For example, if the answer is (false, true) = (amIUpToDate(clientSchemaVersion), amICompatible(clientSchemaversion), the client may just ignore it or it may go off to the PearStore and check if there's a new app version and politely nudge the user to update. If the answer is (false, false), i.e. we're no longer compatible, it's your responsibility as the developer to make sure that there's an update path ready on the PearStore.
BTW, I assume that we're talking mobile/desktop apps here, since they're fundamentally harder to deploy and update than web-based apps. On desktop, there may not even be a PearStore. Instead, you'd have to send folks to a website to manually download a new version of the app, .... .

Anyway, back to the API... implementing amIUpToDate(clientSchemaVersion) and amICompatible(clientSchemaVersion) is actually non-trivial because TB would have to reason what the schema was for any version < currentVersion. It's feasible since it has the migration history but it's non-trivial. Something that would be easier to implement would be amICompatible(apiIdentifier, clientApiSchema), the server could then use a library like https://github.com/IBM/jsonsubschema to say whether clientApiSchema is a sub-type of serverApiSchema. Arguably, this is a way more annoying API because now it's the responsibility of the client to not only keep track of all the json schemas (and not only the types compiled from them) and all the apis and check them all, presumably on startup... 🤮

...waffling on, I do wonder how much TrailBase can actually help here in a horizontal fashion. Sadly, I can't think of any precedent where client compatibility is checked implicitly via schema versions. I can even think of cases, where other invariants could be violated beyond type shapes... I think it's a lot more common to do explicit versioning for the client app and check against that, i.e. have your own implementations of amIUpToDate(explicitSemanticClientVersion) and amICompatible(explicitSemanticClientVersion).

Alternatively, it's also common to version server-side on the API level, e.g. api/v25/myapi, by having a version as part of your API path to support multiple API versions at the same time. Coincidentally, I did a quick search what similar products to TrailBase recommend and I came across: https://github.com/orgs/supabase/discussions/2957. I could see doing something similar with TrailBase using VIEWS....

For example, when you want to drop a non-nullable column you could turn it into a nullable column (to continue to support mutations on the old version of the API) and have a VIEW-based api that returns COALESCE(table.column, 'default').

Sorry, this reply has gotten out of hand. Hope at least some of it made sense. I need to think about this a bit more but I would really love to hear your thoughts and how you've been thinking about this. It sounded like you at least had some thoughts on how a custom amICompatible(explicitSemanticClientVersion) could work. Thanks 🙏

[eugenefil]

Sorry, this reply has gotten out of hand.

No, that's actually great. That's what I was hoping for when I started the discussion - your thoughts on the topic.

I came to the same conclusion that TB can't help much with versioning. Analyzing migration history is definitely not the way to go. At the same time, schema changes is not the only thing that can break the app. You might also have custom http handlers with TS/JS API that can change. And also you might want to force client upgrades for a reason not related to TB altogether - say a critical bug in the app's client code.

So I guess storing a minimum required client app version in some metadata table is the way to go. Right after the start, the app loads that version from TB using regular Record API and decides whether it should update. That minimum required version ensures compatibility with the current DB schema. Going with the original use case of removing a field it's basically how you described it in your 1st reply:

1. You update the app to stop using the column.
2. You release the new version of the app to an app store. You nudge users to update.
3. At some later point you increase the minimum required version. Clients that didn't update are forced to update when the app starts next time.
4. You wait some more and remove the field.

Wrt step 4, how do I do a migration on a live TB? Is it necessary to restart TB so it applies the migration or do I use SQL Editor UI?

[ignatz]

At the same time, schema changes is not the only thing that can break the app. You might also have custom http handlers with TS/JS API that can change. And also you might want to force client upgrades for a reason not related to TB altogether - say a critical bug in the app's client code.

All very good points.

So I guess storing a minimum required client app version in some metadata table is the way to go. Right after the start, the app loads that version from TB using regular Record API and decides whether it should update.

That makes sense. The one aspect TB may be able to help you by providing something like the versioned API + VIEW approach, is that in some cases you may be able to support old clients for longer, effectively broadening the horizon ... but then there are all the other cases you mentioned above 🤷‍♀️

Wrt step 4, how do I do a migration on a live TB? Is it necessary to restart TB so it applies the migration or do I use SQL Editor UI?

In principle you don't need to restart. In any case, do not use the SQL editor to change the live schema. Using the editor won't yield a migration in traildepot/migrations/main/V.*.sql. You should always have the migrations drive your schema to make sure that if you have multiple environments (dev, CI, QA, pre-prod, prod, ...) that they're all in sync. Otherwise, your prod instance will have the new schema but other environments won't.

Ideally, when doing such a major change you wouldn't skip the line, i.e. first do it in a dev environment and test your client against a dev instance. Then you'd commit the schema change via a migration that then goes though CI, QA, pre-prod, and finally prod.

When you then deploy your updated traildepot/migrations/main/V.*.sql in prod, you'd currently need to restart TB to pick them up. While this would be a downtime of <1s, we can improve this. TB already has a SIGHUP handler to reload configuration, we should probably also re-apply migrations there. Just filed #88

(What you can already do live is alter the schema in the table browser. This will yield an appropriate migration file. Just note that if you did this in prod and then committed the schema file, your other environments will pick up the change but you still would have skipped the line.)

[ignatz]

SIGHUP will re-apply migrations starting in the next release (probably v0.15.1).

[eugenefil]

Using the editor won't yield a migration in traildepot/migrations/main/V.*.sql. You should always have the migrations drive your schema...

Got it. Actually I meant getting the migration file first in a dev env, then applying it on prod by pasting and executing in the editor without restarting TB.

Ideally, when doing such a major change you wouldn't skip the line...

Absolutely.

...you'd currently need to restart TB to pick them up.

That's what I was asking.

TB already has a SIGHUP handler to reload configuration, we should probably also re-apply migrations there.

That'd be ideal.

Just note that if you did this in prod and then committed the schema file, ...you still would have skipped the line.

I'm not that of a cowboy really. I won't skip the line. I swear 🤚

SIGHUP will re-apply migrations starting in the next release...

Great news!

Thank you very much!