One of the most popular Micro ORMs for C# is Dapper which is used by Stack Overflow.
There is no real abstraction. You write standard SQL and it maps your recordset to an object. You know exactly what code is running.
There are extensions that will take a POCO object and create an insert statement and I believe updates, but where ORMs usually get obtuse and do magic are Selects. It’s hard to generate a suboptimal Insert or Update.
Ruby on Rails’ ActiveRecord, for all its heft, is excellent at this. You can use raw SQL any time you like. It was an explicit design goal from day 1.
There are times I dislike things about it, and it can be quite heavy, but it’s very easy to mix and match ActiveRecord ORM code and raw SQL even within a single model class.
That’s how I’ve done it on my last two projects. We used TypeORM for the standard repeated simple queries, and then wrote custom SQL for our complicated queries that the ORM failed at and then just executed them with the ORM. It was really nice and made for easier table refactors because we didn’t have to go through and audit every query that was calling that table.
TypeORM is a step in the right direction for JS ORMs bit it's like 1/8th of the way there IMHO. A nearly fully typed ORM is possible now with Typescript and of course proxy's are out now.. TypeORM was doing too much ADHOC string building under the covers as well. I believe a SQL AST is the way to go. It can be transformed and compiled to database specific SQL allowing for things like predicate push down, optimization, and a sane way to implement db specific optimizations and extensions.
I really like your point about type-safety! I think one major issue with the current ORMs in the Node.js/TypeScript ecosystem (Sequelize, TypeORM, ...) is that they're not fully type-safe. As you mention, TypeORM is definitely a step into the right direction here, but the way how it leverages the TypeScript type system isn't where it could be. I work at Prisma where we're building [Photon.js][1], an auto-generated database client with a fully type-safe data access API.
The informal definition I have of a micro ORM is an ORM without an identity map and without lazy fetching through proxy properties. Are there any more concrete definitions?
So then you’re mapping to whatever data structure your app uses instead of objects. In OOP languages like Python, everything is some type of object anyway.
That’s just a semantic game. If your language returns the result of a query as a generic array of generic dictionaries (or whatever), that isn’t mapping, nor is it object oriented in principle.
But then your generic array of generic dictionaries needs to be mapped to whatever data structures make sense for your application. ORMs save you that step.
I've written many apps and I've never experienced what you've described. I write my queries to return exactly the right data in exactly the right format in exactly the right order—so I can go straight from the generic data structures to the screen interface or document layout.
Nothing says the structure which make sense for an application can't be a generic array of generic dictionaries.
If your favourite programming language forces you to go through the silly hoops of data mapping in order to do useful things with query output, I can understand why an ORM might make sense for you.
A lot of people conflate ORM's leaking because of poor designed library with ORM being a bad abstraction in general.