[devmunchies]

yeah but if the generated SQL doesn't look how you want it, now you gotta optimize it using the ORM's language and not SQL. Tweak the ORM, see the generated SQL, tweak again... etc.

If you're looking at the generated SQL I would rather just use the SQL directly in my code. There's probably features in ORMs where you can write raw SQL and tell it how to map the result to an object but I haven't used an ORM in a while.

[jacobsenscott]

Generally it is the table design, and not the ORM that is the constraint on how you can write your queries. A good ORM lets you customize any part of the query, or even just write plain sql. Most performance problems that pop up as a product matures aren't because the ORM generates "slow" queries, it is because the table design didn't scale. That can't be fixed by writing plain sql.

[devmunchies]

This doesn’t really make sense to me. You don’t just design a good table schema independently and start querying it. The design isn’t a step that comes before the queries. The queries and the design are created in tandem. You design the schemas with the indices and queries in mind, write queries that use your indices. A good design doesn’t magically scale. It’s based on how you set up your indices and write SQL.

A good table design is only good BECAUSE it enables efficient predicate use on the SQL queries.

You can’t just query any column willy nilly, you have to plan it. That’s why I like thinking in SQL with the table definition on-hand.

Example, if I write “SELECT * WHERE x OR y” and “y” isn’t indexed, then this will do a full table scan. Not ok. I need to plan my queries so it does something like “WHERE x OR (y AND z)” where “z” is indexed so it filters by “z” and then “y”. I don’t want to have to try and figure out how to get the ORM to produce that.

[jacobsenscott]

And's and or's are trivial in any good ORM. There are valid reasons to not want to use an ORM, but they are more around the the object/relational impedance mismatch, coupling table design to the domain model, etc.

But the alternative to an ORM is not opaque blobs if SQL hard coded into the app all over. How do you handle SQL injection attacks for example? What if you add/rename/drop a column? Do you just grep you code and edit every blob of sql in the app?

[tomjen3]

You still separate your views from your controllers and sql injection is not an issue when you bind params, but even if you are using an ORM you can't change and add columns without some impact on the code (you need to update forms to add the new value to the created objects, show it, and, presumably, do something useful with it).

And that also assumes that you are using databases as an object store. Databases are also useful to answer questions like: show me the number of users who have signed up each day for the last month.

[devmunchies]

Why would we have table references all over the app? We still use centralized models, just not ORMs.

Have a class representing a table and methods where you hit the database and map the response to an instance of the class.

It’s nice in a typed language when I map what the query will return and the compiler enforces it.

But not all my queries map to a class, but it’s not a big mess since we only use statically typed languages on the server so I still need to map the result to a tuple or dictionary of not a class.

[tsimionescu]

In the end, if you're writing in a non-relational language, using a relational database, and processing/using the DB responses in any way other than just returning them to the user, then you will have an ORM layer/library somewhere. Whether you should be using an off-the-shelf one or not is a different question, but you can't really escape the need to Map between your Object and Relational models (substitute Object for Datatype or Struct if you prefer).

[grey-area]

Ah ok, so you wrote your own mapping of object relations.

[devmunchies]

yes. mapping is the trivial part. The query optimization is the more important part IMO so I like doing it manually.

I guess my problem isn't with ORM's, its with ORM SQL generation.

[aeyes]

> You don’t just design a good table schema independently and start querying it

This is only true for trivial problems, in the real world you are going to have tradeoffs. You can't design a schema for transactional and analytical workload at the same time, yet every type of business needs some sort of analytics on their data.

The great thing about SQL is that you don't exactly need to know what your future queries are going to look like. Or your dataset.

[Someone]

Yes, you don’t exactly need to know what queries will look like, but I don’t think the GP claimed that.

You need to have a fairly detailed knowledge, though. How are you going to make educated trade-offs if you don’t know what kind of queries will be made, how often, and how important it is they run fast?

That’s why, in evolving programs, the database evolves, too, even if the table content stays 100% the same. Moving some tables to faster storage, splitting them horizontally or vertically, adding or removing indices, compressing or no longer compressing fields, updating statistics more frequently, etc.