CNLearn FastAPI 3 - Alembic Migrations for the Users Table

Shall we create some users?

So much to cover today. So much that I might split it into two posts. This one will cover using alembic to create the migrations for our database for the users table and the next one will be about implementing authentication (some-very-what based on the Full stack FastAPI PostgreSQL template here).

Alembic Migrations

What is Alembic? It’s a lightweight database migration tool. What’s a database migration tool? It’s a tool used to handle database migrations obviously. Ok what is a database migration? Well, a database migration (usually) changes the schema of a database: add columns, constraints, adding tables, updating tables, etc. The migration essentially performs that while also having a way to roll back the changes. So how do we use Alembic? Well, let’s install it first.

pip install alembic

Then, since alembic doesn’t yet support an async way, let’s also install a sync driver for postgres.

pip install psycopg2

Ok. Now in our root directory, let’s do:

alembic init alembic

That will create an alembic folder and an alembic.ini file. In the alembic.ini file I remvoved the sqlalchemy.url line as I will define it in the file in the alembic directory. So let’s go to that one now. I won’t go through all of it now, I would want to have a nice long SQLAlchemy series, but what I am doing is importing our settings module.

from app.settings.base import settings
def get_url():
    return settings.SQLALCHEMY_POSTGRES_URI

I am also defining a get_url() function which will return that. You might ask, why not just use the string in the places where it’s needed? Well, this way we only have to make one change in the future rather than replacing multiple ones. Also notice that unlike in the previous post where we replaced part of the URI string with async, we are not doing that here.

def run_migrations_offline():
    """Run migrations in 'offline' mode.

    This configures the context with just a URL
    and not an Engine, though an Engine is acceptable
    here as well.  By skipping the Engine creation
    we don't even need a DBAPI to be available.

    Calls to context.execute() here emit the given string to the
    script output.

    url = get_url()
        dialect_opts={"paramstyle": "named"},

    with context.begin_transaction():

def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    configuration = config.get_section(config.config_ini_section)
    configuration["sqlalchemy.url"] = get_url()
    connectable = engine_from_config(

    with connectable.connect() as connection:
            connection=connection, target_metadata=target_metadata, compare_type=True

        with context.begin_transaction():

if context.is_offline_mode():

Then we have that in the rest of the file which is SQLAlchemy/Alembic specific stuff. OK now we have configured the settings. Let’s create a migration script. In the root directory

alembic revision -m "create users table"

A file was generated for us in the alembic/versions/ folder. Let’s have a look. By default, it’s mostly empty with two main functions: upgrade() and downgrade().

"""create users table

Revision ID: 1b40d5775cd1
Create Date: 2021-05-26 14:28:35.729792

from alembic import op
import sqlalchemy as sa

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

def upgrade():

def downgrade():

The two functions are responsible for setting up the changes to the database (former to make the changes and the latter to go back). While the latter isn’t necessary, it’s recommended. Ok let’s add the code to create the users table.

def upgrade():
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("full_name", sa.String(), nullable=True),
        sa.Column("email", sa.String(), nullable=True),
        sa.Column("hashed_password", sa.String(), nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=True),
        sa.Column("is_superuser", sa.Boolean(), nullable=True),
    op.create_index(op.f("ix_user_email"), "user", ["email"], unique=True)
    op.create_index(op.f("ix_user_full_name"), "user", ["full_name"], unique=False)
    op.create_index(op.f("ix_user_id"), "user", ["id"], unique=False)

This revision is taken from here but let’s explain it in more detail. create_table() is an Alembic directive used to issue a “create table” instruction. We have a few columns including id, full_name, email, hashed_password (never save the password directly….), is_active and is_superuser. The last one will be used when creating the “admin” of the site. Then, we also set the Primary Key to be the id. It’s also possible to do that by using the primary_key flag directly on a specific column. What about the next three lines?

    op.create_index(op.f("ix_user_email"), "user", ["email"], unique=True)
    op.create_index(op.f("ix_user_full_name"), "user", ["full_name"], unique=False)
    op.create_index(op.f("ix_user_id"), "user", ["id"], unique=False)

We are creating three indices in our database for quicker lookup. We’re not going to go into database indices here, feel free to read some information on them in the PostgreSQL documentation. We create an index for the user_email named “ix_user_email” which refers to the “user” table and the “email” column. We also indicate that it’s an unique index. We do a similar thing for the full_name and the id column but those are not unique. That’s all we need to create the table in the database. What about the downgrade operation?

def downgrade():
    op.drop_index(op.f("ix_user_id"), table_name="user")
    op.drop_index(op.f("ix_user_full_name"), table_name="user")
    op.drop_index(op.f("ix_user_email"), table_name="user")

It’s basically the reverse. We drop the indices and then drop the tables. Finally, let’s run our migration.

alembic upgrade head

And that’s it! We have our database table for users. In the next post we’ll look at how we can register/login/etc and the crud operations associated with them. Until then!

Also, the commit for this post is here.