680535 - Set up well thought out db migration infrastructure

Status: VERIFIED WONTFIX

(Cloud Services :: Server: Core, defect)

Description

[Rob Miller [:rmiller]]

We're obviously going to have to perform db schema migrations as our applications evolve over time.  Rather than doing this in an ad-hoc manner we should set up a sensible schema management infrastructure for handling and tracking migrations.  We might choose to use sqlalchemy-migrate, or if it's not suitable we should at least learn from it.  At the very least we will want to give each migration a unique id (ideally a UUID) and add a migrations table to the database to track which migrations have been applied.

Comment 1

[Tarek Ziadé (:tarek)]

I don't know if you recall this but raised this problem during the work week, and also raised another one:

Right now ops are doing index tweaks in the prod dbs, and ask us for time to time to backport some changes. also, I am not 100% confident the schema we have is the one Ops have.

IOW we don't have a single source of truth. So I suggested that we should first need to have a place where the SQL original schema lives (A sql dump of the schema) + every change in a form of an upgrade step (a new sql file everytime)

It would then be easy for both worlds to work with updates if we have a simple naming convention for the scripts.

Now for the sqlalchemy migrate, I suggested that we would rather use plain sql since SA knows how to load a mapper from an existing database schema. That makes the sql scripts easy to use for other people.

Also, you don't need a migrations table if you simply have a version number in the database.  for instance you can have "1.1" and upgrade files named 1.2.sql, 1.3.sql etc. e.g. migrations are applied sequentially.

Comment 2

[Rob Miller [:rmiller]]

This bug is for setting up database migration in server-core, but we're not building any new stuff on server-core, so it's probably not worth our time to do so. We already have bug 777650 open for setting up db migration in tokenserver, which will pave the way for similar rigs in AITC, Sync 2.0, and other Cornice/MozSvc based services.

Comment 3

[James Bonacci [:jbonacci]]

OK. Verifying as Wont Fix