Alembic

Alembic is a database migrations tool written by the author of SQLAlchemy.

Install Alembic

Note: This install process was tested on Ubuntu 16.04, but it should also work on other distros just fine.

Assuming you already have MySQL installed on your machine, get the following Alembic dependencies installed:

$ sudo apt-get install libmysqlclient-dev -y
$ sudo pip install mysql-python

Now, install Alembic:

$ sudo pip install alembic

Configure and use Alembic

• Create the database migrations environment (note: use "generic" if setting up a single database):

$ cd $HOME/dev
$ alembic init --template generic alembic

• Edit the alembic.ini (created by the above command) and change the following line to match your MySQL user, password, hostname, and database name (note: change "driver" to "mysql"):

sqlalchemy.url = driver://user:pass@localhost/dbname

• Test that alembic has been properly configured and can connect to MySQL:

$ alembic current

• If the above command did not return any errors, create your first revision:

$ alembic revision -m "Create users table"
Generating $HOME/dev/alembic/versions/9710a474efc0_create_users_table.py ... done

• Edit that 9710a474efc0_create_users_table.py file and update the upgrade() and downgrade() functions to look like the following:

def upgrade():
    op.create_table(
        'users',
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('name', sa.String(50), nullable=False),
        sa.Column('my_decimal', sa.Numeric(16,8), nullable=False),
        sa.Column('dob', sa.DateTime, nullable=False),
    )

def downgrade():
    op.drop_table('users')

• Now, run a database migration to create the above user table to "head" (where "head" is the latest available migration):

$ alembic upgrade head

• Check your MySQL database, and you should see something like the following:

$ mysql -e 'describe dbname.users;'
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int(11)       | NO   | PRI | NULL    | auto_increment |
| name       | varchar(50)   | NO   |     | NULL    |                |
| my_decimal | decimal(16,8) | NO   |     | NULL    |                |
| dob        | datetime      | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

• Undo that above (last) migration with:

$ alembic downgrade -1

• Delete the entire database and re-create it (note: to migrate back to the first migration {the beginning}, use "base" {the opposite of "head"}):

$ alembic downgrade base
$ alembic upgrade head

Your Alembic environment tree should look something like the following:

.
├── alembic
│   ├── env.py
│   ├── env.pyc
│   ├── README
│   ├── script.py.mako
│   └── versions
│       ├── 9710a474efc0_create_users_table.py
│       └── 9710a474efc0_create_users_table.pyc
└── alembic.ini

See the Alembic documentation for many more examples and details.

External links

• Official website
• Alembic documentation