How to Use Materialized View in Django

Django is one of the most popular Python Web framework. It is designed based on the “model-template-view” pattern. In Django, the Model is acting as the interface to the data (Database). As each model is associated with each physical table in database, I start wondering if it’s possible to make ORM deal with more advanced database feature, that is view and materialized view. The result turns out to be more than great!

View and materialized view are results of lookup SQL query. The main difference between them is that materialized view stores result as an actual table while view does not. From technical perspective, view and materialized view are good for repetitive query. They enhance query speed. From business perspective, they can be used to declare data scope for the usages of different teams/departments. The question is how we leverage this feature in Django?

Here are conclusions of my research — ORM works well with materialized view but view. It makes sense, does it? Because materialized view is a physical table. ORM should be able to associate it. Regarding view, ORM has a similar mechanism called “manager” on which I will write another article to walk you through in the future. Let’s continue our tutorial on materialized view.

Dependencies

In this tutorial, I am going to use PostgreSQL database. Definitely choose whatever relational database you would like to use. In order to let Python “talk” to PostgreSQL database, we need to pip install psycopg2-binary. In addition, you may need to set up a local PostgreSQL database on your own machine so that models in your Django project can connect.

Download and install PostgreSQL engine as below. Follow the steps. You should be able to create a local database.

Regarding database client, I would like to use DBeaver community version. There are many options. It’s up to you.

In models.py in polls app, declare a Question model as below.

Run migration commands as below. Await first command making impact, before we run second command.

Let’s populate some data into table.

Run command below that should bring us to a Django console.

Copy and paste following codes in console. Hit Enter. It should insert 1000 rows. Now we have enough data to test!

We should see it under Materialized Views category as below.

In models.py in polls app, create a model Question2021 as below.

Add lookup query by selecting all data on Question2021 model and print number of returns in console. Codes should look like below.

In this article, we’ve created materialized view in database and associate ORM model with it. It’s quite straightforward, is it? For architecture perspective, hope it gives you a better idea how to manage your data queries engine. Thanks for reading. Stay tuned.

If you are interested, I’ve uploaded this project here.

I made video on it. Please see the below. Thanks.

Related posts:

This project was created for the 2021 Design for Change challenge, based on the following challenge statement: How might we create social transformation that shifts users towards greener behaviour…

In April of this year, President Joe Biden made the difficult decision to end America’s war in Afghanistan after nearly 20 years of failure. Even if you disagree with Biden’s decision to withdraw so…

I was in the bath recently — it was Sylvia Plath who said “There must be quite a few things a hot bath won’t cure, but I don’t know many of them” (though given that she eventually put her head in an…