28 ms·

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

• wolco 7y ago
  Active record is a great pattern. Where things fall apart is when you want to do things like a subquery on a select field.
  • dsego 7y ago
    Like this? https://laravel.com/docs/6.x/eloquent#advanced-subqueries https://laravel.com/docs/6.x/eloquent#advanced-subqueries Seems more like a limitation of particular implementation than a fundamental problem with the pattern.
• humble_engineer 7y ago
  No thanks, ORMs work fine.
• lallysingh 7y ago
  Wholeheartedly agree. It's amazing how much work you can save by just having the database do it via a SQL query/view.
  • givehimagun 7y ago
    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.
    • threecreepio 7y ago
      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.
• philwelch 7y ago
  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?
  • blunte 7y ago
    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).
  • matthewmacleod 7y ago
    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.
    • philwelch 7y ago
      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.
  • mavelikara 7y ago
    > 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.
• hashkb 7y ago
  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".
  • bjoli 7y ago
    Can you elaborate? SQL queries compose just fine, it is just that most developers don't understand the relational part.
    • Roboprog 7y ago
      Predicate builder type stuff is probably the reference. Whether or not parallel arrays of template and value pairs to be anded together is better or worse is debatable.
    • laszlokorte 7y ago
      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?
      • ratww 7y ago
        SQL queries can be composed like in your example if you use subqueries.
      • deleted 7y ago
        [deleted]
      • 7y ago
• spullara 7y ago
  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. https://javarants.com/generate-jpa-or-gorm-classes-from-your-database-for-java-and-grails-234d223311a1 https://javarants.com/generate-jpa-or-gorm-classes-from-your...
  • blackflame7000 7y ago
    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.
• jackschultz 7y ago
  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.
  • thenewnewguy 7y ago
    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.
    • RangerScience 7y ago
      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.
      • look_lookatme 7y ago
        Good to see y'all Rails developers out there, enjoying work and getting things done.
        • RangerScience 7y ago
• wvenable 7y ago
  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).
  • celticmusic 7y ago
    A data mapper is not an ORM, it's a data mapper. It's a different access pattern. But I do agree that data mappers tend to be better.
    • moojd 7y ago
      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.
      • celticmusic 7y ago
        My comment wasn't meant to be a refutation of anything, merely an observation.
    • wvenable 7y ago
      https://medium.com/oceanize-geeks/the-active-record-and-data-mappers-of-orm-pattern-eefb8262b7bb https://medium.com/oceanize-geeks/the-active-record-and-data...
• trjordan 7y ago
  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?
  • mikeryan 7y ago
    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.
  • geophile 7y ago
    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.)
  • nitwit005 7y ago
    > If you don't pick an ORM to help manage this translation layer, then you'll end up re-implementing your own. 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.
  • fsloth 7y ago
    Code can think just fine in tables and columns as well.
  • TeMPOraL 7y ago
    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?
• dang 7y ago
  Thread from 2017: https://news.ycombinator.com/item?id=15949144 https://news.ycombinator.com/item?id=15949144 2016: https://news.ycombinator.com/item?id=11981045 https://news.ycombinator.com/item?id=11981045 Discussed at the time: https://news.ycombinator.com/item?id=8133835 https://news.ycombinator.com/item?id=8133835
  • Roboprog 7y ago
    I know, this is a recurring discussion. Both sides continue to distrust the other, though.
    • DoreenMichele 7y ago
      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.
      • Roboprog 7y ago
        Ah, thanks.
    • quickthrower2 7y ago
      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.
  • mindcrime 7y ago
    And another very recent discussion which treads on similar ground: https://news.ycombinator.com/item?id=20872571 https://news.ycombinator.com/item?id=20872571
• meesterdude 7y ago
  > 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.
  • Roboprog 7y ago
    Alas, so true. I tolerate an ORM at work. Secretly loathing it. Like much of industrial programming.
• daenz 7y ago
  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.
  • RangerScience 7y ago
    Which ORMs do you work with? 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.
  • wvenable 7y ago
    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.
    • daenz 7y ago
• RangerScience 7y ago
  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.
  • blackflame7000 7y ago
    I think he's talking about Hibernate.
    • RangerScience 7y ago
      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 :/ https://www.newtonsoft.com/json https://www.newtonsoft.com/json vs https://github.com/rangerscience/maptionary https://github.com/rangerscience/maptionary
• JimmyRuska 7y ago
  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.
  • Roboprog 7y ago
    Wait, this is displayed gray. It got downvoted???
    • knicholes 7y ago
      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.
      • bob1029 7y ago
        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?
• deleted 7y ago
  [deleted]
• psetq 7y ago
  These things don't have to be mutually exclusive. Know your tools and when to use them.
  • commandlinefan 7y ago
    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.
• BerislavLopac 7y ago
  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.
  • whitehouse3 7y ago
    Totally agree on the first pattern. 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.
  • dsego 7y ago
    The old Data Mapper vs Active Record debate.
  • ianamartin 7y ago
    That's only a pattern though, and it's not actually coupled to the ORM. It's a convention. It's a bad habit. No one should do that. The second pattern is also awful. Why would you do that.
• whitehouse3 7y ago
  > 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.
• james_s_tayler 7y ago
  What ORMs have taught me: Use an appropriate combination of ORMs and raw SQL.
• invokestatic 7y ago
  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.
  • mavelikara 7y ago
    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.
  • mindcrime 7y ago
    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.
  • olafure 7y ago
    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.
  • 7y ago
• conroy 7y ago
  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. [0] https://github.com/kyleconroy/sqlc https://github.com/kyleconroy/sqlc
  • brianm 7y ago
    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?
    • conroy 7y ago
      sqlc understands ALTER statements, so it can parse your migrations directory.
• Roboprog 7y ago
  Yep. I’m an ORM-hater, too. Feels like “leaky obstruction”, to me. 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.
  • dsego 7y ago
    That's a load of FUD. > * 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.
• karmicthreat 7y ago
  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.
• dsego 7y ago
  > if you need to know SQL, just use SQL Are ORMs leaky abstraction? Absolutely. Is this the reason to avoid them? Not even slightly. ORMs provide plenty of ergonomic benefits over SQL.
• foobar_ 7y ago
  Time and time again you will poop projects promise utopia but fail miserably.
• 725686 7y ago
  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. [1] https://en.wikipedia.org/wiki/Greenspun%27s_tenth_rule https://en.wikipedia.org/wiki/Greenspun%27s_tenth_rule Edit: typo.
  • j88439h84 7y ago
    Very good point. I'll need to remember this.
  • geodel 7y ago
    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." [1] http://blogs.tedneward.com/post/the-vietnam-of-computer-science/ http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...
    • mapgrep 7y ago
      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.
• bob1029 7y ago
  Swapping from EF6 to Dapper was one of the best choices we ever made with our project stack. It is so relieving to be able to hand-tune queries and transactions now. Initially, we were sold on the apparent simplicity of EF6, but as with many things there is a cost for an abstraction like this. In our case, the performance penalties and opaqueness were dealbreakers after a while. We saw an average speedup of 10x on all SQL operations after rewriting using direct SQL with Dapper handling mapping of result sets into models. Writing your own SQL migrations is actually incredibly straightforward if you just think for a few minutes about how you would do it if ORMs didn't exist. Many database systems have ways to store metadata like schema versions outside the scope of any table structure, so you can leverage these really easily in your scripts/migration logic. EF6 uses an explicit migration table which I was never really a huge fan of. One thing we did do that dramatically eased the pain of writing SQL was to use JSON serialization for containing most of our complex, rapidly-shifting business models, and storing those alongside a metadata row for each instance. Our project would be absolutely infeasible for us today if it weren't for this one little trick. Deserializing a JSON blob to/from a column into/from a model containing 1000+ properties in complex nested hierarchies is infinitely faster than trying to build up a query that would accomplish the same if explicit database columns existed for each property across the many tables.
  • hdfbdtbcdg 7y ago
    I am seriously glad I don't work on this project!
    • bob1029 7y ago
      Care to elaborate on which aspect noted above is adverse from your perspective? I would be happy to provide more context and explain in more detail some of the reasoning involved in our decisions.
      • hdfbdtbcdg 7y ago
        > Deserializing a JSON blob to/from a column into/from a model containing 1000+ properties in complex nested hierarchies > complex, rapidly-shifting business models Your business logic classes have 1000+ properties. And you plan to not migrate them when the schema changes but leave many instances with old versions of the schema sitting in the datastore. Your application logic is going to get nasty!
• sergiotapia 7y ago
  Just use a mixture of both, why the frothing at the mouth from either side?
  • ratww 7y ago
    Yeah, I don't understand why developers feel they should use one over the other in every single case. I guess there's a certain appeal to only using nails or only using screws, but at some point it doesn't make sense to compromise the quality of your code over ideological purity.
• acd 7y ago
  Will tend to agree with the author. Initially ORMs can save time when developing as you get an easy mapping between objects and the database. However in practise ORM tends to give you quite horrible JOINs that quite frankly are hard to understand for humans. Further more I think that ORM can lead to a bad practice in the sense that you do not need to think about your data layout first. But for database performance data layout is of utter most importance. One need to have data in lay out in a form that makes the application run fast. ORMs does not necessarily provide that. One need to normalize the database. ORM save you time during initial development but you pay later in the maintenance phase when what is complex queries that humans may not understand are hard to optimize for performance. Database normalization https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Third_normal_form https://en.wikipedia.org/wiki/Third_normal_form
  • Daishiman 7y ago
    This is completely false. The joins I do in the ORM are completely transparent to me as a developer and there is no mismatch whatsoever. The data layout and data migrations are well described. And I have, at most, a half dozen difficult queries which require serious optimization, and that optimization isn't defined by the query but by the indexing and storage strategy for the tables in question.
    • pknopf 7y ago
      Take a look at these issues: https://github.com/aspnet/EntityFrameworkCore/issues https://github.com/aspnet/EntityFrameworkCore/issues How many of these are a result of of features that have nothing to do with getting data in and out of the database. Get bare metal. Anything caked on adds friction. That doesn't mean "write raw SQL". That means I want just enough of an ORM to give me type-safety for my queries and updates. Leave the rest at the door.
    • jtdev 7y ago
      > The joins I do in the ORM are completely transparent to me as a developer and there is no mismatch whatsoever. Care to provide any examples with comparisons to ANSI SQL or any major SQL platform? *take note that “SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987.” the same cannot be said for any ORM.
