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.

Does this answer your question? [Why Flask-migrate cannot upgrade when drop column](https://stackoverflow.com/questions/30394222/why-flask-migrate-cannot-upgrade-when-drop-column) — Jack, Jun 10 '20 at 09:08

score 37 · Accepted Answer · edited Nov 04 '16 at 20:34

37

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

edited Nov 04 '16 at 20:34

Zitrax

19,036
20
88
110

answered Sep 10 '15 at 19:59

Jotham Apaloo

648
7
10

1

Thanks for the references. The links above are now broken but the page is available at http://alembic.zzzcomputing.com/en/latest/batch.html – xolox Jul 13 '16 at 11:27
1

thanks for the updated link @xolox I'd like to make sure it's readily avaible for anyone else with this question is customary for you to edit the original answer with the updated link? – Jotham Apaloo Oct 12 '16 at 18:26

score 0 · Answer 2 · answered Jun 05 '23 at 22:04

0

adding to the above answer: add render_as_batch=True to both offline and online

answered Jun 05 '23 at 22:04

Charles-Rocke

1

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

2 Answers2