Roadmap to SQLAlchemy 2

[hy144328]

I would like to propose a plan to migrate from SQLAlchemy 1.4 to 2.0.
From what I see in discussions, issues and PRs, we have not quite hit the homerun yet.
I already have some PRs in preparation, and I am interested in feedback.

Goals

• Make migration to SQLAlchemy 2 as boring as possible.
• Break down migration into smaller steps to take the pressure off LGTM reviews.

Motivation

Personal motivation:
My company uses Superset with Dremio.
The SQLAlchemy plug-in for Dremio requires SQLAlchemy 2. https://github.com/narendrans/sqlalchemy_dremio/blob/c576c65318f58e9342bbdb3dd0d6d691af9299ba/setup.py#L12
Superset is still stuck at SQLAlchemy 1.

superset/pyproject.toml

Line 102 in 1230b90

"sqlalchemy>=1.4, <2",

Currently, we use a fork that forcefully downgrades the SQLAlchemy plug-in for Dremio.
However, this is not sustainable in the long term.

General motivation:
While SQLAlchemy has no official EOL dates, it is virtually there. sqlalchemy/sqlalchemy#13009
SQLAlchemy 2.0 has been out for multiple years now.
SQLAlchemy 2.1 is around the corner.
This will make SQLAlchemy obsolete.

Observations

SQLAlchemy 1.4 to 2.0 is a breaking change.
This includes both the direct dependency on SQLAlchemy and the indirect dependencies on other packages, e.g. Flask and Flask-SQLAlchemy.
There is a single-shot PR by @dpgaspar that attempts to bump everything in a single PR.
I am not sure whether the PR is ready.
The last commit is from September 2025.
The PR touches 92 files and over a thousand lines of code, which makes it a daunting task to review.
Therefore, I would like to suggest smaller steps to some pressure off before the final push of bumping the dependencies.

Firstly, SQLAlchemy 1.4 (current version) is already designed to be a transition version. https://docs.sqlalchemy.org/en/20/changelog/migration_20.html#the-1-4-2-0-migration-path
This means that it enables in many cases both version 1 and 2 styles.
So we are able to update the code without actually bumping the SQLAlchemy version.

Secondly, SQLAlchemy is able to emit warnings.
This way, we are able to use the existing unit tests to check on the progress of the migration.

Thirdly, Python warnings and PyTest integrate well with each other.
So we are able to turn warning into errors, and mitigate regressions during collaboration.

Battleplan

1. Enable SQLAlchemy 1.4 deprecation warning in unit testing set-up.

   • Turn on SQLAlchemy deprecation warnings. test: enable SQLAlchemy deprecation warnings #40274

