Making changes to the schema

The final topic we will discuss in this chapter is how to make modifications to an existing Model definition. From the project specification, we know we would like to be able to save drafts of our blog entries. Right now we don't have any way to tell whether an entry is a draft or not, so we will need to add a column that lets us store the status of our entry. Unfortunately, while db.create_all() works perfectly for creating tables, it will not automatically modify an existing table; to do this we need to use migrations.

Adding Flask-Migrate to our project

We will use Flask-Migrate to help us automatically update our database whenever we change the schema. In the blog virtualenv, install Flask-Migrate using pip:

(blog) $ pip install flask-migrate

Note

The author of SQLAlchemy has a project called alembic; Flask-Migrate makes use of this and integrates it with Flask directly, making things easier.

Next we will add a Migrate helper to our app. We will also create a script manager for our app. The script manager allows us to execute special commands within the context of our app, directly from the command-line. We will be using the script manager to execute the migrate command. Open app.py and make the following additions:

from flask import Flask
from flask.ext.migrate import Migrate, MigrateCommand
from flask.ext.script import Manager
from flask.ext.sqlalchemy import SQLAlchemy

from config import Configuration

app = Flask(__name__)
app.config.from_object(Configuration)
db = SQLAlchemy(app)
migrate = Migrate(app, db)

manager = Manager(app)
manager.add_command('db', MigrateCommand)

In order to use the manager, we will add a new file named manage.py along with app.py. Add the following code to manage.py:

from app import manager
from main import *

if __name__ == '__main__':
    manager.run()

This looks very similar to main.py, the key difference being, instead of calling app.run(), we are calling manager.run().

Note

Django has a similar, although auto-generated, manage.py file that serves a similar function.

Creating the initial migration

Before we can start changing our schema, we need to create a record of its current state. To do this, run the following commands from inside your blog's app directory. The first command will create a migrations directory inside the app folder that will track the changes we make to our schema. The second command db migrate will create a snapshot of our current schema so that future changes can be compared to it.

(blog) $ python manage.py db init

 Creating directory /home/charles/projects/blog/app/migrations ... done
 ...
(blog) $ python manage.py db migrate
INFO [alembic.migration] Context impl SQLiteImpl.
INFO [alembic.migration] Will assume non-transactional DDL.
 Generating /home/charles/projects/blog/app/migrations/versions/535133f91f00_.py ... done

Finally, we will run db upgrade to run the migration that will indicate to the migration system that everything is up-to-date:

(blog) $ python manage.py db upgrade
INFO [alembic.migration] Context impl SQLiteImpl.
INFO [alembic.migration] Will assume non-transactional DDL.
INFO [alembic.migration] Running upgrade None -> 535133f91f00, empty message

Adding a status column

Now that we have a snapshot of our current schema, we can start making changes. We will be adding a new column, named status, that will store an integer value corresponding to a particular status. Although there are only two statuses at the moment (PUBLIC and DRAFT), using an integer instead of a Boolean gives us the option to easily add more statuses in the future. Open models.py and make the following additions to the Entry model:

class Entry(db.Model):
 STATUS_PUBLIC = 0
 STATUS_DRAFT = 1

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100))
    slug = db.Column(db.String(100), unique=True)
    body = db.Column(db.Text)
 status = db.Column(db.SmallInteger, default=STATUS_PUBLIC)
    created_timestamp = db.Column(db.DateTime, default=datetime.datetime.now)
    ...

From the command-line, we will once again be running db migrate to generate the migration script. You can see from the command's output that it found our new column!

(blog) $ python manage.py db migrate
INFO [alembic.migration] Context impl SQLiteImpl.
INFO [alembic.migration] Will assume non-transactional DDL.
INFO [alembic.autogenerate.compare] Detected added column 'entry.status'
 Generating /home/charl
es/projects/blog/app/migrations/versions/2c8e81936cad_.py ... done

Because we have blog entries in the database, we need to make a small modification to the auto-generated migration to ensure the statuses for the existing entries are initialized to the proper value. To do this, open up the migration file (mine is migrations/versions/2c8e81936cad_.py) and change the following line:

op.add_column('entry', sa.Column('status', sa.SmallInteger(), nullable=True))

Replacing nullable=True with server_default='0' tells the migration script to not set the column to null by default, but instead to use 0.

op.add_column('entry', sa.Column('status', sa.SmallInteger(), server_default='0'))

Finally, run db upgrade to run the migration and create the status column.

(blog) $ python manage.py db upgrade
INFO [alembic.migration] Context impl SQLiteImpl.
INFO [alembic.migration] Will assume non-transactional DDL.
INFO [alembic.migration] Running upgrade 535133f91f00 -> 2c8e81936cad, empty message

Congratulations, your Entry model now has a status field!