DEV Community

Cover image for Existing DB Works, Empty DB Fails — Repairing a Broken Flask-Migrate History
tosane932
tosane932

Posted on • Originally published at qiita.com

Existing DB Works, Empty DB Fails — Repairing a Broken Flask-Migrate History

#flask #postgres #alembic #docker

Introduction

Hello from Japan! 🇯🇵

This article was originally published in Japanese on Qiita and has been translated and adapted for DEV Community.

While reviewing the migration history of a sales management application built with Flask and PostgreSQL, I discovered a serious problem.

The application worked normally with my existing local database.

However, with a completely fresh PostgreSQL database, the current migration history could not build the required tables from scratch.

The reason was simple but important:

The first migration did not create the tables. It started by adding a column to a table that was assumed to already exist.

Empty PostgreSQL database
        ↓
Add is_active column to products
        ↓
products does not exist
        ↓
Migration fails
Enter fullscreen mode Exit fullscreen mode

This article records the full process:

  • How I discovered the problem
  • How I investigated the migration history
  • How I added a missing initial migration
  • How I verified migration from an empty database
  • How I verified that existing databases would not be damaged
  • What I paid attention to while implementing and testing the fix

Investigating and Verifying the Problem with Codex

I used Codex in VS Code during this investigation and repair.

However, I did not hand the entire task over to Codex.

Instead, I divided the work into separate stages and restricted what Codex was allowed to do at each stage.

Static investigation
        ↓
Design the repair
        ↓
Implement only the approved migration changes
        ↓
Static verification
        ↓
Dynamic test with an empty database
        ↓
Test against a clone of the existing database
        ↓
Final verification
        ↓
Commit and push
Enter fullscreen mode Exit fullscreen mode

For each stage, I explicitly defined conditions such as:

  • Do not modify files during the investigation stage
  • Limit implementation to exactly two migration files
  • Do not directly migrate the normal local database
  • Use a different Docker Compose project name for testing
  • Test the existing database only through a clone created with pg_dump
  • Do not automatically fix additional problems that are discovered
  • Allow commit and push only after final verification

I used Codex for:

  • Inspecting migration history
  • Showing diffs
  • Running verification commands
  • Organizing the results

However, I made the final decisions about:

  • Which repair strategy to use
  • Safety conditions around databases
  • Which resources could be deleted
  • Whether the migration was safe enough to commit and push

One lesson from this work was that AI coding assistance is not only about generating code.

It is important to define the allowed change scope, prohibited operations, verification conditions, and stopping conditions.

Development Environment

The main stack is:

  • Python
  • Flask
  • Flask-SQLAlchemy
  • Flask-Migrate
  • Alembic
  • PostgreSQL
  • Docker Compose
  • pytest

When the Docker container starts, migrations are applied before Gunicorn starts.

Conceptually, the startup process looks like this:

flask db upgrade && gunicorn ...
Enter fullscreen mode Exit fullscreen mode

With this setup, if the migration fails, Gunicorn is never started.

That means the web application itself cannot start.

Discovering the Problem

When I checked migrations/versions/, only one migration file existed.

043c481b4069_add_is_active_to_products.py
Enter fullscreen mode Exit fullscreen mode

This file was the first revision in the migration history.

revision = '043c481b4069'
down_revision = None
Enter fullscreen mode Exit fullscreen mode

However, its upgrade() function did not create the products table.

It only added the is_active column to an already existing products table.

def upgrade():
    with op.batch_alter_table('products', schema=None) as batch_op:
        batch_op.add_column(
            sa.Column(
                'is_active',
                sa.Boolean(),
                server_default=sa.true(),
                nullable=False
            )
        )
Enter fullscreen mode Exit fullscreen mode

In other words, there was no migration that created:

  • products
  • daily_sales

I searched the entire project and found no equivalent table-creation logic such as:

op.create_table('products', ...)
op.create_table('daily_sales', ...)
db.create_all()
Enter fullscreen mode Exit fullscreen mode

The migration history was incomplete.

Why Did the Existing Database Still Work?

The existing database had most likely been created through another method before Flask-Migrate was introduced.

The historical sequence was probably something like this:

products and daily_sales created outside Alembic
        ↓
Flask-Migrate introduced
        ↓
is_active added to products
Enter fullscreen mode Exit fullscreen mode

Because the tables already existed, the existing database could apply the column-addition migration successfully.

A fresh database was different.

The first migration effectively tried to execute something like:

ALTER TABLE products
ADD COLUMN is_active BOOLEAN DEFAULT true NOT NULL;
Enter fullscreen mode Exit fullscreen mode

But an empty database had no products table.

The failure therefore looked approximately like this:

