Getting started with Alembic¶

Why Alembic?¶

It's allows developers to manage the database migrations with ease.
We can the changes and revert the changes to/from the database.
Alembic is similar to Active Record Migrations for Ruby on Rails

install "alembic" with pipenv¶

run the command pipenv install alembic
activate the virtual environment with command pipenv shell
now, run the commnad alembic init alembic. It will create the configuration files for alembic.
the project structure looks something like below.

.
├── Pipfile
├── Pipfile.lock
├── alembic
│   ├── README
│   ├── env.py
│   ├── script.py.mako
│   └── versions
└── alembic.ini

install postgresql python dependencies¶

We will be using the postgres as a database.
To install run the command

pipenv install psycopg2

understanding the files created by alembic¶

alembic.ini is the file for configurations. Now, let's open that file and update as below. change below line

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

to

sqlalchemy.url = postgresql://%(DB_USER)s:%(DB_PASS)s@%(DB_HOST)s/%(DB_NAME)s

sqlalchemy.url is the database url that alembic will use to make migrations.
alembic/env.py is actual file which uses the config file alembic.ini and loads the configuration.
For sqlalchemy.url we are setting the string interpolation so, we need to set the config options in alembic/env.py
Let's add the below code

# here we allow ourselves to pass interpolation vars to alembic.ini
# fron the host env
section = config.config_ini_section
config.set_section_option(section, "DB_USER", os.environ.get("DB_USER"))
config.set_section_option(section, "DB_PASS", os.environ.get("DB_PASS"))
config.set_section_option(section, "DB_HOST", os.environ.get("DB_HOST"))
config.set_section_option(section, "DB_NAME", os.environ.get("DB_NAME"))

- Now the envfile looks like below.

import os
from logging.config import fileConfig

from sqlalchemy import engine_from_config
from sqlalchemy import pool

from alembic import context

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config
# here we allow ourselves to pass interpolation vars to alembic.ini
# fron the host env
section = config.config_ini_section
config.set_section_option(section, "DB_USER", os.environ.get("DB_USER"))
config.set_section_option(section, "DB_PASS", os.environ.get("DB_PASS"))
config.set_section_option(section, "DB_HOST", os.environ.get("DB_HOST"))
config.set_section_option(section, "DB_NAME", os.environ.get("DB_NAME"))

# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = None

# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.


def run_migrations_offline():
    """Run migrations in 'offline' mode.

    This configures the context with just a URL
    and not an Engine, though an Engine is acceptable
    here as well.  By skipping the Engine creation
    we don't even need a DBAPI to be available.

    Calls to context.execute() here emit the given string to the
    script output.

    """
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )

    with context.begin_transaction():
        context.run_migrations()


def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(
            connection=connection, target_metadata=target_metadata
        )

        with context.begin_transaction():
            context.run_migrations()


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

If we want we can make modifications to the alembic confgurations loaded from alembic.ini file.
Now, we should make sure that we need to have the below environment variable.
Create a database named awesome_store in postgres database.

export DB_USER="postgres"
export DB_PASS="root"
export DB_HOST="localhost"
export DB_NAME="awesome_store"

creating first database migration with alembic¶

let's use the command alembic revision -m "create user table" to create the skeliton of the migration.
Now, the direcoty looks like below.

.
├── Pipfile
├── Pipfile.lock
├── alembic
│   ├── README
│   ├── env.py
│   ├── script.py.mako
│   └── versions
│       └── 433af9f41996_create_user_table.py
└── alembic.ini

- 433af9f41996_create_user_table.py is the migration file created with above command.

433af9f41996_create_user_table.py

"""create user table

Revision ID: 433af9f41996
Revises: 
Create Date: 2021-11-20 19:17:01.434529

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '433af9f41996'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    pass


def downgrade():
    pass

Let's talk about the above file.
- revision: It's the code to identify the migration file.
- down_revision: By using it alembic will apply the migrations in an order.
- branch_labels: It's just help info as lables to identify it.
- depends_on: It's reference code on which the current migration depends on.
- upgrade(): It's the function get's called when a migration changes are applied.
- downgrade(): It's the function get's called when a migration changes are removed.
Let's update the 433af9f41996_create_user_table.py as below.

"""create user table

Revision ID: 433af9f41996
Revises: 
Create Date: 2021-11-20 19:17:01.434529

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '433af9f41996'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        'account',
        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')

- The above code will create a table named account with coulmns id, name, description when upgraded and deletes the table when downgraded.

To see the generated sql just run the command alembic upgrade head --sql. It will generate the below query

BEGIN;

CREATE TABLE alembic_version (
    version_num VARCHAR(32) NOT NULL, 
    CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
);

INFO  [alembic.runtime.migration] Running upgrade  -> 433af9f41996, create user table
-- Running upgrade  -> 433af9f41996

CREATE TABLE account (
    id SERIAL NOT NULL, 
    name VARCHAR(50) NOT NULL, 
    description VARCHAR(200), 
    PRIMARY KEY (id)
);

INSERT INTO alembic_version (version_num) VALUES ('433af9f41996') RETURNING alembic_version.version_num;

COMMIT;

To apply the upgrade migration run the below command.

alembic upgrade head

Now, check the database you will find the two tables alembic_version and account.
alembic_version is to maintain the database migrations information to avoid reapplying the already applied migrations.
To remove the last applied migration run command alembic downgrade -1

Getting started with Alembic¶

Why Alembic?¶

install "alembic" with pipenv¶

install postgresql python dependencies¶

understanding the files created by alembic¶

creating first database migration with alembic¶

References:¶

Related Blog Articles

Alembic Ddl Operations

Getting Started With Fastapi