Flask-SQLAlchemy models with Flask-Migrate upgrades on sqlite in tests: working versions (tested 2026-10-15)

flask==3.1.3
flask-sqlalchemy==3.1.1
flask-migrate==4.1.0
pytest==9.1.1
alembic==1.16.5
mako==1.3.10
sqlalchemy==2.0.43
typing-extensions==4.15.0
werkzeug==3.1.3
jinja2==3.1.6
markupsafe==3.0.3
itsdangerous==2.2.0
blinker==1.9.0
click==8.3.0
iniconfig==2.1.0
pluggy==1.6.0
pygments==2.19.2

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import String
from sqlalchemy.orm import Mapped, mapped_column

db = SQLAlchemy()


class User(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String(255), unique=True)
    name: Mapped[str] = mapped_column(String(80))

import os

from flask import Flask
from flask_migrate import Migrate

from models import db

MIGRATIONS = os.path.join(os.path.dirname(__file__), "migrations")


def create_app(uri: str = "sqlite:///app.db") -> Flask:
    app = Flask(__name__)
    # Config must be set before init_app, or the engine binds to the default URI.
    app.config["SQLALCHEMY_DATABASE_URI"] = uri
    db.init_app(app)
    Migrate(app, db, directory=MIGRATIONS)
    return app

from alembic import context
from flask import current_app

# Flask-Migrate runs this inside an app context, so current_app is available.
db = current_app.extensions["migrate"].db

# render_as_batch: sqlite cannot ALTER most constraints, so Alembic
# recreates the table instead. compare_type catches column type drift.
with db.engine.connect() as connection:
    context.configure(
        connection=connection,
        target_metadata=db.metadata,
        render_as_batch=True,
        compare_type=True,
    )
    with context.begin_transaction():
        context.run_migrations()

"""create user"""

import sqlalchemy as sa
from alembic import op

revision = "3f2a1b7c9d10"
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        "user",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("email", sa.String(255), nullable=False, unique=True),
        sa.Column("name", sa.String(80), nullable=False),
    )


def downgrade():
    op.drop_table("user")

import pytest
from alembic.autogenerate import compare_metadata
from alembic.migration import MigrationContext
from flask_migrate import downgrade, upgrade
from sqlalchemy import inspect
from sqlalchemy.exc import IntegrityError

from app import create_app
from models import User, db


@pytest.fixture
def app(tmp_path):
    # A real file, not sqlite:///:memory:. Each pooled connection to an
    # in-memory database gets its own empty DB, so upgrades would be lost.
    app = create_app(f"sqlite:///{tmp_path / 'test.db'}")
    app.config["TESTING"] = True
    with app.app_context():
        yield app
        db.session.remove()
        db.engine.dispose()


def test_upgrade_matches_models(app):
    upgrade()
    with db.engine.connect() as conn:
        mc = MigrationContext.configure(conn, opts={"compare_type": True})
        assert compare_metadata(mc, db.metadata) == []


def test_downgrade_drops_table(app):
    upgrade()
    assert "user" in inspect(db.engine).get_table_names()
    downgrade(revision="base")
    assert "user" not in inspect(db.engine).get_table_names()


def test_unique_email_enforced_after_upgrade(app):
    upgrade()
    db.session.add_all([User(email="[email protected]", name="a"), User(email="[email protected]", name="b")])
    with pytest.raises(IntegrityError):
        db.session.commit()
    db.session.rollback()


def test_data_persists_across_sessions(app):
    upgrade()
    db.session.add(User(email="[email protected]", name="p"))
    db.session.commit()
    db.session.remove()
    assert db.session.scalar(db.select(db.func.count(User.id))) == 1

[pytest]
pythonpath = .