Need to automate database schema updates

[RJPercival]

When we have changed the database schema in the past, we have expected users to either delete and recreate their database (e.g. with scripts/resetdb.sh), or apply schema changes manually (after reverse-engineering them from commits). Users have complained that this (amongst other things) makes it painful to update Trillian. Instead, we should provide database schema migration scripts, e.g. a directory of SQL files containing files such as "increase_display_name_length.sql":

ALTER TABLE Trees ALTER COLUMN DisplayName VARCHAR (100)

We would no longer change the schema itself, but instead add SQL migration scripts to apply the changes we'd like. This does, admittedly, make seeing what the final schema looks like a bit harder - I'm open to suggestions on how to improve this.

[RJPercival]

@AlCutter has suggested Skeema may be a good tool for automating our schema updates.

[RJPercival]

Skeema works for MySQL schema updates, but not for Cloud Spanner (or Postgres, but that's not a supported storage backend at the moment). I've experimented with using github.com/golang-migrate/migrate instead (RJPercival@b1a4594), which does support Cloud Spanner, but it relies on us writing migration scripts and shipping them in the binaries, and managing a version number for each database schema (so it's more work than Skeema to maintain).

[RJPercival]

I investigated doing migrations using Terraform, but this isn't supported for either Cloud Spanner or MySQL (using the most widely used providers, at least).

[mhutchinson]

We aren't making schema changes recently. What we'll do to somewhat address this is add the schemas to a restricted code-owner review, and ensure that no breaking schema changes are made without some kind of documentation or process on upgrading.