We used to show the new developers on the team a query generated by Entity Framework...23 pages long. We replaced it with a well formatted query that fit on half a page. When I switched the team to Java + JPA, we started writing our own queries if wasn't a simple CRUD and it has been wonderful for the entire team.
I think it's easier to learn SQL than learn an abstraction of a DSL.
And SQL has been relevant for about half a century, odds are good you'll continue to find use for that knowledge for the remainder of your career.. Well worth the time investment.
I’ve come to the same conclusion even working with ActiveRecord. SQL is very literally a domain-specific language for working with relational data; why would we go so far out of our way to eschew writing code in it?
I'm a SQL fan as well, having used it before there were ORMs. I still prefer it for complex queries. However, ActiveRecord is so nice for reducing start-up tedium with DDL and migrations, and especially for mapping relations to data structures (objects in this case).
Because the process of building that SQL, executing it, and bringing the data it returns into the object oriented universe is a tedious and fiddly pain in the arse.
1. “Building SQL”? It’s not clear to me what that means in a world where you are writing raw SQL rather than using ORM’s and query builders.
2. “Executing it”—this is called a SQL client library. If you install your SQL statements as stored procedures you can execute those procedures by name, for example. Not that hard.
3. “Bringing the data it returns into the object oriented universe”: I am typically satisfied with an array of hash tables. Funny story: what do you get when you use ActiveRecord’s find_by_sql with a query that returns columns not in the original table? You get a collection of objects each with the extra column monkey-patched to the individual object. In other words, a slower and stupider hash table that happens to have dot syntax rather than bracket syntax and a bunch of do-they-even-still-work-in-this-context instance methods attached.
> SQL is very literally a domain-specific language for working with relational data; why would we go so far out of our way to eschew writing code in it?
It is, and all ORMs I know use it under-the-hood. It is the boilerplate code by which SQL is issued to the DB, and results parsed into something usable by the rest of the program that people try to avoid writing over and over.
SQL just isn't composable. I know this article is old, but these days it's not black and white. In the space between ORM and raw SQL there are things like AREL which can save a ton of dev effort without the "impedance mismatch".
Composing in this context means that one part of your application (eg the list controller) builds part of a query (the select from) and another part (eg the filter controller) builds another part of the query (the where part) and yet another part of you application (the paginator) alters there where part, adds limits and offset and build another query based on the same filter conditions to calculate the total row count. All that without the different controllers knowing each others in advance. This is not possible without complicated string manipulation or building some not-sql-query-algebra.
Some api like prepared statements (supported by the db itself) for building up complex queries step by step would be nice. Does something like this exist?
As the other answer to you says: subqueries will do that. DBs automagically unnesting subqueries has been a thing for a long time, so usually you won't get any performance impact (With a big warning for correlated subqueries).
Neat, not having used rails ever, I didn't know about Arel. Speaking about composable sql, I've had some good success with dbt lately. It has a feature where it lets you 'compile' a query out of multiple select models. Now I wonder whether it'd be feasible to use that dynamically, within the context of an application...
Even if you use an ORM you should probably know SQL so you can use them efficiently. Also, if you use an ORM, make sure it "starts with database schema" not with objects. Otherwise the mismatch will probably be horrible for you. A dozen years ago I published this article on generating your domain objects from the database, still a reasonable thing to do if you want the added query expressiveness the ORMs have over most SQL generators.
A feature of Netbeans I always liked was the ability to create a Hibernate Java Object directly from a database table. It does make sense to me to start at the DB first because the application is just a broker between the front end and the DB so it's more flexible to make the adaptations there.
When I started web work ~9 years ago, it was with Rails and ActiveRecord, which turns out to be incredibly good for basic queries and pretty basic apps. So good to the point where I never bothered to go too far into SQL until years later, which was a mistake.
When doing work in python, I don't feel it has a comparable ORM, to where I kind of write my own files that have things like finders and updaters and creators. In most cases, I've found it's much better than SQLAlchemy. Dealing with joins, and things like math, meaning averages, sums, distributions, division, is so much better to be done in the query rather than more basic queries and looping through the results.
There are of course cases like injections to look at, but lots of things I have are calculations of data and showing it, so we're able to handle it with raw queries. Also, ActiveRecord has ways to enforce no injections.
In lots of cases, we've found that using an ORM to start, finding slowness, and moving towards raw queries is a great way to go.
Current rails developer here, I've found that using ActiveRecord for basic queries and moving to SQL for more complex stuff to be a very good combination.
I agree that ORM users should learn SQL, but SQL and an ORM are not mutually exclusive.
Yep, also current rails dev here and this is also what I do :)
Learning SQL with an ORM does remind me a bit of learning memory management with a garbage collector: helps you make better choices as to how you put things together, even if you never directly use the know-how.
current rails dev here, i've found ripping out the custom SQL and replacing it with ORM equivalents offers the best performance and readability short of custom Postgres functions. The state of ORM in rails of 2014 vs 2019 is a big leap.
We use ActiveRecord, which, from my perspective is a good ORM. It's very simple. If you are diligent you can use pluck and count and a ton of other features to ensure you are selecting only what you need. It's easy to drop down to sql in partial form or in the full form. We tend to not get too crazy with AREL.
In particular I like that it behaves way better using surrogate primary keys (serial id column) instead of natural primary keys... it just answers that question for you that would otherwise result in bike shed conversations.
We minimize logic in the AR models, instead those are in glue objects and this pattern works real well. AR models are mostly there for describing relationships in the code and doing data validations in the code on top of CRUD operations.
I use a data mapping ORM and I've literally not have any of these concerns. They are all non-issues.
The first item about querying is relevant but all ORMs allow you to drop into SQL to execute a complex reporting-style query. It's not really necessary if you are just querying in objects to manipulate (which is what ORMs are good for).
This is the 'data mapper' vs 'active record' debate and I would argue both are sub-categories of 'Object-relational mapper.' I've seen multiple people in this thread even include query builders in their definition of ORM. I'm starting to wonder if much of the disagreement in this thread is caused by everyone having their own definition of ORM.
Code thinks in objects and functions and values and pointers.
Databases think in tables and columns and rows and queries and indexes.
If you don't pick an ORM to help manage this translation layer, then you'll end up re-implementing your own. Maybe this is OK, because yours will be simpler for quite some time.
What else are you going to do? Stored procedures? Concatenated strings?
I always feel that the arguments against ORMs is like the one against frameworks or using “Vanilla JS”. Saying you don’t use one really means you’re just writing your own.
No, you don't end up re-implementing your own, you end up implementing 10% of one. The 10% you need. Maybe it maps rows to objects, but doesn't handle object identity right, because that's not what you need.
The ideal for me is something that manages connections, lets me write the SQL, and gives me easy access to ResultSets, perhaps in the form of an object.
And yes, stored procedures are extremely useful, especially for minimizing chatter between the server and the database. Use them appropriately. (Although I don't see how they are an alternative to ORMs.)
Experience tells me you really need two layers. One for translating databases to objects, second one to translate those objects into proper domain objects.
The way code thinks about objects, behaviors and relationships is unlike the way you need to have it stored. Trying to use ORM-generated objects directly in a more complex business logic is, I learned, a recipe for disaster.
And since you're already writing two layers - data translation layer and actual domain layer - then, one may wonder, why not skip the first layer and implement the domain layer in terms of SQL queries?
Listing prior discussions is not a reprimand suggesting we can't discuss it again. It's a service to the community to let us see what's already been said, if we so desire.
No sides as afar as I am concerned. I just pick the best tool for the job. I love ORMs. I love plain SQL, be that via an ORM "jail break" or a lower layer.
> I do know one thing: I won’t fall into the “ORMs make it easy” trap. They are an acceptable way to represent a data definition, but a poor way to write queries and a bad way to store object state. If you’re using an RDBMS, bite the bullet and learn SQL.
I don't think i'm alone in saying I'd rather hire a developer who is native with an ORM and can dive into SQL when things get thorny, than hire one who's going to fight me on the very merits of an ORM at all.
I mean, hey. To each his own. But lets be clear: this is ridiculous, and if you think it's a position you want to take up, make sure you can get hired holding onto it.
ORMs lure you in with a false sense of neat abstraction. They have nice intuitive examples on their home pages. But then you use them in the real world, doing gnarly queries, and you realize that doing anything powerful and fast in the ORM requires its own completely separate abstractions, which are often difficult for the uninitiated to follow. It's also often a big pain to debug the raw SQL that gets compiled after the ORM does its magic.
The argument I've made before when going down the path of ORMs has been: do we forsee needing to use this model code on a different database engine? Outside of simple toy applications, or needing to support different engines with the same code, I agree that ORMs are more trouble than they're worth.
Whenever I get to the point where I'd do something gnarly with a query, I just fall back to SQL statements with results that the ORM can parse; or just dump directly into data structures and go from there, even if the originating tables still have ORMs.
If your framework/library/language doesn't let you skip the ORM - or populate the object with the results of a custom query - well, I'd say that's a failing of that ORM, not of ORMs in general.
You don't use ORMs for gnarly queries -- that's not what they are for! They are for making manipulating the entities easier -- reading the data out of the database in a way that makes easy to modify.
You can (and should) use them for simple queries. You have a list of entities you want to query and filter, that's going to be fine. Joins are fine. But if you're doing some complex analysis, an ORM is the wrong tool. That doesn't mean it's a poor abstraction, or difficult to follow, or something to be avoided. It's not the right tool for that job. For the job it's designed for, it's going to save a lot of effort.
SQL is great for analysis -- it's pretty much what it's designed for. But for bringing data into your app and modifying it, SQL is cumbersome and verbose. If you're loading data into objects then you're just creating your own personal ORM anyway.
Just because a job is simple doesn't mean it doesn't take time & effort. A tool can be helpful if it reduces the time or effort to accomplish something.
That's not true. A simple job might be 3 lines of code in an ORM to update a record. In SQL that will be a lot more code especially if that's wiring up a foreign relationships. With SQL you will also have a lot of uncheckable strings containing code.
Simple tasks are done maybe thousands of times in any one application. It's the complex tasks are rare.
>A simple job might be 3 lines of code in an ORM to update a record. In SQL that will be a lot more code especially if that's wiring up a foreign relationships.
That's not a fair comparison unless you include the time and effort involved in creating your ORM models, before you can even write those 3 lines of ORM code. The effort to construct those models isn't even fully amortized over your project, since it must be maintained through schema migrations.
ORMs are like putting on an exoskeleton to go buy groceries because it will let you carry your bags more easily. In my opinion, the complexity and indirection they introduce for accomplishing simple tasks do not justify their existence in the vast majority of cases.
You would need to show me your DDL statements because that's the equivalent. I can generate the model from the database or the database from the model.
It's not much more effort to type "create table employees" with all the fixings as it is to type "class employees" with all the fixings.
>I can generate the model from the database or the database from the model.
Depends on the ORM. Just like "raw sql mode", not every ORM supports that. It's not an inherent feature of being an object-relational mapper, it's part of the bells and whistles of some ORM packages. And I'm going to guess you're coming from the Python/scripting universe, because generating models in other languages is definitely more complicated.
But let's pretend that all ORM software can generate model definitions from the database. Just do the update then. I want to validate your argument that a simple update in SQL is "a lot more code especially if wiring up foreign relationships." If you think an ORM is much less code, or simpler, I'd like to see how.
I think I can give a better example that isn't purely about performing updates, but is still about "simple things" and not particularly complex flows.
In my API, I have models from my ORM. These can then be used for database migrations, which allows changes in my database structure to be checked in to version control.
From there, these models are obviously used for queries within my app. Generally, you are correct that generating a simple query with the ORM is not meaningfully easier than writing the SQL myself, with the one exception being that the ORM sanitizes inputs to my queries without me having to think about it at all, which is nice.
From there though, I can use the models from my ORM to automatically validate incoming requests to the API endpoints, as well as automatically serialize the results of my queries when I want to send a response back to the client. If you're not building a REST API, obviously this might not be particularly useful, but for someone that is, it has saved me a lot of work.
Finally, generally I find code in my code is a bit easier to debug than strings.
Your example makes for good SQL but a simple ORM example:
employee = new Employee() { Name = "Bob" }
employee.Manager = new Employee() { Name = "Jill" }
context.Employees.Add(employee)
context.SaveChanges()
This would insert a new employee record for Jill, grab the primary key, then insert the record for Bob with his ManagerId field to Jill's id. If Bob wasn't a new employee, it would perform and update instead. If there were more related elements and different levels of depth the ORM would order the inserts/updates to get the necessary keys and wire everything up. After this block of code, the objects are all updated with their new primary key values. Everything is executed in a single transaction.
The ORM is performing operations on objects -- it's pretty much right there in the name -- it's going to be a poor choice for bulk updates because that's not what it's for. But again, nothing stops you from using the right tool for the right job -- whether that be an ORM or raw SQL.
Yeah, your "uncheckable strings" (which could be checked by spinning up a testing database) is replaced by 700k lines of code that, although tested, is still full of bugs.
The Hibernate repo is 700 thousand lines of java code!
There's a tendency amongst some (especially "enterprise" programmers) to forget that dependencies are also just code. If they break, you are on the hook too. You own that complexity.
I don't think that is what parent wanted to say. It's still a lot of boilerplate code and config that can be avoided by using an ORM for the 'simple' parts of an application. And if you use a framework like sqlalchemy, you still have access to the lower levels if it turns out the ORM abstraction is unsuitable.
Running raw user SQL isn't a prerequisite of an ORM needed to make it an "ORM", it's a useful feature that most ORMs try to include because the authors recognize the many shortcomings. Also, by writing raw engine-specific SQL, you automatically invalidate one of ORMs biggest selling points which is being SQL-database agnostic.
And by "drop into", this typically means writing custom stitching code that stitches the SQL cursor results back into the models again. It's rarely straightforward.
> you automatically invalidate one of ORMs biggest selling points which is being SQL-database agnostic.
The biggest selling point is a massive reduction in boilerplate code. Database agnostism is a feature that almost nobody ever uses, so who cares! Your proposed alternative is engine-specific SQL so you lose either way. At least with an ORM, you'd lose significantly less. You'd just have to deal with the places you used SQL. Which, in my experience, is pretty small and pretty specific.
> this typically means writing custom stitching code that stitches the SQL cursor results back into the models again.
I often feel like people who complain about ORMs have either actually never used one or used a poor one. As long as my query matches the structure of my object(s) I don't need any stitching code. And if they didn't match, I wouldn't write stitching code because that would be waste of time.
If I'm writing a custom query, I'm probably not looking to integrate into the model anyway -- if it's used for reporting I'd just take the results as is. If I'm writing a query specifically to get a matching model object (to manipulate) I'm going to get the whole model and no stitching would be required.
> you automatically invalidate one of ORMs biggest selling points which is being SQL-database agnostic.
I haven't heard anyone talk seriously about database-agnosticism since the very early 2000s. Maybe some commercial products still try (choose MS or Oracle!), but it's rare nowadays.
The primary selling point of an ORM is that it abstracts marshaling/un-marshaling rows to/from entities. Instantiating and persisting entities to relational storage.
> And by "drop into", this typically means writing custom stitching code that stitches the SQL cursor results back into the models again. It's rarely straightforward.
That's not typical in most uses I've seen. Far more typical are things like:
- Go straight to SQL for reporting, since that's what SQL does. Useful in reporting contexts, and also for list/filter UI screens.
- Use raw SQL to query a list of entity IDs for updating based on some complex criteria. Iterate over the identifiers and perform whatever logic you need to before letting the ORM handle all the persistence concerns.
> I haven't heard anyone talk seriously about database-agnosticism since the very early 2000s.
Do you use the same database engine for your unit and integration testing as you do production? I don't. I use sqlite for unit and local integration testing, and aurora-mysql for production.
As a side note, I quite literally can't use aurora-mysql for local unit and integration testing. It doesn't exist outside AWS.
Unit testing code that touches the database is not useful, and in fact it indicates there is likely a design flaw. Code that acquires from or changes data in the DB should be self contained.
An integration test that doesn't use the same DB as production is unsatisfactory.
Using a DB inside your unit tests is an antipattern and arguably a violation of the concept of a unit test in the first place.
Integration tests should run against a test environment, otherwise, what integration are you testing? I don't see the value in writing integration tests that test the integration between my code and a one-off integration test DB that exists solely for the purpose of integration testing.
> Do you use the same database engine for your unit and integration testing as you do production? I don't. I use sqlite for unit and local integration testing, and aurora-mysql for production.
There is no syntactical difference between Aurora/MySQL and regular MySQL until you start interacting with S3.
Also if you’re using only the subset of MySQL that is supported by SQLite, you’re missing out on some real optimizations for bulk data loads like “insert into ignore...” and “insert...on duplicate key update...”. Besides that, the behavior of certain queries/data types/constraints are inconsistent between MySQL and every other database in existence.
Finally, you can’t really do performance testing across databases.
I've always thought the "being SQL database agnostic" theory of ORMs was more about a development team being able to choose from some common choices than about apps being portable in practice.
Yeah, I’ve never seen any complex applications, using ORM’s, that were easy to port to another database. Hell, one company I was at switched from MySQL to MariaDB and even that took some work despite that they should be almost the dame thing. If you switch to a more substantially different database, eg, from MySQL to Postgres, then its even harder. I’m also a believer in using a databases features when it makes sense and not limiting myself to the standardised subset of SQL just in case I might want to change databases later.
The PHP ORM Doctrine supports a custom SQL like language called DQL that integrates your defined model/relationships as well as converting it to the correct SQL dialect for your backing store.
Queries look like:
SELECT u FROM ForumUser u WHERE (u.username = :name OR u.username = :name2) AND u.id = :id
It's certainly limiting, though you can write (or find implementations[0] of) custom types[1] that can be pretty powerful (void where prohibited, limitations apply).
In ActiveRecord, there's a method called find_by_sql. You can't call it directly; it's a class method on an ActiveRecord model. So you have to choose which of your ActiveRecord models should be used to instantiate the rows of your result set. (What if your result set doesn't really match any of your models? Pick one arbitrarily.) Your SQL has some extra columns. What happens to the data in those columns? They get monkey-patched onto the individual objects. (Which is stupidly expensive in Ruby.) Other than that, the individual objects are fine. They even have all your smart instance methods, which may or may not behave properly with all the ad-hoc monkey patching.
If you tried to short-circuit all of that nonsense, you tend to get arrays of hash tables. Which is, in my opinion, already a perfectly adequate interface!
> What if your result set doesn't really match any of your models? Pick one arbitrarily.
I don't want to sound like the ORM defender, but I'm not sure I understand.
This sounds like a deficiency of Ruby and the ActiveRecord record model. In Java, for example, you'd just write a new POJO for your query, which isn't exactly difficult. There are no "smart methods" or whatever.
It is a valid criticism that this can proliferate data classes, but that depends on the application.
I was writing in terms of what I actually understand and have used—which doesn’t include any Java ORM. In fact if there are Java ORMs that consist solely of POJOs which are populated by raw SQL queries, I would gladly use them!
The API docs don’t make it clear whether that’s still possible so I didn’t mention it explicitly, but I have done that before and that’s what I was alluding to with the “array of hash tables” comment.
In my personal experience, ORM’s seem to encourage queries to get scattered throughout your logic (they’re just normal code and function calls, afterall... at least, they look like it) and encourage mixing application-side logic with query logic. The former makes it incredibly hard to remove if you need to reach for raw SQL and the latter leads to bad performance due to many application-database roundtrips snd not filtering enough before sending data to the application.
Yes, both of these things can be solved through disciplined modularisation of ORM logic, but in my personal experience across multiple companies, most developers simply aren’t that disciplined and treat ORM code as any other application code, instead of treating it as the remotely executed database code that it actually is.
In my experience, writing raw SQL (through https://www.hugsql.org/ in my case), you are instead encouraged to think of them as separate and carefully consider the boundaries, which helps keep the queries and application logic modular and allows for more carefully crafted queries that minimise roundtrips and data shuffling.
Again, this has been my experience, across a number of companies. Perhaps your experience differs, in which case, I’m jealous.
Hibernate promises that you use the database "as if" it was not there. You can drop into SQL whenever you need, but you cannot remove all the stuff you had to add just to make it work (saving, reloading, handling caching....). So instead of being "out of the way" it poisons your entire code-base.
Now this is excellent point which is true in my experience. It gives little ramp up in starting and keep creating speed breakers as development proceeds to handle real complex business scenarios.
They make simple things simpler, which is a good thing.
I wouldn't mind writing queries in SQL so I don't have to learn a new ORM for every language I use. However I like that ORMs unmarshal results for me in objects / tuples / maps of the language sparing me the work.
For complicated things I write SQL and possibly decode the results manually.
If you know SQL and know the ORM, you can just do the complicated things in SQL.
If you know a problem is going to be complicated, you know to use SQL.
If you don't know if a problem is going to be complicated, you can default to SQL and decide to use the ORM later if it's a good fit once you've got more problem details.
This is where I disagree. I dislike having a value which is the entity. The basic lesson from relational databases and later data oriented design is that you don't have an entity. All you have are aspects that are related.
This is also tapping into one of the many optimizations you have to know about when using ORMs. Can you "select" 10M rows from a table? Is an object instantiated for each one? If they're lazily created, when are they destroyed, and where is the buffer of rows held, client side or server side? How do you efficiently update each one without incurring a sql statement for each update?
All of these questions require deep knowledge of the inner workings of the ORM, when in SQL, this is a lot more straightforward.
Not really. It isn't a SQL issue; it's a database driver problem.
The Django ORM takes take a whole of an hour to read, and after that you have 90% of the use cases covered. Considering that I've been using that framework for many years now, the overhead of that knowledge is irrelevant.
I tend to use Active record for simple queries. And for anything complex, I write a SQL query to define a view and build a view backed model. That way I can use SQL when I want/need to, but I can keep it contained in one place, and I don't have constantly rewrite tedious queries.
You're going to love this: about the time I left one place I worked one top engineer did a meeting on using Hibernate for... drum roll... analytic queries. My brain fried with questions of why, how is this better, and this is definitely the result of someone that hasn't touched real SQL in a while and just wanted to use the same hammer for everything.
>You can (and should) use them for simple queries.
This is not a very compelling argument to use ORMs. It is saying "it makes easy things easier". This doesn't really buy you much value. The simple things are already simple. Bringing in a very large, complicated external dependency to make simple things simpler, is not a good idea.
>If you're loading data into objects then you're just creating your own personal ORM anyway.
By this definition, the Postgres driver I use for node is an ORM as it takes a row of string/type identification codes and turns them into javascript objects and javascript types. It isn't an ORM though, that is not what an ORM does. An ORM converts one paradigm into a completely different paradigm, which is why it fails and is a terrible idea.
Eventually if you’re working with an object oriented language or if you have to convert the result to JSON, you’re going to have to convert one paradigm to another.
Unless you’re programming in assembly, everything you do is being converted into another paradigm that is what every compiler and interpreter does.
You can only use a fraction of the features of a SQL database when you use an ORM because they don't translate to the new paradigm. When I am using a high level programming language, it is merely helping me do things like manage memory. I am not constantly wishing I could drop down and work with pointers and so on. It is a foundational paradigm that builds on the top of the one before it. ORMs just present a different, incompatible paradigm.
There are plenty of scenarios in certain verticals where you need to control memory management like games and others where you need to program in assembly. Garbage collection is an incompatible paradigm where you need to control when memory is allocated and freed.
Back in the day when I was doing C, there were times when we just couldn’t get the speed we needed from the compiler. I wrote inline assembly. Does that mean C was unsuitable because it was incompatible with our performance requirements?
An ORM is not a high-level version of SQL though. A more accurate metaphor for an ORM would be like a converter from one high level programming language that is object oriented, to another high level programming language that is functional.
In the case of C#, LINQ is a declarative built in part of the language. At runtime, it is converted into an AST and if you use Entity Framework, it is translated into SQL - another declarative language.
You can theoretically express any standard SQL query in LINQ even though outer joins can be obtuse at first. The translation may not be as optimal as hand written sql, but no compiler can translate code into assembly that would be as optimized as someone who could hand roll their own. We decided decades ago that high level languages were worth the trade off most of the time.
> You can only use a fraction of the features of a SQL database when you use an ORM because they don't translate to the new paradigm.
Not remotely true. ORM's are great at mapping views/procs, and you can use any feature of the db you want in a view or proc. Using an ORM does not mean not being able to your database to its fullest extent.
This kind of doctrinaire thinking, this sort of broad and bold declaration, is the stuff of high-traffic blog posts but not good advice for real world developers.
Django, just as an example, does a magnificent job with its built-in ORM. Millions of developers use it, and they are not all fools. A fool is someone who would set out to build a simple-to-intermediate CRUD web app by writing SQL.
> This kind of doctrinaire thinking, this sort of broad and bold declaration, is the stuff of high-traffic blog posts but not good advice for real world developers.
> A fool is someone who would set out to build a simple-to-intermediate CRUD web app by writing SQL.
\u{1f644}
SQL works great for simple-to-intermediate CRUD web apps too.
I love Django’s orm. I wish Django would publish its orm as a separate library. Every time I see a flask / sqlalchemy app I think to myself: Gee you could have saved at least 2x the keystrokes if you’d just used Django.
No it makes simple things easy. Simple things in raw SQL are bloody complicated. Even just getting data, manipulating it, and saving it is at least twice as difficult in maintainability and lines of code than using an ORM.
> An ORM converts one paradigm into a completely different paradigm, which is why it fails and is a terrible idea.
I'm not sure where people get the idea that ORMs fail at their job. They really don't. They do it very well and we're all quite happy.
All the anti-ORM arguments here about how ORMs fail at completely different jobs other than mapping objects to RDBMS operations. Well duh. Nobody complains about cars that can't fly and planes that can't fit on the highway but when ORM can't cook bacon it doesn't fit the paradigm.
> Even just getting data, manipulating it, and saving it is at least twice as difficult in maintainability and lines of code than using an ORM.
That has not been my own experience outside of the most trivial queries. Once any amount of complexity is introduced, I find that ORM-based queries often make it difficult to really see whats going on with indexes and locking, I can’t just paste a query (eg to use EXPLAIN) without finding it in a query log first and, unfortunately, in my experience its rare to find teams disciplined enough to not treat ORM code as if it were normal application code (ie don’t mix it into your application logic), so often end up with a few database/application roundtrips, doing filtering in the wrong place etc. Yes that last one isn’t technically the fault of the ORM, but when I see it again and again in real world code, I start to think that most developers don’t have the discipline to be careful with ORM code while when not using ORM’s and writing raw SQL outside of your applications code, you have no choice. Not the ORM’s fault, but still a symptom of using one that I’ve experienced in multiple teams.
If you want to see what query is hitting the database, put a trace on the database and watch it. It's trivial to copy and view the explain there. Your difficulties aren't the fault of the ORM, it's your refusal to want to change the way you work that is the issue. Query logs work fine as well, having to look in a log is hardly something any developer should find difficult.
Things don't usually start out bad, but they become bad only after you have a more complex codebase with many users (ie many queries). Its at this point that you need to know what a query is doing, yet its at this point that the query logs are full of queries, so finding the ones I want becomes hard. Putting a trace on the queries also becomes hard when you have a large codebase where the query logic is intermingled with application logic. I mentioned this in my comment.
You say it's my "refusal to want to change the way you work that is the issue", but I never chose to work that way. All of the codebases where I've had this issue were inherited: I was not the one to decide to work like this and I did not mix the query logic into the application logic. But that was my point: I've had the same experience across multiple teams in multiple companies, so blaming the developers seems like a cop out and not much of a solution. I'll happily adapt the way I work, but I can't force existing teams to change. Maybe discipline could fix it, but I have not experienced this discipline anywhere I've worked. But sure, its my fault somehow.
There's absolutely nothing in this world that has frustrated me more than knowing how to write a query in SQL but, for the life of me, not being able to express it using the ORM syntax.
Since then I keep using ORMs (because mapping the things you pull from the db to actual objects is undoubtedly good) but I write my queries by hand.
I also dislike ORMs that, by default, when you try to access an object you forgot to pull from the db with the query you wrote, automatically generate another query to pull the data instead of erroring out.
As someone who’s been using mostly Clojure recently, wouldn’t this apply to programming language based object systems, too? Maybe the problem with the object relational impedance mismatch is the objects.
I remember the very first time I had to do anything interactive on a web page. We had a long list of items in a table, and as a stopgap for adding search functionality we were going to sort the table by multiple columns.
Stack Overflow was still a twinkle it Atwood's eye. So I go googling about for stable sort implementations in Javascript and I find plenty. Except they all have the same problem. They were all slow as molasses. I should have taken it as a huge warning that none of these implementations used demos showed sorting of more than 10 items.
I needed to sort 100 items. 300 at the outside. Ultimately I had to go to source material and implement it myself.
Clearly there is some notion in the software community that market for ideas is inexhaustible. That the 'oxygen' in the room is infinite, and therefore I can interject whatever half-assed concept I had into it without costing anybody anything.
Virtually all of us, when we tackle a problem, try to do better than what is already on offer. But what if the thing on offer is truly, horrible? If my only goal is 'better' instead of 'good', then my alternative will be really bad. And in a field full of bad, who wants to be the person who introduces the 6th standard? The 8th?
Keep your dumb ideas to yourself, or put them in question form and ask people why
After someone posted Norvig's Sudoku solver, I was troubled by his statement about how many algorithms he'd have to implement so he just did brute force. So I took a whack at it, got fairly far down the deductive path before things got hard. But I'm not going to show it to everybody. The internet has been working on strategies for 10 years, and they've done way more algorithms than the ones I knew about. The best I've managed is to maybe simplify a couple rules, but I think one could argue that it's the bad definition of 'simple'. It can't handle as many cases, but I can explain it to anyone. Is that enough to add to the noise? Probably not.
For anything complex, definitely. For anything simple, eh.
It also feels to me like he's talking about a specific ORM - I know ActiveRecord has it's fair share of issues, but from what I know of AR usage and implementation, it either doesn't do what he's (legitimately!) complaining about or does do it in the way he's suggesting.
Ah, Java. Tooting my own horn a bit, I wrote a custom YAML/XML/JSON parser for C# specifically because the existing parsers required me to turn the data into objects before being able to anything at all, rather than allowing me to interact with the data just as data. Say, cleaning up and/or validating the data.
This is a common problem I've run into in strongly typed languages; can't "just have some data", have to have an object :/
We used a third party tool for mailers and it was having severe performance problems after moving the server to a cloud based solution.
We confirmed what the DBAs said, the latency was only 34 seconds round trip and the database was pretty fast. I installed wireshark to see what was going on. It was querying a table with 80,000 entries, and then for each entry it would do another, "select ... limit 1" query to get the meta data on each record. This loop was abstracted away in an ORM and the third party had very few clients with tables of our size for that particular table.
When it was run on prem it wouldn't take too long, but when it was on cloud it would take 45 minutes. Insignificant amounts of time being spent thousands of times ends up being significant time spent, and this can creep up on you as more things move to be serverless / API driven. A bunch of money and time was spent on figuring out the source of the issue.
Maybe it's because the problem isn't directly caused by ORMs but just a very poor usage of ORMs. The same problem would have existed if a loop was written to perform the same query.
Sure you can make arguments one way or another regarding if a hand-written block of SQL would have the same flaw, but if an experienced DBA or developer writes it, I would bet on their output way over anything an ORM outputs.
If you consider the software development process as a whole, the explicit SQL approach intrinsically guarantees additional scrutiny of the actual SQL statements. If you hide all of this behind an ORM, all that is seen during code review time is some beautiful POCO model with a few extra IEnumerables thrown on it. No one is paying attention to the man behind the curtain and the horrible join that was just created automagically.
Perhaps the answer is to just log and profile all the ORM generated SQL - Sure, but if you could look at the ORM's output (which in some cases is abusively large) and quickly determine if its good or not, why not just write it yourself and be 100% sure from the start?
You literally need the same piece of knowledge to to avoid that n+1 problem in both Raw SQL and ORMs: you have to use a join.
Developers have been making n+1 mistakes with Raw SQL for years before ORMs became popular.
It doesn't matter if the "DBA or experienced developer" makes a select query that is better than the ORM. If this select query is inside a loop then all bets are off already.
I'm kind of curious about what data the select limit 1 queries were returning that the ORM couldn't get in a single query and had to go back per record. Can you shed any light on this?
ORMs have a degree of flexibility. It's possible to write n+1 queries accidentally, especially for developers who are new to the ORM. It's also often possible to address those issues. Sometimes trivially, sometimes not.
The other thing that comes to mind here is that maybe the query was hitting a non-covering index and that was triggering the lookups? In that case a covering index would have fixed the issue?
In my experience, though, the cases where ORM's seem to be a good fit end up growing to cases where they're no longer a good fit - and getting away from them ends up hurting more than just starting without one to begin with.
My personal gripe about ORMs is that they have two main usage patterns, and each of them has major drawbacks.
The first pattern, common in frameworks like Django, is to cram the business logic into the ORM instance objects. This creates a tight coupling between the two separate concerns (business logic and data persistence), and will cause problems as soon as the two structures deviate from each other.
The second pattern is to keep the ORM as a purely data storage layer, which is much more flexible but commonly causes duplication of nearly identical structures in separate layers.
> In these cases, I’ve elected to write queries using a templating system and describe the tables using the ORM. I get the convenience of an application level description of the table with direct use of SQL. It’s a lot less trouble than anything else I’ve used so far.
This feels like good balance. I want to express my database schema via OO classes. It eases db migrations as the application grows if you use tools like Alembic (same developers as SQLAlchemy).
But use care to avoid SQL injection risk with template queries. SQLAlchemy makes this easy using .bindparams, as does .NET via SqlCommand.
This topic pops up frequently here on HN and every time I’m shocked at how many people have issues with ORMs! I’ve been using Hibernate/Spring Data for several years now and never ran into any issues. If I need to write a complex query, I can easily write a @Query annotation in HQL and it neatly fits right in to the repository class. I also develop with query logging enabled so I have better understanding of the queries actually ran on the DB. I think it really boils down to using the right tools, the right way.
Same here. Hibernate team had always, even as early as 2003 when I first started using it, advocated for turning on show_sql=true in development. If the code interacting with the DB fetches a table - rows and columns - of data, say for displaying a report, ORM does not buy you much. Instead, if the code converts the ResultSet to entities in an OO language you always end up using an ORM - a hand-rolled one, or an off-the-shelf one. I just don't understand the hate.
This topic pops up frequently here on HN and every time I’m shocked at how many people have issues with ORMs! I’ve been using Hibernate/Spring Data for several years now and never ran into any issues.
Same. Hibernate with Spring Data JPA, for me, "just works". But it's not necessarily the right solution for every database interaction. But for a typical microservice which might have up to 20-25 discrete entities, and few if any crazy complex relationships, I find that I plug it in, extend CrudRepository, write some JPQL queries in @Query annotations here and there, and bob's yer uncle.
If you were writing a monolithic CRM system with 900 domain entities, super complex rules governing the inter-relations between them, and trying to write your analytics queries right into the app, then an ORM approach would probably fail miserably.
I have to congratulate you for having worked with a quite sophisticated and complex piece of technology for several years and never run into any issues.
> I’m shocked at how many people have issues with ORMs
Simple inexperience. I'd bet most of those people are mid-level developers who have used ORMs enough to hit the rough edges but not enough, or with enough independent agency, to have worked through how to play to ORM's strengths while avoiding their weaknesses. People that were given a hammer and are just understanding that their hammer doesn't work very well to install bolts, and maybe don't have the authority to say maybe I should use a wrench or the flexibility to try this new crescent shaped idea and see if that works better.
It's condescending and convenient for you to discount the people who disagree with you as merely inexperienced.
You could level the same empty claim at people who like ORMs: I loved ORMs when I was a beginner but learned it's best to avoid them as I accumulated experience. So anyone who likes ORMs is merely in that beginner stage.
Or maybe you worked only on “toy” projects and never understood that you are doing more work that could be expressed more elegantly in a functional SQL expression instead of using the imperative statements of the ORM engine?
The craziest thing in this discussion is that I have to defend SQL that is probably my least favourite language... I never expected this honestly.
My "toy" project with several hundred tables and tens of thousands of users does fine.
I have, at most, a couple dozen "complex" queries in this project.
Whereas I have an order of magnitude more queries that need to be composed from several different query criteria, a task for which SQL is very poorly optimized for and most ORMs excel at.
I have used my ORM for so long that writing a report in SQL or the ORM language is basically the same to me. Neither technology is something I would consider to be "hard", as most of the problems encountered in practice are well-trodden.
Nontheless, a simple ORM query is 20% the length of an equivalent SQL query. And I can compose them trivially. And then I use the model code for the _hard_ part: dealing with the rest of the business logic for template rendering, email sending, API interactions, and so on, for which SQL is completely useless.
And regardless, why should we develop things that only "experienced devs" can work with without blowing a hole in their face? Junior and intermediate devs are often in larger projects.
Reality does seem to paint a very specific picture.. Django; ORM. Rails; ORM.. I think just about every super popular framework either created or has official support for an ORM. Most popular language platforms have a descent enough ORM options. Phoenix? Let's just call Ecto an ORM.
Thinking about projects I'm aware of.. Stack Overflow created an ORM regardless of if they call it "Micro". They created it and people use it. GitLab uses ActiveRecord.. Gogs and Gitea use ORMs that are a bit fringe TBH.. I actually can't think of a large project I've touched that went completely raw dog on the SQL.
I do believe there are instances where using an ORM, or at least all of an ORM, is not the best choice. But to come out and say they should always be avoided? It's interesting how many people are coming in here SO SURE about that position that is seems completely at odds with what the rest of the industry is, objectively, doing. And when pressed their insights into that opinion are so shallow a baby couldn't drown in them. Hmmm.
I'm trying to pick up FeathersJS right now and I will tell you it is hard to find ANY examples of complex queries translated to the ORM's logic. I can find bits and pieces but without any sense of the underlying purpose.
There are 500 tutorials of installing Feathers and making a CRUD app, but something as simple as combining results from two requests or doing a raw DB request seems to exist only deep in the docs (and more often than not, requires bonus functionality from an add-on).
I am familiar with managing databases and I can see benefits of ORMs, but I feel like "glue" technologies could benefit from deeper real-world examples that move beyond day 1 tutorials and get into translating existing code to their style. Half the time I am left digging through outdated github code to grasp the basics of how and where mid-level functionality can be used.
And if you either lack the skills to properly use JPA/Hibernate or really have super custom special requirements, you can at any time execute native queries with the EntityManager. Still not enough? You can even directly work with Connection objects.
If you use Go and PostgreSQL, I’ve been working on a tool to help you “just use SQL” called sqlc[0]. It generates methods and structs for your queries, automatically. It’s made a dramatic difference in my day-to-day workflow.
I really like this! I particularly like that it is oriented around a useful workflow, rather than for just "small code at rest".
It looks like it parses the schema to find type information (2 minute skim, so please forgive me if I got it wrong!) Hoe does it handle schema changes?
I don't want to lose minutes or hours debugging a stupid typo.
> * I don’t test much
Writing meaningful tests with my object model without having to care about bizarre SQL semantics.
> * I don’t care how much time and bandwidth it uses
Because ORM will optimize the queries for me, cache results and perform lazy loading when possible.
> * I only know Java (or C#, or whatever)
Using the full power of expressive languages to work with my object models, keep things DRY and reuse code, and have support for different storage engines and sql flavors.
ORMs have taught me to not rely on ORMs for critical storage. I have one product that uses ruby DataMapper. With my particular product (embedded industrial equipment) it’s terrible. I have to constantly fight it to correct MySQL problems.
If they had just used basic sql it would be much easier to refactor.
Each time I see someone complain about ORMs I remember Greenspun's tenth rule[1], which adapted to ORM would be:
"Any sufficiently complicated program contains an ad-hoc, informally-specified, bug-ridden, slow implementation of half of a decent ORM."
ORMs are hard for a reason. Using an ORM doesn't mean you can't or shouldn't use plain SQL where the situation calls for it. You can mix and match perfectly fine.
To me it is more of as Ted Neward describes "ORM is Vietnam of Computer Science"[1]
"Although it may seem trite to say it, Object/Relational Mapping is the Vietnam of Computer Science. It represents a quagmire which starts well, gets more complicated as time passes, and before long entraps its users in a commitment that has no clear demarcation point, no clear win conditions, and no clear exit strategy."
Ya I’ve heard this one a lot. It’s kind of funny to say and does humorously underline the complexity of the problem but people take it seriously. So to take it seriously for a second:
There was no good reason to be in Vietnam; even taking the stated rationale as a given, which many people did not, it was a concern many levels removed from the actual safety or functioning of American society.
ORMs in contrast achieve much more proximate goals — they solve a real problem and can measurably reduce the amount of code you have to write. It is tedious to write the same sort of SQL query over and over. Even if you prefer to use literal sql for the complex stuff (as I do), ORMs tend to be a significant win (in fewer LOC to write) on abstracting out basic queries.
Tbh you could easily claim that the explicit goal, mapping to objects, is incorrect.
The real value is to reduce the damage of the SQL language itself — the unnecessarily ordered clauses, the arbitrary inconsistencies in syntax, the worthless parser errors, the lack of any static typechecking — which cause so much code bloat and debug headaches.
There are two reasons to use the ORM: to not learn SQL, and to generate SQL.
The first reason is the commonly provides one, and what leads us into vietnam. The latter is why people try to avoid ORMs, yet find themselves back in vietnam.
What we really need is a less shitty version of SQL.
Ye. Static typechecking is the only thing in his list that I really care about, since you can "git gud" at SQL and not be bothered by the syntax/ordering/parser concerns. jOOQ is exactly what I want to bridge the gap between Java and the DB.
The ordering is always a problem, because your logic may not follow it. Eg if your set of conditions apply to multiple queries, then you might know your where conditions before you know your select/from clauses.
So instead of building up your sql string in a straightforward fashion, you need to have at minimum an abstraction that delays construction.
You get lead into vietnam as almost a direct result of SQL’s context-sensitive clauses.
That looks fantastic. No magic mumbo jumbo mapping, just a simple type safe sql. Both syntax safety (no need to remember which of WHERE and HAVING comes first) and type safety on all fields. It's not advertised in the examples on the front page but i also take for granted sql injections are completely impossible since all data goes into functions and are not string formatted, without the mess of having to remember the order of arguments as with prepared statements.
Anyone got tips on similar frameworks for other languages than java and for other dbs.
What I was trying (and evidently failing) to say is that a sufficiently powerful query generation system that's designed for people who actually like their database to be able to generate the exact SQL they would've written by hand is essential to the 'mapper' part of the equation being able to smooth out any impedence mismatches between the appropriate object model and the appropriate database schema.
Hopefully that longer answer is a bit clearer than my first attempt.
SQLAlchemy is pretty close. Been quite a few years since I've had a chance to get drunk with the authors and compare notes though, so I'm not going to try and get into details because I'm pretty much guaranteed to get some of them wrong.
> What we really need is a less shitty version of SQL.
I think the same. In my spared time I building a relational language (http://tablam.org ..accept more help!) starting even lower: Without a rdbms.
I work in the past with FoxPro, and was possible to build a full app with UI and reports and all stuff you can imagine with a database-oriented language. You code the UI on fox, query with fox, make triggers with fox, etc...
I have found the best ORMs don’t hide their SQLness much. SQLAlchemy is pretty great, but you don’t get full use out of it unless you have your arms around SQL itself. When you use an ORM to cut down on chores it’s great. When you use an ORM to avoid your datastore and it’s idiosyncrasies it is worth taking a long hard look at why :)
Most of the ORM interactions are well formatted code that don’t hide the datastore much. As long as you let the ORM map in objects and it’s performant, life is alright. When the ORM starts running the show there may be no coming back.
I enjoyed this comment because I only I only recently tried Sqla for a project and agree fully that it embraces sql, in large part by NOT renaming/rethinking things at the oop level - methods very much tend to be named after sql verbs. I really liked this.
What I liked less was all the setup/config ceremony. Compared to ActiveRecord (the Ruby lib not necessarily the orm concept) I was using more LOC before I got to the part where I started saving time on simple queries. I realize this is because sqla uses the datamapper model. But for me thif sweet spot would be auto setup like AR with sql-like syntax of sqla.
Yeah. When you get good at composing SQLa code it is really nice. You can functionally build your queries and DB interactions. It just fit well with my way of coding. ActiveRecord had a lot of magic to my taste, but you could still drop down with it. Agree SQLa can be tedious at first. No tool is perfect :)
> What we really need is a less shitty version of SQL.
My view is the opposite. The power of SQL perpetuates a low-quality software culture. The root issue is a dev culture that can't see past databases.
A lot of software design runs like this: (1) translate business patterns into a relational schema; (2) build interactions with that schema; and (3) as that gets harder, use SQL arcana and ORMs and views and stored procedures to squeeze out flexibility.
I worked like this for the first decade of my career. My systems got some use, and struggled along, but they are failed projects.
Repeatedly I had this feeling: the project is almost done, but there are some concurrency issues where I would not even know how to start addressing them.
The database-centric design made it impossible to work past that.
After much searching, I came to this: Stored State is a brittle and unwieldy thing, and you want as little of it in your life as possible. The more you have, the harder you have to work to get anything done. Databases are an institution of Stored State.
As an alternative, you can derive state from messages.
I see the same problems but it usually caused by a reluctance to modify the database schema. Once you get comfortable doing that and keep your schema matching the requirements then it solves a lot of the problems.
> ORMs in contrast achieve much more proximate goals — they solve a real problem and can measurably reduce the amount of code you have to write.
Why would you optimize for LOC rather than expressing the behavior you want well? In some situations sure—rapid prototyping—but that’s an odd assumption in a general case.
I would actually let OOP off the hook here. I think what did the harm in this case was the java generation. The generation of programmers that were told that in the future they would only have to write the "business logic", and everthing else would just happen. They were taught javabeans, orms, gigantic frameworks. They completely forgot that their code actually needed to execute, and no one cared about their "business logic" if the application didn't do what it was supposed to do.
This generation has only ever used ORM's, and so to them those tools must solve some hard problem, they are so complex after all. SQL must be hard.
It turns out that SQL is actually much simpler than ORM's. The failure modes are much simpler, and the implementations more robust. Sure, writing the code can be tedious, but tedeious is not hard. Writing brainless code every once in a while gives you time to reflect on the design of your system, and think about the larger context.
> It turns out that SQL is actually much simpler than ORM's.
Are they simpler when it comes to matching the data to the application's data structures? One advantage of ORMs is that they encourage this setup from the start.
This is the polar opposite of my experience. Not to mention that you undercut your own argument by admitting SQL’s tediousness: tedious code tends to lead to more tedious code, as subsequent developers fear breaking something, so they just add a layer on top of it, rather than addressing root causes. So that SQL view you had now has 10 inner joins in it, a union, and is being used by five other views now, each with their own similar levels of complexity. Trying to do any refactoring on this is almost humanly impossible, because you can’t keep all of it in your head.
And this is an extremely common situation to find yourself in. Code bases which are of middle- to large-sized, and which are inevitably touched by various hands of various skill levels, tends towards complexity.
SQL is the worst source of unmaintainable and difficult to refactor code. It’s difficult to unit test. Error messages are more often than not inscrutable. (I’m looking at you, Oracle.) There is no idiomatic way to break up complex SQL into functions or classes. There’s no type checking. You don’t have the equivalent of Ruby Gems, Python’s pip, or Swift’s CocoaPods. IDE support is limited to syntax highlighting Compare this with something like Eclipse or IntelliJ where you can just Ctrl-click on something and go to the definition. Want to rename a public method in a statically typed language? Pretty easy. Want to rename a column in SQL? Yeah, good luck. Not impossible, but you don’t have any guarantees that something won’t break until runtime.
So yeah. ORM has its place, and modern ones work very well at abstracting away SQL’s weaknesses.
The best ORM (outside of ActiveRecord) I've seen was a proprietary hand-rolled one that solved the impedance mismatch.
It had a canonical XML format that entities were defined in and code generation for data access layers, domain models, view models etc.
It actually worked better than I've seen the abuse I've seen developers put EF through. I found it nicer and simpler than times I've worked with Hibernate.
I'm not going to say it was perfect, but every time I think back on it it makes me want to revisit the idea of leveraging a bit of code generation or metaprogramming to be able to have a canonical definition of an entity transformed into concerns that deal with the given entity at different points in the application.
That part of it just really hit the sweet spot for me.
MyBatis has _some_ similarities, notably the defining entities in xml part. But it diverges after that.
Think about it more like MyBatis meets Spring Data JPA. Define that entity in XML the run a code gen which gives you the CrudRepository class but also generates a controller that exposes an API with pretty good good ability to specify adhoc queries. Plus view models.
I think it worked because it both reasonably well designed and hyper opinionated.
Db4j is an async transactional database engine that uses java as the query language. It doesn't eliminate the O/R impedance mismatch entirely (it's not a graph or object database), but it does simplify it greatly since everything is java
I love the idea and it's been fun for me to make demos with, but I've gotten almost zero feedback on the API, and what I have gotten is "can you add a SQL frontend". And yet neither ORM nor SQL are loved
So the question (and I'm not suggesting that Db4j is the answer) is: "what's the API that would be most natural ?"
I think that this is true if you're writing your application in a way that requires object-relational mapping in the first place.
And that's only necessary when you're trying to manage your data in the application in an object-oriented way. And managing your data in an object-oriented way implies more than just the simple fact of defining classes to serve as data records. Those classes can be entirely equivalent to a struct in a procedural language or a record in a functional one. And I can't remember ever suffering from object/relational impedance mismatch when working in a procedural or functional language. Implying that the spot where you really start getting into trouble is when you trot out some distinctive feature of an object-oriented data model.
I submit that the original sin is treating instances of those data classes as if they are discrete entities that can serve as an application-side proxy for some other discrete entity that exists in the database, almost as if ODBC were just a more REST-flavored alternative to CORBA. Which is a thing that I've often been tempted to do in an object-oriented language, but never in a procedural or functional one.
Which isn't to say that I don't use anything to help with talking to databases in those other styles of language. It's just that I retain SQL as my query language (there are plenty of reasons to do it, none of which I'll bother to repeat here) and rely on a more Dapper-style library to handle unpacking the results into data structures. And I don't really consider those to be ORMs; they're just a special class of data mapping utility library.
So, in conclusion, I think that a more accurate stab would be "Any sufficiently hastily built, object-oriented, database-driven non-ORM program contains an ad-hoc, informally-specified, bug-ridden, slow implementation of half of a decent ORM."
(1) While treating a single SQL table as a distinct entity is a sin by your argument, I would argue that treating a set of tables as a distinct entity is not. I'm referring to what DDD calls an aggregate, e.g. a customer (address + email history + scoring + whatever tables), or an order (line items, shipping info, payment info, ...) Reading the comments here, I'm starting to wonder if some kind of holy grail or at least useful approach lies in there.
(2) The impedance mismatch has two ends, and one might as well argue that the problem is not mapping tabular data to objects, but mapping objects to tables. This might be an underrated benefit of what is usually called "NoSQL", dwarfed by the whole discussion around "schemaless". Unfortunately, I don't have any experience in that area, but it's something I have long planned on trying. Note that I'm not trying to say that NoSQL "solves the impedance mismatch" but rather that, in DDD's approach to solve persistence, NoSQL might actually be able to do what DDD wants (e.g. with respect to defining a "unit of consistency"), unlike tabular data.
A distinction that may seem like pedantry, but I think is actually the crux of the issue I've had with with ORM:
The problem is not in treating entities in the database as entities. It's in treating instances of classes in the application's memory space as local proxies for those entities.
You're on much firmer ground if you understand them as simple pieces of information derived from the state of those entities as of some point in time. Both in terms of ending up with a more internally consistent and robust approach to data (by virtue of removing some temptation to preserve an illusion that this data can be presumed to always be complete and up-to-date), and in terms of not artificially cutting yourself off from most of the power of the relational model.
You're right that this tension is somewhat resolved by just switching to using an object store of some sort. (NoSQL is far too diverse of a subject to treat as a single unit.) But it's a very particular sort of resolution, because it's a sort of least common denominator approach where you just drag the data store down to the level of the programming model. It strikes me as akin to resolving the difficulties in maintaining complex software in Perl by resolving to never let anyone write anything more powerful than a shell or CGI script rather than by looking for a language that will better support you for the long haul. It can certainly be a fine and reasonable choice. The problem comes in when people don't fully realize that that's the choice they're making.
What you are saying exactly highlights what the author is missing: That if your application has some logic, you will eventually have to map your database rows to your in memory typed structures.
It sucks. Sometimes it sucks less if you map your queries as well, sometimes it sucks less if you stick to SQL and only map your results, sometimes it sucks so much you're better off with a no-sql solution.
But when using a relational database, ORM isn't optional.
OOP is optional, but if your program has any concept of structured data, then, regardless of whether that structure can be expressed syntactically within the programming language, you will necessarily have a mapping between that and the database. It might be as simple as a 1:1 mapping between relations and ADTs, with collections of references being used within the program to represent the keyed relationships that exist within the database scheme.
This seems like one of those topics where people often feel the need to pick a side for some reason. I've often heard criticism to the effect of, "people only use ORMs because they don't know SQL. Learn SQL!" It's seemingly impossible to convince these people that ORMs are fantastic for reducing boilerplate code and they can coexist right next to raw SQL for problems gnarlier than "select all foo where bar equals baz."
Earlier in my career I made a point to deep dive into SQL. Long story short: I realized that there are a lot of very good reasons to limit the amount of raw SQL in your application that have nothing to do with familiarity.
SQL is just a bad language, and it’s unfortunate that we’re still stuck with it, basically unchanged, decades after its introduction.
YMMV, but for me it feels like magic every time I use it. I can get things done in declarative way that would take a while in any programming language I know (and I'm a Clojure guy, so I value simplicity and conciseness).
The only issue I really have with SQL is the lack of control over how a query is executed can really be an impediment. It's like how people were talking in another thread about getting frustrated with garbage collection.
I spent a lot of time working in a department that wrote a lot of ad hoc Oracle SQL, for updates to a production system, and complex reports, and there was one guy that was ten times faster than everyone else, and also more likely to get his code correct, and so I paid attention to what he did.
He would break down a complex set of operations into simple individual queries generating temporary tables; he just wouldn't bother fighting with the optimizer, or trying to predict what it would do. So he not only wrote queries that ran quickly, but he wrote them quickly, and he got them correct quickly.
I would read Tom Kyte, exhorting people to use the full complexity of the language and the Oracle optimizer, but from my experience, it just was not the way to go. I wrote many page (or more) long queries that were things of beauty and then found that breaking them down into simple ones actually was usually much faster.
One fundamental thing that I don't think gurus understand, is that for the average grunt in a typical corporation, there is a separation of duties, such that you can't just go and change the things that a system administrator controls. So saying "your database is configured wrong" doesn't address normal life.
Although the pure relational model may be nice to think about, I find it really convenient to have a certain amount of sequential context, and PL/SQL always seemed to me to have a disgruntled relationship with SQL, so I've gradually tended towards Microsoft alternatives.
Unless you don't have objects... then no need for an Object-relational mapping, not a well maintained one, and not an ad-hoc one. All you need is a DB connection pooling library, some helpers or libs to help you build dynamic SQL queries and that's all.
653 comments
[ 3.1 ms ] story [ 359 ms ] threadI think it's easier to learn SQL than learn an abstraction of a DSL.
2. “Executing it”—this is called a SQL client library. If you install your SQL statements as stored procedures you can execute those procedures by name, for example. Not that hard.
3. “Bringing the data it returns into the object oriented universe”: I am typically satisfied with an array of hash tables. Funny story: what do you get when you use ActiveRecord’s find_by_sql with a query that returns columns not in the original table? You get a collection of objects each with the extra column monkey-patched to the individual object. In other words, a slower and stupider hash table that happens to have dot syntax rather than bracket syntax and a bunch of do-they-even-still-work-in-this-context instance methods attached.
It is, and all ORMs I know use it under-the-hood. It is the boilerplate code by which SQL is issued to the DB, and results parsed into something usable by the rest of the program that people try to avoid writing over and over.
Whether or not parallel arrays of template and value pairs to be anded together is better or worse is debatable.
Some api like prepared statements (supported by the db itself) for building up complex queries step by step would be nice. Does something like this exist?
https://javarants.com/generate-jpa-or-gorm-classes-from-your...
When doing work in python, I don't feel it has a comparable ORM, to where I kind of write my own files that have things like finders and updaters and creators. In most cases, I've found it's much better than SQLAlchemy. Dealing with joins, and things like math, meaning averages, sums, distributions, division, is so much better to be done in the query rather than more basic queries and looping through the results.
There are of course cases like injections to look at, but lots of things I have are calculations of data and showing it, so we're able to handle it with raw queries. Also, ActiveRecord has ways to enforce no injections.
In lots of cases, we've found that using an ORM to start, finding slowness, and moving towards raw queries is a great way to go.
I agree that ORM users should learn SQL, but SQL and an ORM are not mutually exclusive.
Learning SQL with an ORM does remind me a bit of learning memory management with a garbage collector: helps you make better choices as to how you put things together, even if you never directly use the know-how.
In particular I like that it behaves way better using surrogate primary keys (serial id column) instead of natural primary keys... it just answers that question for you that would otherwise result in bike shed conversations.
We minimize logic in the AR models, instead those are in glue objects and this pattern works real well. AR models are mostly there for describing relationships in the code and doing data validations in the code on top of CRUD operations.
The first item about querying is relevant but all ORMs allow you to drop into SQL to execute a complex reporting-style query. It's not really necessary if you are just querying in objects to manipulate (which is what ORMs are good for).
But I do agree that data mappers tend to be better.
Databases think in tables and columns and rows and queries and indexes.
If you don't pick an ORM to help manage this translation layer, then you'll end up re-implementing your own. Maybe this is OK, because yours will be simpler for quite some time.
What else are you going to do? Stored procedures? Concatenated strings?
The ideal for me is something that manages connections, lets me write the SQL, and gives me easy access to ResultSets, perhaps in the form of an object.
And yes, stored procedures are extremely useful, especially for minimizing chatter between the server and the database. Use them appropriately. (Although I don't see how they are an alternative to ORMs.)
Only in the loose sense that you have to put the data into an object. That's a tiny portion of what most ORMs actually do.
I just cloned the hibernate-orm repo and, even excluding the .git files and /test/ directories, it has about 30 MB of source. There's a lot in there.
The way code thinks about objects, behaviors and relationships is unlike the way you need to have it stored. Trying to use ORM-generated objects directly in a more complex business logic is, I learned, a recipe for disaster.
And since you're already writing two layers - data translation layer and actual domain layer - then, one may wonder, why not skip the first layer and implement the domain layer in terms of SQL queries?
2016: https://news.ycombinator.com/item?id=11981045
Discussed at the time: https://news.ycombinator.com/item?id=8133835
Both sides continue to distrust the other, though.
https://news.ycombinator.com/item?id=20872571
I don't think i'm alone in saying I'd rather hire a developer who is native with an ORM and can dive into SQL when things get thorny, than hire one who's going to fight me on the very merits of an ORM at all.
I mean, hey. To each his own. But lets be clear: this is ridiculous, and if you think it's a position you want to take up, make sure you can get hired holding onto it.
I tolerate an ORM at work. Secretly loathing it. Like much of industrial programming.
The argument I've made before when going down the path of ORMs has been: do we forsee needing to use this model code on a different database engine? Outside of simple toy applications, or needing to support different engines with the same code, I agree that ORMs are more trouble than they're worth.
Whenever I get to the point where I'd do something gnarly with a query, I just fall back to SQL statements with results that the ORM can parse; or just dump directly into data structures and go from there, even if the originating tables still have ORMs.
If your framework/library/language doesn't let you skip the ORM - or populate the object with the results of a custom query - well, I'd say that's a failing of that ORM, not of ORMs in general.
You can (and should) use them for simple queries. You have a list of entities you want to query and filter, that's going to be fine. Joins are fine. But if you're doing some complex analysis, an ORM is the wrong tool. That doesn't mean it's a poor abstraction, or difficult to follow, or something to be avoided. It's not the right tool for that job. For the job it's designed for, it's going to save a lot of effort.
SQL is great for analysis -- it's pretty much what it's designed for. But for bringing data into your app and modifying it, SQL is cumbersome and verbose. If you're loading data into objects then you're just creating your own personal ORM anyway.
See digging.
Simple tasks are done maybe thousands of times in any one application. It's the complex tasks are rare.
That's not a fair comparison unless you include the time and effort involved in creating your ORM models, before you can even write those 3 lines of ORM code. The effort to construct those models isn't even fully amortized over your project, since it must be maintained through schema migrations.
ORMs are like putting on an exoskeleton to go buy groceries because it will let you carry your bags more easily. In my opinion, the complexity and indirection they introduce for accomplishing simple tasks do not justify their existence in the vast majority of cases.
It's no more difficult than building tables by hand. There isn't any extra work that you wouldn't also have to do when not using an ORM.
It's not much more effort to type "create table employees" with all the fixings as it is to type "class employees" with all the fixings.
Depends on the ORM. Just like "raw sql mode", not every ORM supports that. It's not an inherent feature of being an object-relational mapper, it's part of the bells and whistles of some ORM packages. And I'm going to guess you're coming from the Python/scripting universe, because generating models in other languages is definitely more complicated.
But let's pretend that all ORM software can generate model definitions from the database. Just do the update then. I want to validate your argument that a simple update in SQL is "a lot more code especially if wiring up foreign relationships." If you think an ORM is much less code, or simpler, I'd like to see how.
In my API, I have models from my ORM. These can then be used for database migrations, which allows changes in my database structure to be checked in to version control.
From there, these models are obviously used for queries within my app. Generally, you are correct that generating a simple query with the ORM is not meaningfully easier than writing the SQL myself, with the one exception being that the ORM sanitizes inputs to my queries without me having to think about it at all, which is nice.
From there though, I can use the models from my ORM to automatically validate incoming requests to the API endpoints, as well as automatically serialize the results of my queries when I want to send a response back to the client. If you're not building a REST API, obviously this might not be particularly useful, but for someone that is, it has saved me a lot of work.
Finally, generally I find code in my code is a bit easier to debug than strings.
The ORM is performing operations on objects -- it's pretty much right there in the name -- it's going to be a poor choice for bulk updates because that's not what it's for. But again, nothing stops you from using the right tool for the right job -- whether that be an ORM or raw SQL.
A good SQL access library can validate queries against the database itself at build time.
The Hibernate repo is 700 thousand lines of java code!
There's a tendency amongst some (especially "enterprise" programmers) to forget that dependencies are also just code. If they break, you are on the hook too. You own that complexity.
And by "drop into", this typically means writing custom stitching code that stitches the SQL cursor results back into the models again. It's rarely straightforward.
The biggest selling point is a massive reduction in boilerplate code. Database agnostism is a feature that almost nobody ever uses, so who cares! Your proposed alternative is engine-specific SQL so you lose either way. At least with an ORM, you'd lose significantly less. You'd just have to deal with the places you used SQL. Which, in my experience, is pretty small and pretty specific.
> this typically means writing custom stitching code that stitches the SQL cursor results back into the models again.
I often feel like people who complain about ORMs have either actually never used one or used a poor one. As long as my query matches the structure of my object(s) I don't need any stitching code. And if they didn't match, I wouldn't write stitching code because that would be waste of time.
If I'm writing a custom query, I'm probably not looking to integrate into the model anyway -- if it's used for reporting I'd just take the results as is. If I'm writing a query specifically to get a matching model object (to manipulate) I'm going to get the whole model and no stitching would be required.
I haven't heard anyone talk seriously about database-agnosticism since the very early 2000s. Maybe some commercial products still try (choose MS or Oracle!), but it's rare nowadays.
The primary selling point of an ORM is that it abstracts marshaling/un-marshaling rows to/from entities. Instantiating and persisting entities to relational storage.
> And by "drop into", this typically means writing custom stitching code that stitches the SQL cursor results back into the models again. It's rarely straightforward.
That's not typical in most uses I've seen. Far more typical are things like:
- Go straight to SQL for reporting, since that's what SQL does. Useful in reporting contexts, and also for list/filter UI screens.
- Use raw SQL to query a list of entity IDs for updating based on some complex criteria. Iterate over the identifiers and perform whatever logic you need to before letting the ORM handle all the persistence concerns.
Do you use the same database engine for your unit and integration testing as you do production? I don't. I use sqlite for unit and local integration testing, and aurora-mysql for production.
As a side note, I quite literally can't use aurora-mysql for local unit and integration testing. It doesn't exist outside AWS.
You can use mysql though...
An integration test that doesn't use the same DB as production is unsatisfactory.
Integration tests should run against a test environment, otherwise, what integration are you testing? I don't see the value in writing integration tests that test the integration between my code and a one-off integration test DB that exists solely for the purpose of integration testing.
Yeah, don't do that.
That's a recipe for tests that don't catch edge cases.
Also if you’re using only the subset of MySQL that is supported by SQLite, you’re missing out on some real optimizations for bulk data loads like “insert into ignore...” and “insert...on duplicate key update...”. Besides that, the behavior of certain queries/data types/constraints are inconsistent between MySQL and every other database in existence.
Finally, you can’t really do performance testing across databases.
1. If the suggested alternative is writing only SQL from the get-go, then why would one even care about database agnosticism?
2. A large part of SQL is compatible between databases, so this might be a non-issue.
3. You can always use specific in-database abstractions such as Views and Stored Procedures for those complicated bits.
Queries look like:
SELECT u FROM ForumUser u WHERE (u.username = :name OR u.username = :name2) AND u.id = :id
Where ForumUser is your model.
https://www.doctrine-project.org/projects/doctrine-orm/en/2....
[0] https://github.com/martin-georgiev/postgresql-for-doctrine [1] https://www.doctrine-project.org/projects/doctrine-dbal/en/2...
It's the perfect type-safe abstraction on top of raw SQL.
https://github.com/ServiceStack/ServiceStack.OrmLite
https://github.com/CollaboratingPlatypus/PetaPoco
Any errors you get are likely a result of the underlying database/provider (foreign key constraints, etc).
You should never write raw SQL (if possible). You don't need an ORM to achieve that.
With any of the ORMs I've used, as soon as you do something even slightly unorthodox like using a view, you're on your own.
If you're on your own using a view of all the simple shit, you're using an awful and pointless ORM.
In ActiveRecord, there's a method called find_by_sql. You can't call it directly; it's a class method on an ActiveRecord model. So you have to choose which of your ActiveRecord models should be used to instantiate the rows of your result set. (What if your result set doesn't really match any of your models? Pick one arbitrarily.) Your SQL has some extra columns. What happens to the data in those columns? They get monkey-patched onto the individual objects. (Which is stupidly expensive in Ruby.) Other than that, the individual objects are fine. They even have all your smart instance methods, which may or may not behave properly with all the ad-hoc monkey patching.
If you tried to short-circuit all of that nonsense, you tend to get arrays of hash tables. Which is, in my opinion, already a perfectly adequate interface!
I don't want to sound like the ORM defender, but I'm not sure I understand.
This sounds like a deficiency of Ruby and the ActiveRecord record model. In Java, for example, you'd just write a new POJO for your query, which isn't exactly difficult. There are no "smart methods" or whatever.
It is a valid criticism that this can proliferate data classes, but that depends on the application.
results = ActiveRecord::Base.connection.execute(sql)
Yes, both of these things can be solved through disciplined modularisation of ORM logic, but in my personal experience across multiple companies, most developers simply aren’t that disciplined and treat ORM code as any other application code, instead of treating it as the remotely executed database code that it actually is.
In my experience, writing raw SQL (through https://www.hugsql.org/ in my case), you are instead encouraged to think of them as separate and carefully consider the boundaries, which helps keep the queries and application logic modular and allows for more carefully crafted queries that minimise roundtrips and data shuffling.
Again, this has been my experience, across a number of companies. Perhaps your experience differs, in which case, I’m jealous.
B. if it were, that only argues that you don't use the ORM in the latter case.
You can have an ORM in your project and still fall back to plain old SQL if you like. It's not an "either-or" thing.
I wouldn't mind writing queries in SQL so I don't have to learn a new ORM for every language I use. However I like that ORMs unmarshal results for me in objects / tuples / maps of the language sparing me the work.
For complicated things I write SQL and possibly decode the results manually.
If you know a problem is going to be complicated, you know to use SQL.
If you don't know if a problem is going to be complicated, you can default to SQL and decide to use the ORM later if it's a good fit once you've got more problem details.
This is where I disagree. I dislike having a value which is the entity. The basic lesson from relational databases and later data oriented design is that you don't have an entity. All you have are aspects that are related.
All of these questions require deep knowledge of the inner workings of the ORM, when in SQL, this is a lot more straightforward.
The Django ORM takes take a whole of an hour to read, and after that you have 90% of the use cases covered. Considering that I've been using that framework for many years now, the overhead of that knowledge is irrelevant.
This is not a very compelling argument to use ORMs. It is saying "it makes easy things easier". This doesn't really buy you much value. The simple things are already simple. Bringing in a very large, complicated external dependency to make simple things simpler, is not a good idea.
>If you're loading data into objects then you're just creating your own personal ORM anyway.
By this definition, the Postgres driver I use for node is an ORM as it takes a row of string/type identification codes and turns them into javascript objects and javascript types. It isn't an ORM though, that is not what an ORM does. An ORM converts one paradigm into a completely different paradigm, which is why it fails and is a terrible idea.
Unless you’re programming in assembly, everything you do is being converted into another paradigm that is what every compiler and interpreter does.
How does an ORM prevent you from using any SQL features?
When you do the sorts of complicated things that are out of scope for an ORM, you do them in SQL. I don't see any incompatibility.
Back in the day when I was doing C, there were times when we just couldn’t get the speed we needed from the compiler. I wrote inline assembly. Does that mean C was unsuitable because it was incompatible with our performance requirements?
You can theoretically express any standard SQL query in LINQ even though outer joins can be obtuse at first. The translation may not be as optimal as hand written sql, but no compiler can translate code into assembly that would be as optimized as someone who could hand roll their own. We decided decades ago that high level languages were worth the trade off most of the time.
Not remotely true. ORM's are great at mapping views/procs, and you can use any feature of the db you want in a view or proc. Using an ORM does not mean not being able to your database to its fullest extent.
Django, just as an example, does a magnificent job with its built-in ORM. Millions of developers use it, and they are not all fools. A fool is someone who would set out to build a simple-to-intermediate CRUD web app by writing SQL.
> A fool is someone who would set out to build a simple-to-intermediate CRUD web app by writing SQL.
\u{1f644}
SQL works great for simple-to-intermediate CRUD web apps too.
No it makes simple things easy. Simple things in raw SQL are bloody complicated. Even just getting data, manipulating it, and saving it is at least twice as difficult in maintainability and lines of code than using an ORM.
> An ORM converts one paradigm into a completely different paradigm, which is why it fails and is a terrible idea.
I'm not sure where people get the idea that ORMs fail at their job. They really don't. They do it very well and we're all quite happy.
All the anti-ORM arguments here about how ORMs fail at completely different jobs other than mapping objects to RDBMS operations. Well duh. Nobody complains about cars that can't fly and planes that can't fit on the highway but when ORM can't cook bacon it doesn't fit the paradigm.
That has not been my own experience outside of the most trivial queries. Once any amount of complexity is introduced, I find that ORM-based queries often make it difficult to really see whats going on with indexes and locking, I can’t just paste a query (eg to use EXPLAIN) without finding it in a query log first and, unfortunately, in my experience its rare to find teams disciplined enough to not treat ORM code as if it were normal application code (ie don’t mix it into your application logic), so often end up with a few database/application roundtrips, doing filtering in the wrong place etc. Yes that last one isn’t technically the fault of the ORM, but when I see it again and again in real world code, I start to think that most developers don’t have the discipline to be careful with ORM code while when not using ORM’s and writing raw SQL outside of your applications code, you have no choice. Not the ORM’s fault, but still a symptom of using one that I’ve experienced in multiple teams.
Things don't usually start out bad, but they become bad only after you have a more complex codebase with many users (ie many queries). Its at this point that you need to know what a query is doing, yet its at this point that the query logs are full of queries, so finding the ones I want becomes hard. Putting a trace on the queries also becomes hard when you have a large codebase where the query logic is intermingled with application logic. I mentioned this in my comment.
You say it's my "refusal to want to change the way you work that is the issue", but I never chose to work that way. All of the codebases where I've had this issue were inherited: I was not the one to decide to work like this and I did not mix the query logic into the application logic. But that was my point: I've had the same experience across multiple teams in multiple companies, so blaming the developers seems like a cop out and not much of a solution. I'll happily adapt the way I work, but I can't force existing teams to change. Maybe discipline could fix it, but I have not experienced this discipline anywhere I've worked. But sure, its my fault somehow.
Since then I keep using ORMs (because mapping the things you pull from the db to actual objects is undoubtedly good) but I write my queries by hand.
I also dislike ORMs that, by default, when you try to access an object you forgot to pull from the db with the query you wrote, automatically generate another query to pull the data instead of erroring out.
I remember the very first time I had to do anything interactive on a web page. We had a long list of items in a table, and as a stopgap for adding search functionality we were going to sort the table by multiple columns.
Stack Overflow was still a twinkle it Atwood's eye. So I go googling about for stable sort implementations in Javascript and I find plenty. Except they all have the same problem. They were all slow as molasses. I should have taken it as a huge warning that none of these implementations used demos showed sorting of more than 10 items.
I needed to sort 100 items. 300 at the outside. Ultimately I had to go to source material and implement it myself.
Clearly there is some notion in the software community that market for ideas is inexhaustible. That the 'oxygen' in the room is infinite, and therefore I can interject whatever half-assed concept I had into it without costing anybody anything.
Virtually all of us, when we tackle a problem, try to do better than what is already on offer. But what if the thing on offer is truly, horrible? If my only goal is 'better' instead of 'good', then my alternative will be really bad. And in a field full of bad, who wants to be the person who introduces the 6th standard? The 8th?
Keep your dumb ideas to yourself, or put them in question form and ask people why
After someone posted Norvig's Sudoku solver, I was troubled by his statement about how many algorithms he'd have to implement so he just did brute force. So I took a whack at it, got fairly far down the deductive path before things got hard. But I'm not going to show it to everybody. The internet has been working on strategies for 10 years, and they've done way more algorithms than the ones I knew about. The best I've managed is to maybe simplify a couple rules, but I think one could argue that it's the bad definition of 'simple'. It can't handle as many cases, but I can explain it to anyone. Is that enough to add to the noise? Probably not.
It also feels to me like he's talking about a specific ORM - I know ActiveRecord has it's fair share of issues, but from what I know of AR usage and implementation, it either doesn't do what he's (legitimately!) complaining about or does do it in the way he's suggesting.
This is a common problem I've run into in strongly typed languages; can't "just have some data", have to have an object :/
https://www.newtonsoft.com/json vs https://github.com/rangerscience/maptionary
We confirmed what the DBAs said, the latency was only 34 seconds round trip and the database was pretty fast. I installed wireshark to see what was going on. It was querying a table with 80,000 entries, and then for each entry it would do another, "select ... limit 1" query to get the meta data on each record. This loop was abstracted away in an ORM and the third party had very few clients with tables of our size for that particular table.
When it was run on prem it wouldn't take too long, but when it was on cloud it would take 45 minutes. Insignificant amounts of time being spent thousands of times ends up being significant time spent, and this can creep up on you as more things move to be serverless / API driven. A bunch of money and time was spent on figuring out the source of the issue.
If you consider the software development process as a whole, the explicit SQL approach intrinsically guarantees additional scrutiny of the actual SQL statements. If you hide all of this behind an ORM, all that is seen during code review time is some beautiful POCO model with a few extra IEnumerables thrown on it. No one is paying attention to the man behind the curtain and the horrible join that was just created automagically.
Perhaps the answer is to just log and profile all the ORM generated SQL - Sure, but if you could look at the ORM's output (which in some cases is abusively large) and quickly determine if its good or not, why not just write it yourself and be 100% sure from the start?
Developers have been making n+1 mistakes with Raw SQL for years before ORMs became popular.
It doesn't matter if the "DBA or experienced developer" makes a select query that is better than the ORM. If this select query is inside a loop then all bets are off already.
ORMs usually have eager loading options...
I'm kind of curious about what data the select limit 1 queries were returning that the ORM couldn't get in a single query and had to go back per record. Can you shed any light on this?
ORMs have a degree of flexibility. It's possible to write n+1 queries accidentally, especially for developers who are new to the ORM. It's also often possible to address those issues. Sometimes trivially, sometimes not.
The other thing that comes to mind here is that maybe the query was hitting a non-covering index and that was triggering the lookups? In that case a covering index would have fixed the issue?
Curious about this one...
Though... since it sounds like the query was returning the entire table at once eager loading will cause issues of another variety at some point.
Either way, I'd chalk it up to bad design rather than bad tools.
The first pattern, common in frameworks like Django, is to cram the business logic into the ORM instance objects. This creates a tight coupling between the two separate concerns (business logic and data persistence), and will cause problems as soon as the two structures deviate from each other.
The second pattern is to keep the ORM as a purely data storage layer, which is much more flexible but commonly causes duplication of nearly identical structures in separate layers.
For the second, I don't see how skipping the ORM avoids that duplication. Presumably you still need to map your database fields to object attributes.
No one should do that.
The second pattern is also awful. Why would you do that.
This feels like good balance. I want to express my database schema via OO classes. It eases db migrations as the application grows if you use tools like Alembic (same developers as SQLAlchemy).
But use care to avoid SQL injection risk with template queries. SQLAlchemy makes this easy using .bindparams, as does .NET via SqlCommand.
Use an appropriate combination of ORMs and raw SQL.
Same. Hibernate with Spring Data JPA, for me, "just works". But it's not necessarily the right solution for every database interaction. But for a typical microservice which might have up to 20-25 discrete entities, and few if any crazy complex relationships, I find that I plug it in, extend CrudRepository, write some JPQL queries in @Query annotations here and there, and bob's yer uncle.
If you were writing a monolithic CRM system with 900 domain entities, super complex rules governing the inter-relations between them, and trying to write your analytics queries right into the app, then an ORM approach would probably fail miserably.
Simple inexperience. I'd bet most of those people are mid-level developers who have used ORMs enough to hit the rough edges but not enough, or with enough independent agency, to have worked through how to play to ORM's strengths while avoiding their weaknesses. People that were given a hammer and are just understanding that their hammer doesn't work very well to install bolts, and maybe don't have the authority to say maybe I should use a wrench or the flexibility to try this new crescent shaped idea and see if that works better.
You could level the same empty claim at people who like ORMs: I loved ORMs when I was a beginner but learned it's best to avoid them as I accumulated experience. So anyone who likes ORMs is merely in that beginner stage.
I have, at most, a couple dozen "complex" queries in this project.
Whereas I have an order of magnitude more queries that need to be composed from several different query criteria, a task for which SQL is very poorly optimized for and most ORMs excel at.
I have used my ORM for so long that writing a report in SQL or the ORM language is basically the same to me. Neither technology is something I would consider to be "hard", as most of the problems encountered in practice are well-trodden.
Nontheless, a simple ORM query is 20% the length of an equivalent SQL query. And I can compose them trivially. And then I use the model code for the _hard_ part: dealing with the rest of the business logic for template rendering, email sending, API interactions, and so on, for which SQL is completely useless.
Seriously using an ORM is just not hard. Especially if actually do code reviews with your junior team members, which you should always be doing.
And regardless, why should we develop things that only "experienced devs" can work with without blowing a hole in their face? Junior and intermediate devs are often in larger projects.
Thinking about projects I'm aware of.. Stack Overflow created an ORM regardless of if they call it "Micro". They created it and people use it. GitLab uses ActiveRecord.. Gogs and Gitea use ORMs that are a bit fringe TBH.. I actually can't think of a large project I've touched that went completely raw dog on the SQL.
I do believe there are instances where using an ORM, or at least all of an ORM, is not the best choice. But to come out and say they should always be avoided? It's interesting how many people are coming in here SO SURE about that position that is seems completely at odds with what the rest of the industry is, objectively, doing. And when pressed their insights into that opinion are so shallow a baby couldn't drown in them. Hmmm.
There are 500 tutorials of installing Feathers and making a CRUD app, but something as simple as combining results from two requests or doing a raw DB request seems to exist only deep in the docs (and more often than not, requires bonus functionality from an add-on).
I am familiar with managing databases and I can see benefits of ORMs, but I feel like "glue" technologies could benefit from deeper real-world examples that move beyond day 1 tutorials and get into translating existing code to their style. Half the time I am left digging through outdated github code to grasp the basics of how and where mid-level functionality can be used.
[0] https://github.com/kyleconroy/sqlc
It looks like it parses the schema to find type information (2 minute skim, so please forgive me if I got it wrong!) Hoe does it handle schema changes?
But there’s got to be some reason people keep embracing it, even if I’m not one of them.
Possible reasons, which you can rephrase as productivity virtues, if you are so inclined:
* I don’t spell well
* I don’t test much
* I don’t care how much time and bandwidth it uses
* I only know Java (or C#, or whatever)
Sounds despicable to me, but could be many small scale Enterprise projects, I guess.
> * I don’t spell well
I don't want to lose minutes or hours debugging a stupid typo.
> * I don’t test much
Writing meaningful tests with my object model without having to care about bizarre SQL semantics.
> * I don’t care how much time and bandwidth it uses
Because ORM will optimize the queries for me, cache results and perform lazy loading when possible.
> * I only know Java (or C#, or whatever)
Using the full power of expressive languages to work with my object models, keep things DRY and reuse code, and have support for different storage engines and sql flavors.
If they had just used basic sql it would be much easier to refactor.
Are ORMs leaky abstraction? Absolutely. Is this the reason to avoid them? Not even slightly. ORMs provide plenty of ergonomic benefits over SQL.
"Any sufficiently complicated program contains an ad-hoc, informally-specified, bug-ridden, slow implementation of half of a decent ORM."
ORMs are hard for a reason. Using an ORM doesn't mean you can't or shouldn't use plain SQL where the situation calls for it. You can mix and match perfectly fine.
[1] https://en.wikipedia.org/wiki/Greenspun%27s_tenth_rule Edit: typo.
"Although it may seem trite to say it, Object/Relational Mapping is the Vietnam of Computer Science. It represents a quagmire which starts well, gets more complicated as time passes, and before long entraps its users in a commitment that has no clear demarcation point, no clear win conditions, and no clear exit strategy."
[1] http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...
There was no good reason to be in Vietnam; even taking the stated rationale as a given, which many people did not, it was a concern many levels removed from the actual safety or functioning of American society.
ORMs in contrast achieve much more proximate goals — they solve a real problem and can measurably reduce the amount of code you have to write. It is tedious to write the same sort of SQL query over and over. Even if you prefer to use literal sql for the complex stuff (as I do), ORMs tend to be a significant win (in fewer LOC to write) on abstracting out basic queries.
The real value is to reduce the damage of the SQL language itself — the unnecessarily ordered clauses, the arbitrary inconsistencies in syntax, the worthless parser errors, the lack of any static typechecking — which cause so much code bloat and debug headaches.
There are two reasons to use the ORM: to not learn SQL, and to generate SQL.
The first reason is the commonly provides one, and what leads us into vietnam. The latter is why people try to avoid ORMs, yet find themselves back in vietnam.
What we really need is a less shitty version of SQL.
https://www.jooq.org
So instead of building up your sql string in a straightforward fashion, you need to have at minimum an abstraction that delays construction.
You get lead into vietnam as almost a direct result of SQL’s context-sensitive clauses.
Honestly, I think LINQ and entity framework successfully solved most ORM concerns
Anyone got tips on similar frameworks for other languages than java and for other dbs.
https://github.com/StackExchange/Dapper
In http://p3rl.org/DBIx::Class perl has had such a thing for over a decade now.
It makes me cry that nobody's ever adequately cloned it into other languages. Eventually I'll probably do so myself.
Hopefully that longer answer is a bit clearer than my first attempt.
I think the same. In my spared time I building a relational language (http://tablam.org ..accept more help!) starting even lower: Without a rdbms.
I work in the past with FoxPro, and was possible to build a full app with UI and reports and all stuff you can imagine with a database-oriented language. You code the UI on fox, query with fox, make triggers with fox, etc...
I'm looking in capture the same essence.
Most of the ORM interactions are well formatted code that don’t hide the datastore much. As long as you let the ORM map in objects and it’s performant, life is alright. When the ORM starts running the show there may be no coming back.
What I liked less was all the setup/config ceremony. Compared to ActiveRecord (the Ruby lib not necessarily the orm concept) I was using more LOC before I got to the part where I started saving time on simple queries. I realize this is because sqla uses the datamapper model. But for me thif sweet spot would be auto setup like AR with sql-like syntax of sqla.
Any of the D (Date and Darwen's, not Digital Mars’s) family of relational languages might fit the bill.
A lot of software design runs like this: (1) translate business patterns into a relational schema; (2) build interactions with that schema; and (3) as that gets harder, use SQL arcana and ORMs and views and stored procedures to squeeze out flexibility.
I worked like this for the first decade of my career. My systems got some use, and struggled along, but they are failed projects.
Repeatedly I had this feeling: the project is almost done, but there are some concurrency issues where I would not even know how to start addressing them.
The database-centric design made it impossible to work past that.
After much searching, I came to this: Stored State is a brittle and unwieldy thing, and you want as little of it in your life as possible. The more you have, the harder you have to work to get anything done. Databases are an institution of Stored State.
As an alternative, you can derive state from messages.
Bingo!
Unfortunately, if people won't even listen to Alan Kay on this stuff, who will they listen to?
Are we talking about distributed store situations where CAP theorem limits apply, or something else?
Why would you optimize for LOC rather than expressing the behavior you want well? In some situations sure—rapid prototyping—but that’s an odd assumption in a general case.
Yes, the object-relational impedance mismatch. It's the classic case of having a hammer (OOP) and trying to make everything look like a nail.
This generation has only ever used ORM's, and so to them those tools must solve some hard problem, they are so complex after all. SQL must be hard.
It turns out that SQL is actually much simpler than ORM's. The failure modes are much simpler, and the implementations more robust. Sure, writing the code can be tedious, but tedeious is not hard. Writing brainless code every once in a while gives you time to reflect on the design of your system, and think about the larger context.
Are they simpler when it comes to matching the data to the application's data structures? One advantage of ORMs is that they encourage this setup from the start.
That depends on the modelling approaches taken by the application developer and DB developer.
And this is an extremely common situation to find yourself in. Code bases which are of middle- to large-sized, and which are inevitably touched by various hands of various skill levels, tends towards complexity.
SQL is the worst source of unmaintainable and difficult to refactor code. It’s difficult to unit test. Error messages are more often than not inscrutable. (I’m looking at you, Oracle.) There is no idiomatic way to break up complex SQL into functions or classes. There’s no type checking. You don’t have the equivalent of Ruby Gems, Python’s pip, or Swift’s CocoaPods. IDE support is limited to syntax highlighting Compare this with something like Eclipse or IntelliJ where you can just Ctrl-click on something and go to the definition. Want to rename a public method in a statically typed language? Pretty easy. Want to rename a column in SQL? Yeah, good luck. Not impossible, but you don’t have any guarantees that something won’t break until runtime.
So yeah. ORM has its place, and modern ones work very well at abstracting away SQL’s weaknesses.
It had a canonical XML format that entities were defined in and code generation for data access layers, domain models, view models etc.
It actually worked better than I've seen the abuse I've seen developers put EF through. I found it nicer and simpler than times I've worked with Hibernate.
I'm not going to say it was perfect, but every time I think back on it it makes me want to revisit the idea of leveraging a bit of code generation or metaprogramming to be able to have a canonical definition of an entity transformed into concerns that deal with the given entity at different points in the application.
That part of it just really hit the sweet spot for me.
Think about it more like MyBatis meets Spring Data JPA. Define that entity in XML the run a code gen which gives you the CrudRepository class but also generates a controller that exposes an API with pretty good good ability to specify adhoc queries. Plus view models.
I think it worked because it both reasonably well designed and hyper opinionated.
https://github.com/db4j/db4j
I love the idea and it's been fun for me to make demos with, but I've gotten almost zero feedback on the API, and what I have gotten is "can you add a SQL frontend". And yet neither ORM nor SQL are loved
So the question (and I'm not suggesting that Db4j is the answer) is: "what's the API that would be most natural ?"
And that's only necessary when you're trying to manage your data in the application in an object-oriented way. And managing your data in an object-oriented way implies more than just the simple fact of defining classes to serve as data records. Those classes can be entirely equivalent to a struct in a procedural language or a record in a functional one. And I can't remember ever suffering from object/relational impedance mismatch when working in a procedural or functional language. Implying that the spot where you really start getting into trouble is when you trot out some distinctive feature of an object-oriented data model.
I submit that the original sin is treating instances of those data classes as if they are discrete entities that can serve as an application-side proxy for some other discrete entity that exists in the database, almost as if ODBC were just a more REST-flavored alternative to CORBA. Which is a thing that I've often been tempted to do in an object-oriented language, but never in a procedural or functional one.
Which isn't to say that I don't use anything to help with talking to databases in those other styles of language. It's just that I retain SQL as my query language (there are plenty of reasons to do it, none of which I'll bother to repeat here) and rely on a more Dapper-style library to handle unpacking the results into data structures. And I don't really consider those to be ORMs; they're just a special class of data mapping utility library.
So, in conclusion, I think that a more accurate stab would be "Any sufficiently hastily built, object-oriented, database-driven non-ORM program contains an ad-hoc, informally-specified, bug-ridden, slow implementation of half of a decent ORM."
(1) While treating a single SQL table as a distinct entity is a sin by your argument, I would argue that treating a set of tables as a distinct entity is not. I'm referring to what DDD calls an aggregate, e.g. a customer (address + email history + scoring + whatever tables), or an order (line items, shipping info, payment info, ...) Reading the comments here, I'm starting to wonder if some kind of holy grail or at least useful approach lies in there.
(2) The impedance mismatch has two ends, and one might as well argue that the problem is not mapping tabular data to objects, but mapping objects to tables. This might be an underrated benefit of what is usually called "NoSQL", dwarfed by the whole discussion around "schemaless". Unfortunately, I don't have any experience in that area, but it's something I have long planned on trying. Note that I'm not trying to say that NoSQL "solves the impedance mismatch" but rather that, in DDD's approach to solve persistence, NoSQL might actually be able to do what DDD wants (e.g. with respect to defining a "unit of consistency"), unlike tabular data.
The problem is not in treating entities in the database as entities. It's in treating instances of classes in the application's memory space as local proxies for those entities.
You're on much firmer ground if you understand them as simple pieces of information derived from the state of those entities as of some point in time. Both in terms of ending up with a more internally consistent and robust approach to data (by virtue of removing some temptation to preserve an illusion that this data can be presumed to always be complete and up-to-date), and in terms of not artificially cutting yourself off from most of the power of the relational model.
You're right that this tension is somewhat resolved by just switching to using an object store of some sort. (NoSQL is far too diverse of a subject to treat as a single unit.) But it's a very particular sort of resolution, because it's a sort of least common denominator approach where you just drag the data store down to the level of the programming model. It strikes me as akin to resolving the difficulties in maintaining complex software in Perl by resolving to never let anyone write anything more powerful than a shell or CGI script rather than by looking for a language that will better support you for the long haul. It can certainly be a fine and reasonable choice. The problem comes in when people don't fully realize that that's the choice they're making.
It sucks. Sometimes it sucks less if you map your queries as well, sometimes it sucks less if you stick to SQL and only map your results, sometimes it sucks so much you're better off with a no-sql solution.
But when using a relational database, ORM isn't optional.
Thank you!
This seems like one of those topics where people often feel the need to pick a side for some reason. I've often heard criticism to the effect of, "people only use ORMs because they don't know SQL. Learn SQL!" It's seemingly impossible to convince these people that ORMs are fantastic for reducing boilerplate code and they can coexist right next to raw SQL for problems gnarlier than "select all foo where bar equals baz."
Earlier in my career I made a point to deep dive into SQL. Long story short: I realized that there are a lot of very good reasons to limit the amount of raw SQL in your application that have nothing to do with familiarity.
SQL is just a bad language, and it’s unfortunate that we’re still stuck with it, basically unchanged, decades after its introduction.
For me, it’s almost as anachronistic as COBOL.
I spent a lot of time working in a department that wrote a lot of ad hoc Oracle SQL, for updates to a production system, and complex reports, and there was one guy that was ten times faster than everyone else, and also more likely to get his code correct, and so I paid attention to what he did.
He would break down a complex set of operations into simple individual queries generating temporary tables; he just wouldn't bother fighting with the optimizer, or trying to predict what it would do. So he not only wrote queries that ran quickly, but he wrote them quickly, and he got them correct quickly.
I would read Tom Kyte, exhorting people to use the full complexity of the language and the Oracle optimizer, but from my experience, it just was not the way to go. I wrote many page (or more) long queries that were things of beauty and then found that breaking them down into simple ones actually was usually much faster.
One fundamental thing that I don't think gurus understand, is that for the average grunt in a typical corporation, there is a separation of duties, such that you can't just go and change the things that a system administrator controls. So saying "your database is configured wrong" doesn't address normal life.
Although the pure relational model may be nice to think about, I find it really convenient to have a certain amount of sequential context, and PL/SQL always seemed to me to have a disgruntled relationship with SQL, so I've gradually tended towards Microsoft alternatives.