Database Migrations

NG uses a database to store a whole bunch of data. Occasionally, the database structure needs to be updated to accommodate updated requirements. Originally, the only method of defining the database structure was via models - but this requires that the database be re-created any time its structure changes, which has the nasty side-effect of deleting all the data in it too.

The solution to this is migration scripts - Python scripts that define the database updates as updates to an existing structure, rather than overwrites of a previous revision. Those familiar with SQL recognize this as ALTER TABLE instead of CREATE TABLE statements.

How do I create a migration?

You need to do two things:

1. Create a migration file under bin/migrate. I suggest using the filename format d{yyyy}{mm}{dd}{hh}{mm}{ss}.py, to make migrations appear in time order. Including a description in this format isn't possible, as any description long enough to be useful - even three or four words - results in a filename too long for Python to import as a module.

   This migration file is how you define the migration from the existing database structure to the new database structure. You can use the sqlalchemy-migrate module and anything from sqlalchemy itself to achieve this. Your migration file must define a method with this signature:

   def execute(database, engine):

   This is what will get called by the migration runner. The database parameter is a reference to NG's database module (source in src/database.py); the engine parameter is an SQLAlchemy Engine that you should use wherever sqlalchemy-migrate requires an engine. Similarly, wherever sqlalchemy-migrate requires a reference to a table, use database.get_table to get this reference, passing in a model class to get a reference to its table. This method should perform all the necessary changes to the database. For example, a migration to add a unique index to the schema_migrations.migration_file column goes like this:

   from migrate.changeset.constraint import UniqueConstraint
   
   def execute(database, engine):
       cons = UniqueConstraint(database.SchemaMigration.migration_file,
                               table=database.get_table(database.SchemaMigration))
       cons.create(engine=engine)

2. Update the existing schema by adding, altering, or removing attributes on the model classes as necessary. You need to do this in addition to creating a migration file for two reasons: so that the models load the correct data from the updated database, and so that anyone creating the database for the first time gets the correct structure.

   For example, the schema update for the unique index example given for section 1 is this:

   class SchemaMigration(Base, BaseModel):
       __tablename__ = 'schema_migrations'
   
       id = Column(Integer, primary_key=True)
   -   migration_file = Column(String(255), nullable=False)
   +   migration_file = Column(String(255), nullable=False, unique=True)

You'll now need to run your migration to apply the changes to your local database. From the bin/ directory, run python3 run_migrations.py - any errors will be pointed out to you, and the migration will not be marked done until it has run successfully without errors.

Once you've run your migration (not before), update SCHEMA_VERSION in src/database.py to be the same as your migration filename, without the .py.