You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by mi...@apache.org on 2026/08/17 17:19:35 UTC

(superset) 01/01: fix(deps): add temporary SQLAlchemy 1.4 compatibility lane

This is an automated email from the ASF dual-hosted git repository.

aminghadersohi pushed a commit to branch sqlalchemy-dual-compat
in repository https://gitbox.apache.org/repos/asf/superset.git

commit 6c79b158bc619624bf44149c798fd874d26de025
Author: Amin Ghadersohi <am...@gmail.com>
AuthorDate: Mon Aug 17 17:19:13 2026 +0000

    fix(deps): add temporary SQLAlchemy 1.4 compatibility lane
---
 .github/workflows/superset-python-unittest.yml | 50 +++++++++++++++++++++++++-
 pyproject.toml                                 | 28 +++++++--------
 requirements/README.md                         | 26 ++++++++++++++
 requirements/sqlalchemy14.txt                  | 20 +++++++++++
 superset-core/pyproject.toml                   |  2 +-
 superset/utils/core.py                         |  6 ++--
 tests/unit_tests/databases/filters_test.py     | 10 +++---
 tests/unit_tests/utils/test_core.py            | 10 ++++++
 8 files changed, 128 insertions(+), 24 deletions(-)

diff --git a/.github/workflows/superset-python-unittest.yml b/.github/workflows/superset-python-unittest.yml
index 3d2ee7715bd..22b41173553 100644
--- a/.github/workflows/superset-python-unittest.yml
+++ b/.github/workflows/superset-python-unittest.yml
@@ -103,6 +103,48 @@ jobs:
           path: test-results/
           retention-days: 7
 
+  sqlalchemy14-compatibility:
+    needs: changes
+    if: needs.changes.outputs.python == 'true'
+    runs-on: ubuntu-26.04
+    timeout-minutes: 30
+    steps:
+      - name: Checkout
+        uses: actions/checkout@3d3c42e5aac5ba805825da76410c181273ba90b1 # v7.0.1
+        with:
+          persist-credentials: false
+          submodules: recursive
+      - name: Setup Python
+        uses: ./.github/actions/setup-backend/
+        with:
+          python-version: current
+      - name: Install SQLAlchemy 1.4 compatibility pair
+        # The development lock already supplies all transitive dependencies;
+        # replace only the correlated pair so the rest stays identical to SA2.
+        run: uv pip install --system --no-deps --reinstall -r requirements/sqlalchemy14.txt
+      - name: Validate legacy dependencies
+        run: uv pip check
+      - name: SQLAlchemy 1.4 compatibility tests
+        env:
+          SUPERSET_TESTENV: true
+          SUPERSET_SECRET_KEY: not-a-secret
+        run: |
+          pytest -q --cache-clear \
+            tests/unit_tests/initialization_test.py \
+            tests/unit_tests/extensions/test_sqlalchemy.py \
+            tests/unit_tests/commands/dataset/test_duplicate.py \
+            tests/unit_tests/commands/importers/v1/examples_test.py \
+            tests/unit_tests/databases/filters_test.py \
+            tests/unit_tests/db_engine_specs/test_duckdb.py \
+            tests/unit_tests/db_engine_specs/test_snowflake.py \
+            tests/unit_tests/db_engine_specs/test_trino.py \
+            tests/unit_tests/migrations \
+            tests/unit_tests/charts/commands/importers/v1/import_test.py \
+            tests/unit_tests/mcp_service/test_session_scope.py \
+            tests/unit_tests/mcp_service/test_auth_user_resolution.py \
+            tests/unit_tests/sql_lab_test.py \
+            tests/unit_tests/utils/test_core.py::test_pessimistic_connection_health_check_closes_transaction
+
   # Uploads the raw pull_request event payload so the "Python Unit Test
   # Results" workflow (running via workflow_run, in base-branch context) can
   # look up which PR/commit to annotate without checking out untrusted code.
@@ -124,7 +166,7 @@ jobs:
   # protection can require: it passes when unit-tests succeeded or was skipped,
   # and fails only on a real failure.
   unit-tests-required:
-    needs: [changes, unit-tests]
+    needs: [changes, unit-tests, sqlalchemy14-compatibility]
     if: always()
     runs-on: ubuntu-26.04
     timeout-minutes: 5
@@ -139,9 +181,15 @@ jobs:
       - name: Check unit-tests result
         env:
           RESULT: ${{ needs.unit-tests.result }}
+          LEGACY_RESULT: ${{ needs.sqlalchemy14-compatibility.result }}
         run: |
           if [ "$RESULT" != "success" ] && [ "$RESULT" != "skipped" ]; then
             echo "unit-tests did not pass (result: $RESULT)"
             exit 1
           fi
