Prepare to migrate to SQLAlchemy 2.0 in next minor version of CKAN to pave the way for CKAN 3.0

[jqnatividad]

As SQLAlchemy 2.0 nears release https://docs.sqlalchemy.org/en/20/changelog/changelog_20.html, it'd be great if the next minor version of CKAN after 2.10 moves to SQLAlchemy 2.0, which further paves the path to 3.0.

As CKAN 3.0 will take a while, moving to SQLAlchemy 2.0 before then ensures that the DB layer will be on a solid foundation, as SQLAlchemy 2.0 gets rid of a lot of technical debt as it supports Python 3.0 only, removes old patterns that have been discouraged for many years, and has major performance improvements.

https://docs.sqlalchemy.org/en/20/changelog/whatsnew_20.html#

Also, there is a step-by-step, detailed migration guide:

https://docs.sqlalchemy.org/en/20/changelog/migration_20.html

This presentation of the "caterpillar to butterfly" transformation of SQLAlchemy by its creator - Mike Bayer is also instructive.
https://www.youtube.com/watch?v=1Va493SMTcY

Though it was given in 2021 and quite long - it really makes the case for SQLAlchemy 2.0

[pdelboca]

+1 on this!

I have suggested that the next minor release should be an upgrade of pending requirements: #7083

The current pattern we are using to build our engine and initialize the database deprecated in 2.0 (I did a small try) so it will not be an easy task. It will also be necessary to migrate all of datastore extension logic since most of it is based in implicit execution (also deprecated in 2.0)

Edit:

The only issue on doing it on a minor release is that it can potentially include breaking changes for all extensions since the API of our Session object will change. I don't think it is viable to provide a compatibility for previous versions of SQLAlchemy. (Or maybe yes using future?)

[jqnatividad]

Thanks @pdelboca for the +1.

It's not going to be easy, but IMHO, it's well worth the effort.

And as luck would have it, SQLAlchemy 2.0 was just released today!
https://www.sqlalchemy.org/blog/2023/01/26/sqlalchemy-2.0.0-released/

The migration can also present opportunities to make fundamental database changes that make CKAN even better - perhaps, jumping from Postgres 10 (which is now EOL) to Postgres 14 or 15 - which by itself, will introduce major performance improvements.

Things like fully embracing JSONB; being able to query the database using SQL/JSON Path; and use generated columns (both introduced in Postgres 12).

As for the Datastore, perhaps, we can just create a new backend without replacing the existing Postgres backend, that's optimized for Postgres 14/15, and/or use psycopg3 (upgrading from psycopg2 at the same time) instead.