SQLAlchemy Alembic Database Migration

Heads Up! My examples here in this topic SQLAlchemy Alembic Database Migration are not specific to any Python framework. Whether if you are using Django, Flask, FastAPI or others, you should be able to follow along and apply this to your own project. Moreover, we are going to work on a Python 3 version.

PostgreSQL and Psycopg2

PostgreSQL is a Relational Data Base System (RDBS). It is an enterprise grade, reliable and one of the most popular open source database out there. Install the library using pip.

pip3 install SQLAlchemy

Psycop2 on the other hand, is a PostgreSQL adapter for Python. It allows a Python application to connect to PostgreSQL and execute database commands.

SQLAlchemy and Alembic

SQLAlchemy is an Object Relational Mapper (ORM) for Python. This allows us to define models that represent the database table schema. And it also provides functions to create and execute database commands without writing SQL statements.

Alembic is a database migration tool for SQLAlchemy. You use this to apply version changes in your relational database. Most importantly, it can read from an SQLAlchemy model and apply the updates to a database.

How to install Psycopg2

There are two ways you could install psycopg2. First is by installing the binary version, which is okay to do while working in development.

pip3 install psycopg2-binary

Your second option is installing psycopg2 using the source distribution. This method is advised in a production setup, although there are several build prerequisites that you have to install first.

Linux RHEL/CentOS

yum install -y python3-devel postgresql-devel

You may also have to install an SSL library and a C compiler if you do not have it yet in your server.

yum install -y openssl gcc

MacOS

Install client utilities for PostgreSQL.

brew install libpq

Open your .bash_profile using any text editor and add new environment variable LDFLAGS and edit PATH.

export LDFLAGS="-I/usr/local/opt/openssl/include -L/usr/local/opt/openssl/lib"
export PATH="/usr/local/opt/libpq/bin:$PATH"

Alternatively, if libpq does not work, you can also try installing the PostgreSQL library. You do not have to start the database service locally. But what we need here is the pg_config component. And you still need to add the LDFLAGS in your .bash_profile.

brew insall postgresql

If you are still getting errors pertaining to missing libraries, you may also observe the following solutions.

Install the command line tools and SSL Library. Make sure that you are applying the correct path of OpenSSL in LDFLAGS.

xcode-select --install
brew install openssl
which openssl

If you have installed PostgreSQL in a different location, make sure you also add this in PATH. This will resolve the issue when alembic could not locate the pg_config.

which postgres

Add this another line in your .bash_profile.

export PATH=/usr/local/Cellar/postgresql/13.0/bin/:$PATH

Finally, after you have installed all the prerequisites, you can now also install psycopg2 from distribution. And we are using pip here.

pip3 install psycopg2

Configure SQLAlchemy and Alembic

SQLAlchemy ORM

The first thing we need to do is to create a Python package for our ORM models.

mkdir -p app/models && touch app/models/__init__.py

Then we are going to need a new module to define our Base class and our example model. Assuming that the new module is app/models/orm.py, use any text editor of your choice and copy these lines of code.

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import MetaData

convention = {
  "pk": "pk_%(table_name)s"
}

Base = declarative_base()
Base.metadata = MetaData(naming_convention=convention)

class Person(Base):
    __tablename__ = 'person'

    id = Column(Integer, primary_key=True)
    name=  Column(String(50))

In here, the declarative_base() returns a base class and the Person model becomes a subclass of this. Later, when we configure alembic we will use the Base class as the target metadata. In addition, we passed a naming convention for our primary key in the MetaData class.

It is important to notice the __table__ and Column definition we have inside the Person model. Alembic will read and generate a database migration script out of this. The Person model will translate into a new table named “person”, with two columns. The first column is “id”, that is an Integer primary key. And the second column is “name”, a string that has a limit of 50 characters.

Alembic configuration

Now that we have our models in place, this time we need to initialize a Migration Environment for Alembic. Take note, all the setup we will do here will be inside the app folder we just created earlier.

alembic init alembic

The command will generate a new folder structure and I want to mention a few important files and folders here. Starting with alembic as the home of the migration environment. Then inside is the versions, that will hold the version scripts (database update scripts). And env.py, this is a Python script that is run whenever the alembic migration tool is invoked.

Another important file produced by the command is a configuration file named alembic.ini. It contains all the default set up such as script location, file template and many more. But the most important configuration here is the URL to connect the database via SQLAlchemy sqlalchemy.url.

• app
  • alembic.ini
  • alembic
    • versions
    • env.py

PostgreSQL Docker

While working locally in your computer, you can use Docker to easily launch a PostgreSQL database.

docker run --rm --name some-postgres -e POSTGRES_PASSWORD=mysecretpass -e POSTGRES_DB=evaluation_app -p 5432:5432 -d postgres

In this Docker command, we passed some arguments to set up our local database server. The database name is evaluation_app, with password mysecretpass. By default, the username is postgres and the host will be 127.0.0.1:5432. Also, port 5432 will be mapped from our local host to the container.

This time edit the alembic.ini file and apply this in the sqlalchemy.url I mentioned earlier. Alembic is using Psycopg2 to connect to the database, if you have not installed this yet please read about How to install Psycopg2.

sqlalchemy.url = postgresql+psycopg2://postgres:[email protected]:5432/evaluation_app

Alembic Generating Migrations

Now that we have our connection to PostgreSQL database all configured, the next step is to generate our migration script. Since we already created our SQLAlchemy models, we can actually auto generate our script by just passing an option --autogenerate.

But how do we tell alembic where to find our models? That is actually an easy fix. We need to import the Base class that was created using the declarative_base() from our module app/models/orm.py to app/alembic/env.py and assign this in target_metadata variable. Please take note that your import path may be different, depending on your project’s folder structure.

from app.models import Base
targer_metadata = Base.metadata

And the command to auto generate a migration script.

alembic revision --autogenerate -m "Commit message"

If everything goes well, you should be able to see an output something similar below.

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'person'
  Generating /Users/rex/Desktop/app/alembic/versions/18f8cd9baed4_commit_message.py ...  done

Alembic Run Migration

Now is the moment we have been waiting for. This time we are going to apply the database migration script we have generated from the previous step. If you are wondering how alembic keeps track of the upgrade versions, it actually creates a table in your database the first time you run the alembic revision command. The name of the table in your database is alembic_version, and it saves the last revision number in version_num column of the table.

To get the current revision for a database.

alembic current

Apply Latest Database Upgrade

The command to run the latest generated migration script(s) for a database.

alembic upgrade head

If the migration script(s) ran successfully, you should see an output similar below.

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 18f8cd9baed4, Commit message

To conclude this topic SQLAlchemy Alembic Database Migration, I put up a quick cheat sheet of some of the useful commands for managing PostgreSQL database and Alembic migration tool.

Alembic Cheat Sheet

PostgreSQL Cheat Sheet