Flask and Heroku sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgres

Question

When I run

heroku run python
>>> from app.main import app
>>> app.config['SQLALCHEMY_DATABASE_URI']
'postgres://<url string>' # the database url is passed correctly
>>> from app.main import db
>>> db.create_all()

it gives this error:

  Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/app/.heroku/python/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py", line 1039, in create_all
    self._execute_for_all_tables(app, bind, 'create_all')
  File "/app/.heroku/python/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py", line 1031, in _execute_for_all_tables
    op(bind=self.get_engine(app, bind), **extra)
  File "/app/.heroku/python/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py", line 962, in get_engine
    return connector.get_engine()
  File "/app/.heroku/python/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py", line 556, in get_engine
    self._engine = rv = self._sa.create_engine(sa_url, options)
  File "/app/.heroku/python/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py", line 972, in create_engine
    return sqlalchemy.create_engine(sa_url, **engine_opts)
  File "<string>", line 2, in create_engine
  File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/util/deprecations.py", line 298, in warned
    return fn(*args, **kwargs)
  File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/create.py", line 520, in create_engine
    entrypoint = u._get_entrypoint()
  File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/url.py", line 653, in _get_entrypoint
    cls = registry.load(name)
  File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 342, in load
    "Can't load plugin: %s:%s" % (self.group, name)
sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgres

I'm confused because I'm new to Heroku and Postgresql (been using SQLite until now) and none of the tutorials I'm following explain how it all connects to Flask, only how to do it. So I don't understand what to look at to fix the problem. Is there any other code I should include in the question?

(Most of the other questions like this one are typos or errors that don't fix this issue.)

Answer 1

Here's a quick one that works for me, with the latest PostgreSQL on Heroku:

SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL').replace("://", "ql://", 1)

Just hacks the postgres:// from Heroku (which can't be edited) to postgresql://.

Answer 2

This is due to a change in the sqlalchemy library. It was an announced deprecation in the changing of the dialect (the part before the ':' in the SQLALCHEMY_DATABASE_URI) name postgres to postgresql. They released this breaking change from this github commit with a minor version release, which is in their policy to do so.

Heroku's default dialect is postgres in the DATABASE_URL they provide, which gets translated into the SQLALCHEMY_DATABASE_URI. Heroku could update their postgres add-on if updating does not break other libraries which might depend on it.

In the meantime, you can pin your sqlalchemy library back to <1.4.0 (1.3.23 is the last 1.3.x release), and it should work.

Alternatively, you can update your code to modify the dialect.