Compatibility with sqlalchemy v2

[CaselIT]

Just an heads up, not actually using this package, but SQLAlchemy got a bug report about this: sqlalchemy/sqlalchemy#9225

Current templates use str(engine.url) like

Flask-Migrate/src/flask_migrate/templates/flask/env.py

Line 32 in 828c65f

'sqlalchemy.url', str(get_engine().url).replace('%', '%%'))

This does not work in sqlalchemy v2:
https://docs.sqlalchemy.org/en/20/changelog/whatsnew_20.html#change-8567

[miguelgrinberg]

It looks like the solution recommended in your change log works for 1.4+ versions only. I would prefer not to break 1.3 users, which are still out there. Is there a way to get the engine URL with the password in a way that is compatible with older SQLAlchemy versions? Or does this need a version check to decide what method to use?

[CaselIT]

Looking at 1.3 it had __to_string__ but that's deprecated since 1.4, so that's not an option.
In 1.4 url was rewritten, so it's quite different from 1.3

I think your best bet here is to use something like (getattr(url, 'render_as_string', None) or url.__to_string__)(hide_password=False) that's quite ugly, but avoids a version check

[nkgilley]

I'm still seeing this issue with Flask-Migrate 4.0.4 and SQLAlchemy 2.0.1. I can't run the flask db upgrade command with these updates. It works fine if I downgrade SQLAlchemy to 1.4.46

Traceback (most recent call last):
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 3269, in raw_connection
    return self.pool.connect()
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/pool/base.py", line 452, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/pool/base.py", line 1255, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/pool/base.py", line 716, in checkout
    rec = pool._do_get()
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/pool/impl.py", line 283, in _do_get
    return self._create_connection()
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/pool/base.py", line 393, in _create_connection
    return _ConnectionRecord(self)
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/pool/base.py", line 678, in __init__
    self.__connect()
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/pool/base.py", line 903, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/pool/base.py", line 898, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/engine/create.py", line 640, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/engine/default.py", line 580, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
  File "/home/user1/venv/lib64/python3.8/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: connection to server at "[db1.example.com](http://db1.example.com/)" (10.1.2.28), port 5432 failed: FATAL:  password authentication failed for user "user1"


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/user1/venv/bin/flask", line 8, in <module>
    sys.exit(main())
  File "/home/user1/venv/lib64/python3.8/site-packages/flask/cli.py", line 1047, in main
    cli.main()
  File "/home/user1/venv/lib64/python3.8/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/home/user1/venv/lib64/python3.8/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/user1/venv/lib64/python3.8/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/user1/venv/lib64/python3.8/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/user1/venv/lib64/python3.8/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/home/user1/venv/lib64/python3.8/site-packages/click/decorators.py", line 26, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/home/user1/venv/lib64/python3.8/site-packages/flask/cli.py", line 357, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/home/user1/venv/lib64/python3.8/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/home/user1/venv/lib64/python3.8/site-packages/flask_migrate/cli.py", line 150, in upgrade
    _upgrade(directory, revision, sql, tag, x_arg)
  File "/home/user1/venv/lib64/python3.8/site-packages/flask_migrate/__init__.py", line 112, in wrapped
    f(*args, **kwargs)
  File "/home/user1/venv/lib64/python3.8/site-packages/flask_migrate/__init__.py", line 202, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
  File "/home/user1/venv/lib64/python3.8/site-packages/alembic/command.py", line 378, in upgrade
    script.run_env()
  File "/home/user1/venv/lib64/python3.8/site-packages/alembic/script/base.py", line 569, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/user1/venv/lib64/python3.8/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/user1/venv/lib64/python3.8/site-packages/alembic/util/pyfiles.py", line 110, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 843, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "migrations/env.py", line 103, in <module>
    run_migrations_online()
  File "migrations/env.py", line 87, in run_migrations_online
    with connectable.connect() as connection:
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 3245, in connect
    return self._connection_cls(self)
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 147, in __init__
    Connection._handle_dbapi_exception_noconnection(
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 2410, in _handle_dbapi_exception_noconnection
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 3269, in raw_connection
    return self.pool.connect()
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/pool/base.py", line 452, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/pool/base.py", line 1255, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/pool/base.py", line 716, in checkout
    rec = pool._do_get()
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/pool/impl.py", line 283, in _do_get
    return self._create_connection()
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/pool/base.py", line 393, in _create_connection
    return _ConnectionRecord(self)
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/pool/base.py", line 678, in __init__
    self.__connect()
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/pool/base.py", line 903, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/pool/base.py", line 898, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/engine/create.py", line 640, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/user1/venv/lib64/python3.8/site-packages/sqlalchemy/engine/default.py", line 580, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
  File "/home/user1/venv/lib64/python3.8/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "[db1.example.com](http://db1.example.com/)" (10.1.2.28), port 5432 failed: FATAL:  password authentication failed for user "user1"

(Background on this error at: https://sqlalche.me/e/20/e3q8)

[miguelgrinberg]

My guess is that you have a env.py file in your migrations folder that was generated by the buggy Flask-Migrate version. Update this file or regenerate your migration repository and try again.

[nkgilley]

@miguelgrinberg you nailed it. I generated a new env.py file with flask db init and now I'm good to go. Thanks for the quick reply 👍