Skip to main content
In this article, we demonstrate how to manage PostgreSQL database changes using Alembic. Initially, our database was empty, with no defined tables:
We started building our application by creating tables directly in the PostgreSQL database as required. For instance, during the early stages, we created a “posts” table without handling user creation, password hashing, or establishing user relationships. Later, with the implementation of CRUD operations for posts and the introduction of user registration, we added a “users” table and modified the “posts” table to include a foreign key. Eventually, a “votes” table was added, complete with its own set of foreign keys. Now, with Alembic integrated into our workflow, we will walk through a controlled, step-by-step process to manage these database migrations.

​
Exploring Alembic Commands

Begin by reviewing the available Alembic commands. Running the help command:
displays multiple options. One of the most frequently used options is the revision command, which is similar to a git commit message. It allows you to attach a human-readable message to each schema change. For example, to create a revision for the posts table, run:
This command generates a new file in the Alembic versions folder. The output will resemble:
Inside this generated file, you will see a structure similar to the following:
The upgrade() function is used to apply changes, while the downgrade() function allows you to roll back those changes. Always add the necessary logic to these functions based on your migration requirements.
To explore more options for the alembic revision command, run:
Some key options include:

​
Creating the Posts Table

After creating the revision file, add the logic to create the posts table in the upgrade() function. We’ll define two essential columns:
  • An id column of type Integer, which is non-nullable and serves as the primary key.
  • A title column of type String, also non-nullable.
Below is the updated migration script:
After saving the migration file, apply the latest revision by running:
Replace <revision_id> with your revision identifier (e.g., cfcc4fd02d18). Alternatively, you can upgrade to the latest revision using:
This command applies the migration, creating the posts table. Verify the changes by refreshing your PostgreSQL interface with:
You should now see that the posts table includes two columns: id (the primary key, non-nullable) and title (non-nullable).
The image shows a Visual Studio Code interface with Python code for a database migration script using Alembic. The code editor displays a function definition, and a tooltip provides information about the Operations class.
Additionally, when you inspect the table in pgAdmin, the defined columns and associated constraints are clearly visible:
The image shows a pgAdmin interface with a table named "posts" being edited. It displays columns for "id" and "title" with their data types and constraints.

​
Tracking Migrations with Alembic

Alembic maintains a version table in your database that keeps track of all applied revisions. To inspect this version table, execute:
Do not delete the Alembic versioning table. It is crucial for tracking schema changes and ensuring the consistency of your database migrations.

​
Conclusion

This guide demonstrated how to create a simple “posts” table using Alembic, including both the upgrade and downgrade migration paths. For additional operations—such as altering columns, adding constraints, or working with computed defaults—refer to the Alembic API documentation. By managing database migrations with Alembic, you ensure a controlled and consistent way to apply schema changes, making your database evolution both predictable and reversible.

Watch Video