How to use Flask-Migrate to do database migration?

Question

I come across a problem when playing with Flask-Migrate to upgrade/downgrade the database. There were two tables, User and Post, defined by the following classes:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), index=True, unique=True)
    email = db.Column(db.String(120), index=True, unique=True)
    password_hash = db.Column(db.String(128))
    posts = db.relationship('Post', backref='author', lazy='dynamic')

    def __repr__(self):
        return '<User {}>'.format(self.username)

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.String(140))
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id')) 

And I have added some entries into these tables.

Now the problem. :)

For fun, I added a column to User table through: dummy = db.Column(db.String(20)). After modified the table schema, I ran the following commands:

1. flask db migrate ---- success
2. flask db upgrade ---- success

Then I wanted to go back:

3. To restore the database back to previous state, I ran flask db downgrade ---- failed with the Error: sqlite3.OperationalError: near "DROP": syntax error
4. I tried to fix the error by deleting the dummy = db.Column(db.String(20)) statement from User class, then ran flask db downgrade again ---- failed again with the same error.
5. Then I think maybe the Flask-Migrate need to be aware of the deletion , so I ran flask db migrate ---- success
6. Try flask db upgrade ---- failed again with the same error.

So How does the flask-migrate work? And specifically, how can I restore the database to initial state (without the dummy attribute)?

Thanks!

Answer 1

Flask migrate generates migration scripts when you run the flask db migrate command. You can look at the commands in these migration files to see what they're doing and make sure they're doing the right thing. In fact it recommends that you check the automatically generated migration scripts before running them.

If you change something and don't re run the migrate command then neither upgrade nor downgrade will do anything different as they just run the corresponding migration scripts in the migrate folder.

If the database isn't important the easiest thing to do is to delete the database and migration scripts and then run flask db init again and start over with the new knowledge about migration scripts and see if you can get the hand of upgrading and downgrading.

Answer 2

Natively, SQLite does not support dropping or altering columns (obviously I guess you use SQLite). This means that the ALTER statement is not supported by SQLite, yet relational schema migrations rely on this statement.

To work around the problem, you must make a series of copies of the SQLite tables that correspond to the new structure, transfer the data from the existing table to the new one, and then delete the old table.

Fortunately, for the case of Alembic / Flask-migrate, there is a context manager ( batch_alter_table) that allows you to easily manage all these changes.

In your case, the solution is to open your migration script and at the level of the downgrade() method, replace the instruction that is there (probably op.drop_column('roles', 'dummy')) by:

with op.batch_alter_table('roles') as batch_op:
    batch_op.drop_column('dummy')

See this link for more information.

Some small details:

• When using migration tools, always keep in mind that automatic migrations are not always accurate and can miss some details. Migration scripts generated automatically should always be reviewed.

• Just in case, when downgrade your database, make sure you delete the migration script, and then generate a new one to replace it.