How To Add Flask-Migrate To An Existing Project

I am on Windows, when I try to run 'flask db migrate' it says: Error: Failed to find Flask application or factory in module "run". Use "FLASK_APP=run:name to specify one.

I have set FLASK_APP with 'set FLASK_APP=run.py'.
File structure is like this:
├───run.py
├───app_name
│ ├───init.py

Any idea how to fix this?

@GW: what do you have in run.py? The error suggests you don't have an application instance, or if you do, then you did not name it app.

Dear Miguel,
Thank you for all this. I've been using Flask, Flask-Sqlalchemy, and Flask-Migrate on an app and a Postgres database hosted on Heroku and it all works great. For scaling reasons, I'm considering adding databases. First to split the models into 2 databases, as there are two types of data in the current database (user data and a large amount of separate data that users sometimes read, but never write to), and secondly to add follower database(s) on Heroku. I'm hoping that will make the app faster as the database is the main bottleneck.
I understand flask-migrate can be used with multiple databases and the binds feature of Flask-SQLAlchemy and by setting up the repository with
flask db init --multidb
My question is can I do that after the fact ? If so, what is the best way to do that after the fact, as I already have a large number of migrations to that original database. Of course I can't start the databases from scratch, as I can't lose user data. Any help would be appreciated, thanks

@Elisabeth: Alembic does not provide an option to switch configurations mid-project, so the only option is to create a new migration repository with the multidb option, and then manually import your migration scripts from the old place to the new place, making the necessary adjustments. I recommend that you create a test project with the database configuration you intend to work with and then create a couple test migrations so that you see what is the structure of the migration script when using multiple databases. Once you see how the migration script is supposed to look you can take the old migrations and adapt them.

Am not using ORMs. Am using using raw sql queries to create and destroy tables. Kindly advice on how I can go ahead integrating flask migrate into my project

Thank you for putting this together. I had developed a MySQL database prior to signing up for your Mega Tutorial and was stuck. But this did it. Following your examples in chapters 1-4 I was able to create classes in models.py for each of my existing tables (10 with data, 15 more without data - I deleted them so I could ignore them until needed). I am using PyCharm on Win 10. With your help, I am learning a lot. Again Thanks!

@Harun: Flask-Migrate works with Flask-SQLAlchemy, so you cannot use it without it. Consider using Alembic directly if you have other needs.

Thank you for putting this together. I'm getting this error while trying to execute the "flask db upgrade" command. Kindly advise on how to fix this.

psycopg2.errors.DependentObjectsStillExist: cannot drop table courses because other objects depend on it
DETAIL: constraint apply_course_course_fkey on table apply_course depends on table courses
constraint checklist_course_fkey on table checklist depends on table courses
HINT: Use DROP ... CASCADE to drop the dependent objects too.

@Harun: the error is clear, you are trying to drop a table that has foreign keys pointing to it. Before you can drop the table you have to remove all those foreign keys in other tables.

Sorry to bother you, Miguel. How do I remove these keys before dropping the table?
This is how am creating my models and their relationships.

class Department(db.Model):
""" Department Model for storing department related details """
tablename = "departments"

department_id = db.Column(
    db.Integer, autoincrement=True)
department_name = db.Column(
    db.String(120), primary_key=True, unique=True, nullable=False)
created_on = db.Column(db.DateTime, nullable=False,
                       default=datetime.utcnow)
courses = db.relationship(
    'Course', backref='department_obj', cascade="all, delete",
    passive_deletes=True)

class Course(db.Model):
""" Course Model for storing course related details """
tablename = "courses"

course_id = db.Column(
    db.Integer, autoincrement=True)
course_name = db.Column(
    db.String(120), primary_key=True, unique=True, nullable=False)
department = db.Column(db.String(120), db.ForeignKey(
    'departments.department_name', ondelete='CASCADE'), nullable=False)
created_on = db.Column(db.DateTime, nullable=False,
                       default=datetime.utcnow)
courses = db.relationship(
    'ApplyCourse', backref='course_obj', cascade="all, delete",
    passive_deletes=True)

@Harun: you need to look through all your models and remove any foreign keys that point to courses. Once you remove all those foreign keys you should be able to delete the courses table.

