How to Perform Flask-SQLAlchemy Migrations Using Flask-Migrate

The author selected Apache Software Foundation to receive a donation as part of the Write for DOnations program.

Introduction

Flask is a lightweight Python web framework that provides valuable tools and features for creating web applications in the Python Language. SQLAlchemy is an SQL toolkit offering efficient and high-performing relational database access. It provides ways to interact with several database engines, such as SQLite, MySQL, and PostgreSQL. It gives you access to the database’s SQL functionalities. It also gives you an Object Relational Mapper (ORM), which allows you to make queries and handle data using simple Python objects and methods. Flask-SQLAlchemy is a Flask extension that makes using SQLAlchemy with Flask easier, providing you with tools and techniques to interact with your database in your Flask applications through SQLAlchemy.

Flask-Migrate is a Flask extension based on the Alembic library, allowing you to manage database migrations.

Database migration is transferring data between different database schemas without any data loss. It’s commonly used to upgrade a database, change its schema by adding new table columns or relationships, and ensure a smooth transition with minimal downtime and no data loss.

For example, if you have a table called Product with a list of product names and want to add a price column to this table, you can use database migration to add the price column without losing the existing product data.

In this tutorial, you’ll use Flask-Migrate with Flask-SQLAlchemy to perform database schema migrations to modify your tables and preserve data.

Prerequisites

• For A local Python 3 programming environment, follow the tutorial for your distribution in How To Install and Set Up a Local Programming Environment for Python 3 series. In this tutorial, we’ll call our project directory flask_app.

• An understanding of basic Flask concepts, such as routes, view functions, and templates. If you are not familiar with Flask, check out How to Create Your First Web Application Using Flask and Python and How to Use Templates in a Flask Application.

• An understanding of basic HTML concepts. Review our How To Build a Website with HTML tutorial series.

• Understand basic Flask-SQLAlchemy concepts, such as setting up a database, creating database models, and inserting data into the database. SeeHow to Use Flask-SQLAlchemy to Interact with Databases in a Flask Application for background knowledge.

Step 01 - Installing the Required Python Packages

In this step, you’ll install the necessary packages for your application.

In your flask_app directory, activate your virtual environment:

source <my_env>/bin/activate

With your virtual environment activated, use pip to install Flask, Flask-SQLAlchemy, and Flask-Migrate:

pip install Flask Flask-SQLAlchemy Flask-Migrate

Once the installation has finished, the output will print a line similar to the following:

Output
Successfully installed Flask-3.0.0 Flask-Migrate-4.0.5 Flask-SQLAlchemy-3.1.1 Jinja2-3.1.2 Mako-1.3.0 MarkupSafe-2.1.3 Werkzeug-3.0.1 alembic-1.12.1 blinker-1.7.0 click-8.1.7 greenlet-3.0.1 itsdangerous-2.1.2 sqlalchemy-2.0.23 typing-extensions-4.8.0

With the required Python packages installed, you’ll set up an example database and model next.

Step 02 - Setting up an example Database and Model

In this step, you’ll set up your Flask application and a Flask-SQLAlchemy database with a model representing a products table where you’ll store your shop’s products.

In your flask_app directory, open a new file called app.py. This will hold your Flask application’s core code:

nano app.py

Add the following code to it:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///app.db"
db = SQLAlchemy(app)


class Product(db.Model):
    __tablename__ = "products"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    def __repr__(self):
        return f"<Product {self.name}>"
@app.route("/")
def index():
    return "<h1>Hello, World!</h1>"

Save and close the file.

You import the Flask class and SQLAlchemy from the flask and flask_sqlalchemy modules here. Then you create a Flask app instance called app.

Next, you set the SQLite database URI in the app configuration; this specifies the database file name as app.db. This database file will be created in a new folder called instance, which will be generated by Flask in the flask_app main directory.

In db = SQLAlchemy(app), you create an SQLAlchemy instance, db, bypassing the Flask app as an argument.

Next, you create a Product class that inherits from db.Model, representing a database table named ‘products’. In the table, you define an id column for the product ID and a name column for the product name.

The special repr function allows you to give each object a string representation to recognize it for debugging purposes.

Finally, you create a route ('/') that returns a simple HTML response ("<h1>Hello, World!</h1>") when the root URL is accessed.

Execute the following command to test that the application is set up correctly. This runs the app Flask application in a development server with debugging activated:

flask --app app run --debug

Once you run this command, you will receive the following output:

Output
 * Serving Flask app 'app'
 * Debug mode: on
WARNING: This is a development server. Please do not use it in a production deployment. Use a production WSGI server instead.
 * Running on http://127.0.0.1:5000
Press CTRL+C to quit
 * Restarting with stat
 * Debugger is active!
 * Debugger PIN: 633-501-683

This gives you information on your application as it runs.

With the development server running, visit the following URL using your browser:

http://127.0.0.1:5000/

