This works because SQLAlchemy doesn’t have to create a session in order to resolve what a

Within the selectable itself, it’s fine to use ORM constructs such as object references like select ( ) from SQLAlchemy Core avoids this issue because it doesn’t create a session. Sessionmaker ( ) which throws an exception because there’s no app context. So when SQLAlchemy tries to setup the table mappings, callingĭb. Sessionmaker ( ) requires an app context, but theĭb object (and table mappings) are processed by Python before the app is instantiated. query ( ) from the SQLAlchemy ORM won’t work because it creates a circular dependency. Sidenote: I’m using the factory pattern to create my Flask app, so I can only pass aĬreate_mat_view ( ). Trying to use the normalĭb. T.append_column(db.Column(c.name, c.type, primary_key=c.primary_key))ĭb.DDL('DROP MATERIALIZED VIEW IF EXISTS ' + name) T = db.Table(name, _mt) # the actual mat view class is bound to db.metadata _mt = db.MetaData() # temp metadata just for initial Table object creation Return 'CREATE MATERIALIZED VIEW %s AS %s' % (Ĭompiler.sql_compiler.process(lectable, literal_binds=True))ĭef create_mat_view(name, selectable, metadata=db.metadata): # Could use 'CREATE OR REPLACE MATERIALIZED VIEW.' lectable = compile(element, compiler, **kw): After several iterations (and help from Mike Beyer), here’s what I ended up with:įrom app import db # I'm using Flask-SQLAlchemyĬlass CreateMaterializedView(DDLElement): But it is possible to subclassĭDLElement and create your own custom DDL commands. SQLAlchemy doesn’t have a built-in command for creating views, let alone materialized views. Additionally, using a traditional SQLAlchemy table mapping makes it difficult to refresh the materialized view from within your app. I already manage all my database tables and metadata with SQLAlchemy, and wanted the convenience of fully managing my materialized views as well.Īdding custom DDL commands to SQLAlchemy for materialized views: While uncommon in production, it’s very common thing to do in a test suite or your local dev instance.

While this makes it possible to query the materialized view, it also means that you’ll hit problems when creating/dropping all your tables because SQLAlchemy sees the materialized view as a normal SQL table. Then, within your app, create a normal SQLAlchemy Table mapping that corresponds to the columns in your materialized view. Querying a materialized view is very similar to a normal SQL table, so the easiest way to do this is manually create the materialized view in your database. In order to write ORM queries against my materialized views, I needed to tell SQLAlchemy about the materialized view. Ways to teach SQLAlchemy about a materialized view: Overall, materialized views in PostgreSQL are a very convenient way to add caching to many applications. You can’t update a single row–only the entire view.You can also use a database trigger, although you’ll need to create a custom Either your app can update the materialized view after it inserts/updates the source data, or if that causes too many updates, just run a cron job that refreshes the materialized view on a regular schedule. Any data you want to store in a materialized view has be the result of another SQL query–you can’t generate the data in your app and then store it in the materialized view.Doesn’t add complexity to your infrastructure because it’s built into your database and requires no additional services.REFRESH MATERIALIZED VIEW CONCURRENTLY my_mat_view

Flask sqlalchemy postgres