Thank you for your response. Just curious, the Course Model also has some relations with other tables. If I remove the FOREIGN KEYS how do I maintain the table relations? Am working with about 20 tables. And some tables have more than just one relationship.

@Harun: You are complicating this too much. It's really simple. You want to delete your courses table. Before you can do that, you have to remove all foreign keys that point to the courses table in other tables. Think about it, it doesn't make sense to have a foreign key that points to a table that was deleted, right? Other relationships are fine, they don't need to be touched.

Hi Miguel,
I have a Flask app using Blueprints and ran the init from the main folder where I have the run.py file in, using a FLASK_APP link to the folder i have my init.py file in. The problem is that i get "No changes in schema detected." even thought I use "SQLALCHEMY_DATABASE_URL=sqlite:/// flask db migrate". Do you know what the problem can be, should I maybe run migrate from the __init__py folder?

Many thanks,
Jonas

@Jonas: Have you imported your models? SQLAlchemy register models only when they are imported, if you do not import them it is as if they don't exist in terms of Flask-Migrate and Alembic.

Do we follow the same steps for adding Flask-Migrate to an existing Heroku PostgreSQL database in production?

@Aj: Add your migration repository on a development database, why mess with the production database before you are sure you got everything right?

Hi Miguel. Thank you for all the hard work you do to educate people like me :)

I am a bit confused re co-existence of dev, pre-prod and prod databases. So, let's say I initialize Migration in my dev environment using existing Models and empty DB trick. I then stamp it, and finally push my changes. Pre-prod environment will download the updated source code, but tests will fail.

Does it mean flask db upgrade will have to be included into CI/CD pipeline?

Is this all (flask db upgrade) that's needed to make sure DB in pre-prod is aligned with latest changes in the models?

Does it uses Alembic table to track what migrations have been applied and as such it's absence in pre-prod environment will signal Migration that something HAS to be done once flask db upgrade is executed

