Local and heroku db go out of sync during migration using alembic

Question

I am running an application with Flask and Angular hosted on heroku. I have a problem with migrating heroku postgresql. I use flask-migrate , which is a tiny wrapper around alembic. Locally, everything is in order. I got an exception when I run heroku run upgrade , which runs the alembic update 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' {} 

Simple, alembic tries to start from the first migration, which creates db. I tried to explicitly install the correct version 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 the ephemeral file system, Heroku's revisions were not saved, but that should not be a problem if I explicitly install the revision, right? :)

How to set current version header?

Here is the 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 Procfile creates a completely new Alembic repository, which you only do once on your development machine. When deploying to a new computer, all you have to do is run the upgrade command to get the database created and updated to the latest version.

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

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

 $ ./manage.py db stamp HEAD 

and with this the database is marked as updated.

In addition, at some point I spoke out for the idea of ​​entering service commands in Procfile , but these days I only put services there, so I would leave only the web line there. To update the database, I think it is more predictable to run the command explicitly:

 $ heroku run python manage.py db upgrade