Database table updates in GitOps

Question

The scenario: I have a web application running in Kubernetes. The web application is managed and updated by Argo CD, which means a Git repo defines the state of the application.

Now the new requirement: The users should also add a phone number when they register. This requires two changes:

• A change to the application itself, maybe some HTML and JavaScript. You can change the application, package it as (Docker) image and then apply the changes by changing the Argo CD repo
• A change to the database. The user table needs a column phone_number.

The database lives outside of the Kubernetes cluster. How do I apply these two changes in a sensible manner?

One could change the application in a way that it works with the old and the new table layout (using a feature flag, or just by checking whether the column phone_number exists), then deploy the application and then add the column "manually".

But I am looking for a better, more automated solution in the spirit of GitOps.

Answer 1

The general term for this is a 'versioned schema' or 'evolutionary database design'. In a nutshell, you keep a set of scripts in your source control which modify the database. You start with an initial creation script which defines the baseline DB from an empty 'scratch' DB schema. Each change to the DB schema is then a new script. Each script is given a version number so that the order of changes is clear. You never change any existing scripts in normal circumstances, you always add new ones.

In the initial version script, you create one or more tables which tracks all the scripts that have been run against it.

The trick is to align the versions of the DB scripts with release versions of your code in the source repo. That is, if you want to run a version of your application from a year ago, the automation can run all the scripts required to align the DB schema with it. You can then recreate the database from scratch. Additionally, when you run an upgrade, the version table in the DB tells the tooling which scripts need to be run to bring it to the desired version.

There are various vendor/open-source tools around this such as Liquibase or Flyway (not a recommendation, just for reference) It's also not terribly difficult to roll your own here but YMMV.

One note of warning: there's this idea around these kinds of tools that the first thing your application should do on startup is check the version of the DB and upgrade it as necessary. I strongly disagree with that idea for two main reasons: 1) your application almost surely shouldn't have the kinds of rights required on the DB to do that and 2) I would never want to be the person who accidentally connected to the production DB from development and broke production, nor would I wish that on (mostly) anyone. Instead, I would recommend checking the DB version and if it doesn't match the application's version, exit with an error. I suppose if you manage the permissions properly on the prod or other important databases that's what would happen anyway, but I don't see the point of setting a booby-trap for yourself.