This article is part 11 of 11 in the series Python SQLAlchemy Tutorial

Alembic

Alembic is a lightweight database migration tool for SQLAlchemy. It is created by the author of SQLAlchemy and it has become the de-facto standard tool to perform migrations on SQLAlchemy backed databases.

Database Migration in SQLAlchemy

A database migration usually changes the schema of a database, such as adding a column or a constraint, adding a table or updating a table. It's often performed using raw SQL wrapped in a transaction so that it can be rolled back if something went wrong during the migration. In this article, we are going to use a sample database to demonstrate how to write Alembic migration scripts for a SQLAlchemy database.

To migrate a SQLAlchemy database, we add an Alembic migration script for the intended migration, perform the migration, update the model definition and then start using the database under the migrated schema. These steps sound like a lot, but they are quite straightforward to do, which is illustrated in the following section.

Sample Database Schema

Let's create a SQLAlchemy database with a department and a employee table.

After the database alembic_sample.sqlite has been created, we realize we forgot to add a many-to-many relationship between Employee and Department.

Migration

Instead of changing the schema directly and then recreate the database from scratch, we choose to migrate the database using alembic. In order to do that, we install alembic, initialize an alembic environment, write a migration script to add the link table, perform the migration, and then use an updated model definition to access the database again.

The migration script is as follows:

Now that the database alembic_sample.sqlite has been upgraded, we can use an updated piece of model code to access the upgraded database.

Notice that we did not delete the database alembic_sample.sqlite but instead performed a migration to add a link table instead. After the migration, the relationship Department.employees and Employee.departments are working as expected.

Summary

Since Alembic is a lightweight database migration tool built specifically for SQLAlchemy, it allows you to re-use the same kind of database model APIs to perform simple migrations. However, it's not an do-it-all-for-you tool. For very database specific migration, such as adding a trigger function in PostgreSQL, a raw DDL statement is still required.

To Practice: Try this interactive course on the basics of Lists, Functions, Packages and NumPy in Python.

About The Author

  • Fung Chai

    Hi Xiaonuo,

    First of all, thank you for the great effort you put into writing this tutorial series.

    I would appreciate it you could add another article on best practice to adopt when developing SQLAlchemy-based applications. When developing medium-size applications, I would spread my code into several python files. In a real-world application with several database tables, how would you lay out your classes? Please also consider the web-based applications and GUI applications (eg, PyQt) which use the ORM code to interface with the database. Thank you.

    Regards,
    Fung Chai

  • hcbayram

    Hi Xiaonuo,

    Thank you for all articles about python. They are all useful for us who are new and trying to understand concept.

    Now i am trying to understand migrating model. i have the model below.

    i try to use BaseInfo class in Companyinfo class and when creating table it works.

    When i want to change column info in class BaseInfo and want to make it affect on Companyinfo
    how could i write the upgrade script.

    Thank you.

    class BaseInfo:
    #@declared_attr
    #def __tablename__(cls):
    # return cls.__name__.lower()
    id = Column(Integer, primary_key=True)
    datacreatedat = Column(DateTime, default=datetime.datetime.utcnow)
    datacreatedby = Column(String(30))

    Base = declarative_base()

    class Companyinfo(BaseInfo,Base):
    __tablename__=’companyinfo’
    name1 = Column(String(100))
    address2 = Column(String(100))

  • Sam

    Thanks very much for this. When I input alembic upgrade head into terminal, the upgrade() script is called (print statements inside it execute, for instance), but nothing happens to my database. Perhaps I am failing to bind it/identify it as the database we are working with? Any guidance would be much appreciated – thanks!!

    -Sam

  • Richard Frimpong

    Xiaonuo, thanks much for your alchemy tutorials
    really gave a nice intro to a newbie like me
    thanks much once again

  • Pingback: 我在BAT学到的技术工具-数据库访问组件-python版本 – 项目经验积累与分享()