Schema Migrations

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

Migrations can be written as simple python scripts and executed from thecommand-line. Since the migrations only depend on your applicationsDatabase object, it should be easy to manage changing your modeldefinitions and maintaining a set of migration scripts without introducingdependencies.

Example usage

Begin by importing the helpers from the migrate module:

from playhouse.migrate import *

Instantiate a migrator. The SchemaMigrator class is responsiblefor generating schema altering operations, which can then be run sequentiallyby 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 torun in a transaction you will need to wrap the call to migrate in aatomic() 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'))

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)

Parameters:database – a Database instance.

The SchemaMigrator is responsible for generating schema-alteringstatements.

• addcolumn(_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_]($91d5d4e449d7d4b4.md#Field)) – A [<code>Field</code>]($91d5d4e449d7d4b4.md#Field) instance.

Add a new column to the provided table. The field provided will be usedto 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 columnwith the default value. Finally, the column will be marked as not null.

• dropcolumn(_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_.

• renamecolumn(_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.

• addnot_null(_table, column)

Parameters:

- **table** (_str_) – Name of table containing column.
- **column** (_str_) – Name of the column to make not nullable.

• dropnot_null(_table, column)

Parameters:

- **table** (_str_) – Name of table containing column.
- **column** (_str_) – Name of the column to make nullable.

• altercolumn_type(_table, column, field[, cast=None])

Parameters:

- **table** (_str_) – Name of the table.
- **column_name** (_str_) – Name of the column to modify.
- **field** ([_Field_]($91d5d4e449d7d4b4.md#Field)) – [<code>Field</code>]($91d5d4e449d7d4b4.md#Field) instance representing newdata type.
- **cast** – (postgres-only) specify a cast expression if thedata-types are incompatible, e.g. <code>column_name::int</code>. Can beprovided as either a string or a [<code>Cast</code>]($91d5d4e449d7d4b4.md#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.

• renametable(_old_name, new_name)

Parameters:

- **old_name** (_str_) – Current name of the table.
- **new_name** (_str_) – New name for the table.

• addindex(_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.

• dropindex(_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.

• addconstraint(_table, name, constraint)

Parameters:

- **table** (_str_) – Table to add constraint to.
- **name** (_str_) – Name used to identify the constraint.
- **constraint** – either a [<code>Check()</code>]($91d5d4e449d7d4b4.md#Check) constraint or foradding an arbitrary constraint use [<code>SQL</code>]($91d5d4e449d7d4b4.md#SQL).

• dropconstraint(_table, name)

Parameters:

- **table** (_str_) – Table to drop constraint from.
- **name** (_str_) – Name of constraint to drop.

• addunique(_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.

  • setsearch_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 followingoperations are currently not supported for SQLite:

• add_constraint
• drop_constraint
• add_unique

• class MySQLMigrator(database)
• Generate migrations for MySQL databases.