Hacker News Reader

Comment by Waterluvian | Hacker News Reader

I’ve done about ten years of multi-hat development without knowing SQL beyond the basics because an ORM fits my use case well: CRUD of a few million records across a dozen tables utilized internally only be a few hundred humans and thousands of robots, the entire backend written and maintained by 5% of one engineer.

My lessons are:

- you might not need an ORM, but it’s also fine if it’s the right tool. (Though I wonder if it should be inverted: you might not need SQL)

- I wish I learned more SQL just to know it better.

- don’t early optimize, but do read up on how you do optimizations with your ORM. That way you can identify when to get ahead of performance woes.

- set up a profiling system asap. For example, Django Silk.

View on HN

Learning SQL is a software dev superpower at the moment. So many devs don't know SQL and it causes such pain. But mostly invisible pain because if you don't know SQL then you don't understand the horrible things your ORM is doing.

Absolutely.

I had a phase years back of chasing performance issues because your ORM does stuff like thousands of related queries for data you don’t actually need.

Exactly! Devs should learn SQL and should learn how their ORM works, what it is doing "behind the scenes".

It is also a good idea to keep a sql profiler open when working with the application to see which queries are generated. When you do that regularly, you can often fix issues before they really become a problem. This is also useful even if you do not use an ORM. For example, an action might execute a loop with a query on the inside of the loop. It might be more performant to retrieve the data using one query and then loop over the results of that query.

I don't mind an ORM per se, what I don't like is when the ORM bleeds into the whole system (like in Django). If there is an ORM between the database layer or not doesn't matter, but if you're passing fat objects around where touching any property accidentally does a query, or it's impossible to change a table because the details have been solidified in code all over the place, it's not very nice.

For me it would be a red flag if the ORM objects exist in any context outside the views and serializers. It would suggest that I'm mixing business logic and database/API logic.

In the case where you don't want to separate Database/API from your business logic, and you just want to use SQL to directly manipulate the database, then yeah, SQL seems fine. While a lot of things make me nervous about that prospect, it probably makes sense in certain contexts where the alternative feels far too heavy too soon.

One key problem I have with using an ORM that way is that the ORM gives you an opinionated representation of of the database schema. And the shape of your database is rarely the ideal shape of your data structures for use in business logic. I'll inevitably wish I had a view layer in the middle.

This approach is definitely swimming against the tide with Django. I’ve spent a lot of time with Django, and a lot of time trying to work out how to best build complex systems with it. I don’t think that for Django’s case the answer is anything close to being as simple as “don’t let the models go anywhere”, unfortunately. Though I am really intrigued to try it and see how it goes.

I've in some apps tried to use a pattern where only certain files are allowed to touch the model classes (selectors.py, services.py). Something queried from the selector is mapped to a dumb pydantic object before being returned. To change something you need to call a function in services, can no longer modify a value on the object and call save.

Some drawbacks is that it can cost you a few queries when you want to select related. Or you need to make a specific function for selecting the relation, but then have a different pydantic return type now with that value prefetched. So a bit more boilerplate, but no longer any surprises

This. There is no easy way to track down performance killers anymore, and someone will inevitably tank performance with a "this change shouldn't have affected that query" change. Huge problem in every Rails codebase I've worked in over the years, and it's not like it's anyone's fault the framework encourages this.

Reply on news.ycombinator.com