Alembic basics to advance

#python #tooling #database #tutorial

Alembic is a Python tool that integrates with the SQLAlchemy ORM to apply model changes to relational databases such as PostgreSQL, MySQL, and Oracle. It supports both online migrations (recommended for development and UAT environments) and offline migrations (recommended for production). However, Alembic does not support NoSQL databases like MongoDB or DynamoDB.

For example you have a db model that you would like to migrate

run the below command in the terminal
pip install alembic
alembic init migrations
This command creates a migrations directory and generates an alembic.ini file

You will find the migrations folder and the alembic.ini file created in the project directory.
Next, open the env.py file inside the migrations folder and add the DB_CONNECTION and target_metadata variables as shown in the code snippet below.

Please note: In this example, I've used SQLModel, which is a wrapper around SQLAlchemy. If you're working with SQLAlchemy directly, make sure to use SQLAlchemy's own metadata.

Once done run the below command to generate the initial revision file.
alembic revision --autogenerate -m "initial migration"

Go to migrations > version > revision file and verify the auto generated code. Resolve the error if any.

Run the below command and verify the generated tables in database
alembic upgrade head

In my case table were created successfully.

Suppose you want to remove a column from a table.
To do this, go to the database model and delete the corresponding field.
In the code snippet below, I’ve removed the gender field as an example.

Execute the following command. It will generate a revision file, as illustrated below.

alembic revision --autogenerate -m "remove gender"

Run the below command to apply the changes.
alembic upgrade head

def upgrade() -> None:
def downgrade() -> None:(to remove it on rollback)
Both the methods are crucial to perform DB upgrade and downgrade.

Scenarios Where You Use upgrade()
Adding New Tables
Example: Introducing a new projects table.
upgrade() → creates the table.
Adding a New Column
Example: Adding a salary column to the employee table.
upgrade() → uses op.add_column.
Changing Column Type or Constraints
Example: Increasing phone column size.
upgrade() → uses op.alter_column.
Adding Foreign Keys / Indexes
Example: Linking salary.employee_id to employee.id.
upgrade() → uses op.create_foreign_key.
Renaming Tables or Columns
Example: Renaming job_title to position.
upgrade() → uses op.alter_column(new_column_name=...).

Scenarios Where You Use downgrade()
downgrade() is the exact reverse of upgrade(), used when:
Rolling Back a Failed Deployment
You deployed a migration that caused an issue.
downgrade() → safely reverts to the previous state.
Reverting Experimental Features
Example: You added a gender column but later decide to remove it.
downgrade() → drops the column.
Undoing Schema Changes During Development
Example: You added an index but want to test performance without it.
downgrade() → drops the index.
Synchronizing Database With Older Code Versions
You need to roll back to an earlier release of your application.
downgrade() → brings DB schema back in sync with that release.

You add a gender column (upgrade).
Then business decides not to store gender.
You write another migration where:
upgrade() → drops gender column.
downgrade() → re-adds it (in case you need to revert).

Run the command to re-add the column.
alembic downgrade 678f33609bf3
Make sure you use down revision number