Metadata-Version: 2.5
Name: alembic-pg-autogen
Version: 0.5.0
Summary: Alembic autogenerate extension for PostgreSQL-specific objects
Project-URL: Documentation, https://alembic-pg-autogen.readthedocs.io
Project-URL: Repository, https://github.com/eddieland/alembic-pg-autogen
Project-URL: Issues, https://github.com/eddieland/alembic-pg-autogen/issues
Author-email: Edward Jones <edwardrjones97@gmail.com>
License-Expression: MIT
License-File: LICENSE
Keywords: alembic,autogenerate,database,migration,postgresql,sqlalchemy
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3 :: Only
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 :: Python :: 3.14
Classifier: Topic :: Database
Classifier: Topic :: Database :: Front-Ends
Classifier: Typing :: Typed
Requires-Python: >=3.10
Requires-Dist: alembic>=1.19
Requires-Dist: postgast>=0.0.2
Requires-Dist: sqlalchemy>=2
Provides-Extra: docs
Requires-Dist: furo>=2024.8; extra == 'docs'
Requires-Dist: sphinx-autodoc2>=0.5; extra == 'docs'
Requires-Dist: sphinx-copybutton>=0.5; extra == 'docs'
Requires-Dist: sphinx-llms-txt>=0.7; extra == 'docs'
Requires-Dist: sphinx>=8; extra == 'docs'
Requires-Dist: sphinxext-opengraph>=0.9; extra == 'docs'
Description-Content-Type: text/markdown

# alembic-pg-autogen

