Using Alembic with SQLAlchemy

supporting code can be found here https://github.com/JTSG1/alembic-sqlalchemy-tutorial

Alembic is a database version control framework closely associated with SQLAlchemy, an independent Python ORM. I have used ORMs with Python in the past but more specifically it has been the one that is built into Django. For recent projects I have made a point of exploring different frameworks, specifically those with less included out of the box.

This lead me to FastAPI, a RESTful API framework for python. A couple of stand out features that has encouraged me to explore it more are its speed and the build in support for OpenAPI Specifications. This lead to the very valid question of how to handle my database layer and that introduced me to SQLAlchemy, that doesn't include version control as part of its core offering. I am a big fan of DB Version Control for very obvious reasons and the desire to have the functionality lead me to Alembic.

Project setup

The set up is pretty straight forwards, the first thing to ensure is that we have the required dependencies installed. I like to keep these to Python Virtual Environments because it makes sense to scope dependencies to a project.

$ mkdir db-tutorial
$ cd db-tutorial
$ python3 -m venv my-venv
$ source my-venv/bin/activate
$ pip install sqlalchemy alembic psycopg2-binary

We are creating a new project directory above and then creating a virtual environment using the "venv" module, we are calling the within it then activating it. Once that is done, we are getting the required dependencies from pip.

Hint: I tend to freeze the dependency list into a requirement.txt file when I install something new, this is important for collecting the correct dependencies down the line

$ pip freeze > requirements.txt

We are now good to go.

Initialising SQLAlchemy and Creating a Model

Initialise

First task is to do the require configuration for SQLAlchemy.

Lets create a module in at the root of our application that will hold our DB Connection Config and a model

$ mkdir persistence
$ cd persistence
$ touch __init__.py
$ touch db.py
$ touch models.py

open db.py in your favourite editor:

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import os

# Saving connection details in environment variables
# note storing password in plain text in this way would be bad in a production
# system
DB_USER = os.environ.get("DB_USER")
DB_PASSWORD = os.environ.get("DB_PASSWORD")
DB_HOST = os.environ.get("DB_HOST")
DB_PORT = os.environ.get("DB_PORT")
DB_NAME = os.environ.get("DB_NAME")

# Build the connection string
SQLALCHEMY_DATABASE_URL = f"postgresql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"

# create the database connection
engine = create_engine(SQLALCHEMY_DATABASE_URL, connect_args={})
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# Get the base model class - we can use this to extend any models
Base = declarative_base()

# Provide a mechanism to retrieve the database from within the rest of the application
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

The above code is essential boilerplate to configure SQLAlchemy. I have not hard coded any connection details in this snippet and instead would define them as Environment Variables in my run profile of VSCode (Or in a container).

Create a basic model

from sqlalchemy import Column, Integer, String
from persistence.db import Base
from sqlalchemy import  Column, Integer

# a model to hold car data
class Car(Base):

    __tablename__ = "cars"
    id = Column(Integer, primary_key=True, index=True)
    make = Column(String)
    model = Column(String, unique=True, index=True)
    colour = Column(String)

The above is a basic model that will serve the purpose of this tutorial

Note - At this point it is worth noting that I will use a Postgres database running within WSL but you aren't restricted to that for your backend - if SQLAlchemy supports it you can use it.

We are now in a good place, we have created the code to initialise the database connection and created a model to apply to it.

Init alembic and config

Now we want to init our version control system for our Database, lets start by opening a terminal and ensuring we are in the root of the project

$ alembic init alembic

This will initialise the alembic config, it creates a folder called "alembic" in the project root. It will also create "alembic.ini" in the same place.

alembic.ini contains a property that can be manipulated to provide the connection string of your DB to the framework however, I don't think this is the best way to go, we have already defined our connection string as part of our SQLAlchemy config and we are wanting to use the values stored in Environment Variables when the application is started. As such, we will defined our connection string in the generated /alembic/env.py file

from logging.config import fileConfig
from sqlalchemy import engine_from_config
from sqlalchemy import pool
from alembic import context
# import your DeclarativeBase
from persistence.db import Base  # adjust this to fit your actual module path
from persistence.db import SQLALCHEMY_DATABASE_URL

from persistence.models import Car

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config
# set to connection string defined in the db_base.py file
config.set_main_option("sqlalchemy.url", SQLALCHEMY_DATABASE_URL)

target_metadata = Base.metadata  # use your DeclarativeBase's metadata

Mostly this file can stay how it is when generated however there are a couple of elements that need considering:

• As discussed above, we are setting the sqlalchemy.url property programmatically to ensure we don't repeat thing to that end, we are importing SQLALCHEMY_DATABASE_URL from from db.py

• We are also importing our models to ensure Alembic can see them

Once this is done we can try and make the initial commit to the versioning

$ alembic revision --autogenerate -m "initial"

All being well, outputs with messages similar to

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'cars'
INFO  [alembic.autogenerate.compare] Detected added index ''ix_cars_id'' on '('id',)'
INFO  [alembic.autogenerate.compare] Detected added index ''ix_cars_model'' on '('model',)'
  Generating /home/james/python/db-tutorial/alembic/versions/0d25ea6c8183_initial.py ...  done

This has not yet created the table in the database but it has committed it to an internal table that alembic uses to track versioning and has created the migrations under the alembic/versions folder. If you look into that file created in there you will see your models being described.

To push the changes to the database you can use the command

$ alembic upgrade head

If you look inside your database, you will see that a cars table has been created.

The takeaway

Your SQLAlchemy based database schema is now version controlled. Not only can you apply migrations as you make changes to your models but you can also go backwards too

$ alembic downgrade -1

would take you back one revision, if you put -2 it would go back two revisions and so on and so forth.

In conclusion, we've successfully navigated the landscape of database version control using Alembic, a robust framework closely entwined with the powerful SQLAlchemy ORM.

By venturing beyond the familiar confines of built-in Django ORM, we've embraced the flexibility of FastAPI and harnessed its speed and native support for OpenAPI Specifications. With a meticulous project setup, we crafted SQLAlchemy configurations, initialized database connections, and defined models for our journey.

The initiation of Alembic brought version control to our database schema, offering not only seamless migrations but also the ability to gracefully navigate backward when needed. As we delve deeper into the realm of SQLAlchemy and Alembic with FastAPI, this tutorial serves as a foundational guide, laying the groundwork for reliable, iterative application development. Stay tuned for more insights and explorations in the exciting intersection of Python, databases, and web frameworks.

Thanks for joining in, and I hope you found this journey into database version control both enlightening and useful!