I tried running alembic, a Python migration tool

Target

Try running the python migration package alembic · PyPI

What is alembic? ??

• Welcome to Alembic’s documentation! — Alembic 1.4.2 documentation

A migration tool that manages your DB when using SQLAlchemy in Python

Environmental preparation

Build an environment with Docker

I want to build it easily, so I will do it with Docker

Folder structure

.
├── README.md
├── docker-compose.yml
└── src
    └── model.py

docker-compose.yml

version: "3"

services:
  db:
    image: postgres:11.7
    container_name: alembic-db
    ports:
      - 5432:5432
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=almebic
  app:
    image: python:3.8.2-slim
    container_name: alembic-app
    volumes:
      - ./src:/usr/src
    environment:
      - PYTHONPATH=${PYTHONPATH}:/usr/src
    tty: true

Install alembic

Install the required packages with pip

pip install alembic psycopg2-binary

If you install alembic, ** SQLAlchemy ** will be installed at the same time.
** psycopg2-binary · PyPI ** is used to connect to postgres

alembic installation

root@9a7582105665:/usr/src# pip install alembic psycopg2-binary
Collecting alembic
  Downloading alembic-1.4.2.tar.gz (1.1 MB)
     |████████████████████████████████| 1.1 MB 7.8 MB/s
  Installing build dependencies ... done
  Getting requirements to build wheel ... done
    Preparing wheel metadata ... done
Collecting psycopg2-binary
  Downloading psycopg2_binary-2.8.5-cp38-cp38-manylinux1_x86_64.whl (3.0 MB)
     |████████████████████████████████| 3.0 MB 32.3 MB/s
Collecting python-dateutil
  Downloading python_dateutil-2.8.1-py2.py3-none-any.whl (227 kB)
     |████████████████████████████████| 227 kB 23.8 MB/s
Collecting Mako
  Downloading Mako-1.1.2-py2.py3-none-any.whl (75 kB)
     |████████████████████████████████| 75 kB 11.2 MB/s
Collecting SQLAlchemy>=1.1.0
  Downloading SQLAlchemy-1.3.16-cp38-cp38-manylinux2010_x86_64.whl (1.2 MB)
     |████████████████████████████████| 1.2 MB 54.3 MB/s
Collecting python-editor>=0.3
  Downloading python_editor-1.0.4-py3-none-any.whl (4.9 kB)
Collecting six>=1.5
  Downloading six-1.14.0-py2.py3-none-any.whl (10 kB)
Collecting MarkupSafe>=0.9.2
  Downloading MarkupSafe-1.1.1-cp38-cp38-manylinux1_x86_64.whl (32 kB)
Building wheels for collected packages: alembic
  Building wheel for alembic (PEP 517) ... done
  Created wheel for alembic: filename=alembic-1.4.2-py2.py3-none-any.whl size=159543 sha256=dc29f47f6c24908d9413da7e3c969c64c252d0cbf9f90fca7cfbb5782b2452d0
  Stored in directory: /root/.cache/pip/wheels/70/08/70/cea787a7e95817b831469fa42af076046e55a05f7c94657463
Successfully built alembic
Installing collected packages: six, python-dateutil, MarkupSafe, Mako, SQLAlchemy, python-editor, alembic, psycopg2-binary
Successfully installed Mako-1.1.2 MarkupSafe-1.1.1 SQLAlchemy-1.3.16 alembic-1.4.2 psycopg2-binary-2.8.5 python-dateutil-2.8.1 python-editor-1.0.4 six-1.14.0

Check the version of the package

root@ecce2b20848e:/usr/src# pip list
Package         Version
--------------- -------
alembic         1.4.2
Mako            1.1.2
MarkupSafe      1.1.1
pip             20.1
psycopg2-binary 2.8.5
python-dateutil 2.8.1
python-editor   1.0.4
setuptools      46.1.3
six             1.14.0
SQLAlchemy      1.3.16
wheel           0.34.2

Create an alembic environment

Creating a migration environment with alembic init

alembic init {environment name of migration}

Create a migration environment with

alembic initial settings

root@ecce2b20848e:/usr/src# alembic init migration
  Creating directory /usr/src/migration ...  done
  Creating directory /usr/src/migration/versions ...  done
  Generating /usr/src/migration/README ...  done
  Generating /usr/src/alembic.ini ...  done
  Generating /usr/src/migration/env.py ...  done
  Generating /usr/src/migration/script.py.mako ...  done
  Please edit configuration/connection/logging settings in '/usr/src/alembic.ini' before proceeding.

When the creation is completed, it will have the following structure
You can see that the ** migration directory ** and ** alembic.ini file ** are created

tree

.
├── README.md
├── docker-compose.yml
└── src
    ├── alembic.ini
    ├── migration
    │   ├── README
    │   ├── env.py
    │   ├── script.py.mako
    │   └── versions
    └── model.py

About files generated by alembic init

• env.py
  Contains a Python script that runs every time the alembic tool is launched
  It is written so that SQLAlchemy Engine can be set and generated and migration can be executed.
  Customizable script

• READEME.md
  It describes what kind of environment the migration environment was created in.

• script.py.mako
  Mako template file used to generate new migration scripts
  Anything here is used to generate a new file in version /