[![PyPI](https://img.shields.io/pypi/v/alembic-pg-autogen)](https://pypi.org/project/alembic-pg-autogen/)
[![Python](https://img.shields.io/pypi/pyversions/alembic-pg-autogen)](https://pypi.org/project/alembic-pg-autogen/)
[![License: MIT](https://img.shields.io/badge/license-MIT-blue.svg)](LICENSE)
[![CI](https://img.shields.io/github/actions/workflow/status/eddieland/alembic-pg-autogen/ci.yml?label=CI)](https://github.com/eddieland/alembic-pg-autogen/actions/workflows/ci.yml)
[![Coverage](https://codecov.io/gh/eddieland/alembic-pg-autogen/graph/badge.svg)](https://codecov.io/gh/eddieland/alembic-pg-autogen)
[![Docs](https://readthedocs.org/projects/alembic-pg-autogen/badge/?version=latest)](https://alembic-pg-autogen.readthedocs.io)
[![Downloads](https://img.shields.io/pypi/dm/alembic-pg-autogen)](https://pypi.org/project/alembic-pg-autogen/)

> **Status: Beta.** The core pipeline is stable. The test suite runs against a real PostgreSQL server. The API may still
> change before version 1.0. You can use the library in production.

Alembic autogenerate extension for PostgreSQL functions and triggers. You declare your DDL strings, and
`alembic revision --autogenerate` writes the `CREATE`, `DROP`, and `CREATE OR REPLACE` statements.

<p align="center">
  <img src="https://raw.githubusercontent.com/eddieland/alembic-pg-autogen/main/docs/logo.png" width="350" alt="alembic-pg-autogen logo"/>
</p>

## Background

[alembic_utils](https://github.com/olirice/alembic_utils) was the first project to add autogenerate support for
PostgreSQL objects. Many projects use it today. This project takes a different approach. The goal is faster autogenerate
on large schemas that contain many functions and triggers.

## How it works

You declare each function and trigger as a plain DDL string. Autogenerate then runs four steps:

1. The extension reads the live database catalog.
1. The extension canonicalizes your DDL inside a temporary savepoint.
1. The extension compares the current state against the declared state.
1. The extension emits migration operations in dependency order.

## Quick example

```python
import alembic_pg_autogen  # noqa: F401  # registers the comparator plugin

PG_FUNCTIONS = [
    """
    CREATE OR REPLACE FUNCTION set_updated_at()
    RETURNS trigger LANGUAGE plpgsql AS $$
    BEGIN
        NEW.updated_at = now();
        RETURN NEW;
    END;
    $$
    """,
]

PG_TRIGGERS = [
    """
    CREATE TRIGGER set_updated_at_on_update
    BEFORE UPDATE ON my_table
    FOR EACH ROW EXECUTE FUNCTION set_updated_at()
    """,
]

# in run_migrations_online():
context.configure(
    connection=connection,
    target_metadata=target_metadata,
    autogenerate_plugins=["alembic.autogenerate.*", "alembic_pg_autogen.*"],
    pg_functions=PG_FUNCTIONS,
    pg_triggers=PG_TRIGGERS,
)
```

```bash
alembic revision --autogenerate -m "add audit function and trigger"
```

### Both wildcards are required

`autogenerate_plugins` *replaces* Alembic's default value of `["alembic.autogenerate.*"]`. The option does not extend
the default value. Your list must keep the built-in wildcard next to ours. This package only adds comparators. This
package never replaces Alembic's comparators, which still handle every table, column, index, and constraint.

A wrong list fails silently in two directions. Neither direction raises an error:

- **Omitting `alembic_pg_autogen.*`** keeps Alembic's default value in force. The same result follows when you never set
  the option. An import of the package registers the plugins but does not enable them. The extension then never compares
  functions, triggers, views, check constraint expressions, or index definitions.
- **Omitting `alembic.autogenerate.*`** makes every migration empty. This result covers Alembic's own features and this
  package's features. The comparator that drives the whole diff belongs to `alembic.autogenerate.schemas`. Alembic
  dispatches no comparator without it.

To confirm what a run loaded, set `logging.getLogger("alembic.runtime.plugins").setLevel(logging.INFO)`. Each included
plugin logs one `setting up autogenerate plugin ...` line. A correct configuration logs lines from **both** namespaces.
It logs several `alembic.autogenerate.*` entries, above all `schemas` and `tables`, which drive the diff. It also logs
`alembic_pg_autogen.compare`, `alembic_pg_autogen.checkconstraints`, and `alembic_pg_autogen.indexes`. Lines from one
namespace only mean that your list is missing the other wildcard.

## What gets managed

An object type becomes *managed* when you declare it. Inside a managed type, your declared set is the whole truth. The
extension drops each object that it finds in the inspected schemas and that you did not declare. This rule is what makes
the tool declarative.

A key that you never pass leaves that object type unmanaged. The extension inspects nothing, compares nothing, and emits
nothing for that type. The example above declares functions and triggers, so views stay unchanged. You can adopt the
library one object type at a time.

Pass the `IGNORED` sentinel to record the unmanaged object type in the configuration, or to set it conditionally. The
sentinel means exactly what an absent key means:

```python
from alembic_pg_autogen import IGNORED

context.configure(
    connection=connection,
    target_metadata=target_metadata,
    autogenerate_plugins=["alembic.autogenerate.*", "alembic_pg_autogen.*"],
    pg_functions=PG_FUNCTIONS,
    pg_triggers=PG_TRIGGERS,
    pg_views=IGNORED,  # views are not managed yet, so keep them
)
```

An empty list means something else. `pg_views=[]` declares that the schema contains no views, and the extension drops
every existing view. `pg_views=IGNORED` declares that this configuration does not manage views. Note the difference when
you build the list at runtime. The expression `collect_view_ddl() or IGNORED` keeps an empty result from clearing your
schema.

An unrecognized key also leaves its object type unmanaged. A misspelled key such as `pg_view` therefore manages nothing.
The extension reports each misspelled key as a warning that names the key you meant.

## Check constraints

Alembic detects a named `CHECK` constraint that you add to your models or remove from them. Alembic treats two
constraints with the same name as equal, because it cannot normalize SQL expressions across backends. A change from
`amount >= 0` to `amount > 0` therefore generates no migration, and the schema drifts.

This package closes that gap for PostgreSQL, and **the gap is all that it closes**. Our comparator augments Alembic's
comparator. Keep `alembic.autogenerate.checkconstraint_byname` enabled, so that it continues its own work. Alembic
1.19.2 renamed that plugin to `alembic.ext.checkconstraint_byname` and no longer enables it through the
`alembic.autogenerate.*` wildcard, so list it explicitly:

| Situation                                             | Who handles it                                | Result                                                                                                                      |
| ----------------------------------------------------- | --------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------- |
| Name in your models only                              | `alembic.autogenerate.checkconstraint_byname` | `create_check_constraint`                                                                                                   |
| Name in the database only                             | `alembic.autogenerate.checkconstraint_byname` | `drop_constraint`                                                                                                           |
| Name on **both** sides, expression possibly changed   | `alembic_pg_autogen.checkconstraints`         | `drop_constraint` and `create_check_constraint`                                                                             |
| Name on **both** sides, set of allowed values changed | `alembic_pg_autogen.checkconstraints`         | `drop_constraint` and `create_check_constraint(..., postgresql_not_valid=True)`, then `VALIDATE CONSTRAINT` on the next run |
| Name on **both** sides, database holds `NOT VALID`    | `alembic_pg_autogen.checkconstraints`         | `ALTER TABLE ... VALIDATE CONSTRAINT`                                                                                       |
| No name in your models, equal expression in database  | `alembic_pg_autogen.checkconstraints`         | Nothing. The comparator discards the `drop_constraint` that Alembic emits                                                   |
| No name in your models, no equal expression           | `alembic_pg_autogen.checkconstraints`         | `create_check_constraint` under the name that PostgreSQL chooses                                                            |

The two sets of names are disjoint by construction, so each named constraint gets its operations from one comparator.
For an unnamed constraint, our comparator discards the drop that Alembic emitted, and it avoids every name that the
schema or the same migration already holds. Since Alembic 1.19.2, Alembic's plugin also adds a missing named constraint
that a type generates, such as a non-native `Enum` constraint. Our comparator replaces that add with its own, so the
migration holds one add. You gain nothing when you disable Alembic's comparator. You lose the detection of added
constraints and removed constraints, and our comparator reports nothing in their place. On PostgreSQL, Alembic's own
expression check always reports equality: `DefaultImpl.compare_check_constraint` returns `Equal()`, and the PostgreSQL
dialect does not override that method. That result is the gap our comparator fills, and the reason the two comparators
never collide.

Remember one consequence. Your schema may drift only through constraints that you forgot to declare and constraints that
you no longer want. This package then reports nothing, and Alembic emits every operation in the migration. That outcome
is the intended division of work.

Declare constraints in SQLAlchemy metadata as usual:

```python
class Order(Base):
    __tablename__ = "orders"

    id: Mapped[int] = mapped_column(primary_key=True)
    amount: Mapped[Decimal]

    __table_args__ = (CheckConstraint("amount > 0", name="ck_orders_amount"),)
```

A changed expression now produces a migration:

```python
def upgrade() -> None:
    op.drop_constraint("ck_orders_amount", "orders", type_="check")
    op.create_check_constraint("ck_orders_amount", "orders", "amount > 0")
```

The comparator sends each expression through PostgreSQL. It adds the expression to the table as a throwaway `NOT VALID`
constraint. It works inside a savepoint, and it reverts that savepoint. PostgreSQL therefore reports `amount >= 0` and
the catalog form `amount >= 0::numeric` as one constraint. A real change still appears as a change.

### Unnamed constraints

PostgreSQL gives a name to every constraint. A constraint without a name in your models, such as
`CheckConstraint("amount > 0")`, therefore gets a name such as `orders_amount_check` in the database. Alembic's
`checkconstraint_byname` plugin matches by name only, so it reports that database constraint as removed on every run.

This package matches an unnamed model constraint by its expression instead. It sends the expression through PostgreSQL,
as for a named constraint, and pairs it with a database constraint whose expression is equal. For a pair, it discards
the `drop_constraint` that Alembic emitted. When the database holds the pair `NOT VALID`, it emits
`VALIDATE CONSTRAINT`, with the same rule as for a named constraint. You do not have to rename the constraints of a
schema that is already in production.

A database constraint that no model constraint matches keeps its `drop_constraint`. This includes a constraint that was
added in raw SQL and never added to the models. A model constraint that no database constraint matches gets an add. The
add uses the name that PostgreSQL chooses, because a migration cannot drop a constraint without a name on downgrade. The
name avoids every constraint name in the schema and the named constraints that the same migration adds:

```python
def upgrade() -> None:
    op.create_check_constraint("orders_amount_check1", "orders", "amount < 100")


def downgrade() -> None:
    op.drop_constraint("orders_amount_check1", "orders", type_="check")
```

An unnamed non-native `Enum` whose members change leaves one unmatched constraint on each side. When both restrict the
same column to a set of values, the comparator treats them as one changed constraint. It drops the database constraint
and adds the new one under the same name, with the two-revision flow of [Non-native enums](#non-native-enums) for a
removed member.

A changed unnamed constraint that is not a value set, such as `amount > 0` that becomes `amount > 1`, gives an add for
the new expression and an unmatched database constraint for the old one. Alembic's `checkconstraint_byname` plugin emits
the drop of the old one. Without that plugin, the old constraint stays in the database and PostgreSQL still enforces it.

An expression can occur more than once on a table. The comparator pairs as many copies as both sides have. It pairs
validated database copies first and then the others, each group in name order, so a duplicate that is dropped is the
weaker copy. It logs a warning when the counts differ. An unnamed constraint on a column that the same migration adds
gets its add on the next run, because PostgreSQL cannot test the expression before the column exists. The same holds for
an unnamed constraint declared on a column of a new table, because Alembic's `create_table` leaves column-level
constraints out. The comparator logs a warning in both cases. This rule does not apply to the constraint of a non-native
`Enum`, because the column's type creates that constraint. While a model constraint cannot be tested, the comparator
also keeps the database constraints that no model constraint matched, and it logs a warning. They stay until every
unnamed model constraint on the table can be tested. For a new column that is the next run. For an expression that
PostgreSQL rejects, they stay until you fix the expression. While they stay, each autogenerated migration holds a line
such as `pass  # Kept check constraints ...` that names them, and `alembic check` fails, so the drift cannot stay hidden
in CI.

The comparator tests each expression on an empty `TEMP` copy of the table. The copy takes an `ACCESS SHARE` lock on the
real table, so autogenerate does not block readers or writers. Some expressions cannot run on the copy, such as a call
to a function that takes the table's row type. The comparator tries such an expression once more on the real table, with
a lock wait of at most one second. The emitted migration keeps the expression text exactly, including a `%` and a `:` in
a string literal.

### Non-native enums

A native PostgreSQL enum resists expand and contract deployment. `ALTER TYPE ... DROP VALUE` does not exist, and a value
added inside a transaction is unusable until that transaction commits. SQLAlchemy offers a replacement.
`Enum(Status, native_enum=False, create_constraint=True)` stores the column as `varchar` and derives a named `CHECK`
constraint from the Python enum:

```python
class Status(enum.Enum):
    new = "new"
    done = "done"


class Order(Base):
    __tablename__ = "orders"

    id: Mapped[int] = mapped_column(primary_key=True)
    status: Mapped[Status] = mapped_column(
        Enum(Status, native_enum=False, create_constraint=True, name="ck_orders_status", length=16)
    )
```

This package compares that constraint like any other named constraint. Alembic's `checkconstraint_byname` plugin skips
every constraint that a type generates, and it therefore reports the database copy as removed. This package runs after
that plugin, discards that drop, and adds the constraint when the database lacks it. A changed set of members therefore
produces a migration here and nowhere else.

An added member is one migration. No existing row can violate a wider set, so the new constraint validates at once:

```python
def upgrade() -> None:
    op.drop_constraint("ck_orders_status", "orders", type_="check")
    op.create_check_constraint("ck_orders_status", "orders", "status IN ('new', 'done', 'shipped')")
```

A removed member spans two revisions, because rows that hold the removed value need a backfill first. The first revision
replaces the constraint as `NOT VALID`. PostgreSQL enforces the new constraint for every new row at once, and it holds
`ACCESS EXCLUSIVE` for one millisecond instead of a full table scan. The migration says which values went away:

```python
def upgrade() -> None:
    op.drop_constraint("ck_orders_status", "orders", type_="check")
    # ck_orders_status no longer allows status IN ('shipped'). Rows that hold a removed value fail validation.
    # Backfill those rows before the revision that validates ck_orders_status.
    op.create_check_constraint("ck_orders_status", "orders", "status IN ('new', 'done')", postgresql_not_valid=True)
```

The next `alembic revision --autogenerate` reads `convalidated` from the catalog and emits the validation. The scan runs
under `SHARE UPDATE EXCLUSIVE`, which blocks no reads and no writes:

```python
def upgrade() -> None:
    op.execute("ALTER TABLE orders VALIDATE CONSTRAINT ck_orders_status")
```

The validation revision fails with a check violation until every row holds an allowed value. Write the backfill
yourself. The package states the need and nothing more.

The downgrade of a validation revision does nothing, and it says why. PostgreSQL offers no statement that marks a
validated constraint as `NOT VALID` again. Every other check constraint change keeps the single validating statement
shown above, because only `col IN (...)` and `col = ANY (ARRAY[...])` classify as value sets.

Two settings change this behavior:

- `pg_check_constraint_validation="immediate"` in `context.configure()` keeps one validating statement for every change.
  The default is `"deferred"`.
- `postgresql_not_valid=True` on a `CheckConstraint` in your models keeps that constraint `NOT VALID`. The comparator
  then emits no validation for it.

## Skipping `drop_index` for dropped tables

Alembic emits `op.drop_index()` for each index of a table that the same migration drops. The
[Alembic cookbook](https://alembic.sqlalchemy.org/en/latest/cookbook.html#don-t-emit-drop-index-when-the-table-is-to-be-dropped-as-well)
hook that removes those calls ships here as an opt-in `Rewriter`:

```python
from alembic_pg_autogen import skip_drop_index_for_dropped_tables

context.configure(
    ...,
    process_revision_directives=skip_drop_index_for_dropped_tables,
)
```

Use `skip_drop_index_for_dropped_tables.chain(my_hook)` if you already pass a hook.

## Indexes

Alembic compares the columns, the expressions, uniqueness, and the `NULLS NOT DISTINCT` flag of an index. Alembic does
not compare the `WHERE` predicate of a partial index. It also does not compare the access method, the `INCLUDE` columns,
or the operator classes. If you change one of these parts in a model, autogenerate emits no operation. Autogenerate
emits no operation on later runs too.

We tested each row below against PostgreSQL 16 and Alembic 1.19:

| Change in your model                                   | Alembic alone | With this package             |
| ------------------------------------------------------ | ------------- | ----------------------------- |
| Add, remove, or change `postgresql_where`              | no operation  | `drop_index` + `create_index` |
| Change `postgresql_using` (`btree` to `gin` or `brin`) | no operation  | `drop_index` + `create_index` |
| Add or change `postgresql_ops` (operator classes)      | no operation  | `drop_index` + `create_index` |
| Add or change `postgresql_include`                     | no operation  | `drop_index` + `create_index` |
| Change an expression by a cast only                    | no operation  | `drop_index` + `create_index` |

The last row has a different cause. Alembic compares expressions as text. Before the comparison, Alembic removes casts,
quotes, and spaces from the text. Thus an index on `a` and an index on `a::int` give the same text.

This comparator adds to the Alembic comparator. It does not replace it. Alembic decides if an index exists. Alembic also
handles each index that its own comparison finds different. This comparator runs after Alembic. It examines only indexes
that exist on both sides and that have no Alembic operation. Thus one index never gets two `drop_index` and
`create_index` pairs.

Declare indexes in SQLAlchemy metadata as usual:

```python
class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str]
    deleted_at: Mapped[datetime | None]

    __table_args__ = (Index("ix_users_email", "email", postgresql_where=text("deleted_at IS NULL")),)
```

When you add the predicate, autogenerate emits this migration:

```python
def upgrade() -> None:
    op.drop_index(op.f("ix_users_email"), table_name="users")
    op.create_index("ix_users_email", "users", ["email"], unique=False, postgresql_where=sa.text("deleted_at IS NULL"))
```

The comparator creates each desired index in PostgreSQL and reads the definition back. PostgreSQL writes both
definitions in one canonical form. Thus `WHERE status IN ('a', 'b')` in your model and
`WHERE (status = ANY (ARRAY['a'::text, 'b'::text]))` in the catalog compare as equal.

The comparator creates the index on an empty `TEMP` copy of the table, not on the real table. `CREATE INDEX` builds the
index, and it has no `NOT VALID` option. On a large table, a build takes seconds and holds a lock. On an empty copy, a
build takes less than one millisecond. The comparator never changes your data.

### Build indexes concurrently

`CREATE INDEX` takes a lock that blocks writes to the table. `CREATE INDEX CONCURRENTLY` does not block writes.
PostgreSQL cannot run `CONCURRENTLY` inside a transaction, and Alembic runs each migration in a transaction. Set
`pg_index_concurrently` to get a migration that runs the index operations outside the transaction:

```python
context.configure(
    connection=connection,
    target_metadata=target_metadata,
    autogenerate_plugins=["alembic.autogenerate.*", "alembic_pg_autogen.*"],
    pg_index_concurrently=True,
)
```

```python
def upgrade() -> None:
    with op.get_context().autocommit_block():
        op.drop_index(op.f("ix_users_email"), table_name="users", postgresql_concurrently=True)
    with op.get_context().autocommit_block():
        op.create_index(
            "ix_users_email",
            "users",
            ["email"],
            unique=False,
            postgresql_where=sa.text("deleted_at IS NULL"),
            postgresql_concurrently=True,
        )
```

This option changes only the rendered migration. Autogenerate always creates an ordinary index on the empty copy. A
concurrent build can fail and leave an invalid index. This behavior comes from PostgreSQL, not from this package. Check
for invalid indexes when a migration runs without supervision.

The comparator skips indexes that implement a primary key, unique, or exclusion constraint. The constraint owns such an
index, and Alembic compares the constraint.

## Grants

Alembic does not compare privileges. A `GRANT` in a migration is invisible to `alembic check`, and a rebuilt or squashed
schema loses it without a warning. Declare table and schema grants as SQL in `pg_grants`:

```python
PG_GRANTS = [
    "GRANT SELECT, INSERT ON orders TO app_writer",
    "GRANT SELECT ON orders, order_totals TO app_reader WITH GRANT OPTION",
    "GRANT USAGE ON SCHEMA public TO app_reader",
]

context.configure(
    connection=connection,
    target_metadata=target_metadata,
    autogenerate_plugins=["alembic.autogenerate.*", "alembic_pg_autogen.*"],
    pg_grants=PG_GRANTS,
)
```

The extension compares each privilege of each role on each object. It emits `GRANT` for a missing privilege and `REVOKE`
for a privilege that you did not declare. A changed `WITH GRANT OPTION` gives `GRANT ... WITH GRANT OPTION` or
`REVOKE GRANT OPTION FOR`. `GRANT ALL` expands to the privileges of your server version. The migration holds one
statement for each object and role.

```python
def upgrade() -> None:
    op.execute("REVOKE DELETE ON TABLE public.orders FROM app_writer")
    op.create_table("order_totals", ...)
    op.execute("GRANT SELECT ON TABLE public.order_totals TO app_reader WITH GRANT OPTION")
```

Revokes come first and grants come last. Thus a grant on a new table follows its `create_table`.

These rules apply:

- `pg_grants` follows the rules of [What gets managed](#what-gets-managed). A declared list revokes every other grant on
  the tables, views, and schemas in the inspected schemas. This includes grants that a user added on an extension
  object. The privileges that `CREATE EXTENSION` gives stay.

- Autogenerate inspects the default schema only, unless you set `include_schemas=True`. The extension logs a warning for
  each declared grant in a schema that it does not inspect, and emits nothing for it.

- `include_name` and `include_object` apply to grants on tables, with type `"table"`, and to schemas, with type
  `"schema"`. The default schema reaches `include_name` as `None`, as in Alembic. Views skip these filters, because
  Alembic never sends a view through them. The Alembic version table is skipped. The extension logs a warning for each
  declared grant that a filter removes.

- The extension reads the declared statements without executing them. A grant can name a table that the same migration
  creates. A name without a schema finds an existing table through the `search_path`, as `GRANT` does.

- Each statement must be a `GRANT ... ON [TABLE]` or a `GRANT ... ON SCHEMA`. The extension rejects `REVOKE`,
  `ON ALL TABLES IN SCHEMA`, `GRANTED BY`, sequences, functions, and column privileges with a `ValueError`.

- A declared `GRANT ALL` renders as `GRANT ALL`, so a migration from a newer server runs on an older one. Privileges
  that you name one by one render as a list, even when the list holds every privilege, because `ALL` on a newer server
  holds more. After a server upgrade that adds a privilege, a declared `GRANT ALL` gives a `GRANT` for the new
  privilege.

- A revoke on a table or view that the same migration drops is implicit. The `DROP` removes the privileges, and
  `downgrade()` grants them again. The extension logs a warning when `pg_grants` still declares a grant on that object.

- The extension does not manage these privileges unless you declare them:

  - Initial privileges in `pg_init_privs`. For example, `initdb` gives `USAGE` on the `public` schema to `PUBLIC`.
  - Default privileges from `ALTER DEFAULT PRIVILEGES`, which each new table receives.
  - The `initdb` defaults of `PUBLIC` on the `public` schema: `USAGE`, and `CREATE` before PostgreSQL 15. This rule also
    covers a `public` schema that a script dropped and created again.

  A `pg_grants` list without these privileges does not revoke them. A declared one is compared as usual.

- The privileges of the owner of an object are implicit. The extension does not compare them.

## Squashing a migration chain

A test that needs a database at head replays every revision. With hundreds of revisions, this replay is slow. The
`alembic_pg_autogen.squash` module replaces a revision that you choose, and every ancestor of it, with one revision.

```bash
python -m alembic_pg_autogen.squash --config alembic.ini --target 3f9a1c2b7d4e \
    --server-url postgresql+psycopg://postgres@localhost/postgres
```

Choose as the target the newest revision that every deployed database has reached. The new revision keeps the ID of the
target, so these databases need no `stamp`. A database older than the target cannot upgrade after the squash.

The squash builds the schema in a scratch database with your old chain. The body of the new revision is the
`pg_dump --schema-only` output of that database. Before the squash changes a file, it builds the schema with both chains
and compares the two databases at the target and at the heads. The comparison covers the dump text with owners, the
column order, materialized view population, and the roles and settings of the server. At the target, it also covers row
counts and sequences. Any difference stops the squash.

The squash needs a `pg_dump` binary and a role that can create databases. Your `env.py` must read the `sqlalchemy.url`
option or use `config.attributes["connection"]`. A preflight checks this before the first migration runs. A schema-only
dump holds no rows, so a squash that loses rows stops. Pass the rows in a SQL file with `--extra-sql`, or accept the
loss with `--allow-data-loss`. The squash refuses a chain that creates roles or tablespaces. Read the
[squash guide](https://alembic-pg-autogen.readthedocs.io/en/latest/squash.html) before the first run.

## Installation

```bash
pip install alembic-pg-autogen
```

Requires Python 3.10+ and SQLAlchemy 2.x. Install a PostgreSQL driver yourself (`psycopg`, `psycopg2`, or `asyncpg`).
This package depends on [postgast](https://github.com/eddieland/postgast) for DDL parsing. postgast requires
`protobuf >= 5.27`.

## Documentation

The full documentation is at [alembic-pg-autogen.readthedocs.io](https://alembic-pg-autogen.readthedocs.io). It contains
a quick start guide, migration instructions for alembic_utils users, and the API reference.

## Development

```bash
make install     # Install dependencies (uses uv)
make lint        # Format (mdformat, codespell, ruff) then type-check (basedpyright)
make test        # Run full test suite (requires Docker for integration tests)
make test-unit   # Run unit tests only (no Docker needed)
```

## License

[MIT](LICENSE)
