SQL Views for Postgres


Adds first-class support for PostgreSQL Views in the Django ORM

Installation

Install via pip:

pip install django-pgviews

Add to installed applications in settings.py:

INSTALLED_APPS = (
  # ...
  'django_pgviews',
)

Examples

from django.db import models

from django_pgviews import view as pg


class Customer(models.Model):
    name = models.CharField(max_length=100)
    post_code = models.CharField(max_length=20)
    is_preferred = models.BooleanField(default=False)

    class Meta:
        app_label = 'myapp'

class PreferredCustomer(pg.View):
    projection = ['myapp.Customer.*',]
    dependencies = ['myapp.OtherView',]
    sql = """SELECT * FROM myapp_customer WHERE is_preferred = TRUE;"""

    class Meta:
      app_label = 'myapp'
      db_table = 'myapp_preferredcustomer'
      managed = False

NOTE It is important that we include the managed = False in the Meta so Django 1.7 migrations don't attempt to create DB tables for this view.

The SQL produced by this might look like:

CREATE VIEW myapp_preferredcustomer AS
SELECT * FROM myapp_customer WHERE is_preferred = TRUE;

To create all your views, run python manage.py sync_pgviews

You can also specify field names, which will map onto fields in your View:

from django_pgviews import view as pg


VIEW_SQL = """
    SELECT name, post_code FROM myapp_customer WHERE is_preferred = TRUE
"""


class PreferredCustomer(pg.View):
    name = models.CharField(max_length=100)
    post_code = models.CharField(max_length=20)

    sql = VIEW_SQL

Features

Updating Views

Sometimes your models change and you need your Database Views to reflect the new data. Updating the View logic is as simple as modifying the underlying SQL and running:

python manage.py sync_pgviews --force

This will forcibly update any views that conflict with your new SQL.

Dependencies

You can specify other views you depend on. This ensures the other views are installed beforehand. Using dependencies also ensures that your views get refreshed correctly when using sync_pgviews --force.

Note: Views are synced after the Django application has migrated and adding models to the dependency list will cause syncing to fail.

Example:

from django_pgviews import view as pg

class PreferredCustomer(pg.View):
    dependencies = ['myapp.OtherView',]
    sql = """SELECT * FROM myapp_customer WHERE is_preferred = TRUE;"""

    class Meta:
      app_label = 'myapp'
      db_table = 'myapp_preferredcustomer'
      managed = False

Materialized Views

Postgres 9.3 and up supports materialized views which allow you to cache the results of views, potentially allowing them to load faster.

However, you do need to manually refresh the view. To do this automatically, you can attach signals and call the refresh function.

Example:

from django_pgviews import view as pg


VIEW_SQL = """
    SELECT name, post_code FROM myapp_customer WHERE is_preferred = TRUE
"""

class Customer(models.Model):
    name = models.CharField(max_length=100)
    post_code = models.CharField(max_length=20)
    is_preferred = models.BooleanField(default=True)


class PreferredCustomer(pg.MaterializedView):
    name = models.CharField(max_length=100)
    post_code = models.CharField(max_length=20)

    sql = VIEW_SQL


@receiver(post_save, sender=Customer)
def customer_saved(sender, action=None, instance=None, **kwargs):
    PreferredCustomer.refresh()

Custom Schema

You can define any table name you wish for your views. They can even live inside your own custom PostgreSQL schema.

from django_pgviews import view as pg


class PreferredCustomer(pg.View):
    sql = """SELECT * FROM myapp_customer WHERE is_preferred = TRUE;"""

    class Meta:
      db_table = 'my_custom_schema.preferredcustomer'
      managed = False

Django Compatibility

Django Version	Django-PGView Version
1.4 and down	Unsupported
1.5	0.0.1
1.6	0.0.3
1.7	0.0.4
1.9	0.1.0
1.10	0.2.0

Django 1.7 Note

Django 1.7 changed how models are loaded so that it's no longer possible to do sql = str(User.objects.all().query) because the dependent models aren't yet loaded by Django.

Django 1.9 Note

You now have to use the .view module directly.

Python 3 Support

Django PGViews supports Python 3 in versions 0.0.7 and above.