Database schema migrations

Importing migrate.changeset adds some new methods to existing SQLAlchemy objects, as well as creating functions of its own. Most operations can be done either by a method or a function. Methods match SQLAlchemy’s existing API and are more intuitive when the object is available; functions allow one to make changes when only the name of an object is available (for example, adding a column to a table in the database without having to load that table into Python).

Changeset operations can be used independently of SQLAlchemy Migrate’s versioning.

For more information, see the API documentation for migrate.changeset.

Here are some direct links to the relevent sections of the API documentations:

  • Create a column
  • Drop a column
  • Alter a column (follow a link for list of supported changes)
  • Rename a table
  • Rename an index
  • Create primary key constraint
  • Drop primary key constraint
  • Create foreign key contraint
  • Drop foreign key constraint
  • Create unique key contraint
  • Drop unique key constraint
  • Create check key contraint
  • Drop check key constraint


Many of the schema modification methods above take an alter_metadata keyword parameter. This parameter defaults to True.

The following sections give examples of how to make various kinds of schema changes.


Given a standard SQLAlchemy table:

table = Table('mytable', meta,
   Column('id', Integer, primary_key=True),

You can create a column with create():

col = Column('col1', String, default='foobar')
col.create(table, populate_default=True)

# Column is added to table based on its name
assert col is table.c.col1

# col1 is populated with 'foobar' because of `populate_default`


You can pass primary_key_name, index_name and unique_name to the create() method to issue ALTER TABLE ADD CONSTRAINT after changing the column.

For multi columns constraints and other advanced configuration, check the constraint tutorial.

New in version 0.6.0.

You can drop a column with drop():


You can alter a column with alter():


# Renaming a column affects how it's accessed by the table object
assert col is table.c.col2

# Other properties can be modified as well
col.alter(type=String(42), default="life, the universe, and everything", nullable=False)

# Given another column object, col1.alter(col2), col1 will be changed to match col2
col.alter(Column('col3', String(77), nullable=True))
assert col.nullable
assert table.c.col3 is col

Deprecated since version 0.6.0: Passing a Column to ChangesetColumn.alter() is deprecated. Pass in explicit parameters, such as name for a new column name and type for a new column type, instead. Do not include any parameters that are not changed.


SQLAlchemy includes support for creating and dropping tables..

Tables can be renamed with rename():



SQLAlchemy supports creating and dropping indexes.

Indexes can be renamed using rename():



SQLAlchemy supports creating or dropping constraints at the same time a table is created or dropped. SQLAlchemy Migrate adds support for creating and dropping PrimaryKeyConstraint, ForeignKeyConstraint, CheckConstraint and UniqueConstraint constraints independently using ALTER TABLE statements.

The following rundowns are true for all constraints classes:

  1. Make sure you import the relevent constrain class Migrate and not from SQLAlchemy, for example:

    from migrate.changeset.constraint import ForeignKeyConstraint

    The classes in that module have the extra create() and drop() methods.

  2. You can also use Constraints as in SQLAlchemy. In this case passing table argument explicitly is required:

    cons = PrimaryKeyConstraint('id', 'num', table=self.table)
    # Create the constraint
    # Drop the constraint

    You can also pass in Column objects (and table argument can be left out):

    cons = PrimaryKeyConstraint(col1, col2)
  3. Some dialects support CASCADE option when dropping constraints:

    cons = PrimaryKeyConstraint(col1, col2)
    # Create the constraint
    # Drop the constraint


SQLAlchemy Migrate will try to guess the name of the constraints for databases, but if it’s something other than the default, you’ll need to give its name. Best practice is to always name your constraints. Note that Oracle requires that you state the name of the constraint to be created or dropped.


Primary key constraints:

from migrate.changeset.constraint import PrimaryKeyConstraint

cons = PrimaryKeyConstraint(col1, col2)

# Create the constraint

# Drop the constraint

Foreign key constraints:

from migrate.changeset.constraint import ForeignKeyConstraint

cons = ForeignKeyConstraint([table.c.fkey], [])

# Create the constraint

# Drop the constraint

Check constraints:

from migrate.changeset.constraint import CheckConstraint

cons = CheckConstraint('id > 3', columns=[])

# Create the constraint

# Drop the constraint

Unique constraints:

from migrate.changeset.constraint import UniqueConstraint

cons = UniqueConstraint('id', 'age', table=self.table)

# Create the constraint

# Drop the constraint

Project Versions

Table Of Contents

Previous topic

Database schema versioning workflow

Next topic

Repository migration (0.4.5 -> 0.5.4)

This Page