"ALTER" syntax error while doing migration with alembic for new added non-nullable column in flask_sqlalchemy

Question

When I add a new column (nullable=False) to an existing table, I will need to update the migration revision file by manually to first add the column with nullable=True, and then update all existing records to set the column, after that alter the column to nullable=False. But I encountered an error of "ALTER": syntax error.

Here is the test script (test.py):

#!/usr/bin/env python
import os

import flask_migrate
from flask import Flask
from flask_script import Manager
from flask_sqlalchemy import SQLAlchemy

basedir = os.path.abspath(os.path.dirname(__file__))

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] =\
    'sqlite:///' + os.path.join(basedir, 'data.sqlite')
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

manager = Manager(app)
db = SQLAlchemy(app)

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)

migrate = flask_migrate.Migrate(app, db)
manager.add_command('db', flask_migrate.MigrateCommand)

if __name__ == '__main__':
    manager.run()

First I initialized the db and upgrade it to latest:

$ python test.py db init && python test.py db migrate && python test.py db upgrade
  Creating directory /tmp/test/migrations ... done
  Creating directory /tmp/test/migrations/versions ... done
  Generating /tmp/test/migrations/env.pyc ... done
  Generating /tmp/test/migrations/script.py.mako ... done
  Generating /tmp/test/migrations/env.py ... done
  Generating /tmp/test/migrations/alembic.ini ... done
  Generating /tmp/test/migrations/README ... done
  Please edit configuration/connection/logging settings in '/tmp/test/migrations/alembic.ini' before proceeding.
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'users'
  Generating /tmp/test/migrations/versions/86805d015930_.py ... done
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 86805d015930, empty message

Then I updated the model to add the new column 'email' which is nullable=False:

<snip>
...
class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), nullable=False)  # this is the new column

Then generate the migration revision file:

$ python test.py db migrate -m "add name"
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added column 'users.name'
  Generating /tmp/test/migrations/versions/c89371227a53_add_name.py ... done

Since the name column is non-nullable, need to update the migration file by manual, update it as bellow:

$ cat /tmp/test/migrations/versions/c89371227a53_add_name.py

from alembic import op
import sqlalchemy as sa

revision = 'c89371227a53'
down_revision = '45a51b6df68c'
branch_labels = None
depends_on = None

def upgrade():
    op.add_column('users', sa.Column('name', sa.String(length=64), nullable=True))
    op.execute("""
    UPDATE users SET name="foobar"
    """)
    op.alter_column('users', 'name', nullable=False)


def downgrade():
    op.drop_column('users', 'name')

Now run the migration:

$ python test.py db upgrade

I got an error as below:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "ALTER": syntax error [SQL: u'ALTER TABLE users ALTER COLUMN name SET NOT NULL']

How can I fix this or how should I do the migration for such cases?

My env is:

Flask==0.12.1
Flask-Migrate==2.0.3
Flask-Script==2.0.5
Flask-SQLAlchemy==2.2
SQLAlchemy==1.1.9
alembic==0.9.1

Answer 1

Just figured out the reason, it's because I'm using sqlite and sqlite lacks of ALTER support, a workaround for this is using the batch operation migrations http://alembic.zzzcomputing.com/en/latest/batch.html