Answer a question

I am having some issues updating schemas on Heroku Postgres. I have deployed a working application on a Heroku Dyno with a Postgres DB working previously. I have made some changes to the database schema (added new columns) and have committed the migration changes to the local migration folders. On my localhost the upgraded database works fine. However, when deploying the application to Heroku, I am getting a sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column <my_col> does not exist error.

I have tried the following commands
heroku run -a mydyno flask db history and it shows the correct migration history heroku run -a mydyno flask db migrate and it shows the following -

INFO  [alembic.autogenerate.compare] Detected added table 'phishing_email'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_phishing_email_created_at' on '['created_at']'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_phishing_email_receiver_address' on '['receiver_address']'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_phishing_email_sender_address' on '['sender_address']'
INFO  [alembic.ddl.postgresql] Detected sequence named 'user_user_id_seq' as owned by integer column 'user(user_id)', assuming SERIAL and omitting                                                                                             
INFO  [alembic.ddl.postgresql] Detected sequence named 'email_address_email_id_seq' as owned by integer column 'email_address(email_id)', assuming SERIAL and omitting                                                                          
INFO  [alembic.autogenerate.compare] Detected added column 'email_address.active'                                       
INFO  [alembic.autogenerate.compare] Detected added column 'email_address.email_password'                               
INFO  [alembic.autogenerate.compare] Detected added column 'email_address.last_mailbox_size'                            
INFO  [alembic.autogenerate.compare] Detected added column 'email_address.last_updated'                                 
INFO  [alembic.autogenerate.compare] Detected added column 'email_address.phishing_mail_detected'                       
INFO  [alembic.autogenerate.compare] Detected added column 'user.is_active'                                             
INFO  [alembic.autogenerate.compare] Detected added column 'user.is_admin'                                              
INFO  [alembic.autogenerate.compare] Detected added column 'user.last_logged_in'                                          Generating /app/migrations/versions/4e74c3c15c25_.py ...  done                        

It would seem like the migrate process detected the newly added tables and columns, when I executed heroku run -a mydyno flask db upgrade afterwards the logs shows

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.                                                          
INFO  [alembic.runtime.migration] Will assume transactional DDL.

So it would seem like everything is going well, however when deploying my application in heroku -a mydyno logs --tail the logs shows an error -

2020-11-13T15:21:01.291956+00:00 app[web.1]: sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column user.last_logged_in does not exist                                                                                       
2020-11-13T15:21:01.291956+00:00 app[web.1]: LINE 1: ...r_password, "user".created_at AS user_created_at, "user".las... 2020-11-13T15:21:01.291956+00:00 app[web.1]: ^                                                                          
2020-11-13T15:21:01.291957+00:00 app[web.1]:                                                                            
2020-11-13T15:21:01.291958+00:00 app[web.1]: [SQL: SELECT "user".user_id AS user_user_id, "user".username AS user_username, "user".password AS user_password, "user".created_at AS user_created_at, "user".last_logged_in AS user_last_logged_in, "user".is_admin AS user_is_admin, "user".is_active AS user_is_active                                                  
2020-11-13T15:21:01.291958+00:00 app[web.1]: FROM "user"]

which says that the column does not exist. Am I doing anything wrongly? Or is there a proper procedure for upgrading local database changes to Heroku Postgres? I have made sure to TRUNCATE CASCADE all tables on the Heroku Postgres.

The application database was previously working prior to this deployment, however just for to make it clearer my Flask Config object contains SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') which is set as an environment variable in my Config Var in Heroku app.

Any help will be greatly appreciated, thank you!

Edit: running heroku run -a mydno flask db history shows that all migrations are present and heroku run -a mydyno flask db current shows the correct revision number, the schemas are still not updating though.

Answers

I eventually used the CLI to drop all tables and imported a dump file from my local database using pg_dump -h [host] -U [user] [db] > ./dump.out to export my local database

Alter in the dump file all user into my Heroku database user

psql -h [heroku-host] -U [postgres-user] [db-name] < <dump.file>

I'm pretty sure this is not the correct way (as it drops alembic_version) as well but this worked for me.