• floki999 7y ago
  Agree with the article - always found ORM’s over-engineered and too opinionated for my liking. I’ll stick to SQL and keep total control thanks.
• elamje 7y ago
  What are some good rule-of-thumbs for when to use ORM vs SQL? I have worked with SQLAlchemy and Entity Framework, before and like them, but haven’t been able to find that magical demarcation line for when to go raw SQL. Does anyone have basic rules they use for determining this? Applicable to MVP or enterprise level products
  • nouveaux 7y ago
    The more joins or when you need more performance, drop into SQL. SQLAlchemy's expression language is a good middle ground. I will start there if I need to do more than a couple of joins. As some people have noted here, I think the biggest problem is that people who only know ORMs will have trouble because their understanding of the database will be limited by their lack of SQL knowledge.
• xienze 7y ago
  I feel like there should be a better term to distinguish an ORM framework from the kind of ORM that pretty much every competent developer uses (a hand-rolled one). These “you don’t need an ORM!!!” posts always annoy me because even if you don’t think you’re using an ORM, you probably are. You aren’t mixing raw SQL statements into your business logic, are you? Probably not. You’re probably writing wrapper classes that “map” the “relational” data into the “objects” that your business logic uses, otherwise known as an ORM. But there’s a big difference between that and an ORM framework that generates SQL. Realistically speaking, you _do_ need an ORM in all cases, but not necessarily an ORM framework.
• joelS 7y ago
  Surprised to see no one has mentioned query builders (like http://knexjs.org http://knexjs.org - no affiliation). All the niceties of not writing raw queries but no abstraction leaks... Although I have found for anything more interesting (recursive queries, etc) there is no escaping raw queries and every developer needs to bite the bullet and learn SQL.
  • bob1029 7y ago
    I see query builders as a learning tool for fresh developers, and as a job aid for business analysts. For some this can be the final destination, but for any developer I would push hard to get them writing SQL by hand ASAP. A few weeks of suffering through DIY SQL is really the only way to fundamentally understand how the database is working for (or against) you. Once you learn it, it really does become like a second language. You can fade in and out of proficiency based on recency of exposure, but it is mostly like riding a bicycle. One other advantage is you can take it across any SQL platform without a second thought. Many ORMs have provider-specific compatibility woes to contend with.
    • syntheticcdo 7y ago
      I disagree. Knex isn't an abstraction over SQL itself, as much as an abstraction over the tedious process of writing literal strings, which let's the developer treat SQL queries as code. My process is generally - write SQL to find the best query, then convert the SQL to Knex for integration. As a simple example, say you are making a simple query to the db, but in some edge cases you need another column to be selected. Knex lets you do this: let query = KNEX("table").select("col_1") if(weirdCondition) { query = query.select("col_2") } const rows = await query Which naturally compiles into either SELECT col_1 FROM table or SELECT col_1, col_2 FROM table
    • celticmusic 7y ago
      query builders are useful for building queries dynamically.
• deleted 7y ago
  [deleted]
• kuon 7y ago
  I mostly agree. But lately I've been using Ecto, and I like the approach. It's mostly an SQL generator. So far it's the sanest approach to ORM I've used.
  • honkycat 7y ago
    Agreed. Awesome abstraction that lets you build on top of it while still using low level SQL when warranted
  • deleted 7y ago
    [deleted]
• bartread 7y ago
  There's a sensible middle ground here, although I agree with the thrust of the message because using an ORM doesn't obviate the need to learn SQL - something I think a lot of developers forget. The other extreme from using an ORM "for everything" is using SQL "for everything", either via loads of handwritten ad hoc SQL, or stored procedures, UDFs, views, or a mix of all of these. This is just a different nightmare. And don't be fooled: it really is still a nightmare. A sensible approach blends use of an ORM with handwritten SQL where needed. In fact most ORMs will allow you to do things like build collections of objects from custom SQL anyway, so there's really no need to shy away from it. One other thing I'd say: I wouldn't necessarily trust my ORM to adequately design my database for me via a code first approach. It's more work but thinking about the data model and explicitly designing the database often yields better results, and you have more control. Code first is OK for simple stuff, but often even simple stuff becomes complex over time so I tend to shy away from it.
  • pknopf 7y ago
    > A sensible approach blends use of an ORM with handwritten SQL where needed. In fact most ORMs will allow you to do things like build collections of objects from custom SQL anyway, so there's really no need to shy away from it. This is really where it's at. Seriously people. Nobody should be writing raw SQL. Give me just enough of an ORM/abstraction to give me type-safety, leave the rest at the door.
    • jtdev 7y ago
      > Nobody should be writing raw SQL. What?!? That’s an absolutely foolish and naive assertion. Please don’t give any of your fellow junior devs that “advice”...
      • pknopf 7y ago
        Are you advocating that I put a bunch of magic strings in my codebase? That would be a maintenance nightmare. Unless you think I'm suggesting that no devs should ever write SQL at all in their career? Which isn't the case.
• EamonnMR 7y ago
  ORMs can be nice if you're using the database for a single application in a very conventional CRUD pattern, but that's about it. And that's if you have the luxury of building a new DB to go with your new App. If you find yourself doing anything interesting (ie not just CRUDing single rows by ID) you should not only learn SQL, you really need to become familiar with databases. The promise of ORMs (at least when I first encountered them) was "you're not going to have to do SQL" but in reality, you will.
• fabiosussetto 7y ago
  I think there's often an underlying confusion between different tools and what they are supposed to do. An ORM - as the acronym says - is helpful to map database records to objects in the system. The meaning of the acronym already says that an ORM is not really designed for scenarios like aggregations and reporting. Within those contexts, you don't normally reason in terms of list of "objects" and "relationships" between them. A "SQL builder" gives you a nice programming interface to build and manipulate SQL statements. Manually building complicated SQL strings is tedious, error prone and it makes it hard to reuse the same queries. With a SQL builder instead you can easily add dynamic conditions, joins etc, based on the logic of your application. Think of building a filterable Rest API that needs to support custom fields and operators passed through the URL querystring: concatenating strings would be hard to scale in terms of complexity. Some people prefers to use templates instead of SQL builder to add conditions, dynamic values, select fields etc. I personally find that this approach is like a crippled version of a proper SQL builder interface. I prefer to use the expressiveness of a real programming language instead of some (awkward?) template engine syntax. I think the confusion between these two different tools is caused by the fact that in some popular frameworks as Django or Rails you just get to use the ORM, even if behind the scenes the ORM uses some internal query builder. Other ORMs like SQLAlchemy instead gives you both tools. You can indeed use SQLAlchemy as a ORM and you can also use it directly as a SQL builder when the ORM abstraction doesn't really work. Normally, if someone tells me that it's better to write SQL queries by concatenating strings, I'd ask them how they'd build a webpage that filters products in the catalog with a series of filters specified by the user (by price, by title, by reviews, sorting, etc.). Try and build that concatenating raw SQL bits, without making a huge mess. Also, the "just learn SQL" may apply to ORMs, but certainly not to a SQL builder.
  • breatheoften 7y ago
    Query builders can be so good at making it easy to work with the database ... the popularity of ORMs over query builders is a really big collective reasoning failure in my opinion. With a good query builder in hand - it is very unclear to me why anyone would ever want to use an orm.
    • 7y ago
• brlewis 7y ago
  The article gives specific examples of difficulties using an ORM. It would be nice if people advocating ORMs here gave specific examples of how to overcome them.
• time0ut 7y ago
  I've found that you need to first understand basic SQL very well. Then once you've mastered the ORM, it can dramatically improve your productivity. But you need to understand both to reason about what you are doing. ORMs have their place. Simple CRUD microservice? An ORM can help tremendously. Complex reporting system? Probably not the right tool. Be careful though. I've run into issues where once you're scaled way up and need the ORM to get out of the way, it can be a beast to detangle if you weren't disciplined.
• ineedasername 7y ago
  I work a lot with SQL, not at all ORM's. (I'm on the data extraction, modeling, analysis side). So what's the counter argument here, where to ORM's excel?
  • ojglees 7y ago
    The counter-argument is that for a lot of applications you have a bunch of tables and a bunch of objects and there's a lot of repetitive, boring code to write just to get simple CRUD working. The hobby Java ORM tool I wrote (http://hrorm.org http://hrorm.org) only does simple CRUD. Everything else it leaves to you using JDBC and SQL, since the problems being discussed here are real. But for managing persistence of simple object models, I think ORMs are a big time saver.
• geebee 7y ago
  This article was well balanced. I find title just a bit misleading, since "What ORMs have taught me: just learn SQL", to me, carries a slightly different message than "What ORMs have taught me: learn SQL". The second sentence is a little more compatible with the first line, "they can be used to nicely augment working with SQL in a program, but they should not replace it." That's certainly how I use them. ORMs can save you a lot of irritating typing where it comes to insert and update statements. Aside from that, I write a lot of raw SQL. I have seen what I would describe as ORM-induced, yaml-induced database damage. This isn't meant as a criticism of these tools per se, they're perfectly compatible with a well designed app. But I have noticed that people sometimes create databases that are useful only in the context of their configuration-file/ORM heavy app. Essentially, the programmers conceive of their data as a set of objects, and they use the config file to store global constants and the ORM to persist files, almost as if they're pickling and retrieving objects back into the system. The result is a database that can't really be queried with SQL, more or less useless outside the context of the application. I firmly agree with developers who maintain that information will outlive an application, a database will outlive the software that was originally designed to use it (perhaps in parallel with it). I think a SQL database should be useful all on its own as a data source. If you got rid of the app, you'd lose a lot of operations on that data, a lot of UI, a lot of valuable things, but you'd be able to get at and use your data. If that's not the case, I'd seriously reconsider the design. Kind of hard to do that without understanding SQL, so yeah, definitely learn it.
• xxxpupugo 7y ago
  Can't we all agree ORMs are just leaky wrappers of SQL? Almost all of them need a backdrop workaround to issue raw SQL to the backend DB to avoid being a blocker at some point.
