Skip to content

Latest commit

 

History

History
177 lines (135 loc) · 5.57 KB

README.md

File metadata and controls

177 lines (135 loc) · 5.57 KB
Raw

For developers

Initial setup

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

$ ./bin/run_migration.sh init

Generate migration script

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

Prerequisites

  • Complete the initial setup.
  • Do not set the environment variable: DATABASE_SCHEMA.

Auto-Generate script

The following steps will automatically generate a migration script.

$ ./bin/run_migration.sh generate $file_suffix

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

Upgrade

$ ./bin/run_migration.sh upgrade

Downgrade

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

$ ./bin/run_migration.sh downgrade

To revert to the previous version, use -1.

$ ./bin/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! ###
    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! ###
    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 engine, 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 engine.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 engine.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 engine.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 engine.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! ###
    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 ###

After(Manually modify)

from alembic import op
import sqlalchemy as sa
from sqlalchemy import update
from sqlalchemy.orm.session import Session

from app.database import get_db_schema
from app.model.db import Account  # Target Table Model

def upgrade():
    session = Session(bind=op.get_bind())
    _accounts = session.execute(
        update(Account)
        .where(Account.eoa_password == None)
        .values(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.execute(
        update(Account)
        .where(Account.eoa_password == "DEFAULT_WORD")
        .values(eoa_password = None)
    )
    session.flush()