61–70 of 288 posts
Earlier quoted context omitted.
I have decades of experience with databases and I happily use ORMs. You're conflating ORM with people who know nothing about databases. Why?
From my experience, ORMs allow folk who know nothing about databases to continue knowing nothing about databases
Earlier quoted context omitted.
I find this ends up being 90% of SELECT queries. Usually when selecting data you want to retrieve a bunch of related objects too. Often with complex criteria for which objects to pick. And doubly so for "list" type endpoints where you're selecting many records. I tend to use the ORM function CUD operations, and just write raw SQL for SELECTs unless they're super-simple. > It's also very hard if not impossible to gues…
> It's almost no extra work to just write these optimised in the first place Not everyone is capable of quickly optimizing SQL, and I don't think it's an absolutely necessary skill to build a decent application. Junior devs can pick up on this skill over time and as long as they can manage to avoid any obvious footguns, using the ORM is fine most of the time. Writing queries that are optimized in the first place just…
I mean that's true, but equally not everyone is capable of using an ORM. I don't think SQL is inherently any harder to learn.
At my last job, I had juniors who had never used SQL at all productive in SQL within a couple of weeks, and using "complex" SQL like JSON aggregation and windows function with a few months. They were a little intimidated by it when they started, but didn't find it too hard to learn in the end.
Earlier quoted context omitted.
>Things they usually aren't very good at are when you start trying to do things that require a lot of optimization I find this ends up being, like, 1 or 2% of queries. It's also very hard if not impossible to guess which queries will end up in that group. You're better off building it with the ORM first and breaking out SQL later when you are trying to performance optimize. There is also a small % of queries which us…
I find this ends up being 90% of SELECT queries. Usually when selecting data you want to retrieve a bunch of related objects too. Often with complex criteria for which objects to pick. And doubly so for "list" type endpoints where you're selecting many records. I tend to use the ORM function CUD operations, and just write raw SQL for SELECTs unless they're super-simple. > It's also very hard if not impossible to gues…
What is it that you do here that can't be handled by, say, django's workhorses - filter and select_related?
If it's impossible to write 90% of your queries in an ORM my suspicion would be that you're either not using the ORM correctly or you're using a crappy ORM.
If you're going to end up querying all the fields and putting them into a model like this dataclass anyways... Django can do that for you. If you're going to later pick and choose what fields you query on the first load, and defer other data til later.... Django can do that for you. If you're going to have some foreign relations you want to easily query.... Django can do that for you. If you're doing a bunch of joins…
The main problem I've encountered with complaints surrounding ORMs usually tend to be the result of trying to overfit the ORM in a certain way. ORMs are, for the most part, good at the CRUD operations - that is to say, they easily translate SELECT, UPDATE, INSERT and DELETE operations between conventional class objects and database rows. Things they usually aren't very good at are when you start trying to do things t…
If you're going to end up querying all the fields and putting them into a model like this dataclass anyways... Django can do that for you. If you're going to later pick and choose what fields you query on the first load, and defer other data til later.... Django can do that for you. If you're going to have some foreign relations you want to easily query.... Django can do that for you. If you're doing a bunch of joins…
Having written the sort of SQL-inline code the author talks about, then refactored the whole thing to use Django: Django's ORM solves waaaay more problems than it creates in this regard.
Earlier quoted context omitted.
I have decades of experience with databases and I happily use ORMs. You're conflating ORM with people who know nothing about databases. Why?
From my experience, ORMs allow folk who know nothing about databases to continue knowing nothing about databases
Why stop there? Let's ban RDBMS. If you can't contemplate a binary file structure and index strategy perfectly tailored to your application's needs ahead of using an RDBMS, why should we deign to let you use an abstraction layer with clever query planning and algorithms?
It's all too easy to morally 'ban' people from technology and gatekeep it behind wishy-washy nonsense like "ORMs are bad for beginners."
Firstly (1); "I want to use the ORM for everything (table definitions, indexes, and queries)"
Then second (2), on the other extreme: "I don't want an ORM, I want to do everything myself, all the SQL and reading the data into objects".
Then thirdly (3) the middle ground: "I want the ORM to do the boring reading/writing data between the database and the code's objects".
The problem with ORMs is that they are often designed to do number 1, and are used to do number 3. This means there's often 'magic' in the ORM, when really all someone wanted to do was generate the code to read/write data from the database. In my experience this pushes engineers to adopt number 2.
I'm a big fan of projects like sqlc[1] which will take SQL that you write, and generate the code for reading/writing that data/objects into and out of the database. It gives you number 3 without any of the magic from number 1.
With SQLAlchemy, I come for the type checking. I stay for the Alembic migrations.
I feel this, sort of. I taught myself how to code by writing a Python bot (among other things), and eventually needed some sort of database handling to make things work. I decided on teaching myself basic SQLite, and just did it raw with `sqlite3`. Currently 50% of my anxiety when it comes to my bot is related to database matters. There's no type checking so I gotta be careful when writing them, and stuff might blow…
Earlier quoted context omitted.
I find this ends up being 90% of SELECT queries. Usually when selecting data you want to retrieve a bunch of related objects too. Often with complex criteria for which objects to pick. And doubly so for "list" type endpoints where you're selecting many records. I tend to use the ORM function CUD operations, and just write raw SQL for SELECTs unless they're super-simple. > It's also very hard if not impossible to gues…
>I find this ends up being 90% of SELECT queries. Usually when selecting data you want to retrieve a bunch of related objects too. Often with complex criteria for which objects to pick. What is it that you do here that can't be handled by, say, django's workhorses - filter and select_related? If it's impossible to write 90% of your queries in an ORM my suspicion would be that you're either not using the ORM correctly…
- Complex joins
- Complex WHERE clauses with mixes of AND and OR (with parentheses)
- JSON aggregation
- Window functions
tend to require quite heavyweight syntax in ORMs (e.g. nested lambda functions). Whereas the corresponding SQL tends to introduce much less noise.
It's basically just another case of a dedicated language being nicer to use than a DSL embedded into a general purpose language. Normally it's not worth creating a whole language just for nicer syntax, but in the case of SQL the language already exists! So why not use it.
Earlier quoted context omitted.
I find this ends up being 90% of SELECT queries. Usually when selecting data you want to retrieve a bunch of related objects too. Often with complex criteria for which objects to pick. And doubly so for "list" type endpoints where you're selecting many records. I tend to use the ORM function CUD operations, and just write raw SQL for SELECTs unless they're super-simple. > It's also very hard if not impossible to gues…
> It's almost no extra work to just write these optimised in the first place Not everyone is capable of quickly optimizing SQL, and I don't think it's an absolutely necessary skill to build a decent application. Junior devs can pick up on this skill over time and as long as they can manage to avoid any obvious footguns, using the ORM is fine most of the time. Writing queries that are optimized in the first place just…
Using a case-sensitive filter (default for Django) in a DB with case-sensitive collation (default in Postgres)? Django will helpfully cast the tuple and your query to UPPER to match it for you, and the former wrecks indexing.
Checking if a string ends with something else? Goodbye, index.
I _think_ the latter can be worked around in PG with a GIN index, but I’m not positive (I work with MySQL much more). And in any case, you’d have to know to create that, and I imagine most devs won’t.
Fixing seemingly tiny things like that have a massive impact on large table query speed.