• residentfoam 7y ago
  I used ORMs JPA/Hibernate in several projects/teams and the outcome is always the same: things always get messy and overcomplicated, few reasons : - they push developers to design super-normalized db schemas that look beautiful on paper but are horrible in practice - the average developer has a very superficial knowledge of how ORMs work and this often leads to bad code/performance - soon or later you will find yourself fighting the "framework" because what you are trying to do does not fit their model (e.g: upsert) In my experience, this whole idea of abstracting from the DB is faulty at its root. You want your code to be close to the DB so that you can use all the greatest and latest functionalities without waiting for the framework X to support it. I have found that JOOQ or simply Spring JdbcTemplate in most cases are more than enough.
  • Glyptodon 7y ago
    First your tree needs an apple, then it needs a pear, and then a fig, and after a year or two of grafting other things on you end up wishing you'd just created a cornucopia with a bunch of columns instead of joining a endless fruit because two thirds of the time you need a mix of the fruit anyway. I'm definitely guilty of over-normalizing at least some of the time, but I'm less convinced that it's related to ORMs so much as it is to frequently having to build things without knowing how related they'll end up being in the future.
  • kjeetgill 7y ago
    Agreed! I've found JOOQ to be excellent. Generally ORMs try to abstract away the database entirely. This is mostly fine for CRUD stuff where you really just want to persistently stash away something basic off host. If you could write perfect uncrashing programs you'd probably just keep them in memory. As soon as you need to find things, especially based on their relationships, you'll need to be aware of what indexes you have available at least. Eventually you'll want to have control of the precise query JOOQ lets you compose queries programmatically without really hindering you from producing any query you want but not forcing you to glue strings together either.
• gfiorav 7y ago
  I knew sql before using ORMs. It can never substitute that. What it can do is abstract most common operations to do with your dB, and also help with mocking some tests. I really don’t see the point in these articles. It’s not supposed to save you from learning SQL
• iamleppert 7y ago
  I too have gone back to SQL after working with ORMs for 10+ years. Having worked with them on a wide range of projects and teams, I can say, without any reservation, they are not worth it. Not worth it for the small projects nor the large projects. They significantly complicate the development workflow and add another layer of (often times, cumbersome) abstraction between the user and the data. If you encounter any issues (and better pray you don't), expect to spend hours stepping through reams of highly abstracted byzantine code, all the time feeling guilty when you just want to open a database connection and send the SQL string you developed and tested in minutes.
  • jillesvangurp 7y ago
    I've ripped out broken ORM on multiple projects with over-engineered domain models designed by people with no apparent knowledge of how to do a proper database design. This is the key problem with ORM. It leads to lots of unnecessary joins just so you can pretend databases do inheritance or all those tiny objects you will never query on need dedicated tables with indexed columns. It's stupid. It's also stupidly slow, fragile, and hard to maintain such systems. I've been on a project where we had 20+ tables. After I made the point that the sole purpose of this database was producing json documents through expensive joins that were indexed and searched in Elasticsearch (i.e. this was a simple document db), we simplified it to a handful of tables with basically an id and a json blob; got rid of most of the joins and vastly simplified the process of updating all this with simple transactions and indexing this to elasticsearch with a minimum of joins and selects. We also ripped out an extremely hard to maintain admin tool that was so tightly coupled to the database that any change to the domain made it more complicated and hard to use because the full madness of the database complexity basically leaked through in the UI. ORMs don't have to be a problem but they nudge people into doing very sub optimal things. When the domain is simple, the interaction with the database should be simple as well. We're talking a handful of selects and joins and simple insert/update/delete statements for CRUD operations. Writing this manually is tedious but something you do only once on a project. With modern frameworks, you don't end up with more lines of code than you'd generate with an orm. All those silly annotations you litter all over the place to say "this field is also a column" or "this class is really a table" get condensed in nice SQL one liners that are easy to write, test, and maintain.
• breatheoften 7y ago
  Query builders are awesome. Orms really really aren’t ...
• tracker1 7y ago
  On a recent project, it was a weird inversion in terms of access... in order to keep the middle tier thin, and meet requirements that all data access happen through stored procedures... we pretty much standardized an interface with one input parameter (@json) and two output parameters (@result, @errorResult). In the end, all input/output was JSON and the database handled all data internally. I don't really like it much, but it did make the API service super thin, and the "database guys" were happy to do it all in SQL. Of course, testing that beast isn't so fun, and there be dragons... In the end, I tend to favor creating APIs with scripted languages that require less translation to work with the database side, and structure responses to match the expected data layouts for the API side. With node, I usually create/use a simple abstraction... var result = await db.query`SELECT ... WHERE foo=${bar}`; or var result = await db.exec('sprocname', {...}); In either case, not really a need for a formal ORM here.
  • Nican 7y ago
    It was not the first time I heard the requirement about "all data access happen through stored procedures", and I find it ludicrous. Does anyone know how such a paradigm came to exist? What problem is this solving?
    • qaq 7y ago
      Security, decoupling calling code from db schema
      • tracker1 7y ago
        Don't API services do that?
        • qaq 7y ago
          How do they do that in the context of ORM vs sps :)?
          • tracker1 7y ago
• codr7 7y ago
  Me thinks someone needs to have a go at maintaining a 2Mloc accounting package built using only embedded SQL statements and stored procedures, including migrating the whole mess between major database vendors. I guess one advantage is you have to learn, it but I really prefer some kind of ORM for more mundane repetitive CRUD. More to get a structured (ha!) interface between the database and the application than for the convenience. I do wish most of them would stop insisting on putting the cart in front of the horse and make code the primary representation.
  • ianamartin 7y ago
    I deal with that. Millions of lines of SQL stored procs. It's awful, but not anything an ORM could help with.
    • codr7 7y ago
      Not all of it, but I'm sure most statements follow some kind of CRUD pattern. Getting that out of the way is exactly what an ORM can help with.
• qaq 7y ago
  One of the problems with many ORMs is they are dumbed down and try to support multiple RDBMs.
• deleted 7y ago
  [deleted]
• elchief 7y ago
  An ORM is pretty darn useful for CRUD Use a query tool like QueryDSL or Jooq for querying. It's not surprising that you fail if you don't use the right tool for the job
• ianamartin 7y ago
  ORMs were never about not using or not knowing SQL. They are a convenience for people who already do. If you don't know SQL, don't interact with an SQL database.
• Mountain_Skies 7y ago
  What happened to object databases? They seemed to have a brief moment in the spotlight during the 90s but haven't made much noise since. Bad product or just too much inertia with relational databases?
• redact207 7y ago
  I've found that DDD and a read/write separation of concerns simplifies things greatly. On the write side, the ORM just returns an aggregate, usually based on the PK of the root. Thats's trivial for any ORM. On the read side, simple queries can be modelled with ORM syntax if you're just trying to fetch a graph of existing objects. Complex queries can be returned with raw SQL that map to custom read models. I tend to wrap both styles in integration tests that ensure the query logic doesn't change, and that the actual mappings don't break. Both ORMs and SQL have their usages, and they're not mutually exclusive.
• astine 7y ago
  An ORM is not a substitute for learning SQL. It's just a nice tool for more tightly integrating your database and your application code. An ORM can make you like much easier, but if you don't know SQL or how a relational database works, your ORM isn't going to save you.
• lemmsjid 7y ago
  I've followed ORM like/hate flamewars as long as I've been in the industry, and I think I'll be following them for as long as I continue. It's really an interesting and ultimately irresolvable tug of war between abstraction, automation, and a number of other issues. I think people who have used ORMs a long time do not see them as a SQL replacement or as a total database abstraction layer, but as an automation tool for CRUD operations, with some capabilities of doing interesting things with querying, and potentially a methodology for managing database schema and migrations (depending on the tool). At their best, ORMs can tremendously reduce one's requirements for unit tests in particular areas because they have enough structural metadata to typecheck (automatically or compiler time) all the way down to forms in interfaces. BUT, without a doubt, ORMs are indeed a tremendously leaky abstraction. That said, I would argue that every data store interface is, in that sense, a leaky abstraction. No matter the datastore you're using, be it RDBMS or a specialized NoSQL / search store, you need to learn the hows and whys of how it is structured. Not only should a professional learn SQL, but they should get at least a layperson's knowledge of how the database underneath the SQL works. However, the argument still stands, because when debugging a complex ORM query, you are debugging how it puts SQL together, and then you need to debug what the generated SQL is doing. So you're now multiple layers away from the actual thing you're managing. Hence the 'final form' of my day-to-day ORM stance: I like to have an ORM around because of the massive automation around entity manipulation, but I think of the ORM in terms of the database and how the database should be structured, rather than as a generic domain model that happens to be mapped to some behind-the-scenes database. Furthermore, I believe it is quite valid to drop down to SQL for the very complicated stuff, especially if you want to trust to the query planning of the database. Once you do, you've broken the total safety of the abstraction, hence my ultimately seeing ORMs as automation tools rather than abstraction interfaces. I have no blame for people who refuse to utilize them, though I'd argue in a design meeting for their use, and hope I wouldn't have to take on all the CRUD queries if I lost the argument, because I'd start writing code to generate them, which then would become a terribly under-engineered faux-ORM :).
• kbenson 7y ago
  Advocating for the use of SQL over an ORM in every case is like advocating for the use of Assembly over C in every case. In both cases, one is a higher level abstraction over the lower level capabilities, which can provide a quite large gain in usability and ability to easily understand what is going on at the level you are working at, for the loss of hand optimizing at a low level to get just what you want in every case. Similarly to with Assembly and C, you can often drop to the lower level as needed for speed or other very specific needs. In both cases a good understanding of the lower level language will help you both know when it's appropriate to drop to a lower level for performance or for a special feature, and when it doesn't matter because the ops/SQL generated is rear optimal anyways or the gains are almost definitely less than the problems caused from a maintainability perspective. I'm perfectly happy to use an ORM for 95% of my DB needs. Just the query builders that generally ship with them are worth the price of their inclusion IMO (at least for the good ones), as it can greatly simplify queries that are variable based on different parameters you may have per run.
  • AdrianB1 7y ago
    I live in a world where tables have 10^12 rows and all the queries need to be manually optimized. In my world, an ORM is the most useless thing in the world. Different people, different needs. However, in all my projects SQL was more useful than ORM, except where the data models were so simple my mom could write the code for it.
    • scarface74 7y ago
      If you have one table that you’re doing a query on, what types of optimizations are you doing to the query as opposed to the database - or even if you’re doing joins?
      • AdrianB1 7y ago
        In general for one table an index helps, but we have almost no queries on a single table. The Query Profiler is telling what part of the query takes the most (CPU, disk reads) and that is the starting point. Tuning a single query can take hours, depending how slow it is and how often it will run. There is some information on Internet on query optimizations, look it up. There are also server side optimizations, specific to the RDBMS you use and the flexibility it has.
