sqlite3.OperationalError) no such table on Heroku

I'm working with https://github.com/hack4impact/flask-base (a flask boilerplate) as a basis for a project. The project runs as expected locally on windows.

I don't need to make any db changes on the production version of my code, so for simplicity's sake, I've decided to use sqllite both locally and on heroku. With that in mind I changed the production class in https://github.com/hack4impact/flask-base/blob/master/config.py to :

class ProductionConfig(Config):
    # SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or 'sqlite:///' + os.path.join(basedir, 'data.sqlite')
    SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(basedir, 'data-dev.sqlite')
    print('IN PRODUCTION '+ SQLALCHEMY_DATABASE_URI)
    SSL_DISABLE = (os.environ.get('SSL_DISABLE') or 'True') == 'True'

When I deploy the code and try to login I see a 500 error. The logs show:

2019-05-15T21:47:28.749283+00:00 app[web.1]:     raise value.with_traceback(tb)
2019-05-15T21:47:28.749284+00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
2019-05-15T21:47:28.749286+00:00 app[web.1]:     context)
2019-05-15T21:47:28.749287+00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute
2019-05-15T21:47:28.749289+00:00 app[web.1]:     cursor.execute(statement, parameters)
2019-05-15T21:47:28.749301+00:00 app[web.1]: sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: users [SQL: 'SELECT users.id AS users_id, users.confirmed AS users_confirmed, users.first_name AS users_first_name, users.last_name AS users_last_name, users.email AS users_email, users.password_hash AS users_password_hash, users.role_id AS users_role_id \nFROM users \nWHERE users.email = ?\n LIMIT ? OFFSET ?'] [parameters: ('[email protected]', 1, 0)]
Disconnected from log stream. There may be events happening that you do not see here! Attempting to reconnect...
2019-05-15T21:47:28.749277+00:00 app[web.1]:     exc_info
2019-05-15T21:47:28.749278+00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
2019-05-15T21:47:28.749280+00:00 app[web.1]:     reraise(type(exception), exception, tb=exc_tb, cause=cause)
2019-05-15T21:47:28.749281+00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
2019-05-15T21:47:28.749283+00:00 app[web.1]:     raise value.with_traceback(tb)
2019-05-15T21:47:28.749284+00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
2019-05-15T21:47:28.749286+00:00 app[web.1]:     context)
2019-05-15T21:47:28.749287+00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute
2019-05-15T21:47:28.749289+00:00 app[web.1]:     cursor.execute(statement, parameters)
2019-05-15T21:47:28.749301+00:00 app[web.1]: sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: users [SQL: 'SELECT users.id AS users_id, users.confirmed AS users_confirmed, users.first_name AS users_first_name, users.last_name AS users_last_name, users.email AS users_email, users.password_hash AS users_password_hash, users.role_id AS users_role_id \nFROM users \nWHERE users.email = ?\n LIMIT ? OFFSET ?'] [parameters: ('[email protected]', 1, 0)]

So it appears sqlalchemy can't find the table. I'm wondering if the line:

SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(basedir, 'data-dev.sqlite')

is then wrong way to access the flatfile db (data-dev.sqlite)

Solution

You really shouldn't use SQLite on Heroku. Its filesystem is ephemeral. Any changes you make to files will be lost the next time your dyno restarts. This happens frequently (at least once per day).

The original code did the right thing:

SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or 'sqlite:///' + os.path.join(basedir, 'data.sqlite')

If the DATABASE_URL environment variable is set it will be used to connect to your database. Since psycopg2 is in your requirements.txt file Heroku should automatically provision a PostgreSQL database and set the DATABASE_URL variable to its connection string.

If you really must use SQLite (and I strongly advise against that), treat it as read-only. You'll have to commit your database file on your local development machine and push that commit to Heroku. Even then it might not work properly. Heroku famously generates errors if Ruby users even try to install the sqlite3 gem.