For the last three years or so, I've been telling anyone who asked that ORM is an antipattern, to be avoided at all costs. I've settled into wrapping all queries in classes, with any parameters exposed as public properties. The SQL is written inside the class, essentially in a template. When necessary, the generated query can change based on the values assigned to the properties. All the mechanics of how the query is executed and passed to the database get rolled up in the base class, with a public method that returns the dataset returned by execution. Then I just run the dataset through transformation functions to get the data structures I actually need - combining multiple result sets if necessary to build really complicated objects when I have to.

I've been building this approach for a couple years now, and I haven't regretted it for a minute. I don't have to fit my databases to the vagaries of the ORM layer, and I have full control over my queries. I NEVER want to go back to ORM :)

> I NEVER want to go back to ORM

Except that you just invented your own ORM.

Think about it: you are encapsulating SQL data into classes, in other words, mapping relational data to objects.

That's an ORM.

You've just broadened the definition of ORM so that any SQL abstraction layer in an OO language becomes an ORM.

That's a rather nonstandard usage.

ORM is "Object Relational Mapper".

If you are taking data out of a relational database and mapping it into objects, you are implementing an ORM.

Seems like I'm sticking to the exact definition of an ORM, aren't I?

> If you are taking data out of a relational database and mapping it into objects, you are implementing an ORM.

No, ORM is a particular approach to doing that; the query abstraction approach described upthread is closer to the DAO pattern, to which ORM is an alternative. People were using RDBMSs to provide a persistence layer for OO programs before ORM was a thing, but as you have broadened the term any use of an RDBMS to store/retrieve data used in an OO program would be "ORM".

"is a technique for" does not mean "includes every technique for".

Spearfishing is a technique for catching fish, but not every technique for catching fish is spearfishing.

I believe the difference here is that one is an ORM framework, while the other is just objects bound to a database.

ORM frameworks usually allow you to drop down to SQL. But then you're stuck in that terrible world of depending on poorly-documented and soon-to-be-deprecated (or already deprecated) internals. Which are pretty much guaranteed to not fit what you really need anyway. Then you have to bridge your hack objects with the "proper" ORM objects. Your crufty hack objects will probably never be seen as first-class citizens in ORM land, forever banished to edge case hell.

I've used this pattern and I like it a lot (for the reasons you say) but I find I still spent way too much time on really mundane stuff... particularly table<-->object mapping if the database table has a lot of columns.

My current approach is:

1. I basically judge ORMs on how easily they allow me to use custom SQL. (ActiveRecord makes this pretty tolerable with find_by_sql) 2. Let the ORM handle as much CRUD and table<-->object mapping as possible 3. If I have complex SQL, I try to wrap it in an appropriate database object (view, sproc, function).

(caveat: I'm a developer but I haven't used ORMs very much.)

Don't most ORMs let you write raw SQL when you really want to? In that case, you could use the ORM for simple things, but revert to raw SQL when you need more power. Or is that not the case?

Yes, but the ORM often influences the schema design. That can be very painful down the road when you realize your tables are actually tables, rather than instances of objects, which would be what your ORM led you to believe.

I think the problem is not that an ORM often influences schema design, it's that Relational Databases/SQL often influence application design.

People complain that an ORM isn't using a relational database effectively. The greatest contribution of the rise of ORMs is that relational databases are hard to use properly.

Bring on the ACID compliant document databases.

No you have it backwards. RDBMS are as they are because maths (relational algebra and calculus). There is deep theory behind doing things this way. You can put data in without needing to know how it will be accessed and used (and vice versa). NoSQL just doesn't have this rigor. You have to tightly couple what creates the data with what consumes it. THAT is just begging for trouble down the line.

That is absurd. Saying that using a certain data access api tightly couples you to it is as crazy as saying using variables tightly couple you to ram (and using ram is obviously bad!).

I honestly don't think you understand what tight coupling means.

You have data. To access the data you use an api. SQL is forcing you to use a generalised API, which is very old, hard to use and more importantly; hard to test.

If you actually want rigor in your SQL API, you use stored procedures. So now you're maintaining two languages (SQL and stored procedures) in addition to your application language.

For me, for most things, I just write a webservice which talks to whatever database I want. That's the API I expose. Anything can consume the API as long as it follows my RESTful spec.

With this architecture, I; 1) Don't have to struggle with SQL, making development faster. 2) Don't need a DBA making development cheaper. 3) Get to write in one language making testing a lot easier, which in turn makes quality higher. 4) can scale easier, picking whatever data storage characteristics are important to me.

Lastly, saying that NoSQL just doesn't have this rigor really makes me wonder what you think of google's bigtable or amazon's simpledb?

That's a good point, I think I agree.

However, my gut tells me that the instance/object nomenclature that ORMs require does a lot more harm than good in the long run.

I think more specifically the rows in your tables is data. And data tends to long outlive even through many application redesigns.

Often that is not the case as ORMs keep a lot of internal state, so if you go bypassing them you no longer get any benefit from the ORM at all.

Either that or you can write in a SQL-like language (e.g. JPQL for JPA).

Depends, some libraries like ActiveRecord for Ruby makes it hard to drop down to SQL (you lose data type conversion, etc).

Also see what the others said about the ORM influencing your database schema.

That's been my experience. From what I can tell the only time ORM tools actually make sense is when you have a lot of tables (or fewer tables with a lot of columns) and your interactions are very simple - i.e. you're mostly dealing with one table at a time.

But this is pretty much an edge case. For simple applications I can write all the SQL in less time than it takes to configure Hibernate, and applications that require me to join seven tables in every query, use analytic functions, or share schemas with other applications, ORM tools don't handle the complexity very well.