relationship query_class in SQLAlchemy 1.4.0b3

[Ahmed]

Hello,

It seems that SQLAlchemy 1.4.0b3 ignores relationship()  query_class parameter. Here's the snippet that works with 1.3 but doesn't with 1.4:

class Parent(db.Model):
    __tablename__ = "todo"

    id = db.Column(db.Integer, primary_key=True)

    # ... Column mappings

    children = db.relationship("Child", backref="todo", query_class=DerivedQuery, lazy="dynamic")

class Child(db.Model):
    __tablename__ = "todo"

    # ... Column mappings
    parent_id = db.Column(db.Integer, db.ForeignKey("todo.id"))

assert isinstance(p.children, DerivedQuery)

In 1.4, children attribute is always an instance of AppenderQuery regardless of the query_class value. I might have missed something above though.

[Mike Bayer]

this is a bug.  however, the Query object is legacy.   what is your actual use case?  there are better ways to achieve them now.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/4454277c-b3a1-484e-b0e5-aef3e72eeb01n%40googlegroups.com.

[Mike Bayer]

this will be fixed in https://github.com/sqlalchemy/sqlalchemy/issues/5981  where I've reverted entirely some changes to AppenderQuery that made it work more in 2.0 style.  As Query is going to be present in 2.0, "dynamic" relationships will remain also as legacy.   They are superseded by explicit use of the with_parent() filtering construct.

On Thu, Feb 25, 2021, at 3:25 PM, Ahmed wrote:

[Ahmed]

Hi Mike - Thank you for your insights. Actually, this is part of upgrading Flask-SQLAlchemy library dependency to 1.4.0b3 and eventually 2.0. The snippet above is extracted from a test case that didn't pass against 1.4.0b3.

I've checked sqlalchemy.orm.with_parent (Python function, in Query API) documentation entry, however, it's not clear to me how with_parent construct can fit in the implementation instead of Query. I guess it would require a major change in how the library (Flask-SQLAlchemy) is currently designed as it functionally extends sqlalchemy.orm.Query and pass the extended class to relationship and other constructs as well.

[Mike Bayer]

On Fri, Feb 26, 2021, at 8:04 AM, Ahmed wrote:

Hi Mike - Thank you for your insights. Actually, this is part of upgrading Flask-SQLAlchemy library dependency to 1.4.0b3 and eventually 2.0. The snippet above is extracted from a test case that didn't pass against 1.4.0b3.

I've checked sqlalchemy.orm.with_parent (Python function, in Query API) documentation entry, however, it's not clear to me how with_parent construct can fit in the implementation instead of Query. I guess it would require a major change in how the library (Flask-SQLAlchemy) is currently designed as it functionally extends sqlalchemy.orm.Query and pass the extended class to relationship and other constructs as well.

yes so, SQLAlchemy 2.0's approach is frankly at odds with the spirit of Flask-SQLAlchemy.    The Query and "dynamic" loaders are staying around largely so that Flask can come on board, however the patterns in F-S are pretty much the ones I want to get away from.

2.0's spirit is one where the act of creating a SELECT statement is a standalone thing that is separate from being attached to any specific class (really all of SQLAlchemy was like this, but F-S has everyone doing the Model.query thing that I've always found to be more misleading than helpful), but SELECT statements are now also disconnected from any kind of "engine" or "Session" when constructed.   

as for with_parent(), with_parent is what the dynamic loader actually uses to create the query.  so this is a matter of code organization.

F-S would have you say:

user = User.query.filter_by(name='name').first()

address = user.addresses.filter_by(email='email').first()

noting above, there's no "Session" anywhere.  where is it?   Here's a Hacker News comment lamenting the real world implications of this: https://news.ycombinator.com/item?id=26183936 

SQLAlchemy 2.0 would have you say instead:

with Session(engine) as session:

    user = session.execute(

          select(User).filter_by(name='name')

    ).scalars().first()

  

   address = session.execute(

       select(Address).where(with_parent(user, Address.user)).filter_by(email='email')

   ).scalars().first()

Noting above, a web framework integration may still wish to provide the "session" to data-oriented methods and manage its scope, but IMO it should be an explicit object passed around.  The database connection / transaction shouldn't be made to appear to be inside the ORM model object, since that's not what's actually going on.

If you look at any commentary anywhere about SQLAlchemy, the top complaints are:

1. too magical, too implicit

2. what's wrong with just writing SQL?

SQLAlchemy 2.0 seeks to streamline the act of ORMing such that the user *is* writing SQL, they're running it into an execute() method, and they are managing the scope of connectivity and transactions in an obvious way.   People don't necessarily want bloat and verbosity but they do want to see explicitness when the computer is being told to do something, especially running a SQL query.  We're trying to hit that balance as closely as possible.

The above style also has in mind compatibility with asyncio, which we now support.  With asyncio, it's very important that the boundary where IO occurs is very obvious.  Hence the Session.execute() method now becomes the place where users have to "yield".  With the older Query interface, the "yields" would be all over the place and kind of arbirary, since some Query methods decide to execute at one point or another.  

Flask-SQLAlchemy therefore has to decide where it wants to go with this direction, and there are options, including sticking with the legacy query / dynamic loader, perhaps vendoring a new interface that behaves in the flask-sqlalchemy style but uses 2.0-style patterns under the hood, or it can go along with the 2.0 model for future releases.       From SQLAlchemy's point of view, the Query was always not well thought out and was inconsistent with how Core worked, and I've wanted for years to resolve that problem.

- mike

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/087934f8-d7fb-4062-8b2a-9d623a2e7941n%40googlegroups.com.

[Ahmed]

