Flask-migrate: removing columns with sqlite backend breaks migrate

[ytjohn]

First time using Flask-Migrate, so feel free to correct any bad assumptions I have. If I remove a column from my model and I'm using sqlite as my backend, I can no longer utilize the db migrate/db upgrade features.

This isn't even really an issue with Flask-Migrate. sqlite itself does not support dropping or renaming database columns, so an ALTER TABLE table1 DROP COLUMN column2 will fail. This, I understand.

The problem I'm having is that I can't do anything from that point. Once Flask-Migrate has a migration recorded that involves DROP COLUMN, I'm stuck. I can manually go into sqlite and drop/recreate my tables, but then Flask-Migrate reports that Target database is not up to date. I can't even add the columns back into my model, because migrate won't generate new sql if the database isn't up to date.

I did work out (while writing this up in fact) that if I manually correct the database (drop and recreate table according to how I want it), and then do an UPDATE alembic_version SET version_num=${REVISION}; statement, I can get back to using migrate/upgrade. Before I figured this out, I was deleting the database and migrations directory all the time. I also think that I could edit the specific migration file directly and remove the op.drop_column lines.

I was thinking that it would be nice if the migrate script realized that op.drop_column and sqlite backend do not work together and could inform the user of that/bail out. Another thought I had was that some sort of "force-recreate" option could essentially take the current model and generate the equivalent op.drop_table and op.create_table statements with no alter statements required.

At minimum, I think this should be noted in a "troubleshooting" section of the docs. If we want to go that way, I'd even be happy to write something up in a pull request.

[miguelgrinberg]

Removing a column is not the only limitation in SQLite, there are others. Mike Bayer, the author of Alembic indicated that he would accept a contribution to Alembic to work around these limitations, but he personally considers that SQLite should implement DROP COLUMN and others properly. There is an open issue in Alembic's bug tracker about this, with a very interesting comment thread: https://bitbucket.org/zzzeek/alembic/issue/21/column-renames-not-supported-on-sqlite

I think if you are working with SQLite you should avoid column deletes and renames, and conversely if you need these operations then you should avoid SQLite and go with the more robust databases.

If one of these invalid operations gets into your migration history then you have two options to fix everything:

1. make the changes by hand, then use manage.py db stamp HEAD to register in the Alembic metadata that you are at the most updated version.
2. if you don't mind losing your data then delete the SQLite file, call db.create_all() and then use the stamp command as above.

I think the best Flask-Migrate can do is document the problem, any solutions that try to work around the SQLite limitation or prevent the invalid operations from being generated would have to go into Alembic, and for that you will have to discuss it with Mike.

[cameronehrlich]

I just switched to running Postgres locally, and while its a bit of a pain, it fixed this problem for me. Also, it's much faster the SQLite!

[miguelgrinberg]

Also note that alembic 0.7 introduces some support for renaming and removing columns in sqlite. What it does is create a new table and copy all the data, so it is a bit of a trick.

[miguelgrinberg]

Closing due to inactivity.

[emcenrue]

@cameronehrlich were you able to drop columns with postgre and not have flask freak out when upgrading? I get the error: sqlalchemy.exc.ProgrammingError: (ProgrammingError) index does not exist when I upgrade after dropping a column, but migrating worked just fine.

Thanks! :)

[danidee10]

@miguelgrinberg i'm running alembic 0.8.7 and this problem still exists....though i must admit i've switched to postgres

was the fix in alembic 0.7 removed?

[miguelgrinberg]

@danidee10 so this isn't automatically enabled, you have to make changes to your env.py file to activate this. See https://github.com/miguelgrinberg/Flask-Migrate/issues/61 for information on how to use this feature of Alembic.

[pfabri]

For those who struggle to put this all together, here's an explanation video I found very helpful.