Flask-Migrate: flask db upgrade completes without error, but database has not changed.

[zipengsg]

Hi Miguel,

I recently encountered a problem while working with Flask-Migrate. For context, I started the development with Flask-Migrate incorporated into the app the same way you detailed in your microblog Flask tutorial. Everything worked as intended until I received a new "app.db" to link with the app.

The new "app.db" presently contains information I'd like to retain when updating the schema (so no dropping tables). Having done some research, I learned that by deleting the original migrations folder and invoking "flask db init" again I can start tracking the new database from scratch. I did so. However, when I called "flask db migrate" afterwards, I saw the generic:

INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.

message, while no migration script was generated. As I researched further, I found that the alembic version mismatch as suggested by kjohnsen on June 23 in issue #216:

https://github.com/miguelgrinberg/Flask-Migrate/issues/216

could be causing the problem. So I used an external application and deleted the "alembic_version" table from app.db. Afterwards, I ran "flask db migrate" again and successfully generated a migration script that reflected the schema difference between "app.db" and what I specified in "models.py".

I proceeded to invoke "flask db upgrade" and was presented with the following message:

INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade -> 277b4b9dbe77, empty message

I thought it had worked, but when I went into "app.db", I noticed that the database had not changed (the 2 columns I wanted to add had not been added). A new "alembic_version" table has been created, however, but the column did not contain any data (as opposed to the original "alembic_version" table that I deleted).

Could you please offer me some insight into what is going on?

Thank you in advance for your assistance!

[miguelgrinberg]

Did you look at the migration script labeled 277b4b9dbe77? Does it do what you expect or not?

[zipengsg]

Yes, it does what I intended it to do. Here's the full script (I want to add two indexed columns to the Topic table).

"""empty message

Revision ID: 277b4b9dbe77
Revises:
Create Date: 2018-10-02 22:34:53.080893

"""
from alembic import op
import sqlalchemy as sa

revision identifiers, used by Alembic.

revision = '277b4b9dbe77'
down_revision = None
branch_labels = None
depends_on = None

def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_foreign_key(None, 'article', 'source', ['source_id'], ['id'])
op.add_column('topic', sa.Column('topic_lastupdated', sa.DateTime(), nullable=True))
op.add_column('topic', sa.Column('topic_status', sa.String(length=128), nullable=True))
op.create_index(op.f('ix_topic_topic_lastupdated'), 'topic', ['topic_lastupdated'], unique=False)
op.create_index(op.f('ix_topic_topic_status'), 'topic', ['topic_status'], unique=False)
# ### end Alembic commands ###

def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_index(op.f('ix_topic_topic_status'), table_name='topic')
op.drop_index(op.f('ix_topic_topic_lastupdated'), table_name='topic')
op.drop_column('topic', 'topic_status')
op.drop_column('topic', 'topic_lastupdated')
op.drop_constraint(None, 'article', type_='foreignkey')
# ### end Alembic commands ###

[miguelgrinberg]

There is no "down" revision, so I imagine this is your only migration. Are you sure that is what you wanted? Basically none of the tables and columns before these two that appear in this migration are recorded. Seems you lost your database history somehow.

[zipengsg]

I deleted the old migrations folder because I want to start documenting migrations for a new "app.db" that already contains data. My understanding is that if we had some database and would like to use Flask-Migrate to start managing it, we would be able to do so by creating a brand new migrations repository and assigning the new database as the one to be used for the application in the configuration file, i.e.:

import os
basedir = os.path.abspath(os.path.dirname(__file__))

class Config(object):

SECRET_KEY = os.environ.get('SECRET_KEY') or 'you-will-never-guess'
SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or \
    'sqlite:///' + os.path.join(basedir, 'app.db')
SQLALCHEMY_TRACK_MODIFICATIONS = False

And my __init__.py for the app is:

from flask import Flask
from config import Config
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from flask_login import LoginManager

app = Flask(__name__)
app.config.from_object(Config)
db = SQLAlchemy(app)
migrate = Migrate(app, db)
login = LoginManager(app)
login.login_view = 'login'

from app import routes, models

If a full database history is needed, is there some way we can "trick" Flask-Migrate by having it create an empty database named "app.db", generating all the columns to match my real "app.db", applying the migration and finally replacing the placeholder "app.db" with my intended "app.db"? Will Flask-Migrate be able to distinguish between the two?

[miguelgrinberg]

No, the full history is not required, you can start tracking migrations after there is already some tables, but of course you will not be able to recreate your database from scratch, you will always need to start from those base tables that existed before the first migration.

You may want to upgrade your Flask-Migrate and try again. As pointed in the reference above, there was a bug that suppressed some error messages. Try with the latest version and maybe we'll have more clues as to what the problem is.

[labulaka521]

my question as some as you, i had solve my question by uninstall flask-migrate ,after install flask-migrate again, hope could help you

[JOFLIX]

For me I had to delete the migrations, then flask db init, followed by flask db upgrade then I ran python shell, to get to define my engine, where I imported the databse containig module,in my case the schema in manage.py, keyed in the db in the shell, which showed the correct psql engine; after which I ran the db.create_all();
This should give a temporary solution, if you are worried about some data you initially had, I'd advise you to seek information about psql copy.
I hope this may help.

[GulyasGergelyR]

Anyone who might have this issue now.
My problem was that I created custom types and the generated migration file did not import my type definitions.
After adding the import manually it finally worked:

from alembic import op
import sqlalchemy as sa

import skeleton  # <===== THIS LINE WAS MISSING

# revision identifiers, used by Alembic.
revision = '9b10335d1f66'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('project',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=64), nullable=False),
    sa.Column('config_path', skeleton.models.ConfigPathType(length=512), nullable=False),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('config_path')
    )