I don't want to get tired of handwriting the upgrade and downgrade scripts for migration, so I introduced Alembic.
Alembic is a nice tool that automatically generates upgrade and downgrade diffs from SQLAlchemy migration scripts and server status.
Installation ends with pip
pip install sqlalchemy
pip install alembic
It is necessary to install the SQL driver for python according to the SQL you are using (you need to be able to connect to the SQL server in order to make a difference with the server).
This time I used it in MySQL.
pip install pymysql
Create a working directory with the alembic init command.
alembic init [Working directory name]
The alembic.ini file and the specified working directory are created. There is a configuration file called env.py in the working directory, so edit this file and two files, alembic.ini.
First, alembic.ini describes the settings for connecting to MySQL. Rewrite sqlalchemy.url.
sqlalchemy.url = mysql+pymysql://username:password@hostname:port/DB name
※:Port name is optional
You need to specify up to the DB name. Since you can prepare multiple ini files and use them properly at runtime, you can handle it by preparing an ini file for each DB.
Then rewrite env.py in your working directory. Since there is a comment line # add your model's MetaData object here, register an instance of the MetaData class of the DB definition script for SQLAlchemy for the variable target_metadata under it.
It may be a little confusing, so first of all, the DB definition script for the sample SQLAlchemy
sample.py
from sqlalchemy import MetaData, Table, Column, Integer, String, DateTime, Float, Boolean, Text
from sqlalchemy.dialects.mysql import TINYINT, SMALLINT
meta = MetaData()
Table(
'status', meta,
Column('account_id', Integer, primary_key=True, autoincrement=False),
Column('hp', SMALLINT, nullable=False, server_default='30'),
Column('weapon_id', SMALLINT, nullable=False, server_default='1'),
Column('weapon_lv', TINYINT, nullable=False, server_default='0'),
Column('armor_id', SMALLINT, nullable=False, server_default='1'),
Column('armor_lv', TINYINT, nullable=False, server_default='0'),
)
If there was,
env.py
# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
import sample
target_metadata = sample.meta
Write like this.
In order to import the sample.py file, you need to specify the location of the file in PYTHONPATH. Even if it is in the current state, it is necessary to specify.
PYTHONPATH=. alembic revision --autogenerate
When preparing multiple alembic.ini, change the file name and specify it with the --config option.
PYTHONPATH=. alembic --config ini file revision--autogenerate
By adding --autogenerate, the difference between the current server and the specified SQLAlchemy script will be taken in the directory called versions in the working directory, and the script including upgrade and downgrade will be automatically generated.
After that, edit the file that is the source of migration specified in the ini file, and generate a difference file with the alembic revision command. Even if you make a mistake and create a script with strange settings, you can say that if you just created it, you did not just delete the generated file.
Scripts created with alembic revision can be applied to the server using alembic upgrade. You can upgrade or downgrade to any revision you like, but most of the time you will update with the latest version. Specify head to update to the latest version.
PYTHONPATH=. alembic --config ini file upgrade head
alembic is a simple version control system that creates a table called alembic_version in the DB and stores the file name (excluding the last _) in version_num.
So, if you set the latest file name in versions to version_num after matching the DB to the latest version, there will be no version deviation.
If you delete the script in versions under the working directory and the alembic_version table in the DB, you can assume that there was no setting by alembic.