777650 - Add a db migration script for the tokenserver

Status: VERIFIED FIXED

(Cloud Services Graveyard :: Server: Token, defect, P3)

Description

[Alexis Metaireau (:alexis)]

We need a way to do proper migrations of our schema for the tokenserver / wimms.

I'm thinking in terms of alter tables mainly. I don't know if something exists already that would solve our issues. but having a way to apply some files in one way or the other sounds like a welcome addition.

Comment 1

[Alexis Metaireau (:alexis)]

Not sure if we need to handle this from the dev side or from the ops side. Feedback appreciated.

Comment 2

[Rob Miller [:rmiller]]

Alembic seems to be the best in class SQLAlchemy migration tool, written by Michael Bayer (SQLAlchemy author):

http://pypi.python.org/pypi/alembic/0.3.5
http://alembic.readthedocs.org/en/latest/index.html

After getting Alembic set up ops would run `alembic upgrade head` to apply all database migrations and `alembic downgrade <REV_UUID>` to revert.

Comment 3

[Ryan Kelly [:rfkelly]]

(In reply to Alexis Metaireau (:alexis) from comment #1)
> Not sure if we need to handle this from the dev side or from the ops side.
> Feedback appreciated.

Something else to consider here is third-party deployments of our stack.  It would be great if the migrations are exposed in a way that's easy for the to apply, so I think something like alembic would be useful even if ops don't use it directly.

Comment 4

[Ryan Kelly [:rfkelly]]

Alembic: http://alembic.readthedocs.org/en/latest/tutorial.html

Comment 5

[James Bonacci [:jbonacci]]

Related: (via :mostlygeek) Regarding schema handling the FxA guys have this: https://github.com/mozilla/fxa-auth-server/blob/master/bin/db_patcher.js

Comment 6

[Ryan Kelly [:rfkelly]]

Attachment: tokenserver-alembic-migrations.diff

This patch adds a basic alembic setup to the tokenserver repo, and includes migrations for the two not-yet-in-production deployment requests that I have open in Bug 986204 and Bug 993537.

To use them, you first have to "stamp" the database with its current version.  This creates a special table called "alembic_version" to track the schema version.

For prod, which has neither of the two migrations, do:

    ./bin/alembic stamp base

(It will find the token-prod.ini file and read it automatically, or you can pass a TOKEN_INI=/path/to/token.ini environment variable)

For stage, which has Bug 986204 already, we should probably downgrade to match prod so that we can test the entire deployment/migration setup.  This is facilitated by:

  ./bin/alembic stamp 17d209a72e2f
  ./bin/alembic downgrade base

Deployments will be able to migrate the schema by doing:

  ./bin/alembic upgrade head

And if you need to roll back to a previous version then you can:

  ./bin/alembic downgrade 17d209a72e2f

For now the upgrade process is manual, and IMO it should stay that way.  But I'm happy to consider further automation once we get comfortable with the 
workflow.

Thoughts?

Comment 7

[Toby Elliott [:telliott]]

Comment on attachment 8405897 [details] [diff] [review]
tokenserver-alembic-migrations.diff

Review of attachment 8405897 [details] [diff] [review]:
-----------------------------------------------------------------

Why do we need mako? I see the system already has it, but it seems like an odd dependency to have on a pure backend system. Otherwise, looks OK.

Comment 8

[Ryan Kelly [:rfkelly]]

> Why do we need mako? 

I think pyramid pulls this in by default.  We don't use it, but we might as well pin in the deps so they're a more realistic portrayal of what's on the system.

Comment 9

[Ryan Kelly [:rfkelly]]

Committed in:
https://github.com/mozilla-services/tokenserver/commit/be018f5613d3290b863649266dc782432cc63bc7

Comment 10

[Ryan Kelly [:rfkelly]]

Comment on attachment 8405897 [details] [diff] [review]
tokenserver-alembic-migrations.diff

I chatted with benson about this IRL, clearing feedback flag.

Comment 11

[James Bonacci [:jbonacci]]

I do believe we have completed this work as of last week...