Proposal: First‑class Alembic support for Views (incl. Materialized) + Routines for ETL/PostGIS

Describe the use case
I’m running an ETL pipeline on PostgreSQL + PostGIS and use Alembic to manage schema migrations. Views and materialized views are core to the pipeline (spatial aggregation, routing, feature generation). I also use stored routines (functions + procedures) for ETL logic and validation. Today these objects are maintained manually with raw SQL in migrations, and Alembic autogeneration cannot detect changes to them, which creates drift risk and makes ordering fragile (views depend on tables/other views). I want Alembic to manage these objects alongside tables, including create/replace/drop and autogenerate support.

Databases / Backends / Drivers targeted
PostgreSQL (with PostGIS enabled). Drivers: psycopg2 (and compatible PostgreSQL drivers).

Example Use
Materialized view definition used in the project (raw SQL):

WITH ordered_stops AS (
    SELECT 
        ls.line_id,
        ls.variant_id,
        ls.direction_id,
        ls."order" as stop_order,
        p.ref_node as node_id,
        LEAD(p.ref_node) OVER (
            PARTITION BY ls.line_id, ls.variant_id, ls.direction_id 
            ORDER BY ls."order"
        ) as next_node_id
    FROM line_stop ls
    JOIN stop s ON ls.stop_id = s.id
    JOIN platform p ON s.number::text = p.sub_stop_id
    WHERE p.ref_node IS NOT NULL
),
route_segments AS (
    SELECT 
        os.line_id,
        os.variant_id,
        os.direction_id,
        os.stop_order,
        e.linestring as geom
    FROM ordered_stops os
    JOIN edge e ON e.from_node = os.node_id AND e.to_node = os.next_node_id
    WHERE os.next_node_id IS NOT NULL
      AND e.linestring IS NOT NULL
),
combined_routes AS (
    SELECT 
        line_id::text || '_' || COALESCE(variant_id::text, 'NULL') || '_' || COALESCE(direction_id::text, 'NULL') as route_id,
        line_id,
        variant_id,
        direction_id,
        ST_LineMerge(ST_Collect(geom ORDER BY rs.stop_order)) as geom,
        COUNT(*) as segment_count,
        SUM(ST_Length(geom)) as total_length
    FROM route_segments rs
    GROUP BY line_id, variant_id, direction_id
    HAVING COUNT(*) >= 1
)
SELECT 
    route_id,
    line_id,
    variant_id,
    direction_id,
    segment_count,
    total_length,
    geom
FROM combined_routes
WHERE geom IS NOT NULL
  AND ST_IsValid(geom)
  AND ST_GeometryType(geom) = 'ST_LineString';

I want to define this in Alembic (or SQLAlchemy‑style DDL metadata), have Alembic autogenerate detect changes, and use CREATE OR REPLACE semantics where possible.

Additional context
This is for an ETL system where views/materialized views are part of the core data model, not just reporting. Autogenerating support and correct dependency ordering are critical. I’m happy for the initial scope to be PostgreSQL‑only and to treat other dialects as unsupported.

Have a nice day!

No files, tests, or entry points are named. Start by reviewing Alembic’s PostgreSQL autogeneration and migration-operation extension points, then define the supported scope for views, materialized views, functions, and procedures. Done means PostgreSQL-only create, replace, drop, change detection, and dependency ordering are specified and covered by tests.