Best practice for a database used by multiple versions of an application
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.
"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.