How to declare NOT VALID PostgreSQL constraints with SQLAlchemy and alembic?

Question

I'd like to create a PostgreSQL CHECK constraint that is marked as NOT VALID, yet I don't see a way to create such constraint in alembic or declare it using SQLAlchemy.

It looks like SQLAlchemy added support for introspection of NOT VALID constraints, but not the other way around: https://github.com/sqlalchemy/sqlalchemy/commit/3980a9a455d08c5073cabc3c2b77de46fa36d7b4 .

Is there a way to do that? Or is the feature just missing and I need to manually write the SQL statements with alembic?

Answer 1

Support for declaring NOT VALID constraints in PostgreSQL dialect was added in SQLAlchemy 1.4.32. Such constraint can be declared by setting postgresql_not_valid dialect option to True:

import sqlalchemy as sa


sa.CheckConstraint(
    "lastname IS NOT NULL",
    postgresql_not_valid=True,  # ⬅
)
sa.ForeignKeyConstraint(
    ["head_teacher_id"],
    ["teachers.id"],
    postgresql_not_valid=True,  # ⬅
)

Alembic's create_check_constraint and create_foreign_key functions will forward any dialect option to SQLAlchemy, therefore creating a migration with such constraint is pretty straightforward:

from alembic import op


def upgrade():
    op.create_check_constraint(
        "ck_lastname_not_null",
        "students",
        "lastname IS NOT NULL",
        postgresql_not_valid=True,  # ⬅
    )
    op.create_foreign_key(
        "fk_head_teacher",
        "students",
        "teachers",
        ["head_teacher_id"],
        ["id"],
        postgresql_not_valid=True,  # ⬅
    )


def downgrade():
    op.drop_constraint("ck_lastname_not_null", "students")
    op.drop_constraint("fk_head_teacher", "students")

See also PostgreSQL constraint options documentation.