How to make alembic autogenerate names for foreign_keys?

[maham360]

In my migration file I have alembic bringing up alterations such as:

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'])

I am trying to incorporate this code into my env.py file in order to make alembic name foreign keys automatically, but I'm not sure how to incorporate this into my code.

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)

So far I have it existing in my env.py file like this, but after I run a new migration nothing changes:

from __future__ import with_statement
from alembic import context
from sqlalchemy import engine_from_config, pool, MetaData
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


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)


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
                )

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


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

I'm using flask sqlalchemy. Can someone help me get this to work? Or have any better solutions? Thanks

[zzzeek]

the naming convention needs to be inside the MetaData object that your model is built on, this one:

target_metadata = current_app.extensions['migrate'].db.metadata

so wherever Flask sets up the "db.metadata", that's where you set up that naming convention.

here's a stackoverflow example https://stackoverflow.com/a/29153957/34549

[CaselIT]

Again the error is telling you the problem: NotNullViolation: column "prefix_sms" contains null values

• You have set prefix_sms in your model from nullable=True to nullable=False.
• Alembic detected that and created the migration
• The table in the db has null values in that column, so that cannot be null until you have not updated these values

You have to decide how to proceed here.
If that column needs to be not nullable, an option is to change the null value to a default if it has one, by running an update in the migration before the op.aleter_column. But it depends on your application, there is no problem I can see here from the alembic point of view

[maham360]

I see. So I want nullable=False for this column. I ran an update in an older migration file where 'services_history's' 'prefix_sms' column was first created, and it had nullable=True. So in that rule in the older file I added server_deafault here:

def upgrade():
    op.add_column('services_history', sa.Column('prefix_sms', sa.Boolean(), nullable=True, server_default='t'))

After doing so, I was able to get around the [SQL: ALTER TABLE services_history ALTER COLUMN prefix_sms SET NOT NULL] error. I mean I guess this technically works, but I don't want the prefix_sms column to have a default value of 'true' so I guess I am stuck on what to have the default value as. I want to forbid nulls if a column fails if some rows contain null already.

[CaselIT]

you can create a new migration or update the current one to remove the server default after adding the column.

[maham360]

Well I added server_default='t' to get past the SQL error, so I can just remove it from the old migration I added it to. That wouldn't really solve my issue, I'd have that SQL ALTER TABLE error again.

[CaselIT]

The process I follow to add a non nullable column is

• add the column with a server default op.add_column
• remove the server default op.alter_column