Schema Migrations

Peewee now supports schema migrations, with well-tested support for Postgresql, SQLite and MySQL. Unlike other schema migration tools, peewee’s migrations do not handle introspection and database “versioning”. Rather, peewee provides a number of helper functions for generating and running schema-altering statements. This engine provides the basis on which a more sophisticated tool could some day be built.

Migrations can be written as simple python scripts and executed from the command-line. Since the migrations only depend on your applications Database object, it should be easy to manage changing your model definitions and maintaining a set of migration scripts without introducing dependencies.

Example usage

Begin by importing the helpers from the migrate module:

from playhouse.migrate import *

Instantiate a migrator. The SchemaMigrator class is responsible for generating schema altering operations, which can then be run sequentially by the migrate() helper.

# Postgres example:
my_db = PostgresqlDatabase(...)
migrator = PostgresqlMigrator(my_db)
# SQLite example:
my_db = SqliteDatabase('my_database.db')
migrator = SqliteMigrator(my_db)

Use migrate() to execute one or more operations:

title_field = CharField(default='')
status_field = IntegerField(null=True)
migrate(
    migrator.add_column('some_table', 'title', title_field),
    migrator.add_column('some_table', 'status', status_field),
    migrator.drop_column('some_table', 'old_column'),
)

Warning

Migrations are not run inside a transaction. If you wish the migration to run in a transaction you will need to wrap the call to migrate in a transaction block, e.g.

with my_db.transaction():
    migrate(...)

Supported Operations

Add new field(s) to an existing model:

# Create your field instances. For non-null fields you must specify a
# default value.
pubdate_field = DateTimeField(null=True)
comment_field = TextField(default='')
# Run the migration, specifying the database table, field name and field.
migrate(
    migrator.add_column('comment_tbl', 'pub_date', pubdate_field),
    migrator.add_column('comment_tbl', 'comment', comment_field),
)

Renaming a field:

# Specify the table, original name of the column, and its new name.
migrate(
    migrator.rename_column('story', 'pub_date', 'publish_date'),
    migrator.rename_column('story', 'mod_date', 'modified_date'),
)

Dropping a field:

migrate(
    migrator.drop_column('story', 'some_old_field'),
)

Making a field nullable or not nullable:

# Note that when making a field not null that field must not have any
# NULL values present.
migrate(
    # Make `pub_date` allow NULL values.
    migrator.drop_not_null('story', 'pub_date'),
    # Prevent `modified_date` from containing NULL values.
    migrator.add_not_null('story', 'modified_date'),
)

Renaming a table:

migrate(
    migrator.rename_table('story', 'stories_tbl'),
)

Adding an index:

# Specify the table, column names, and whether the index should be
# UNIQUE or not.
migrate(
    # Create an index on the `pub_date` column.
    migrator.add_index('story', ('pub_date',), False),
    # Create a multi-column index on the `pub_date` and `status` fields.
    migrator.add_index('story', ('pub_date', 'status'), False),
    # Create a unique index on the category and title fields.
    migrator.add_index('story', ('category_id', 'title'), True),
)

Dropping an index:

# Specify the index name.
migrate(migrator.drop_index('story', 'story_pub_date_status'))

Migrations API

migrate(\operations*)

Execute one or more schema altering operations.

Usage:

migrate(
    migrator.add_column('some_table', 'new_column', CharField(default='')),
    migrator.create_index('some_table', ('new_column',)),
)

class SchemaMigrator(database)

The SchemaMigrator is responsible for generating schema-altering statements.

• add_column(table, column_name, field)

  Parameters:	table (str) – Name of the table to add column to. column_name (str) – Name of the new column. field (Field) – A Field instance.

  Add a new column to the provided table. The field provided will be used to generate the appropriate column definition.

  Note

  If the field is not nullable it must specify a default value.

  Note

  For non-null fields, the field will initially be added as a null field, then an UPDATE statement will be executed to populate the column with the default value. Finally, the column will be marked as not null.

• drop_column(table, column_name[, cascade=True])

  Parameters:	table (str) – Name of the table to drop column from. column_name (str) – Name of the column to drop. cascade (bool) – Whether the column should be dropped with CASCADE.

• rename_column(table, old_name, new_name)

  Parameters:	table (str) – Name of the table containing column to rename. old_name (str) – Current name of the column. new_name (str) – New name for the column.

• add_not_null(table, column)

  Parameters:	table (str) – Name of table containing column. column (str) – Name of the column to make not nullable.

• drop_not_null(table, column)

  Parameters:	table (str) – Name of table containing column. column (str) – Name of the column to make nullable.

• rename_table(old_name, new_name)

  Parameters:	old_name (str) – Current name of the table. new_name (str) – New name for the table.

• add_index(table, columns[, unique=False[, using=None]])

  Parameters:	table (str) – Name of table on which to create the index. columns (list) – List of columns which should be indexed. unique (bool) – Whether the new index should specify a unique constraint. using (str) – Index type (where supported), e.g. GiST or GIN.

• drop_index(table, index_name)

  :param str table Name of the table containing the index to be dropped. :param str index_name: Name of the index to be dropped.

class PostgresqlMigrator(database)

Generate migrations for Postgresql databases.

• set_search_path(schema_name)

  Parameters:	schema_name (str) – Schema to use.

  Set the search path (schema) for the subsequent operations.

class SqliteMigrator(database)

Generate migrations for SQLite databases.

class MySQLMigrator(database)

Generate migrations for MySQL databases.