Database migration with Flask, SQLAlchemy and Alembic

Database migrations are essential for keeping evolving applications aligned with changing data models, without losing consistency or integrity.

In this article, we’ll examine how Flask, SQLAlchemy, and Alembic work together to manage schema changes while ensuring smooth upgrades to your database schema.

Initial Setup

For simplicity, I’ll describe the steps for a project using Flask 3, SQLAlchemy 2 and a SQLite database in the following steps. To integrate Alembic into the project, follow these steps:

1. Install Alembic:

1

pip install alembic

2. Initialize Alembic in your project:

1

alembic init alembic

This creates a alembic directory and a alembic.ini file in your project root.

3. Update alembic.ini at the root of the project and find the sqlalchemy.url line and set it to your SQLite database URL:

1

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

1. Modify alembic/env.py to use your SQLAlchemy models. Add these lines near the top:

1
2
3
4
5
6

import os
import sys
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from your_app import app, db
target_metadata = db.metadata

Note: replace your_app with the name of your Flask application module.

5. Create a migration:

1

alembic revision --autogenerate -m "Description of your changes"

6. Apply the migration:

1

alembic upgrade head

7. For future changes, repeat steps 5 and 6.

Integration With the Flask Application

To integrate this with Flask, you could do the following way:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

    def run_migrations(self, configuration: Config):
        if configuration.TESTING == 'True' or configuration.TESTING is True:
            return

        self.logger.info("Running database migrations...")

        # Create an Alembic configuration object
        alembic_cfg = AlembicConfig()
        # This tells where the folder "alembic" is in your project, usually at the
        # root of the project
        alembic_cfg.set_main_option("script_location", "alembic")

        # Set the SQLAlchemy URL to the one from your application config
        # See the `_get_db_url` below
        alembic_cfg.set_main_option("sqlalchemy.url", self._get_db_url(configuration))

        # Run the migration
        command.upgrade(alembic_cfg, "head")

        self.logger.info("Database migrations completed.")

    def _get_db_url(self, configuration: Config):
        self.logger.debug(f'Creating the sqlite file at: {configuration.DB_FILE_NAME}')
        return str(f'sqlite:///{configuration.DB_FILE_NAME}')

With the above, the alembic.ini file, in particular the sqlalchemy.url setting, isn’t used anymore, as the code above takes precedence.

It’s a more flexible manner to configure the database to use when you run your application. This way, you could specify in your configuration a separate sqlalchemy.url depending on your environment.

Note: the configuration object isn’t detailed in the code above, but you picture it as a class with properties that are pulled from a .env file.

Editing the File Generated by Alembic

Let’s say a table is updated with a column and its default requires a value.

Can I edit the migration generated with a custom script using alembic?

You can edit the migration generated by Alembic with a custom script, and it’s often necessary to do so, especially when dealing with non-nullable columns in production.

You can and often should edit the migration script generated by Alembic. After running alembic revision --autogenerate, you’ll find a new script in your alembic/versions directory. You can modify this script to include custom SQL or Python code to handle complex migrations.

Handling New Non-Nullable Columns

Let’s say we want to add non-nullable columns once the table has been in production for a while.

How should we proceed with Alembic’s migration?

It’s generally a good practice to avoid adding non-nullable columns to existing tables in production without careful consideration. This is because:

• If the table already contains data, you need to provide a default value for existing rows.
• Adding a non-nullable column can lock the table during the migration and might cause downtime in a busy production environment.

Here’s a typical approach to safely add a non-nullable column:

1. Add the column as nullable first.
2. Deploy the change and update the application to start populating the column.
3. Once all rows have been populated, alter the column to be non-nullable in a separate migration.

Example of editing a migration script:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

def upgrade():
    # Add column as nullable first
    op.add_column('your_table', sa.Column('new_column', sa.String(50), nullable=True))

    # Populate the new column with a default value
    op.execute("UPDATE your_table SET new_column = 'default_value' WHERE new_column IS NULL")

    # Now alter the column to be non-nullable
    op.alter_column('your_table', 'new_column', nullable=False)

def downgrade():
    op.drop_column('your_table', 'new_column')

This approach minimizes the risk and potential downtime in production environments.

You can read more about this topic in these articles:

1. “Adding a NOT NULL column to an existing table” by PostgreSQL
2. “Best Practices for Schema Changes and Data Migrations” by Shlomi Noach
3. “Online Schema Change for MySQL” by Facebook Engineering:

These sources provide more detailed information on managing schema changes in production environments and the considerations involved in adding non-nullable columns.

Docker and Alembic Migration

Last but not least, you need to think about Docker when it comes to running your migration to deployed environments.

The above integration with the flask application runs at application startup.

So if you don’t tell in your Dockerfile where the migrations are, it won’t apply them and the application will fail to start.

So make sure to copy the migration in the container:

1
2
3
4
5
6

#set work directory early so remaining paths can be relative
WORKDIR /project-container
# Create the folder
RUN mkdir databases
# Include the db migrations
COPY ./alembic/ /project-container/alembic

Troubleshooting

“Target database isn’t up to date”

Let’s say you modified a table with a new column in the code-first database file, but running alembic revision --autogenerate -m "Add a column to my table X" outputs “Target database isn’t up to date”.

What could be the reason for that message?

The “Target database isn’t up to date” message usually indicates that there are pending migrations that haven’t been applied to your database yet. This can happen if you’ve created migrations previously but haven’t run them, or if there’s a mismatch between your database state and Alembic’s record of applied migrations.

To resolve this issue, try the following steps:

1. Check the current database state:

   1	alembic current

   Copy

   This will show you the current revision of your database.

2. View of migration history:

   1	alembic history --verbose

   Copy

   This will show you all available migrations and their status.

3. If there are pending migrations, apply them:

   1	alembic upgrade head

   Copy

   This will bring your database up to date with the latest migration.

4. If the above doesn’t work, or if you’re certain that your database should be up to date, you might need to stamp your database with the current head revision:

   1	alembic stamp head

   Copy

   This tells Alembic that your database is up to date with the latest migration.

5. After doing one of the above, try generating the new migration again:

   1	alembic revision --autogenerate -m "Description of your changes"

   Copy

If you’re still having issues, it might be helpful to check:

• The contents of your alembic_version table in the database
• The alembic/versions directory to see what migration scripts exist
• Your model definitions to ensure they match the current database state

Conclusion

Here you go! Now you know how to integrate Alembic, a database migration manager to your Flask project.

If you learned something new today, make sure to follow me.

Photo by Ernesto RƎIƎZ.