products does not exist
        ↓
First migration fails
        ↓
flask db upgrade exits with a non-zero status
        ↓
Gunicorn does not start
        ↓
The web application does not start
Enter fullscreen mode Exit fullscreen mode

That was the key issue:

The application worked because the existing database already contained history that Alembic itself could not reproduce.

Current Models

The application currently contains two models.

Product 

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    year = db.Column(db.Integer, nullable=False)
    month = db.Column(db.Integer, nullable=False)
    name = db.Column(db.String(100), nullable=False)
    price = db.Column(db.Integer, nullable=False)
    is_active = db.Column(
        db.Boolean,
        nullable=False,
        server_default=db.true()
    )
Enter fullscreen mode Exit fullscreen mode

DailySales 

class DailySales(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    product_id = db.Column(
        db.Integer,
        db.ForeignKey('products.id'),
        nullable=False
    )
    date = db.Column(db.Date, nullable=False)
    quantity = db.Column(
        db.Integer,
        nullable=False,
        default=0
    )
Enter fullscreen mode Exit fullscreen mode

Repair Strategies I Considered

I considered several approaches.

1. Add an Initial Migration Before the Existing Revision 

Create initial tables
        ↓
Add is_active
Enter fullscreen mode Exit fullscreen mode

This preserves the existing history while adding the missing foundation.

It creates a natural migration chain.

2. Rewrite the Existing First Migration

Another option would be to change the existing revision so that it creates all current tables directly.

However, that would significantly change the meaning of a migration that had already been applied.

That would make the historical record less trustworthy.

3. Create a Conditional Migration

Another possibility would be:

If products does not exist:
    create it
else:
    add the column
Enter fullscreen mode Exit fullscreen mode

This could support multiple database states.

However, it would introduce more state-dependent behavior and make verification more complicated.

4. Use db.create_all() and stamp

Another approach would be to create tables directly from the models and then use Alembic only to mark the database as being at a particular revision.

This could solve the immediate problem quickly.

However, it could create divergence between:

  • The models
  • The actual database
  • The migration history

I therefore did not use this approach.

The Approach I Chose

I chose to insert an initial migration at the beginning of the history.

The repaired migration chain became:

base
        ↓
b7e2c4a91f30 create products and daily_sales
        ↓
043c481b4069 add is_active to products
        ↓
head
Enter fullscreen mode Exit fullscreen mode

This allowed the migration history to tell the actual story:

  1. Create the initial tables
  2. Add is_active later

Implementing the Initial Migration

I added:

migrations/versions/b7e2c4a91f30_create_initial_tables.py
Enter fullscreen mode Exit fullscreen mode

The migration looked like this:

"""create initial products and daily_sales tables

Revision ID: b7e2c4a91f30
Revises:
Create Date: 2026-08-06
"""

from alembic import op
import sqlalchemy as sa


revision = 'b7e2c4a91f30'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        'products',
        sa.Column(
            'id',
            sa.Integer(),
            nullable=False
        ),
        sa.Column(
            'year',
            sa.Integer(),
            nullable=False
        ),
        sa.Column(
            'month',
            sa.Integer(),
            nullable=False
        ),
        sa.Column(
            'name',
            sa.String(length=100),
            nullable=False
        ),
        sa.Column(
            'price',
            sa.Integer(),
            nullable=False
        ),
        sa.PrimaryKeyConstraint('id')
    )

    op.create_table(
        'daily_sales',
        sa.Column(
            'id',
            sa.Integer(),
            nullable=False
        ),
        sa.Column(
            'product_id',
            sa.Integer(),
            nullable=False
        ),
        sa.Column(
            'date',
            sa.Date(),
            nullable=False
        ),
        sa.Column(
            'quantity',
            sa.Integer(),
            nullable=False
        ),
        sa.ForeignKeyConstraint(
            ['product_id'],
            ['products.id']
        ),
        sa.PrimaryKeyConstraint('id')
    )


def downgrade():
    op.drop_table('daily_sales')
    op.drop_table('products')
Enter fullscreen mode Exit fullscreen mode

Table Creation Order Matters

daily_sales.product_id references products.id.

Therefore, the upgrade must create the tables in this order:

1. products
2. daily_sales
Enter fullscreen mode Exit fullscreen mode

The downgrade must remove them in reverse order:

1. daily_sales
2. products
Enter fullscreen mode Exit fullscreen mode

If products were dropped first, the foreign key from daily_sales could prevent the operation.

This is a small detail, but an important one when creating migration history manually.

Why I Did Not Put is_active in the Initial Migration

I deliberately did not create is_active in the initial migration.

The history remained:

Base revision
└── Create products without is_active
        ↓