You’ll get an <h1> heading, ’ Hello, World!`. This confirms that the application is set up properly. You can now move into the next step and add Flask-Migrate to your app.

Step 03 - Adding Flask-Migrate to Your Application

In this step, you will modify your app.py application file to add Flask-Migrate and use it to manage your Flask-SQLAlchemy database.

First, open app.py for modification:

nano app.py

Modify the file so that everything above the Product class declaration is as follows:

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

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app.db'
db = SQLAlchemy(app)
migrate = Migrate(app, db)
class Product(db.Model):

Save and close the file.

You import the Migrate class from the flask_migrate package.

After the db declaration, you use the Migrate class to initiate a migration instance called migrate, passing it to the Flask app instance and the db database instance.

Flask-Migrate provides a flask db command helper to manage your database.

To finish setting up Flask-Migrate and add support to your current project, use the following command in your flask_app directory:

flask db init

You will receive output similar to the following:

[secondary_label Output]  
Creating directory 'flask_app/migrations' ...  done
  Creating directory 'flask_app/migrations/versions' ...  done
  Generating flask_app/migrations/README ...  done
  Generating flask_app/migrations/script.py.mako ...  done.
  Generating flask_app/migrations/env.py ...  done
  Generating flask_app/migrations/alembic.ini ...  done
  Please edit the configuration/connection/logging settings in
  'flask_app/migrations/alembic.ini' before proceeding.

This creates a new migrations directory inside your flask_app folder, where all the migration scripts that manage your migrations will be stored.

If you are familiar with Alembic and want to add advanced configurations to your database migration system, you can modify the generated migrations/alembic.ini file. For our purposes, we will leave it as is.

With Flask-Migrate connected to your application, you will perform an initial database migration. Using the ’ Product ’ class will create the products table you declared earlier.

Creating the Product Table using a Migration Script

You will now perform your first migration, creating your database’s Products table.

In your flask_app directory, run the following command. This flask db migrate command detects all the new tables or modifications you perform on your Flask-SQLAlchemy database models. The -m flag allows you to specify a short message describing the modification you performed:

flask db migrate -m "initial migration"

You will receive the following output:

Output
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'products'
  Generating
  flask_app/migrations/versions/b9198aca3963_initial_migration.py
  ...done

Because our database has not been created yet, this output informs you that a new table called products was detected, and a migration script called b9198aca3963 initial_migration.py was created inside a directory called versions, where all different migration versions are stored.

The b9198aca3963 part in the migration script’s file name is a random ID generated to identify different migrations so that it will be different for you.

To understand how a migration script works, open yours with the following command. Make sure you replace b9198aca3963 with the ID that was generated for you and that you are in your flask_app root directory:

nano migrations/versions/b9198aca3963_initial_migration.py

Aside from internal imports and a setup, this initial migration script will have two main functions similar to the following:

def upgrade():
    # ### commands auto-generated by Alembic - please adjust! ###
    op.create_table('products',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=50), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###

def downgrade():
    # ### commands auto-generated by Alembic - please adjust! ###
    op.drop_table('products')
    # ### end Alembic commands ###

• The upgrade() function changes the database based on the modifications detected by the flask db migrate command. In this case, a new table called products was detected, so the upgrade() function creates the new table with the columns specified in the Product() database model you declared in the app.py file.

• The downgrade() function removes the changes and restores the state of the database as it was before upgrading. In this example, the previous state is restored by deleting the products table that will be created by the upgrade() function.

With the migration script ready, you can now use it to perform an initial upgrade. This will create the app.db database file and the products table. To do this, run the following command in your flask_app directory:

flask db upgrade

The output will be similar to the following:

Output
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> b9198aca3963, initial migration

This informs you that the upgrade was successfully executed.

A new app.db database file will be added to your instance folder inside your flask_app directory.

With the database and products table created, you can now add a few products to your database. You’ll do this next.

Populating the Database

You will now use the Flask shell to insert a few items into the products table.

With your virtual environment activated, run the following command to access the Flask shell:

flask shell

Inside the interactive shell, run the following code:

from app import db, Product

apple = Product(name="Apple")
orange = Product(name="Orange")
banana = Product(name="Banana")
db.session.add_all([apple, orange, banana])
db.session.commit()

This code does the following:

• Imports the db object and the Product model from the app.py file.
• Creates three instances of the Product() class passing a name for each product.
• Adds the new products into the database session using the db.session.add_all() method.
• Applies changes to the database using the db.session.commit() method.

For more on how to use Flask-SQLAlchemy, check out How to Use Flask-SQLAlchemy to Interact with Databases in a Flask Application.

Exit the Flask shell:

exit()

To ensure that the product items were added to the database, relaunch the Flask shell with a fresh memory:

flask shell

Then execute the following code to loop through the items in the products table:

from app import db, Product

for p in Product.query.all():
    print(p.name, p.id)

Here, you import the db object and Product model; then you use a for loop on the result of the query.all() method to access each item in the products table and print the name and ID of each item.

The output will be as follows:

Output
Apple 1
Orange 2
Banana 3

You successfully migrated your database and populated the products table with three items. We will modify the database schema and add a new price column to the products table, then migrate and upgrade our database while preserving this data using Flask-Migrate.

Step 04 - Modifying the Database Model

You now have a products table in your database with a few items stored in it. In this step, you will use Flask-Migrate to add a price column to the products table. Suppose you want to do this without a database migration manager. In that case, you must first delete the entire products table and create it again with the new column, resulting in you losing all the existing product items. However, with Flask-Migrate, you can add a new column while preserving existing data.

Adding a New Price Column

To add a price column to your products table, open the app.py file and modify the Product database model that defines the table schema.

Inside your flask_app directory, open app.py for modification:

nano app.py

Edit the Product() class by adding a new integer column called price:

class Product(db.Model):
    __tablename__ = 'products'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    price = db.Column(db.Integer)

    def __repr__(self):
        return f'<Product {self.name}>'

Save and close the file.

Your database schema modification is now done. You’ll generate a new migration script to apply this to the database while preventing data loss.

Generate a migration

Once you modify your database model, run the flask db migrate command for Flask-Migrate to detect your modification and generate a new migration script. Make sure to add a message describing what you modified:

flask db migrate -m "add price column"

Similar to the initial migration, you’ll receive an output like this:

Output
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added column 'products.price'
  Generating
  flask_app/migrations/versions/7ad34929a0f2_add_price_column.py
  ...  done

This informs you that a new column was detected, and a migration script was created.

Remember to review the generated migration script. In this case the upgrade() and downgrade() main functions will be like so:

def upgrade():
    # ### commands auto-generated by Alembic - please adjust! ###
    with op.batch_alter_table('products', schema=None) as batch_op:
        batch_op.add_column(sa.Column('price', sa.Integer(), nullable=True))
    # ### end Alembic commands ###

def downgrade():
    # ### commands auto-generated by Alembic - please adjust! ###
    with op.batch_alter_table('products', schema=None) as batch_op:
        batch_op.drop_column('price')
    # ### end Alembic commands ###

Here, the upgrade() function alters the products table and adds a price column. The downgrade() function removes the price() column, restoring your database to the previous version.

Upgrading the database

After modifying your database model, generate a migration script based on this modification. You can now apply the changes to the database using the upgrade command, which runs the upgrade() function in the latest migration script.

flask db upgrade

The output will inform you that the database was moved from the previous version to a new version with the "add price column" as a migration message.

To test that the price column was added, run the Flask shell:

flask shell

Then, loop through product items in the database and print the column values:

from app import db, Product

for p in Product.query.all():
    print(p.name, p.id, p.price)

The output should be as follows:

Output
Apple 1 None
Orange 2 None
Banana 3 None

The None values in the output reflect the values of the new price column, and you can now modify them as you wish to reflect the prices of your products.

If you receive an error while executing the previous loop, the price column was not added properly, and you should carefully review the previous steps, ensuring you have correctly taken all the necessary actions.

Exit the Flask shell:

exit()

The database is now migrated to a new version. Next, you will downgrade the database and remove the price column to demonstrate how to restore a previous database state.

Step 05 - Downgrading the Database

In the previous step, you upgraded your initial database version and added a price column to its products table. To demonstrate how to restore a previous state when managing database migrations, you will downgrade your current database and remove the price column from the products table.

To downgrade your database and restore its previous version, run the following command inside your flask_app directory:

flask db downgrade

The output will inform you that the database version has changed, and the previous version is restored.

To test that the price column is removed, open your Flask shell:

flask shell

Then run a query on the Product model to get all the items of the products table:

from app import db, Product
Product.query.all()

You should receive an error indicating that the products table has no price column:

Output
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: products.price
[SQL: SELECT products.id AS products_id, products.name AS products_name, products.price AS products_price
FROM products]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

This confirms that the price column was successfully removed and that the database downgrade was successful, and the error occurs because you still have a price column defined in the Product() model inside the app.py file.

To fix this error, exit the Flask shell:

exit()

Then open app.py inside your flask_app directory:

nano app.py

Modify the Product() model by removing the price column declaration so that the final version looks like this:

class Product(db.Model):
    __tablename__ = 'products'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))

    def __repr__(self):
        return f'<Product {self.name}>'

Save and close the file.

To test that the error was fixed, open the Flask shell:

flask shell

Then query all products:

from app import db, Product

Product.query.all()

The output should be as follows:

Output
[<Product Apple>, <Product Orange>, <Product Banana>]

This indicates that the error was fixed.

Finally, you can delete the migration file that contains add_price_column in its file name if you no longer need it:

rm migrations/versions/7ad34929a0f2_add_price_column.py

With this, you have successfully downgraded your database and restored it to its previous version.

Conclusion

You have created a small Flask application with a database and integrated it with Flask-Migrate. You learned to modify your database models, upgrade them to a new version, and downgrade them to a previous version.

In general, you can take the following steps to manage your database migrations as you develop your Flask applications:

1. Modify the database models.

2. Generate a migration script with the flask db migrate command.

3. Review the generated migration script and correct it if necessary.

4. Apply the changes to the database with the flask db upgrade command.

5. To restore a previous database version, use the flask db downgrade command.

See the Flask-Migrate documentation for more information.

If you would like to read more about Flask, check out the other tutorials in the How To Build Web Applications with Flask series.

class Base(DeclarativeBase):
    __table_args__ = {'schema': 'public'}
    pass


db = SQLAlchemy(model_class=Base)