Maintaining SQLAlchemy Models Like Django ORM with Alembic

Andrew Jones

I've often wanted the simplicity of django's model management in projects that didn't have any sort of web interface. Here's a guide to how I've set that up in the past
​
Once a model is defined in a django database, the two commands below will create and apply any changes to that database.

manage.py makemigrations
manage.py migrate

This led me to investigate the ORM capabilities of the popular Python sqlalchemy package and its migrations management tool alembic.
​

Getting started

Firstly, you're going to need to install some tools. These can be done with:

pip install sqlalchemy alembic

For a production system, these should have their versions frozen added to the relevant requirements.txt or setup.py file.
​

Creating your mappings

Next, you'll need to create some table definitions. The code below needs to be added to the ./mappings.py module in your project root. This definition will create the ORM mapping for the two tables user and address, with columns as defined by the classes.

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
​
Base = declarative_base()
​
class User(Base):
   __tablename__ = 'user'
​
   id = Column(Integer, primary_key=True)
   name = Column(String)
   fullname = Column(String)
   nickname = Column(String)
​
   addresses = relationship("Address", backref="user", order_by="Address.id")
​
class Address(Base):
   __tablename__ = 'address'
​
   id = Column(Integer, primary_key=True)
   user_id = Column(ForeignKey('user.id'))
   email_address = Column(String)

​

Setting up alembic

Once you've got your requirements installed, you'll need to set up alembic in your repository. Luckily, there's a convenient command which does most of the work for you:

alembic init alembic

You'll notice a new ./alembic directory and ./alembic.ini files. You now need to wire alembic to connect to your database and discover your models.

At this point you should have a folder structure like this:

.
├── alembic/
│   ├── env.py
│   ├── README.md
│   ├── script.py.mako
│   └── versions/
├── alembic.ini
└── mappings.py

​

Connect to your database:

The sqlalchemy.url needs to point to your local database. For example, a local sqlite3 database:

sqlalchemy.url = sqlite:///local.db/

Note: This variable is pulled through in the generated env.py and therefore can be changed to integrate any other configuration mechanism available to python.
​

Target the ORM Mappings

At the moment, alembic is configured to generate empty migrations. You'll now need to edit the alembic/env.py module to automatically generate database migrations when changes are made to the defined mappings. Specifically, the target_metadata variable needs to get changed to accept the metadata list of the Base object used by the mappings. We do this by updating the mappings we created earlier with the following:

target_metadata = None

to

from mappings import Base
...
target_metadata = Base.metadata

​
More details available
https://alembic.sqlalchemy.org/en/latest/tutorial.html
​

Generating and Applying Migrations

Migrations can now be generated simply by running the command:

export PYTHONPATH=${PYTHONPATH}:$(pwd)
alembic revision --autogenerate --message "Create the example Database"

Note: PYTHONPATH needs to be set if the project isn't set up as a python package. For more information on setting up a python package see this link:
https://packaging.python.org/tutorials/packaging-projects/

You'll see a new file has been generated in the versions folder and the new folder structure should look something like this:

.
├── alembic/
│   ├── env.py
│   ├── README.md
│   ├── script.py.mako
│   └── versions/
│       └── 79d6e3347fd4_create_the_example_database.py
├── alembic.ini
└── mappings.py

These migrations can be applied with the command:

alembic upgrade head

Verify the sqlite database with the command:

sqlite3 local.db ". tables"

then display the generated tables with:

address
alembic_version # Auto created by alembic to keep track of applied migrations.
user          

​
These shadow the two commands which would be run if using djangos ORM which achieves our goal to allow the database setup to be versioned and maintained as code.

Andrew Jones

I hope you find this guide useful. If you have any questions, feel free to drop us a message.