alembic util command error can't find identifier

Alembic stores the version history in your database. Hence it is using the value stored in your database to search for the revision. The version number for my personal database is stored in the table alembic_version:

mysql> SELECT * FROM alembic_version;
+-------------+
| version_num |
+-------------+
| c8ad125e063 |
+-------------+
1 row in set (0.00 sec)

Hint: Use the command SHOW TABLES if it's a SQL based database to see the tables.

To solve your problem simply use the command:

DROP TABLE alembic_version;

Or whatever the name of database version table is. And then you need to re-init the migration folder using the command:

python manage.py db init

And then creating a new migration:

python manage.py db migrate

And then you should be good to go with working migrations in alembic.


SirKaiserKai's solution didn't work for me, likely because I made some stupid mistake last time I migrated and deleted a file that I should have kept.

Instead of dropping the alembic_revision table I just updated the value in version_num to match where I knew my DB was at.

Make sure you use the migration ID of the file that matches the current state of your database

  1. Check the missing migration number

    psql=> SELECT * FROM alembic_version;
    +-------------------------+
    |      version_num        |
    +-------------------------+
    | <the missing migration> |
    +-------------------------+
    (1 row)
    
  2. Update the value

    psql=> UPDATE alembic_version
    psql->    SET version_num = '<true state of DB>'
    psql->    WHERE version_num = '<the missing migration>';
    UPDATE 1
    

If your database is in a state other than the migration file <true state of DB> then you're just going to continue to have errors. However, you could run a alembic upgrade head if the <true state of DB> is a migration file continuing from where you left off previously, and that would run all the migrations post this state as well.


In case you face the same issue above run the following commands:

Drop your local Alembic table

 - drop table alembic_version;

Then run the following alembic commands:

 - alembic stamp head
 - alembic revision --autogenerate -m "New revision"
 - alembic upgrade head
 - alembic stamp head

The stamp head allows alembic to have the latest table structure in place. Before running your new revisions.