title: Custom migrations for PostgreSQL Materialized View with SQLAlchemy
body: <p>I'm trying to auto-generate migrations for materialized views in SQLAlchemy, and the only thing that's not working is detecting changes between revisions. More specifically, it <em>always</em> generates a change, even when the model hasn't changed.</p>
  
  <p>I think the problem is, and I could just be using the wrong approach here, is the difference between the SQL generated by <code>schema.compile(compile_kwargs={'literal_binds': True})</code> and the SQL returned by <code>inspector.get_view_definition(name)</code>.</p>
  
  <p>Backing up, here's what the <strong>model definition</strong> looks like, just a simple adjacency list tree. The goal of using the materialized view is the automatically generate a node's depth and path in such a way that it can be queried against:</p>
  
  <pre><code>from app.extensions import db  # Flask-SQLAlchemy
  
  @db.slugify('name')
  class Node(db.Model):
      id = db.Column(db.BigInteger, primary_key=True)
      name = db.Column(db.String, index=True)
      slug = db.Column(db.String, index=True)
  
      parent_id = db.foreign_key('Node', nullable=True)
      parent = db.relationship('Node', back_populates='children', remote_side=id)
      children = db.relationship('Node', back_populates='parent')
  
      mv = db.relationship('NodeMV', uselist=False, foreign_keys='NodeMV.id',
                           primaryjoin='Node.id == NodeMV.id')
      depth = db.association_proxy('mv', 'depth')
      path = db.association_proxy('mv', 'path')
  
  _cte = (db.select([Node.id.label('id'),
                     literal(0).label('depth'),
                     literal('/').label('path')])
          .where(Node.parent_id is None)
          .cte(name='nodes_cte', recursive=True))
  _union = _cte.union_all(
      db.select([
          Node.id.label('id'),
          label('depth', _cte.c.depth + 1),
          label('path', case([(_cte.c.depth == 0, _cte.c.path + Node.slug)],
                             else_=_cte.c.path + '/' + Node.slug))])
      .select_from(db.join(_cte, Node, _cte.c.id == Node.parent_id))
  )
  
  class NodeMV(db.Model):
      __tablename__ = 'node_mv'
      __table__ = create_materialized_view('node_mv', db.select([_union]))
  
  db.Index('ix_node_mv_id', NodeMV.id, unique=True)
  </code></pre>
  
  <p>Where the <strong><code>create_materialized_view</code> factory method</strong> looks like this:</p>
  
  <pre><code>def create_materialized_view(name, selectable, metadata=db.metadata):
      # must use a temporary metadata here so that SQLAlchemy doesn't detect the
      # table as "standalone". (it will still use the correct metadata once
      # attached to the __table__ attribute of the declarative base model)
      table = db.Table(name, db.MetaData())
      for col in selectable.c:
          table.append_column(
              db.Column(col.name, col.type, primary_key=col.primary_key))
  
      if not any([col.primary_key for col in selectable.c]):
          table.append_constraint(
              db.PrimaryKeyConstraint(*[col.name for col in selectable.c]))
  
      # to support using db.drop_all()
      db.event.listen(metadata, 'before_drop',
                      db.DDL(f'DROP MATERIALIZED VIEW IF EXISTS {name}'))
  
      # to support auto-generated migrations
      metadata.info.setdefault('materialized_views', set()).add(
          (name, selectable))
      metadata.info.setdefault('materialized_tables', {})[name] = table
  
      return table
  </code></pre>
  
  <p>And the <strong>migrations comparator</strong> that detects changes:</p>
  
  <pre><code>class ReplaceableSql:
      def __init__(self, name, create_sql, drop_sql, indexes, **kwargs):
          self.name = name
          self.create_sql = create_sql
          self.drop_sql = drop_sql
          self.indexes = indexes
          self.kwargs = kwargs
  
      def __eq__(self, other):
          if not isinstance(other, ReplaceableSql):
              return False
          return self.__hash__() == other.__hash__()
  
      def __hash__(self):
          key = (self.name, re.sub(r'\s+', ' ', self.create_sql), self.indexes)
          return hash(key)
  
  def create_replaceable_sql(name, schema, metadata):
      query = (schema if isinstance(schema, str)
                      else schema.compile(compile_kwargs={'literal_binds': True}))
      indexes = [(index.name, [c.name for c in index.columns], index.unique)
                 for index in metadata.info['materialized_tables'][name].indexes]
      return ReplaceableSql(name,
                            f'CREATE MATERIALIZED VIEW {name} AS {query}',
                            f'DROP MATERIALIZED VIEW IF EXISTS {name}',
                            indexes)
  
  @comparators.dispatch_for('schema')
  def compare_views(autogen_context, upgrade_ops, schemas):
      inspector = autogen_context.inspector
      metadata = autogen_context.metadata
      prev_revision = autogen_context.migration_context.get_current_revision()
  
      # existing views
      views = set()
      view_names = set()
      for schema in schemas:
          for name in inspector.get_view_names(include='materialized', schema=schema):
              views.add(create_replaceable_sql(
                  name, inspector.get_view_definition(name, schema=schema), metadata))
              view_names.add(name)
  
      # current metadata views
      metadata_views = set()
      metadata_view_names = set()
      for name, selectable in metadata.info.setdefault('materialized_views', set()):
          metadata_views.add(create_replaceable_sql(name, selectable, metadata))
          metadata_view_names.add(name)
  
      creates = metadata_views.difference(views)
      drops = views.difference(metadata_views)
      upgrades = view_names.intersection(metadata_view_names)
  
      for replaceable_sql in creates:
          name = replaceable_sql.name
          if name in upgrades:
              replaceable_sql.kwargs['prev'] = f'{prev_revision}.{name}'
          upgrade_ops.ops.append(CreateOp(replaceable_sql))
  
      for replaceable_sql in drops:
          if replaceable_sql.name in upgrades:
              continue
          upgrade_ops.ops.append(DropOp(replaceable_sql))
  </code></pre>
  
  <p>The differences in inspected vs generated SQL are minor (ignoring whitespace), but they're enough to throw off a string comparison:</p>
  
  <p><strong>inspector.get_view_definition(table_name)</strong>:</p>
  
  <pre><code>WITH RECURSIVE nodes_cte(id, depth, path) AS (
      SELECT
        node.id,
        0 AS depth,
        '/'::text AS path
      FROM node
      WHERE false
    UNION ALL
      SELECT
        node.id,
        (nodes_cte_1.depth + 1) AS depth,
        CASE
          WHEN (nodes_cte_1.depth = 0) THEN (nodes_cte_1.path || (node.slug)::text)
          ELSE ((nodes_cte_1.path || '/'::text) || (node.slug)::text)
        END AS path
      FROM (nodes_cte nodes_cte_1
      JOIN node ON ((nodes_cte_1.id = node.parent_id)))
  )
  SELECT nodes_cte.id, nodes_cte.depth, nodes_cte.path FROM nodes_cte;
  </code></pre>
  
  <p><strong>schema.compile(compile_kwargs={'literal_binds': True})</strong>:</p>
  
  <pre><code>WITH RECURSIVE nodes_cte(id, depth, path) AS (
      SELECT
        node.id AS id,
        0 AS depth,
        '/' AS path
      FROM node
      WHERE false
    UNION ALL
      SELECT
        node.id AS id,
        nodes_cte.depth + 1 AS depth,
        CASE
          WHEN (nodes_cte.depth = 0) THEN nodes_cte.path || node.slug
          ELSE nodes_cte.path || '/' || node.slug
        END AS path
      FROM nodes_cte
      JOIN node ON nodes_cte.id = node.parent_id
  )
  SELECT nodes_cte.id, nodes_cte.depth, nodes_cte.path FROM nodes_cte
  </code></pre>
  
  <p>So I suppose the question is, what's the correct approach I should be taking here? (It's also possible that using a materialized view is the wrong way to solve my problem - I did try using the <code>@hybrid_property</code>/<code>@prop_name.expression</code> stuff but I couldn't figure out how to make it work with the recursive nature of the data.)</p>
  
  <p>Thanks in advance!</p>