> Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating query composition, providing abstraction for database-specific and driver-specific quirks
None of that requires an ORM. A simple query builder will suffice and it will be much easier to debug and much less error prone than an ORM.
> providing patterns to map object graphs to relational graphs, and marshaling rows between your object model and database rows - that last one is something your application needs to do whether or not you write raw SQL
So this is the real ORM juice. And ya, without an ORM you have to do this by hand.
So here’s the question: How well can an ORM map to your data model out of the box? In a lot of cases this is where the mess comes from. You need to set up basically a low level AI that can figure out the “right” thing to do as call sites all over your codebase are making arbitrary queries on a huge API surface.
And so the argument against ORMs is that it will take less time and be less error-prone to write bespoke loaders that take rows and build your data model than it will be to configure that AI such that it can do the “right” thing with arbitrary queries.
(If you don’t like the word “AI” here, use “expert system” instead.)
What's the difference? To me, an ORM is largely a query builder.
Query Builder: You build a query, just not in SQL. So you can get around SQL's limitations (like composition)
Try this:
> ORM: You ask for something and because you've researched and found a well-written, quality ORM you trust that it will create a sane query.
or possibly:
> ORM: You ask for something and you accept the tradeoffs vs hand-written queries but you're content that it's the correct tradeoff for your use-case.
There's plenty of places for bottlenecks to hide and premature optimization is the root of at least some evils.
Ha. More like:
ORM: You've just joined a project already using an ORM selected by an 'architect' that no longer works here. Everything is fine until you start testing your system with a database sufficiently populated with real-world data. You and the DBA spend the next next 6 months trying to get the damn ORM to generate performant SQL (you even call Oracle support, which promptly dispatches an "engineer" who tries to sell you on another $500k of crap you don't need that won't really address the problem). You eventually just start writing queries by hand wherever you find a bottleneck caused by the naive ORM, which you could have done at the outset, but no one who actually knew SQL well enough was on the team back then.
Ironically the last time I had a big ORM performance problem was Hibernate eager loading all joins. Diagnosing and fixing it didn't take more than an hour. (Though we did have a very experienced DBA at the time.) YMMV
... that you understand and can be sure are sensible.
> while an ORM builds queries for you
... that you have to hope are sensible.
That's one of the biggest flaws of the ORM for me - you have limited visibility of what it's doing to your DB.
And then what do you do if the ORM is generating junk? If the answer is "use a querybuilder/handcrafted SQL for that one", what's the point of the ORM in the first place?
The point may be that 98% of the queries are just fine, and you've saved time vs writing by hand, and it may be easier to read/understand for the next people to have to touch the code.
Most software work is maintenance work. Optimizing for initial deployment is shortsighted.
I've also worked at a companies where all of the logic was in ungodly stored procedures and getting anything released took months and we had a whole two weeks "hardening sprint" because we had to coordinate with the "database developers" and of course the entirety of the business logic was In stored procedures.
Most software being maintenance work is even more of a reason to optimize deployment. What good is unreleased software? The most important part of the business is releasing software. Most of my emphasis asan Architect is making sure we can release fast.
* The 'developers' weren't allowed to write the sprocs. We were at the biz meetings, but the DB guys were hardly ever there - their meetings were separate for some reason, but because devs were at the meetings, the devs were the ones who also were the face of the project. When the project was behind, we caught it in the neck, even if we were bottlenecked waiting for the DB team to write their logic and expose it to us.
* The DB team was always fewer people, juggling more projects, and other things like system uptime, maintenance, backups, etc.
* The sprocs were never part of version control or part of any source code that we could ever see as part of normal development. They typically weren't subject to any unit testing process.
The answer to all of this is mostly human management, structuring resources differently, coming up with different processes, etc. But any of those things would have changed the power dynamic, which seemed to be the purposes in those environments. Hey, I can write a stored procedure too - let me write them, and if a DB wants to 'review' them - or really, anyone on the team - please review and let's hammer it out and make it better. But roadblocking projects until the DB guys can 'get around' to writing our mission critical procs is just silly.
One other 'weird' division I saw a few places was this "developers can never have access to production systems - that violates XYZ" (a regulation, or some 'law' that was never produced, etc). I asked what the core issue was, and it was "you can't just have developers going on to production and just making changes on live systems - that's ... (against our policy, etc)". This was particularly challenging in a situation where a critical bug only happened on one production system, and we weren't allowed to replicate the database to another system, nor was anyone with any knowledge of the deployed code allowed to get on to the production system to even see if what was deployed was what we'd developed. But... this was still "our problem". Yet... the DBA in this case was "allowed" to get on the system and hand-write new triggers and sprocs to 'fix' our problem, all without documenting/testing his code, nor committing the code to any repo for us to even have visibility in to the data manipulation he was doing to 'fix' the problem we supposedly cause but couldn't investigate.
Again, I know this isn't a problem specifically with stored procedures. When sprocs have been promoted as the primary interface, however, it's usually been a political/power grab more than a technical benefit. And yes, again, I know there are technical advantages in some cases, but usually not enough to outweigh the drawbacks I've encountered.
Yes I optimize when my automated performance testing/stress testing, tells me I need to and may write handcrafted sql, but I've also handcrafted some classes in C back in the day when my old Windows Mobile app using the C# compact framework wasn't performing.
From what I've seen (couple of home-grown ones, Class::DBI, ActiveRecord), "more often than you want".
(I'm willing to admit they may not be class-beating examples. :)
Of course most ORMs do fall short because most languages don't have the powerful concept of "code as data".
SQL syntax is extremely verbose, and compounds the more tables are involved in a query. You're not counting the time the ORM has saved you before having to resort to logging SQL.
But of course, as the queries get more and more complex, the flexibility of the ORM syntax approaches the flexibility SQL. In the end, there are many situations one would rather just use SQL.
For example, in Go, I use Gorp, which has a Select() function where you pass in the SELECT query string (plus bound values) and the target type, and it loads every result row into an object of that type. So you can have an arbitrarily complex SQL query as long as it starts with `SELECT one_table.* FROM`. That's a marvelous design.
And when you have to do a query that returns results from multiple tables? Guess what, you just use the normal SQL module from the standard library.
postgres=# SELECT * FROM domains WHERE id = 'foo';
ERROR: invalid input syntax for integer: "foo"
LINE 1: SELECT * FROM domains WHERE id = 'foo';
^Your scenario happens with people that either don't know or don't care. They will write crappy queries with any tool.
I built a moderately complex application in Django at a previous workplace, using the ORM for most things, until the queries were too complex for the ORM.
Another guy connected to the same database and built some graphs using PHP and SQL. Guess who had to help him write the SQL when the queries got too complex for him? The ORM user.
https://github.com/bgard6977/sqorm
Flyway & jOOq take a similar approach:
Taking the SQL-first approach also allows you to serialize without circular reference problems, since you don't just load the data, you also define a path to decompose the graph into a DAG.
1. provide idiomatic domain-object oriented query interface which it in turn translates to SQL
2. provide CRUD sql generation
3. provide some sort of session-based object lifecycle change tracking and management
#1 - The generated SQL is important on many levels. As things like HQL/linq/<your QL here> deviates further from the generated sql transparency is lost. SQL is normally brittle compared to your domain language which has better testability and type safety but still you have a handful of queries where it feels more sensible to write the SQL yourself.
#2 - Code which reflects on a type and generates basic insert/select/update/delete is usually pretty naive and easily done. With the exception of complicated legacy databases and iBatis-style tools ORMs which only support #2 arent really worth bothering.
#3 - I've found this to be the real benefit of an ORM. Being able to scope object lifecycles into clear units of work, buffer pending changes until a discrete point and get scoped caches for "free" have been hugely beneficial.
Each ORM unfortunately/inevitably come with great learning curve. It seems to unfortunately/inevitably bring lots of new concepts and conventions to the table required for the user simply must understand. Sometimes it requires you to re-arrange the way you may write your code to be more session-oriented.
I dont like the idea of AI managing how/when to apply changes to storage media. AI can be smart and efficient yes but you lose that important transparency/predictability. On the contrary I prefer very dumb/mechanical/predictable ORM, something not elegant but the behavior of what happens when is well-understood and easily scaled out to a large team. In my experience hibernate, EF, sqlalchemy have that sort of dumb/predictable behavior (however the SQL they sometimes generate can be performant but unreadable).
On the other hand refactoring your data logic is just business as usual, and you will probably do it in both cases anyway.
What AI do you need? You map your tables to objects and relationships between objects via FK relationships.
Most ORMs will download all the data and evaluate them on the client side, with code that is even more convoluted that the SQL one and thus with less performance.
Entity Framework and any other Linq to data provider translates the Linq expression to the native language of the source data and does it server side.
LINQ only allows for a fraction of what is possible with SQL, and good luck having the best queries generated out of it, if the RDMS doesn't happen to be SQL Server.
It all depends on the talent of the authors of the drivers.
I've seen that pattern in lots of homegrown applications, but I've never seen such a thing in a mainstream ORM. Care to provide examples ?
Nowadays I only bother with Dapper, MyBatis, using a mix of SQL and stored procedures.
EF only if the RDMS happens to be SQL Server.
That is only true in a one-to-many entity relationship (and even so, it is debatable). A one-to-one relationship can be modeled in the two objects, in one of them, or delegated to a third entity. A many-to-many entity relationship can also be handled, in OO, in various different ways. Idem for a ternary relationship or, basically any higher order relation between objects.
This is known, borrowing a term from electrical engineering, as an impedance mismatch between the two models, and it's not an easy problem by any measure.
Here is why.
Bitcoin cryptocurrency AI biomedical supply chain networking quantum systems-thinker.
I recommend anyone that has to deal with ORMs create their own at some point, in a non critical project. Not because they will necessarily create the next big thing (but who knows?), but because nothing quite gives you the perspective and appreciation for what these systems can do and why they have their pain points like making one yourself. You'll most likely not use your own module long after you've created it and then again surveyed what's already available, but it's invaluable in making a good assessment of those options as well.
Similarly, making a web framework yields similar benefits.
In both cases, a good understanding of the underlying technologies they build on (SQL and HTTP), is required, and if you don't have it doing in you'll have it coming out the other side (which is really the reason for this in the end).
Similar things exist all along the spectrum. From embedded OS's and compilers to javascript utility libraries.
What it comes down to is that a tool is best utilized when the person knows when and how to apply it appropriately, and that's as often as not an understanding of the tool as it is of the context.
A person intimately familiar with hammers and their uses can build some interesting wood furniture, but they'll likely never achieve the same level of product as a master woodworker that's just well acquainted with a hammer. Investing time and effort into tools provides only so much benefit. At some point, more knowledge of the craft itself is far more beneficial.
Joking aside, SwlAlchemy is very impressive software, and zzzeek deserves all this praise and more. Every time that there's one of these anti-ORM articles I feel like SqlAlchemy pre-emtively addressed all the substantive criticisms in its flexible, well layered, design.
(And in the interests of fairness, Django's ORM is also very good at making simple things simple).
I am a huge fan of the custom column types -- they have allowed me to have code that works against SQLite as well as a production databases with ease!
I wish more people aspired to be like him, because you know there's no more authoritative answer when you run into a Stack Exchange post and he's offering up his assistance.
1. As a reaction to common SQL injection from poor libraries not implementing parameterized queries. (2004 or so)
2. Novice engineers not wanting to learn SQL (look I learned how to make a blog in RoR, and I like mongo!)
3. As a theoretical abstraction above the data-store (as though you might someday be able to switch the data-store beneath the ORM)
1 has been solved, 2 was never okay (point of the article), and 3 isn't really okay either because it's too leaky (performance specifics).
5. Type checking all your queries.
6. In code query composability.
But I totally agree that ORMs are too leaky of an abstraction for 2 to be really useful, and that 3 is much harder than it appears to be.
But I'm really surprised every time people tell me they look at the schema as defined into the ORM instead of at the table in the database.
I'm really jaw dropped the few times I know somebody doesn't even know SQL, only the ORM. Maybe they look at it as if it were the reaction of somebody that thinks you must know assembly if you want to program Ruby, Python or Node (I don't.) Still, if you work with a database you must know it's internal language, SQL or NoSQL. Your going to need it or build a mess.
And involving a DBA early in the project can make your database at least twice as fast, with the right schema and the right queries. Then you translate that into the ORM you want to use.
In my experience, nearly no one knows SQL, and the attitude seems to be that learning it at all is a waste of mental bandwidth.
On the other hand, I wonder if knowing none at all is better than knowing a little.
Even 1 year "learn programming fast" courses here in Uruguay have at least the basics. OTOH, Javascript is not taught in many courses so YMMV...
Very happy to not have and ORM on our stack for many years.
I believe that the interaction with the database is just about the most important part of code that you need to rely on. We have had messy data written to the database due to misused or misconfigured ORM (perfomance issues, bad orphan handling, session state problems, overflown sequences, to list a few) and decided that we should not rely on all devs touching that code to be experts in the ORM api to avoid the pitfalls, we rather our devs be expert in SQL.
Never had any of them complain about mapping using something like jOOQ. Type checking and composability are also built in.
Just because we are using SQL doesn't mean we have to do the mapping fully manual.
Plus it plays very nicely with kotlin :)
For short-lived processes, like typical web applications and command line utilities – that load data from the database, do something with it, and then purge it from memory again – I'm becoming less and less convinced that ORMs are actually a benefit.
On the other hand, if your application plans to map the database data to memory for long periods of time, with the need to keep them in sync, then you're probably going to end up writing something that resembles an ORM anyway, and poorly at that. In this case a good ORM is beneficial.
I used to work in an environment where we had relations as full first class data structures. They are very pleasant to work with.
We actually had 'relation-object-mappers', ie when we had to interact with other systems that didn't use relations, we often mapped them to relations internally to make them play nicer.
It wasn't about speed of execution, but expressiveness when coding. Later on they even added proper type system support.
Common operations were things like map/project, extend, filter, join, collect-by-key / expand, etc.
Just as Codd pointed out in his original papers, relations allow you to not have to make a choice about a hierarchy for your data.
Using key-value store like a hash-table in your program, or the much vaunted has-a relationship between objects would force you to make these choices. Thus making interacting with the data awkward for all but one access pattern.
Relations work best when your program is written in a style that deals largely in immutable data. (What we call 'purely functional', but people in dysfunctional languages have also picked up on the advantages recently.)
Or, alternatively, make it really painful when you do actually need to query hierarchies, along the lines of "give me all the tuples above this one in the hierarchy".
Datalog is a specifically chosen subset of Prolog.
- replaced the DB driver mid way through a project to a slower, but more complete implementation. this went flawlessly, because it’s all so generic and in the end the same DB under the hood, so a simple change (unless you don’t use an ORM where i could see it being a nightmare)
- started a new project where i had to use MSSQL, which I’d never used before, but i’m a big fan of postgres/sqlalchemy. other than ODBC oddities, it was really simple to write the new app with all the same patterns i was used to with things like update on write, lazy joins, constraint deferral, and i think most importantly would be MIGRATIONS! huge help to have the same migration framework that i was used to
I worked on a project with a home-grown ORM (in C; it was horrible) that abstracted over both MySQL and Postgres ... except the overarching application required Postgres-specific column types and functions.
EDIT: Sorry, my mistake. The partent is talking about the parent comment, not the FTA
No need to rewrite all your SQL, just the ORM description of the model!
That is quite theoretical. My PRs for fixing non-spec compliant behavior in pgjdbc get rejected because they might break some ORMs (mostly Play). My PRs for adding MariaDB sequence support to Hibernate get rejected because there are additional MariaDB features that Hibernate doesn't support as well.
as an example: https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalch...
heaps of this kind of thing are nicely dotted around the code so you don’t have to deal with weird driver quirks, maps “Text” column type to whatever it needs to be in your given database to have an unbounded text blob
id say that’s far more than theoretical
EDIT: typo
EDIT 2: also, on DB specific features, SQLA supports a bunch (not all). eg: https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalch...
The presence of an ORM relying on these bugs should not prevent a bug in the driver from being fixed.
I did the opposite and faced lots of difficulties. Then I concentrated on plain SQL and suddenly I felt like I am understanding ORM much better.
I've found that the people who know SQL pretty well can actually do good work with or without an ORM. But the maintenance is a lot easier when the abstractions are consistent with the abstractions that are used by a popular ORM.
This gives you a lot of flexibility and power, but as zzzeek mentioned, you need to know SQL to understand how what the ORM is inefficient and to be able to replace it as necessary while still letting the ORM do what it is really good at.
Analogously, I'd say that being able to write a compiler, down to having it generate machine code or assembly, gives you a leg up when using a compiled language. I've met and interviewed a number of coders who had cringeworthy gaps in their knowledge, because to them, a C compiler was just some kind of "magic."
If this person spent all that time using Hibernate and then SQLAlchemy, and all that time did not know SQL, then their suffering and bad experiences make complete sense. You absolutely need to know SQL if you're going to use an ORM effectively. Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating query composition, providing abstraction for database-specific and driver-specific quirks, providing patterns to map object graphs to relational graphs, and marshaling rows between your object model and database rows - that last one is something your application needs to do whether or not you write raw SQL, so you'll end up inventing that part yourself without an ORM (I recommend doing so, on a less critical project, to learn the kinds of issues that present themselves). None of those things should be about "hiding SQL", and you need to learn SQL first before you work with an ORM.