Paula Vieira

[Python] Database migration with Alembic

Paula Vieira's photo
Paula Vieira
·

3 min read

Hey, everyone! In the last post we saw how to map our database tables with SQLAlchemy and now we are going to see how to create a migration script for a python project with Alembic. For those who are not familiar with this concept, a database migration tool is very important to help us ensure that the database we deploy in different environments has the same schema. Without a migration tool, it is quite easy to mess things up. Imagine that you have three environments and every change in the database schema is done manually. I've seen many production deploys failing because of a new column that the responsible person forgot to add to the deploy script.

1.What is Alembic?

Alembic is a lightweight database migration tool for usage with the SQLAlchemy Database Toolkit for Python.

2.Installation

Just install the library in your environment:

pip install alembic

3. First steps

First, let's open our project root in command line and start our alembic environment:

project-root$ alembic init alembic

This is going to create a set of folders and files inside our project:

  project-root/alembic/versions
  project-root/alembic/script.py.mako 
  project-root/alembic/env.py 
  project-root/alembic.ini 
  project-root/alembic/README

alembic/versions stores the individual migration scripts

alembic/script.py.mako is a template file used to generate migration scripts

alembic/env.py is a script that is run whenever the alembic migration tool is invoked

alembic.ini contains a database configuration.

4.Configure database url

Open the alembic.ini file and replace the line:

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

with your database url. In this case, I am using a postgres database, so my configuration is going to look like this:

sqlalchemy.url = postgresql://postgres:postgres@localhost:5432/school

5. Create a migration script

We are going to run the command:

alembic revision -m "create student table"

This is going to create a migration script inside the alembic/versions folder:

"""create student table

Revision ID: 1060edca3faf
Revises: 
Create Date: 2021-05-08 22:33:58.686102

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '1060edca3faf'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    pass


def downgrade():
    pass

We are going to add our new table specification inside upgrade() and the reverse procedure inside downgrade()

"""create student table

Revision ID: 1060edca3faf
Revises: 
Create Date: 2021-05-08 22:33:58.686102

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '1060edca3faf'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        'student',
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('name', sa.String(50), nullable=False),
    )


def downgrade():
    op.drop_table('student')

Let's test it! Just run this command inside your project root:

project-root$ alembic upgrade head

You should see a message like this: 

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 1060edca3faf, create student table

This means your migration was successful.

It is very important to notice that once you ran the migration, you can't just change the version file and run it again, you have to create another migration.

This is the first step to start using Alembic. As you can see, it is very simple and easy. I hope you enjoyed it!

Python 3SQLmigration