Flask Migrate "ValueError: Constraint must have a name"

Question

I have created a flask migrate script ,however, when I running the upgrade function, I get the following error:

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 6378428b838a, empty message
Traceback (most recent call last):
  File "migrate.py", line 22, in <module>
    manager.run()
  File "/Users/slatifi/git/StaffTrainingLog/venv/lib/python3.7/site-packages/flask_script/__init__.py", line 417, in run
    result = self.handle(argv[0], argv[1:])
  File "/Users/slatifi/git/StaffTrainingLog/venv/lib/python3.7/site-packages/flask_script/__init__.py", line 386, in handle
    res = handle(*args, **config)
  File "/Users/slatifi/git/StaffTrainingLog/venv/lib/python3.7/site-packages/flask_script/commands.py", line 216, in __call__
    return self.run(*args, **kwargs)
  File "/Users/slatifi/git/StaffTrainingLog/venv/lib/python3.7/site-packages/flask_migrate/__init__.py", line 95, in wrapped
    f(*args, **kwargs)
  File "/Users/slatifi/git/StaffTrainingLog/venv/lib/python3.7/site-packages/flask_migrate/__init__.py", line 280, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
  File "/Users/slatifi/git/StaffTrainingLog/venv/lib/python3.7/site-packages/alembic/command.py", line 298, in upgrade
    script.run_env()
  File "/Users/slatifi/git/StaffTrainingLog/venv/lib/python3.7/site-packages/alembic/script/base.py", line 489, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/Users/slatifi/git/StaffTrainingLog/venv/lib/python3.7/site-packages/alembic/util/pyfiles.py", line 98, in load_python_file
    module = load_module_py(module_id, path)
  File "/Users/slatifi/git/StaffTrainingLog/venv/lib/python3.7/site-packages/alembic/util/compat.py", line 173, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 728, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "migrations/env.py", line 96, in <module>
    run_migrations_online()
  File "migrations/env.py", line 90, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/Users/slatifi/git/StaffTrainingLog/venv/lib/python3.7/site-packages/alembic/runtime/environment.py", line 846, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/Users/slatifi/git/StaffTrainingLog/venv/lib/python3.7/site-packages/alembic/runtime/migration.py", line 518, in run_migrations
    step.migration_fn(**kw)
  File "/Users/slatifi/git/StaffTrainingLog/migrations/versions/6378428b838a_.py", line 23, in upgrade
    batch_op.create_foreign_key(None, 'organisation', ['organisation'], ['id'])
  File "/Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.7/lib/python3.7/contextlib.py", line 119, in __exit__
    next(self.gen)
  File "/Users/slatifi/git/StaffTrainingLog/venv/lib/python3.7/site-packages/alembic/operations/base.py", line 325, in batch_alter_table
    impl.flush()
  File "/Users/slatifi/git/StaffTrainingLog/venv/lib/python3.7/site-packages/alembic/operations/batch.py", line 106, in flush
    fn(*arg, **kw)
  File "/Users/slatifi/git/StaffTrainingLog/venv/lib/python3.7/site-packages/alembic/operations/batch.py", line 390, in add_constraint
    raise ValueError("Constraint must have a name")
ValueError: Constraint must have a name

I have seen other people with the same error and they simply added render_as_batch to the env.py file. I did this but I still get the same error. Any thoughts?

Note: This is the modification I made in the env.py file:

with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            process_revision_directives=process_revision_directives,
            **current_app.extensions['migrate'].configure_args,
            render_as_batch=True
        )

This is the upgrade script created by the migration

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '2838e3e96536'
down_revision = None
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.add_column(sa.Column('organisation', sa.String(length=5), nullable=False))
        batch_op.create_foreign_key(None, 'organisation', ['organisation'], ['id'])

    # ### 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.drop_constraint(None, type_='foreignkey')
        batch_op.drop_column('organisation')

    # ### en

d Alembic commands ###

Answer 1

This is normal because SQLite3 doesn't support ALTER tables.

You can pass the render_as_batch=True during the instantiation of Flask-Migrate like this :

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

Don't need to modify the env file of Flask-Migrate.

In addition, to totally avoid the problem for your futures migrations, you can create a constraint naming templates for all types of constraints to the SQLAlchemy metadata, and then I think you will get consistent names.

See how to do this in the Flask-SQLAlchemy documentation. I'm copying the code example from the docs below for your convenience:

from sqlalchemy import MetaData
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

convention = {
    "ix": 'ix_%(column_0_label)s',
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s"
}

metadata = MetaData(naming_convention=convention)
db = SQLAlchemy(app, metadata=metadata)

Answer 2

The other answers show how to configure Flask to use named constraints in the future, but that doesn't solve the problem of dropping existing unnamed constraints in Alembic migrations. To handle any existing unnamed constraints, you need to also define the naming convention in the migration script, as explained in the Alembic docs.

For example, suppose you're trying to do a migration that adds ON DELETE CASCADE to an existing foreign key in your test table. If you've followed one of the other answers (e.g. https://stackoverflow.com/a/62651160/470844) and added the naming_convention to the Flask init script, then flask db upgrade will generate something like this:

def upgrade():
    with op.batch_alter_table('test', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.create_foreign_key(batch_op.f('fk_test_user_id_user'), 'user', ['user_id'], ['id'], ondelete='CASCADE')

Note that while the create_foreign_key call uses a constraint name (i.e. fk_test_user_id_user), the drop_constraint call still uses None as the constraint name, which will cause ValueError: Constraint must have a name error in this question's title.

To fix that, you need to edit the migration to use the naming_convention, and replace the None with the generated constraint name. For example, you'd change the above upgrade to:

naming_convention = {
    "ix": 'ix_%(column_0_label)s',
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(column_0_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s"
}

def upgrade():
    with op.batch_alter_table('test', schema=None, naming_convention=naming_convention) as batch_op:
        batch_op.drop_constraint('fk_test_user_id_user', type_='foreignkey')
        batch_op.create_foreign_key(batch_op.f('fk_test_user_id_user'), 'user', ['user_id'], ['id'], ondelete='CASCADE')

(In this example, you'd also need to disable SQLite foreign key support while running the migration script, e.g. using the technique here, to avoid the batch migration itself triggering a cacading delete. The problem is documented here.)