Database Migration using Alembic

posted by March 21, 2018 No Comment

Hello guys, it's me again, Wicaksono. This time I will share you about database schema migration and why we're using it.

Schema migration is like version control for your database. Imagine how hard it is to maintain your code while working with the team. A database is also the same, especially when you are working on a scrum development cycle that most likely force you to change your database over time. Without migrations, modifying and sharing the application's database schema is a lot of mess. In short, schema migration refers to the management of incremental, reversible changes to relational database schemas. With schema migration, you can easily update or revert the database schema if there is an update or trouble for the database schema. Database migration is an essential part of software evolution, especially in agile environments.

Ok, enough for the theory, you can google it if you want to know more.
Let's step into how we implement database migration on our project!

We are using flask-sqlalchemy for our Object Relational Mapper (ORM), so we can use flask-migrate that will handle our SQLAlchemy database migrations using Alembic. Install flask-migrate via pip:

1
pip install Flask-Migrate

If you are a Django developer, you may head command like 'python manage.py db init'. Flask also supports that kind of command via flask-script CLI module and still suggested by the flask-migrate documentation. But according to the flask-script webpage, the flask-script is no more active on developing features since flask 0.11, flask includes a built-in CLI tool. So here we configure our flask CLI for database migration:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
import os

from flask import Flask
from flask_migrate import Migrate, MigrateCommand
from models import db

app = Flask(__name__)
app.config.from_object(os.environ['APP_SETTINGS'])
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
api = Api(app)
db.init_app(app)
migrate = Migrate(app, db)

@app.cli.command()
def db():
    return MigrateCommand

The first command to use the CLI is 'flask', to run the app is 'flask run', because we have imported the command from flask_migrate into the CLI command, now we can use:

1
2
3
4
flask db init
flask db migrate
flask db upgrade
flask db --help

For the first time only, to initialize the database migrations you can run:

1
flask db init

The structure will look like:

1
2
3
4
5
6
7
yourproject/
    alembic/
        env.py
        README
        script.py.mako
        versions/
            3512b954651e_add_account.py

After you run the command, there will be a new migration file on the 'versions' folder that contains SQLAlchemy command on how to upgrade the database and how to downgrade the database. Here is the example of our migration file when we init our database models.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
"""empty message

Revision ID: 7cc2eee2d907
Revises: 
Create Date: 2018-03-15 06:23:16.913448

"""
from alembic import op
import sqlalchemy as sa


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


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('branch',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=15), nullable=False),
    sa.Column('address', sa.String(length=30), nullable=True),
    sa.Column('address_territory_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['address_territory_id'], ['territory.id'], name='fk_address_territory_id', use_alter=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('ketua_arisan',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('visit_count', sa.Integer(), nullable=True),
    sa.Column('is_candidate', sa.Boolean(), nullable=True),
    sa.Column('latitude', sa.String(length=20), nullable=True),
    sa.Column('longitude', sa.String(length=20), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('role',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=20), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('territory',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('province', sa.String(length=20), nullable=False),
    sa.Column('city', sa.String(length=20), nullable=False),
    sa.Column('subdistrict', sa.String(length=20), nullable=False),
    sa.Column('village', sa.String(length=20), nullable=False),
    sa.Column('branch_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['branch_id'], ['branch.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('profile',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('first_name', sa.String(length=15), nullable=False),
    sa.Column('last_name', sa.String(length=15), nullable=True),
    sa.Column('phone_number', sa.String(length=15), nullable=True),
    sa.Column('address', sa.String(length=30), nullable=True),
    sa.Column('territory_id', sa.Integer(), nullable=True),
    sa.Column('role_id', sa.Integer(), nullable=False),
    sa.Column('ketua_arisan_id', sa.Integer(), nullable=True),
    sa.Column('manager_branch_id', sa.Integer(), nullable=True),
    sa.Column('penyuluh_branch_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['ketua_arisan_id'], ['ketua_arisan.id'], ),
    sa.ForeignKeyConstraint(['manager_branch_id'], ['branch.id'], name='fk_manager_branch_id'),
    sa.ForeignKeyConstraint(['penyuluh_branch_id'], ['branch.id'], ),
    sa.ForeignKeyConstraint(['role_id'], ['role.id'], ),
    sa.ForeignKeyConstraint(['territory_id'], ['territory.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('profile')
    op.drop_table('territory')
    op.drop_table('role')
    op.drop_table('ketua_arisan')
    op.drop_table('branch')
    # ### end Alembic commands ###

To apply/upgrade you database migration changes, you can run:

1
flask db upgrade

Then you can see in your database that your database has been modified. Here is also a migration example when we want to add new table and column to our models. First, after you have changed your model's structure, run:

1
flask db migrate

There will be a new file created in the 'version' folder (again). Here is the content:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
"""empty message

Revision ID: 7fd8c310a783
Revises: 7cc2eee2d907
Create Date: 2018-03-19 07:00:21.365847

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '7fd8c310a783'
down_revision = '7cc2eee2d907'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('revision',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.add_column('branch', sa.Column('revision_id', sa.Integer(), nullable=False))
    op.create_foreign_key('fk_address_territory_id', 'branch', 'territory', ['address_territory_id'], ['id'], use_alter=True)
    op.create_foreign_key('fk_branch_revision_id', 'branch', 'revision', ['revision_id'], ['id'])
    op.add_column('profile', sa.Column('revision_id', sa.Integer(), nullable=False))
    op.create_foreign_key('fk_profile_revision_id', 'profile', 'revision', ['revision_id'], ['id'])
    op.add_column('territory', sa.Column('revision_id', sa.Integer(), nullable=False))
    op.create_foreign_key('fk_territory_revision_id', 'territory', 'revision', ['revision_id'], ['id'])
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('fk_territory_revision_id', 'territory', type_='foreignkey')
    op.drop_column('territory', 'revision_id')
    op.drop_constraint('fk_profile_revision_id', 'profile', type_='foreignkey')
    op.drop_column('profile', 'revision_id')
    op.drop_constraint('fk_branch_revision_id', 'branch', type_='foreignkey')
    op.drop_constraint('fk_address_territory_id', 'branch', type_='foreignkey')
    op.drop_column('branch', 'revision_id')
    op.drop_table('revision')
    # ### end Alembic commands ###

After that, just run:

1
flask db upgrade

And your job has done!

Easy right? Thank you for reading and hope it will help you guys!
CAK

0 komentar:

Post a Comment

Copyright © 2018 • allocateam| Designed By VeeThemes.Co.In