SQL Alchemy migrations

You already know what SQLAlchemy is and how to use some essential methods of this library while working with databases. There is one more thing that goes hand in hand with database handling. Let's find it out!

What is migration?

While working with data, quite often you need to change your database structure: to add a column or a constraint, create, delete, or update a table. The process of changing the schema of a database is called migration. Migrations are something like version control for databases. For sure, you can modify databases using SQL queries, for example. But if you have massive data, it's too much work to do it manually. Moreover, if you make a lot of SQL queries, the code gets more difficult to read. Luckily, there are some migration tools, using which you can make this process much faster, easier, and safer.

Using these tools, you can create a table, fill it with data, and, if you later decide to modify the table by adding or deleting a column, you can write a migration to make this change without worrying about corrupting the data in the database. Such tools are aimed at minimizing the effect of change on the existing data stored in the database. Moreover, it allows returning to the previous database state. If you decide to undo some changes you've made, you can roll the migration back.

Let's have a look at some of such migration tools that can help you automize the process.

Migration with Alembic

Alembic is the migration tool that is used with SQLAlchemy. Alembic provides a simple way to create, delete, and change tables and columns in a database. Foremost, you need to install it. You can do this using pip:

pip install alembic

After installing it, you need to create a migration environment using the command below:

alembic init alembic

After this, you will see some files and folders in your project directory:

> alembic/env.py
> alembic/script.py.mako
> alembic/versions
> alembic.ini

env.py is a script that is run every time you invoke the alembic migration tool.

script.py.mako is a template file that is used to generate new migration scripts.

In the versions folder, migration scripts will appear.

The file alembic.ini contains all the basic information that makes it possible to connect to the database and is called every time Alembic is used.

Let's create a User model with the columns id, name, surname, and birth in the database users. Pay attention that you also need to configure the database and establish the connection.

from sqlalchemy import Column, String, Integer, DateTime
from sqlalchemy.orm import declarative_base

Base = declarative_base()
class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    surname = Column(String, nullable=False)
    birth = Column(DateTime)

engine = create_engine('sqlite:///users.db')
Base.metadata.create_all(engine)

Then, it is necessary to set "sqlalchemy.url" in alembic.ini to point to your database.

sqlalchemy.url = sqlite:///users.db

Now you have to make some changes in env.py in your alembic folder. To detect auto changes by alembic, you need to give your model a path to env.py:

from model import Base
target_metadata = [Base.metadata]

Next, run the command below to generate a migration script:

alembic revision -m "users"

Using the above command, alembic generates your first migration commit file in the versions folder.

As soon as this file is generated, finally, you are ready to apply the migration using the following command:

alembic upgrade head

Now enjoy: your tables are generated in the database!

By the way, if you want to roll back to the previous revision, you can run the following command:

alembic downgrade -1

Migration with Flask-Migrate

One more way to make migrations is using Flask-Migrate. Actually, Flask-Migrate is an extension that handles SQLAlchemy database migrations for Flask applications using Alembic. Everything is handled by Alembic, so there is exactly the same functionality.

To start using it, you need to install Flask-Migrate:

pip install Flask-Migrate

Then, you should create a migration repository with the following command:

flask db init

This will create a migrations folder. Let's suppose you have the same model User from the previous section.

Next, let's generate a migration:

flask db migrate

And finally, let's apply the migration:

flask db upgrade

Great! You are done with it! Then every time you need to change the database models, repeat the migrate and upgrade commands.

While using Flask-Migrate, you can undo the migration as well. Just use the following command:

flask db downgrade

Summary

In this topic, you've learned some basic points related to database migration. Let's sum them up:

• Database migration is the process of changing the database structure;

• To make migrations, you can use such tools as Alembic and Flask-Migrate;

• Migration tools help to automize the process of modifying databases and make it safer, faster, and easier.

Now, it's time to practice the concepts you've learned!