Proposal: First‑class Alembic support for Views (incl. Materialized) + Routines for ETL/PostGIS #1797
Description
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!