Saturday 2:35 p.m.–3:05 p.m.

Sane schema migrations with Alembic and SQLAlchemy

Selena Deckelmann

Audience level:
Intermediate
Category:
Databases

Description

Working with developers on schema migrations is a perennial challenge for DBAs and developers. Devs tend to like a "set it and forget it" tool. This talk discusses the strategies used to move from a pure SQL and shell migration system to using SQLAlchemy and alembic for maximum DBA and developer happiness.

Abstract

Working with developers on schema migrations is a perennial challenge for DBAs and developers. Devs tend to like a "set it and forget it" tool. Very few of those tools work well with distributed teams, continuous integration or situations which require raw SQL. This talk discusses the strategies used to move from a pure SQL and shell migration system to using [SQLAlchemy](http://sqlalchemy.readthedocs.org) and [alembic](http://alembic.readthedocs.org) for maximum DBA and developer happiness. I'll share tips for DBAs and developers in bringing schema elements into the mainstream revision control system, and making it reasonable and easy to get code review on stored procedures from the entire team. Whether you're a Python shop looking for a better migration tool, or a DBA looking to integrate better with your developer team, you'll get useful insight into the key features your next schema migration system should support. Our database environment is a 2+TB PostgreSQL cluster running 9.2. We have a rapidly evolving schema and reporting system, and over 150 user defined functions in plpgsql. We also support custom types, DOMAINs and data types not supported yet by SQLAlchemy. Migrating from pure SQL to Python models was the first challenge. Creating a reasonable system for managing the UDFs was the second. The final challenge has been training developers to create and manage their own migrations (called "revisions" in alembic). [1]: http://sqlalchemy.readthedocs.org