introducing alembic to your PostgreSQL project

python, sqlalchemy and alembic

October 29, 2021


The use case I would like to describe is about introducing alembic to an existing project and database.

Just iamgine you started a small project and later on decided to modify your models.
Every time the models are changed, the database tables also need to be modified.

Preparations

For the sake of simplicity, I assume a running PostgreSQL database server on 127.0.0.1:5432:

# Quick and dirty docker container
# This creates a 'postgres' folder in order to make the database persistent.
$ docker run -d --name=postgres_alembic_db -v $(pwd)/postgres:/var/lib/postgresql/data: --log-driver json-file --log-opt max-size=10m --log-opt max-file=3 -p 127.0.0.1:5432:5432 -e POSTGRES_HOST_AUTH_METHOD=trust  postgres:alpine

This setup allows you to access the database server like this:

$ docker exec -it postgres_alembic_db psql -Upostgres
>$ postgres=#

In order to introduce alembic, you need to created a base migration for yor models:

./project
  models/
    user.py
  ...

Let's quickly install and configure alembic:

$ cd ./project
$ python3 -mvenv venv
$ venv/bin/pip install alembic==1.7.4 psycopg2-binary==2.9.1
$ venv/bin/alembic init --template generic alembic

In alembic.ini set the database connection string:

sqlalchemy.url = postgresql://postgres@127.0.0.1:5432
# sqlalchemy.url = postgresql://user:password@127.0.0.1:5432

Note:

    DATABASE_URL = 'postgresql://postgres@127.0.0.1:5432'

    sqlalchemy.url =

    from config import DATABASE_URL
    from models.user import Base
    ...
    config = context.config  # This line already exists.
    config.set_main_option("sqlalchemy.url", DATABASE_URL)
    ...
    target_metadata = [Base.metadata]  # This line needs modification.

The project/ now looks like this:

./project
  models/
    user.py
  alembic/
    env.py
    ...
  alembic.ini
  ...

Database models

In models/user.py you keep your User models:

import datetime

from sqlalchemy import Column, String, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    username = Column(String(200), unique=True, nullable=False)
    email = Column(String(120), unique=True, nullable=False)
    created = Column(DateTime, nullable=False, default=datetime.datetime.utcnow)

    def __repr__(self):
        return f"<User {self.username} {self.email}>"

Database migrations

Create an alembic migration - --autogenerate creates the migration file.

$ venv/bin/alembic revision --autogenerate -m "base"
# Created 'alembic/versions/327f7b9dd52a_base.py'
# This file looks like the folloiwng.
# Note: I manually added the default value for column 'created'.
$ cat alembic/versions/327f7b9dd52a_base.py
"""base

Revision ID: 327f7b9dd52a
Revises: 
Create Date: 2021-10-29 08:40:55.041661

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '327f7b9dd52a'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('users',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('username', sa.String(length=200), nullable=False),
    sa.Column('email', sa.String(length=120), nullable=False),
    sa.Column('created', sa.DateTime(), nullable=False, server_default=sa.func.current_timestamp()),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('email'),
    sa.UniqueConstraint('username')
    )
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('users')
    # ### end Alembic commands ###

Note:

This first migration (base) contains the current state of your database models.
At this point, still referring to the use case we imagined at the beginning, this is basically what we assume to be already existing in the database itself:


In order to make the database aware of the models and bring alembic and the database to the same level:

venv/bin/alembic stamp head

alembic now shows the most recent version (migration):

$ venv/bin/alembic current
# Output
327f7b9dd52a (head)

In case the database on 127.0.0.1 is just your local test database, you need to change the database connection string to your real database before executing the stamp head command.

In case you have an empty database, like a local test database, you need to do an upgrade by applying the alembic migration:

# Dry run
$ venv/bin/alembic history -r current:head
# Output
<base> -> 327f7b9dd52a (head), base

# Upgrade
$ venv/bin/alembic upgrade head
# Output
INFO  [alembic.runtime.migration] Running upgrade  -> 327f7b9dd52a, base

$ venv/bin/alembic current
# Output
327f7b9dd52a (head)

If you like to play around without destroying your initial test database and users table on the local server, you can simply create another database on the same server:

$ docker exec -it postgres_alembic_db psql -Upostgres
# Create a new database with the name 'project'
>$ postgres=# CREATE DATABASE project;
# Connect to this new database
>$ postgres=# \c project
>$ project=#


Then simply tell alembic to connect to the new database by appending the new database name project to your database connection string in alembic.ini or config.py, respectively - dependening on your configuration:

# Instead of
#   sqlalchemy.url = postgresql://postgres@127.0.0.1:5432
# append the new database name
sqlalchemy.url = postgresql://postgres@127.0.0.1:5432/project

On the database itself you can directly create users:

$ docker exec -it postgres_alembic_db psql -Upostgres
>$ postgres=# insert into users (username,email) values('test','testmail');
>$ postgres=# select * from users;
 id | username |  email   |       created       
----+----------+----------+---------------------
  1 | test     | testmail | 2021-10-29 08:31:06.95216
(1 rows)