Why is Flask-Migrate making me do a 2-steps migration?

Question

I'm working on a project with Flask, SQLAlchemy, Alembic and their wrappers for Flask (Flask-SQLAlchemy and Flask-Migrate). I have four migrations:

1c5f54d4aa34 -> 4250dfa822a4 (head), Feed: Countries
312c1d408043 -> 1c5f54d4aa34, Feed: Continents
41984a51dbb2 -> 312c1d408043, Basic Structure
<base> -> 41984a51dbb2, Init Alembic

When I start a new and clean database and try to run the migrations I get an error:

vagrant@precise32:/vagrant$ python manage.py db upgrade
...
sqlalchemy.exc.ProgrammingError: (ProgrammingError) relation "continent" does not exist
...

If I ask Flask-Migrate to run all migrations but the last, it works. If after that I run the upgrade command again, it works – that is, it fully upgrades my database without a single change in code:

vagrant@precise32:/vagrant$ python manage.py db upgrade 312c1d408043
INFO  [alembic.migration] Context impl PostgresqlImpl.
INFO  [alembic.migration] Will assume transactional DDL.
INFO  [alembic.migration] Running upgrade  -> 41984a51dbb2, Init Alembic
INFO  [alembic.migration] Running upgrade 41984a51dbb2 -> 312c1d408043, Basic Structure

vagrant@precise32:/vagrant$ python manage.py db upgrade
INFO  [alembic.migration] Context impl PostgresqlImpl.
INFO  [alembic.migration] Will assume transactional DDL.
INFO  [alembic.migration] Running upgrade 312c1d408043 -> 1c5f54d4aa34, Feed: Continents
INFO  [alembic.migration] Running upgrade 1c5f54d4aa34 -> 4250dfa822a4, Feed: Countries

TL;DR

The last migration (Feed: Countries) run queries on the table fed by the previous one (Feed: Continents). If I have the continents table create and fed, the scripts should work. But it doesn't. Why do I have to stop the migration process between then to re-start it in another command? I really don't get this. Is it some command Alembic executes after a serie of migrations? Any ideas?

Just in case

My models are defined as follows:

class Country(db.Model):

    __tablename__ = 'country'
    id = db.Column(db.Integer, primary_key=True)
    alpha2 = db.Column(db.String(2), index=True, unique=True)
    title = db.Column(db.String(140))
    continent_id = db.Column(db.Integer, db.ForeignKey('continent.id'))
    continent = db.relationship('Continent', backref='countries')

    def __repr__(self):
        return '<Country #{}: {}>'.format(self.id, self.title)

class Continent(db.Model):

    __tablename__ = 'continent'
    id = db.Column(db.Integer, primary_key=True)
    alpha2 = db.Column(db.String(2), index=True, unique=True)
    title = db.Column(db.String(140))

    def __repr__(self):
        return '<Continent #{}: {}>'.format(self.id, self.title)

Many thanks,

UPDATE 1: The upgrade method of the last two migrations

As @Miguel asked in a comment, here there are the upgrade methods of the last two migrations:

Feed: Continents

def upgrade():
    csv_path = app.config['BASEDIR'].child('migrations', 'csv', 'en')
    csv_file = csv_path.child('continents.csv')
    with open(csv_file) as file_handler:
        csv = list(reader(file_handler))
        csv.pop(0)
        data = [{'alpha2': c[0].lower(), 'title': c[1]} for c in csv]
        op.bulk_insert(Continent.__table__, data)

Feed: Countries (which depends on the table fed on the last migration)

def upgrade():

    # load countries iso3166.csv and build a dictionary
    csv_path = app.config['BASEDIR'].child('migrations', 'csv', 'en')
    csv_file = csv_path.child('iso3166.csv')
    countries = dict()
    with open(csv_file) as file_handler:
        csv = list(reader(file_handler))
        for c in csv:
            countries[c[0]] = c[1]

    # load countries-continents from country_continent.csv
    csv_file = csv_path.child('country_continent.csv')
    with open(csv_file) as file_handler:
        csv = list(reader(file_handler))
        country_continent = [{'country': c[0], 'continent': c[1]} for c in csv]

    # loop
    data = list()
    for item in country_continent:

        # get continent id
        continent_guess = item['continent'].lower()
        continent = Continent.query.filter_by(alpha2=continent_guess).first()

        # include country
        if continent is not None:
            country_name = countries.get(item['country'], False)
            if country_name:
                data.append({'alpha2': item['country'].lower(),
                             'title': country_name,
                             'continent_id': continent.id})

The CSV I'm using are basically following this patterns:

continents.csv

...
AS, "Asia"
EU, "Europe"
NA, "North America"
...

iso3166.csv

...
CL,"Chile"
CM,"Cameroon"
CN,"China"
...

_country_continent.csv_

...
US,NA
UY,SA
UZ,AS
...

So Feed: Continents feeds the continent table, and Feed: Countries feeds the country table. But it has to query the continents table in order to make the proper link between the country and the continent.

UPDATE 2: Some one from Reddit already offered an explanation and a workaround

I asked the same question on Reddit, and themathemagician said:

I've run into this before, and the issue is that the migrations don't execute individually, but instead alembic batches all of them (or all of them that need to be run) and then executes the SQL. This means that by the time the last migration is trying to run, the tables don't actually exist yet so you can't actually make queries. Doing

from alembic import op

def upgrade():
    #migration stuff
    op.execute('COMMIT')
    #run queries

This isn't the most elegant solution (and that was for Postgres, the command may be different for other dbs), but it worked for me. Also, this isn't actually an issue with Flask-Migrate as much as an issue with alembic, so if you want to Google for more info, search for alembic. Flask-Migrate is just a wrapper around alembic that works with Flask-Script easily.

Answer 1

As indicated by @themathemagician on reddit, Alembic by default runs all the migrations in a single transaction, so depending on the database engine and what you do in your migration scripts, some operations that depend on things added in a previous migration may fail.

I haven't tried this myself, but Alembic 0.6.5 introduced a transaction_per_migration option, which might address this. This is an option to the configure() call in env.py. If you are using the default config files as Flask-Migrate creates them, then this is where you fix this in migrations/env.py:

def run_migrations_online():
    """Run migrations in 'online' mode.

    # ...
    context.configure(
                connection=connection,
                target_metadata=target_metadata,
                transaction_per_migration=True        # <-- add this
                )
    # ...

Also note that if you plan to also run offline migrations you need to fix the configure() call in the run_migrations_offline() in the same way.

Give this a try and let me know if it addresses the problem.