-** versions directory **
Directory where migration scripts are stored

• alembic.ini
  Files to look for when alembic scripts are executed
  Describe run-time settings
  ex. env.py location, log output, migration file naming convention, etc.

Run migration

Editing alembic.ini

Edit the alembic.ini file to connect to the DB
Rewrite the following part of the ini file with DB connection information

alembic.ini (before editing)

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

alembic.ini (after editing)

sqlalchemy.url = postgresql://postgres:postgres@alembic-db:5432/almebic

Use the connection information described in ** docker-compose.yml **

Creating a migration file

Create a migration file with the ** revision ** command

alembic revision -m {file name}

Creating a migration file

root@ecce2b20848e:/usr/src# alembic revision -m "create account table"
  Generating /usr/src/migration/versions/b5f586d58141_create_account_table.py ...  done

After execution, a migration file will be created under the versions directory.

b5f586d58141_create_account_table.py

"""create account table

Revision ID: b5f586d58141
Revises:
Create Date: 2020-05-02 17:49:20.493493

"""
from alembic import op
import sqlalchemy as sa


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


def upgrade():
    pass


def downgrade():
    pass

Edit migration file

Edit the generated migration file
Here we copy the official and create an account table

b5f586d58141_create_account_table.py

"""create account table

Revision ID: b5f586d58141
Revises:
Create Date: 2020-05-02 17:49:20.493493

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'b5f586d58141'
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')

Run migration

Run migration with the ** upgrade ** command

alembic upgrade head

** head ** will perform migration to the latest version
If you want to raise only one version, use ** + 1 ** instead of head
If you want to lower the version, use the ** downgrade ** command
If you want to return to the initial state

alembic downgrade base

To run
Use ** -1 ** instead of base to revert to the previous version

Run migration

root@ecce2b20848e:/usr/src# alembic upgrade head
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> b5f586d58141, create account table

Automatically generate migration files

By editing env.py as described in Auto Generating Migrations — Alembic 1.4.2 documentation
You will be able to automatically create a migration file from the ** SQLAlchemy ** model information defined in Python.
First, define the model with SQLAlchemy

SQLAlchemy model definition

In the model definition, define the account table added earlier in the SQLAlchemy model.
Add columns for ** created_at ** and ** updated_at **
I want to pass the SQLAlchemy Engine to env.py, so define it

model.py

from datetime import datetime

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

#Creating an Engine
Engine = create_engine(
    "postgresql://postgres:postgres@alembic-db:5432/almebic",
    encoding="utf-8",
    echo=False
)

'''
Create Base for model
If you define a model based on this Base, the model information will be stored in metadata.
'''
ModelBase = declarative_base()


class AcountModel(ModelBase):
    """
    AcountModel
    """
    __tablename__ = 'account'

    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    description = Column(Unicode(200))
    created_at = Column(DateTime, default=datetime.now, nullable=False)
    updated_at = Column(DateTime, default=datetime.now, nullable=False)

Editing env.py

Edit env.py so that you can get information about the model defined in model.py
Import the ** ModelBase ** and ** Engine ** defined earlier at the beginning
Substitute ** ModelBase.metadata ** for ** target_metadata **
Also, when executing migration, edit ** run_migrations_online () ** so that migration will be executed.

env.py

from logging.config import fileConfig

from sqlalchemy import engine_from_config
from sqlalchemy import pool

from alembic import context
from model import ModelBase, Engine

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

# 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 = ModelBase.metadata

# 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.

    """
    url = config.get_main_option("sqlalchemy.url")
    connectable = Engine

    with connectable.connect() as connection:
        context.configure(
            url=url,
            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()

Performing migration using autogenerate

The revision command with the ** –autogenerate ** option creates a migration file from the model information defined in SQLAlchemy.

revision command (--With autogenerate)

root@9a7582105665:/usr/src# alembic revision --autogenerate -m "Added columns."
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.ddl.postgresql] Detected sequence named 'account_id_seq' as owned by integer column 'account(id)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected added column 'account.created_at'
INFO  [alembic.autogenerate.compare] Detected added column 'account.updated_at'
  Generating /usr/src/migration/versions/dcd0d354f648_added_columns.py ...  done

The following files are created after execution

dcd0d354f648_added_columns.py

"""Added columns.

Revision ID: dcd0d354f648
Revises: b5f586d58141
Create Date: 2020-05-02 18:58:03.864154

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'dcd0d354f648'
down_revision = 'b5f586d58141'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('account', sa.Column('created_at', sa.DateTime(), nullable=False))
    op.add_column('account', sa.Column('updated_at', sa.DateTime(), nullable=False))
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('account', 'updated_at')
    op.drop_column('account', 'created_at')
    # ### end Alembic commands ###

After that, migration is completed with the upgrade command

bonus

What if I want to rename the files generated by migration?

Edit alembic.ini if you want to change the naming convention instead of dcd0d354f648_added_columns.py (for example, to include date information)
Edit ** file_template ** in alembic.ini

Example

file_template = %%(year)d%%(month).2d%%(day).2d-%%(hour).2d%%(minute).2d_%%(slug)s

Will autogenerate detect any changes?

Please note that some changes may not be detected.
See below for details
Auto Generating Migrations — Alembic 1.4.2 documentation