Existing revision
└── Add is_active
Enter fullscreen mode Exit fullscreen mode

This preserved the historical meaning of the existing revision.

The initial migration alone does not need to match the current model.

What matters is:

Applying every revision through head should produce the current model structure.

That was the goal.

Changing the Existing Revision

In the existing 043c481b4069 migration, I changed only its revision relationship.

Before:

revision = '043c481b4069'
down_revision = None
Enter fullscreen mode Exit fullscreen mode

After:

revision = '043c481b4069'
down_revision = 'b7e2c4a91f30'
Enter fullscreen mode Exit fullscreen mode

I also updated the Revises field in its docstring.

-Revises:
+Revises: b7e2c4a91f30
Enter fullscreen mode Exit fullscreen mode

I did not change the actual upgrade() or downgrade() logic.

The original operation remained intact.

Why I Did Not Add a server_default to quantity

The DailySales.quantity model has:

quantity = db.Column(
    db.Integer,
    nullable=False,
    default=0
)
Enter fullscreen mode Exit fullscreen mode

The important detail is:

default=0
Enter fullscreen mode Exit fullscreen mode

This is a Python-side SQLAlchemy default.

It is not a database-side default.

A database default would instead be represented using something like:

server_default='0'
Enter fullscreen mode Exit fullscreen mode

I intentionally did not add that to the migration.

Doing so would have introduced a database-level behavior that the current model did not define.

I wanted the migration to reproduce the actual intended schema, not silently add extra behavior.

Static Verification Before Touching a Database

Before starting PostgreSQL, I checked the syntax of both migration files.

PYTHONPYCACHEPREFIX=/tmp/sales_data_app_pycompile \
python -m py_compile \
migrations/versions/b7e2c4a91f30_create_initial_tables.py \
migrations/versions/043c481b4069_add_is_active_to_products.py
Enter fullscreen mode Exit fullscreen mode

The result was successful.

Exit code: 0
Syntax errors: none
Enter fullscreen mode Exit fullscreen mode

I also confirmed that the revision chain formed a single path:

base
        ↓
b7e2c4a91f30
        ↓
043c481b4069
        ↓
head
Enter fullscreen mode Exit fullscreen mode

Only after these static checks did I move on to database testing.

Testing a Completely Empty Database

I did not want to affect my normal local database.

Instead, I created an isolated environment by changing the Docker Compose project name.

docker compose \
  -p sales_data_app_migration_test \
  up --build -d
Enter fullscreen mode Exit fullscreen mode

Using a different Compose project name creates separate:

  • Containers
  • Networks
  • Volumes

For example:

Normal environment:
sales_data_app_postgres_data

Migration test environment:
sales_data_app_migration_test_postgres_data
Enter fullscreen mode Exit fullscreen mode

The test PostgreSQL database was completely empty.

The startup logs showed the migrations running in the expected order:

Running upgrade  -> b7e2c4a91f30,
create initial products and daily_sales tables

Running upgrade b7e2c4a91f30 -> 043c481b4069,
add is_active to products
Enter fullscreen mode Exit fullscreen mode

Gunicorn then started normally.

Starting gunicorn 26.0.0
Listening at: http://0.0.0.0:5000
Booting worker
Enter fullscreen mode Exit fullscreen mode

The HTTP request also succeeded.

200 text/html; charset=utf-8
Enter fullscreen mode Exit fullscreen mode

This confirmed that the application could now start from a completely empty PostgreSQL database.

Schema Created from the Empty Database

Three tables were created:

alembic_version
products
daily_sales
Enter fullscreen mode Exit fullscreen mode

products

Column Type NULL DB Default
id integer No ID sequence
year integer No None
month integer No None
name varchar(100) No None
price integer No None
is_active boolean No true

daily_sales

Column Type NULL DB Default
id integer No ID sequence
product_id integer No None
date date No None
quantity integer No None

The foreign key was also created as expected.

daily_sales.product_id
        ↓
products.id
Enter fullscreen mode Exit fullscreen mode

The delete and update behavior remained:

ON DELETE: NO ACTION
ON UPDATE: NO ACTION
Enter fullscreen mode Exit fullscreen mode

No ON DELETE CASCADE behavior was added because it does not exist in the model.

I also verified that the migration did not introduce:

  • Extra UNIQUE constraints
  • CHECK constraints
  • Model-independent indexes
  • A database-side default for quantity

The final revision was:

043c481b4069
Enter fullscreen mode Exit fullscreen mode

Verifying an Existing Database

The next question was more dangerous:

What happens to a database that has already reached 043c481b4069?

I needed to confirm that the newly inserted ancestor migration would not suddenly run against the existing database.