• neilobremski 7y ago
  Whether or not you love/hate ORM, I think the advice at the very end is still applicable: "If you’re using an RDBMS, bite the bullet and learn SQL." This knowledge will help you write SQL if that's what you're doing or debug the SQL that the ORM writes for you. Either way ... learning SQL is a good thing!
• honkycat 7y ago
  One of the many reasons I love Elixir Ecto: it is a pretty small abstraction on top of SQL. Gets out of your way and let's you write SQL in elixir
  • cultofmetatron 7y ago
    OMG YES, I LOVEE Ecto!. it lets you create beautiful sql like declarations ``` payment_types = from(s in Ecto.assoc(order_cycle, :splits)) |> join(:left, [s], p in assoc(s, :payments)) |> select([s, p], %{ type: p.type, card_type: p.card_type }) |> Repo.all() ``` its the first database library that makes queries easy to compose together. On top of that transactions are a snap to put together thanks to Ecto.Multi
• _bxg1 7y ago
  In my opinion this whole problem is one of the strongest condemnations of OOP. Even if OOP really were a great way to model your data, to express relationships and property types and such (setting aside all the questionability of that claim), it's all just going to end up in a database anyway. Unless you're using a denormalized database like Mongo, or the bulk of your application state is non-persistent, your perfectly thought-out OOP model is at best going to be a terrible approximation of the real data model and you're much better off using a language/paradigm that lets you just treat data as data.
  • Daishiman 7y ago
    What makes you think that the DB will always by the dominant data store for a business object? Business objects need a lot more, from rendering, processing, email sending, notifications, etc. All those things are well approximated by an object and poorly approximated by SQL.
    • _bxg1 7y ago
      Everything you can build with OOP can be done with FP and vice-versa. So the question is which one's strengths you'll most benefit from. OOP's strengths surround modeling nested object structures and encapsulating state and logic within opaque containers. If you're primarily writing data to/reading data from a relational database, everything gets flattened and exposed. So you have to ask yourself what you're getting from it at that point. Rendering and processing are well-suited to FP. "Email sending" and "notifications" are vague, but I see nothing about SQL + FP that makes those harder.
  • wvenable 7y ago
    > Even if OOP really were a great way to model your data, to express relationships and property types and such, it's all just going to end up in a database anyway. Of course. But when you operate on it. When you end user is typing into a screen. When you're validating the contents. You're doing in the application, not in the database. RAM is where the data lives when it's being acted on. It's like saying your Word processor should have no internal data structures different from how the file stored on disk. OOP is a great way to model data sotred in RAM and operated on. And an ORM is a great tool for persisting that structure to disk as needed.
• orware 7y ago
  I've looked at ORMS mostly on the periphery for years and years now, but haven't really been able to use them in our environment and this might be similar to the situation for others as well. One thing I noticed early on, is that even using a query builder complicated things a bit as well since it meant that the original SQL string was then broken up into multiple method calls to construct the SQL string. Since the full query wasn't built until the very end, this meant that a helper line needed to be added into the code to get that full SQL query if any issues were found down the road and then taken over into our program of choice (in this case, SQL Developer, since we're dealing with Oracle queries in most cases) and running our additional tests over there. Our campus ERP is pretty complicated table-wise, so our queries (developed either by myself or by our systems analysts) tend to be fairly complex, requiring multiple joins and other complex logic that I feel would have a very difficult time being translated into an ORM format. A query builder is still somewhat usable, but for the most part I just stick to mostly straight up SQL queries and make use of prepared statements to help avoid SQL injection and keep life simple so it's easier to move back and forth between the application side and testing the query on the database side :-). On a side note, I'm not sure if it's mentioned here in the discussion (and it might be less of an issue now than in years past) but I have noted some ORM usage doesn't always choose the most efficient mechanism for things (e.g. pulling results using small, but relatively expensive multiple SQL queries rather than being contextually savvy enough to know that a set-based query would be better suited to retrieve the entire set of results at once). Overall though, working with SQL and coming up with solutions for things is probably one of the funner aspects of my current position and while using an ORM has sounded like fun in the past, it just hasn't seemed like it's the best fit for our particular workflow/environment.
• ummonk 7y ago
  You have to distinguish between heavyweight ORMs that impose their own data model and lightweight ORMs which just provide transparent query builders and static typing for query calls from your given programming language. The former is frustrating to use in my experience but the latter tends to make SQL easier to work with without taking away any of the power and expressiveness of SQL.
• Koshkin 7y ago
  I’ve seen organizations that had ORM groups. You had to file a formal request when you needed to map a column.
• jkdufair 7y ago
  I would have generally agreed with this sentiment until we took a leap of faith and chose Entity Framework 4-5 years ago. That, coupled with the power of LINQ has allowed us to do things we simply wouldn’t be able to do otherwise. Pass IQueryables around without realizing them right away. This leads to functional, composable queries. And the queries we generate would simply not be possible to be written by hand. If we need to write SQL, we can and do. But with good design and good indexes, we rarely, if ever, do. I was skeptical, but EF is a tool we have not regretted.
  • ratww 7y ago
    Entity Framework and LINQ are amazing feats of engineering. Its query builder is super transparent, consistent and flexible, thanks to LINQ, but still generates very performant queries.
• danschumann 7y ago
  I pretty much agree... I roll my own ORM usually, though... mostly for niceties like firstOrCreate, and such. I always keep a sense of the actual MySQL being ran behind the scenes.
• Koshkin 7y ago
  ORM or not ORM, LINQ to SQL is amazing!
• amedvednikov 7y ago
  I agree. Unfortunately each database has its own SQL dialect with small differences, so migrating to a different database becomes a problem when using plain SQL. You also lose compile-time checks. I'm trying to combine the best of the two approaches in the V language. It has a built-in ORM that uses SQL-like syntax: uk_customers := db.select from Customer where country == 'uk' && nr_orders > 0 println(uk_customers.len) for customer in uk_customers { println('id: $customer.id; name: $customer.name') } https://vlang.io/docs#orm https://vlang.io/docs#orm
  • echelon 7y ago
    The solution is to use something closer to a compile-time SQL or DSL, such as jOOQ or SqlAlchemy (sans the ORM pieces). These are terrific technologies that improve upon SQLs weaknesses and achieve some level of portability.
• Koshkin 7y ago
  The thang is, what is an object in the context of the relational model has been generally grossly misunderstood. For a detailed analysis, see C. J. Date’s The Third Manifesto.
• vladsanchez 7y ago
  I said it once, I'll say it forever: ORMs are people who don't know SQL.
• stickfigure 7y ago
  Beginning programmer: ORMs let me write code without learning SQL! Intermediate programmer: ORMs just get in the way! SQL isn't that hard after all. Advanced programmer: I write a lot of SQL, but I use ORMs to cut out most of the boilerplate.
  • hinkley 7y ago
    The phrase you may be searching for is 'informed consumer'. The world is full of tools that experts use because they understand what the tool is doing, and why this is a good thing... most of the time. Having the tool is a poor substitute for the knowledge that led you to use the tool instead of doing it by hand. There are times where you would not do a thing by hand and so you don't ask the tool to do it, and there are times you ask the tool to do something you would never do by hand.
  • matthewmueller 7y ago
    I guess I'm an advanced programmer. You described my exact progression :-)
  • sjwright 7y ago
    Even more advanced programmer: writing the ‘boilerplate’ queries out manually takes barely more time than composing them in an ORM, means less indirection, saves me a major dependency, and encourages me to think intelligently about each query no matter how boilerplate they might seem at the surface. Super-advanced programmer: allowing my database structure to be influenced by the needs of an off-the-shelf ORM will make it worse.
    • deleted 7y ago
      [deleted]
    • chipotle_coyote 7y ago
      Programmer without anything in particular to prove: hey, I already know SQL, and ORMs create queries "under the hood" in ways I can reason about and control, so I'm going to use this ORM in a way that doesn't involve just throwing objects and tables together in a big pile and mooshing them all together with duct tape.
• buboard 7y ago
  ORMs look like sonething that violates Occam s razor
• braindongle 7y ago
  "...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." Rails perspective: Pump your brakes there, friend. This claim depends completely on your application's needs. I write web apps for research that don't need to scale and don't need lighting performance. I have built apps that ended up needing some optimized-SQL-versus-ActiveRecord experimentation, and sometimes, writing raw queries is certainly worth it. AR has been evolving for 15 years, is extremely useful, and all that is needed for many use cases. We're not all trying to do the same thing.
• nickjj 7y ago
  Since 2014 we've gotten new tools to play with. For example, Ecto isn't technically an ORM but it lets you do ORM-like things. It's Elixr's data mapping and query language tool. It happens to be one of the nicest "I need to work with data" abstractions I've ever used. It's a bit more typing than ActiveRecord and even SQLAlchemy, but you feel like you're at a good level of abstraction. It's high enough that you're quite productive but it's low enough that it doesn't feel like a black box. You get nice benefits of higher level ORMs too such as being able to compose queries, so you can design some pretty compact and readable looking functions, such as: def eligible_discounts(package_id, code) do __MODULE__ |> for_package(package_id) |> with_discount() |> active() |> usage_count_less_than_usage_limit() |> after_starts_at() |> before_ends_at() |> maybe_code(code) end Each of those function calls is just a tiny bite sized query and in the end it all gets composed into 1 DB query that gets executed. I think Ecto's biggest win was having the idea of changesets, schemas and repos as separate things. It really gives you the best of everything. A way to ensure your data is validated but also flexible enough where you can separate your UI / forms from your underlying database schema. You can even choose not to use a database backend but still leverage other pieces of Ecto like its changesets and schemas, allowing you to do validate and make UIs from any structured data and then plug in / out your data backend (in memory structs or a real DB, etc.).
