Add opt-in autogenerate support for check constraints

[jrmalin]

Describe the use case

Currently, Alembic's autogenerate does not detect check constraints. When a CheckConstraint is added to or removed from a SQLAlchemy model, running alembic revision --autogenerate does not produce any migration operations for these constraints. This forces developers to manually write migrations for check constraints, which is error-prone and inconsistent with how other constraints (unique, foreign key) are handled.

This feature request is for an opt-in configuration option compare_check_constraints:

• Check constraints present in models but missing from the database; generates CreateCheckConstraintOp
• Check constraints present in the database but missing from models; generates DropConstraintOp

The comparison is done by name only to avoid false positives from SQL text normalization differences between databases and SQLAlchemy - this should be reasonably safe, as changing the text should mean that the constraint will be different in purpose and thus deserves a different name.

Databases / Backends / Drivers targeted

Any database that supports Inspector.get_check_constraints().

Example Use

# configuration in env.py
context.configure(
    connection=connection,
    target_metadata=target_metadata,
    compare_check_constraints=True,
)

# model code
CheckConstraint("amount >= 0", name="ck_order_amount_non_negative")

# generated migration
def upgrade():
    op.create_check_constraint(
        'ck_order_amount_non_negative',
        'order',
        'amount >= 0'
    )

Additional context

• opt-in: will not change existing functionality
• throws if constraint is unnamed: otherwise no reliable way to match
• name-based comparison: avoiding false positives for SQL normalization would be difficult, especially for each dialect.
• filtered out auto-generated psql enum constraints

Have a nice day!

[zzzeek]

note we have #508 for this, so one or the other of these issues should be closed in favor of the other.

I see that you're proposing a flag that looks like compare_check_constraints=True, but then it has a whole lot of limitations (as would be expected): names only, no support for unnamed, has a certain opinion about PG enum constraints (also fine, but an opinion). So right off, it doesn't feel like True is the right value here, because there's all kinds of potential answers to "should it compare check constraints" - someone comes along with a version that actually does expression comparison, someone comes along with a version that can do those expressions but only on some backends, or someone comes along with something that is more broadly scoped, like one that also autognerates PG EXCLUDE constraints or PG indexes with expressions, etc.

I'm headed somewhere with this, which is that this seems like an area that would scale better if these new kinds of "autogenerate" are pluggable. Like here, "name based check constraint autogenerate" is a modestly useful strategy, there's no other strategy right now, great let's use it. Oh later, someone publishes on pypi a "SQL Server CHECK constraint autogen" strategy - let's use that! or lets use both at the same time. See? The idea here is that comparing expression-based constraints is open ended and un-generalizable enough that I think a boolean True is probably not sufficient.

OK now I'm going to actually look at your PR to see what you have.... OK! So you can see, your PR is already a plugin. It starts out like this:

@comparators.dispatch_for("table")
def _compare_check_constraints(
    autogen_context: AutogenContext,
    modify_table_ops: ModifyTableOps,
    schema: Optional[str],
    tname: Union[quoted_name, str],
    conn_table: Optional[Table],
    metadata_table: Optional[Table],
) -> None:
    if not autogen_context.opts.get("compare_check_constraints", False):
        return

those last two lines right there are the whole reason that the PR has to also modify context.pyi, environment.py bah.

the entire set of functionality you have in your PR you could run with a released version of alembic right now, since it uses plugin points @comparators.dispatch_for(), @renderers.dispatch_for(ops.CreateCheckConstraintOp) etc. That is, this is already all plugins ( I did it this way to support the day that features like this would be proposed :) )

Let's do it like this:

# env.py
def run_migrations_online() -> None:
    connectable = engine_from_config(...)

    with connectable.connect() as connection:
        context.configure(
            connection=connection, target_metadata=target_metadata,
            autogenerate_plugins = [
                 "alembic.ext.checkconstraint",
                 "nifty_mssql_package.mssql_checks",
                 "pg_package.alembic.compare_exclude_constraints",
            ],
        )

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

all those plugins come in via setuptools entrypoint (the same way SQLAlchemy dialects work) and they provide some function like setup().

you still get to have all the code you have in your PR we just reorganize it to come from alembic.ext.checkconstraint which becomes the first "plugin" to go with alembic in this way.

I will gladly pull this in and organize it to use a new entrypoint feature as I do it in such a way that supports local "ext" files also.

[jrmalin]

@zzzeek thanks for the detailed review here! I think it's very fair that True is not exactly accurate here.

I will gladly pull this in and organize it to use a new entrypoint feature as I do it in such a way that supports local "ext" files also.

For the plugin, are you saying you'd gladly merge this once I organize it as you've described, or that you plan to organize it as you've described and there's nothing left for me to do?

[zzzeek]

at the moment we spent a few hours today discussing a re-org of alembic that would make a lot of room for 3rd party and internal plugins and I've begun sketching out this refactoring over at https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/6519 . it has a ways to go and this is not total top priority for me right now, but it would make room not just for your feature here but also hopefully plugins that can do PostgreSQL ENUM and similar without us having to commit to a specific system for that.

[jrmalin]

@zzzeek Any idea on a timeline for that? Is there anything I can do short-term to get this flag available?

[zzzeek]

@jrmalin you can use your code with any downloaded version of alembic right now. All you need is to run those hooks that you are already running like this:

@comparators.dispatch_for("table")
def _compare_check_constraints(...) 

that is, you can package up all your check constraint code into any module you want, import it in your env.py and set up those registration hooks.

Here are some other libraries that do the same thing:

https://github.com/olirice/alembic_utils

https://github.com/Pogchamp-company/alembic-postgresql-enum

The work I'm doing with "plugins" is at https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/6519?tab=comments but this is just reorganizing things a little bit so that third party libraries like the two above could also use entrypoints, but im not even sure there's much advantage to that