Metadata-Version: 2.4
Name: alembic-utils-extended
Version: 1.3.3
Summary: A sqlalchemy/alembic extension for migrating entities like functions, triggers, views, materialized views, and check constraints.
License-Expression: MIT
License-File: LICENSE
Author: Justin Malin
Author-email: justin@joincandidhealth.com
Requires-Python: >=3.10
Classifier: Development Status :: 4 - Beta
Classifier: License :: OSI Approved :: MIT License
Classifier: Intended Audience :: Developers
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: SQL
Requires-Dist: alembic (>=1.9)
Requires-Dist: flupy
Requires-Dist: parse (>=1.8.4)
Requires-Dist: sqlalchemy (>=1.4)
Requires-Dist: typing_extensions (>=0.1.0)
Project-URL: GitHub, https://github.com/candidhealth/alembic-utils-extended
Project-URL: PyPI, https://pypi.org/project/alembic-utils/
Description-Content-Type: text/markdown

# Alembic Utils Extended

<p>
    <a href="https://github.com/candidhealth/alembic-utils-extended/actions">
        <img src="https://github.com/candidhealth/alembic-utils-extended/workflows/Tests/badge.svg" alt="Test Status" height="18">
    </a>
    <a href="https://github.com/candidhealth/alembic-utils-extended/actions">
        <img src="https://github.com/candidhealth/alembic-utils-extended/workflows/pre-commit%20hooks/badge.svg" alt="Pre-commit Status" height="18">
    </a>
</p>
<p>
    <a href="https://github.com/candidhealth/alembic-utils-extended/blob/master/LICENSE"><img src="https://img.shields.io/pypi/l/markdown-subtemplate.svg" alt="License" height="18"></a>
    <a href="https://badge.fury.io/py/alembic-utils-extended"><img src="https://badge.fury.io/py/alembic-utils-extended.svg" alt="PyPI version" height="18"></a>
    <a href="https://github.com/psf/black">
        <img src="https://img.shields.io/badge/code%20style-black-000000.svg" alt="Codestyle Black" height="18">
    </a>
    <a href="https://pypi.org/project/alembic-utils-extended/"><img src="https://img.shields.io/pypi/dm/alembic-utils-extended.svg" alt="Download count" height="18"></a>
</p>
<p>
    <a href="https://www.python.org/downloads/"><img src="https://img.shields.io/badge/python-3.10+-blue.svg" alt="Python version" height="18"></a>
    <a href=""><img src="https://img.shields.io/badge/postgresql-11+-blue.svg" alt="PostgreSQL version" height="18"></a>
</p>

**Autogenerate Support for PostgreSQL Functions, Views, Materialized Views, Triggers, Policies, and Check Constraints**

