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 ###

score 15 · Answer 1 · edited May 08 '21 at 12:17

15

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)

edited May 08 '21 at 12:17

Vipul

4,038
9
32
56

answered Jun 30 '20 at 06:00

dnmxm

260
1
11

I tried this as well but it still doesn't work - I still get the same error. Any ideas? – Saif Jun 30 '20 at 11:49
Could you show us the upgrade method from the migration file ? – dnmxm Jun 30 '20 at 13:33
I have updated the original question to include the above. Thanks for your support – Saif Jun 30 '20 at 18:03
@Saif: if you still get the same error after following this answer, you might need the additional changes in [my answer](https://stackoverflow.com/a/66920329/470844) that let you handle existing unnamed constraints. – ntc2 Apr 02 '21 at 13:55

ntc2 · Accepted Answer · 2021-04-19T14:08:42.637

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.)

score 2 · Answer 3 · answered Feb 22 '21 at 19:51

Installing Flask-Migrate helps to avoid code references to declarative_base or modifying the env.py file. For apps with app factory, split the metadata assignment and the db initiation between the extensions.py and __init__.py files.

extensions.py:

from sqlalchemy import MetaData
from flask_sqlalchemy import SQLAlchemy

metadata = MetaData(
    naming_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"
    }
)

db=SQLAlchemy(metadata=metadata)

__init__.py:

from flask import Flask, Blueprint

from config import ...
from flask_migrate import Migrate
from extensions import db

...

def create_app(): 
    app = Flask(__name__,
                template_folder='../app/templates')

    app.config.from_object(config[...])
    config[...].init_app(app)
    
    db.init_app(app)

    with app.app_context():
        db.create_all()

    migrate = Migrate(app, db)

    ...
    
    from .templates.main import main

    app.register_blueprint(main)

    return app

Flask Migrate "ValueError: Constraint must have a name"

3 Answers3