yes so, SQLAlchemy 2.0's approach is frankly at odds with the spirit of Flask-SQLAlchemy.    The Query and "dynamic" loaders are staying around largely so that Flask can come on board, however the patterns in F-S are pretty much the ones I want to get away from. 

2.0's spirit is one where the act of creating a SELECT statement is a standalone thing that is separate from being attached to any specific class (really all of SQLAlchemy was like this, but F-S has everyone doing the Model.query thing that I've always found to be more misleading than helpful), but SELECT statements are now also disconnected from any kind of "engine" or "Session" when constructed.

 

as for with_parent(), with_parent is what the dynamic loader actually uses to create the query.  so this is a matter of code organization.
F-S would have you say:

 

user = User.query.filter_by(name='name').first()
address = user.addresses.filter_by(email='email').first()

 

noting above, there's no "Session" anywhere.  where is it?   Here's a Hacker News comment lamenting the real world implications of this: https://news.ycombinator.com/item?id=26183936  

 

SQLAlchemy 2.0 would have you say instead:

 

with Session(engine) as session:
    user = session.execute(
          select(User).filter_by(name='name')
    ).scalars().first()
   
   address = session.execute(
       select(Address).where(with_parent(user, Address.user)).filter_by(email='email')
   ).scalars().first()

 

Noting above, a web framework integration may still wish to provide the "session" to data-oriented methods and manage its scope, but IMO it should be an explicit object passed around.  The database connection / transaction shouldn't be made to appear to be inside the ORM model object, since that's not what's actually going on.

The newer design indeed provides a clearer view of the session.

If you look at any commentary anywhere about SQLAlchemy, the top complaints are:

1. too magical, too implicit

2. what's wrong with just writing SQL?

SQLAlchemy 2.0 seeks to streamline the act of ORMing such that the user *is* writing SQL, they're running it into an execute() method, and they are managing the scope of connectivity and transactions in an obvious way.   People don't necessarily want bloat and verbosity but they do want to see explicitness when the computer is being told to do something, especially running a SQL query.  We're trying to hit that balance as closely as possible.

The above style also has in mind compatibility with asyncio, which we now support.  With asyncio, it's very important that the boundary where IO occurs is very obvious.  Hence the Session.execute() method now becomes the place where users have to "yield".  With the older Query interface, the "yields" would be all over the place and kind of arbirary, since some Query methods decide to execute at one point or another.   

Flask-SQLAlchemy therefore has to decide where it wants to go with this direction, and there are options, including sticking with the legacy query / dynamic loader, perhaps vendoring a new interface that behaves in the flask-sqlalchemy style but uses 2.0-style patterns under the hood, or it can go along with the 2.0 model for future releases.       From SQLAlchemy's point of view, the Query was always not well thought out and was inconsistent with how Core worked, and I've wanted for years to resolve that problem.

I'm not authorized to talk on behalf of F-S but IMO, these options could be milestones applied in parallel toward migration to 2.0. However, a question arises here, that you might have already seen, which is: given the major leap in how SQLAlchemy 2.0 is designed, is it better to think of rebuilding medium+ projects for 2.0 while maintaining existing codebases for 1.3? In other words, how much will 2.0 be backward compatible with 1.3?

 A. 

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f161e6b7-b8b1-457c-bd96-b4e2494b52f8%40www.fastmail.com.

[Mike Bayer]

On Mon, Mar 1, 2021, at 9:45 AM, Ahmed wrote:

I'm not authorized to talk on behalf of F-S but IMO, these options could be milestones applied in parallel toward migration to 2.0. However, a question arises here, that you might have already seen, which is: given the major leap in how SQLAlchemy 2.0 is designed, is it better to think of rebuilding medium+ projects for 2.0 while maintaining existing codebases for 1.3? In other words, how much will 2.0 be backward compatible with 1.3?

I'm hoping this is thoroughly addressed in the new documentation in particular https://docs.sqlalchemy.org/en/14/changelog/migration_14.html and https://docs.sqlalchemy.org/en/14/changelog/migration_20.html 

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAKwjfOB%2BeToncrsTp7AC8%2Bfa5T-bdDuq%2Bbia81_A9YM1Lp10cw%40mail.gmail.com.

[Jonathan Vanasco]

"is it better to think of rebuilding medium+ projects for 2.0 while maintaining existing codebases for 1.3? In other words, how much will 2.0 be backward compatible with 1.3?"

I am saying the following as a general user, and not a past contributor to this project:

As per the Release Status system (https://www.sqlalchemy.org/download.html#relstatus) when 1.4 becomes the official "Current Release", 1.3 will drop to "Maintenance" status.  I believe we can expect that, when 2.0 becomes the "Current Release", 1.4 will drop to "Maintenance" and 1.3 will drop to "EOL".

IMHO, while I might prioritize some migration work based on the size of a project, if any given project is expected to be undergoing active development or be deployed in 2022 and beyond, they should start planning for the "2.0" style migration in their sprints. I can't stress this enough, my metric would be active-use and active-development, not the size of the codebase.

Personally, I would prioritize adapting projects to deploy on 1.4 as the ASAP first step -- there are a few small backwards incompatibilities between 1.4 and 1.3.  I still run everything on 1.3, but we test and develop against 1.4 -- using comments. docstrings to note what changes will be required in 1.4 -- or "switch" blocks so CI can run against both versions.  

I strongly recommend doing all new work in the 2.0 style, and start scheduling the 2.0 migration into sprints. Building anything against 1.3 right now is really doing nothing but assuming technical debt, and it's going to be much easier (and much less work!) planning for this change now.  I would not want to be in a situation where one or more projects require an EOL version, and there are critical features/bugfixes in the newer branch.

You're likely to get a good chunk of time out of 1.4, but I would not target 1.3 at this point.