Making database schema changes for Django

As I found out in my previous post it is not possible to modify your database schema via Django. This means once you have created the schema for your models, the only thing you can do is add more models using:

python manage.py syncdb

If you make a change to a model you are on your own.

When I defined my first model for a node (as in a node in a hierarchy) the model was something like this:

class Node(models.Model):
    title = models.CharField(max_length=100)
    sort_order = models.IntegerField(default=1)
    menu = models.ForeignKey('Menu')
    parent = models.ForeignKey('self')
    is_visible = models.BooleanField(default=True)

    def __unicode__(self):
        return self.title

The problem with this is that this model does not allow for a root node, since the parent column is a foreign key on itself, but the root node must by definition not have a parent (that is parent = NULL in the database). So I changed the model to this:

class Node(models.Model):
    title = models.CharField(max_length=100)
    sort_order = models.IntegerField(default=1)
    menu = models.ForeignKey('Menu')
    parent = models.ForeignKey('self', blank=True, null=True)
    is_visible = models.BooleanField(default=True)

    def __unicode__(self):
        return self.title

This allows the parent column to be null and is fine for the model, but now I need to make the change to the schema manually in sqlite.

So first I run this script to see what table schema the model is expecting:

python manage.py sql cwcms

Which brings back this:

CREATE TABLE "cwcms_node" (
    "id" integer NOT NULL PRIMARY KEY,
    "title" varchar(100) NOT NULL,
    "sort_order" integer NOT NULL,
    "menu_id" integer NOT NULL REFERENCES "cwcms_menu" ("id"),
    "parent_id" integer NULL,
    "is_visible" bool NOT NULL
)
;

I then need to log in to sqlite and make the change to the schema to reflect this. This is easily done with the manage.py script:

python manage.py dbshell

Next, because sqlite only supports basic ALTER TABLE syntax (ie you can’t change column definitions) you essentially need to create your new table schema using a different name. Then copy the contents from the old table into the new one, delete the old table and then rename the new table to the name of the old table (ie the one that Django is expecting). The series of statements would look something like this:

CREATE TABLE "cwcms_node_new" (
    "id" integer NOT NULL PRIMARY KEY,
    "title" varchar(100) NOT NULL,
    "sort_order" integer NOT NULL,
    "menu_id" integer NOT NULL REFERENCES "cwcms_menu" ("id"),
    "parent_id" integer NULL,
    "is_visible" bool NOT NULL
);

INSERT INTO cwcms_node_new SELECT * FROM cwcms_node;

DROP TABLE cwcms_node;

ALTER TABLE cwcms_node_new RENAME TO cwcms_node;

Another way of doing this if you’re still early on in development is to simply get the commands needed to clear the database using manage.py:

python manage.py sqlclear cwcms

And run them on the your sqlite database and use syncdb to recreate your whole database. In either case it is probably worth creating scripts to populate your database with some dummy default data for testing purposes.

And now, after around 6 hours work from the initial install of Django I have a fully functioning admin interface that allows me to create any number of menus with any kind of hierarchy.