djangosql-view

How to programatically create a database view in Django?


I have following query that I ran to create a database view inside my SQLite database:

CREATE VIEW customerview AS
SELECT
    a.id
  , name
  , email
  , vat
  , street
  , number
  , postal
  , city
  , country
  , geo_lat
  , geo_lon
  , customer_id
  , is_primary
FROM customerbin_address a
  , customerbin_customer b
WHERE b.id = a.customer_id
  AND a.is_primary = 1

In models.py I added the model:

class Customerview(models.Model):
    name = models.CharField(max_length=100, db_column='name')
    email = models.EmailField(unique=True, db_column='email')
    vat = VATNumberField(countries=['NL', 'BE', 'FR', 'DE', 'UK'], blank=True, null=True, db_column='vat')
    street = models.CharField(max_length=100, db_column='street')
    number = models.IntegerField(null=True, db_column='number')
    postal = models.IntegerField(null=True, db_column='postal')
    city = models.CharField(max_length=100, db_column='city')
    country = CountryField(db_column='country')
    is_primary = models.BooleanField(null=False, db_column='is_primary')
    geo_lat = models.DecimalField(max_digits=9, decimal_places=6, blank=True, null=True, db_column='geo_lat')
    geo_lon = models.DecimalField(max_digits=9, decimal_places=6, blank=True, null=True, db_column='geo_lon')

    class Meta:
            managed = False
            db_table = 'customerview'

and in admin.py I altered the list:

@admin.register(models.Customerview)
class CustomerviewAdmin(admin.ModelAdmin):
    list_display = ('name', 'email', 'vat', 'street', 'number', 'postal', 'city', 'country', 'is_primary', 'geo_lat', 'geo_lon')
    readonly_fields = ('name', 'email', 'vat', 'street', 'number', 'postal', 'city', 'country', 'is_primary', 'geo_lat', 'geo_lon',)

How do I programatically add the database view with the query above in my application?


Solution

  • Django's migrations framework lets you execute raw SQL - https://docs.djangoproject.com/en/3.1/ref/migration-operations/#runsql

    So, you could create an empty migration (manage.py makemigrations <appname> --empty) and then edit it to execute your view-creating SQL via a migrations.RunSQL() call.