What about if you could organize your database objects (e.g. tables and views) in namespaces according with its roles in the system?
In this article we will see the right way to handle with PostgreSQL schemas in Django and some little tips about Django models and Python.

Schema

Also known as a namespace, schema is a type of database object whose purpose is to be a hierarchical organization layer that is just below a database.
On PostgreSQL, “public” is the default schema, but you can create your own namespaces to organize others kind objects such as tables, views, functions and etc.

Database Objects Hierarchy

- Server
   |- PostgreSQL Instance (Port 5432 by default)
       |- Role (Users and Groups)
       |- Tablespace
       |- Database
           |- Trigger
           |- Extension
           |- Language
           |- Schema      
               |- Table
               |- View
               |- Materialized View
               |- Sequence
               |- Function
               |- Procedure

About our Lab

That’s a simple lab with Django in a virtual environment (with virtualenv) and PostgreSQL installed in localhost.

• Python 3.8
• Django 3.0
• PostgreSQL 12

Should work with many older versions 🙂

Codes

• > SQL (psql);
• $ shell (Linux, FreeBSD, Unix*);
• >>> Python shell.

Practice

• PostgreSQL

The database struture is the first thing that we will do.

• Creation of database user for the application;
• Database creation;
• Schema creation;
• Table creation

Let’s create our own example in psql build-in command line tool:

$ psql

Creation of user application:

CREATE ROLE user_test ENCRYPTED PASSWORD '123' LOGIN;

The database role was created with an encrypted password and login attribute (user).

Creation of database for tests:

> CREATE DATABASE db_test OWNER user_test;

The database is owned by “user_test”.

Connect to it as user “user_test”:

> \c db_test user_test

Inside the psql shell \c database username.

Creation of a schema:

> CREATE SCHEMA ns_hr;

The namespace for our example is ready!

Show all schemas that aren’t catalogs:

> SELECT
nspname AS namespace
FROM pg_catalog.pg_namespace
WHERE nspname !~ '(^pg_|information_schema)';

Output:

namespace 
-----------
 public
 ns_hr

Notice that appears the default namespace (public) and the ns_hr, created for our lab.

Creation of a table in ns_hr schema:

> CREATE TABLE ns_hr.tb_person(
    id_ serial primary key,
    name text not null,
    surname text not null
);

A simple table…

Hit <Ctrl> + D to exit.

• Django

It’s time to code in Python! 😀

• Virtual environment;
• Python modules installation;
• Django project creation and configuration;
• Django app creation;
• Django model creation;
• Migrations;
• Tests in shell;

Virtual environment creation:

$ virtualenv -p `which python3.8` django

The absolute path of binary of Python 3.8 was indicated as Python interpreter of this environment.

Access directory of environment and activate it:

$ cd django && source bin/activate

Your prompt changed, started at “(django)” indicating that your virtual environment was activated.

Install need modules for our tests:

$ pip install django psycopg2-binary configobj ipython

Respectivaly: Django web framework, PostgreSQL driver, configuration file reader and improved interactive shell.

New Django project creation:

$ django-admin startproject my_project

Rename the directory of project to src:

$ mv my_project src

This is for ease the directory hierarchy and will not affect the results. It’s because that has a same name directory that can cause some confusion…

Database configuration file creation:

$ cat << EOF > src/my_project/db.conf
DB_HOST = 'localhost'
DB_NAME = 'db_test'
DB_USER = 'user_test'
DB_PASSWORD = '123'
DB_PORT = 5432
EOF

Here we created a separated configuration file for the database connection.

Edit the main configuration file of the project:

$ vim src/my_project/settings.py

import os

from configobj import ConfigObj

Below the imports add a line that brings ConfigObj class.

# Database
# https://docs.djangoproject.com/en/2.2/ref/settings/#databases

# Database configuration file location
DB_CONF_FILE = f'{BASE_DIR}/my_project/db.conf'

# Read the configurations from file
DB_CONFIG = ConfigObj(DB_CONF_FILE)

# Database connection parameters

DB_HOST = DB_CONFIG['DB_HOST']
DB_NAME = DB_CONFIG['DB_NAME']
DB_USER = DB_CONFIG['DB_USER']
DB_PASSWORD = DB_CONFIG['DB_PASSWORD']
DB_PORT = DB_CONFIG['DB_PORT']

DATABASES = {
             'default': {
                         'ENGINE': 'django.db.backends.postgresql',
                         'NAME': DB_NAME,
                         'USER': DB_USER,
                         'PASSWORD': DB_PASSWORD,
                         'HOST': DB_HOST,
                         'PORT': DB_PORT,
                         }
            }

