> 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.
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.