Django + Postgres Views

(Comments)

Over the years, the Django ORM has become my go-to way to interact with a database from Python. It is an incredibly robust binding between the database and Python. As Postgres added advanced database features, the Django ORM kept up. An early example of this is Django's implementation of JSONB fields. The JSONB fields allowed you to save JSON as a field and interact with the JSON object's contents from the SQL query level. Django quickly supported this powerful feature. Awesome.

Another powerful feature added Postgres added is Views. Views are a simple database feature that allows you to create an alias for a common query. Suppose you were routinely running a SELECT statement on a table of transactions for the category outdoor_products:

SELECT * FROM tranactions_table WHERE product_type='outdoor'

In Postgres, you can save this query as a view for future reference.

CREATE OR REPLACE VIEW 'outdoor_product_transactions_view' AS 
(SELECT * FROM tranactions_table WHERE product_type='outdoor')
Then you can just
SELECT * FROM outdoor_product_transactions_view
And easily apply additional filters
SELECT * FROM outdoor_product_transactions_view WHERE amount > 1000

This feature is great for complex queries you are regularly repeating.

In the application I am currently building, there are complex queries that I run regularly on our API. I would also like our BI tools to benefit from these complex queries without repeating the logic in SQL. So I set out to create an easy way for a Django queryset to manage a Postgres view.

Here is the result:

create_or_replace_view.py

from django.db import connection


def create_or_replace_view(view_name, view_qs):
    with connection.cursor() as cursor:
        queryset = view_qs
        compiler = queryset.query.get_compiler(using=queryset.db)
        sql, params = compiler.as_sql()
        sql = "CREATE OR REPLACE VIEW {view} AS {sql}".format(
            view=connection.ops.quote_name(view_name), sql=sql
        )
        cursor.execute(sql, params)

migrate_views.py

from postgresviews.create_or_replace_view import create_or_replace_view
from ledger.querysets.formattedTransactionsQS import FormattedTransactionsQS


## Define Views Here
## NOTE: Renamed or Deleted Views will not automatically clean up the old version.
create_or_replace_view(
    "view_formatted_transactions", FormattedTransactionsQS().get_qs()
)

migrate_views.py is run on every deployment of our Django API. This allows me to use complex querysets with our API via Django Rest Framework and build a Postgres view of the queryset available to our BI tools. Magic! I hope you find this useful in your own applications!

Currently unrated

Comments