2. After running all unit tests, I get the following warning cases:

   $ SQLALCHEMY_WARN_20=1 TZ=UTC python3 -m pytest tests/unit_tests/ |& grep "ovedIn20Warning: " | sed 's/.*ovedIn20Warning: //' | sort | uniq
   Passing a string to Connection.execute() is deprecated and will be removed in version 2.0.  Use the text() construct, or the Connection.exec_driver_sql() method to invoke a driver-level SQL string. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
   "Query" object is being merged into a Session along the backref cascade path for relationship "Database.queries"; in SQLAlchemy 2.0, this reverse cascade will not take place.  Set cascade_backrefs to False in either the relationship() or backref() function for the 2.0 behavior; or to set globally for the whole Session, set the future=True flag (Background on this error at: https://sqlalche.me/e/14/s9r1) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
   "SavedQuery" object is being merged into a Session along the backref cascade path for relationship "Database.saved_queries"; in SQLAlchemy 2.0, this reverse cascade will not take place.  Set cascade_backrefs to False in either the relationship() or backref() function for the 2.0 behavior; or to set globally for the whole Session, set the future=True flag (Background on this error at: https://sqlalche.me/e/14/s9r1) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
   "SqlaTable" object is being merged into a Session along the backref cascade path for relationship "Database.tables"; in SQLAlchemy 2.0, this reverse cascade will not take place.  Set cascade_backrefs to False in either the relationship() or backref() function for the 2.0 behavior; or to set globally for the whole Session, set the future=True flag (Background on this error at: https://sqlalche.me/e/14/s9r1) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
   "SqlMetric" object is being merged into a Session along the backref cascade path for relationship "SqlaTable.metrics"; in SQLAlchemy 2.0, this reverse cascade will not take place.  Set cascade_backrefs to False in either the relationship() or backref() function for the 2.0 behavior; or to set globally for the whole Session, set the future=True flag (Background on this error at: https://sqlalche.me/e/14/s9r1) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
   "TableColumn" object is being merged into a Session along the backref cascade path for relationship "SqlaTable.columns"; in SQLAlchemy 2.0, this reverse cascade will not take place.  Set cascade_backrefs to False in either the relationship() or backref() function for the 2.0 behavior; or to set globally for the whole Session, set the future=True flag (Background on this error at: https://sqlalche.me/e/14/s9r1) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
   "TaggedObject" object is being merged into a Session along the backref cascade path for relationship "Tag.objects"; in SQLAlchemy 2.0, this reverse cascade will not take place.  Set cascade_backrefs to False in either the relationship() or backref() function for the 2.0 behavior; or to set globally for the whole Session, set the future=True flag (Background on this error at: https://sqlalche.me/e/14/s9r1) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
   The ``as_declarative()`` function is now available as sqlalchemy.orm.as_declarative() (deprecated since: 1.4) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
   The autoload parameter is deprecated and will be removed in version 2.0.  Please use the autoload_with parameter, passing an engine or connection. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
   The connection.execute() method in SQLAlchemy 2.0 will accept parameters as a single dictionary or a single sequence of dictionaries only. Parameters passed as keyword arguments, tuples or positionally oriented dictionaries and/or tuples will no longer be accepted. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
   The current statement is being autocommitted using implicit autocommit, which will be removed in SQLAlchemy 2.0. Use the .begin() method of Engine or Connection in order to use an explicit transaction for DML and DDL statements. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
   The `database` package is deprecated and will be removed in v2.0 of sqlalchemy. Use the `dialects` package instead. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
   The ``declarative_base()`` function is now available as sqlalchemy.orm.declarative_base(). (deprecated since: 1.4) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
   The Engine.execute() method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. All statement execution in SQLAlchemy 2.0 is performed by the Connection.execute() method of Connection, or in the ORM by the Session.execute() method of Session. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
   The legacy calling style of select() is deprecated and will be removed in SQLAlchemy 2.0.  Please use the new calling style described at select(). (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
   The "whens" argument to case(), when referring to a sequence of items, is now passed as a series of positional elements, rather than as a list.  (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
   "User" object is being merged into a Session along the backref cascade path for relationship "Role.user"; in SQLAlchemy 2.0, this reverse cascade will not take place.  Set cascade_backrefs to False in either the relationship() or backref() function for the 2.0 behavior; or to set globally for the whole Session, set the future=True flag (Background on this error at: https://sqlalche.me/e/14/s9r1) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
   

   This means that we are able to work on each warning case more or less independently:

   • Passing a string to Connection.execute() is deprecated refactor: update Connection.execute() to use queries with text() #40277
   • "Query" object is being merged into a Session refactor: set cascade_backrefs=False for Query #42210
   • "SavedQuery" object is being merged into a Session refactor: set cascade_backrefs=False for SavedQuery #42212
   • "SqlaTable" object is being merged into a Session refactor: set cascade_backrefs=False for SqlaTable #42213
   • "SqlMetric" object is being merged into a Session refactor: do not cascade to SqlMetric #42221
   • "TableColumn" object is being merged into a Session refactor: do not cascade to TableColumn #42222
   • "TaggedObject" object is being merged into a Session refactor: do not cascade to TaggedObject #42223
   • The as_declarative() function is now available chore: as_declarative() warnings are irrelevant #41982
   • The autoload parameter is deprecated refactor: automatically load with engine/connection #41980
   • The connection.execute() method refactor: make import/expression layer SQLAlchemy 2.0-compatible #41179 chore: convert warning to error if parameters not passed as collection #41977
   • The current statement is being autocommitted using implicit autocommit refactor: begin transaction to automatically commit #41979
   • The database package is deprecated chore: sqlalchemy.database package warnings are irrelevant #41978
   • The declarative_base() function is now available refactor: import from sqlalchemy.orm #41981
   • The Engine.execute() method is considered legacy refactor: replace Engine.execute() with Connection.execute() #41917
   • The legacy calling style of select() is deprecated refactor: update SQLAlchemy select() syntax to 2.0 #40276
   • The "whens" argument to case refactor: update SQLAlchemy case() syntax to 2.0 #40275
   • "User" object is being merged into a Session chore: SQLAlchemy User cascade backref warnings are irrelevant #42360

3. Use the future flag on Engine. https://docs.sqlalchemy.org/en/20/changelog/migration_20.html#migration-to-2-0-step-four-use-the-future-flag-on-engine refactor: engine enforce SQLAlchemy 2.0 #42277

4. Use the future flag on Session. https://docs.sqlalchemy.org/en/20/changelog/migration_20.html#migration-to-2-0-step-five-use-the-future-flag-on-session chore: session enforce sqlalchemy 2.0 #42365

5. Add __allow_unmapped__ to explicity typed ORM models. https://docs.sqlalchemy.org/en/20/changelog/migration_20.html#migration-to-2-0-step-six-add-allow-unmapped-to-explicitly-typed-orm-models refactor: make import/expression layer SQLAlchemy 2.0-compatible #41179

6. Actually bump SQLAlchemy and indirect dependencies.
   By then, chore: Support Flask 3.X and SQLAlchemy 2.X #35117 will hopefully have to do some less heavy lifting. :-)

   • Bump SQLAlchemy and indirect dependencies. chore: Support Flask 3.X and SQLAlchemy 2.X #35117 feat: bump SQLAlchemy to 2.0 and flask-sqlalchemy to 3.1.1 #42803

7. Clean up the unit test set-up again.

   • Streamline unneeded SQLAlchemy deprecation warnings.
   • Remove future flags. chore: Unset future flag in engines and sessions. #43273
   • Annotate models.

Conclusion

As mentioned, I already have some PRs in mind, that I will add to the battleplan.
I do not see any downsides to the refactoring approach of incrementally mitigating the deprecation warnings before the actual bump (except boredom).
Curious to hear your thoughts.

[betodealmeida]

This is great, thanks for working on this!

[rusackas]

@hy144328 what's the next step (or steps) here? Happy to help move things forward, we just might be able to land this in 7.0 if we're quick :D

[hy144328]

Sorry for the inactivity.

Let me get started again, and crush some of the lower-hanging items from the checklist tonight and tomorrow. I.e., those items that are mainly local syntax changes.

I have some me time this weekend that I would like to use to look at the refactor of the ORM models. https://docs.sqlalchemy.org/en/20/changelog/migration_20.html#migration-to-2-0-step-six-add-allow-unmapped-to-explicitly-typed-orm-models
I am tempted to take the 'quick and dirty' path of just adding __allow_unmapped__ to the declarative Base for now.
This would allow us to postpone the full refactor of the ORM models until after the version bump.
The version bump of SQLAlchemy and the associated Flask dependencies carries arguably the highest risk in this whole endeavor.
So the upside of this quick and dirty path is that we get to focus more on the critical step.
The downside is that the type system is weakened because annotations are by-passed.
Let me know if this temporary state is unacceptable.

If things go well, by the beginning of next week, let us bump the SQLAlchemy version.
I will need you or someone else to release the PR for integration tests.
I will probably need some additional help for testing because I do not know how complete the coverage of the integration tests is.

Let me know what you think. Again, let me also try to get a few PRs up tonight to get the ball rolling again.

[rusackas]

That sounds amazing! Let me know if there's any part of it you'd like me to tackle in parallel to help, or you're welcome to take the wheel here and I'll help review/shepherd as best I'm able.

[rusackas]

Probably taking a pass at test coverage is a GREAT place to start, so we can see things fail as we move. The main thing is to be able to pull all this off without causing massive bugs/outages for anyone along the way. Safety first.

[rusackas]

Didn't want to wait on this, so I dug into the actual checklist (the commented-out error: lines in pytest.ini) and ran the suite locally with SQLALCHEMY_WARN_20=1.

As it turns out, that env var is never actually set anywhere in CI, so none of this, including the 3 categories already "locked in", has ever been enforced automatically. #41915 fixes that.

Also tried to knock out a few more categories that looked like free wins from a quick grep, but two of them (as_declarative(), the database package warning) turned out to be tripped by Flask-AppBuilder's and pyhive's own code, not ours, so those stay commented out for now. The other three panned out, one needed a mechanical import-path fix across 101 files for declarative_base(). That's #41914.

@hy144328 does this line up with what you had in mind, or do you already have more of this in flight? Don't want to step on toes.

[rusackas]

Merged #41982

#41981 approved but needs a rebase.

[hy144328]

Just FYI that I have rebased #41979 , #41980 and #41981 .

[hy144328]

Merged #41917! Thank you! #41915 also merged.

I simply rebased #41914 for now, but I'm happy to close it out if that's messing up your implementation plan.

Ah, sorry. I only check #41914 for stuff relevant to #41917 .
I might have caused duplicate work by creating #41981 and some small PRs.
In general, I am open to merging in your #41914 , and then see if my smaller PRs pick up any additional stuff.
Since four eyes see more than two eyes, and some things were definitely caught by unit tests.

[hy144328]

Just FYI that I have rebased #41979 , #41980 and #41981 .

@rusackas Perhaps I need a little help here.
After rebase, end-to-end Playwright tests started to fail for all three pull requests.
I am scanning the error traces but I do not see anything SQLAlchemy related -- only Flask. https://github.com/apache/superset/actions/runs/29287003752/job/86941892825?pr=41979

[rusackas]

Those tests broke on master, and a fix just merged today... I've rebased your PRs and resolved the conflicts, so hopefully they will pass now. Unfortunately you rebased when master was broken... not your fault.

As noted, there are a handful of bot comments that need to be contended with on your PRs, but once those are dealt with and CI is (hopefully) green, I'll re-review. :)

I've marked #41914 as draft and can close it or rebase it later to see if there's anything of value left there. Don't worry too much about it.

[lijia1]

THANK YOU VERY MUCH for doing this!!

I'm a huge fan and a heavy user of Superset at my firm. However, IT dept is threatening to shut it down due to Superset's pin on Python 3.11, which only has 1 year left before reaching End Of Life. Apparently, being able to receive security patches is a big deal at my firm, and I'm sure many firms are in the same shoe.

Apparently, the pin on 3.11 was mainly due to SqlAlchemy. So your work to bump SqlAlchemy to 2.0 is life-saving!!!

[rusackas]

Chipped away at three more of the cascade_backrefs cases: Query (#42210), SavedQuery (#42212), and SqlaTable (#42213). Turns out the callers were already adding everything to the session explicitly, so each one is just cascade_backrefs=False on the backref plus flipping the pytest.ini line on. The only production code leaning on the reverse cascade was an expunge in raise_for_access, now guarded. SqlMetric, TableColumn, and TaggedObject look like the same shape if anyone wants to grab them... the "User" one is trickier since Role.user lives in Flask-AppBuilder. @hy144328 holler if any of this collides with what you have in flight.

[rusackas]

Awesome! Merged in #42223

[rusackas]

Looks like #42221 #42222 need a little extra love to pass CI. Holler if you want a hand.

Feel free to review #42210, #42212, or #42213 if you have time... I'll need a Committer stamp, but your Approval counts for a lot here :)

[hy144328]

My prayers that the integration tests would automatically pass along with the unit tests were not heard.
I am revising #42221 and #42222 now.

Also, I had looked at #42210 , #42212 and #42213 yesterday, and they looked great.
I have added my approval stamp to those PRs now additionally.

[hy144328]

@rusackas #42210 and #42212 finally pass all the integration tests, and are ready for review.
There is only User left, and we are done with refactoring.

[rusackas]

Both merged! Thanks for your help here!

[rusackas]

Maybe we should also add an item to the TODO list to bump all the SQLAlchemy drivers to the newer 2.0 compatible versions. It's basically follow-up work, but we might as well enumerate it.

[hy144328]

I included two more steps from the official migration guide.
Turning on the future flag for the engine is not blocked by the current refactoring.
Turning on the session flag for the session is possibly blocked until we refactor User.

[rusackas]

Approved #42277 but not sure if you want to fix the nit there before I merge it.

[rusackas]

Merged!!!

[lijia1]

Thank you @hy144328 @rusackas for pushing this. Any chance this will make its way to Superset 7.0?

[rusackas]

That's the hope!

[rusackas]

#42542 merged and covers the driver dual-compat prep for step 6: bumped what could bump, and added known_incompatibilities metadata (now rendered in docs) for the ones that can't, Aurora Data API, D1, Kusto, Solr.

I also tried the flask-sqlalchemy 3.0.5 bump in that same PR and had to revert it. Real CI turned up widespread NoneType errors across dashboards/security/tasks tests plus MySQL lock timeouts, capped below 3.0 for now. Dug into it more since, and I don't think it's our session handling at all, it's a gap in Flask-AppBuilder's own FSA-3.x shim: base_legacy.py rebinds our models onto FAB's declarative_base() via create_session()/get_bind() overrides, base.py (the FSA 3.x version) drops all of that and never passes model_class= to SQLA(app). Flagged it on #39278 and asked dpgaspar directly since he wrote FAB.

Also caught two more models missing cascade_backrefs=False from step 2's sweep, SSHTunnel and the two report models. #42787 has that fix, tests green.

So step 6's driver prep is done but the actual core bump is still blocked on the FAB gap getting resolved one way or another.

[EDIT]

Correction on the FAB theory from my last comment. Tested it directly in #42789 (draft, now closed), confirmed the registries actually do get unified with model_class= wired through, and the exact same CI failures happened anyway. So that wasn't it.

Pulled real failure logs this time instead of the summary. db.session.bind is None outright at the point of failure, and a query right after a fixture sets up its data comes back empty. That's back to FSA 3.0's actual session-scoping change being the real suspect, not a registry mismatch. Going to dig into where the bind resolution actually breaks down before claiming a theory again.

[rusackas]

Status check on current master (2026-08-24), since the last update here was "blocked awaiting FAB resolution":

The core bump is done and stable. #42803 (SQLAlchemy 2.0 + flask-sqlalchemy 3.1.1) merged 2026-08-13 and has held for 11 days since, including routine dependabot patch bumps (currently sqlalchemy>=2.0.52,<2.1). A later revert attempt (#43260) was proposed but closed rather than merged, so whatever FSA 3.x session-scoping issue was blocking things in early August got resolved by the time #42803 actually landed.

What's left, for anyone picking up loose ends:

Upstream-blocked, nothing to do but track:

• aurora-data-api, d1, kusto, solr dialects are each hard-pinned to SQLAlchemy 1.4 by their own upstream package (no 2.0 work on their side). Documented via known_incompatibilities metadata in the respective db_engine_specs/*.py and in pyproject.toml.
• sqlalchemy-bigquery works fine under 2.0 but the upstream package was archived 2026-05-16 — an orphaned-dependency risk independent of this migration.

Small follow-ups:

• sqlalchemy-ocient's 2.0 compatibility is flagged "unverified" in pyproject.toml (lower confidence than the other driver bumps in chore(sqla2): bump SQLAlchemy-dependent drivers to dual-compat versions #42542) — worth actually testing.
• build: Bump SQLAlchemy to version 2.0. #42871 (the original tracking PR for the raw version bump) is now closed as superseded by feat: bump SQLAlchemy to 2.0 and flask-sqlalchemy to 3.1.1 #42803.
• SQLALCHEMY_WARN_20 in unit-test CI was a silent no-op under 2.0 (it only had meaning pre-bump) — removed in chore(ci): drop inert SQLALCHEMY_WARN_20 flag from unit-test CI #43495.
• superset/utils/pandas_sqlalchemy_compat.py is a documented-as-obsolete shim (it says so in its own docstring) that's been a no-op since the bump landed — the module plus its call site in superset/__init__.py can be deleted.

Not blocking, but worth naming since "how far can we go" came up:

• The codebase still leans heavily on the legacy Session.query() API (~586 call sites) vs. 2.0-style select()/session.execute() (~11 files). Fully supported either way under 2.0, so this is a modernization opportunity rather than debt — a large, non-urgent refactor if anyone wants to pick at it incrementally.
• flask_appbuilder.Model.__allow_unmapped__ = True (set globally in superset/__init__.py) preserves legacy annotation behavior across all models/mixins. Migrating models to typed Mapped[...] declarations can happen incrementally without needing to flip that flag off as a gate.
• 16 relationship(..., cascade_backrefs=False) call sites remain — correct and required under 2.0.52 today, but the parameter itself is deprecated upstream and may need to go in a future SQLAlchemy release.

FAB: currently pinned >=5.2.2,<6.0.0 and not presenting as a blocker given the bump's stability since 08-13.

Docs: UPDATING.md already has an accurate section covering the bump and the driver caveats above — no gap there.

Net: for the 7.0 release, this is done. What remains is either genuinely out of Superset's hands (upstream dialect support) or optional cleanup/modernization that doesn't need to gate anything.

[rusackas]

Follow-up on the punch list above — two more items closed out:

• fix(ocient): update GIS test fixtures for pyocient's relocated geo types #43496: verified the ocient driver's SQLAlchemy 2.0 compatibility, previously flagged "unverified" in chore(sqla2): bump SQLAlchemy-dependent drivers to dual-compat versions #42542. Found and fixed a real (if narrow) bug along the way: pyocient>=3.9.0 relocated its GIS classes from private top-level names to public ones under pyocient.api, which broke test_ocient.py's fixtures. That breakage was invisible in standard CI since pyocient isn't a default test dependency — only surfaces if you actually install the ocient extra. All 21 tests pass now, confirming the driver itself is fine under 2.0.
• chore: remove obsolete pandas/SQLAlchemy version compat shim #43497: removed superset/utils/pandas_sqlalchemy_compat.py, the shim that let pandas'''s SQL IO tolerate a pre-2.0 SQLAlchemy install. Its own docstring called out removal once Superset requires SQLAlchemy >= 2, which is now the case.

Remaining items are all upstream-blocked (aurora-data-api, d1, kusto, solr dialects with no 2.0 support on their end) — nothing actionable on Superset's side beyond tracking upstream.