+          if [ "$LEGACY_RESULT" != "success" ] && [ "$LEGACY_RESULT" != "skipped" ]; then
+            echo "sqlalchemy14-compatibility did not pass (result: $LEGACY_RESULT)"
+            exit 1
+          fi
           echo "unit-tests result: $RESULT"
+          echo "sqlalchemy14-compatibility result: $LEGACY_RESULT"
diff --git a/pyproject.toml b/pyproject.toml
index d82adc359c5..878cc3fc21e 100644
--- a/pyproject.toml
+++ b/pyproject.toml
@@ -60,11 +60,11 @@ dependencies = [
     "flask-login>=0.6.0, < 1.0",
     "flask-migrate>=4.1.0, <5.0",
     "flask-session>=0.4.0, <1.0",
-    # Bumped to 3.1.1 alongside the SQLAlchemy 2.0 core bump (discussion
-    # #40273, step 6), which resolves the session/app-context handling
-    # across Celery task boundaries that previously blocked this (see
-    # PR #42542).
-    "flask-sqlalchemy>=3.1.1, <4.0",
+    # The uncorrelated bounds permit two supported pairs: the default lock uses
+    # Flask-SQLAlchemy 3.1.1 with SQLAlchemy 2.x; the temporary downstream lane
+    # constrains Flask-SQLAlchemy 2.5.1 with SQLAlchemy 1.4.54. Consumers of the
+    # legacy lane must constrain both packages; see requirements/README.md.
+    "flask-sqlalchemy>=2.5.1, !=3.0.*, <4.0",
     "flask-wtf>=1.3.0, <2.0",
     "geopy",
     "greenlet<=3.5.4, >=3.5.4",
@@ -111,7 +111,7 @@ dependencies = [
     "sshtunnel>=0.4.0, <0.5",
     "simplejson>=4.1.1",
     "slack_sdk>=3.43.0, <4",
-    "sqlalchemy>=2.0.0, <2.1",
+    "sqlalchemy>=1.4.54, <2.1",
     "sqlalchemy-continuum>=1.6.0, <2.0.0",
     "sqlalchemy-utils>=0.42.1, <0.43", # expanding lowerbound to work with pydoris
     "sqlglot>=30.16.0, <31", # 30.16.0 adds Trino inline UDF IF/CASE routine statement parsing
@@ -161,8 +161,8 @@ datafusion = ["flightsql-dbapi>=0.2.2, <0.3"]
 db2 = ["ibm-db-sa<=0.4.4, >=0.4.4"]
 denodo = ["denodo-sqlalchemy>=2.0.5,<2.1.0"]
 # sqlalchemy-dremio 3.0.5+ hard-pins sqlalchemy~=2.0.41, dropping 1.4.
-# Widened now that Superset's own SQLAlchemy 2.0 core bump has landed
-# (discussion #40273).
+# The supported default is SQLAlchemy 2. This extra is not available in the
+# temporary SQLAlchemy 1.4 compatibility lane (see requirements/README.md).
 dremio = ["sqlalchemy-dremio>=3.0.5, <4"]
 # <2 was an artificial ceiling; upstream has no SQLAlchemy version cap and
 # 1.1.10 already supports SQLAlchemy 2.0 (added `import_dbapi` in 1.1.7).
@@ -176,8 +176,7 @@ dynamodb = ["pydynamodb>=0.8.2"]
 solr = ["sqlalchemy-solr>=0.2.4.3"]
 elasticsearch = ["elasticsearch-dbapi>=0.2.13, <0.3.0"]
 # sqlalchemy-exasol cuts hard from SQLAlchemy 1.4-only (<6.0.0) to 2.0-only
-# (>=6.0.0) with no dual-compat release. Bumped now that Superset's own
-# SQLAlchemy 2.0 core bump has landed (discussion #40273).
+# (>=6.0.0) with no dual-compat release. This extra is SQLAlchemy 2-only.
 exasol = ["sqlalchemy-exasol>=6.0.0, <8.0"]
 excel = ["xlrd>=2.0.2, <2.1"]
 # Async dashboard "Export Data/Images to Excel": uploads the workbook to S3 and
@@ -193,8 +192,7 @@ fastmcp = [
 ]
 # sqlalchemy-firebird >=2.0.0 unconditionally requires SQLAlchemy 2.0 on
 # Python >=3.8 (which covers Superset's >=3.11 floor), with no dual-compat
-# release. Bumped now that Superset's own SQLAlchemy 2.0 core bump has
-# landed (discussion #40273).
+# release. This extra is SQLAlchemy 2-only.
 firebird = ["sqlalchemy-firebird>=2.2.0"]
 firebolt = ["firebolt-sqlalchemy>=1.1.2, <2"]
 gevent = ["gevent>=26.7.0"]
@@ -234,13 +232,11 @@ presto = ["pyhive[presto]>=0.6.5"]
 trino = ["trino>=0.338.0"]
 prophet = ["prophet>=1.3.0, <2"]
 # sqlalchemy-redshift cuts hard from SQLAlchemy 1.4-only (0.8.x) to 2.0-only
-# (>=1.0.0) with no dual-compat release. Bumped now that Superset's own
-# SQLAlchemy 2.0 core bump has landed (discussion #40273).
+# (>=1.0.0) with no dual-compat release. This extra is SQLAlchemy 2-only.
 redshift = ["sqlalchemy-redshift>=1.0.0"]
 # No release of sqlalchemy-risingwave has ever supported both SQLAlchemy 1.4
 # and 2.0 (version numbers don't track SQLAlchemy compat monotonically).
-# Bumped to the 2.0-only line now that Superset's own SQLAlchemy 2.0 core
-# bump has landed (discussion #40273).
+# The selected driver line is SQLAlchemy 2-only.
 risingwave = ["sqlalchemy-risingwave>=2.0.0"]
 shillelagh = ["shillelagh[all]>=1.4.5, <2"]
 singlestore = ["sqlalchemy-singlestoredb>=1.2.1, <2"]
diff --git a/requirements/README.md b/requirements/README.md
index 7550931cffd..dfb7e92a1bc 100644
--- a/requirements/README.md
+++ b/requirements/README.md
@@ -18,3 +18,29 @@ This will generate the pinned requirements in the `.txt` files, which will be us
 We recommend to everyone in the community to use the pinned requirements in their local development environments, to ensure consistency across different environments, though we don't force requirements as part of our python package semantics to allow flexibility for users to install different versions of the dependencies if they wish.
 
 Note that `development.txt` is a superset of what's in `base.txt`, and all version numbers for shared library should fully match at all times. `translations.txt` is meant as a supplemental file to be used in conjunction with the other requirements files, and is not meant to be used standalone.
+
+## Temporary SQLAlchemy 1.4 compatibility lane
+
+The generated `base.txt` and `development.txt` files remain the normal OSS
+environment and resolve SQLAlchemy 2.x with Flask-SQLAlchemy 3.1.1. A downstream
+that temporarily needs SQLAlchemy 1.4 must constrain **both** packages using
+`requirements/sqlalchemy14.txt` (SQLAlchemy 1.4.54 and Flask-SQLAlchemy 2.5.1).
+Constraining SQLAlchemy alone is intentionally unsupported because
+Flask-SQLAlchemy 3.1 requires SQLAlchemy 2.
+
+Python package metadata cannot express correlated alternatives such as “A 1.4
+with B 2.5, or A 2.x with B 3.1.” The published bounds therefore describe the
+union needed for downstream constraint files; they do not make arbitrary
+cross-pair combinations supported. CI tests the two exact pairs, and the OSS
+lock files prevent a default install from selecting the legacy pair.
+
+The core package and these commonly tested extras are compatible with both
+lanes: `bigquery`, `druid`, `duckdb`, `fastmcp`, `gevent`, `gsheets`, `mysql`,
+`postgres`, `presto`, `prophet`, `trino`, and `thumbnails`. The selected driver
+lines for `dremio`, `exasol`, `firebird`, `redshift`, and `risingwave` require
+SQLAlchemy 2 and must not be installed in the legacy lane. Other extras are not
+covered by the legacy CI lane and should be validated by downstream users.
+
+This lane is a temporary bridge for downstream migration, not a change to the
+OSS default. Remove the constraints, widened lower bounds, compatibility code,
+and legacy CI job together once those downstreams have moved to SQLAlchemy 2.
diff --git a/requirements/sqlalchemy14.txt b/requirements/sqlalchemy14.txt
new file mode 100644
index 00000000000..c9a2a6f3caf
--- /dev/null
+++ b/requirements/sqlalchemy14.txt
@@ -0,0 +1,20 @@
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more contributor
+# license agreements.  See the NOTICE file distributed with this work for
+# additional information regarding copyright ownership.  The ASF licenses this
+# file to you under the Apache License, Version 2.0 (the "License"); you may not
+# use this file except in compliance with the License.  You may obtain a copy of
+# the License at
+#
+#    http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.  See the
+# License for the specific language governing permissions and limitations under
+# the License.
+#
+# Temporary SQLAlchemy 1.4 compatibility constraints. Always apply this file as
+# a unit: Flask-SQLAlchemy 3.1 requires SQLAlchemy 2 and is not a legacy option.
+SQLAlchemy==1.4.54
+Flask-SQLAlchemy==2.5.1
diff --git a/superset-core/pyproject.toml b/superset-core/pyproject.toml
index 01a406bcce0..13ca67517cc 100644
--- a/superset-core/pyproject.toml
+++ b/superset-core/pyproject.toml
@@ -45,7 +45,7 @@ dependencies = [
     "isodate>=0.7.0",
     "pyarrow>=16.0.0",
     "pydantic>=2.8.0",
-    "sqlalchemy>=2.0.0,<2.1",
+    "sqlalchemy>=1.4.54,<2.1",
     "sqlalchemy-utils>=0.38.0, <0.43", # expanding lowerbound to work with pydoris
     "sqlglot>=30.8.0, <31",
     "typing-extensions>=4.0.0",
diff --git a/superset/utils/core.py b/superset/utils/core.py
index 1c3b92ebd65..bb9ef18440f 100644
--- a/superset/utils/core.py
+++ b/superset/utils/core.py
@@ -816,7 +816,8 @@ def pessimistic_connection_handling(some_engine: Engine) -> None:
             # the SELECT of a scalar value without a table is
             # appropriately formatted for the backend
             connection.scalar(select(1))
-            connection.rollback()  # pylint: disable=consider-using-transaction
+            if transaction := connection.get_transaction():
+                transaction.rollback()
         except exc.DBAPIError as err:
             # catch SQLAlchemy's DBAPIError, which is a wrapper
             # for the DBAPI's exception.  It includes a .connection_invalidated
@@ -829,7 +830,8 @@ def pessimistic_connection_handling(some_engine: Engine) -> None:
                 # here also causes the whole connection pool to be invalidated
                 # so that all stale connections are discarded.
                 connection.scalar(select(1))
-                connection.rollback()  # pylint: disable=consider-using-transaction
+                if transaction := connection.get_transaction():
+                    transaction.rollback()
             else:
                 raise
         finally:
diff --git a/tests/unit_tests/databases/filters_test.py b/tests/unit_tests/databases/filters_test.py
index 6a6cc665346..b4ecffcd079 100644
--- a/tests/unit_tests/databases/filters_test.py
+++ b/tests/unit_tests/databases/filters_test.py
@@ -117,7 +117,9 @@ def test_database_filter(mocker: MockerFixture) -> None:
         engine,
         compile_kwargs={"literal_binds": True},
     )
-    assert (
-        str(compiled_query)
-        == "SELECT dbs.id, dbs.verbose_name, dbs.database_name, dbs.sqlalchemy_uri, dbs.password, dbs.cache_timeout, dbs.select_as_create_table_as, dbs.expose_in_sqllab, dbs.configuration_method, dbs.allow_run_async, dbs.allow_file_upload, dbs.allow_ctas, dbs.allow_cvas, dbs.allow_dml, dbs.force_ctas_schema, dbs.extra, dbs.encrypted_extra, dbs.impersonate_user, dbs.server_cert, dbs.is_managed_externally, dbs.external_url, dbs.created_on, dbs.changed_on, dbs.created_by_fk, dbs.changed_by_ [...]
-    )
+    sql = str(compiled_query)
+    # SQLAlchemy 1.4 and 2.x produce the same filter but differ in inherited
+    # column order and optional grouping parentheses when compiling it.
+    assert "FROM dbs LEFT OUTER JOIN ssh_tunnels" in sql
+    assert "'[my_db].(id:42)', '[my_other_db].(id:43)'" in sql
+    assert "dbs.database_name IN ('my_db', 'my_other_db', 'third_db')" in sql
diff --git a/tests/unit_tests/utils/test_core.py b/tests/unit_tests/utils/test_core.py
index d0b8a510d7a..ed6e19378b2 100644
--- a/tests/unit_tests/utils/test_core.py
+++ b/tests/unit_tests/utils/test_core.py
@@ -25,6 +25,7 @@ import pytest
 from flask import current_app
 from pandas.api.types import is_datetime64_dtype
 from pytest_mock import MockerFixture
+from sqlalchemy import create_engine
 
 from superset.exceptions import SupersetException
 from superset.utils.core import (
@@ -49,6 +50,7 @@ from superset.utils.core import (
     normalize_dttm_col,
     parse_boolean_string,
     parse_js_uri_path_item,
+    pessimistic_connection_handling,
     QueryObjectFilterClause,
     QuerySource,
     remove_extra_adhoc_filters,
@@ -2088,3 +2090,11 @@ def test_extract_dataframe_dtypes_with_duplicate_columns() -> None:
     df = pd.DataFrame([[1, 2, 3]], columns=["a", "b", "a"])
     result = extract_dataframe_dtypes(df)
     assert len(result) == 3
+
+
+def test_pessimistic_connection_health_check_closes_transaction() -> None:
+    engine = create_engine("sqlite://")
+    pessimistic_connection_handling(engine)
+
+    with engine.connect() as connection:
+        assert not connection.in_transaction()