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 env.py 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()
context.configure(
url=url,
target_metadata=target_metadata,
literal_binds=True,
dialect_opts={"paramstyle": "named"},
)
with context.begin_transaction():
context.run_migrations()
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(
configuration,
prefix="sqlalchemy.",
poolclass=pool.NullPool,
)
with connectable.connect() as connection:
context.configure(
connection=connection, target_metadata=target_metadata, compare_type=True
)
with context.begin_transaction():
context.run_migrations()
if context.is_offline_mode():
run_migrations_offline()
else:
run_migrations_online()
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
Revises:
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():
pass
def downgrade():
pass
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():
op.create_table(
"user",
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),
sa.PrimaryKeyConstraint("id"),
)
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")
op.drop_table("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.