Hacker News new | ask | show | jobs
by applejacks 1361 days ago
Sure, this is a simple example -- and you are right, if you are just writing a few selects, you may not find any value.

ORMs (or query-builders, as some like to draw a distinction there) become more valuable as you use them to compose and transform queries. SQL is decidedly not composable.
1 comments
10000truths 1361 days ago
You can compose SQL queries with subqueries.

My experience with ORMs is that they’re a minefield of performance cliffs. It’s easy to accidentally generate suboptimal SQL or introduce additional round trips unless you’re very careful about the code you write, at which point you might as well write SQL directly and be at the mercy of one less black box.

link
applejacks 1361 days ago
If you compose SQL with subqueries you tend to do less composition in general (because you are restricted in what you can compose), and you are more at the mercy of the query planner, which is its own black box.

I'd rather simply understand how my ORM generates queries, and then use the ORM to get the full power of my language of choice in creating the right SQL queries.

link
to11mtm 1361 days ago
> I'd rather simply understand how my ORM generates queries,

Depending on the ORM this may be be far from trivial. Despite being a C# dev rather than a DBA I can say that I have a better understanding at least one DBMS's query planner than what Entity Framework will generate for certain queries [0].

I'll note that -good- MicroOrms can make 'subqueries' in a fairly composable way.

I think with a manual SQL, you can still make a 'good' mini-DSL with subqueries, but it will take some thought to do well. Actually, with -extremely- thoughtful design it can be far more performant and productive, but IMO the complexity would need to show ROI to be worth it.

But in -either- case, in the long run the restrictions help with perf and maintenance. Whether it's hand-written mini-DSL or a good MicroORM DSL, you have a much better mechanical sympathy for what you're doing to the DB, and it becomes easier to write Table designs that are performant.

> and then use the ORM to get the full power of my language of choice in creating the right SQL queries.

Now you have to learn an ORM, a Query planner, -and- how to make them play nice.

[0] - There has been more than one shop where I was the 'EF Expert'... take that as you will.

link