README.md

For developers

Initial setup

By executing the following command, migration will be performed to the current version.

$ ./run_migration.sh init

Generate migration script

Once you have changed the DB model, create a migration script.

Prerequisites

• Complete the initial setup.

Auto-Generate script

The following steps will automatically generate a migration script.

$ ./run_migration.sh generate $file_suffix

e.g.) ./run_migration.sh generate v1.0.0

Upgrade

$ ./run_migration.sh upgrade

Downgrade

To revert to the initial state, execute the following command.

$ ./run_migration.sh downgrade

To revert to the previous version, use -1.

$ ./run_migration.sh downgrade -1

Notice

In the following cases, you will need to manually modify the autogenerated script.

If column name changed.

• CREATE/DROP script will be generated automatically.
• By executing the auto-generated script, the data in the existing columns will be deleted.

e.g.)
Before(Auto-generated)

from alembic import op
import sqlalchemy as sa

from app.database import get_db_schema

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    connection = op.get_bind()

    op.add_column('idx_transfer', sa.Column('from_address', sa.String(length=42), nullable=True), schema=get_db_schema())
    op.drop_index('ix_idx_transfer_transfer_from', table_name='idx_transfer', schema=get_db_schema())
    op.create_index(op.f('ix_idx_transfer_from_address'), 'idx_transfer', ['from_address'], unique=False, schema=get_db_schema())
    op.drop_column('idx_transfer', 'transfer_from', schema=get_db_schema())
    # ### end Alembic commands ###

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    connection = op.get_bind()

    op.add_column('idx_transfer', sa.Column('transfer_from', sa.VARCHAR(length=42), autoincrement=False, nullable=True), schema=get_db_schema())
    op.drop_index(op.f('ix_idx_transfer_from_address'), table_name='idx_transfer', schema=get_db_schema())
    op.create_index('ix_idx_transfer_transfer_from', 'idx_transfer', ['transfer_from'], unique=False, schema=get_db_schema())
    op.drop_column('idx_transfer', 'from_address', schema=get_db_schema())
    # ### end Alembic commands ###

After(Manually modify)

from alembic import op
import sqlalchemy as sa

from app.database import get_db_schema

def upgrade():
    op.alter_column('idx_transfer', 'transfer_from', new_column_name='from_address', existing_type=sa.String(length=42), schema=get_db_schema())
    
    if op.get_bind().dialect.name == "postgresql":
        schema = get_db_schema()
        schema = f"{schema}." if schema is not None else ""
        op.execute(f"ALTER INDEX {schema}ix_idx_transfer_transfer_from RENAME TO ix_idx_transfer_from_address")
    elif op.get_bind().dialect.name == "mysql":
        op.execute("ALTER TABLE idx_transfer RENAME INDEX ix_idx_transfer_transfer_from TO ix_idx_transfer_from_address")

def downgrade():
    op.alter_column('idx_transfer', 'from_address', new_column_name='transfer_from', existing_type=sa.String(length=42), schema=get_db_schema())
    if op.get_bind().dialect.name == "postgresql":
        schema = get_db_schema()
        schema = f"{schema}." if schema is not None else ""
        op.execute(f"ALTER INDEX {schema}ix_idx_transfer_from_address RENAME TO ix_idx_transfer_transfer_from")
    elif op.get_bind().dialect.name == "mysql":
        op.execute("ALTER TABLE idx_transfer RENAME INDEX ix_idx_transfer_transfer_from TO ix_idx_transfer_from_address")

If column constraint changed.

• The execution of the auto-generated scripts may fail.
• For example, constrains changed Nullable to NotNull, etc

e.g.)
Before(Auto-generated)

from alembic import op
import sqlalchemy as sa

from app.database import get_db_schema

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    connection = op.get_bind()

    op.alter_column('account', 'eoa_password',
               existing_type=sa.VARCHAR(length=2000),
               nullable=False, schema=get_db_schema())
    # ### end Alembic commands ###

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    connection = op.get_bind()

    op.alter_column('account', 'eoa_password',
               existing_type=sa.VARCHAR(length=2000),
               nullable=True, schema=get_db_schema())
    # ### end Alembic commands ###

After(Manually modify)

from alembic import op
import sqlalchemy as sa

from app.database import get_db_schema
from sqlalchemy.orm.session import Session
from app.model.db import Account  # Target Table Model

def upgrade():
    session = Session(bind=op.get_bind())
    _accounts = session.query(Account).filter(Account.eoa_password == None).all()
    for _account in _accounts:
        _account.eoa_password = "DEFAULT_WORD"
    session.flush()
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('account', 'eoa_password',
               existing_type=sa.VARCHAR(length=2000),
               nullable=False, schema=get_db_schema())
    # ### end Alembic commands ###

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('account', 'eoa_password',
               existing_type=sa.VARCHAR(length=2000),
               nullable=True, schema=get_db_schema())
    # ### end Alembic commands ###
    session = Session(bind=op.get_bind())
    _accounts = session.query(Account).filter(Account.eoa_password == "DEFAULT_WORD").all()
    for _account in _accounts:
        _account.eoa_password = None
    session.flush()