This is a fork of the much more popular [alembic_utils](https://github.com/olirice/alembic_utils) package
to extend the capabilities of [Alembic](https://alembic.sqlalchemy.org/en/latest/), which adds support for
autogenerating a larger number of [PostgreSQL](https://www.postgresql.org/) entity types,
including [functions](https://www.postgresql.org/docs/current/sql-createfunction.html), [views](https://www.postgresql.org/docs/current/sql-createview.html), [materialized views](https://www.postgresql.org/docs/current/sql-creatematerializedview.html), [triggers](https://www.postgresql.org/docs/current/sql-createtrigger.html),
and
[policies](https://www.postgresql.org/docs/current/sql-createpolicy.html).

This repo adds additional support for defining indices for materialized views and
autogenerating [check constraints](https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS).

## Quickstart

Visit the [quickstart guide](docs/quickstart.md) for usage instructions.

### Entity Registration

```python
# migrations/env.py

from alembic_utils_extended.pg_view import PGView
from alembic_utils_extended.replaceable_entity import register_entities

view = PGView(schema="public", signature="view", definition="SELECT 1")
register_entities([view])
```

### Monitor Check Constraints

Check constraints defined in SQLAlchemy models can also be autogenerated. Note that check constraints must be named. Add
to your `env.py`:

```python
# migrations/env.py
from alembic import context

context.configure(
    # ... other configurations ...
    compare_check_constraints=True,
)
```

### Monitor Indexes

Alembic's built-in autogenerate on SQLAlchemy 1.4 mishandles several PostgreSQL index shapes — function expressions
(`func.lower(col)`), directional modifiers (`desc(col)`, `literal_column("col DESC")`), `postgresql_ops` opclass hacks
for direction, and mixed shapes routinely produce wrong / duplicated diffs.

With `compare_indexes=True`, `alembic_utils_extended` takes over autogen for **all** user-declared indexes, reading the
DB side directly from `pg_index` and applying an identity-based diff. Consumers must also register an `include_object`
filter in `env.py` returning `False` for `type_ == "index"` so stock Alembic's index dispatcher doesn't fire and duel
with the fork. All indexes must be named.

```python
# migrations/env.py
from alembic import context

def include_object(obj, name, type_, reflected, compare_to):
    # alembic-utils-extended's `compare_indexes` comparator owns all index autogeneration.
    # Skip stock Alembic's index dispatcher entirely to avoid dueling autogeneration.
    if type_ == "index":
        return False
    return True

context.configure(
    # ... other configurations ...
    include_object=include_object,
    compare_indexes=True,
)
```

Indexes backing PRIMARY KEY and UNIQUE constraints are excluded automatically (managed by stock Alembic's constraint
diff).

**`NULLS NOT DISTINCT` on unique indexes (PostgreSQL 15+).** Declare it with the `postgresql_nulls_not_distinct=True`
dialect option on a `unique=True` index — the same spelling SQLAlchemy 2.0 uses natively, so model code is
forward-compatible:

```python
Index("uq_widget_slug", table.c.slug, unique=True, postgresql_nulls_not_distinct=True)
```

SQLAlchemy 1.4 has no support for this at all (it rejects the kwarg and never emits the clause). On 1.4,
`alembic_utils_extended` replicates SQLAlchemy 2.0's behavior: importing the package registers the dialect argument and
installs a compiler hook that splices `NULLS NOT DISTINCT` into `CREATE INDEX`. On SQLAlchemy 2.x it defers entirely to
native support. Two caveats on 1.4: **import `alembic_utils_extended` before any model module that declares the kwarg**,
so the dialect argument is registered first; and this covers unique *indexes* only — UNIQUE *constraints* are managed by
stock Alembic's constraint diff and are out of scope. `NULLS NOT DISTINCT` only affects uniqueness, so it is a no-op on
a non-unique index; the comparator treats the flag without `unique=True` as a mistake and raises at autogenerate time.

**Content changes under a stable name are not detected.** Comparison is identity-only (`(table_name, index_name)` set
diff). If the same index name exists in both the model and the database, the comparator treats it as unchanged. To
evolve an index's columns, WHERE clause, opclass, INCLUDE list, or method, rename it (which produces a drop + create
pair the fork will emit) or write a manual migration. This is a real trade-off vs. stock Alembic, which detects column-
list changes for plain-column indexes — but stock Alembic's index handling has enough other bugs on SA 1.4 that
identity-only-plus-rename is easier to reason about than any partial coverage.

**Coverage is best-effort, not guaranteed.** Indexes can drift out of prod (manual `CREATE INDEX`, out-of-band drops)
in ways autogen against a local DB can never catch. This library closes the most common autogen bugs but does not
guarantee every declared index actually exists in your database. Audit periodically with a direct `pg_index` query —
see the auditing recipe below.

**Common pitfalls the comparator catches at autogenerate time:**

- **`func.X("col_name")` anti-pattern** — bare strings inside `func.X(...)` are treated as bound-parameter literal
  values, not column references. The resulting index is on the constant string, not the column. Use
  `func.X(table.c.col_name)` or `func.X(literal_column("col_name"))` instead. The comparator raises with a remediation
  hint when it detects this.

### Auditing indexes against production

Run against a prod replica to catch drift the fork can't detect on its own (indexes declared in code but missing from
prod, or vice versa):

```sql
-- Lists every user-declared index in prod (excludes PK/UNIQUE constraint indexes).
-- Cross-reference against your model's declared index set.
SELECT
    n.nspname AS schema_name,
    t.relname AS table_name,
    c.relname AS index_name,
    pg_get_indexdef(i.indexrelid) AS index_definition
FROM pg_index i
JOIN pg_class     c ON c.oid = i.indexrelid
JOIN pg_class     t ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_constraint con ON con.conindid = i.indexrelid
WHERE n.nspname = 'public'
  AND con.oid IS NULL
  AND NOT i.indisprimary
ORDER BY t.relname, c.relname;
```

### Autogeneration

The next time you autogenerate a revision, Alembic will detect if your entities are new, updated, or removed and
populate the migration script.

```shell
alembic revision --autogenerate -m 'message'
```

## Contributing

If you have any issues with contributing, please reach out to justin@joincandidhealth.com so that we can work out any
issues you are having! This is mostly just forked directly
from [alembic_utils](https://github.com/olirice/alembic_utils), so it's possible something is
misconfigured.

### Testing

```bash
poetry install
poetry run pre-commit run --all-files
poetry run pytest
```

