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.
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.