[charles-salvia]

The main reason is the object-relational impedance mismatch[1]. Basically, programmers like working with objects that have data fields. This is because most modern, widely-used programming languages treat objects/aggregates with data fields as a first class concept. But SQL isn't designed around objects with fields, it's designed around tables, rows, and result sets from queries. Therefore, working with SQL in most modern programming languages generally requires layers of annoying result-set->object or object->row plumbing/conversion code. (Not to mention the vagaries of type conversions.) Of course, these days, this problem can be substantially mitigated to a certain extent by clever ORMs, but an ORM is generally a leaky abstraction at best. Obviously, whether or not any of this bothers you will depend on your use cases and a lot of other factors.

[1] https://en.wikipedia.org/wiki/Object-relational_impedance_mi...

[MarHoff]

But SQL isn't designed around objects with fields, it's designed around tables and rows.

I think a more correct analogy would be that table are like classes, columns are the properties, and rows are instances. And so defining foreign keys is like setting a pointer to a parent instance.

There is not direct analogy for methods, but you can use function/trigger to do the same job.

PostgreSQL is actually an object-oriented RDMBS, it's not because you are meant to manipulate these objects through SQL that they are less powerful. And SQL is actually Turing Complete with PostgreSQL.

It's clearly not convenient for general programming, but as soon as data manipulation is involved you benefit from a lot of built-in optimization.

[metaphorm]

> defining foreign keys is like setting a pointer to a parent instance.

I can't agree with that statement, though I'm sympathetic to why you would say it. A foreign key is something that essentially doesn't translate from the world of Tables/Rows to the world of Classes/Objects.

In the relational data model the foreign key is a convenient place to include an index for joining on. What does join even mean between two classes? The closest you'll typically get is nested objects, but that's not quite right. A row produced from table joins isn't a nested data structure. It's still flat. The join operation in relational algebra has no direct equivalent in OOP.

[judah]

This breaks down when you deal with classic object oriented concepts like inheritance, encapsulation, or polymorphism.

This is why object/relational mapping (ORM) has been called the Vietnam of Computer Science[0].

[0]: http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...

[MarHoff]

Sorry if I was unclear, I'm not advocating for ORM.

And yes it breaks down because it is similar but different. Row "instances" can perfectly exists outside a table and even be defined outside any table via composites types or even on the fly.

Just saying a RDMS can be a powerful object-oriented environment using solely SQL and no ORM at all.

[talaketu]

> tables are like classes, columns are the properties, and rows are instances.

tables are like functions, columns are the arguments, rows are the invocations

[MarHoff]

functions are actually functions...

[talaketu]

functions are relations

[corpMaverick]

I spent years trying to hide the database. Now days I just embrace it. The database is there. Tables and rows are as real as invoices or purchase orders. The database will still be there after my programs and I are gone. I use OOP to construct good gateways into my tables.

[dunmalg]

The struggle is real. Letting go of my local data structures was a surprisingly difficult psychological hurdle. I sometimes wish there'd been someone there to tell me that the easiest paths are basically "all" and "nothing". Unfortunately I was the only programmer in the entire organization so I was making it up as I went along. I struggled for WEEKS playing pull-change-push games with huge duplicate local data structures before it even occurred to me there was a better way.

[gnaritas]

> I use OOP to construct good gateways into my tables.

That's all an ORM is, a good default gateway to the tables that you don't have to write by hand.

[corpMaverick]

ORMs (i.e. Hibernate) try to hide the database. You change the objects and they are magically saved. See http://stackoverflow.com/questions/20863150/in-the-context-o...

[gnaritas]

I've written ORM's, I don't need them explained to me, and no all ORM's don't try and hide the database, that's just one pattern. Others like ActiveRecord simply try and make life simpler without complete hiding behind so much magic you can't figure out what's going on. ORM's abstract the database, that's not the same as hiding the database.

[yawaramin]

Except for the times when they load all columns into memory and then you programmatically discard almost everything because you really just needed Person#name.

[remotehack]

An ORM is a gateway...not a good gateway.

[gnaritas]

Most disagree, hence the popularity of ORM's.