Why does Flask-Migrate force me to do a 2-step migration?

Question

I am working on a project with Flask, SQLAlchemy, Alembic and their Flask wrappers (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 start the migration, I get an error message:

 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 the migrations, but last, it works. If after that I run the update command again, it works, that is, it completely updates my database without a single code change:

 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) starts queries in the table loaded by the previous one (Feed: Continents). If I create and load a table of continents, scripts should work. But this is not so. Why should I stop the migration process between in order to restart it in another team? I really don't get it. Is this some kind of team that Alembic executes after a series 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) 

Thank you very much,

UPDATE 1: update method for the last two migrations

As @Miguel explained in a comment, there are methods for updating the last two migrations here:

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 depend on the table loaded in 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 that I use mainly follows these 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 serve as a table of continents, and Feed: Countries serve as a table of countries. But he must request a table of continents to ensure the correct connection between the country and the continent.

UPDATE 2: Someone from Reddit already offered an explanation and workaround

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

I came across this before, and the problem is that the migrations are not performed individually, but instead alembic will port them all (or all of them that need to be run) and then execute SQL. This means that by the time of the last migration attempt, the tables do not actually exist, therefore you cannot make queries. Performance

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

This is not the most elegant solution (and it was for Postgres, the command may be different for other dbs), but it worked for me. Also, this is actually not a problem with Flask-Migrate, but a problem with alembic, so if you want Google for more information, search for the distillation cube. Flask-Migrate is just a wrapper around alembic that works with Flask-Script easily.

Answer 1

As pointed out by @themathemagician in reddit, Alembic by default runs all migrations in one transaction, so depending on the database engine and what you do in your migration scripts, some operations depending on things added to the previous migration may fail .

I have not tried this myself, but Alembic 0.6.5 introduced the transaction_per_migration option, which could solve this problem. This is a variant of calling configure() in env.py If you use the default configuration files, since Flask-Migrate creates them, then 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 do offline migrations as well, you need to commit configure() to run_migrations_offline() in the same way.

Try it and let me know if the problem is resolved.