Modify the database “session” as above.

Symbolic link creation for manage.py:

$ ln -s `pwd`/src/manage.py `pwd`/bin/manage.py

To ease our work we created a symbolic link to manage.py in bin directory that is in our $PATH.

Run virtual web server:

$ manage.py runserver 0.0.0.0:8000

Test in your browser: http://localhost:8000 and then <Ctrl> + C to abort.

Access project directory:

$ cd src

Let’s check the files inside the current directory:

$ tree .

Output:

.
├── manage.py
└── my_project
    ├── db.conf
    ├── __init__.py
    ├── __pycache__
    │   ├── __init__.cpython-38.pyc
    │   ├── settings.cpython-38.pyc
    │   ├── urls.cpython-38.pyc
    │   └── wsgi.cpython-38.pyc
    ├── settings.py
    ├── urls.py
    └── wsgi.py

List contents of current directory in a tree-like format.
Here we see all files inside the project.

First migration for Django metadata:

$ manage.py migrate

Creation of super user of Django:

$ manage.py createsuperuser

Create an app:

$ manage.py startapp human_resource

Edit settings.py to add new app:

$ vim my_project/settings.py

# Application definition

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',

    # Custom Apps
    'human_resource',
]

A cool Django trick: you can use a directory models instead of a file models.py.
But you must create a dunder init file (__init__.py) inside the models directory.
Let’s go!

Creation of models directory inside the the app directory:

$ mkdir human_resource/models

Remove the models.py file:

$ rm -f human_resource/models.py

Model creation:

$ vim human_resource/models/hr.py

from django.db.models import AutoField
from django.db.models import Model
from django.db.models import TextField


class Person(Model):
    '''
    Person Model

    Namespace: ns_hr
    Table: tb_person
    '''

    id_ = AutoField(db_column='id_', name='id', primary_key=True,)
    name = TextField(db_column='name', name='name',)
    surname = TextField(db_column='surname', name='surname',)

    def __str__(self):
        return f'{self.name} {self.surname}'

    class Meta:
        db_table = 'ns_hr"."tb_person'  # 'schema"."object'
        verbose_name_plural = 'Person'

To enjoy the benefits of PostgreSQL schemas, inside your model, in Meta inner class, to the value of “db_table” attribute you must put a dot that separate namespace and object between quotation marks.

'schema"."object'

The object could be a table or a view, for example…

Dunder init inside the models directory fot the migrations take effect:

vim human_resource/models/__init__.py

from human_resource.models.hr import Person

This is necessary for the models directory works as models.py file.

(No) Migrations: My Database, My Rules!

We create the structure of our database and no ORM should do it for us!
We have the power!
We have the might!
We are in command!

Our database, our rules! 😉

Just model your database by your own hands and make a fake Django migration.
Because only we know how the database objects must be created 😉

Make migrations for human_resource app:

$ manage.py makemigrations human_resource

Fake migration:

$ manage.py migrate --fake

Let’s check the directory hierarchy of the app:

$ tree human_resource/

human_resource/
├── admin.py
├── apps.py
├── __init__.py
├── migrations
│   ├── 0001_initial.py
│   ├── __init__.py
│   └── __pycache__
│       ├── 0001_initial.cpython-38.pyc
│       └── __init__.cpython-38.pyc
├── models
│   ├── hr.py
│   ├── __init__.py
│   └── __pycache__
│       ├── hr.cpython-38.pyc
│       └── __init__.cpython-38.pyc
├── __pycache__
│   ├── admin.cpython-38.pyc
│   └── __init__.cpython-38.pyc
├── tests.py
└── views.py

Django Shell (Ipython):

$ manage.py shell

>>> from human_resource.models.hr import Person

>>> p = Person(name='Ludwig', surname='van Beethoven')                                                                         

>>> print(p)

Output:

Ludwig van Beethoven

>>> p.save()   # Persist in database

Hit <Ctrl> + D to exit!

Database shell (psql):

$ manage.py dbshell

A query to check if the data was inserted by Django:

>  SELECT id_, name, surname FROM ns_hr.tb_person;

Output:

 id |  name  |    surname    
----+--------+---------------
  1 | Ludwig | van Beethoven

Conclusion

PostgreSQL is a robust and powerful RDBMS with a lot of features, including namespaces for its objects.
Django is a great web framework that is very robust and has a lot of features as well.
So, you can extract the better of both to achieve better results and to do this, one of the ways is getting a better organization.
Organizing your database objects in namespaces according with its roles will bring benefits for you 😉