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 atomic() context-manager, e.g.

with my_db.atomic():
    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'),
)

Altering a field’s data-type:

# Change a VARCHAR(50) field to a TEXT field.
migrate(
    migrator.alter_column_type('person', 'email', TextField())
)

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'))

Adding or dropping table constraints:

# Add a CHECK() constraint to enforce the price cannot be negative.
migrate(migrator.add_constraint(
    'products',
    'price_check',
    Check('price >= 0')))
# Remove the price check constraint.
migrate(migrator.drop_constraint('products', 'price_check'))
# Add a UNIQUE constraint on the first and last names.
migrate(migrator.add_unique('person', 'first_name', 'last_name'))

Note

Postgres users may need to set the search-path when using a non-standard schema. This can be done as follows:

new_field = TextField(default='', null=False)
migrator = PostgresqlMigrator(db)
migrate(migrator.set_search_path('my_schema_name'),
        migrator.add_column('table', 'field_name', new_field))

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.

• alter_column_type(table, column, field[, cast=None])

  Parameters:	table (str) – Name of the table. column_name (str) – Name of the column to modify. field (Field) – Field instance representing new data type. cast – (postgres-only) specify a cast expression if the data-types are incompatible, e.g. column_name::int. Can be provided as either a string or a Cast instance.

  Alter the data-type of a column. This method should be used with care, as using incompatible types may not be well-supported by your database.

• 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)

  Parameters:	table (str) – Name of the table containing the index to be dropped. index_name (str) – Name of the index to be dropped.

• add_constraint(table, name, constraint)

  Parameters:	table (str) – Table to add constraint to. name (str) – Name used to identify the constraint. constraint – either a Check() constraint or for adding an arbitrary constraint use SQL.

• drop_constraint(table, name)

  Parameters:	table (str) – Table to drop constraint from. name (str) – Name of constraint to drop.

• add_unique(table, \column_names*)

  Parameters:	table (str) – Table to add constraint to. column_names (str) – One or more columns for UNIQUE constraint.

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.

SQLite has limited support for ALTER TABLE queries, so the following operations are currently not supported for SQLite:

• add_constraint
• drop_constraint
• add_unique

class MySQLMigrator(database)

Generate migrations for MySQL databases.