Multitenancy post code examples

multiple_db_querying.py

spinners = (
   Spinner.objects
          .using(request.customer.name)
          .annotate(
              avg_duration=Avg('owned_spinners__spins__duration'))
          .order_by('-avg_duration'))

multiple_db_settings.py

DATABASES = {
   'default': {
       'ENGINE': ...,
       'NAME': ...,
   },
   'ibm': {
       'ENGINE': ...,
       'NAME': ...,
   }
}

multiple_db_tl_middleware.py

def multidb_middleware(get_response):
   def middleware(request):
       subdomain = get_subdomain(request)
       customer = get_customer(subdomain)
       request.customer = customer

       @thread_local(using_db=customer.name)
       def execute_request(request):
           return get_response(request)

       response = execute_request(request)
       return response
   return middleware

multiple_db_tl_querying.py

spinners = (
   Spinner.objects
          .annotate(
            avg_duration=Avg('owned_spinners__spins__duration'))
          .order_by('-avg_duration'))

multiple_db_tl_router.py

class TenantRouter(object):
   def db_for_read(self, model, **hints):
       return get_thread_local('using_db', 'default')

   def db_for_write(self, model, **hints):
       return get_thread_local('using_db', 'default')
   # …

multiple_db_tl_settings.py

DATABASE_ROUTERS = ['multitenancy.routers.TenantRouter']

schemas_create.sql

CREATE SCHEMA ibm;

schemas_db_backend.py

           
           ...
           try:
               cursor_for_search_path.execute(
                   'SET search_path = {0}'.format(','.join(search_paths)))
           except (django.db.utils.DatabaseError, psycopg2.InternalError):
               self.search_path_set = False
           else:
               self.search_path_set = True

           if name:
               cursor_for_search_path.close()
           ...

schemas_middleware.py

       ...
       connection.set_schema_to_public()

       hostname = self.hostname_from_request(request)
       TenantModel = get_tenant_model()

       try:
           tenant = self.get_tenant(TenantModel, hostname, request)
           assert isinstance(tenant, TenantModel)
       except TenantModel.DoesNotExist:
           # ...

       request.tenant = tenant
       connection.set_tenant(request.tenant)
       ...

schemas_search_path.sql

SET search_path TO ibm;

SELECT id, name FROM user
      WHERE user.name LIKE 'F%';

schemas_select.sql

SELECT id, name FROM ibm.user
      WHERE ibm.user.name LIKE 'F%'

schemas_union.sql

SELECT id, duration FROM ibm.spinner_spin
      WHERE duration > 120
UNION
SELECT id, duration FROM vinta.spinner_spin
      WHERE duration > 120;

schemas_uuid.sql

SELECT uuid, duration FROM ibm.spinner_spin
      WHERE duration > 120
UNION
SELECT uuid, duration FROM vinta.spinner_spin
      WHERE duration > 120;

shared_middleware.py

def tenant_middleware(get_response):
    def middleware(request):
        # we are going to use subdomains to identify customers.
        
        # the first step is to extract the identifier from the url
        host = request.get_host() # here is the full url, something like this: 'https://ibm.spinnertracking.com'
        host = host.split(':')[1]  # we remove the protocol part: 'ibm.spinnertracking.com'
        subdomain = host.split('.')[0]  # and now we get the subdomain:'ibm'
        
        # now is just a matter of using the subdomain to find the 
        # corresponding Customer in our database
        try:
            customer = Customer.objects.get(name=subdomain)
        except Customer.DoesNotExist:
            customer = None
        # and it to the request
        request.tenant = customer
        
        # all done, the view will receive a request with a tenant attribute
        return get_response(request)

   return middleware

shared_querying.py

def my_view(request):
    avg_duration = (
       Spin.objects
           .filter(user_spinner__user__customer=request.customer)
           .aggregate(avg=Avg('duration')))['avg']
    return render(request, 'show_average.html', 
                  {'avg_duration': avg_duration})

shared_querying_simpler.py

avg_duration = (
   Spin.objects
       .filter(customer=request.customer)
       .aggregate(avg=Avg('duration')))['avg']

shared_settings.py

MIDDLEWARE_CLASSES = [
    'my_project.middlewares.tenant_middleware',
    ...
]