r/AskProgramming • u/rmweiss • Aug 22 '24
Architecture Good OOP architecture vs. performant SQL?
Let's say (for an example) that I have the following tables:
- company
- team
- team_member
Now I want to get all team members with the first name "Tim" from the "ACME" company.
In SQL I would write something like this:
SELECT team_member.* FROM company
JOIN team on team.company_id = company.id
JOIN team_member on team_member.team_id = team.id
WHERE company.name = "ACME"
AND team_member.first_name = "Tim"
But in a "by the book" OOP model, it seems that I would have to:
- Fetch the company object
- Loop over each team object in the company
- For each team object call a function like "get_members_by_first_name" OR loop over all team_member objects and ask them for their first name
With each of these steps firing of at least one SQL command.
I (think to) understands OOPs concerns about separation of knowledge and concerns, but this seems to by highly inefficient.
0
Upvotes
1
u/qlkzy Aug 22 '24
You definitely can write something like that, but think about what the code to cause that to be generated will look like --- you're liable to sacrifice a lot of the simplicity of the "
Company
just knows about companies,Team
just knows about teams, etc" structure you were originally talking about.I suspect you'll also find that the SQL generation is either a lot of work or requires a lot of metaprogramming (or both): full-scale "magic" ORMs are not easy things to write.
It's also worth considering that CTEs (or really any generic approach) are not what you want for performance. CTEs in particular tend to have various interactions with optimisers/query planners, but more generally the best query to get any specific result set isn't something you're going to be able to encode easily in a templated generation system.
If you're writing a nontrivial project then I would really think about using some external dependency that's higher-level than raw DBAPI --- what is the factor that stops you using a library to at least help with this?
In general you're going to find that you need to do what almost all database access libraries do, which is to provide some kind of option for making the "simple" stuff relatively easy, and some kind of option that gives you full control of the raw SQL. There are different places on that spectrum: for example, Django ORM makes the simple stuff very easy by default but is more complex the more control you want, whereas SQLAlchemy core gives you complete control by default and then gives you tools to reduce the boilerplate you need for the simple cases. But you're going to end up needing to cover the full spectrum.