flask-security: minimize the database hits

Question

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:

select ... from user where user.id = 'the user identifier'
select ... from role, roles_users ...

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.

did you find a good solution? – IamMashed Mar 27 '20 at 10:35 — IamMashed, Mar 27 '20 at 10:35

b4stien · Answer 1 · 2014-07-13T10:47:56.553

1

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.

edited Jul 13 '14 at 10:47

answered Jul 13 '14 at 10:12

b4stien

1,810
13
14

It's not my choice to decide what SQL sentences Flask-Security sends to the database and when to do it. I am searching for a method to minimice them – Miguel Prz Jul 13 '14 at 12:07

score 0 · Answer 2 · answered Sep 17 '17 at 16:32

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.

flask-security: minimize the database hits

2 Answers2