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.
[python]
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)
[/python]
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.
[shell]
$ 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"
Generating /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic/versions/1da977fd3e6e_add_department_employee_link.py ... done
$ 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.
Current revision for sqlite:///alembic_sample.sqlite: None -> 1da977fd3e6e (head), add department_employee_link
[/shell]
The migration script is as follows:
[python]
'''
add department_employee_link
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
def upgrade():
op.create_table(
'department_employee_link',
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
)
)
def downgrade():
op.drop_table(
'department_employee_link'
)
[/python]
Now that the database alembic_sample.sqlite has been upgraded, we can use an updated piece of model code to access the upgraded database.
[python]
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',
secondary='department_employee_link'
)
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String)
hired_on = Column(DateTime, default=func.now())
departments = relationship(
Department,
secondary='department_employee_link'
)
class DepartmentEmployeeLink(Base):
__tablename__ = 'department_employee_link'
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)
Base.metadata.bind = engine
s = session()
IT = Department(name='IT')
Financial = Department(name='Financial')
s.add(IT)
s.add(Financial)
cathy = Employee(name='Cathy')
marry = Employee(name='Marry')
john = Employee(name='John')
s.add(cathy)
s.add(marry)
s.add(john)
cathy.departments.append(Financial)
marry.departments.append(Financial)
john.departments.append(IT)
s.commit()
s.close()
[/python]
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.
