SQLAlchemy - Database gets hit on every query?
I am currently working with a web application written in Python (and using SQLAlchemy). To handle authentication, the application first validates the user ID in the session and provides it, pulls the entire user record from the database, and stores it for the rest of this request. Another request is also made to check the permissions of the user that it has saved.
I'm new to the world of web application development, but hitting the database for something like this on every request is ineffective in my opinion. Or is this considered normal?
The only thing I've thought of so far is to parse this data once and store what's relevant (most of the data isn't even required for every request). However, this raises the problem of what should happen if this user record is deleted in the interim. Any ideas on the best way to do this?
"hitting the database for something like this in every query is inefficient.
False. And you assumed there is no caching, which is also wrong.
Most ORM layers are perfectly capable of caching rows while storing some database queries.
Most DBMSs have extensive caching, resulting in extremely fast responses to common queries.
All ORM tiers will use consistent SQL, further helping the database in optimizing repetitive operations. (Specifically, the SQL statement is cached, saving parsing and scheduling time.)
"Or is this considered normal?"
True.
Until you can prove that your queries are the slowest part of your application, worry not. Create something that actually works. Then optimize the part you can prove is the bottleneck.
a source to share
For user login and basic permission tokens in a simple web application, I will make sure to store this in a cookie based session. It is true that multiple SELECT queries per query are not a big issue at all, but then again, if you can get some / all of your web queries to execute from cached data without any DB hits, it just adds that much more scalability an application that plans to receive a heavy load.
The problem of changing the user token in the database is done in two ways. First, ignore it - for many use cases, it isn't too much for a user to log out and log back in to get new permissions granted elsewhere (like unix example). Another is that all user string mutations are filtered by a method that also resets state in the cookie-based session, but this is only valid if the user himself is initiating changes through the browser interface.
If OTOH doesn't apply to any of the above use cases, then you probably have to stick with the small amount of database access built into each request.
a source to share
You are mainly talking about data caching as a performance optimization. As always, premature optimization is a bad idea. It is difficult to find out where the bottlenecks are ahead of time, especially if the application domain is new to you. Optimization adds complexity, and if you optimize the wrong things, you not only wasted your efforts, but also made the necessary optimizations more difficult.
Requesting user data is usually a fairly trivial request. You can create a simple benchmark for yourself to see what costs it will bring. If that's not a significant percentage of your budget, just leave it.
If you still want to cache data on the application server, you need to design a cache invalidation scheme.
Possible schemes are to check for changes in the database. If you don't have a lot of data to cache, this really isn't much more efficient than just a reload.
Another option is to simply cache the data. This is a good option if instant visibility of changes is not important.
Another option is to actively invalidate caches on changes. It depends on whether you are only modifying the database through the application and whether you have a single application server or cluster solution.
a source to share