2

I'm working on a new product which will run on-premise at our customers. The configuration and usage of this application will be stored in a database. One of the requirements is that a newer version of the application can be installed side-by-side next to an older version. This way the customer can validate the new version of our software, and can slightly move his business processes from the old version to the new version. Probably this will be done per process, because it will involve a lot of testing and validation.

At the end of the migration to the new version, all configuration changes and run/usage data of both versions of the application, should exist in the database. This implies that both versions of the application will have to use the same database.

So how should we deal with database changes in future releases of the app? And how can we make sure the older version of the app can still use the upgraded database? I was thinking about the following approach, but maybe there are better ways to tackle this problem:

  • Create an API layer between the application and database. If a newer version gets installed, it will update the database and the API layer. The API layer will provide access to the database in a backwards-compatible manner, and will be used by all installed versions of the app.

Any other thoughts or suggestions?

FWIW: we haven't decided on a database yet, might be MySQL, PostgreSQL, or MS SQL.

6
  • 1
    Your question is off topic as it's too broad and opinion based.
    – Dan Guzman
    Commented Jan 17, 2022 at 15:34
  • As Dan mentioned, this is a very broad topic. I would advise against what you’re planning to do, as it basically equates to “testing in prod”. If anything goes wrong, you’ve now trashed the single copy of your prod database. Build a test server, restore a copy of prod to it and test it until you feel good about it. Once you do, perform a migration and leave the old database behind. If you must use the same database, you need to make sure all schema changes are backwards comparable. Commented Jan 17, 2022 at 18:07
  • Does the database need to live on the customers' servers or will you be the sole host of the customers' databases and data?
    – J.D.
    Commented Jan 17, 2022 at 19:36
  • It may be best to have two completely separate databases -- one per version. Then connect to the server with the version you want to test/use or USE the database (within a single server).
    – Rick James
    Commented Jan 17, 2022 at 19:52
  • Thanks all for your remarks. I know this is not an ideal situation, hence my question if there are any ways to handle this in an easy/safe way. To answer your questions: - Our customers will indeed test the new version. But at some point they will move some of their business processes to the new version, while other processes keep using the old version. So it's not as easy as installing a test version and promote it to PROD at some point in time. - The database and software will run on-premise at the customer.
    – Joost
    Commented Jan 18, 2022 at 7:38

2 Answers 2

2

"Best practice for a database used by multiple versions of an application" is to not have such a thing at all.

What you want to do is build a new environment (call it pre-production, User Acceptance Test, or whatever) for your customers to do their validation and training. You populate it with data from the current production using your migration procedure (that you'll later use to migrate the production instance).

Once the customers sign off on the release, you deploy your new code and migrate the database schema and data in production, in whatever sequence is appropriate. You can do it in an entirely new production environment (and then retire the current production) or in place (if downtime is tolerable).

This is how big boys like Oracle and SAP do that, and you should too.

2
  • As stated in my other comment, customers will move some of their business processes to the new version, while other processes keep using the old version. So both versions will be used at the same time for production purposes.
    – Joost
    Commented Jan 18, 2022 at 7:43
  • Plenty of references for this if you search for Blue/Green database deployment Commented Jan 18, 2022 at 15:39
2

This isn't any different than any other 24/7 service that works on the internet. Usually such services don't have a maintenance downtime period for the deployment, therefore they must allow a new version to be deployed alongside the old. If it's for a short period of time (just during the deployment procedure) it's called Blue-Green Deployment; the longer coexistence of two versions would be Canary Releases.

In both of these types of releases we'd like to minimize the amount of time the old version has to be supported. The longer it can exist, the more temporary solutions we have to maintain and once they start to overlap it turns into a nightmare. But the overall principles don't change: you need to keep backward compatibility and support forward compatibility.

Backward Compatibility

For this your new version (v2) can introduce only additive changes compared to the previous version (v1). Meaning - no renaming & deleting columns used by prev version. If you need to rename, you'll have to add a new column in v2, and delete the old one in v3.

And while v1 exists you must maintain old data. Meaning you'll have to write to the old columns even though you don't need them anymore.

Forward Compatibility

This one is trickier. If you create a new column or table in v2, how would your v1 fill it if it doesn't know about it? Solutions could be:

  1. To use triggers. So if we insert only into OLD_COLUMN, then the NEW_COLUMN is getting filled automatically.
  2. But the logic may become even more complicated and it may become impractical to do this with triggers. In which case you may have to split your releases into multiple ones: v2.1 adds a new column but doesn't use it, v2.2 starts filling it but doesn't use it otherwise, and finally v2.3 migrates the old data and starts to use it fully.

Such complexity is manageable if you have frequent releases (Continuous Delivery), the longer the release cycle the more energy this takes.

You can probably find more information by looking up "Canary Releases" along with "database backward and forward compatibility".

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.