Migrations

#

As we've already seen, when we're building a web application with a relational database (like MySQL or PostgreSQL) we will often have to add new tables or add columns. If your project is only operating locally with test data, it's not a big deal to drop the entire database and recreate it with the new tables or columns, but if we're in a production environment, your users will be pretty upset if you destroy their data. Instead we need to introduce changes to the production database progressively. If you're working with other developers, they also need to be able to make the same changes to their development database. The way we solve for this is to create snippets of code that encode what changes we're making to schema and track which ones we have already run. The individual changes are referred to as migrations.

To manage the migrations and integrate with SQLAlchemy, we use a Python library called Alembic which is able to generate and run migrations. To better integrate Alembic with Flask, we'll use an extension called Flask-Migrate. This provides us with an way to create and run migrations through the Flask CLI command.

To install these libraries, we just need to add Flask-Migrate to our requirements.txt file and run pip install -r requirements.txt. Then like other extensions, we'll need to initialize it within extensions.py and pass in app within yumroad/__init__.py. There is a catch this time however, Alembic needs to be know about all of the models in our application to properly generate migrations. In order for that to happen, we need to import our models.py file. If we don't import models.py here, Python may never load that module.

yumroad/extensions.py:

from flask_migrate import Migrate
...

migrate = Migrate()

yumroad/__init__.py:

from yumroad.extensions import (..., db, migrate)
import yumroad.models
...
def create_app(environment_name='dev'):
    ...

    migrate.init_app(app, db, render_as_batch=True)

The render_as_batch is a Alembic configuration that helps us support both SQLite and PostgreSQL for our migrations. Without it, some migrations would not be able to run on SQLite.

Since we are planning on using different kinds of databases in development (SQLite) and production (PostgreSQL), we will need tell SQLAlchemy to use a consistent naming convention for how indexes and constraints are named on the database level. By specifying a fixed naming convention, we make it easier for Alembic to identify which constraints already exist or need to be changed. This ends up being important since by default the naming conventions may vary between the databases. You can read more about this on Alembic's documentation.

from sqlalchemy import MetaData

naming_convention = {
    "ix": 'ix_%(column_0_label)s',
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(column_0_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s"
}

db = SQLAlchemy(metadata=MetaData(naming_convention=naming_convention))
migrate = Migrate()

On rolling back migrations: While it is possible to rollback a migration, it's best to avoid rolling back migrations in production. Instead you can write a new migration to make the changes you want. This ensures that's there's a clear one way track of changes to your database along with the deployed code.