Querying properties of transient models in sqlalchemy

Question

I am using flask-sqlalchemy and flask-login.

My flask login's user loader currently looks like this:

@login_manager.user_loader
def load_user(user_id):
    return User.query.filter_by(id=user_id).options(load_only('id')).first()

This means that every time a user needs to be logged in the load_user() a database query will be run to return a User object. In my case I almost never require other user fields like email, name, etc., so I specify sqlalchemy to only load the id field. This will allow the mysql optimiser to execute the query fairly fast.

However, I don't even need to perform this query because:

When user logged in and is issued a session, there's a guarantee that a unique id exists for him.
Flask-login's cookies are signed using my secret key, so there is no way for a random user to spoof his user id to impersonate some other user by changing his id.

Also, due to some specific business reasons, every logged in user has to send a heartbeat signal every 1 second. This means every second, this user id fetch query will be performed, creating a considerable load on my database server (I have about 1000 users doing this simultaneously). This requirement is non negotiable, and I can't improve speed by other methods, like by using an in-memory server.

I wanted to do this instead:

@login_manager.user_loader
def load_user(user_id):
    return User(id=user_id)

Note that in this case a fake object with the user's id is returned.

From other files, I do

from flask_login import current_user

The above line give me a handle on the currently logged in user.

However, using my proposed method wont work when I want to fetch other attributes of the user, and more importantly its relationships.

It means that I cannot simply do current_user.email, or:

current_user.friends #this is a relationship

I tried this, and it simply returns None. (However, I can get the id of the user like I would normally)

Is there any way to instantiate a model and make sqlalchemy think that whatever attributes I specified when creating the model were the result of a sqlalchemy query? Essentially make sqlalchemy think that only some attributes were loaded using the load_only() method.

This way if I do current_user.email it will fetch the email if it was not loaded already.

harveyslash harveyslash · Accepted Answer · 2018-03-02T17:33:44

After discussing this issue with Michael Bayer (creator of sqlalchemy) here, this is the solution he suggested:

make the object act like detached and expired, then associate with the session:

from sqlalchemy.orm.session import make_transient_to_detached 

@login_manager.user_loader
def load_user(user_id):
    u = User(id=int(user_id))     # will not do any db query
    make_transient_to_detached(u) # This is the suggested solution
    db.session.add(u)             # add the detached object to session
    return u

Later on, its possible to do :

from flask_login import current_user

and access other attributes of the current_user like:

print(current_user.email)  # will perform a database query to fetch email

Querying properties of transient models in sqlalchemy

1 Answers