Skip to content

Migration 101

Overview

Migration are the process for initation and update/delete for objects in database.

E.g:

  • Change schema name

  • Updagrade permission for group of people

  • Changea table columns name or data types.

To make sure we sync up between all the environments, it requested to have a progress to build/sync the metadata with the update environments.

Document to help you the process for intergration at production levels and practice with Docker

The context:

  • There are 2 envs: Dev/Staging

  • Related to 2 schema: s*dev/ s_prod with s*\* is prefix of schema

Progress

Establish the installment

requirement.txt
...
alembic>=4.0.0
...

Create Metadata

Change the database

Transfer data from old table too new table

https://stackoverflow.com/questions/18726527/fetch-table-values-using-alembic-and-update-to-another-table

Test current database revision is at head(s) A recipe to determine if a database schema is up to date in terms of applying Alembic migrations. May be useful for test or installation suites to determine if the target database is up to date. Makes use of the MigrationContext.get_current_heads() as well as ScriptDirectory.get_heads() methods so that it accommodates for a branched revision tree:

from alembic import config, script
from alembic.runtime import migration
from sqlalchemy import engine


def check_current_head(alembic_cfg, connectable):
    # type: (config.Config, engine.Engine) -> bool
    directory = script.ScriptDirectory.from_config(alembic_cfg)
    with connectable.begin() as connection:
        context = migration.MigrationContext.configure(connection)
        return set(context.get_current_heads()) == set(directory.get_heads())

e = engine.create_engine("mysql://scott:tiger@localhost/test", echo=True)
cfg = config.Config("alembic.ini")
print(check_current_head(cfg, e))

See also MigrationContext.get_current_heads() ScriptDirectory.get_heads()

Add metadata to docker-compose.yml file

Change command to

    commands: [alembic head && make run]

Make Docker Tutorial Learning

make up
make down
make up-refresh
make down-refresh
make db-gen-refresh
DATABASE_URI=XXX make db-check
DATABASE_URI=XXX make db-migration

Reference

https://www.compose.com/articles/schema-migrations-with-alembic-python-and-postgresql/