☰

Django

Introduction

This package is in the form of Django middleware whose purpose is to augment a SQL statement right before execution, with information about the controller and user code to help with later making database optimization decisions, after those statements are examined from the database server’s logs.

The middleware uses Django’s connection.execute_wrapper.

Requirements

The middleware uses Django’s connection.execute_wrapper and therefore requires Django 2.0 or later (which support various versions of Python 3).

To record OpenCensus information opencensus-ext-django, version 0.7 or greater, is required.

Installation

This middleware can be installed by any of the following: 

pip3 install google-cloud-sqlcommenter
git clone https://github.com/google/sqlcommenter.git
cd python/sqlcommenter-python && python3 setup.py install

Enabling it

Please edit your settings.py file to include google.cloud.sqlcommenter.django.middleware.SqlCommenter in your MIDDLEWARE section like this:

--- settings.py
+++ settings.py
@@ -1,3 +1,4 @@
 MIDDLEWARE = [
+  'google.cloud.sqlcommenter.django.middleware.SqlCommenter',
   ...
 ]

Fields

In the database server logs, the comment’s fields are:

Sample log entry

After making a request into the middleware-enabled polls web-app.

2019-05-28 11:54:50.780 PDT [64128] LOG:  statement: INSERT INTO "polls_question"
("question_text", "pub_date") VALUES
('Wassup?', '2019-05-28T18:54:50.767481+00:00'::timestamptz) RETURNING "polls_question"."id"
/*controller='index',framework='django%3A2.2.1',route='%5Epolls/%24'*/

Expected Fields

Field Included by default? Description
app_name ❌ The application namespace of the matching URL pattern in your urls.py
controller ✔ The name of the matching URL pattern as described in your urls.py
db_driver ❌ The name of the Django database engine
framework ✔ The word “django” and the version of Django being used
route ✔ The route of the matching URL pattern as described in your urls.py
traceparent ❌ The W3C TraceContext.Traceparent field of the OpenCensus trace
tracestate ❌ The W3C TraceContext.Tracestate field of the OpenCensus trace

End to end examples

Examples are based off the polls app from the Django introduction tutorial.

Source code

# settings.py

MIDDLEWARE = [
    'sqlcommenter.django.middleware.SqlCommenter',
    ...
]
# polls/urls.py

from django.urls import path
from . import views

urlpatterns = [
    path('', views.index, name='index'),
]
# polls/views.py

from django.http import HttpResponse
from .models import Question

def index(request):
    count = Question.objects.count()
    return HttpResponse(f"There are {count} questions in the DB.\n")
# settings.py
INSTALLED_APPS = [
    'opencensus.ext.django',
    ...
]


MIDDLEWARE = [
    'opencensus.ext.django.middleware.OpencensusMiddleware',
    'sqlcommenter.django.middleware.SqlCommenter',
    ...
]

OPENCENSUS = {
    'TRACE': {
        'SAMPLER': 'opencensus.trace.samplers.AlwaysOnSampler()',
    }
}

SQLCOMMENTER_WITH_CONTROLLER = False
SQLCOMMENTER_WITH_FRAMEWORK = False
SQLCOMMENTER_WITH_ROUTE = False
SQLCOMMENTER_WITH_OPENCENSUS = True
# polls/urls.py

from django.urls import path
from . import views

urlpatterns = [
    path('', views.index, name='index'),
]
# polls/views.py

from django.http import HttpResponse
from .models import Question

def index(request):
    count = Question.objects.count()
    return HttpResponse(f"There are {count} questions in the DB.\n")
# settings.py

MIDDLEWARE = [
    'sqlcommenter.django.middleware.SqlCommenter',
    ...
]

SQLCOMMENTER_WITH_CONTROLLER = False
SQLCOMMENTER_WITH_FRAMEWORK = False
SQLCOMMENTER_WITH_ROUTE = False
SQLCOMMENTER_WITH_APP_NAME = True
# polls/urls.py

from django.urls import path
from . import apps, views

app_name = apps.PollsConfig.name

urlpatterns = [
    path('', views.index, name='index'),
]
# polls/views.py

from django.http import HttpResponse
from .models import Question

def index(request):
    count = Question.objects.count()
    return HttpResponse(f"There are {count} questions in the DB.\n")
# settings.py

MIDDLEWARE = [
    'sqlcommenter.django.middleware.SqlCommenter',
    ...
]

SQLCOMMENTER_WITH_CONTROLLER = False
SQLCOMMENTER_WITH_FRAMEWORK = False
SQLCOMMENTER_WITH_ROUTE = False
SQLCOMMENTER_WITH_DB_DRIVER = True
# polls/urls.py

from django.urls import path
from . import views

urlpatterns = [
    path('', views.index, name='index'),
]
# polls/views.py

from django.http import HttpResponse
from .models import Question

def index(request):
    count = Question.objects.count()
    return HttpResponse(f"There are {count} questions in the DB.\n")

From the command line, we run the django development server in one terminal:

# python manage.py runserver

And we use curl to make an HTTP request in another:

# curl http://127.0.0.1:8000/polls/

Results

Examining our Postgresql server logs, with the various options

2019-07-19 14:27:51.370 -03 [41382] LOG:  statement: SELECT COUNT(*) AS "__count" FROM "polls_question"
/*controller='index',framework='django%3A2.2.3',route='polls/'*/
2019-07-19 17:39:27.430 -03 [46170] LOG:  statement: SELECT COUNT(*) AS "__count" FROM "polls_question"
/*traceparent='00-fd720cffceba94bbf75940ff3caaf3cc-4fd1a2bdacf56388-01'*/
2019-07-19 15:31:33.681 -03 [42962] LOG:  statement: SELECT COUNT(*) AS "__count" FROM "polls_question"
/*app_name='polls'*/
2019-07-19 14:47:53.066 -03 [41602] LOG:  statement: SELECT COUNT(*) AS "__count" FROM "polls_question"
/*db_driver='django.db.backends.postgresql'*/

References

Resource URL
Django https://www.djangoproject.com/
OpenCensus https://opencensus.io/
opencensus-ext-django https://github.com/census-instrumentation/opencensus-python/tree/master/contrib/opencensus-ext-django
sqlcommenter on PyPi https://pypi.org/project/google-cloud-sqlcommenter
sqlcommenter on Github https://github.com/google/sqlcommenter