alembic generation of materialized view
<p>TL;DR: How do I get alembic to understand and generate SQL for materialized views created in sqlalchemy?</p>

<p>I'm using flask-sqlalchemy and also using alembic with postgres. To get a materialized view working with sqlalchemy, I followed a <a href="http://www.jeffwidman.com/blog/847/using-sqlalchemy-to-create-and-manage-postgresql-materialized-views/" rel="noreferrer">nice post</a> on the topic. I used it heavily, with just a few minor divergences (the article uses flask-sqlalchemy as well, however the complete code example uses sqlalchemy's declarative base directly instead).</p>

<pre><code>class ActivityView(db.Model):
    __table__ = create_materialized_view(
        'activity_view',
        db.select([
            Activity.id.label('id'),
            Activity.name.label('name'),
            Activity.start_date.label('start_date'),
        ]).where(
            db.and_(
                Activity.start_date != None,
                Activity.start_date &lt;=
                    datetime_to_str(datetime.now(tz=pytz.UTC) + timedelta(hours=48))
            )
        )
    )

    @classmethod
    def refresh(cls, concurrently=True):
        refresh_materialized_view(cls.__table__.fullname, concurrently)

db.Index('activity_view_index',
         ActivityView.__table__.c.id, ActivityView.__table__.c.start_date,
         unique=True)
</code></pre>

<p>The <code>create_materialized_view</code> and <code>refresh_materialized_view</code> methods are taken straight from the blog post.</p>

<p>Note that the example above has been greatly simplified and probably seems silly because of my simplifications, <strong><em>but the real idea I want to get at is how do I get alembic to translate this view to a series of alembic operations during a migration</em></strong>?</p>

<p>When I run tests, the code runs fine, the view gets generated fine and everything works. When alembic runs it doesn't do anything with the view. So what I end up doing is copying the SQL that the tests emit for the materialized view into the alembic migrations/versions file and just end up executing that directly as:</p>

<pre><code>op.execute(activities_view_sql)
</code></pre>

<p>Similarly, I do the same direct SQL execution when generating the unique index on the materialized view.</p>

<p>Unfortunately my approach is error prone and creates seemingly unnecessary code duplication.</p>

<p>Is there a way to get alembic to understand my <code>ActivityView</code> so that any time it changes, alembic will know how to update the view?</p>

<p>Thanks much!</p>