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.