Getting Django to use Postgresql schemas in the Arches geospatial project

Getting Django to use Postgresql schemas in the Arches geospatial project

Getting Django to use Postgresql schemas in the Arches geospatial projectWhen we began work on the Arches project (http://archesproject.org), we knew we were going to use Postgresql (http://www.postgresql.org/) because it’s awesome, and because we were developing the software in Python, we knew Django (https://www.djangoproject.com/) would give us a nice structured framework to work in. The only issue with django (a big one I think), is that it doesn’t natively allow use of Postgresql schemas which we really wanted to employ. So, with a some stack tracing, internet searches, some hacked django modules, and a little luck I was able to get Django to recognize our Postgresql schemas.

Here’s what I did….

First off you have to be willing to modify core Django modules. I could get away with these kind of hijinks because Arches comes complete with it’s own virtual environment (https://pypi.python.org/pypi/virtualenv) into which Django is installed. It effictively means that the Arches Django install doesn’t have to play nice with anyone else, it’s only there to serve Arches. If you have multiple Django applications within a single Django instance then this probably won’t work for you. Also I was willing to let management commands like syncdb break because of my changes; we make all our changes to the db directly and use the inspectdb command instead. Additionaly, it requires that you fix to a specific version of Django (1.6 in this case). If you can live within those parameters, then read on.

The first thing to do is add a ‘SCHEMAS’ key to the default DATABASES dictionary, like so:

DATABASES = {
    'default': {
        'ENGINE': 'django.contrib.gis.db.backends.postgis', 
        'NAME': 'arches',                  
        'USER': 'postgres',                
        'PASSWORD': 'postgis',                
        'HOST': 'localhost',                    
        'PORT': '5432',                    
	'SCHEMAS': 'public,data,app_metadata,ontology,concepts', 
    }
}

The value of the ‘SCHEMAS’ key will be used by the folowing 2 edits to the core django code to set the search_path in Postgresql.

The first change you need to make is to insert at line 119 of django/db/backends/postgresql_psycopg2/base.py this line of code:

	self.connection.cursor().execute("SET search_path TO " + settings_dict['SCHEMAS'])

If you want to be able to run inspectdb (not required), then at line 41 of django/core/management/commands/inspectdb.py insert this line of code:

	cursor.execute("SET search_path TO " + settings.DATABASES[DEFAULT_DB_ALIAS]['SCHEMAS'])

Finally, to guarentee that you can read and write successfully back to the tables in your schemas, you need to add the schema name to your db_table references in your models.py file like so:

    class Meta:
        db_table = u'schema"."table name'

After that, you will be able to read and write back to your schema enabled Postgresql database.

Related articles

FARL_Divider_Graphic-cropped
Deploying ArcGIS Portal and Your First Web Applications with the LGIM
Use your mobile phone to find the best fishing hotspots near you
Port of San Francisco Uses Enterprise GIS to Prepare for Sea Level Rise Caused by Climate Change