Welcome to the next installment of our Flask-SQLAlchemy tutorial series! If you’ve been following along, you’ll have gained a solid understanding of how to create, read, update, and delete records using SQLAlchemy, the Python SQL toolkit that provides a full suite of high-level ORM operations.

In this tutorial, we’ll delve into database migrations and version control. When developing an application, your database schema will often change. You may need to add new tables, modify existing tables, or delete unused ones. To manage these changes and ensure they don’t cause problems in your application, you can use a concept called migrations.

What is a Migration?

A database migration refers to the process of moving from one state of a database schema to another. This is much like version control for your code, but in the context of your database.

Migrations allow you to change your database schema over time, while preserving existing data. Migrations can also be used to seed your database with initial data.

To manage migrations in Flask-SQLAlchemy, we can use the Flask-Migrate extension. Flask-Migrate is a wrapper around Alembic, a database migration tool for SQLAlchemy.

Let’s walk through the process of setting up and using Flask-Migrate.

Setting up Flask-Migrate

First, install Flask-Migrate using pip:

pip install flask-migrate

Next, we’ll set up Flask-Migrate in our application:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db'

db = SQLAlchemy(app)
migrate = Migrate(app, db)

# our models go here

Here, we’ve imported Migrate from flask_migrate and created a Migrate instance. The Migrate instance is initialized with our app and db instances.

Creating a Migration

Let’s say we have a Product model, and we want to add a new column called price. Here’s how our model looks like:

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)

To add a new column, we’ll modify the Product model:

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    price = db.Column(db.Float)

Next, we’ll create a migration script. Open a terminal and run:

flask db migrate -m "Added price column to Product"

This will generate a migration script in a folder called migrations/versions.

Applying Migrations

To apply the migration and update the database schema, run:

flask db upgrade

Flask-Migrate will apply the migration, and our Product table will now have a price column.

If something goes wrong and you want to undo the last migration, you can use the downgrade command:

flask db downgrade

This will remove the price column from the Product table.

Managing Migrations in Version Control

Just like your application code, you should also version control your migrations. This allows you to keep track of changes to your database schema over time.

When you create a migration with flask db migrate, Flask-Migrate generates a script in migrations/versions. This script contains the instructions to move from the current state of your database schema to the new state (the “upgrade” path), and vice versa (the “downgrade” path).

You should commit these migration scripts to your version control system. When you or someone else checks out your application code and wants to set up the database, they can use flask db upgrade to apply all migrations and bring the database schema to the latest state.

Migrations with Alembic

Alembic, a database migration tool, is another alternative to Flask-Migrate. It’s created by the same team behind SQLAlchemy and provides a robust set of tools for managing migrations. With Alembic, you can autogenerate migration scripts based on the current state of your SQLAlchemy models. You can then review and edit these scripts as needed before applying them to your database.

Setting Up Alembic

To get started with Alembic, you first need to install it:

$ pip install alembic

Then, run the init command to create a new Alembic environment in your project:

$ alembic init alembic

This command creates an alembic directory containing several files, including alembic.ini (the main Alembic configuration file) and a versions subdirectory (where your migration scripts will be stored).

You’ll need to edit alembic.ini to tell Alembic how to connect to your database. In most cases, you can simply copy the SQLALCHEMY_DATABASE_URI setting from your Flask configuration:

# alembic.ini

sqlalchemy.url = postgresql://user:password@localhost/mydatabase

Generating Migration Scripts

With Alembic set up, you can now generate migration scripts based on your SQLAlchemy models. To do this, run the revision command with the --autogenerate option:

$ alembic revision --autogenerate -m "Add user table"

Alembic compares the current state of your database with your SQLAlchemy models and creates a new migration script in the versions directory. The -m option lets you provide a brief description of the migration, which is used as the filename.

Before you apply this migration, it’s a good idea to review the generated script and make any necessary edits. For instance, Alembic might not correctly detect changes to certain types of constraints, or you might want to add some custom SQL.

Applying Migrations

To apply all pending migrations, run the upgrade command:

$ alembic upgrade head

The head keyword refers to the most recent migration. You can also migrate to a specific version by providing its identifier (the part of the filename before the description).

If you ever need to undo a migration, you can use the downgrade command:

$ alembic downgrade -1

The -1 keyword refers to the previous version. Like upgrade, you can also provide a specific version identifier.

Version Control

By tracking each migration script in your version control system (like Git), you can maintain a detailed history of your database schema. This makes it easy to see when changes were made and why. Plus, other developers can simply pull the latest changes and run alembic upgrade head to update their local databases.

Remember to always create a new migration script when changing your SQLAlchemy models. Even for minor changes, it’s much safer than manually updating the schema and risking data loss or corruption.

Flask-Migrate or Alembic

Both Alembic and Flask-Migrate are excellent tools for handling database migrations with SQLAlchemy. The choice between them often comes down to your specific needs and preferences.

Alembic is a powerful and flexible tool that gives you fine-grained control over your migrations. It’s created by the same team behind SQLAlchemy, so it integrates smoothly with SQLAlchemy’s ORM. Alembic supports a wide range of databases and migration operations.

Flask-Migrate, on the other hand, is a Flask extension that adds some additional convenience on top of Alembic. It simplifies the process of initializing Alembic and running migrations, and it provides command-line commands that are easy to use. It’s a great choice if you’re using Flask and want a more streamlined workflow for your migrations.

If you’re working on a Flask application, Flask-Migrate may be the most straightforward choice since it integrates tightly with Flask’s ecosystem and makes handling migrations a bit simpler. However, if you’re not using Flask, or if you need the advanced features and flexibility of Alembic, you might choose to use Alembic directly.

In summary, both tools are excellent for managing database migrations in SQLAlchemy-based applications, and your choice will largely depend on your specific use case and personal preference.

Conclusion

Managing changes to a database schema can be complex, especially for large applications. Using a migration tool like Flask-Migrate can help to simplify this process, and keep your database schema consistent and version controlled.

In this tutorial, we’ve seen how to set up Flask-Migrate, create and apply migrations, and manage migrations in version control. We’ve also seen how to set up Alembic, create and manage migrations. As with the previous tutorials in this series, we encourage you to try these concepts in your own projects, and to explore further based on your needs.