Database Schema Updates with Django and SQLite

A quick tutorial.

Performing database schema migrations is a pain, even when using Django. There are a few projects that offer some degree of automated schema migration. Each has its limitations which can range from the databases it supports to the types of table alterations it is capable of performing:

• deseb
• django-evolution
• schema-evolution
• dmigrations

The alternative to using these sorts of projects is to create the database migration script manually, which can be a bit scary if databases aren't your forte. It is important to follow the same procedure for each database schema migration, and to test all scripts against a development database first as to minimize the risk of data permanent loss.

Originally, this blog didn't support published and non-published post statuses. The rest of this post is a summary of the steps necessary to fill that functional gap.

First, add new fields to blog Entry model:

date_published = models.DateTimeField(blank=True, null=True)
is_published = models.BooleanField()

Next, update the development database to support the new fields. Luckily, the syncdb command will still update our database with any new tables, but it won't modify any tables that already exist. Modifications to existing tables must be performed manually. To do this use the sqlall command in the root of the Django project to output all of the CREATE TABLE statements for the given app name(s):

$ python manage.py syncdb
$ python manage.py sqlall <django app name>

Next, isolate the statements that are a result of the modifications to the Entry model, and copy and paste them into a new text file. This new file is used to update the production database later:

-- Add new columns. Note that if a new column is NOT NULL it must have a DEFAULT value
BEGIN;
    ALTER TABLE blog_entry ADD COLUMN "date_published" datetime NULL;
    ALTER TABLE blog_entry ADD COLUMN "is_published" bool NOT NULL DEFAULT FALSE;

-- Update previous entries to be published
UPDATE blog_entry
SET date_published = date_created
WHERE date_published IS NULL;

UPDATE blog_entry
SET is_published = 1;

-- Output a subset of the blog_entry columns to verify everything worked
SELECT date_created, date_published, is_published FROM blog_entry;
COMMIT;

One note about the final COMMIT in the above code snippet: If further manual validation is needed before the migration is committed then leave out the final COMMIT and perform it manually before closing the SQLite session.

It is a good idea to commit database migration scripts to a 'database' folder that is at the same level as the Django project folder in the SVN. This way all iterations of the database schema are well documented and updates to the production server can easily be deployed.

Next, install SQLite on both the development and production servers. Windows users download the SQLite command line app from here. Linux users perform the following:

$ sudo aptitude install sqlite

Lastly, run the migration script on the production server.

$ sudo sqlite3 /<path to database>/<name of database>
sqlite> .read /<path to migration script>/<name of migration script>.sql
.
.
.
sqlite> .exit

In summary the basic steps to performing a schema migration are:

• Modify models
• Run python manage.py syncdb
• Run python manage.py sqlall command
• Isolate new SQL that was not migrated by syncdb
• Create a migration script
• Run migration script on development server and verify results
• Run migration on production server

Comments

Add your comment

No HTML. Line breaks and URLs will automatically be converted.