[hparadiz]

I've written/worked on several ORMs from scratch. ORMs are the industry standard. When I see posts like this I simply can't take them seriously. All they are saying is "I won't be a team player" and "I don't actually understand the subject matter". The reality is at a certain scale there's an entire orm team that optimizes everything. But even when there's no team involved there's no way you can write anything more optimized because I'm already at the computational limit of how far something can be optimized.

There's no (good) ORM that doesn't let you simply put your own query in.

[swasheck]

I don’t understand this comment because in no way did I express that I’m not the team player. Seems like this is something of a sacred cow for you. Or maybe it’s a language barrier thing, but all I was trying to do was say that as a member of the data platform team, when I recommend handwritten SQL to address specific limitations of an orm, that is the response that I got. Hope this helps.

[hparadiz]

My reply was talking in general terms about the original post.

You wrote the exact opposite of my opinion here which is why I replied to your specifically:

> People who love and advocate the merits of ORM insist that everything be executed through ORM because it introduces too much complexity for them to blend handwritten SQL with the ORM generated queries

I believe strongly that good ORMs expose the ability to put your own queries in. But I can't possibly boil down all the reasons for this in one HN comment.

An ORM is not a query writer. It's a way to map SQL primitives to run time primitives in a static deterministic way backed by a suite of unit tests.

If you have a special query you wanna run that has 10 joins, 2 sub queries, and a derived view that's totally fine. No one says you can't. However remember that statistically 99.9% of all queries are not that.

[hatefulheart]

What optimizations are you making here when at the end of the day performance is dictated by the schema, the query planner and the network?

[bot403]

I read it as "I've optimized the orm to be minimal overhead over raw sql a lot of the time".

[hparadiz]

I've actually benchmarked the overhead for my ORM against every major PHP orm that exists.

https://the-php-bench.technex.us/runs/1

But the speed is irrelevant as long as it's good enough. Notice Laravel's Eloquent at the bottom of the list yet thousands of projects are being built with it regularly.

[hparadiz]

How can I possibly condense 24 years of deep knowledge in one comment for you?

The tldr is if you're ever concatenating strings in order to build a query you're just doing what the entire job of orm is but rolling your own and chances are you'll end up with a bunch of bugs in how you handle well.... Everything.

[hatefulheart]

I think your tone is a bit combative. You can certainly provide the cliff notes but if you want me to believe you’re at working at computational limits whilst talking to me about string concatenation in web dev backend languages I think the burden of proof is on you.

[7bit]

I don't think OP ever expected you to believe anything. He stated his experience and nothing more

[hatefulheart]

Oh it was just a flex?

Ok then!

[swasheck]

the amount of vitriol my comment generated was unexpected. i was sharing that my experience was the opposite of the comment I was replying to. So many people have read things into it that simply do not make sense to me, including this one. It wasn’t a flex, it was a statement of experience that was simply a different experience than the post I was replying to asserted as truth. As a senior member of the data team, I interact with developer teams regularly and suggest manual handwritten sql for particular performance edge cases, and I met with the response I mentioned. It’s not me not being the team player, it’s the development team using the ORM that has decided that the level of effort to maintain handwritten and ORM sequel is too much for their team to handle

[toast0]

> All they are saying is "I won't be a team player" and "I don't actually understand the subject matter".

I get the first part, but not the second.

Preferring to use SQL rather than an ORM + SQL is all about understanding the subject matter, which is the data as it exists in the database.

> The tldr is if you're ever concatenating strings in order to build a query you're just doing what the entire job of orm is but rolling your own and chances are you'll end up with a bunch of bugs in how you handle well.... Everything.

Yeah, so basically don't do this, except when you have to, like concatenating placeholders for a variable size IN query.

There's some classes of applications where it's hard to write all the queries because there's all sorts of mix and match stuff happening. Those are pretty much doomed to poor performance if the tables are large, so I would rather not play on those teams. On the bright side, the limit of a small table gets bigger every ram generation, and table scans on nvme aren't so painful either.

[hparadiz]

We're pointing out the same thing. Someone that uses an ORM knows when they shouldn't use them and I tend to trust that more than someone who simply refuses to use them and ends up recreating an ORM by accident.

