Getting started with Alembic

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:[email protected]/dbname
    
    to
    sqlalchemy.url = postgresql://%(DB_USER)s:%(DB_PASS)[email protected]%(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

References: