I am not understanding why I have "None" as the foreign key constraint name.

[maham360]

I read online that I don't need a name for my foreign key constraints so I'm not sure why it is showing up as migration change.

Migration file:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_foreign_key(None, 'complaints', 'notification_history', ['notification_id'], ['id'])
    op.create_foreign_key(None, 'templates_history', 'provider_details', ['provider_id'], ['id'])
    op.create_foreign_key(None, 'templates_history', 'communication_items', ['communication_item_id'], ['id'])

env.py file:

from __future__ import with_statement
from alembic import context
from sqlalchemy import engine_from_config, pool
from logging.config import fileConfig
from flask import current_app

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support

config.set_main_option(
    'sqlalchemy.url', current_app.config.get('SQLALCHEMY_DATABASE_URI'))
target_metadata = current_app.extensions['migrate'].db.metadata

# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.


def run_migrations_offline():
    """Run migrations in 'offline' mode.

    This configures the context with just a URL
    and not an Engine, though an Engine is acceptable
    here as well.  By skipping the Engine creation
    we don't even need a DBAPI to be available.

    Calls to context.execute() here emit the given string to the
    script output.

    """
    def include_object(object, name, type_, reflected, compare_to):
        if type_ == "index" or type_== "table" and reflected and compare_to is None:
            return False
        else:
            return True

    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        include_object=include_object,
        target_metadata=target_metadata,)

    with context.begin_transaction():
        context.run_migrations()


def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    def include_object(object, name, type_, reflected, compare_to):
        if type_ == "index" or type_== "table" and reflected and compare_to is None:
            return False
        else:
            return True

    engine = engine_from_config(
                config.get_section(config.config_ini_section),
                prefix='sqlalchemy.',
                poolclass=pool.NullPool)

    connection = engine.connect()
    context.configure(
                connection=connection,
                target_metadata=target_metadata,
                include_object=include_object,
                compare_type=True,
                include_schemas=True
                )

    try:
        with context.begin_transaction():
            context.run_migrations()
    finally:
        connection.close()


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

models.py source table:

class Complaint(db.Model):
    __tablename__ = 'complaints'

    id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    notification_id = db.Column(UUID(as_uuid=True), db.ForeignKey('notification_history.id'),
                                index=True, nullable=False)
    service_id = db.Column(UUID(as_uuid=True), db.ForeignKey('services.id'), unique=False, index=True, nullable=False)
    service = db.relationship(Service, backref=db.backref('complaints'))
    feedback_id = db.Column(db.Text, nullable=True)
    complaint_type = db.Column(db.Text, nullable=True, default=UNKNOWN_COMPLAINT_TYPE)
    complaint_date = db.Column(db.DateTime, nullable=True)
    created_at = db.Column(db.DateTime, nullable=False, default=datetime.datetime.utcnow)

Is there a way I can manually add a foreign key constraint name if I really need to? It doesn't make sense if I don't need one then why is this happening?

[zzzeek]

I read online that I don't need a name for my foreign key constraints so I'm not sure why it is showing up as migration change.

foreign key constraints can be created in the database without passing a name. in this case, PostgreSQL will choose a name for each one automatically. The issue comes when you want to write new migrations that wants to drop these constraints; when that happens, you need to supply the names. If PostgreSQL generated those names, you'd need to look in your database and manually write in those names for these constraints; additionally, Alembic might have trouble detecting that these PostgreSQL-named constraints match the ones you have in your model, since they don't have the same name.

So to solve all that we have the naming convention practice.

To use that with Flask, you'd need to set up this naming convention in the main Flask metadata object as described at https://stackoverflow.com/questions/29153930/changing-constraint-naming-conventions-in-flask-sqlalchemy/29153957#29153957 .

if the naming convention is set up, then when Alembic generates foreign key directives and other constraint directives you will see these names in the code it generates, such as :

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "test",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("name", sa.Text(), nullable=True),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_test")),
    )
    op.create_table(
        "o",
        sa.Column("test_id", sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ["test_id"], ["test.id"], name=op.f("fk_o_test_id_test")
        ),
        sa.PrimaryKeyConstraint("test_id", name=op.f("pk_o")),
    )

above you can see the PrimaryKeyConstraints and the ForeignKeyConstraint have a "name" present. that's the naming convention thing at work. If you dont have a naming convention then you'd see None as you are seeing in those alter statements.

[maham360]

Ok so when you alter a table you need a naming convention in place. I placed it in my models.py file here but I am getting this error:

sqlalchemy.exc.InvalidRequestError: When initializing mapper mapped class User->users, expression 'user_to_service' failed to locate a name ("name 'user_to_service' is not defined"). If this is a class name, consider adding this relationship() to the <class 'app.model.user.User'> class after both dependent classes have been defined.

db = SQLAlchemy(metadata=MetaData(naming_convention={
    'pk': 'pk_%(table_name)s',
    'fk': 'fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s',
    'ix': 'ix_%(table_name)s_%(column_0_name)s',
    'uq': 'uq_%(table_name)s_%(column_0_name)s',
    'ck': 'ck_%(table_name)s_%(constraint_name)s',
}))
Base = declarative_base(metadata=db)

Here is the traceback:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/ext/declarative/clsregistry.py", line 326, in __call__
    x = eval(self.arg, globals(), self._dict)
  File "<string>", line 1, in <module>
NameError: name 'user_to_service' is not defined

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

