Database views in Django

December 13, 2023 Django, Python

This is part of a series of posts I’m doing as a sort of Python/Django Advent calendar, offering a small tip or piece of information each day from the first Sunday of Advent through Christmas Eve. See the first post for an introduction.

A view to a database

Most databases support creating and working with views, which, if you’ve never encountered them before, are like a virtual table — instead of being defined by the CREATE TABLE statement, they’re created by the CREATE VIEW statement, and instead of defining their own set of columns they’re defined as a query against one or more other tables. You can query against a view the way you’d query against a table, and your queries will be applied on top of the base query that backs the view.

For example, a few years ago I gave a DjangoCon tutorial on the Django ORM, and gave an example of several models — Widget, Factory, and Production — and aggregation queries to track things like production statistics of widgets per factory, etc. I showed how to do this both by writing raw SQL and by using the ORM’s annotation and aggregation features.

But you could also do that aggregate query as a view. Here’s what the syntax would look like on PostgreSQL for the set of tables I was using as an example:

CREATE VIEW IF NOT EXISTS widgets_stats AS
  SELECT
    widgets_factory.city AS city,
    SUM(widgets_production.quantity) AS total
  FROM
    FROM widgets_production
    INNER JOIN widgets_factory
    ON (
      widgets_production.factory_id = widgets_factory.id
    )
  GROUP BY city;

Now do it in Django

A Django database migration consists of a Migration subclass with two important attributes:

dependencies: a list of identifiers of other migrations this one depends on.
operations: a list of migration operations to perform.

There are a bunch of migration operations, but the one we want here is the RunSQL operation. It takes one or optionally two strings of SQL (plus parameters, if needed) to run. If you supply only one, that’s the “upgrade” SQL and the migration can’t be run in reverse, and if you supply two the second one is the “downgrade” SQL and the migration can be reversed.

So first off, you could have Django’s migration system create the above view for you by writing a migration with a RunSQL operation, like so:

from django.db import migrations


CREATE_SQL = """
CREATE VIEW IF NOT EXISTS widgets_stats AS
  SELECT
    widgets_factory.city AS city,
    SUM(widgets_production.quantity) AS total
  FROM
    FROM widgets_production
    INNER JOIN widgets_factory
    ON (
      widgets_production.factory_id = widgets_factory.id
    )
  GROUP BY city;
"""

DROP_SQL = "DROP VIEW IF EXISTS widgets_stats;"


class Migration(migrations.Migration):
    dependencies = [
        # list the migrations that create the widget/factory/production tables
    ]

    operations = [
        migrations.RunSQL(
            sql=CREATE_SQL,
            reverse_sql=DROP_SQL,
        ),
    ]

But how to query it? You could use Django’s raw SQL support, but you can also wrap a database model around this view, defining fields that match the columns it returns. And then you can tell Django two more things:

You can explicitly specify which “table” (in this case, not a table, but it still works) to use for the model, and
You can tell Django’s migration system not to try to create or maintain real tables for this model.

Which looks like this:

from django.db import models


class WidgetStats(models.Model):
    city = models.CharField(
        max_length=255,
        primary_key=True
    )
    total = models.IntegerField()


    class Meta:
        db_table = "widgets_stats"
        managed = False

The two Meta attributes do what we need here: db_table tells Django which “table” to use, and managed = False tells the migration system not to try to auto-manage it.

And now you’d be able to query against the WidgetStats model the same way you’d query any other model. Just be careful about trying to write to it — whether a view is writeable or not varies according to the database you’re using, the query that created the view, and how you’re trying to update it (see, for example, Postgres’ explanation of when views support updating).