Implementing Database Migration and Versioning with Alembic

Here in this article we will try to leverage Alembic to generate our database migration scripts based on SQLAlchemy ORM constructs and apply them to the mariadb database.

Table of Contents

Test Environment

Fedora 41 server
MariaDB server v10.11.11
SQLAlchemy toolkit v2.1.6

What is SQLAlchemy Alembic

Alembic is a lightweight database migration tool for usage with the SQLAlchemy Database Toolkit for Python. Alembic provides for the creation, management, and invocation of change management scripts for a relational database, using SQLAlchemy as the underlying engine.

If you are interested in watching the video. Here is the YouTube video on the same step by step procedure outlined below.

Procedure

Step1: Ensure MariaDB service installed and running

As a first step ensure that you have some kind of Relational Database Management Service (ie. RDBMS) installed on your system. Here we are going to install MariaDB server and secure the instace.

# Install Mariadb server
admin@linuxser:~$ sudo dnf install mariadb mariadb-server

# Start database service
admin@linuxser:~$ sudo systemctl start mariadb
admin@linuxser:~$ sudo systemctl status mariadb

# Secure instance
admin@linuxser:~$ sudo mariadb-secure-installation

Once our db services is up and running we can connect to it using the root user and create our database.

# Connect to the database
admin@linuxser:~$ mariadb -u root -p

# Create a database if not exists
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS inventory;
Query OK, 1 row affected (0.001 sec)

# List the databases
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| inventory          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.001 sec)

MariaDB [(none)]> exit

Step2: Initialize UV Project

Now, let’s initialize a python project named “alembicdemo” using the uv package manager. Ensure that you got the uv package manager installed on your system.

Once the project is initialized we can add the required dependencies (ie. alembic and mysqlclient).

# Initialize uv project
admin@linuxser:~$ uv init alembicdemo
Initialized project `alembicdemo` at `/home/admin/alembicdemo`
admin@linuxser:~$ cd alembicdemo/

# Add dependencies
admin@linuxser:~/alembicdemo$ uv add alembic mysqlclient

# List dependencies
admin@linuxser:~/alembicdemo$ uv pip list
Package           Version
----------------- -------
alembic           1.20.1
mako              1.4.3
markupsafe        3.0.4
mysqlclient       2.3.0
sqlalchemy        2.1.6
typing-extensions 4.16.0

# Check alembic binary version available in the PATH
admin@linuxser:~/alembicdemo$ uv run alembic --version
alembic 1.20.1

# Check the directory structure
admin@linuxser:~/alembicdemo$ tree .
.
├── main.py
├── pyproject.toml
├── README.md
└── uv.lock

1 directory, 4 files

It is recommended to have alembic installed in the same module / Python path as that of the target project, usually using a Python virtual environment, so that when the alembic command is run, the Python script which is invoked by alembic, namely your project’s env.py script, will have access to your application’s models.

Step3: Create Alembic migration environment

Here we are going to create alembic migration environment. This is basically a database migrations project stucture that is getting generated based on a template. In this case we are using the default “Generic” template for working with a single database.

admin@linuxser:~/alembicdemo$ uv run alembic init alembic
  Creating directory /home/admin/alembicdemo/alembic ...  done
  Creating directory /home/admin/alembicdemo/alembic/versions ...  done
  Generating /home/admin/alembicdemo/alembic/README ...  done
  Generating /home/admin/alembicdemo/alembic.ini ...  done
  Generating /home/admin/alembicdemo/alembic/env.py ...  done
  Generating /home/admin/alembicdemo/alembic/script.py.mako ...  done
  Please edit configuration/connection/logging settings in /home/admin/alembicdemo/alembic.ini before
  proceeding.

admin@linuxser:~/alembicdemo$ tree .
.
├── alembic
│   ├── env.py
│   ├── README
│   ├── script.py.mako
│   └── versions
├── alembic.ini
├── main.py
├── pyproject.toml
├── README.md
└── uv.lock

3 directories, 8 files

For starting up with just a single database and the generic configuration, we just need to update the sqlalchemy.url directive within the alembic.ini file as shown below.

admin@linuxser:~/alembicdemo$ cat alembic.ini | grep "sqlalchemy.url"
# sqlalchemy.url = driver://user:pass@localhost/dbname
sqlalchemy.url = mysql+mysqldb://root:admin%%401234@localhost/inventory

Step4: Create Model.py using SQLAlchemy ORM constructs

Once our migration environment is ready, we can create our projects models using the SQLAlchemy ORM constructs. Here we are going to create a models.py as shown below.

(alembicdemo) admin@linuxser:~/alembicdemo$ cat models.py 
from sqlalchemy import String, Text
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column

class Base(DeclarativeBase):
  pass

class Books(Base):
  __tablename__ = "books"

  isbn: Mapped[int] = mapped_column(primary_key=True)
  title: Mapped[str] = mapped_column(String(100))
  author_id: Mapped[int] = mapped_column()
  publisher_id: Mapped[int] = mapped_column()
  year_pub: Mapped[str] = mapped_column(String(4))
  description: Mapped[str] = mapped_column(Text)

Step5: Connect Alembic to Your Models (alembic/env.py)

In order for Alembic to connect with our project model, we need to update the alembic env.py with the metadata() object reference from our model as shown below.

(alembicdemo) admin@linuxser:~/alembicdemo/alembic$ cat env.py 
from logging.config import fileConfig

from sqlalchemy import engine_from_config
from sqlalchemy import pool

