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.

import os

from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Department(Base):
__tablename__ = 'department'
id = Column(Integer, primary_key=True)
name = Column(String)

class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String)
hired_on = Column(DateTime, default=func.now())

db_name = 'alembic_sample.sqlite'
if os.path.exists(db_name):
os.remove(db_name)

from sqlalchemy import create_engine
engine = create_engine('sqlite:///' + db_name)

from sqlalchemy.orm import sessionmaker
session = sessionmaker()
session.configure(bind=engine)
Base.metadata.create_all(engine)

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.

$alembic init alembic Creating directory /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic ... done Creating directory /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic/versions ... done Generating /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic/env.pyc ... done Generating /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic.ini ... done Generating /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic/script.py.mako ... done Generating /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic/env.py ... done Generating /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic/README ... done Please edit configuration/connection/logging settings in '/home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic.ini' before proceeding.$ vim alembic.ini # Change the line thats starts with "sqlalchemy.url" into "sqlalchemy.url = sqlite:///alembic_sample.sqlite"

$alembic current INFO [alembic.migration] Context impl SQLiteImpl. INFO [alembic.migration] Will assume non-transactional DDL. Current revision for sqlite:///alembic_sample.sqlite: None$ alembic revision -m "add department_employee_link"

$alembic upgrade head INFO [alembic.migration] Context impl SQLiteImpl. INFO [alembic.migration] Will assume non-transactional DDL. INFO [alembic.migration] Running upgrade None -> 1da977fd3e6e, add department_employee_link$ alembic current
INFO [alembic.migration] Context impl SQLiteImpl.
INFO [alembic.migration] Will assume non-transactional DDL.


The migration script is as follows:

'''

Revision ID: 1da977fd3e6e
Revises: None
Create Date: 2014-10-23 22:38:42.894194

'''

# revision identifiers, used by Alembic.
revision = '1da977fd3e6e'
down_revision = None

from alembic import op
import sqlalchemy as sa

op.create_table(
sa.Column(
'department_id', sa.Integer,
sa.ForeignKey('department.id'), primary_key=True
),
sa.Column(
'employee_id', sa.Integer,
sa.ForeignKey('employee.id'), primary_key=True
)
)

op.drop_table(
)


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

import os

from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Department(Base):
__tablename__ = 'department'
id = Column(Integer, primary_key=True)
name = Column(String)
employees = relationship(
'Employee',
)

class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String)
hired_on = Column(DateTime, default=func.now())
departments = relationship(
Department,
)

department_id = Column(Integer, ForeignKey('department.id'), primary_key=True)
employee_id = Column(Integer, ForeignKey('employee.id'), primary_key=True)

db_name = 'alembic_sample.sqlite'

from sqlalchemy import create_engine
engine = create_engine('sqlite:///' + db_name)

from sqlalchemy.orm import sessionmaker
session = sessionmaker()
session.configure(bind=engine)
s = session()
IT = Department(name='IT')
Financial = Department(name='Financial')
cathy = Employee(name='Cathy')
marry = Employee(name='Marry')
john = Employee(name='John')
s.close()
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.