Local and heroku db get out of sync while migrating using alembic

Question

I'm bulding an app with Flask and Angular hosted on heroku. I have a problem with migrating heroku postgresql. I'm using flask-migrate which is a tiny wrapper around alembic. Locally everything is fine. I got an exception when I run heroku run upgrade which runs alembic upgrade command.

INFO  [alembic.migration] Context impl PostgresqlImpl.
INFO  [alembic.migration] Will assume transactional DDL.
INFO  [alembic.migration] Running upgrade None -> 19aeffe4063d, empty message
Traceback (most recent call last):
  File "manage.py", line 13, in <module>
    manager.run()
...

   cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) relation "users" already exists
 '\nCREATE TABLE users (\n\tid SERIAL NOT NULL, \n\tusername VARCHAR(32), \n\tpassword_hash VARCHAR(128), \n\tPRIMARY KEY (id)\n)\n\n' {}

Simply, alembic is trying to run from the first migration which creates the db. I tried to set explicitly the proper revision with heroku run python manage.py db upgrade +2 or revision number but the exception is the same.

lukas$ heroku run python manage.py db current
Running `python manage.py db current` attached to terminal... up, run.1401
INFO  [alembic.migration] Context impl PostgresqlImpl.
INFO  [alembic.migration] Will assume transactional DDL.
Current revision for postgres://...: None

My guess was that due to ephemeral filesystem of Heroku revision in not stored, but that shouldn't be a problem if I explicitly set the revision, right? :)

How can I set the current head of revision?

Here is relevant code:

Procfile:

web: gunicorn server:app
init: python manage.py db init
upgrade: python manage.py db upgrade

models.py

db = SQLAlchemy()

ROLE_USER = 0
ROLE_ADMIN = 1


class User(db.Model):
  __tablename__ = "users"
  id = db.Column(db.Integer, primary_key = True)
  username = db.Column(db.String(32), unique = True, index = True)
  email = db.Column(db.String(255), unique = True)
  password_hash = db.Column(db.String(128))
  role = db.Column(db.SmallInteger, default = ROLE_USER)

Answer 1

The init command that you put in the Procfile creates a brand new Alembic repository, which is something you only do once on your development machine. When you deploy to a new machine all you need to do is run the upgrade command to get the database created and updated to the last revision.

Alembic and Flask-Migrate have a command called stamp that can help you fix this problem. With stamp you can tell Alembic to write the revision of your choice to the database, without touching the database itself.

For example, creating a database from scratch when there are a lot of migrations can take a long time if Alembic has to go through all the migrations one by one. Instead, you can create the database with db.create_all() and then run:

$ ./manage.py db stamp HEAD

and with this the database is marked as updated.

Also, at some point I favored the idea of putting maintenance commands in the Procfile, but these days I only put services there, so I would only leave the web line there. To upgrade the database I think it is more predictable to run the command explicitly:

$ heroku run python manage.py db upgrade