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.
You can set a variable (render_as_batch=True) in the env.py file created with the initial migration for both online and offline migrations.
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 https://alembic.sqlalchemy.org/en/latest/batch.html#batch-mode-with-autogenerate
If you still encounter issues, be advised - there is still nuance with sqlite, e.g. https://alembic.sqlalchemy.org/en/latest/batch.html#dropping-unnamed-or-named-foreign-key-constraints