8 ms·

> If you do any kind of dynamic SQL it can get pretty gnarly though This can be addressed with typesafe SQL-builders like jooq [1]. I got an OLAP application w

> If you do any kind of dynamic SQL it can get pretty gnarly though This can be addressed with typesafe SQL-builders like jooq [1]. I got an OLAP application with plenty of gnarly SQL broken up in individual business rules. The best is that I don't even touch the SQL much. I expose it read-only to the users, hidden under an advanced mode button; and when they want to change things it often comes already written in SQL (mostly filters and projections). New queries come in requests to mix already-existing parts. And it helps technical knowledge be shared. Win-win all around. Jooq works well with CRUD/OLTP apps as well. And when you have a problem, you don't have to debug both the ORM and SQL. > Then it's important to learn how to optimize performance. Starting with IO at the db level, and execution plan in the db. And ending right there without an ORM. Neither you nor your ORM are going to be smarter than the planner. For starters do you even collect statistics about your data? And your ORM doesn't even know what the downstream processing will be. > Lastly ORMs deal with the issues an application typically has to deal with anyways. Such as mapping to objects, Hopefully included with the typesafe SQL builder, but not always needed. > detecting changes, Plenty of SQL features for an audit trail > caching, Well maybe there. But if read-availability is your problem, reading from replicas gets you very far. > concurrency Comes out of the box with MVCC RDBMS; ie good'ol postgres, mysql ---- I'm not going back to ORMs /rant [1] https://www.jooq.org/ https://www.jooq.org/

nightski 4y ago
I only meant performance in context of the SQL execution, not the ORM. Agreed that is by far the most important. If one does use a query builder/ORM though it is also important to understand how that query builder/orm dsl maps to actual sql (even if it is a straightforward transformation). I love query builders! Especially type safe ones. But if you have a complex domain doing updates can get messy. You are going to write a lot of code mapping domain objects to SQL updates. It's not insurmountable, but you are basically re-inventing an ORM. Maybe find a lightweight ORM for this purpose (The command side of CQRS). But yeah if you don't need one that's great! I wasn't trying to convince anyone to use one, just that they can be worthwhile to learn. Personally I like using either direct SQL or query builder style interface for reads/queries. Then I like using an ORM with domain objects for commands/updates. But I deal with a lot of complex domains in finance, supply chain optimization, production planning, etc...

nightski 4y ago

I only meant performance in context of the SQL execution, not the ORM. Agreed that is by far the most important. If one does use a query builder/ORM though it is also important to understand how that query builder/orm dsl maps to actual sql (even if it is a straightforward transformation). I love query builders! Especially type safe ones. But if you have a complex domain doing updates can get messy. You are going to write a lot of code mapping domain objects to SQL updates. It's not insurmountable, but you are basically re-inventing an ORM. Maybe find a lightweight ORM for this purpose (The command side of CQRS). But yeah if you don't need one that's great! I wasn't trying to convince anyone to use one, just that they can be worthwhile to learn. Personally I like using either direct SQL or query builder style interface for reads/queries. Then I like using an ORM with domain objects for commands/updates. But I deal with a lot of complex domains in finance, supply chain optimization, production planning, etc...