Sqlite lack of ALTER support, Alembic migration failing because of this. Solutions?

Question

I am developing a small registration application for a friend zumba class, using Flask, SQLAlchemy and Flask-migrate(alembic) to deal with db update. I settled on SQlite because the application has to be self contained and runs locally on a laptop without internet access and SQLite requires no installation of a service or other, which is a must too.

Dealing with SQLite lack of support of ALTER table wasn't a problem during the initial development as I simply destroyed, recreated the DB when that problem arised. But now that my friend is actually using the application I am facing a problem.

Following a feature request a table has to be modified and once again I get the dreaded " "No support for ALTER of constraints in SQLite dialect". I foresee that this problem will probably arise in the future too.

How can I deal with this problem? I am pretty much a newbie when it comes to dealing with database. I read that a way to deal with that is to create a new table, create the new constraint and copy the data and rename the table, but I have no idea how to implement that in the alembic script.

Answer 1

You can set a variable (render_as_batch=True) in the env.py file created with the initial migation.

context.configure(     connection=connection,     target_metadata=target_metadata,     render_as_batch=True ) 

It requires alembic > 0.7.0

This enables generation of batch operation migrations, i.e. creates a new table with the constraint, copies the existing data over, and removes the old table. See http://alembic.zzzcomputing.com/en/latest/batch.html#batch-mode-with-autogenerate

If you still encounter issues, be advised - there is still nuance with sqlite, e.g. http://alembic.zzzcomputing.com/en/latest/batch.html#dropping-unnamed-or-named-foreign-key-constraints