Making Changes to a Database Schema

When we introduced the syncdb command earlier, we noted that syncdb merely creates tables that don’t yet exist in your database — it does not sync changes in models or perform deletions of models. If you add or change a model’s field, or if you delete a model, you’ll need to make the change in your database manually. This section explains how to do that.

When dealing with schema changes, it’s important to keep a few things in mind about how Django’s database layer works:

• Django will complain loudly if a model contains a field that has not yet been created in the database table. This will cause an error the first time you use the Django database API to query the given table (i.e., it will happen at code execution time, not at compilation time).
• Django does not care if a database table contains columns that are not defined in the model.
• Django does not care if a database contains a table that is not represented by a model.

Making schema changes is a matter of changing the various pieces — the Python code and the database itself — in the right order.

Adding Fields – When adding a field to a table/model in a production setting, the trick is to take advantage of the fact that Django doesn’t care if a table contains columns that aren’t defined in the model. The strategy is to add the column in the database, and then update the Django model to include the new field.

However, there’s a bit of a chicken-and-egg problem here, because in order to know how the new database column should be expressed in SQL, you need to look at the output of Django’s manage.py sqlall command, which requires that the field exist in the model. (Note that you’re not required to create your column with exactly the same SQL that Django would, but it’s a good idea to do so, just to be sure everything’s in sync.)

The solution to the chicken-and-egg problem is to use a development environment instead of making the changes on a production server. (You are using a testing/development environment, right?) Here are the detailed steps to take.

First, take these steps in the development environment (i.e., not on the production server):

Add the field to your model.

• Run pysqlall[yourapp] to see the new CREATETABLE statement for the model. Note the column definition for the new field.
• Start your database’s interactive shell (e.g., psql or mysql, or you can use pydbshell). Execute an ALTERTABLE statement that adds your new column.
• (Optional.) Launch the Python interactive shell with pyshell and verify that the new field was added properly by importing the model and selecting from the table (e.g., MyModel.objects.all()[:5]).

Then on the production server perform these steps:

• Start your database’s interactive shell.
• Execute the ALTERTABLE statement you used in step 3 of the development environment steps.
• Add the field to your model. If you’re using source-code revision control and you checked in your change in development environment step 1, now is the time to update the code (e.g., svnupdate, with Subversion) on the production server.
• Restart the Web server for the code changes to take effect.

For example, let’s walk through what we’d do if we added a num_pages field to the Book model. First, we’d alter the model in our development environment to look like this:

class Book(models.Model):
title = models.CharField(maxlength=100)
authors = models.ManyToManyField(Author)
publisher = models.ForeignKey(Publisher)
publication_date = models.DateField()
num_pages = models.IntegerField(blank=True, null=True)
def __str__(self):
return self.title

(Note: Read the “Adding NOT NULL Columns” sidebar for important details on why we included blank=True and null=True.) Then we’d run the command manage.py sqlall books to see the CREATE TABLE statement. It would look something like this:

CREATE TABLE "books_book" (
"id" serial NOT NULL PRIMARY KEY,
"title" varchar(100) NOT NULL,
"publisher_id" integer NOT NULL REFERENCES "books_publisher" ("id"),
"publication_date" date NOT NULL,
"num_pages" integer NULL );

The new column is represented like this:

   "num_pages" integer NULL

Next, we’d start the database’s interactive shell for our development database by typing psql (for PostgreSQL), and we’d execute the following statements:

ALTER TABLE books_book ADD COLUMN num_pages integer;

Adding NOT NULL Columns – There’s a subtlety here that deserves mention. When we added the num_pages field to our model, we included the blank=True and null=True options. We did this because a database column will contain NULL values when you first create it.

However, it’s also possible to add columns that cannot contain NULL values. To do this, you have to create the column as NULL, then populate the column’s values using some default(s), and then alter the column to set the NOT NULL modifier. For example:

BEGIN;
ALTER TABLE books_book ADD COLUMN num_pages integer;
UPDATE books_book SET num_pages=0;
ALTER TABLE books_book ALTER COLUMN num_pages SET NOT NULL;
COMMIT;

After the ALTER TABLE statement, we’d verify that the change worked properly by starting the Python shell and running this code:

>>> from mysite.books.models import Book>>> Book.objects.all()[:5]

If that code didn’t cause errors, we’d switch to our production server and execute the ALTER TABLE statement on the production database. Then, we’d update the model in the production environment and restart the Web server.

Removing Fields – Removing a field from a model is a lot easier than adding one. To remove a field, just follow these steps:

• Remove the field from your model and restart the Web server.
• Remove the column from your database, using a command like this:

ALTER TABLE books_book DROP COLUMN num_pages;

Removing Many-to-Many Fields – Because many-to-many fields are different than normal fields, the removal process is different:

• Remove the ManyToManyField from your model and restart the Web server.
• Remove the many-to-many table from your database, using a command like this:

DROP TABLE books_books_publishers;

Removing Models – Removing a model entirely is as easy as removing a field. To remove a model, just follow these steps:

• Remove the model from your py file and restart the Web server.
• Remove the table from your database, using a command like this:

DROP TABLE books_book;6: The Django Administration Site

Back to Tutorial