• QuadrupleA 7y ago
  As a refugee of Hibernate etc. I definitely agree with this. Python in particular already has data types that map nicely to relational data - dict, list, tuple, iterators - so mixing SQL calls directly in code works great. Plus you get full access to the particular database's features, direct control & understanding of what's being executed when, easier query & performance tuning, no special 2nd pseudo-sql dialect to learn, no big extra stack of leaky abstractions to troubleshoot, etc. One of the big pitches of ORMs is "you can change your DBMS mid project!" Which sounds cool and has its occasional applications but is something I've never actually needed in over 20 years of development.
  • ratww 7y ago
    I worked in some projects where we had to support multiple databases. Entity Framework and a few Database Views helped us keep it agnostic. It was very common in the pre-cloud enterprise world. Uber did it recently, too. They changed from Postgres to MySQL. But I don't know if ORMs helped them or not.
• JesseAldridge 7y ago
  This was my position for a while. ORMs introduce a layer of magic which obscures what's actually going on under the hood. I decided I would just make raw SQL queries and handle mapping data explicitly. I quickly ended up with a lot of duplicated code. So then I thought, "Well ok, I should add a bit of abstraction on top of this..." I started coding some simple functions to help map the tabular data to objects. One thing led to another and suddenly I looked at what I had done and said, "Wait a minute..."
  • stephen82 7y ago
    Hahaha! ^_^ Seriously Jesse, isn't this the very same reason __some__ people end up implementing yet another programming language without realizing it? First they start out of exasperation with X language they use, because they hit some obstacles or limitations, and before they know it they end up implementing a newly created language. You know what's the fun part? In their attempt to fix the aforementioned language's issues, they end up introducing __the very same problems__ in their own language, only under different "cloak" so to speak. It's a vicious cycle I'm afraid...
    • mruts 7y ago
      So are you suggesting that we can’t do better than assembly machine code?
  • vbezhenar 7y ago
    Sometimes you just should live with duplicated code. It's OK.
    • dymk 7y ago
      But you shouldn’t live with a hand-rolled pseudo ORM that stumbled into existence when there’s developed alternatives
      • pknopf 7y ago
        There's a middle ground. Micro ORMs.
• blacksoil 7y ago
  The problem on the article is because the author seems to be using ORM as a complete replacement of SQL query. When it's used like that, for sure it's gonna be a hindrance. How I use ORM is to help automating some of the things, while on top of that, I'd still write raw queries for the more complex things. For example, I create base tables using ORM, and then lay off all the joins and more complexity in a handwritten views. For query, I use ORM for something simple such as direct single-table queries or straight forward joins. When things get more complex such as joining a bunch of tables, or doing nested queries, I just use raw query. The thing about ORM is that it should always be used as a complementary tool. Feel free to mix and match it with raw SQL queries as needed. Trying to do everything in ORM is just too much. One would end up spending too much time learning about the inner working of the ORM, instead of getting things done. So yeah, use it as a complement to raw queries, not as a substitution.
• otakucode 7y ago
  In my prior job, I worked with a few (really skilled) interns that were just finishing up their CS degrees. I found out that they didn't learn any SQL at all and their interactions with databases was severely limited overall. This really surprised me, as I had a whole databases course when I went through college (different school, but in the same state, just 20 years earlier). Is not teaching SQL or relational database concepts no longer common at colleges? I should mention that the school they went to otherwise was excellent and taught a great many things my own schooling never touched on and I had to pick them up later myself. I'm just curious if it's a matter of just that school, or if other courses have been prioritized across the academic field generally for some reason.
• hardwaresofton 7y ago
  ORMs are a leaky abstraction -- there is a sweet spot (mostly basic queries/relationships) where they provide lots of value, but there is a veritable infinity of scenarios beyond where they are a hindrance. Also they tend to introduce lots of hidden complexity when they try to introduce flexibility to match the landscape. I'd argue that most of the time what you really need is a query builder with the right combinators, and maybe some higher order combinators to wrap those together for even better ergonomics. For those writing NodeJS one of the best ORMs that I've found is typeorm[0], check it out. Here are some reasons I like it: - Typescript-first - Provides the repository pattern (ex. repository.findOne) & the entity manager pattern (ex. manager.findOne) so you can use whichever you prefer - Annotation-based entity markup - Ability to drop down to raw SQL query easily at any time - Query building ability, special handling for relation queries, it's even got support for building with where params like and and or - Great documentation - Ability to programmatically use the introspection that it does for you (accessing the metadata from code is trivial) - Handles migrations (you can generate them, run them, etc), easy to use programmatically as well - Supports some low level concepts from my own favorite RDBMS, postgres [0]: https://github.com/typeorm/typeorm https://github.com/typeorm/typeorm
  • TylerE 7y ago
    That's why you use something like SQLAlchemy that gives you all the good parts, but lets you painlessly drop down to native SQL when you have to.
  • graphememes 7y ago
    TypeORM is great, until you realize you have to use Typescript which is not that great if you've ever used a real type based language
• brettnak 7y ago
  I think a lot of this is just some weird expectations. You cannot _use_ a database without knowing sql. ORMs work great, but you have to know sql to use them properly. What if you want to load a relationship with a join? What if you want only to load certain columns? I would consider part of the profession to be knowing sql. This whole "orms are bad / long live the orm" split attitude is ridiculous. ORMs are fine. Some orms are bad, just like some code is bad, and some frameworks are poorly thought out. I have worked with developers in the past, who I _desparately_ wish were forced to use a good ORM like ActiveRecord, so that they could understand just how far you can get with a solid and good pattern. I've also worked with developers in the past who used an ORM like a 15kg sledgehammer, and had absolutely no idea what was going on.
• fortran77 7y ago
  I just need something that takes the result from the query and puts it in a language-compatible data structure.
• ReD_CoDE 7y ago
  I have four questions related to SQL 1. PostgreSQL or MySQL? And why? 2. Is it possible to build a hybrid database schema? For example, SQLite+JSON? 3. Is it possible to convert XML(XMI) schema to SQLite schema automatically? 4. Is it possible to build a custom file format based on SQLite or hybrid one based on SQLite+JSON?
• jbogard 7y ago
  Just learn both? Use ORM for writes, SQL for reads, if you need to. ORMs do not obviate the need to learn SQL.
• peteretep 7y ago
  I used SQLAlchemy and would not reach for it again, but you can prise DBIx::Class from my cold dead fingers, so I think _which_ ORM is an important consideration too.
  • mst 7y ago
    Every time I hear somebody say that I grin. Fun things are coming soon btw, I finally figured out a couple of design problems that have been annoying me for years and the code is close to stable :)
• solidsnack9000 7y ago
  One of the biggest problems with ORMs is that they encourage unstructured SQL access. What queries does the application run? With an ORM, it's impossible to know, because relation-fronting objects are available everywhere in the code, and that means any module could be composing or adjusting queries. A logical thing to do is restrict these kinds of actions to a database module, which exposes a function for each query we want to run against the database. This is a lot like the stored procedure model, with the procedures in the app (and adjustable from the app) instead of in the database. With a structure like this, it isn't so bad to write all queries as SQL files with some template parameters. Maybe there is repeated logic but there is usually a SQL templater for your language that lets you template in table names. There is abstraction, there is clarity about what code is being run, there is control over performance, there is syntax highlighting.
• dfilppi 7y ago
  Amen brother
• smitty1e 7y ago
  > My contention with ORMs is that, if you need to know SQL, just use SQL since it prevents the need to know how non-SQL gets translated to SQL. I feel the same way about Wiki syntax as ORMs. While SQLAlchemy is great for getting the DDL done, my SQL-fu is such that I wind up just using SQLAlchemy as a glorified connection manager while I construct the SQL strings directly rather than muddy up a sophisticated query by translating it into Python. The same holds true for Wiki syntax. If I'm already proficient at HTML, it buys me precious little to use this 'easier' syntax if sometimes I'm in Redmine, sometimes I'm in Confluence, or wherever else I land. Just let me code the page already!
  • philwelch 7y ago
    In terms of wiki syntax, HTML is just so damned noisy that I'm fine with Markdown--but mostly because Markdown is fairly standardized and I don't have to learn a totally different syntax for everything.
    • smitty1e 7y ago
      "Fairly" So I spend as much time faffing about with how this particular tool does anchors as I would just doing the "a" tag outright in HTML.
      • philwelch 7y ago
        The real question is why aren’t you using the WYSIWYG editor most wikis have these days.
• noobquestion81 7y ago
  Composible ORMs (for me, AR+Arel, this was about 4 yrs back) let me write some of the most magical, legible query code I have ever seen. I could create and reason about subqueries with highly dynamic filter options and pass them around and wrap them with outer queries, all in. perfectly readable code. Glorious. Of course, for complex analysis like in specific periodic data reports, where the filter parameters are mostly known and don’t change constantly with the model, there are diminishing returns to this. However, when you start writing code that writes other code using string concatenation, big-picture wise I think you are doing it wrong. Look at HTML and how it has developed towards client heavy apps as other example. Encoding is hard, dangerous stuff, and having a library do it for you (like the w3c DOM APIs, or higher abstractions like react) can be invaluable and can make the difference between spaghetti code and gorgeously understandable functional statements.
• Mc_Big_G 7y ago
  2014
• nurettin 7y ago
  ORM is like a regex. When you use it, you've got two problems. The problem with having two problems is that we love solving problems! Yay OCD! So if an ORM doesn't let us do something, instead of just getting the work done with a couple of quoted SQL strings, we try bending the ORM to our will. I know I've done this with the entity framework. And when we do that, the perpetual debate starts all over again.
  • cutler 7y ago
    Why do I have two problems if I use a regex? "Programming Perl" and "Mastering Regular Expressions" were what drew me into programming in the early 2000s. Whilst ORMs are optional regular expressions are not so I don't get your point.
    • nurettin 7y ago
      Pretty sure regex is not the only answer to parsing problems. Maybe it is for someone who has preferred using it for the past 15 years.
      • cutler 7y ago
        Ok, what's your alternative for validating an email address?
        • nurettin 7y ago
          To use a parser that can validate RFC5322 p 3.4.1
          • cutler 7y ago
            A parser such as ....?
