CODEWITHSUNDEEP
pythonflaskflask-loginflask-security
Miguel Prz
Miguel Prz

Reputation: 13792

flask-security: minimize the database hits

In my application, I have used flask-security to add authentication and authorization topics. SQLAlchemy is also used as the data provider (with MySQL as backend). The app is working fine.

Then, I did some MySQL tracing, and the log shows me that at every URL requested on the app, the flask-security library send two database queries:

I think this is a performance problem, and I like to minimize these queries. I don't know if there is a configuration feature I'm missing.

Upvotes: 5

Views: 1165

Answers (3)

johnny b good
johnny b good

Reputation: 61

I was unable to find a configuration feature. I encountered this problem with Flask-Security as it was calling the users table for all static files. I was able to work around it using an override of the user_loader. Here is the code I put into my init.py:

# Override user_loader
@security.login_manager.user_loader
def user_loader(user_id):
    if request.path.startswith('/static/'):
        return None
    return user_datastore.find_user(fs_uniquifier=user_id)

Upvotes: 0

turdus-merula
turdus-merula

Reputation: 8854

One option may be to override Flask-Login's user_loader. Though, see this discussion about why it's often necessary to load the user information on each request.

This is how Flask-Security implements the user_loader.

def _user_loader(user_id):
    return _security.datastore.find_user(id=user_id)

This ultimately yields an SQL query.

For posterity, here's how to enable tracing (debugging) of SQL queries issued by sqlalchemy:

import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

Another solution, perhaps a bit over-engineered is to have a separate SQL-lite database for users and roles (for Flask-Login, Flask-Security and so on). In this case, you'd have ~0 latency for reads.

Upvotes: 1

b4stien
b4stien

Reputation: 1850

Without going into further optimization (like using Redis to cache SQL responses or user objects) I don't think you can avoid the first request. In most cases you need the data about the user, and you don't want to store these data in the user's session cookie. Again you could use something like Redis to store these info server-side, indexed by the session ID.

However you could probably use a JOIN to avoid the second request. By doing the first and the second request at the same time it would save you some transport time + the DB would be able to select an appropriate query plan.

Upvotes: 1

Related Questions