Lemona

DB catalog exporter in SQLAlchemy form.

Lemona is a tool that can make DB version management more convenient with alembic.

Lemona supports migration of each database using sqlalchemy's metadata.

Dependency

• python3.6

• sqlalchemy

• alembic(optional)

How to use

Postgresql

Alembic

Go to the directory where you want to run alembic version control and execute the following command.

I created a project name as alembic for explanation. You can create it with the name you want.

$ alembic init myproject 

Now, the alembic version control project is created with the following directory structure.

myproject-directory-tree/
├── alembic.ini
  └── myproject
    └── versions
    └── env.py
    └── README
    └── script.py.mako

After creating myproject, you can see the following in the env.py file. You should put the information corresponding to the database model.

In this example, we use the sqlalchemy's metadata to get schema information.

# env.py
# 
# ...
# 
# add your model's MetaData object here
# for 'autogenerate' support
# target_metadata = None
import sys
sys.path.append('../Lemona')
import meta_model
target_metadata = meta_model.DevBase.metadata
# 
# ...
# 

※ Importance

You must execute the command with the name "initial_migraion". If you do not naming like this, you can not parse the sequence part of postgresql properly.

$ alembic revision --autogenerate -m "initial_migration"

In the alembic.ini file, put the url of the database to be version controlled as alembic.

# the output encoding used when revision files
# are written from script.py.mako
# output_encoding = utf-8

sqlalchemy.url = driver://user:pass@localhost/dbname

PostgreSQL requires that you set up a schema when there are multiple schemas in the database.

You can fill in the following settings in meta_model.py.

SCHEMA_NAME = ''
# DEV_DATABASE: Development Database Information
DEV_DATABASE = {'user': '',
                'password': '',
                'host': '',
                'database': ''}

# PROD_DATABASE: Product Database Information
PROD_DATABASE = {'user': '',
                 'password': '',
                 'host': '',
                 'database': ''}

Alembic does not provide a migration code to create a sequence.

So the meta_model.py module creates a sequence using sqlalchemy.

$ python3 meta_model.py

Now you can run the migration code generated by alembic with the following command.

$ alembic upgrade head

More info

sqlAlchemy : http://alembic.zzzcomputing.com/en/latest/

Alembic : https://www.sqlalchemy.org/