I did not run this experiment directly against the real local database.

Instead, I used the following process:

Start the normal database in read-only mode
        ↓
Create a pg_dump
        ↓
Stop the normal database
        ↓
Restore the dump into a separate PostgreSQL environment
        ↓
Run flask db upgrade only against the cloned database
Enter fullscreen mode Exit fullscreen mode

The normal web container was not started during the dump process.

I also used a read-only PostgreSQL setting:

PGOPTIONS=-c default_transaction_read_only=on
Enter fullscreen mode Exit fullscreen mode

The idea was simple:

Test the migration against data that behaves like the real database, without using the real database.

Running upgrade Against the Cloned Database

The cloned database initially contained:

alembic_version = 043c481b4069
products = 16 rows
daily_sales = 16 rows
Enter fullscreen mode Exit fullscreen mode

I ran the migration only against this cloned database.

docker compose \
  -p sales_data_app_existing_migration_test \
  run --rm --no-deps --build \
  web flask db upgrade
Enter fullscreen mode Exit fullscreen mode

The command completed successfully.

Exit code: 0
Enter fullscreen mode Exit fullscreen mode

Importantly, the logs did not contain:

Running upgrade -> b7e2c4a91f30
create initial products and daily_sales tables
Enter fullscreen mode Exit fullscreen mode

There were also no:

CREATE TABLE
Enter fullscreen mode Exit fullscreen mode

operations and no duplicate-table errors.

Alembic correctly treated the database as already being at head.

The newly inserted ancestor revision was not executed.

Comparing the Database Before and After upgrade

I compared the cloned database before and after running flask db upgrade.

The comparison included:

  • alembic_version
  • Table list
  • Column names
  • Data types
  • NULL constraints
  • Database defaults
  • Primary keys
  • Foreign keys
  • UNIQUE constraints
  • CHECK constraints
  • Indexes
  • Sequence states
  • Every row in products
  • Every row in daily_sales

I exported all rows as CSV ordered by primary key and compared them using both:

cmp
Enter fullscreen mode Exit fullscreen mode

and SHA-256 hashes.

Everything matched exactly.

Comparison Before After
Revision 043c481b4069 043c481b4069
products 16 rows 16 rows
daily_sales 16 rows 16 rows
Schema Same Same
Constraints Same Same
Indexes Same Same
Sequences Same Same
All data Same Same

This confirmed both migration paths:

Empty DB:
Base revision runs

Existing DB:
Base revision does not run again
Enter fullscreen mode Exit fullscreen mode

That was the result I needed.

Running pytest

Finally, I ran the existing test suite.

PYTHONDONTWRITEBYTECODE=1 \
pytest -p no:cacheprovider
Enter fullscreen mode Exit fullscreen mode

Result:

3 passed in 0.06s
Enter fullscreen mode Exit fullscreen mode

The existing tests also continued to pass after the migration repair.

Git Diff

Only two migration-related files changed.

migrations/versions/
├── b7e2c4a91f30_create_initial_tables.py
└── 043c481b4069_add_is_active_to_products.py
Enter fullscreen mode Exit fullscreen mode

The final commit was:

9a4422e fix: add initial database migration
Enter fullscreen mode Exit fullscreen mode

What I Learned

1. “The Existing Database Works” Does Not Mean the Migration History Is Correct

If an existing database already contains the necessary tables, an application can continue working even with incomplete migration history.

But a new:

  • Development environment
  • Test environment
  • Machine
  • Deployment target

may need to start from an empty database.

That means:

Existing environment starts successfully
≠
Migration history is correct
Enter fullscreen mode Exit fullscreen mode

This was the biggest lesson from the incident.

2. Migrations Are About the Path, Not Only the Current Schema

Even when:

Current model
=
Current database schema
Enter fullscreen mode Exit fullscreen mode

that alone is not enough.

The following path must also work:

Empty database
        ↓
Apply every revision in order
        ↓
Reach the current schema
Enter fullscreen mode Exit fullscreen mode

A migration system is not only a description of the final schema.

It is also the reproducible path used to reach that schema.

3. Modifying an Applied Revision Requires Careful Verification

In this repair, I changed the down_revision of an already applied migration.

The migration graph was logically valid after the change.

However, that was not enough evidence for me.

I wanted to know how Alembic would treat a real existing database.

That is why I tested it against a cloned database created from pg_dump, instead of experimenting directly on the normal database.

4. Test Both Empty and Existing Databases

For migration repairs, I now think at least two paths should be tested:


text
1. Empty DB → upgrade head

2. Already migrated DB → upgrade head
Enter fullscreen mode Exit fullscreen mode

Top comments (0)