Database handling using Python Alembic and SQL Alchemy

Suppose we are working on a Python backend application, and we often make changes that require database updates. Alembic and SQLAlchemy are excellent solutions for using migrations in Python. They both provide a modern approach to creating and tracking all changes to your databases. Alembic is a database migrations tool that works together with SQLAlchemy.

We can initialize Alembic in our project through this command:

alembic init migrations

We can see a new folder called migrations under the project folder. This folder will be used by Alembic to manage the migrations, revisions, and configurations.

We need to first understand the Python SQLAlchemy and what Alembic does. What does an alembic upgrade and downgrade do?

SQLAlchemy is like an "Object Relational Mapper." When a Python class is created and defined, SQLAlchemy takes my Python class and "maps" it to a database.

Alembic works together with SQLAlchemy to modify our database without overwriting or deleting any data. Suppose we modified our schema and want to reflect the new changes. Now, Alembic will look at the old database, then compare it to the new one we generate, and will perform the changes on our target database table. Here's an alembic migration demo file:

"""Add new column 'status' to users table

Revision ID: 123456789abc
Revises: abcdef123456
Create Date: 2025-12-02 18:00:00
"""

from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = "123456789abc"
down_revision = "abcdef123456"
branch_labels = None
depends_on = None


def upgrade() -> None:
    """
    Upgrade migrations: apply changes to the database schema.
    Example: Adding a new column.
    """
    op.add_column(
        "users",
        sa.Column("status", sa.String(length=20), nullable=True),
    )


def downgrade() -> None:
    """
    Downgrade migrations: reverse the upgrade changes.
    Example: Removing the column added in upgrade().
    """
    op.drop_column("users", "status")

Let's understand the alembic upgrade and downgrade command:

Upgrade the database forward by exactly one migration step from the current version. Suppose our migrations in version files are the following:
A -> B -> C

Our current head in versions files is C but B in the database. Then type this command:

alembic upgrade +1

will move us from B → C.

Suppose our current version is C and we want to downgrade the database back by one migration step. Then we can type the following command:

alembic downgrade -1

moves us from C → B

Let's say I am working in a branch where I have made some changes, and these changes affected some tables in the database by inserting a new column. Now I want to work on another branch where this feature is not implemented. In that case, if I run the project, I will get an error from the backend since the database migration occurred when I worked on the previous feature. And if we downgrade, then it will work.

Every time we make changes to the schema, Alembic will autogenerate a file with a version number that stores all the changes made to the database, as well as a downgrade command to revert changes.

Sometimes, more than one alembic file is generated, which is sometimes not required at all. We can simply merge them into one. To do this, we can follow the process described below.

First, we can type this command:

alembic history

We will see something like:

a1->b2 -> c3 -> d4 -> e5 -> f6 -> g7 -> h8 -> i9 -> j10 (head)
a1 (base).

Suppose we want to merge h8,i9 and j10 into one migration file.

We will type the following command:

alembic downgrade g7

g7 will be the head now.

Now we will move these three files into another backup folder. And then run this command:

alembic revision --autogenerate -m "squashed_h8_i9_j10_migrations"

Now we can see a new migration file generated.
Then we will give this command to upgrade the head:

alembic upgrade head

There is another approach that we can also follow. If we open any migration file, we will see that every file has a downgrade and an upgrade function. So, we can generate a new file first:

alembic revision --autogenerate -m "combined-features"

Then we can simply take the code of the upgrade and downgrade functions from all the files that we want to remove and update this new file with this code. Then change the downrevision of that file to the revision of the previous file, which is the HEAD, and then do the upgrade.

Sometimes it fails because Alembic still references a missing revision in the database. We can force stamp with --revision that exists:

alembic stamp 26429623e713

Let's discuss some issues.

Scenario 1:
Sometimes Alembic throws an error like the head (b886dffa554d) does not exist, or the requested revision was not found. This usually happens when the database has an old or incorrect revision stored in alembic_version.

But our codebase (the alembic/versions directory) now uses a different head, such as 26429623e713.

To fix the mismatch, we can manually update the revision stored in our database so it matches the latest head from our codebase.

We can safely correct this using the following SQL:

UPDATE alembic_version
SET version_num = '26429623e713'
WHERE version_num = 'b886dffa554d';

To check the version

SELECT * FROM alembic_version;

Scenario 2:
Suppose there are 2 developers, dev1 and dev2. They started working on two separate branches with 2 new features, corresponding to cloning the main branch. Suppose both made changes to their branch.

In main branch: a->b(head)

dev1: a->b->d(head)
dev2: a->b->c(head)

While merging :

dev1: a->b->d(head) merged to main

dev2: a->b->c(head) [error: version d doesn’t exist]

So, first we can rebase the dev1 branch now that the head is d. While adding dev2's feature, we can make the dev2's head, which is c, next to dev 1, which is d now. And push the changes to the dev1 branch. And then merge the dev1 PR. But this is sometimes problematic. Because we are rewriting migration history, which is sometimes dangerous.

If migrations modify the same tables/columns, rebase may cause:

1. inconsistent database states

2. duplicated constraints

3. partial schema drift

4. Alembic does not support manually reordering migrations to resolve divergence.

So, here is another solution:

If dev1 merges first, the main branch becomes:

a -> b -> d (head)

Next pull the latest main into dev2’s branch:

git checkout dev2
git pull origin main

Now dev2 has both migrations:

a -> b -> d (main)
a -> b -> c (dev2)

Alembic will detect two heads: d, c.

Run

alembic heads

Create a merge migration:

alembic merge -m "Merge dev1 and dev2 migrations" d c

A new merge file is created. Now we can push it to dev2's branch and create a PR.