The Django script manage.py can be used to create our new table.  We pointed out in the previous newsletter that the table created will differ between databases, and this newsletter will consider some of this variation and what we can learn from it.

To make it easy for me to create and test these examples on different types of database, I have Oracle XE and PostgreSQL set up on my computer.  I created a new user (markm) in Oracle, and a new user (markm) and a new database (DjangoModelling) in PostgreSQL.  Users need enough authority to create new tables, views and similar, so that Django can do its work of modifying the database to match model changes.  To try to make the examples in this series clearer, they will all show a user ‘markm’ having a password ‘markm’ rather than using some other replacement text.

My setup is more complex than a simple single-database example would be, but Django’s design and documentation reduce and clarify the complexities, making it all achievable.

Database connections and ‘migrations’

Django allows connections to multiple databases at the same time, so having created a new project (using django-admin.py startproject <projname>), I added database configuration to <projname>\<projname>\settings.py.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    },
    'oracleDB': {
        'ENGINE': 'django.db.backends.oracle',
        'NAME': 'xe',
        'USER': 'markm',
        'PASSWORD': 'markm',
    },
    'postgresqlDB': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'DjangoModelling',
        'USER': 'markm',
        'PASSWORD': 'markm',
    }
}


A database router class is also required for each database other than the default, so that Django knows when to write to each database.  My separation of this example into one app for each database type was partly to make this easier.  The router classes must also be specified in the settings.py file.

# database 'default' (SQLite) handles everything (including the pots app),
# excluding our duplicated pots apps for Oracle (pots_ora)
# and PostgreSQL (pots_pos)
DATABASE_ROUTERS = ['NewsletterEg.database_routers.OracleRouter',
                   'NewsletterEg.database_routers.PostgreSQLRouter']

With the database router complete, I finished the creation of three apps: “pots” to use with SQLite, “pots_ora” for Oracle and “pots_pos” for PostgreSQL (using manage.py startapp <appname>).

python manage.py startapp pots
python manage.py startapp pots_ora
python manage.py startapp pots_pos


These three pots apps must be added to INSTALLED_APPS in settings.py. The name of the configuration class for each app is found in the <appdir>\apps.py file.

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'pots.apps.PotsConfig',
    'pots_ora.apps.PotsOraConfig',
    'pots_pos.apps.PotsPosConfig',
]

This all sounds like quite a lot of work, and, quite frankly, it is.  Frameworks can be easy to use, but the initial setup will often take a while, particularly when one is just starting.  It is easy to miss a step and spend frustrating hours trying to find out why it isn’t working.

The final steps are to use the “makemigrations” and “migrate” commands with manage.py to assess what database changes are necessary and to apply those changes.  “Migration” is the name used by Django to describe the changes needed in a database to reflect changes in the models of the project.


Create the tables required by the Django contrib apps:

python manage.py migrate

Use Django to create our “POTS” table in each database. First we work out what changes are necessary

python manage.py makemigrations pots
python manage.py makemigrations pots_ora
python manage.py makemigrations pots_pos

and then we apply those changes

python manage.py migrate pots 0001
python manage.py migrate –database oracleDB pots_ora 0001
python manage.py migrate –database postgresqlDB pots_pos 0001

This creates a table named <app_name>_pot in each database. The table creation commands for each database are shown below. Note that some other commands and code that are used to create the sequences and triggers have been left out.

SQLite

CREATE TABLE “pots_pos_pot” (“id” serial NOT NULL PRIMARY KEY, “TYPE” varchar(20) NOT NULL, “SIZE” varchar(20) NOT NULL, “AVAILABLE” boolean NOT NULL, “DIRTY” boolean NOT NULL);

Oracle

CREATE TABLE “POTS_ORA_POT” (“ID” NUMBER(11) NOT NULL PRIMARY KEY, “TYPE” NVARCHAR2(20) NULL, “SIZE” NVARCHAR2(20) NULL, “AVAILABLE” NUMBER(1) NOT NULL CHECK (“AVAILABLE” IN (0,1)), “DIRTY” NUMBER(1) NOT NULL CHECK (“DIRTY” IN (0,1)));

PostgreSQL

CREATE TABLE “pots_pos_pot” (“id” serial NOT NULL PRIMARY KEY, “TYPE” varchar(20) NOT NULL, “SIZE” varchar(20) NOT NULL, “AVAILABLE” boolean NOT NULL, “DIRTY” boolean NOT NULL);

Comparison of column types

Once the tables have been created, we can review the data types used by Django to create the table in each database.  These are shown in the following table.

Column SQLite type Oracle type PostgreSQL type
id/ID Serial, not null, primary key Number(11), not null, primary key Serial, not null, primary key
TYPE Varchar(20), not null Nvarchar2(20) Varchar(20), not null
SIZE Varchar(20), not null Nvarchar2(20) Varchar(20), not null
AVAILABLE Boolean, not null Number(1), not null Boolean, not null
DIRTY Boolean, not null Number(1), not null Boolean, not null

Three important differences stand out:

1. SQLite and PostgreSQL use “serial”, a defined type that makes it easy to create automatically incremented primary key columns.  In Oracle, the table uses a standard integer type, with a sequence for generating the key values and a trigger to make sure the key field is populated.  Inside the database, the results are the same, but Oracle requires the details to be provided by the user, while the other databases wrap all that complexity into a simple type.  Never mind, whichever database you use, Django does the necessary work to make it easy and hide the differences.

2. The TYPE and SIZE columns are not nullable in SQLite and PostgreSQL, while in Oracle this constraint is missing.  The reason for this is the difference in the way of handling empty strings.  Oracle considers an empty string to be the same as null, while both SQLite and PostgreSQL allow empty strings to be stored and do not consider them to be null values.  Never mind, whichever database you use, Django does the necessary work to make it easy and hide the differences.  However, there may still be a conceptual difference between NULL and an empty string.

3. Oracle does not have a boolean data type.  If you wish to store boolean data, it is up to you to add the check constraints to enforce it.  This limitation has been a source of questions and frustration for many Oracle users over many years (see https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6263249199595).  Since this datatype is not available in Oracle, users have come up with many different ways of emulating the type.  Some have used a single character field with values T/F or Y/N, however this has some obvious problems: what about different languages? and what about variation in case (T/t, F/f, Y/y, N/n)?  Django chooses (wisely) to use a number to emulate a boolean type and adds a constraint which allows only 0 or 1 to be used in the field.  However, a Django user doesn’t need to worry about this quirk in Oracle because Django does the necessary work to make it easy and hide the differences.

What do we learn?

This simple example highlights the differences that can result from using different databases.  However, it also shows that Django does its best to provide a toolbox filled with tools that will have a consistent interface and provide consistent results whatever the backend database may be.  This can save us a lot of work.

Our original article discussing the POTS table raised various modelling questions for some of the columns used including a questions of dead lizards.  These are worth considering as they apply to our Django implementation also.