from alembic import context

from models import Base                 # Import base class from models

# 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.
if config.config_file_name is not None:
    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
target_metadata = Base.metadata             # Reference models MetaData object

# 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() -> None:
    """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() -> None:
    """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()

Step6: Create autogenerated migration script

We are now ready to generate our automated migration script based on the model.py file that we built using the below command.

By default these scripts will be generated in the versions folder within the database migration environment.

(alembicdemo) admin@linuxser:~/alembicdemo$ uv run alembic revision --autogenerate -m "create books table within inventory db"
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.schemas
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.tables
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.types
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.constraints
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.defaults
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.comments
INFO  [alembic.autogenerate.compare.tables] Detected added table 'books'
  Creating directory /home/admin/alembicdemo/alembic/versions ...  done
  Generating
  /home/admin/alembicdemo/alembic/versions/f153a6ff72ed_create_books_table_within_inventory_db.py ...  done

We can have a look at the autogenerated migration script to validate if does what is expected to be done.

(alembicdemo) admin@linuxser:~/alembicdemo$ cat alembic/versions/f153a6ff72ed_create_books_table_within_inventory_db.py 
"""create books table within inventory db

Revision ID: f153a6ff72ed
Revises: 
Create Date: 2026-10-30 13:51:01.736516

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = 'f153a6ff72ed'
down_revision: Union[str, Sequence[str], None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('books',
    sa.Column('isbn', sa.Integer(), nullable=False),
    sa.Column('title', sa.String(length=100), nullable=False),
    sa.Column('author_id', sa.Integer(), nullable=False),
    sa.Column('publisher_id', sa.Integer(), nullable=False),
    sa.Column('year_pub', sa.String(length=4), nullable=False),
    sa.Column('description', sa.Text(), nullable=False),
    sa.PrimaryKeyConstraint('isbn')
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('books')
    # ### end Alembic commands ###

Step7: Apply autogenerated migration script

Assuming our database is totally clean, it’s as yet unversioned. The alembic upgrade command will run upgrade operations, proceeding from the current database revision, in this example None, to the given target revision.

(alembicdemo) admin@linuxser:~/alembicdemo$ uv run alembic upgrade head
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> f153a6ff72ed, create books table within inventory db

Step8: Validate Migrations

Now, we can connect to our database and ensure that the books table is created. Also you can look athe alembic_version table which hold information about the latest applied revision.

admin@linuxser:~/alembicdemo$ mariadb -u root -p -h localhost inventory

MariaDB [inventory]> SHOW TABLES;
+---------------------+
| Tables_in_inventory |
+---------------------+
| alembic_version     |
| books               |
+---------------------+
2 rows in set (0.003 sec)

MariaDB [inventory]> SELECT * FROM alembic_version;
+--------------+
| version_num  |
+--------------+
| f153a6ff72ed |
+--------------+
1 row in set (0.001 sec)

Step9: Update and Apply revisions

We can now make some modification to our model by updating the character limits for the title column as shown below.

(alembicdemo) admin@linuxser:~/alembicdemo$ cat models.py 
from sqlalchemy import String, Text
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column

class Base(DeclarativeBase):
  pass

class Books(Base):
  __tablename__ = "books"

  isbn: Mapped[int] = mapped_column(primary_key=True)
  title: Mapped[str] = mapped_column(String(50))
  author_id: Mapped[int] = mapped_column()
  publisher_id: Mapped[int] = mapped_column()
  year_pub: Mapped[str] = mapped_column(String(4))
  description: Mapped[str] = mapped_column(Text)

Let’s now generate a new revision of our migration script based on the updates carried out to model.py file.

(alembicdemo) admin@linuxser:~/alembicdemo$ uv run alembic revision --autogenerate -m "update the char limit for title to 50 chars "
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.schemas
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.tables
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.types
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.constraints
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.defaults
INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.comments
INFO  [alembic.autogenerate.compare.types] Detected type change from VARCHAR(length=100) to String(length=50) on 'books.title'
  Generating
  /home/admin/alembicdemo/alembic/versions/443f6d004869_update_the_char_limit_for_title_to_50_.py ...  done

Once the revision migration scripts are ready we can apply those changes and validate the changes.

(alembicdemo) admin@linuxser:~/alembicdemo$ uv run alembic upgrade head
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade f153a6ff72ed -> 443f6d004869, update the char limit for title to 50 chars

If we want to revert the last applied migration and bring it back to one level below, we can run the following.

(alembicdemo) admin@linuxser:~/alembicdemo$ uv run alembic downgrade -1
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade 443f6d004869 -> f153a6ff72ed, update the char limit for title to 50 chars

If you ever wanted to look at the history all the migrations that weere applied to our database and the corresponding script you can run the following commands.

# Get history of migrations applied
admin@linuxser:~/alembicdemo$ uv run alembic history
f153a6ff72ed -> 443f6d004869 (head), update the char limit for title to 50 chars 
<base> -> f153a6ff72ed, create books table within inventory db

# Get the list of all migration scripts applied
admin@linuxser:~/alembicdemo$ ls -ltr alembic/versions/
total 8
-rw-r--r--. 1 admin admin 1202 Oct 30 13:51 f153a6ff72ed_create_books_table_within_inventory_db.py
-rw-r--r--. 1 admin admin 1167 Oct 30 14:03 443f6d004869_update_the_char_limit_for_title_to_50_.py

Hope you enjoyed reading this article. Thank you..

Implementing Database Migration and Versioning with Alembic