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

sqlite sqlalchemy alembic
Kinwolf picture Kinwolf · May 21, 2015 · Viewed 9.8k times · Source

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

Jotham Apaloo picture Jotham Apaloo · Sep 10, 2015

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

Related questions

Using Alembic API from inside application code

I am using SQLite as an application file format (see here for why you would want to do this) for my PySide-based desktop application. That is, when a user uses my app, their data is saved in a single database …

Read More
How to delete a table in SQLAlchemy?

I want to delete a table using SQLAlchemy. Since I am testing over and over again, I want to delete the table my_users so that I can start from scratch every single time. So far I am using SQLAlchemy …

Read More
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table

I am fresh to flask and was trying to build a blog on my own, and I ran into an issue with SQLite operation error. I have researched similar problem on Github and Stackoverflow but none of the typical typo …

Read More