How to migrate DB?

[mzch]

Hi,

It seems that pygmy doesn't migrate db automatically. Please tell me how to migrate DB.

[amitt001]

Hi, Yes, currently it doesn't. Migrations through Alembic(https://alembic.sqlalchemy.org/en/latest/) was something that was planned but I didn't get to it. You can use it.

[mzch]

Mm...I can't understand how to use alembic in order to migrate db...

[mzch]

I created .py file of alembic and executed it successfully. Thanks! :)

"""create table

Revision ID: e7d7571bbcd3
Revises:
Create Date: 2020-01-09 19:44:30.527606

"""
from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = 'e7d7571bbcd3'
down_revision = None
branch_labels = None
depends_on = None

def upgrade():
    op.create_table(
      'users',
      sa.Column('id', sa.Integer, autoincrement=True, primary_key=True),
      sa.Column('email', sa.String(120), unique=True, index=True),
      sa.Column('f_name', sa.String(30), nullable=False),
      sa.Column('m_name', sa.String(30), default=''),
      sa.Column('l_name', sa.String(30), nullable=False),
      sa.Column('password', sa.String(300), nullable=False),
      sa.Column('is_deleted', sa.Boolean, default=False),
      sa.Column('created_at', sa.TIMESTAMP(timezone=False), server_default=sa.func.current_timestamp()),
      sa.Column('updated_at', sa.TIMESTAMP(timezone=False), onupdate=sa.func.current_timestamp())
    )

    op.create_table(
      'link',
      sa.Column('id', sa.Integer, autoincrement=True, primary_key=True),
      sa.Column('long_url', sa.Unicode(1000), index=True),
      sa.Column('protocol', sa.String(10), default='http://'),
      sa.Column('domain', sa.String(300), ),
      sa.Column('long_url_hash', sa.BigInteger, index=True),
      sa.Column('short_code', sa.Unicode(6), unique=True, index=True, default=None),
      sa.Column('description', sa.String(1000), default=None),
      sa.Column('owner', sa.Integer, default=None),
      sa.Column('secret_key', sa.String(12), default=''),
      sa.Column('expire_after', sa.Integer, default=None),
      sa.Column('is_default', sa.Boolean, default=False),
      sa.Column('is_protected', sa.Boolean, default=False),
      sa.Column('is_disabled', sa.Boolean, default=False),
      sa.Column('is_custom', sa.Boolean, default=False),
      sa.Column('created_at', sa.TIMESTAMP(timezone=False), server_default=sa.func.current_timestamp()),
      sa.Column('updated_at', sa.TIMESTAMP(timezone=False), onupdate=sa.func.current_timestamp())
    )

    op.create_table(
      'clickmeta',
      sa.Column('id', sa.Integer, autoincrement=True, primary_key=True),
      sa.Column('link_id', sa.Integer, sa.ForeignKey('link.id')),
      sa.Column('country', sa.String(5), nullable=True),
      sa.Column('referrer', sa.String(100), nullable=True),
      sa.Column('created_at', sa.TIMESTAMP(timezone=False), server_default=sa.func.current_timestamp())
    )

def downgrade():
    op.drop_table('user', schema='production'),
    op.drop_table('clickmeta', schema='production'),
    op.drop_table('link', schema='production')

p.s. I renamed user table to users.