2020-07-30T21:55:00Z

Fixing ALTER TABLE errors with Flask-Migrate and SQLite

If you've done any work with SQLite databases you surely know that this database is very limited in terms of making changes to the database schema. When working with a migration framework such as Flask-Migrate, it is common to end up with migration scripts that fail to upgrade or downgrade just because they need to remove or modify a column in a table, something that SQLite does not support.

In this article I'm going to discuss this limitation of the SQLite database, and show you a workaround that is specific to Flask-Migrate and Alembic.

SQLite's ALTER TABLE Implementation

Changes that you make to the fields in your model or to the constraints associated with them will end up as an ALTER TABLE statement sent to the database. If you are using MySQL, Postgres or most other database servers besides SQLite, this isn't a problem. With SQLite, however, the ALTER TABLE command only supports adding or renaming columns. Any other change to columns or constraints is going to be rejected with an error.

You can test this yourself very easily. Take any Flask-SQLAlchemy application (you can use one of mine) and after making sure your database is up to date, remove or comment out a column in one of the models. Then generate a migration:

(venv) $ flask db migrate -m "remove a column"

If you open the generated migration script everything will look correct. Below you can see the migration that was generated after I removed a column named about_me from the User model:

"""remove a column

Revision ID: ec813e760b53
Revises: 834b1a697901
Create Date: 2020-07-19 18:18:44.066766

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'ec813e760b53'
down_revision = '834b1a697901'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('user', 'about_me')
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('user', sa.Column('about_me', sa.VARCHAR(length=140), nullable=True))
    # ### end Alembic commands ###

The problem occurs when you try to upgrade the database with this migration:

(venv) $ flask db upgrade
[2020-07-19 18:21:14,268] INFO in __init__: Microblog startup
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 834b1a697901 -> ec813e760b53, remove a column
Traceback (most recent call last):
  File "/Users/mgrinberg/Documents/dev/python/microblog/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1283, in _execute_context
    self.dialect.do_execute(
  File "/Users/mgrinberg/Documents/dev/python/microblog/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 590, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: near "DROP": syntax error

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
[ ... removed a long list of uninteresting stack frames ... ]
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "DROP": syntax error
[SQL: ALTER TABLE user DROP COLUMN about_me]
(Background on this error at: http://sqlalche.me/e/e3q8)

If you tried this on your database, delete the broken migration script before continuing. You will learn how to generate migrations that work better in the next section.

Using Batch Mode

Unfortunately there is no simple way to perform these operations that SQLite has not implemented. The only way to do this is to generate a brand new table with the new schema and copy all the data. Alembic includes support for migrating a table in this way with a feature called "batch mode".

You can enable batch mode in Flask-Migrate right when you initialize the extension. If you use the direct method of initialization:

migrate = Migrate(app, db, render_as_batch=True)

If you use the two-step initialization:

migrate = Migrate()

def create_app():
    # ...
    migrate.init_app(app, db, render_as_batch=True)
    # ...

Now that you have batch mode enabled, try to generate the migration again:

(venv) $ flask db migrate -m "remove a column"

Nothing appears to be different, but if you look at the new migration script, you will see differences:

"""remove a column

Revision ID: adfac8a1b2ee
Revises: 834b1a697901
Create Date: 2020-07-19 18:32:27.782197

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'adfac8a1b2ee'
down_revision = '834b1a697901'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('user', schema=None) as batch_op:
        batch_op.drop_column('about_me')

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('user', schema=None) as batch_op:
        batch_op.add_column(sa.Column('about_me', sa.VARCHAR(length=140), nullable=True))

    # ### end Alembic commands ###

Complications

There are situations in which batch mode alone does not solve upgrade errors.

A nasty type of issue occurs when the ALTER TABLE error occurs in the middle of a migration, after some operations were already applied. This could leave your database in an inconsistent state, where some changes from the migration script have been applied, but because of the error the version information is still pointing to the previous migration.

To unblock a database after a partial migration was applied I follow these steps:

  • Determine which of the operations were applied.
  • Delete everything from the upgrade() function.
  • Edit the downgrade() function so that it only contains the reverse of the operations that were applied to your database.
  • Run flask db upgrade. This is going to succeed because now you are running an empty upgrade. The database version will be upgraded.
  • Run flask db downgrade. This will undo those partial changes that were applied earlier, and reset the database version back to the last good state.
  • Delete the migration script and try again with batch mode enabled.

Another common issue occurs when your table has unnamed constraints, which the batch mode process can't delete or modify because there is no way to refer to them by name. The Alembic documentation has some information on how to deal with unnamed constraints when using batch mode.

Conclusion

I hope this was a useful tip that can help you improve your migration workflow. Have you had issues with SQLite migrations that are not covered in this article? Let me know below in the comments.

1 comment

  • #1 Matt said 2020-08-12T20:33:36Z

    Thanks Miguel! And that Flask-Migrate batch configuration was the option all the time? Hours of rewriting migrate scripts with SQLite db...

Leave a Comment