Finally, I've noticed that Migrate is not compatible with SQLAlchemy's db.create_all() - I had this line in my Flask factory function and it kicks off way before migration can take control of App context. As such, everytime I was executing flask db migrate one empty DB in memory it was telling me that everything is up to date, because SQLAlchemy was creating all tables before migration (so, DB wasn't empty). Does it mean that db.create_all() cannot be used with Migration ? If so, does it mean that DB creation MUST not be controlled by the app, but instead via Migration shell

Many thanks!

@Tim: Here are some answers:

"Pre-prod environment will download the updated source code, but tests will fail."

Why will tests fail? Your tests should run on their own database, which is created and discarded for each run. You do not want to reuse an existing database for tests, since its initial state can affect the result of the tests, as you are indicating.

"Does it mean flask db upgrade will have to be included into CI/CD pipeline?"

Your test database should be brand new. At the start of each test you should wipe your database clean, then run a flask db upgrade, or a db.create_all(). The latter will be faster, but less like your real environments.

"Is this all (flask db upgrade) that's needed to make sure DB in pre-prod is aligned with latest changes in the models?"

When you introduce migrations into an existing project you need to stamp all your databases, dev, prod and any others than you have. After that initial set up new migrations should work fine with the normal workflow.

"Does it uses Alembic table to track what migrations have been applied and as such it's absence in pre-prod environment will signal Migration that something HAS to be done once flask db upgrade is executed"

I don't understand this. The "something" that you mention is that stamp command, which you have to apply to all your databases, not just dev.

"Does it mean that db.create_all() cannot be used with Migration ?"

The db.create_all() function competes with Alembic and Flask-Migrate. You have to choose one of the two to use. For tests you can go with the former, since it is a throwaway database. For your dev/prod/etc environments you should only do upgrades through the flask db upgrade command.

@Miguel thank you! It does make sense. May I just confirm one thing. When you say "you have to stamp all DBs". Does it mean - stamp all environments, then commit/merge to make sure stamp changes are all merged into master? And only after this point I can tell that Migrate tracks the state of DB models in my whole project.

@Tim: stamping is about the databases, not your code or your environment. It simply sets that Alembic revision that reflects the state of the database. So there's nothing to commit or merge, just run flask db stamp head on all your databases so that they are marked as updated and can execute future migrations going forward.

Perfect! Thank you.
So, stamping is about creating Alembic table in the DB with a snapshot of current version.
Many many many thanks! I hope you'll carry on as your Flask tutorials are one of the best.

Hello Miguel,

I have used SQL Alchemy+alembic, i developed an application, it running fine with good amount of Data, Now i have to add the new tables to my DB without disturbing production DB.
So when i executed flask db migrate on dev setup i get bellow Error, Please help in resolving this issue.

ERROR [flask_migrate] Error: Target database is not up to date.

@Suri: the error means that your database has not been upgraded to the latest migration, or that you have extra migrations that are not supposed to be in your repository. Upgrade your database, or delete the unused migrations to remove this error.

Hi Miguel, I've been following your guide to set up migrations in my pre-existing database, however when i attempt to migrate for the first time, the version that is auto-generated tries to drop all of my existing tables, as if it cant see the models that i have defined. Any idea on how to fix this? Im using a PostgreSQL database, and im using a macbook.

def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_table('volume')
op.drop_table('sig')
op.drop_table('venue')
op.drop_table('paper')
op.drop_table('extraction')
# ### end Alembic commands ###

def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('extraction',
sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
sa.Column('paper_id', sa.VARCHAR(), autoincrement=False, nullable=True),
sa.Column('full_paper_text', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column('introduction_text', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column('method_text', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column('results_text', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column('references_text', sa.TEXT(), autoincrement=False, nullable=True),
sa.ForeignKeyConstraint(['paper_id'], ['paper.id'], name='extraction_paper_id_fkey'),
sa.PrimaryKeyConstraint('id', name='extraction_pkey')
)
op.create_table('paper',
sa.Column('id', sa.VARCHAR(), autoincrement=False, nullable=False),
sa.Column('title', sa.VARCHAR(), autoincrement=False, nullable=True),
sa.Column('volume', sa.VARCHAR(), autoincrement=False, nullable=True),
sa.Column('abstract', sa.VARCHAR(), autoincrement=False, nullable=True),
sa.Column('author', postgresql.ARRAY(sa.VARCHAR()), autoincrement=False, nullable=True),
sa.Column('url', sa.VARCHAR(), autoincrement=False, nullable=True),
sa.Column('pdf', sa.VARCHAR(), autoincrement=False, nullable=True),
sa.ForeignKeyConstraint(['volume'], ['volume.id'], name='paper_volume_fkey'),
sa.PrimaryKeyConstraint('id', name='paper_pkey')
)
op.create_table('venue',
sa.Column('id', sa.VARCHAR(), autoincrement=False, nullable=False),
sa.Column('name', sa.VARCHAR(), autoincrement=False, nullable=True),
sa.Column('years', postgresql.ARRAY(sa.INTEGER()), autoincrement=False, nullable=True),
sa.Column('volumes', postgresql.ARRAY(sa.VARCHAR()), autoincrement=False, nullable=True),
sa.PrimaryKeyConstraint('id', name='venue_pkey')
)
op.create_table('sig',
sa.Column('id', sa.VARCHAR(), autoincrement=False, nullable=False),
sa.Column('title', sa.VARCHAR(), autoincrement=False, nullable=True),
sa.Column('url', sa.VARCHAR(), autoincrement=False, nullable=True),
sa.PrimaryKeyConstraint('id', name='sig_pkey')
)
op.create_table('volume',
sa.Column('id', sa.VARCHAR(), autoincrement=False, nullable=False),
sa.Column('title', sa.VARCHAR(), autoincrement=False, nullable=True),
sa.Column('venues', postgresql.ARRAY(sa.VARCHAR()), autoincrement=False, nullable=True),
sa.Column('sigs', postgresql.ARRAY(sa.VARCHAR()), autoincrement=False, nullable=True),
sa.Column('year', sa.INTEGER(), autoincrement=False, nullable=True),
sa.Column('url', sa.VARCHAR(), autoincrement=False, nullable=True),
sa.Column('pdf', sa.VARCHAR(), autoincrement=False, nullable=True),
sa.PrimaryKeyConstraint('id', name='volume_pkey')
)
# ### end Alembic commands ###