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.