What ORMs have taught me: just learn SQL (2014)

If you put unnecessary abstraction in front of perfectly good solution the you will end up with problems.

Object oriented programing is about clearly expressing business logic. There is no complex business logic in dumping big tables of data.

So, it's not about ORMs, it is object oriented programing that is poor fit for doing reporting.

Relational databases, declarative SQL, functional pure programming are good solutions for reporting.

And you should certainly learn SQL if you want to do any applications with ORM or without. I recommend Joe Celko's SQL for Smarties: Advanced SQL Programming.

Object oriented languages shine when there is a need to create a precise language that facilitates fast and robust communication between domain experts and developers. In situations where you have little data, but a lot of intricate logic. This is where relational databases are simply no good.

Relational algebra and SQL is not a very expressive natural language. SQL limits your vocabulary to 4-5 verbs unless you start writing procedural code in procedures etc. but SQL is not a good procedural language. Relational databases are a solution to specific technical problems of scale, execution speed, atomicity, consistency, isolation, and durability (ACID). They excel at that, not at communicating intention.

You should use ORMs (preferably Data Mapper) if your goal is to solve problem of expressing complex domain specific logic. You use relational databases in that situation because they just work. Data Mapper allows you to isolate your domain model from tricky technical aspects of data storage like indexing and/or not corrupting files during power outage. ORM works very well as long as you will actually be able to ignore technical aspects of speed etc. in your domain model.

You can do the data mapping, querying, migrations, and all this technical cruft manually with a handwritten SQL if you want, but SQL certainly will not address very well the goal of creating an expressive domain model that facilitates robust communication between developers and business experts.

So, given that we addressed the elephant in the room, some other points:

Dual schema dangers: "I much prefer to keep the data definition in the database and read it into the application."

That's perfectly good solution, if you have a lot of data and amount of logic related to data is minimal. If you have little data and a lot of logic you will not be able to store data definition exclusively in database whether you are using ORM or not. Even if you just store SQL queries in your code, then you do store schema structure with SQL, just not explicitly, but implicitly.

Data migrations, rolling release etc. are tricky whatever you do. I use my ORM to dump me SQL that is needed to move structure from point A to point B, since ORM knows the schema it can do that, and then I manually adjust it as needed to massage data etc.

Identities: Not sure if I follow here. It seems like you have issues with auto incremented ids. Auto incremented ids with ORMs are annoying, indeed. My advice is to use UUID generated in the code, then you will have no need to hit database. Also a side note here: if you use ORMs, do not use anything that has any real world meaning for ids. Use UUID, so that you are sure that no domain expert will want to mess with that.

Transactions: The same situation as with speed and migrations. Getting technical details of transactions is hard whether you use ORM or not. You can use stored procedures, but I'm curious what will you do e.g. when you will need external REST API to get in sync or to copy user files to assure full transaction from the user perspective. There is no magic bullet, it's just hard.

Transactional semantics are problematic with a lot of nosql databases but I've used a few and you can work around this if you have some kind of consistency checks using content hashes. Postgres is pretty nice these days for a wide variety of use cases; including nosql ones. And it does transactions pretty nicely.

Regarding the object relational impedance mismatch, check here: https://en.wikipedia.org/wiki/Object-relational_impedance_mi...

In short, there are lots of things you'd do different in an OO domain model vs. properly normalized tables. A lot of what ORMs do is about taking object relations and mapping those to some kind of table structure. You either end up making compromises on your OO design to reduce the number of tables or on the database side to end up with way too many tables and joins (basically most uses of ORM I've encountered in the wild).

For reporting, you can of course choose to go for a hybrid document/column based approach. I've done that. In a pinch you can even extract some data from the json in an sql query using whatever built in functions the database provides. Kind of tedious and ugly but I've done it.

Or you can use something that actually was built to do reporting properly. I do a lot of stuff in Elasticsearch with aggregations and it kind of blows most sql databases out of the water for this kind of stuff if you know what you are doing. In a pinch, I can do some sql queries and I've also used things like amazon athena (against json or csv in s3 buckets) as well. Awesome stuff but limited. Either way, if that's a requirement, I'd optimize the database schema for it.

But for the kind of stuff people end up doing where they have an employee and customer class that are both persons that have addresses and a lot of stuff that is basically only ever going to be fetched by person id and never queried on, I'll take a document approach every time vs. doing joins between a dozen tables. I also like to denormalize things into documents. Having a category table and then linking categories by id is a common pattern in relational databases. Or you can just decide that the category id is a string that contains some kind of urn or string representation of the category and put those directly in in a column or in the json. You lose the referential integrity check on the foreign key of course; but then you should not rely on your database to do input validation so that check would be kind of redundant.

Same as a rest api

The things I've found positive about ORMs are exactly that mapping of results to business objects. The things I've found "not worth it" are the query-building APIs baked into the objects. These principles can be seen in a lightweight ORM I made, PureORM [1].

[1] https://github.com/craigmichaelmartin/pure-orm

Yeah, ORMs have grown to mean more than mapping relational data to objects. An example of "just" this mapping can be seen in PureORM[0].

[0]: https://github.com/craigmichaelmartin/pure-orm