Managing Database Migrations with Alembic

As applications evolve, their data models also evolve. Developers may need to add new fields, change existing ones, or introduce completely new tables to support new features. In SQL-based systems, these changes must also be reflected in the database schema. If the code changes but the database does not, the application will break or behave incorrectly.

Alembic is a tool used together with SQLAlchemy to manage these database schema changes in a controlled and safe way. Instead of manually writing SQL scripts every time a change happens, Alembic helps developers track, generate, and apply these changes step by step.

The process starts when the developer updates the SQLAlchemy model. For example, a Movie table might originally have only id and title, and later the developer adds a new field such as rating. At this point, only the Python model has changed, not the database itself.

To detect and prepare this change for the database, the developer runs:

alembic revision --autogenerate -m "add rating column"

This command compares the current SQLAlchemy models with the existing database schema. Based on the differences, Alembic automatically generates a migration file inside the alembic/versions/ folder.

Inside this file, Alembic writes Python code that describes the database change. For example, it may include a function like:

def upgrade():
    op.add_column(
        "movies",
        sa.Column("rating", sa.Integer())
    )

At this stage, the developer does not trust the file blindly. They open and review it manually to make sure everything is correct. They check whether the correct table is being modified, whether the column name is correct, and whether the data type matches the intention.

After confirming that the migration is correct, the developer applies it to the database using:

alembic upgrade head

This command executes the migration and updates the actual database schema. After this step, the database and the SQLAlchemy models are synchronized again.

In summary, the workflow is: the developer first changes the model, then generates a migration file with Alembic, reviews the generated code inside the alembic/versions/ folder, and finally applies the changes to the database using the upgrade command. This process ensures that database changes are consistent, version-controlled, and safe across different environments.