Support for full and delta deployments of database fragments (database migrations)

[mikezaschka]

By defining the core application within the .cds files, it is super easy to build and enhance an applications data model, as it is fully represented within cds. And this works pretty great until after you have deployed the first version of your application to your production database. From then on, you cannot make a full deployment of your data model anymore but need to only apply the deltas coming from new developments.

CAP has some built in functionality to compile the whole data model into various formats (cds compile --to ) and even to directly deploy it to SQLite and SAP HANA (cds deploy --to ). While the HANA adapter transforms the data model into HANA specific fragments and then utilizes the HDI deployer to handle the deltas, the SQLite adapter simply drops and recreates all known tables and views... which does not work for production environments.

It is therefore mandatory to introduce a new mechanism within this project, that supports initial as well as delta deployments (and could potentially be used by other db adapters as well). The rough requirements would be:

• still allow for full deployments in dev mode (like cds deploy --to sqlite or currently cds-pg deploy srv --to db) by leverage existing functionality like the import of csv files, etc.
• create some sort of file based (and thus versionable) internal changelog that stores information of the database development over time
• create a deployment mechanism, that stores the internal version of the database within the database and allows to only apply the missing deltas
• deltas should be autogenerated to keep the development flow clean, but they also should still be customizable (e.g. renaming a column cannot be automated and probably needs adjustments by hand)
• must be able to rollback to a previous release
• contain some sort of command line support
• must respect multitenancy

While this can also be handcrafted based on the internal CSN models, there are already tools available that may come in handy like Liqubase (https://www.liquibase.org/) and others.

[mikezaschka]

I may chose this topic as part of the devtoberfest build week to discuss and build a possible solution. :-)

[mikezaschka]

I started adding support within the project but then moved stuff out into its own library: Currently at https://github.com/mikezaschka/cds-dbm

Reasons:

Internally I heavily rely on liquibase which has support for multiple databases. There is definitely some stuff that is specific to PostgreSQL, but on the other hand, there are many parts that are agnostic. By keeping everything in cds-dbm it will be possible to keep the shared stuff in one place and add adapters for each supported database in the future.

[gregorwolf]

I think we can close this issue as cds-dbm solves it.