Can't migrate or upgrade database with Flask-Migrate (Alembic)
I've been using Flask-Migrate (Alembic) for updating my database. I updated my models.py file however I made an error. I ran a migration and went to upgrade the database, however I got this error:
sqlalchemy.exc.IntegrityError: (_mysql_exceptions.IntegrityError) (1215, 'Cannot add foreign key constraint') [SQL: u'\nCREATE TABLE topics (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\t`subjectID` INTEGER, \n\ttopic VARCHAR(150) NOT NULL, \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(`subjectID`) REFERENCES subjects (id)\n)\n\n']
What I had done was have db.Text instead of db.Integer for a foreign key.
When I try run a new migration I get this:
alembic.util.CommandError: Target database is not up to date.
So now I'm stuck. I cannot upgrade the database nor run a migration. I tried to downgrade to an older database version by using something like this:
python manage.py db downgrade --sql b877018671c:36949b1cca31
But when I run python manage.py db current I get the newest database version which I am stuck in.
Is there a fix for this? Thanks.
Answer
Alembic stores the db version in a table it creates called alembic_version. This table contains a single field and row alembic_version.version_num. Make sure the value for this matches the filename of the most recent file in migrations/version. This version number is also contained inside the revision file in the revision variable that generally shows up on line 26 of the file. Make sure it matches the db version.
Another option is to simply drop the db and recreate it using alembic. If this is a development environment, where the data is not important, that would be my recommendation.