[BUG] - jupyterhub container doesn't work with sqlalchemy 2.0

[dcmcand]

Describe the bug

The conda-store container used in the examples/docker/docker-compose.yaml file and the examples/docker-without-nfs/docker-compose.yaml file crashes on docker-compose up.

Expected behavior

on docker-compose up, the example runs and is accessible through the web browser.a

How to Reproduce the problem?

From the root of the project, run docker-compose -f examples/docker/docker-compose.yaml up or docker-compose -f examples/docker-without-nfs/docker-compose.yaml up .

This should result in the conda-store image running, but it immediately crashes.

Output

Output from the container logs:

[D 2023-10-17 13:24:11.608 JupyterHub application:905] Looking for /opt/jupyterhub/jupyterhub_config in /opt/jupyterhub
[D 2023-10-17 13:24:11.609 JupyterHub application:926] Loaded config file: /opt/jupyterhub/jupyterhub_config.py
[I 2023-10-17 13:24:11.609 JupyterHub app:2479] Running JupyterHub version 1.5.0
[I 2023-10-17 13:24:11.609 JupyterHub app:2509] Using Authenticator: jupyterhub.auth.DummyAuthenticator-1.5.0
[I 2023-10-17 13:24:11.609 JupyterHub app:2509] Using Spawner: builtins.CondaStoreSpawner
[I 2023-10-17 13:24:11.609 JupyterHub app:2509] Using Proxy: jupyterhub.proxy.ConfigurableHTTPProxy-1.5.0
[D 2023-10-17 13:24:11.611 JupyterHub app:1594] Generating new cookie_secret
[I 2023-10-17 13:24:11.611 JupyterHub app:1599] Writing cookie_secret to /opt/jupyterhub/jupyterhub_cookie_secret
[D 2023-10-17 13:24:11.611 JupyterHub app:1721] Connecting to db: sqlite:///jupyterhub.sqlite
[E 2023-10-17 13:24:11.616 JupyterHub app:2989]
    Traceback (most recent call last):
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/jupyterhub/app.py", line 2986, in launch_instance_async
        await self.initialize(argv)
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/jupyterhub/app.py", line 2521, in initialize
        self.init_db()
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/jupyterhub/app.py", line 1726, in init_db
        self.session_factory = orm.new_session_factory(
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/jupyterhub/orm.py", line 880, in new_session_factory
        check_db_revision(engine)
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/jupyterhub/orm.py", line 771, in check_db_revision
        current_table_names = set(inspect(engine).get_table_names())
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/sqlalchemy/inspection.py", line 145, in inspect
        ret = reg(subject)
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/sqlalchemy/engine/reflection.py", line 303, in _engine_insp
        return Inspector._construct(Inspector._init_engine, bind)
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/sqlalchemy/engine/reflection.py", line 236, in _construct
        init(self, bind)
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/sqlalchemy/engine/reflection.py", line 247, in _init_engine
        engine.connect().close()
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3264, in connect
        return self._connection_cls(self)
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 174, in __init__
        self.dispatch.engine_connect(self)
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/sqlalchemy/event/attr.py", line 487, in __call__
        fn(*args, **kw)
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/sqlalchemy/event/legacy.py", line 99, in wrap_leg
        return fn(*conv(*args))
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/jupyterhub/orm.py", line 737, in ping_connection
        connection.scalar(select([1]))
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/sqlalchemy/sql/_selectable_constructors.py", line 489, in select
        return Select(*entities)
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/sqlalchemy/sql/selectable.py", line 5136, in __init__
        self._raw_columns = [
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/sqlalchemy/sql/selectable.py", line 5137, in <listcomp>
        coercions.expect(
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/sqlalchemy/sql/coercions.py", line 413, in expect
        resolved = impl._literal_coercion(
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/sqlalchemy/sql/coercions.py", line 651, in _literal_coercion
        self._raise_for_expected(element, argname)
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/sqlalchemy/sql/coercions.py", line 1139, in _raise_for_expected
        return super()._raise_for_expected(
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/sqlalchemy/sql/coercions.py", line 710, in _raise_for_expected
        super()._raise_for_expected(
      File "/opt/conda/envs/conda-store/lib/python3.10/site-packages/sqlalchemy/sql/coercions.py", line 535, in _raise_for_expected
        raise exc.ArgumentError(msg, code=code) from err
    sqlalchemy.exc.ArgumentError: Column expression, FROM clause, or other columns clause element expected, got [1]. Did you mean to say select(1)?

[D 2023-10-17 13:24:11.617 JupyterHub application:1031] Exiting application: jupyterhub

Versions and dependencies used.

used quansight/conda-store:latest from dockerhub.

Anything else?

No response

[trallard]

We need to bump JHub to at least 3.1.1; see https://github.com/jupyterhub/jupyterhub/issues/4312

If we are already on a Hub version that is compatible with SQLAlchemy 2 then https://github.com/conda-incubator/conda-store/blob/main/examples/docker-without-nfs/assets/jupyterhub_config.py will need to be updated (see https://jupyterhub.readthedocs.io/en/stable/reference/config-reference.html for the config reference)

[peytondmurray]

Related #931.