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

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

   • Streamline unneeded SQLAlchemy deprecation warnings.

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?