• rodmena 7y ago
  it's obvious. ORMs are result of those OO people who want to make even their carpet Object Oriented.
• zem 7y ago
  I don't want an orm to keep from learning SQL, I want one to save me from having to write reams of boilerplate code copying fields one at a time from an SQL result to my language objects/structs/etc and then back again when I want to save them to the DB.
• cmrdporcupine 7y ago
  Codd's relational model on its own is a more beautiful, flexible, and concise system of data representation than the object oriented model. SQL is a rather poor implementation of it, but it will do, in most instances, for the sake of compromise. What it is missing is a model of _behaviour_. I've always (ok well, not always, but for the last 15 years probably?) felt the missing piece in what we call 'middleware' isn't to resolve the impedance between object orientation and the relational model, but to somehow come up with a method of representation behaviour/execution for the relational model itself. And do away with all these hierarchies of classes and whatnot. Something conceptually like what we call a method/multimethod in OO, but consistent with the mathematical, set-based lingo of the relational algebra. But I'm probably not smart enough to figure that out. I hope someone does. I don't think 'stored procedures' is even close to it. Maybe Datalog has it, but I haven't grokked it well enough to say.
• danenania 7y ago
  The way I'd put it is: "learn SQL then use an ORM". If you understand exactly what SQL is being generated and what the implications are, you can use an ORM effectively and benefit from it in many ways (readability, security, composability, etc.). You'll also know when to use it and when it's better to just drop down into SQL. It's when people don't really know what the ORM is doing underneath that they get in trouble.
  • GiorgioG 7y ago
    And therein lies the problem. These ORMs (by and large) have become so complex and abstract that it's nearly impossible to understand what they're doing underneath (I'm looking at you Entity Framework (in .NET)) You wind up having to have a deep understanding of the ORM and SQL, at which point I would argue why bother with the ORM at all? For .NET, I'm much happier with a very very thin layer over the base .NET database client library called Dapper. Unfortunately most shops use Entity Framework by default.
    • danenania 7y ago
      "You wind up having to have a deep understanding of the ORM and SQL, at which point I would argue why bother with the ORM at all?" I agree that ORMs are often too complicated, but imho it's still worth it to learn and use one. I'm using ORM loosely though in that I don't think heavy 'Object' and 'Mapping' layers are so important. Mainly you just need a reasonable way to parameterize and compose queries so you can avoid injection and deduplicate logic in a sane way. So I guess my argument is more "use a library" than "use an ORM". In my experience everyone who says they'll use pure SQL ends up adding gnarly string building logic at some point because the duplication gets out of control. It's better to just find a decent lib that works at whatever level of abstraction you're comfortable with and use that.
• methehack 7y ago
  Have any of the naysayers used rails' ActiveRecord? I used to not like ORMs much, but honestly it's a whole different ballgame. Very easy to slip into SQL for bits (or big pieces) as well. People don't really complain about in the rails world (my respect only increases) and it has a lot of miles.
• jtdev 7y ago
  SQL is to much software development what rivets, bolts, or even beams are to the construction of skyscrapers... if you’re going to be in the software business, you should really get comfortable with SQL, after which you’ll likely find the friction and headaches caused by ORMs to be a deal breaker.
• zmmmmm 7y ago
  Writing SQL is a lot of work, not type safe (in the programming language static typing sense), often not re-usable, and hard to test. There are libraries that solve these parts for you (or at least help with them). For me the optimum is libraries that do only these parts but actively try to NOT introduce any new layer of abstraction over the database model itself. The problem is that in this whole ORM discussion, the baby gets thrown out with the bathwater.
  • brianpan 7y ago
    I’ve had success in Java with Freemarker. It’s a templating engine so you’re just producing strings. But you can add typing; use variables, loops, and conditionals; and include other templates for sub query reuse. IntelliJ has a plug-in. It’s a nice compromise between crafting strings vs SQL DSL. As for the objects, you can get very far with everything being a Map until you really need to add a class or two. :)
    • zmmmmm 7y ago
      Absolutely. One of the things I like about Groovy is that it solves the other half of that equation because it has the built in map constructor and built in SQL APIs that work with it. So you can actually do class Foo { Long id String name ... } Foo foo = new Foo(db.firstRow("select * from foo limit 1")) And it all just works if the database columns match the fields of Foo. And if you just do Foo foo = new Foo(db.firstRow("select name from foo limit 1")) Then you get a `foo` with only the name populated, etc, and you got type safety, easy direct, efficient SQL queries and the ability to test your code without hitting the database, all without imposing any "leaky" abstractions that cause all the problems.
• ydnaclementine 7y ago
  Personally, I feel like a very similar article could have been written called 'What CoffeeScript has taught me: just learn Javascript'. I have nothing against coffee or typescript (and other alternatives) and think they're very useful, but at the end of the day it's really just javascript. But I guess you could make that argument about anything until you get down to machine code.
• mmusc 7y ago
  I don't know.. when starting out or prototyping a new feature I find ORMS useful for moving fast, and the performance good enough. You can always slowly migrate to pure sql for critical parts of the system.
• toasterlovin 7y ago
  FWIW, I think the sweet spot for most web apps is to use an ORM on top of database views. This gives you the power and performance of SQL, but avoids the impedance mismatch of the ORM, since you’re defining what data will be on your objects at the database level.
• jwmoz 7y ago
  Having to use Doctrine with Symfony and PHP was genuinely one of the main reasons I quit using PHP and went with Python.
• jukik 7y ago
  So do i , take some times to learn about it
• calvinchengx 7y ago
  This is a false dichotomy isn't it? Most ORMs allow the developer to bypass the ORM and use raw SQL when the need arises, so I don't really see the point of avoiding ORMs. Across the lifespan of a project, in early stages, a developer would discover that ORMs provide code maintainability and depend heavily on it. As project requirements increase in complexity, raw SQLs will be required because of performance reasons.
• deepak1990 7y ago
  https://gadgetsreviews.org/walmart-black-friday-sales-deals/ https://gadgetsreviews.org/walmart-black-friday-sales-deals/
• holri 7y ago
  ORMs often implement only the lowest common denominator of all supported databases. Easy changing databases is in a free software world seldom a necessary requirement. If you use a advanced feature rich database often a lot of it is not supported by the ORM.
• ltbarcly3 7y ago
  I have fought with orms for many, many years. I think if you sit behind someone and watch them build an application with an ORM, and you sit behind the same person and watch them use SQL and data access functions and other boring things, the person using the ORM will spend many, many hours fighting to get the ORM to do what they want, or fixing bugs that pop up because the ORM didn't do what they thought it was going to do in subtle ways. Generally, with threadlocal sessions and an application passing orm data class instances around the code freely (which is by far the most common pattern of use), the application will end up doing 10,000x more queries than the programmer would have guessed (this is a literal number and not an exaggeration). Trying to tell the ORM to preload the tree of objects that is going to be accessed is nearly impossible, since the instances go up and down the stack from function to function, each potentially accessing an attribute of an instance loaded as an attribute of an instance many levels back to the original object intentionally pulled from the database. ORMs make writing the application 90% faster for the first 2 weeks and then 50% slower from then on. That doesn't mean you are stuck writing straight SQL queries and passing around rows of data, you can sit for a bit and build data access functions that make your life easier and write classes that represent entities from the data, but without your data going through tens of thousands of lines of (extremely well engineered and thoughtful) ORM code that you have no hope of ever understanding well enough that you will avoid catastrophic mistakes that are extremely hard to fix. And you will make catastrophic mistakes, mistakes you would probably never make with SQL. SqlAlchemy has 5 states that orm objects can be in. If you are using SqlAlchemy and you can't instantly tell me what those 5 states are, and the detailed description of each, you are already making huge mistakes and corrupting data. To quote one section from the many pages of SQLAlchemy documentation about 'session state management' (and if you don't know all this stuff by heart you will end up learning a lot of it the hard way): ============================================== The SELECT statement that’s emitted when an object marked with expire() or loaded with refresh() varies based on several factors, including: The load of expired attributes is triggered from column-mapped attributes only. While any kind of attribute can be marked as expired, including a relationship() - mapped attribute, accessing an expired relationship() attribute will emit a load only for that attribute, using standard relationship-oriented lazy loading. Column-oriented attributes, even if expired, will not load as part of this operation, and instead will load when any column-oriented attribute is accessed. relationship()- mapped attributes will not load in response to expired column-based attributes being accessed. Regarding relationships, refresh() is more restrictive than expire() with regards to attributes that aren’t column-mapped. Calling refresh() and passing a list of names that only includes relationship-mapped attributes will actually raise an error. In any case, non-eager-loading relationship() attributes will not be included in any refresh operation. relationship() attributes configured as “eager loading” via the lazy parameter will load in the case of refresh(), if either no attribute names are specified, or if their names are included in the list of attributes to be refreshed. Attributes that are configured as deferred() will not normally load, during either the expired-attribute load or during a refresh. An unloaded attribute that’s deferred() instead loads on its own when directly accessed, or if part of a “group” of deferred attributes where an unloaded attribute in that group is accessed. For expired attributes that are loaded on access, a joined-inheritance table mapping will emit a SELECT that typically only includes those tables for which unloaded attributes are present. The action here is sophisticated enough to load only the parent or child table, for example, if the subset of columns that were originally expired encompass only one or the other of those tables. When refresh() is used on a joined-inheritance table mapping, the SELECT emitted will resemble that of when Session.query() is used on the target object’s class. This is typically all those tables that are set up as part of the mapping. ========================================= Yep, sounds like an ORM makes life a lot simpler!
• cryptonector 7y ago
  Just use YesSQL.
• kyllo 7y ago
  Learn SQL, yes, and then use your ORM as a handy SQL generator library because life is short and you do not want to write that much SQL.