Traceback (most recent call last):
  File "/usr/local/bin/flask", line 8, in <module>
    sys.exit(main())
  File "/usr/local/lib/python3.8/site-packages/flask/cli.py", line 966, in main
    cli.main(prog_name="python -m flask" if as_module else None)
  File "/usr/local/lib/python3.8/site-packages/flask/cli.py", line 586, in main
    return super(FlaskGroup, self).main(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1053, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1659, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1659, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1395, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 754, in invoke
    return __callback(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/click/decorators.py", line 26, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/flask/cli.py", line 425, in decorator
    with __ctx.ensure_object(ScriptInfo).load_app().app_context():
  File "/usr/local/lib/python3.8/site-packages/flask/cli.py", line 388, in load_app
    app = locate_app(self, import_name, name)
  File "/usr/local/lib/python3.8/site-packages/flask/cli.py", line 240, in locate_app
    __import__(module_name)
  File "/app/application.py", line 11, in <module>
    from app import create_app
  File "/app/app/__init__.py", line 52, in <module>
    from app.clients.email.govdelivery_client import GovdeliveryClient  # noqa
  File "/app/app/clients/email/govdelivery_client.py", line 1, in <module>
    from app.models import (NOTIFICATION_CANCELLED, NOTIFICATION_DELIVERED, NOTIFICATION_PERMANENT_FAILURE,
  File "/app/app/models.py", line 368, in <module>
    class Service(db.Model, Versioned):
  File "/usr/local/lib/python3.8/site-packages/flask_sqlalchemy/model.py", line 67, in __init__
    super(NameMetaMixin, cls).__init__(name, bases, d)
  File "/usr/local/lib/python3.8/site-packages/flask_sqlalchemy/model.py", line 121, in __init__
    super(BindMetaMixin, cls).__init__(name, bases, d)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/ext/declarative/api.py", line 76, in __init__
    _as_declarative(cls, classname, cls.__dict__)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/ext/declarative/base.py", line 131, in _as_declarative
    _MapperConfig.setup_mapping(cls, classname, dict_)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/ext/declarative/base.py", line 160, in setup_mapping
    cfg_cls(cls_, classname, dict_)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/ext/declarative/base.py", line 194, in __init__
    self._early_mapping()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/ext/declarative/base.py", line 199, in _early_mapping
    self.map()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/ext/declarative/base.py", line 695, in map
    self.cls.__mapper__ = mp_ = mapper_cls(
  File "/app/app/history_meta.py", line 164, in map
    _history_mapper(mp)
  File "/app/app/history_meta.py", line 42, in _history_mapper
    for prop in local_mapper.iterate_properties:
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/mapper.py", line 2081, in iterate_properties
    configure_mappers()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/mapper.py", line 3301, in configure_mappers
    mapper._post_configure_properties()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/mapper.py", line 1966, in _post_configure_properties
    prop.init()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/interfaces.py", line 197, in init
    self.do_init()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/relationships.py", line 2076, in do_init
    self._process_dependent_arguments()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/relationships.py", line 2105, in _process_dependent_arguments
    setattr(self, attr, attr_value())
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/ext/declarative/clsregistry.py", line 333, in __call__
    self._raise_for_name(n.args[0], n)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/ext/declarative/clsregistry.py", line 293, in _raise_for_name
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception

[zzzeek]

so that's into SQLAlchemy things, somewhere there is a Table named "user_to_service" that is using a different MetaData object as its registry. So you need to find in the whole application the original, single MetaData object that everthing is using and make sure the naming convention is set there, without making new MetaData objects, since you already have the original one.

[maham360]

I'm not exactly sure where the file is that stores all the original MetaData. From navigating the backend, I see in "models.py" all the classes are there but I'm not sure where else it would be.

[zzzeek]

so the "db" thing we are looking for you are getting it here:

current_app.extensions['migrate'].db

i dont use flask so while I can find their docs for "current_app" I can't find the nature of this "extensions" attribute, but perhaps the flask-migrate extension is being used. I think you need to ask them for help on how to configure naming convention correctly.

[maham360]

I guess I am not understanding what file I am looking for. current_app.extensions['migrate'].db is in env.py file. I am using 'flask db migrate' to create a new migration so I think you are correct in saying flask-migrate is the extension. And all my models are in 1 file.

[zzzeek]

yes that's an example of referring to the Flask configuration you have set up. but you need to find where this configuration is declared, and modify that part. So this is a flask configuration issue.

you can try asking at https://github.com/pallets/flask/discussions where the folks there should know all about how flask is configured, since I don't know specifics for flask.

[maham360]

ah ok. thanks

[mmzeynalli]

I still get this problem. When the table is first created, everything is okay, and it follows naming convention. However, if I add foreign key later to the model, alembic generates it with None as name. It applies to unique constraints as well.

[zzzeek]

do you put names in the ForeignKey() objects you're making (or have a naming convention in place)?

[mmzeynalli]

No, I do not put name, expecting auto naming. However, I do have naming convetion which works when model is created, but fails when field is added later

[zzzeek]

if your naming convention is applied to the MetaData that is the parent of the Table where you put your ForeignKey or ForeignKeyConstraint, the AddConstraint operation will have the name rendered

[mmzeynalli]

Appearantly I have not. I was only doing target_metadata.naming_convention = DB_NAMING_CONVENTION in alembics env.py. Adding naming convention in Base class solved the problem. Thank you.