First Revision

In this article, we demonstrate how to manage PostgreSQL database changes using Alembic. Initially, our database was empty, with no defined tables:

SELECT * FROM public.posts
ORDER BY id ASC

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:

alembic --help

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:

alembic revision -m "create posts table"

This command generates a new file in the Alembic versions folder. The output will resemble:

Generating C:\Users\sanje\Documents\Courses\fastapi\alembic\versions\cfcc4fd02d18_create_posts_table.py .

Inside this generated file, you will see a structure similar to the following:

from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = 'cfcc4fd02d18'
down_revision = None
branch_labels = None
depends_on = None

def upgrade():
    pass

def downgrade():
    pass

To explore more options for the alembic revision command, run:

alembic revision --help

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:

from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = 'cfcc4fd02d18'
down_revision = None
branch_labels = None
depends_on = None

def upgrade():
    op.create_table(
        'posts',
        sa.Column('id', sa.Integer(), nullable=False, primary_key=True),
        sa.Column('title', sa.String(), nullable=False)
    )

def downgrade():
    op.drop_table('posts')

After saving the migration file, apply the latest revision by running:

alembic upgrade <revision_id>

Replace <revision_id> with your revision identifier (e.g., cfcc4fd02d18). Alternatively, you can upgrade to the latest revision using:

alembic upgrade head

This command applies the migration, creating the posts table. Verify the changes by refreshing your PostgreSQL interface with:

SELECT * FROM public.posts
ORDER BY id ASC

You should now see that the posts table includes two columns: id (the primary key, non-nullable) and title (non-nullable).

Additionally, when you inspect the table in pgAdmin, the defined columns and associated constraints are clearly visible:

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:

SELECT * FROM public.alembic_version
ORDER BY version_num ASC

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.