[toast0]

> Someone that uses an ORM knows when they shouldn't use them

That's not been my experience. But admittedly, I've usually been brought in when the slow query is killing the database. Then I look at the query that nobody with any subject matter knowledge would have written, come up with an alternate query that will give either the same result or something close enough. Sometimes I have to then dig in and figure out how to make that happen, because the ORM user doesn't always know how to make direct queries.

But it sure did make the easy things easier, as the other poster said.

[hparadiz]

People focus on the query writing aspect of ORMs too much. That's not that primary reason you use an ORM. It's primary purpose is to hydrate objects in the runtime. If I pull a datetime from SQL there's a lot of value in having a single piece of code handle that datetime the same way across the entire stack. I can unit test that handling once across the entire code base. Very few ORMs are aware of how the data is indexed and yes a lot of people will write code that generates a complex WHERE clause against columns that aren't indexed. But that's an understanding problem. I expect someone who uses an ORM to understand SQL well. Including indexes and fixed length tables. Obviously you are encountering code made by people who don't understand this but the problem isn't the ORM. They would have made that mistake with or without an ORM.

[toast0]

> I expect someone who uses an ORM to understand SQL well.

From experience, I don't. ORMs are usually sold as 'learn this instead of learning SQL'. For many, the ORM creates the tables, alters the tables, and queries the tables; they don't see SQL and they don't know SQL. When that works, it works, but when it falls apart, they have to debug the SQL and the abstraction layer. I'd rather have fewer unnecessary abstraction layers.

> If I pull a datetime from SQL there's a lot of value in having a single piece of code handle that datetime the same way across the entire stack.

There's value there, datetimes are very complex, but the rest of the stuff it comes with obscures the value IMHO.

> Obviously you are encountering code made by people who don't understand this but the problem isn't the ORM. They would have made that mistake with or without an ORM.

It's hard to write the kind of complex queries I've seen by hand, and I like to imagine if you out how to do that, you'll also know why it's slow and not need my help... But the ORM is part of the problem, because when you've written bad queries by hand, and I give you a better query (or sequence of queries), it's easy to apply. When you've done it with an ORM, you may not even know where the query is made.

[hparadiz]

You can always make the ORM Model based on a view. Sometimes a background job compiling a simple result set table is the appropriate answer.

Almost all ORMs boil down their queries down to a single query handler so it's actually super easy to find the query.

My ORM for example:

  *Read paths*

  - Models/Factory/Getters/GetAllRecords.php:28 - table(...) when indexField is set.
  - Models/Factory/Getters/GetAllRecords.php:31 - allRecords(...).
  - Models/Factory/Getters/GetAllRecordsByWhere.php:95 - table(...) when indexField is set.
  - Models/Factory/Getters/GetAllRecordsByWhere.php:98 - allRecords(...).
  - Models/Factory/Getters/GetRecordByWhere.php:20 - oneRecord(...).
  - Models/Factory/Getters/GetByQuery.php:9 - oneRecord(...).
  - Models/Factory/Getters/GetAllByQuery.php:9 - allRecords(...).
  - Models/Factory/Getters/GetTableByQuery.php:9 - table(...).
  - Models/Versioning.php:122 - revision table(...).
  - Models/Versioning.php:124 - revision allRecords(...).

  *Write paths*

  - Models/Events/Save.php:41 - insert on save() for phantom records.
  - Models/Events/Save.php:53 - update on save() for existing dirty records.
  - Models/Events/Delete.php:18 - delete by primary key.
  - Models/Events/Destroy.php:24 - insert history row before destroy for versioned models.
  - Models/Versioning.php:180 - insert history row after versioned save.

  Error/retry path

  - Models/Events/HandleException.php:35 - direct $connection->exec(...) for auto-creating missing tables.
  - Models/Events/HandleException.php:43 - direct $connection->query(...) to rerun the failed query after table creation.

  All of those eventually bottom out in IO/Database/StorageType.php:119 for non-result queries via PDO exec, or IO/Database/StorageType.php:149 for result queries via PDO query.

I used to profile all my queries in those two methods but with tools like NewRelic there's no need to slow the code down with profiling cruft.