• Animats 7y ago
  Rather than having a full ORM, a SQL system that didn't put in variables by embedding strings would be useful. You can almost do this. SQL has variables, but they're more persistent than needed for this. SELECT a,b,c FROM tab WHERE a=@mysearchkey; An API should look something like result = sql->command("SELECT a,b,c FROM tab WHERE a=@mysearchkey;", {"@mysearchkey": val }) and the result should be a key/value form. In some languages, you might get a typed structure back. No more string escapes. No more forgetting the string escapes. If you required that the command had to be a constant, SQL injection attacks would be a thing of the past.
  • wvenable 7y ago
    Almost every single native RDBMS API provides parameterized queries / prepared statements where the parameters are sent separately from the query text. Here's one from MySQL: https://dev.mysql.com/doc/refman/5.7/en/mysql-stmt-bind-param.html https://dev.mysql.com/doc/refman/5.7/en/mysql-stmt-bind-para... String escapes should have been dead a few decades ago -- I don't think any modern platform requires it; they all support parameterized queries natively.
• known 7y ago
  In SQL we can't move the cursor back and forth with Index;
• gigatexal 7y ago
  ORMs makes it easy to stand up databases and make doing migrations a lot easier. That being said it’s my opinion that after using them in the person space the best ones do the boring stuff but then are best used a as query building tools so you can have some control over what the query looks like and actually does. That being said if done right I prefer thin abstractions over my data layer and raw SQL — I don’t care for the data layer to be a black box
• jasonkester 7y ago
  I don't know. I've been building database backed stuff for 25 odd years now, and I've never experienced this Object/Relational Impedance Mismatch that everybody talks about in any of my designs. I sometimes wonder if it's just the approach I take that has ended up dodging that bullet somehow. My initial design is always done in the database. Whether it's a little feature or a green field new project on a blank sheet of paper, that sheet of paper is the Schema Designer of my db (or a schema.sql if I'm in postge/mysql land). Once the schema is nailed down, the object structure flows out easily. You can follow foreign keys, many-to-many tables and non-identity primary keys to figure out your children, relations, and inheritance. It's so well defined that for my own stuff I just point my code generator at it to get a pile of base classes and stored procedures for all the CRUD. (And re-run it at build time to ensure that everything always matches up.) So when it comes to pulling down records, modifying them, saving them, grabbing sets of them to spin through, etc. There's never any mismatch because what you get from the db will naturally look just like what you need. Because you designed it to be that way. I may hazard a guess as to why so many people do run into issues, and it's because I notice that nearly every ORM I've seen in the wild expects you to define your schema someplace other than the database. It'll have some wacky XML config file that you're supposed to keep up to date, with even wackier "migrations" for when that changes. And it'll then either build your db schema for you or expect you to have something in place that matches what it wants. But that's silly. There's already a perfectly good place to keep your schema. In the database. And I guess it follows that if you don't design your system with a sensible relational data model in mind, you might find that your object structure doesn't in fact fit in a database correctly. Could it be that that's what people are describing when they talk about Impedance Mismatch?
  • spion 7y ago
    So if you have to change the schema afterwards (with lots of data already in the table), what do you do?
    • jasonkester 7y ago
      Change the schema, and run a data migration script if necessary. And, of course, check the change script into source control so that your build system knows to run it on the staging and prod dbs when the time comes. Then simply build your project and fix any compile-time errors that arrived when the base classes were all blown away and rewritten. Extra points for keeping your column names in string enumerations so you can't ever get runtime errors from typos or having renamed a column. (all handled by the code generator, of course) But yeah, even on my mature projects I still find myself changing the schema all the time. It's as easy to do as adding/changing a class in the project itself.
• ne01 7y ago
  It’s like advising someone with an arrow in his heart to not smoke—who cares, he has a few moments left anyways. OOP is enough of complexity to kill most projects—and yet we discuss the dangers of ORM. “Object-oriented programming offers a sustainable way to write spaghetti code.” -PG
• qaq 7y ago
  A good alternative is libs like https://pugsql.org/ https://pugsql.org/
