Alembic: Database Migrations Using Python

Tom provides a useful tutorial for using database migration to build schema for web applications

When building web applications we are used to using database migrations and seeders to build your schema, make changes and populate our app’s database. A great example of this is within Laravel Database Migrations which provides a command line tool to create migrations

php artisan make:migration create_users_table

You then use the Schema builder to create and modify tables

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsersTable extends Migration
{
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }
    public function down()
    {
        Schema::drop('users');
    }
}

Running the migration command will sequentially roll out your migrations to the database.

php artisan migrate

Benefits

So let’s reflect on the benefits.

• We can work in teams. Because all changes to the schema of all tables are specified and timestamped in our migrations, any team member pulling changes to their local machine can simply run the migration command and build the database.
• We can make changes to existing tables. There’s no need to drop the table, make a change and then migrate again, we can simply create a new migration that modifies an existing table.
• The schema is there forever. No more trying to manually re-build a table for a project you haven’t worked on in years and lost your original script for building the schema.

Alembic

So that’s great – but what if we’re not using Laravel but instead working in Python?

Alembic provides a similar Database migration toolkit. It uses the powerful SQLAlchemy Python library as the underlying engine for writing your database schema.

We begin by installing Alembic (using pip). This should also install the SQLAlchemy dependency.

pip install alembic

Once installed, create a new directory for your project to live in and then generate your migration environment by running the following command

cd yourproject
alembic init alembic

This creates a versions folder (to hold your migration files) and an env.py config file.

We now need to setup our connection to our database. The env.py file includes the connection url

sqlalchemy.url = driver://user:pwd@localhost/dbname

For this example we will be using the MySQL driver with a local database alembic_demo. Therefore our connection url will look like this

sqlalchemy.url = mysql://root:root@localhost/alembic_demo

(Note you will need to install the corresponding MySQL repositories. For example, working on Ubuntu 16.04 therefore require the python-mysqldb repo)

Now our migration environment is setup and we are connected to our database we can crack on with building our tables. Like Laravel, we can use the command line to generate a migration

alembic revision -m "create users table"

Again, similarly to Laravel we have upgrade and downgrade functions where we can write our schema that will build and roll back our table modifications. It’s also worth noting that, if run for the first time, this command will create the alembic_version table that will keep track of the migrations currently rolled out on the database.

"""create users tableRevision ID: 1975ea83b712
Revises: Create Date: 2011-11-08 11:40:27.089406

"""
# revision identifiers, used by Alembic.
revision = '1975ea83b712'
down_revision = None
branch_labels = None

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.create_table(
        'users',
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('name', sa.String(50), nullable=False),
        sa.Column('description', sa.Unicode(200)),
    )
def downgrade():
    op.drop_table('account')

To roll out all migrations we run the following upgrade command

alembic upgrade head

And our table is built – simple as that! We can then create new tables or modify existing by generating new migrations and running the upgrade command. Moreover, we can roll back to base by calling the command

alembic downgrade base

Recap

We should now have a migration environment for building and modifying database schema in a detailed, sequential and team-orientated process.

Further reading may be looking at how Alembic’s tools can help us auto generate migrations from an existing database.