Lets talk about Postgres…

Postgres queries are CaSeSensitivE unlike in MySQL. So when a simple Model.objects.get(field='hello') would get the record. In postgres you would need to use field__iexact='hello'

In fact,unique doesn’t work was different cases with standard postgres. That is why django has the CIText Mixin and postgres has the citext extension

Unfortunately this won’t help for a unique_together field. The case sensitivity will still come into play.

So you can make the field lowercase by overriding the model’s save method:


    def save(self, **kwargs):
        '''Override save to enforce project name is lowercase'''
        self.name = self.name.lower()
        super().save()

Nope you don’t need to do the above. Just ensure the field is a CICharField

Converting a Single Serving Django Application into Saas

I built a time tracking, leave application and billing application with django, called crowdminder. The product was created in partnership with a consulting company, they paid me and I developed it for them while retaining co-ownership.

I have seen that they have benefited from this simple application and know that many other consulting companies in South Africa could benefit too, so I want to make it into a Software-as-a-service product.

But where to start…

Dynamic Settings

Tenants should also be able to change settings for their instance. With django’s default settings file that would be very difficult and I thought I should take a look at django-constance to see if it may benefit my situation.

Django constance allows you to set some settings as dynamic so they will be retrieved from the database instead of the settings file. For example: TIME_ZONE, TIMECAPTURE_INTERVAL_MINUTES, BILLABLE_HOURS_IN_WORKING_DAY.

I have separated these settings out and it seems to be working quite well so far.

Multi-Tenancy

I realised that to make the product available to many others, it would need to be able to have multiple customers or tenants.

The choice of you multi-tenant strategy needs consideration for your individual case but in my case it made sense to use  django-tenant-schemas.

As it would minimise the change to tables (I would need a tenant linked to every record in the db if I went with a shared db). With the shared schemas approach though I would need to switch to postgres from mysql.

Setup

Importantly there are a few setup steps like explicitly telling schemas which apps are shared and which are tenant specific. Shared apps will create tables in the public schema. I have to admit that you will run into problems and it is better to use a rule of thumb keep things in tenant apps unless they definitely will be shared. An important thing to understand is that django.contrib.auth in both SHARED_APPS and TENANT APPS would allow users to be created for the public site and for each tenant.

Furthermore with django-tenant-schemas you should never use migrate as that will migrate everything into the current schema instead, migrate_schemas should be used. Migrate schemas can be run in parallel if a speed up is needed.

Then after migrating just the public schemas, we need to create a public tenant, whose domain should just be the base domain of the site. Then other tenants can be created.

Management commands that apply to tenants now need to inherit from BaseTenantCommand

Calling management commands will also need added care: eg. /manage.py tenant_command do_foo --schema=customer1

Thrid Party Apps

Celery also needs to be made to use tenants

Issues while working with postgres

django.db.utils.ProgrammingError: cannot cast type numeric to interval
LINE 1: …R COLUMN “duration” TYPE interval USING “duration”::interval

django.db.utils.OperationalError: cannot DROP TABLE “auth_group_permissions” because it has pending trigger events

It won’t even let you delete the database:

postgres-headache-wont-let-delete-database

Another gem from the django docs on postgres migrations:

PostgreSQL is the most capable of all the databases here in terms of schema support; the only caveat is that adding columns with default values will cause a full rewrite of the table, for a time proportional to its size.

For this reason, it’s recommended you always create new columns with null=True, as this way they will be added immediately.

When running tests the initial migrations panicked with:

django.db.utils.ProgrammingError: relation “users_user” does not exist

Testing Multitenancy

Testing the multitenant sites and public site was a bit tricky in some cases. To test the public schema website the PUBLIC_SCHEMA_URLCONF were not being used so I had to override the ROOT_URLCONF to point to the public. Also the public schema was not created automatically so I had to do that in setUp().

Another thing is that I sometimes use WebTest for my tests, as it is sometimes better than the standard Django TestCase. When working with tenant tests, there is a Class that can be used that does a lot of the tenant related stuff for you so converting those tests would look like this:


from django_webtest import WebTestMixin
from tenant_schemas.test.cases import TenantTestCase

class EntryUnitTests(WebTestMixin, TenantTestCase):
    ...
Yet More Issues

Another fucking issue, is that the migrations didn’t seem to be running before the tests. The tables were not created for the tenant with TenantTestCase.

The ContentType model needs to be used with care as the model field is saved in camelcase with MySQL but in lowercase with postgres.

So instead of:


leave_content_type = ContentType.objects.get(
app_label="leave", model="LeaveRequest"
)
you should use:

leave_content_type = ContentType.objects.get(
app_label__iexact="leave", model__iexact="LeaveRequest"
)

Damn you postgres:


Type 'yes' if you would like to try deleting the test database 'test_crowdminder', or 'no' to cancel: yes
Destroying old test database for alias 'default'...
Got an error recreating the test database: database "test_crowdminder" is being accessed by other users
DETAIL:  There is 1 other session using the database.

Ok, I just needed to kill the other test that was running after cancelling an ipdb session.

So it looks like django-webtest is not allowing transactions within tests:


    def do_request(self, req, status, expect_errors):
        # Django closes the database connection after every request;
        # this breaks the use of transactions in your tests.
        if close_old_connections is not None:  # Django 1.6+
            signals.request_started.disconnect(close_old_connections)
            signals.request_finished.disconnect(close_old_connections)
        else:  # Django < 1.6
            signals.request_finished.disconnect(close_connection)

        try:
req.environ.setdefault('REMOTE_ADDR', '127.0.0.1')

But that doesn’t make sense as the tests passed previously (with MySQL) and django-constance…it turns out if you fix the `HTTP_HOST` so the request happens properly (and doesn’t return a 400). If a 400 is returned the current transaction is aborted.

Site URL and Emails

Previously the app send emails from the `DEFAULT_FROM_EMAIL` and sometimes in the template I had to use a custom settings SITE_URL to set urls on emails and templates. Now I envision that each tenant will have a <my_company>.crowdminder.co.za domain, so these settings will need to be dynamic as well (but not editable by the admin user)

Protected Files

I have enabled a way to protect files based on permissions based on the id of the file and if the site is built with an isolated database or schema then there would be duplicated id’s and that would affect file retrieveal. So a tenant based folder would need to be created.

For this I may need to look at using the tenant storage API

Potentially Useful Packages when building a Saas

Apart from the packages mentioned above there may be some more that will make it easier to build a multi-tenant Saas platform with Django.

Django-ikari – Anchored sub-domains

Django-guardian – Per-object permissions

Django-billing – Plan based subscription controls

Django-pricing – Plan based subscription definitions

Django-merlin – multi-step django forms

Django-waffle – A feature flipper, controlling which users can see the features so there is not one massive release / conflict during production deployment.

Sources