• headmelted 7y ago
  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. I was with the spirit of the article save for this. Recently I've been developing some work for one of my client's in the .NET world. There had been ongoing discussions about developers wanting to use Entity Framework as an ORM vs. using stored procedures. The client already uses SQL projects and DACPACs (effectively a system for specifying the DB in SQL then diffing it between versions to alter the database). The arguments for and against ORM were largely naive from each side of the fence - the developers wanted strong typing (doesn't require an ORM) while the DBAs wanted to be able to review query execution and suggest changes if there's an issue (you can see the generated SQL for the ORM). The solution I came up with was to use reflection on the build server as part of the CD process to map the inputs and outputs of the stored procedures into strongly-typed C# methods and objects, generate code for it, and build/pack/push a nuget package back to our feed. It's similar to what EF provides without maintaining the EDMX (which we can do as we don't need to cover every possible data access scenario like EF does). It means that once the database project is checked and the build green-lights, an updated Nuget package that constitutes the DAL is automatically waiting on the internal Nuget feed. I've found it gives us the best of both worlds. We can do anything we need to in t-SQL and the C# wrapper only cares about the ultimate input/output. I can force the use of parameter sanitization in the wrapper (by not providing any other way to call the procs), and the DBAs can review/amend whatever they want without the developers needing to change their code as the interfaces don't break. We also don't have to write DAL boilerplate or worry about inexperienced developers getting it wrong and opening injection attack surfaces at the DAL layer. There's usually a solution to your use case if you look for it is my point.
  • Shorel 7y ago
    Make it a product and sell it/github it.
    • headmelted 7y ago
      I'd happily github it if it would fit more use cases, is it something you think others would be interested in?
• speedplane 7y ago
  I'm okay with ORMs, but I can't stand lazy evaluation. It often leads to situations where it's entirely unclear when the program is making a database call. For example, given the small django program: dbcall = models.Purchases.objects.filter(amount = 100) if dbcall: do_something() if len(dbcall) > 5: do_something_else() third_thing(dbcall[0]) Does the above app make 1, 2 or 3 database requests? There is an answer, but it's not at all clear to the developer.
  • Nicksil 7y ago
    >There is an answer, but it's not at all clear to the developer. A cursory reading of the documentation is usually a first step in acclimating to an otherwise unfamiliar system. It just so happens, for your example here, a complete explanation[1] can be found at the very top of what's likely the most vital subsystem's documentation. During due diligence, this information will be among the first encounters. [1]https://docs.djangoproject.com/en/stable/ref/models/querysets/#when-querysets-are-evaluated https://docs.djangoproject.com/en/stable/ref/models/queryset...
    • speedplane 7y ago
      > A cursory reading of the [django] documentation is usually a first step in acclimating to an otherwise unfamiliar system. It just so happens, for your [lazy evaluation] example here, a complete explanation can be found at the very top ... The "very top" of the django lazy evaluation documentation you pointed to hardly sheds much light on the issue. The documentation just says that certain operations cause database queries, and that some are cached. It's not clear whether doing two similar operations consecutively will result in two database queries (e.g., checking len(dbquery) twice, or checking for truthiness then pulling the first object from a database query). The django documentation does differentiate between evaluated and non-evaluated database queries, but to the developer, it's the same object. Would be much better if an "unevaluated query" and an "evaluated query" were two different types with different semantics. Worse, these lazy evaluation semantics are not terribly pythonic. Checking the length or truthiness of a string, list, set, dict, or tuple is an O(1) operation. Checking the length of a database query may (or may not) make network calls, and may have O(N) performance or worse. Databases queries can give rise to performance bottlenecks and race conditions, and hiding what's actually happening from the developer is a recipe for all sorts of problems.
• jmnicolas 7y ago
  I learned SQL at school. When I was hired and suggested to use an ORM, the boss didn't know what an ORM was, so he said no. I have no problem with SQL, I actually like the language it's very powerful. However mapping query results to objects is soul crushing. So a few year ago when I became the sole dev in the company, I decided I would try Entity Framework (Microsoft .net ORM)on a new project that needed to be delivered yesterday. To get even more speed I didn't even design the database as I do usually, I went with the code first approach. It was magical, so much less work to do. Then something went wrong with Entity Framework it didn't retrieve the right data I think (sorry I don't remember what exactly, but it was a show stopper) and I couldn't find a solution to my problem online. Then I had to do everything by hand in a rush, I was very late with the project. I kind of lost interest in ORMs after that but I thought there should be a way to map queries to objects. So I started to develop my own micro ORM with Tuples. I went nowhere fast but while I was searching I discovered that what I wanted to do already existed and was actually named a micro ORM. Now I use Dapper (Stackoverflow .net micro ORM) and I'm very satisfied with it. Its level of magic is relatively low, so if it doesn't work I can quickly replace it with a manual query without losing much time.
• hackerbabz 7y ago
  Is it so hard to say ORMs are great for 90% of grunt work but sometimes you need to write your own SQL?
• yegor256a 7y ago
  You may find this relevant: https://www.yegor256.com/2014/12/01/orm-offensive-anti-pattern.html https://www.yegor256.com/2014/12/01/orm-offensive-anti-patte...
• sword_smith 7y ago
  I use Entity Framework for C# and I have grown to appreciate it. I get libraries for in-memory databases which makes it easy to write thorough unit tests, the `Include` function uses join to include foreign key objects in an optimal manner, and scaffolding tools make it easy to map from SQL to C# classes. The resulting SQL from the Linq expressions is logged which makes it easy to see what is going on, if you already know SQL. The only problem is that I think the writeback to the database of manipulated or new objects is not clear but that might just be because I haven't read the documentation for that thoroughly.
  • eknkc 7y ago
    EF is fantastic to use. I think it's because while other ORMs try to integrate their logic into a language, EF was developed alongside linq, IQueryable stuff and language extensions. Basically, they changed the language to better accomodate stuff like EF. And it shows.
• marble-drink 7y ago
  Don't just learn SQL. An ORM like SQLAlchemy will allow you to save time and write cleaner, more comprehensible code. But do learn SQL. Do not use an ORM before you have learnt SQL.
• nikolasburk 7y ago
  This is a great article, I've really enjoyed reading it! I want to react to a few of the points made in the post, and point out some potential techniques for solving them! Most ORMs are heavily inspired by object-oriented programming which fosters the object-relational impedance mismatch. In addition to logic for storing and retrieving data, models often also implement business logic (ActiveRecord is a good example here). This makes for bloated and complex objects that are difficult to work with in the application. A solution can be to lower the level of abstraction and use a more lightweight query builder (like knex.js for Node.js). These kind of tools give you more control in constructing your queries as well as the ability to optimize them. These tools still require you to understand quite a bit of SQL though and the productivity leap compared to writing manual SQL isn't as high. I believe that query builders are the best compromise we have today for accessing a database from an application. Regarding the dual schema dangers that are mentioned by the author, I strongly believe that these can be alleviated using code generation tooling that helps to keep your database in sync with your application models (approaches like SQLBoiler in Go where application code is generated based on the database schema are an example here).
• leowoo91 7y ago
  Using both should be fine.. SQL alone encourages early optimization that might slow down the development, especially when you don't have the complete non-functional requirements. It sounds like a pitfall to compare it against using ORM. The way I see it, ORM stands good when it's understood as an additional capability.
• chousuke 7y ago
  I think that my primary issue with ORMs is that they often allow you to code as if the database doesn't exist, instead of explicitly structuring code to handle network and database failures at the points where you deal with external data. It is a problem if potentially any part of your program can just fail, and that happens if you allow magic ORM-objects to leak onto code paths that aren't written to handle them.
• gwn7 7y ago
  > I’ve found that reflection techniques to get the data definition are not worth it and I succumb to managing the redundancy of data definitons in two places. Not sure if much has changed regarding the "reflection techniques" since 2014, but I think Postgres does a fine job reflecting. (Don't know much about others) For example my favorite library Massive.js (a data mapper) depends completely on reflection. It allows its users to access tables, views, functions, extensions, and even enum types from its Javascript API, without the need for models. This completely solved the data definition redundancy problem for me. I even made a small layer on top of it to get the constraint information too using the information_schema, and everything is working like a charm.
• Isinlor 7y ago
  If you put unnecessary abstraction in front of perfectly good solution the you will end up with problems. Object oriented programing is about clearly expressing business logic. There is no complex business logic in dumping big tables of data. So, it's not about ORMs, it is object oriented programing that is poor fit for doing reporting. Relational databases, declarative SQL, functional pure programming are good solutions for reporting. And you should certainly learn SQL if you want to do any applications with ORM or without. I recommend Joe Celko's SQL for Smarties: Advanced SQL Programming. Object oriented languages shine when there is a need to create a precise language that facilitates fast and robust communication between domain experts and developers. In situations where you have little data, but a lot of intricate logic. This is where relational databases are simply no good. Relational algebra and SQL is not a very expressive natural language. SQL limits your vocabulary to 4-5 verbs unless you start writing procedural code in procedures etc. but SQL is not a good procedural language. Relational databases are a solution to specific technical problems of scale, execution speed, atomicity, consistency, isolation, and durability (ACID). They excel at that, not at communicating intention. You should use ORMs (preferably Data Mapper) if your goal is to solve problem of expressing complex domain specific logic. You use relational databases in that situation because they just work. Data Mapper allows you to isolate your domain model from tricky technical aspects of data storage like indexing and/or not corrupting files during power outage. ORM works very well as long as you will actually be able to ignore technical aspects of speed etc. in your domain model. You can do the data mapping, querying, migrations, and all this technical cruft manually with a handwritten SQL if you want, but SQL certainly will not address very well the goal of creating an expressive domain model that facilitates robust communication between developers and business experts. So, given that we addressed the elephant in the room, some other points: Dual schema dangers: "I much prefer to keep the data definition in the database and read it into the application." That's perfectly good solution, if you have a lot of data and amount of logic related to data is minimal. If you have little data and a lot of logic you will not be able to store data definition exclusively in database whether you are using ORM or not. Even if you just store SQL queries in your code, then you do store schema structure with SQL, just not explicitly, but implicitly. Data migrations, rolling release etc. are tricky whatever you do. I use my ORM to dump me SQL that is needed to move structure from point A to point B, since ORM knows the schema it can do that, and then I manually adjust it as needed to massage data etc. Identities: Not sure if I follow here. It seems like you have issues with auto incremented ids. Auto incremented ids with ORMs are annoying, indeed. My advice is to use UUID generated in the code, then you will have no need to hit database. Also a side note here: if you use ORMs, do not use anything that has any real world meaning for ids. Use UUID, so that you are sure that no domain expert will want to mess with that. Transactions: The same situation as with speed and migrations. Getting technical details of transactions is hard whether you use ORM or not. You can use stored procedures, but I'm curious what will you do e.g. when you will need external REST API to get in sync or to copy user files to assure full transaction from the user perspective. There is no magic bullet, it's just hard.
• guggle 7y ago
  Apps should not have access to the underlying schema and should just interact with the database through stored procs or views anyway.
• tarsinge 7y ago
  My experience is that ORM are great for avoiding boilerplate IF you already know SQL and don’t consider them as an abstraction layer but as syntax sugar.
• goto11 7y ago
  The "ORM or not" discussion quickly runs off track, because there are so many different ORMs with different features and working on different levels of abstraction. Recently I have most experience with EF Core (from .net) which does not have the "select * " issue the article describe (you can select whole entities, but you can also project individual columns if you want.) It uses Linq for expressing queries, which means it is more concise end express relational algebra clearer than SQL. On the other hand I have tried Hibernate, which had such a verbose and cumbersome query builder syntax that it made you long for plain SQL.
• SQL2219 7y ago
  ORM=Rube Goldberg Machine?
• NicoJuicy 7y ago
  I use EF, it helps me with migrations and quick coding. If I need something extra, I just write the SQL. All in all, in general, I program much faster than other people who don't have this setup/flow. I recreated an API someone was working on for 2-3 hours in 9 minutes. This was the "senior" developer of 55 years old. Including creating the project, creating the migrations, creating the api and a small test.
• talkingtab 7y ago
  For simple cases it is almost always a good decision to use an ORM if you don't know SQL. The question is whether and how long the ORM shoe will continue to fit. Databases are essential for many use cases so the chances are that an ORM will not always work. So decide on that basis when to learn SQL. Maybe not today, maybe not this project. The other issue is how central
• anthony_doan 7y ago
  You should learn SQL first then ORM afterward. Most modern ORM have escape hatch to let you write raw SQL. Your favorite web dev language will have a dominant ORM. C#, Python, Elixir, Ruby all have a popular ORM that works within a popular framework. ORM will make working with database and web framework easier. I do agree with post that querying in ORM can be hard and sometime not possible without raw SQL. Writing query for Ecto, Elixir's most popular ORM, can be tricky when you want to do dynamic query.
• lichtenberger 7y ago
  By the way, not directly related, but I wonder if there are at least libraries to build type safe XQuery statements?
• codeulike 7y ago
  I'm old enough to remember when pretty much every business app developer knew SQL. ORMs didn't arrive because people didn't want to learn SQL, they arrived because there was a mismatch between 'relational database' and the emerging trend to 'put your business logic in an OO-powered independent middleware layer'. Unless you want to code all your logic into stored procedures and whatnot, you end up needing some sort of abstraction of the database structure that can manage business rules for you.
• tenroc2o0o 7y ago
  It seems like most of the points made in this article are either resolved by or don’t apply to Entitiy Framework and LINQ?
• cryptica 7y ago
  It amazes me how much consensus there is now about ORMs being an anti-pattern. I had figured this out over a decade ago; back then ORMs were so common and popular that even the most senior developers thought I was a fool. There was literally no choice; I had to work for several companies which were using ORMs. I dealt with the reality of the industry by specializing on the front end. Those were the dark ages for back end development. Nowadays front end development has taken the lead when it comes to insanity; React, Babel, GraphQL, Webpack, CoffeeScript and TypeScript... All tools/frameworks which add negative long term value. I switched back again to back end development a few years ago to escape the front end madness... But now back end development is also starting to degrade; pure functional programming and languages which transpile-to-JavaScript (to run on Node.js) are the latest diseases. Thankfully back end development is more fragmented and there is more room for different tools. If this continues, I will have to give up on software development and switch to consulting; I will be forced to sell complex (but popular) tools which create problems for businesses and then offer to sell them a solution to solve some of the problems which I created. It's not a joke though, business people really are becoming THAT retarded.
• carapace 7y ago
  What SQL has taught me: Just learn Prolog. "I'm seriously you guys." I was recently working with a Prolog-SQLite binding when it suddenly dawned on me that I should just skip SQL and use the Prolog engine directly as the DB. Modern Prolog systems can handle millions of records, for a lot of applications that's all you need. On the other hand they also have e.g. ODBC bindings. SWI Prolog can internalize data in flatfiles "Managing external tables for SWI-Prolog" https://www.swi-prolog.org/pldoc/doc_for?object=section(%27packages/table.html%27) https://www.swi-prolog.org/pldoc/doc_for?object=section(%27p... There's also a Prolog graph database coming out in October: TerminusDB. https://medium.com/terminusdb https://medium.com/terminusdb
• kizer 7y ago
  There is friction here because it’s at the meeting of two languages instead of implementation-interface, or implementation-service. People either try to hide the underlying SQL entirely on the one pole, or attempt to embed a SQL-like DSL within the primary language. I think that modern programming language ought to offer first class support for embedding other languages or DSLs - an idea behind Language Oriented Programming. SQL offers the domain appropriate syntax, while the “host” language allows access to in-scope variables, functions, etc. Of course there would be some more work in allowing a “mix” of two syntaxes. Another option is a query language as a subset of the language, sort of like C# and Linq.
• dokka 7y ago
  In my career, I have used Entity Framework, Active Record, Sequelize.js and Hibernate. I haven't had a problem with any of them. Especially not with postgres and mysql. I've worked in teams of 10+ and personal projects that I've completed in a couple of days. My advice is to only use the best of ORM's. I won't use an ORM unless it's at least 5 years old.
• dictatorsunion 7y ago
  Isnt SQL backed by Relational Algebra, which is why it’s so flexible?
• deleted 7y ago
  [deleted]
• lukaslalinsky 7y ago
  Using ORM if you don't know SQL is a recipe for disaster. And after you learn SQL, you will likely decide to go back to ORMs, because now you know how to use it efficiently to save your time.
• jakedub4d2 7y ago
  Working with SQL was nicer in PHP. It gets more difficult for some reason in Node/JS. SQL injection would always be a concern by default when writing raw queries. Though I think I could avoid writing flawed queries/endpoints, it's harder to expect that from a team at scale. #foodforthot