Add database migration script

[dionhaefner]

We've had the issue a couple of times where a new release breaks compatibility with the database format while not really changing much. The way this is handled in other libraries is by providing a migration script that works like this:

$ terracotta migrate mysql://example.com
Found Terracotta database v0.7.3 (this is v0.8.0)
Bump database to v0.8.0? (y/N)
Performing database migration ...
Done!

$ terracotta migrate mysql://example2.com
Found Terracotta database v0.0.1 (this is v0.8.0)
Error: cannot auto-migrate. Please re-create database.

$ terracotta migrate mysql://example3.com
Found Terracotta database v0.8.1 (this is v0.8.0)
Nothing to do

This requires some sort of incremental changelog that specifies the necessary transformations between versions.

[j08lue]

That would be sweet. I had this case a few dozen times when upgrading deployments. I usually just bump the version in the DB manually when I know that the schema did not change. This would be a nice tool for that.

[mrpgraae]

Since we are using SQLAlchemy, I would suggest using Alembic https://alembic.sqlalchemy.org/en/latest/

Alembic is a CLI tool, but they also have a python API, so you can call their migration commands from within your own tool.

[dionhaefner]

Sounds good to me. Let's just make it an optional dependency that we check for inside the migration script.

[dionhaefner]

Okay so I played around with alembic and ... didn't love it.

The biggest problem (apart from the steep learning curve) is that modifying SQLite is not fully supported (for example re-naming tables which just ends in NotImplementedError).

I'm hesitant whether we need a migration library at all. Some simple ALTER TABLE statements should do it. I suggest we use SQLalchemy to connect to the DB, then run the migration via engine.execute and raw SQL.

@nickeopti could you perhaps imagine writing the SQLalchemy boilerplate for this? 😬 Then I can finish up the actual migrations and CLI and such.

[nickeopti]

I hope to get time to help at some point. Until then, this can probably get you started:

We can actually use the driver logic, just set verify=False to disable all checks:

import sqlalchemy as sqla
from terracotta import drivers

driver = drivers.get_driver('<driver_path>')
with driver.connect(verify=False):
    driver.meta_store.connection.execute(sqla.text('<raw sql statement>'))