Thankfully 1.4 started nudging usage of SQLAlchemy to use the new approaches, so the changes needed weren't that large.
Noteworthy changes:
DB model definitions have been adjusted. The python types now need to be wrapped in Mapped[...], and columns now get defined either with mapped_column OR nothing with SQLAlchemy figuring out the type from the python type (similar to Dataclasses or Pydantic). This largely results in more minimal class definitions especially ones with a bunch of basic columns.
DB session/connection/engine logic adjusted slightly to have fewer edge cases. For the changes here that just required some adjustments to the underlying connection setup, something I'd done on another project already to fix some connection issues. Largely this comes down to connections no longer auto-committing and needing to be in with conn.begin() blocks to function properly.
Raw SQL calls to execute should be wrapped in the text class
DeclarativeBase is better supported and instantiated slightly differently. The Metadata object no longer works globally and has to be attached to the DeclarativeBase to work properly.
Typing for MyPy is built into SQLAlchemy and no longer relies on libraries like SQLAlchemy-stubs
Testing
Since the DB hits virtually everything, I tested everything as thoroughly as possible.
Because of how the SQLAlchemy-stubs we removed work, they need to be completely deleted otherwise MyPy thinks they should run. If you are running outside of the docker container, run poetry install --no-root --all-extras --with dev --sync which will delete extra packages.
Basics
Tests, formatting, linting, all working, only required a few fixes to make SQLAlchemy happy.
SQLAlchemy warnings
When running unit tests, SQLAlchemy will output warnings for deprecated features. The only still usable deprecated feature we had was our getqueries which were adjusted. Prior to the fix, we would see this warning:
Migrations
They still work, adding a few new columns generates what we would expect and uses the mapping config added to the Base class.
Swagger
Was able to successfully use the local swagger endpoints and create/update/read from the DB which was populated like so:
Ticket
https://github.com/navapbc/template-application-flask/issues/82
Changes
Upgraded to SQLAlchemy 2.0, see context section for major changes in this version
Context for reviewers
SQLAlchemy 2.0 comes with an incredibly thorough migration guide: https://docs.sqlalchemy.org/en/20/changelog/migration_20.html
Thankfully 1.4 started nudging usage of SQLAlchemy to use the new approaches, so the changes needed weren't that large.
Noteworthy changes:
Mapped[...], and columns now get defined either withmapped_columnOR nothing with SQLAlchemy figuring out the type from the python type (similar to Dataclasses or Pydantic). This largely results in more minimal class definitions especially ones with a bunch of basic columns.with conn.begin()blocks to function properly.executeshould be wrapped in thetextclassTesting
Since the DB hits virtually everything, I tested everything as thoroughly as possible.
Because of how the SQLAlchemy-stubs we removed work, they need to be completely deleted otherwise MyPy thinks they should run. If you are running outside of the docker container, run
poetry install --no-root --all-extras --with dev --syncwhich will delete extra packages.Basics
Tests, formatting, linting, all working, only required a few fixes to make SQLAlchemy happy.
SQLAlchemy warnings
When running unit tests, SQLAlchemy will output warnings for deprecated features. The only still usable deprecated feature we had was our
getqueries which were adjusted. Prior to the fix, we would see this warning:Migrations
They still work, adding a few new columns generates what we would expect and uses the mapping config added to the Base class.
Swagger
Was able to successfully use the local swagger endpoints and create/update/read from the DB which was populated like so: