20 ms·

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

• dahdum 9y ago
  Agree so much on this, efficient stored procedures are a godsend when refactoring applications and are excellent at backup authorization checks.
• tuespetre 9y ago
  No
  • jackaroe78 9y ago
    Boggles the mind
• leeoniya 9y ago
  query builders are the sweet spot.
  • pbecotte 9y ago
    I agree- Sqlalchemy without the ORM is pretty much the best code I've seen.
    • dfee 9y ago
      How does the ORM detract from sqlalchemy? I’ve been very happy with it for years, and I’m not clear on where an advantage is explicitly not using mappers.
• dola 9y ago
  A lot of the reasons listed in this article actually made me shift from sophisticated ORMs like Hibernate back to a query based approach. A really nice framework for this (in Java) is jOOQ which gives you the possibility to write typesafe SQL via code generation. https://www.jooq.org/ https://www.jooq.org/ (I'm not at all affiliated with jOOQ - just a happy user)
  • gregopet 9y ago
    Another happy jOOQ user here! I'm ditching Hibernate wherever I can in favour of it. And using it with Kotlin instead of Java makes it almost a form of poetry.. I've seriously been thinking about writing a blog post, something along the lines of "jOOQ: how I learned to love the database again" I've been thinking about what makes jOOQ so good and a huge part of it is brilliant engineering: SQL clauses are mapped almost 1:1 into reasonable and understandable code while the author spends huge effort to cover new features as databases introduce them without turning his product into a mess or introducing API breaks in every major new version. That's hard.. but awesome! :)
• anonfunction 9y ago
  If you have not read The Vietnam of Computer Science[1] I highly suggest it. http://blogs.tedneward.com/post/the-vietnam-of-computer-science/ http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...
• saryant 9y ago
  I have to agree. ORMs can be great when an application is just starting, because that's when you're writing the most tedious queries and statements, but beyond that I personally find that ORMs just get in the way. As soon as I need to write something more complicated than select-by-id I end up reaching straight for SQL. Otherwise I have to learn both the ORM's query API or DSL and have the proper mental model for how it translates to actual SQL. Or I could just write SQL and be done with it. No mysteries. Maybe this is just me, but I've never written a join in an ORM that I had the slightest bit of confidence in.
  • ProblemFactory 9y ago
    Any discussion on ORMs needs to consider what sort of app and queries you are writing. ORMs are fantastic for the very common case: * Fetch 20 rows and display them as a table, * Fetch 1 row by primary key and display it as a form, * Write updated fields from the form back into the 1 row in the database. Anything more complicated, and direct SQL starts being more attractive. But for the common case that ORMs are designed for, they are a major productivity boost.
• bjt 9y ago
  He says he likes SQLAlchemy, but doesn't say why. I'm interested to know that though. To me, the big win with SQLAlchemy is that it separates the SQL expression layer from the ORM layer so cleanly. I can write very complicated queries with the expression layer that wouldn't be possible at the ORM layer, and do it in a much more composable way than concatenating SQL strings. Example: http://btubbs.com/postgres-search-with-facets-and-location-awareness.html http://btubbs.com/postgres-search-with-facets-and-location-a...
• wybiral 9y ago
  I prefer to use SQL directly as well. But some points to be made in favor of ORMs (some of them, anyway): * Multiple backend support to handle different SQL engines. * Minimized risk of accidental injection. * Migrations.
  • qaq 9y ago
    Migrations are orthogonal to ORMs.
    • brucephillips 9y ago
      No. Migrations can be auto generated from Model changes.
• jim_combinator 9y ago
  LOL! I got fired from a project for making this argument. OMG, just get your data from sql statements or some sort of cache and be done with it...wtf.
• brucephillips 9y ago
  > ORMs, however, encourage this use and often make writing precise projections as tedious as they are in SQL. "As tedious as SQL" is not an argument to use SQL instead.
  • jjoonathan 9y ago
    Every extra abstraction layer comes at the cost of complexity, maintenance, and capability, and therefore inherits the burden of proving its worth. ORMs frequently claim to be less tedious than SQL. Failure to deliver on that promise is a reason to cut them out.
• jtchang 9y ago
  Why are we even having this debate? There are some ORMs that bring so much value to the table that you'd be stupid not to use them. Example being Django's ORM or SqlAlchemy. Also be specific in what ORM you are comparing to raw SQL. Are you talking about Hibernate or SQLAlchemy. Are you talking about a query builder? Good ORMs help tremendously with maintainability and security. They also let you drop down to raw SQL when needed. I don't think rails would have been as popular if you had to use SQL.
  • djrobstep 9y ago
    I've used SQLalchemy a lot. I've even written a keyset paging extension for SQLalchemy. But recently I've switched to writing stored procedures and calling them directly, instead of going through an ORM for everything... And it's so much easier.
    • wasted_intel 9y ago
      Keeping logic in the database like that means you can’t version those procedures alongside the rest of your code. That’s a pretty big downside.
      • jsmeaton 9y ago
        `create proc if not exists get_customer_v1243(params)` .. I've seen this.
      • 0xCMP 9y ago
        I mean you could, but it’d need to be through some kind of migration system to update it.
        • other_herbert 9y ago
          Flyway for Java... For stored procedures you use the repeatable syntax that way it checks the checksum of the file and if it doesn't match what is in the migration table it will run it... Easy and always up to date... That's with Java anyway...
• blaisio 9y ago
  I think saying "Just use SQL" is probably a bad idea. You'll most likely end up implementing an ORM anyway, or you will end up with your model code mixed up everywhere with your views. I do think a lot of people use ORMs as a crutch, which sucks. Also, ORMs often provide too much abstraction, forcing people who actually know SQL to relearn how to do everything the way the ORM happens to like it. I should not have to learn twice as much to be productive due to an abstraction. What I prefer are really lightweight ORMs that give me models which I can then enhance with custom code. I don't need an ORM that supports plugins or inheritance or a dozen different kinds of joins. All of that can be done more efficiently with custom code. Also, I think SQL builders are really useful. I think a lot of people conflate SQL builders with ORMs but they're actually very different problems.
  • putlake 9y ago
    AlphaZero's chess strategies turned out to be quite different from how humans have traditionally come up with chess heuristics/strategies. I wonder what paradigms will be used by AI that does computer programming. Will it organize code into some bits of functional programming, some OOO? Will it structure things into MVC? My guess it whatever AI does will be completely inscrutable to us. It may be optimized for efficiency rather than understandability, which humans need for maintainability.
  • craigvn 9y ago
    > You'll most likely end up implementing an ORM anyway, This is a really good point. Many people start with the "no ORM" philosophy, realize their application needs some way to map the SQL to the code, time passes..., they have implemented their own half-baked ORM.
    • hodgesrm 9y ago
      A more positive spin is that you'll have an "ORM" that's exactly adapted to your application. Many apps (1) don't need to work with multiple DBMS types and (2) don't use even close to the full panoply of SQL features. In a language like Java that has a generic DBMS API you can get along just fine with a few classes that handle CRUD operations and transaction management. Somebody familiar with JDBC and SQL can write the bridge classes in about a day, while keeping the overall application vastly simpler. Either way somebody needs to make an informed choice about ORM vs. direct SQL. It seems as some people get in trouble because they skip that part of the design process.
• adamnemecek 9y ago
  I don't hear people complaining about say LINQ. Maybe it's just that your language and/or ORM or integration thereof suck?
  • Someone1234 9y ago
    LINQ isn't a ORM. I assume you mean Entity Framework? EF definitely has the foreign key issue. We have around a thousand tables, we tried to generate the classes for all of them including foreign keys, problem is that when you create a context that references even only a single table, it will load everything that is foreign keyed including siblings of siblings of siblings, until you run out of memory. Only way around it is to not set up foreign keys which massively diminishes the value of using EF, so we wound up creating two copies of each table's classes, one with and one without foreign keys. That causes its own issues.
    • bloaf 9y ago
      Wait, it ran out of memory because it couldn't hold the class structure?
      • Someone1234 9y ago
        Essentially yes. It created giant object map with millions of objects in it and hit a memory limit (rightfully so, it wouldn't have been usable anyway). It would start at the initial table, get the siblings, then the siblings of the siblings, and so on until it was trying to generate an object for every property of almost every table we had (even if we only referenced the original table).
    • flukus 9y ago
      Sounds like you disabled lazy loading and that forced everything to be loaded at once. That's a user error, not a EF fault. This is a common theme I've seen with people blaming ORM's for being slow, it's the devs not using them appropriately more than the ORM's themselves. Not to say that they don't have their own issues.
• andrewstuart 9y ago
  I'd rather learn the ins and outs, problems and issues, highs and lows, of SQL rather than an ORM. ORM require just as much investment in time and even then you still need to learn the sql to get the ORM to do what you want it to do. SQLAlchemy on Python is a truly fine piece of software but in the end it was much simpler and felt more powerful for me to write the SQL. And not even hard BTW. I only have a limited amount of time available for learning and if I can trim out an entire class of technology (i.e. the ORM) then that's a whole bunch of stuff I just don't need to spend time learning.
  • mythrwy 9y ago
    I prefer SQL to ORMs as well for exactly the reason you state. It's more comfortable and it's less of a mystery. But you don't always get to pick what code you'll be working with and if you are working with others in a web framework pretty good chance you'll be learning an ORM anyway. Also really long SQL statements are no fun. Like debugging a whole program written in one line. I think sometimes there is a temptation to get excessively clever with SQL.
• nine_k 9y ago
  Object-relational mapping is in many cases an excessively leaky abstraction. I try keep away from it. A DSL for writing SQL in a nice, composable way is a useful thing. Some libraries, like SQLAlchemy, provide both levels, not insisting on using the object mapper.
  • jes5199 9y ago
    SQLalchemy is kind of a pain in the ass to actually use, though. The DSL doesn't feel very Pythonic, it's weird and confusing. The way it traverses the Object graph when loading associations between models is magical and opaque and I could never predict when it was going to automatically work and when it wouldn't. I actually would rather be writing Ruby on Rails, because it's _less magic_ than SQLalchemy.
    • lykr0n 9y ago
      True, kinda. I love SQLalchemy and it does feel different to use than every other python package I've used, but I'm not sure if I could do it better. It's pure magic.
    • sethammons 9y ago
      The one that bit me with sqlalchemy is joins. You have to structure your sqlalchemy object in a specific way to do joins. Usually I write my sql query then spend half an hour trying to convert it to sqlalchemy.
      • nine_k 9y ago
        Having PK relations defined was usually sufficient for me. I like SQLAlchemy for its straightforward mapping of SQL. I don't usually work on the "model" level, though; I work on "table" level.
    • nine_k 9y ago
      Yes, SQLAlchemy uses tons of magic. But my very point was not to make it traverse object graphs at all. You can write nice SQL using it, with selects, joins, functions, etc. All these parts, SQL clauses, are composable, so you can factor out common parts. Yes, I'm fine working with lists of tuples, not "model objects". Object graphs don't map all to well to the RDBMS model all too well. It's best done on case-by-case basis, if you care about performance at all.
• grangerg 9y ago
  It depends on what you're after. I'm very comfortable in SQL, and would prefer to write my queries. But our team has grown, and we've found that developers say they know SQL, but they really don't. In general, I've found that it's not the syntax that messes people up. There's a significant mental "jump" between the usual procedural coding paradigm and the "set based" paradigm offered by SQL. Some people just never catch on. So we're going to start using an ORM (Entity Framework Core 2) so that the "mere mortal" developers can pitch in. I know there's a way to run raw SQL, so I know that when we find spots where the ORM fails, we can just rewrite it with some good SQL if we decide that's best. But maybe that'll never happen? We've been trying to do simpler SQL stuff as of late so that the heavier lifting in the system is done by the application/client instead of the database (bottleneck). As the database sees simpler, less unique queries, more stay in the plan cache, indexes are more reliably hit as expected, and performance increases.
  • grv07 9y ago
    how your dev. can write optimal query with ORM if they are not good in SQL.
• default-kramer 9y ago
  I've found ORMs (such as Entity Framework) great for operations that can be described as "find a single thing by PK and update it." For read operations I've favored this strategy: "Imagine the ideal result set for the task at hand. Use SQL to deliver that result set. Do the rest of the work in your app language of choice." Edit: I guess I should clarify that I would favor using any library that maps result sets to lists of objects. And I would consider that to be part of "do the rest of the work in your app language of choice."
  • taspeotis 9y ago
    Yeah EF is great for CRUD. The way I distinguish whether EF is going to be used or not is simply whether the workload is OLTP or OLAP. At OLTP EF excels. It's terrible at OLAP (ORMs generally are) so I'll drop to raw ADO.NET and (if lots of data has to go in to SQL Server) table valued parameters. Use the right tool for the job.
• Shorel 9y ago
  I do agree every one who needs to get data from an SQL database should know SQL, and mostly, should know about indexes and how and why queries can be slow. But a query builder is extremely useful and in any complex application, if you don't use one, you end up bulding one yourself, which may not be a very good idea if you don't understand things like query injection. So learn SQL, learn ORM, and choose in a case by case basis.
  • sanderjd 9y ago
    I wish there was a commonly agreed upon name for "query builder" that isn't "ORM". Actually mapping relational data to objects often rubs people the wrong way, for good reasons, but the query-building layer underneath is pretty universally useful. But in general I agree with you: Why just learn SQL? Learn all the layers!
• ulkesh 9y ago
  I guess my experience is limited, but I’ve not seen much of this despite working in an ORM environment with around 75 entities for the last few years (my recent experience anyway, the rest goes back 16 years). Maybe that is small potatoes, I don’t know, but I’ve found that anyone who understands JPA well enough can work to avoid any pitfalls of using ORM. It seems to me that having a good mix of understanding SQL and ORM is a good thing; and especially understanding exactly what the ORM system is doing for you and how it is doing it. Dropping ORM altogether sounds like a bad idea since it provides a number of built-in security features as well as an abstract modeling paradigm that is fairly easy to conceive and maintain; provided, of course, that you learn to say “No” to protect the integrity of the model (such as rejecting the attribute creep the article warns about). I have found, in my experience, that people who tend to want to write SQL over ORM usually want to do so because they simply know SQL better. That’s okay, there is nothing wrong with that. But that doesn’t immediately mean ORM systems are bad. No need to be tribal about it. The problem I see is that many new software developers these days sometimes can’t see the forest for the trees because they dwell too much on what they think is better instead of simply seeing the software and abstractions as nothing more than tools in the tool belt. It happens everywhere — PC vs Mac, iOS vs Android, Scala vs Java, SQL vs ORM. It’s fine to have opinions, I have many, but as I’ve aged I’ve become acutely aware that my biases are almost solely rooted in the limitations of my understanding.
• randomdrake 9y ago
  "What ORMs have taught me: just learn SQL" Could be retitled: "I should have learned SQL before dealing with ORMs." Every single point the author brings up comes seems to come down to simple database design, lazy development, or not understanding their tools. They really don't seem to have anything to with ORMs or query languages. Any screwdriver can make for a bad hammer and some screws may go in with a large enough mallet. > Perhaps the most subversive issue I've had with ORMs is "attribute creep" or "wide tables" Normalization of data is required whether you're using a query language or an ORM to access it. The fact that ORMs make it easy to "hide" the fact that you've added 500 columns to a table isn't the ORM's fault. > Knowing how to write SQL becomes even more important when you attempt to actually write queries using an ORM. This is especially important when efficiency is a concern. What do they think the ORMs are doing? Magical incantations over the disks? The ORMs are just using queries too. You can write really horrifically bad queries in a query language and also abuse ORMs, but that doesn't make either one bad. Most ORMs can let you see precisely the SQL they are creating. If not, the database will surely log the queries for you and let you know what's going on. > The problem is that you end up having a data definition in two places: the database and your application. Welcome to the fact that we have multi-layered technology? There's always going to be discrepancies between the layers that have to be ironed out because no data designs are perfect or future-proof. The author then attempts to bring migrations into the picture as if database migrations are somehow just not a problem if you aren't using ORMs (hint: database migrations have always been tough even in very well-design systems). > Dealing with entity identities is one of those things that you have to keep in mind at all times when working with ORMs, forcing you to write for two systems while only have the expressivity of one. What this results in is having to manipulate the ORM to get a database identifier by manually flushing the cache or doing a partial commit to get the actual database identifier. Sounds like a pretty frustrating example, but I've worked with at least 10 different ORMs I can think of off of the top of my head and not a single of them required "manually flushing a cache" or a "partial commit" to "get the actual database identifier." I wouldn't write this up as being an issue with ORMs or that this problem would be magically fixed by only writing SQL either. > Transactions. Something that Neward alludes to is the need for developers to handle transactions. Transactions are dynamically scoped, which is a powerful but mostly neglected concept in programming languages due to the confusion they cause if overused. Transactions are pretty straightforward and I cannot agree with: "The concept of a transaction translates poorly to applications due to their reliance on context based on time." Transactions don't care about time at all. They care about order and making sure that things are completed in a certain series of steps. This actually translates very well to applications, especially when you have processes that take a long time, where you don't want something to happen unless another thing happens first. While a decently-written article, this comes across as someone who learned about ORMs more deeply than databases, discovered the flaws that ORMs have, and decided that query languages must be the only way forward. This ignores the fact that we created and adopted ORMs after struggling through years of rigid queries smattered throughout code. Writing bare queries has a time and a place, but ORMs have saved countless hours of development time, and allowed for vastly improved longevity of code. Don't throw the baby out with the bathwater.
• misterbowfinger 9y ago
  Ah yes - the proverbial "ORMs are bad, just learn SQL" post. This is analogous to saying "don't use frameworks". Sound ridiculous? Yes, yes it is. The law of leaky abstractions applies to many, many things, ORMs included. I would also argue they apply in different degrees, usually related to the design of the ORM (the post mentions SQLAlchemy vs. Hibernate, for example). But consider the following: 1) Why do people still use ORMs? Exactly. 2) Question 1 but s/ORM/framework_or_widely-used-library 3) ORMs allow you to develop faster, and deliver value 4) Beginners already have a hard time coding, designing, and understanding what they're doing. ORMs provide a nice abstraction over underlying data stores 5) Although fraught with peril, ORMs provide a common interface that'd give _some_ help if you switch data stores 6) Multi-line SQL statements are a huge pain in some languages
  • eru 9y ago
    ORMs are bad, because objects aren't particularly useful to deal with most data. And when you've already been through the effort to make your data relational for the database, might as well re-use that effort. That's not to say that SQL is the answer. Proper first class support for relations in your programming language / libraries is great. As for using a DSL, Datalog is worth a look.
    • wvenable 9y ago
      > ORMs are bad, because objects aren't particularly useful to deal with most data. Objects are containers of data; that's a crazy statement to make. The relational structure maps pretty cleanly to objects, properties, and collections. However, objects are not good for reporting. And the author mentioned doing 14 joins and hundreds of columns - that smells like a reporting query.
      • amarkov 9y ago
        The relational structure maps cleanly to badly designed objects, where most classes have 5-10 fields and most fields are opaque data values with setters and getters. If you wrote classes like that in isolation, you'd have to do some serious work in code review, explaining why a hodgepodge collection of properties is a single unit of responsibility. But I've never worked in an ORM-using system where they weren't endemic.
• zzzeek 9y ago
  > If you're using an RDBMS, bite the bullet and learn SQL. If this person spent all that time using Hibernate and then SQLAlchemy, and all that time did not know SQL, then their suffering and bad experiences make complete sense. You absolutely need to know SQL if you're going to use an ORM effectively. Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating query composition, providing abstraction for database-specific and driver-specific quirks, providing patterns to map object graphs to relational graphs, and marshaling rows between your object model and database rows - that last one is something your application needs to do whether or not you write raw SQL, so you'll end up inventing that part yourself without an ORM (I recommend doing so, on a less critical project, to learn the kinds of issues that present themselves). None of those things should be about "hiding SQL", and you need to learn SQL first before you work with an ORM.
  • manicdee 9y ago
    This is especially true where the ORM can mask things such as moving a column to an associated table. The ORM knows that object.attribute is now represented in the object_attribute table with the relationship using object_attribute.pk in the object.attribute column (which may or may not be renamed to attribute_id). No need to rewrite all your SQL, just the ORM description of the model!
    • Joeri 9y ago
      Realistically though, if you’re querying the same table from that many places your architecture is already in trouble. So editing the queries shouldn’t be that big of a deal.
  • mathattack 9y ago
    Everything that isn't SQL tuned to your environment forces you to sacrifice performance at some point. At some point it does an inefficient join. How can you not go back and fix performance issues is you don't know SQL? (It isn't like you need to fix it in C)
    • 9y ago
• jmadsen 9y ago
  An ORM is not a "query builder" An ORM turns your result into a collection of objects. THAT is what an ORM is for and about. The fact that they are built on top of query builders and lighten the load when developing is just a handy side effect.
  • default-kramer 9y ago
    To me, it hinges on what you mean by "a collection of objects." If it is just a list C-like structs, that's great. If it involves lazy-loaded child collections, inheritance hierarchies, or any kind of behavior at all, that makes me worried.
    • yen223 9y ago
      So true. This is the reason why I no longer think Django's ORM is a good idea.
    • simooooo 9y ago
      The lazy loading is my major annoyance. Makes it very hard to cache the resultant half-populated, stateful objects
    • spronkey 9y ago
      Yeah, I agree. Lazy loading in ORMs what happens when people want OO databases. And by OO databases, I mean everything to act like it's in an in-memory collection. Sounds nice, but in reality, latencies, networks, massive data sets, atomicity of operations, and a whole host of other annoyances get in the way. ... so it ends up being simpler and easier in the long run to be very explicit about your interactions with data stores. Took me a long time to get to this point.
• tuespetre 9y ago
  This topic has been done to death and the rate at which comments have been made since this was posted tells me that we either haven’t learned much of anything as a collective, or we just like to rehash the same talking points because... we can. Know how and when to use ORMs. Probably learn SQL first. Don’t believe that any shiny bullet is silver. Or in short: “No”
• qwerty456127 9y ago
  The first time I've seen an ORM I was fascinated by this seemingly beautiful idea. But I have quickly realized that it's almost useless in real life projects, plain old SQL seems just much much better. Now I don't understand why would anybody use an ORM actually. Also, basic SQL can be easily taught in as little as 10 minutes (I have been initially taught it at middle school during MS Office Query, Access and VBA class). An image of a programmer that can't use SQL (I don't mean advanced cases which can indeed be a bit tricky but these are far beyond the powers of any ORMs anyway) seems really bizarre to me.
  • meesterdude 9y ago
    > But I have quickly realized that it's almost useless in real life projects there are plenty of real life projects out there that would beg to differ
    • qwerty456127 9y ago
      Sure. I mean in all the real life projects I've seen from the inside, using ORM instead of SQL felt like roaming a forest on a gyroboard.
• jeswin 9y ago
  The post is from 2014, so I won't be too harsh here. The author's problem is with some specific flavors of ORM he's used, and shouldn't be generalized. Hibernate's expressiveness is/was crippled by Java itself. C# ORMs on the other hand are way better because they benefit from LINQ which adds queries natively into the language. Other more expressive languages have excellent ORMs as well. The objective of ORMs is not to replace 100% of your queries. That 10% might still require SQL or Stored Procs and that's fine. ORMs give you: 1. Type safe queries 2. Ability to refactor easily, click to rename prop 3. Not having to handcode joins if objects are related customers.select(c => { cust: c, orders: c.orders }) 4. Lazy evaluation and composition (C# examples) //If getOrders() returned a query expression getOrders().where(o => o.city === "London") //You extend it further getLondonOrders().where(o => o.total > 200) //^ These queries aren't executed yet.
  • rjbwork 9y ago
    I've been writing C# professionally for ~5 years at this point. While I was initially quite infatuated with LINQ2SQL and EF, I have gone through the same situation as this fellow. I just write SQL in-line using Dapper for parameterization/data mapping, and use stored procs when I need some of the more arcane features of SQL (merges, CTE, etc).
    • mattmanser 9y ago
      I've been writing C# professionally for ~12 years at this point. I'm extremely comfortable with SQL, the first startup I worked for for 3 or 4 years in the mid-2000s did amazing things with it and was extremely anti-ORM. We did things like write SQL that would automatically get translated into XML, which we'd combine with xslt to create dynamic pages. Yes, I've hit major problems with the EF (including one on Friday which was causing huge CPU and Mem spikes at the busiest time of our year). Yes, it's a PITA to debug the queries. Yes, it can do stupid things. Yes, some programmers can massively over-complicate it. But you can prise the EF from my cold, dead hands before I give it up. ORMs rock. It's such a huge time saver as long as you KISS and accept you have to drop down to SQL sometimes. And screw switching to Core until they've sorted out lazy loading. Lazy Loading can also screw you, but again, it's just wonderful when you use it right. I've also turned into a huge fan of Code-First and Migrations, having been against them at first.
• joshtopher 9y ago
  Learning SQL is all well and good but what am I supposed to do then? I can't stand writing SQL in string literals
  • syntheticcdo 9y ago
    Use a query builder, for example http://knexjs.org/ http://knexjs.org/ for Node.
• 3riverdev 9y ago
  (Bias: I'm one of the Hibernate ORM committers.) Hibernate (and presumably any ORM) was never intended to be a complete abstraction of anything-SQL. Like others have mentioned here, an understanding of SQL must be had before using an ORM. The ORM is one piece to the puzzle, not a shield to prevent you from having to touch SQL. One pattern I typically use is a take on CQRS: Hibernate for writing/updating/fetching/deleting a single instance of deeply-relational object model, SQL (I like jOOQ) for larger-scale fetches and any bulk actions. Shameless plug for a write-up I put together last year: https://www.3riverdev.com/hibernate-orm-jooq-hikaricp-transactions-and-spring-an-sql-cqrs-tutorial/ https://www.3riverdev.com/hibernate-orm-jooq-hikaricp-transa...
  • jgeraert 9y ago
    I'm so glad someone finally confirms my idea about using hibernate. I've been in constant battle with developers that map full object graphs coming Frome some endpoint to hibernate/jpa annotated classes and then throw it at hibernate. Here you save this... It just doesn't work that way very well. It's always a mess with relationships. Whereas you run your logic on entities attached to the session things work out nicely.
  • gwbas1c 9y ago
    I must admit that I've inherited two messes where someone used NHibernate as a replacement for SQL. In both cases, the schema was excruciatingly simple but the code performed excruciatingly poorly. In once case the project was canceled, in part because it was so late due to the developers not knowing how to use a database. In the other case I put my foot down and removed NHibernate. The schema was so simple that it was just easier to put a few extra minutes into boilerplate code than to put time into learning a new thing. I'd really like to see a good writeup about the use cases that tools like Hibernate excel at. The problem is that, in both cases, I had to work with a high-level manager who had very bad assumptions about what Hibernate can and can't do.
  • lmm 9y ago
    > Hibernate (and presumably any ORM) was never intended to be a complete abstraction of anything-SQL. Like others have mentioned here, an understanding of SQL must be had before using an ORM. Disagree. You need to understand the relational model, but you don't need to understand SQL-the-language. I've written plenty of successful systems using hibernate without needing to touch SQL, and am much happier for it.
• cryptonector 9y ago
  Some people, when faced with a database problem, choose to use an ORM on top of an RDBMS. Those people now have three problems. The problems are: the original problem, the expressive and performance disaster that is every ORM ever, and the layered and hidden RDBMS whose peculiarities nonetheless always find a way to leak through the ORM abstraction. Fun stuff. Do what TFA says: just learn SQL.
  • Demiurge 9y ago
    I've been using Django + Django ORM for a decade, and it has covered vast majority of database usage. Entire applications written with zero SQL, and clean DRY code describing data and allowing to use it in the code. And yet other application written with lots of SQL. The real question is, why anyone using an ORM not learning SQL, how the the thing they are mapping to objects actually works?
• atsjie 9y ago
  The most cringeworthy thing I heard about ORM's actually happened two weeks ago when I explained our use of a query builder rather than an ORM. The new senior developer was talking about speed (??? uhm… k...) and the benefit of being able to switch between PostgreSQL and... MongoDB. I just cringed up, didn't know what to say. Using the same domain model in an RDMBS as a Document Store? I really didn't know how to respond to that. On the other side you have juniors, just starting development and working with an ORM/ODM the first time. It's not uncommon for them to spend years in development while learning very little SQL at all, hardly understanding the database (apart from what they learn in school). I don't blame them. I hardly know TCP/IP at all and still I write technology for it everyday. But ORMs are a more leaky abstraction; a lot of complexity is made easier by just switching to SQL. More principal points: - Why do we want to abstract away further from the most important part of the business: data. Data usually outlives the applications built on top of it. - Best practice is to keep backend API's stateless and requests short-lived. ORM's promote the use of state. In client-side applications state is a much more interesting long-lived thing, but a backend API these days? It has actually become a lot simpler there since I've started doing development; few modern backend servers render views these days. I understand why the author (re-)embraced stored procedures but I never will. Blame the overzealous PL/SQL Oracle seniors I've met, god forbid any human bestows such complexity on it's fellow colleagues. It's also hard to automate tests for them and promote it through DTAP alongside an application, hence I keep putting all logic in the application. I can imagine if the language and ecosystem are very strongly geared towards ORM you shouldn't try to do anything else. That's just painful. But in most languages other than Java and C# I'd make a good consideration if you really need/want an ORM (coming from Java, I never took non ORM design seriously there, but perhaps I shouldn't have). Not sure where I'll stand in a few years time on this… but for now just happy with plain old SQL and query builders.
  • ldarcyftw 9y ago
    ORM stands for Object-Relational Mapping (wikipedia). It is just a way to map domain objects to tables. There is no "promotion the use of state" in that definition. It is your choice to start using state in a (mis)designed manner but please don't blame ORMs for that.
• westurner 9y ago
  ORMs: - Are maintainable by a team. "Oh, because that seemed faster at the time." - Are unit tested: eventually we end up creating at least structs or objects anyway, and then that needs to be the same everywhere, and then the abstraction is wrong because "everything should just be functional like SQL" until we need to decide what you called "the_initializer2". - Can make it very easy to create maintainable test fixtures which raise exceptions when the schema has changed but the test data hasn't. - Prevent SQL injection errors by consistently parametrizing queries and appropriately quoting for the target SQL dialect. (One of the Top 25 most frequent vulnerabilities). This is especially important because most apps GRANT both UPDATE and DELETE; if not CREATE TABLE and DROP TABLE to the sole app account. - Make it much easier to port to a new database; or run tests with SQLite. With raw SQL, you need the table schema in your head and either comprehensive test coverage or to review every single query (and the whole function preceding db.execute(str, *params)) - May be the performance bottleneck for certain queries; which you can identify with code profiling and selectively rewrite by hand if adding an index and hinting a join or lazifying a relation aren't feasible with the non-SQLAlchemy ORM that you must use. - Should provide a way to generate the query at dev or compile-time. - Should make it easy to DESCRIBE the query plans that code profiling indicates are worth hand-optimizing (learning SQL is sometimes not the same as learning how a particular database plans a query over tables without indexes) - Make managing db migrations pretty easy. - SQLAlchemy really is great. SQLAlchemy has eager loading to solve the N+1 query problem. Django is often more than adequate; and has had prefetch_related() to solve the N+1 query problem since 1.4. Both have an easy way to execute raw queries (that all need to be reviewed for migrations). Both are much better at paging without allocating a ton of RAM for objects and object attributes that are irrelevant now. - Make denormalizing things from a transactional database with referential integrity into JSON really easy; which webapps and APIs very often need to do. Is there a good JS ORM? Maybe in TypeScript?
  • atsjie 9y ago
    I've used http://bookshelfjs.org/ http://bookshelfjs.org/ but I'd stay away from it, only felt cumbersome. No productivity gain. It's built on top a query builder Knex (http://knexjs.org/ http://knexjs.org/) which is decent.
  • 9y ago
• mythz 9y ago
  You can do both. My preference is to use "Micro ORMs" which provide a thin, light-weight, typed RDBMS-agnostic API around the most popular CRUD operations but also allow you to execute custom parameterized SQL when you need to run more complex queries but still let you use their fast mapping to populate clean POCOs/POJOs to take the tedium away from forcing you to become a manual bookkeeper from extracting the results into a more manageable form. .NET has particularly nice support for developing typed ORM's by utilizing typed Expressions which lets you parse the syntax tree of the expression (instead of executing it) so you can generate the appropriate SQL that matches the intent of the expression. You can check out a live example of what that looks like for C# in: http://gistlyn.com/?gist=84129042921da413661c96545a63e541&collection=991db51e44674ad01d3d318b24cf0934 http://gistlyn.com/?gist=84129042921da413661c96545a63e541&co... Although the development experience is more productive using the rich intelli-sense inside any C# IDE. It's not just the Type Safety and producitivy that typed APIs offer, ORM's also provide built-in conventions for converting RDBMS types into the most appropriate language data type and their typed abstractions take care of generating the appropriate RDBMS specific SQL for each supported RDBMS. A lot of the stigma of using ORMs is from "Heavy ORMs" which constantly fight the leaky abstraction of mapping a Relational Data Model into a Hierarchical Object Model which I've never seen an implementation I've liked, they're always inefficient and expose APIs that make it difficult to know what SQL is generated or have any ideas which APIs perform hidden perf-killing N+1 queries behind the scenes. Many Heavy ORMs want to maitain entire control over the source code used to interface with your RDBMS. They should be separated from "Micro ORMs" which are loosely coupled so it only needs a DB connection a Type definition that matches the RDBMS table or schema that's returned where they provide a clear 1:1 typed mapping of an RDBMS table to your programming languages Type. The Types provide a contract your app logic can bind to and given they can map to clean disconnected POCOs/POJOs they can be reused to develop declarative, safe, typed Web Services that can be inferred from the Type's schema saving you the effort from having to implement it: http://docs.servicestack.net/autoquery-rdbms http://docs.servicestack.net/autoquery-rdbms as well as automatically generating the UI to query it: https://github.com/ServiceStack/Admin https://github.com/ServiceStack/Admin Disclaimer: I've developed the above. If your ORM is causing you friction by all drop down to custom SQL, but don't use Stored Procedures unless you've identified situations where they provide clear benefits over their trade-offs. They're essentially free text commands without the support or capability of a proper programming language that splits your logic from your system making it harder to reason about it in isolation that doesn't benefit from the investments around maintaining source code, e.g. development environments, source control, CI, static analysis & compiler feedback, fast unit testing, REPLs, etc. I wouldn't recommend using an ORM to save you from learning SQL, but rather to leverage ORM's to save the effort and boilerplate from interfacing your programming language with your RDBMS and provides an "in code" contract representation of your RDBMS Tables that your App logic can bind to.
• api 9y ago
  The problem with ORMs is that they're an attempt to hack relational models into languages that lack them. The thing nobody has ever done is add relations as first class citizens to a language. Instead we have only maps, arrays, and lists in our languages and we have to shoehorn richer data into that. That's why hierarchical document databases have had a resurgence. At least they match the data model if the language.
• tzahola 9y ago
  “What compilers have taught me: just learn assembly”
• Boxxed 9y ago
  My main complaint with ORMs is that the workflow goes like this: 1. Roughly imagine the query you want to execute 2. Map said query into whatever annoying interface the ORM actually exposes 3. Attempt to reason about its behavior because it probably isn't exactly what you envisioned in step 1 I would much rather just write the damn query in the first place.
• hota_mazi 9y ago
  What ORM's have taught me: be comfortable with both SQL and at least one ORM, and use each appropriately.
• esfandia 9y ago
  What's the current state of object-oriented databases? Any progress since good old Gemstone? Why was Gemstone unsuccessful? Cost? Performance? Tie-in with Smalltalk? Is there any promising OODB being used these days? Because now that would take care of the OO-relational impedance problem.
• exabrial 9y ago
  It's not my quote but it goes along something like in order to use a layer of abstraction effectively you need to know 1 layer deeper. Honestly what are you doing using an orm without knowing sql? The point isn't to hide sql, it's to automate repetitive tasks. Who goes to learn Angular.js without first knowing JavaScript or html?
• beagle3 9y ago
  Many posts say that an ORM is a good idea for the type safety / vulnerability / composibility / portability aspects. But that’s actually independent from the object mapping aspects - e.g. web2py provides all of the above in its DAL without the object mapping. It does sort-of abstract over SQL variants with Python query syntax, but no objects are involved. Nim’s Ormin is still in not fully functional but is providing the same in a static language with straight up SQL. My experience is that the O aspect of ORM is where it doesn’t help (and often gets in the way); if that’s your experience, consider DAL and Ormin
• Demiurge 9y ago
  This type article of article has come up countless times before. Why not both? Using both, you let the ORM handle regular, boring CRUD, validation, repeatable exercises, and write tricky joins, aggregates, function calls that ORMs don't do well in SQL. Every decent ORM supports dropping to SQL.
• gigatexal 9y ago
  Disagree on joins and foreign keys: normalized tables make for smaller tables in my experience. Sure you might denomalize for reporting but it’s best to normalize and then demoralize as needed not avoid foreign keys because you have to write a large number of joins
• kapv89 9y ago
  If you are working with Node.js and Postgresql, give tabel(https://github.com/fractaltech/tabel https://github.com/fractaltech/tabel) ORM a shot. It is an unconventional ORM that works with pure JS objects and arrays, instead of "Model" classes, and "Collection" classes, and what not. It has quite a few other nifty features too.
• EGreg 9y ago
  ORMs were pretty limited at the time I began writing my first platform, phponpie.com . I remember looking at Propel and Doctrine at the time. Since then, I've written our own ORM which works in both PHP and Node.js, but we're the only ones that use it. It's been battle tested, though, with millions of users and variations. I would say that many of the issues the author brings up were things we had to face, and we solved them. 1) Schema – the ORM should have a script to regenerate base classes from the database, so that your schema only lives in once place. The nice thing is, after that, your IDE can help you out instead of writing sql by hand. It can use your language syntax to catch unbalanced parentheses, and more. 2) Adapters – the ORM should be modular so you can hook in adapters for MySQL, PostGres, SQLite, MongoDB, and various key-value stores. 3) Joins – the ORM is supposed to be smart enough to describe relationships and automatically write the most optimized JOIN queries for you. For example $article->getTags() . You could, of course, implement this stuff yourself manually but it gets tedious, when the code could easily be autogenerated with stuff like $article->hasMany('tags', ...) kind of like this: https://qbix.com/platform/guide/models#relations https://qbix.com/platform/guide/models#relations 4) Insight – using an ORM makes you pass actual values in a structured way, instead of interpolating them in a string. Thus you don't make the catastrophic mistake of forgetting to escape them, allowing SQL injections by Mr Bobby Tables. Also our ORM can do SHARDING in the app layer, especially useful in Node.js where it can issue simultaneous queries to several databases and combine the results. Although I recommend using CockroachDB these days :) 5) Flexibility – the ORM should support fetching partial objects, but with the Primary Key so they can be saved back. Recently we even added support for vector-valued lists, something we needed for extra flexibility. 6) Transactions – the ORM should be smart enough to handle transactions, in fact support nested transactions on various shards. Since the database engine usually does not support nested transactions, you need to emulate that in the app layer. For instance, when you start a session, you might want to begin a transaction and lock the session for update. 7) Methods – objects which are fetched can have user-friendly methods added, like $stream->exportToClient() and so on. It's free and open source. Here are examples of usage: https://qbix.com/platform/guide/database https://qbix.com/platform/guide/database https://qbix.com/platform/guide/models https://qbix.com/platform/guide/models
• jaequery 9y ago
  Are there programmers these days that actually don't know SQL? I find that hard to believe, that's gotta be like a CS 101 course.
• robgibbons 9y ago
  > ...in order to use ORMs effectively, you still need to know SQL. 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. This is presuming that you will never come across ORM being used almost exclusively in any future projects. After all, ORM doesn't seem to be going anywhere (even with all the hate against it). One could make an argument that learning both effectively would provide a better general foundation.
• eecc 9y ago
  Yup, take those Repository designs, where you can’t join on two tables unless stars align very specifically. Of course there are very good technical reasons for that, but isn’t this because the abstractions are leaking onto each other? What’s the added value of the extra layer then?
• bmarkovic 9y ago
  It's "Active Record" style ORMs like Hibernate that are the culprit, and the way many developers utilize them to avoid any contact with the realities of RDBMs which leads to data access antipatterns which lead to poor performance (multiple needless queries per request etc.). Another thing people need to really give up on is the pipe dream of switching databases -- you're not going to do it. I've never seen one single case of people actually utilizing ORM to actually change RDBMs they store data in. Not all ORMs are like that and the best solutions are the ones like Knex/Objecion where an ORM (Objection in this case) is nice abstraction for single-object access/writing and underlying SQL builder (Knex) is fully exposed and used for everything else.
  • rickycook 9y ago
    i’d agree switching a current project from DB server to a different on is a pipe dtream, but the couple of use cases that are related but incredibly handy are switching DB driver mid project for any number of reasons, or using a new and unfamiliar database in a new project
  • toomanybeersies 9y ago
    At my previous job we practically exclusively used Activerecord (the Ruby version), we had maybe half a dozen queries that were hard coded SQL. We also managed to switch from MySQL to Postgres with about 1 minute of downtime on a 16 GB database. This was a reasonably mature codebase, about 3 years old.
  • NoGravitas 9y ago
    I think it's misleading to think about switching databases on an ongoing application as the use case for database independence. More important use cases: 1. Using SQLite for unit testing and a real RDBMS for integration testing, acceptance testing, and production. 2. When you are writing a library that will be used by different projects, not all within a single organization (e.g., a Free Software project). 3. When you are providing a product that the end-user may want to use with different choices of database (e.g. forum software, Nextcloud, etc.)
• dang 9y ago
  Large previous discussions: https://news.ycombinator.com/item?id=11981045 https://news.ycombinator.com/item?id=11981045 https://news.ycombinator.com/item?id=8133835 https://news.ycombinator.com/item?id=8133835
• groue 9y ago
  There are ORMs that address partial records, multi-threading troubles brought by lazy loading, uniquing, auto-updating records, and, importantly, put raw SQL on the same level as the query builder. I think of [Diesel](http://diesel.rs http://diesel.rs) and [GRDB.swift](https://github.com/groue/GRDB.swift/blob/master/Documentation/WhyAdoptGRDB.md https://github.com/groue/GRDB.swift/blob/master/Documentatio...).
• andybak 9y ago
  Like static vs dynamic typing and frameworks vs libraries this is an endless debate that rarely sheds much light on the topic. The people who use ORMs daily are likely to favour them and those that think they are the devil's work are unlikely to have intimate experience of a range of different ORMs.
• sametmax 9y ago
  Again, ORM haters are missing the point. It's not about avoiding to write SQL, it's about to have standardized API on which all your architecture can count. Why do you think Django was so successful ? Because it was built in a way that allowed a rich and powerful ecosystem to flourish. The Django ORM is not the best out there and it's doing plenty of silly things. If you don't know SQL and you use it you will be in a world of pain. However. Because Django features this ORM it can: - provide auto-generated forms from db model, outputting HTML and validating user inputs, saving changes automatically to the DB. - provide auto-generated CRUD views from the db model, that you can extend at will. - provide auto-generated admin - provide tookits and helpers to deal with your data: signals, various forms of getters, native object casting, advanced validation, better error messages... - provide entry points for extending the data manipulation API, in a generic way (fields, managers, etc) - provide tooling for migrations - provide auth and permissions - provide user input cleaning and escaping - automatically deals with value normalization: encoding, timezones, text/number formats, currencies... There is one entry points for those where you can put custom code, and you don't need custom code most of the time since somebody did the work for you more often than not. - ensure all django projects look the same, so that it's very easy to move from team to team or train people - formalize the schema, which became a the documentation and only source of truth for your data, that is commited to your VCS. Wannan know what a Django project is all about ? Check urls.py, settings.py and models.py. Done. The cherry for this cake is of course the fact 3rd party modules (so called django "apps") can leverage that, which lead to the amazing ecosystem Django has. - auto-generate REST views from model (eg: django-rest-framework), again that you can tweak as much as you want. - dozens of auth backends. - data manipulation (workflow, filtering, dashboard, analytics) that just work. - tags, search, comments, registration and all those stuff you alway rewrite otherwise. And because they all use the ORM, they are all compatible with each others. And they all work on Mysql, Oracle, SQlite and Postgres, like the entire rest of the framework, out of the box, for free. You want to do that in any other framework (except RoR) ? You'll get a lib that do half of it, and let the persistence and API integration work to you. And it will not play with others. And that will be integrated differently on another project. If you have a lib at all ! Oh, and you have to use the proper DB. If you are corporate or startup, it won't be the same one and you better hope the lib author is in your shoes. All that stuff is easy in Django because you have a centralized, easy to inspect, standard, shareable definition of each of your model in one place. That's what ORM are for. Not "doh, SQL is hard". Now you could get some part of those benefits by creating central models using schemas untied to the DB, such as marshmallow. It would be an interesting take, but my guess is that you will end up with interfacing it with your DB with some kind of layer, that would look like an ORM anyway.
• justaaron 9y ago
  yes yes yes! the point is NOT "learn sql" you already know sql, FINE! the point is: use sql, not orms! migrations are best done in pure sql, i'll contend that model-inflation is best done in pure sql also. another thing: if the format is json, that's already a nested "joined" blob of usable data! it's what the end result of a sql join would achieve, the client often just has to drill into that data blob and everything needed for the entity in question is already there!
• majewsky 9y ago
  > just learn SQL This is great advice in general for everyone in a role that even slightly touches on ops. In my day-to-day work, I frequently observe that knowing some SQL (esp. joins, and aggregate functions like SUM/MAX with GROUP BY and HAVING) turns you into some sort of mighty wizard for most people. They're trying to debug a problem in their service and not making progress for hours, and you just walk straight into psql, take a look at the schema, do a few SELECTs, and zoom in on the problem. Yet nobody seems to consider SQL a valuable skill. I guess it's not buzzwordy enough.
• jlebrech 9y ago
  when you learn how to use views and stored procedures you'll speed up your application so much. * and temporary tables, etc.
• yason 9y ago
  An ORM is both a more native way to represent data rows in the application and a convenient wrapper to automate much of dealing with SQL and result sets. But only a fool would wish to see an ORM as an object-based abstraction over SQL. Dynamic languages such as Lisp and Python can often make-do without a specific ORM layer because it's easy to stash data into lists and dictionaries. If you can read rows from your result set into a list, make dicts or structs out of each row, and pass around that list or its entries to various functions you've just implicitly implemented a LRM: lispy relational mapping. But sometimes it just fits to create objects out of rows. Simple wrappers will do for an ORM: the best kind always make it clear that you're just using a machinery to operate an SQL database instead of updating an object and then "saving" it back to disk in the end.
• ux-app 9y ago
  fuck ORMs. Here's the thing, the DB queries will make up less than 0.1% of your typical codebase. Spend a week and write a good fucking query instead of relying on your bullshit magic blackbox to do it for you. Even a shitty query is going to perform better than the 100s or (literally!!) 1000s of lines of ORM code which need to happen to generate your "select * from users" query. seriously. grow up. learn the amount of SQL you need to and napalm any ORM that comes within arm's reach. and if you're not sure about the SQL you've come up with then just go a few cubicles down and ask your DBA what they think. Chances are they'll write something 1000x better than you came up with an you'll have learned something along the way. win-win.
• red_admiral 9y ago
  My 0.02 BTC on the matter: Object-oriented programming 101 assumes that all your objects are in memory, in a graph, so you can do things like person.getFriends()get(0).getName() [assuming the person in question has >0 friends]. Each step in the graph is essentially a pointer dereference, costing a constant effort. (If your data is small enough to fit in memory, that's what you should generally be doing. People who use hadoop for half a GB of data are usually doing it wrong.) A relational database assumes that all your data fits on disk, but only a subset of it will be in RAM at any one time (and you generally have a network round trip every time you change that subset). This means you need a completely different way of thinking; this difference is sometimes called the "object-relational impedance mismatch". This is not to do with SQL and OOP just being different APIs for the same thing, they are designed for very different use cases. ORM tries to pretend that this difference doesn't matter, and works quite well in simple cases when it really doesn't matter. My standard example why it sometimes does matter: PersonDAO.fetchAll().size() is silly because it forces the database to fetch all Person objects, send them over the network, your application creates the necessary objects for them - and then you throw it all away again because all you needed was the number of people. PersonDAO.count() is much better, even if you have to implement it yourself. If you don't like the syntax of SQL, sure - use a query builder. In C# or Java you can even get some kind of type safety that way. But you need to understand the difference between an object graph and a relational database to use either of them efficiently, long before you get to advanced ideas such as window functions.
  • spdionis 9y ago
    > My standard example why it sometimes does matter: PersonDAO.fetchAll().size() is silly because it forces the database to fetch all Person objects, send them over the network, your application creates the necessary objects for them - and then you throw it all away again because all you needed was the number of people. PersonDAO.count() is much better, even if you have to implement it yourself. I mean, PersonDAO.fetchAll().size() is just bad code. You probably need to know how to write ifs properly before programming? You need to know about databases before using an ORM.
    • red_admiral 9y ago
• ganonm 9y ago
  In my current project (work) I've chosen to use JDBI for communication between model and database. For me it is the perfect level of abstraction whereby it still feels like I'm just writing SQL queries but also avoiding a lot of the boilerplate code that comes with having to manually manage DB connections. I still have to write the actual code that maps the result set to objects, but I've found this is a very small tax to pay, especially with Kotlin's data classes. I used an ORM in a previous job (RoR ActiveRecord). I didn't find it an altogether horrible experience, but there where many cases where we would get these 'leaky abstractions' in the wrong direction from the model -> database schema. There were also a lot of cases where we would realise that some ActiveRecord query we were doing was unintentionally loading entire tables into memory (our fault, not ActiveRecord's fault). This was usually remedied quite easily by just RTFMing the docs, but my feeling is we could have avoided it in the first place if we'd used a lower level strategy. Personally, when developing a new feature I always like to start by thinking about the database representation first and then working my way up. I think having this sensitivity to how it should be represented in the database can allow you to avoid many of the pitfalls that may come with using a more opaque ORM. Another valuable attribute I get from using JDBI is it's dead easy to mock (like heavyweight ORMs), so unit testing stuff that interacts with it is super simple.
• manigandham 9y ago
  One of the most endless and pointless debates. 99% of the time, an ORM is fantastic and will make you more productive while providing performance, security, and maintainability. They come in many sizes from thin wrappers around a db connection to full-featured frameworks. For the other 1%, use raw SQL, or perhaps a query building tool to help with parameterization, composability, etc. In fact, modern ORMs will even let you input raw SQL and handle the conversion back to objects if you need it. Saying ORMs are always wrong is just as dumb of a statement as saying all database access needs to be in raw SQL. They are just a tool and abstraction, like everything else you use in software development. You know the right time to use it. That being said, not knowing SQL at all means a lack of general understanding in how relational databases work and will almost always cause problems.
  • bunderbunder 9y ago
    All of these endless debates seem to boil down to two different groups who work in two different problem domains talking past each other. I've personally never seen an ORM lead to success in the long run. But I also work in a space where queries frequently end up involving something that ORMs typically don't handle well: merge statements and pivot statements, window functions, management of the lock escalation policy to fine-tune performance, temp tables... The list is endless. What I have not ever worked on is a relatively basic CRUD datastore. Which I realize is what most people are using databases for. So at this point, I'm putting my money on ORMs being a hole in one for that application. Because, otherwise, I just can't reconcile a statement like, "99% of the time, an ORM is fantastic" with the reality I'm living in. In my career, 100% of the time, when an ORM was present, it was invariably the single biggest piece of technical debt.
    • manigandham 9y ago
      Yes, you are talking the 1% of use-cases, which is probably more like 10% these days with more complex software. Most business apps are just CRUD, but if you're doing analytics queries and such with tabular/pivot/nested result sets, then an ORM isn't going to do much for you. SQL is the database interface so of course using it directly without abstraction helps you get all the power and control. I have seem some cases though where a query-builder with a solid DSL can be a good middle-ground.
• federicoc 9y ago
  ORM isn't a query builder and that's not related to learn or not SQL.
• nottorp 9y ago
  You can write more efficient C(++) if you know assembly, even if you don't write anything in assembly. You can write more efficient python if you know C(++) and the various tradeoffs between data structures, even if you don't write any C++ and don't do any data structure by hand in the current project. ... You can use an ORM more efficiently if you know SQL. Same thing isn't it? That assumes the ORM doesn't completely get in the way, of course.
• sreque 9y ago
  I really enjoy seeing this debate because I hope more anti-ORMers unite and we see less use of ORMs in future projects. There are other disadvantages of ORMs that I haven't seen mentioned much: 1) low-level performance. Even if, and it's big if, you manage to get your ORM to generate somewhere near the optimal query, ORMs in my experience are always significantly slower than hand-written sql. When I last benchmarked, I couldn't get hibernate to be any better than 4x as slow as JDBC, and keep in mind that's pure CPU overhead. Think your service is I/O bound? It's probably not, and it's probably your ORM to blame. This may be less of an issue for a dynamic language like Python, but I see it as a much bigger issue for Java/C# and friends. 2) Debugging/understandability. Did you know that hibernate maintains a cache of every object you load in a session until you flush it? I didn't, until we had an outage because our service OOM'ed while loading too much data without flushing. Do you know how exactly your ORM is loading and saving data and when? Depending on your use of the various lazy-loading and storing features of your ORM, it can be very difficult to reason about when and how your ORM is talking to your database. Do you know how your ORM is integrating with your cache, which is likely memcached? Why is your ORM integrating into itself the concept of a cache in the first place? In my experience, hibernate gets caching wrong, and that's not entirely its fault. It's difficult to get caching right in the general case. But I would rather be forced to think about caching up front and get it right for my use case rather than try to understand how Hibernate is doing it and working around its mistakes and limitations. The common theme is that the use of an ORM makes it incredibly more difficult to understand, reason about, and debug your application rather than using a simpler library. In my experience, this alone makes an ORM not pull its weight.
  • huherto 9y ago
    I kind of agree with you. Using an ORM saves your from having to implement a lot of code. But you still have to understand how everything works. ORMs makes it look easy, but there is a lot of magic involved that you need to understand sooner or later.
• jdc0589 9y ago
  I have had exactly one positive experience with ORMs: nHibernate + fluent nHibernate config with .net's IQueryable expression interface. That is the ONLY time I have used an ORM and felt like I had enough control to do what I needed for edge cases and still the a perceived productivity boost; granted, there was what seemed like a large learning curve at the time. That said, I must prefer to just write queries myself and use an object mapper (Dapper, etc...) to eliminate a lot of the boilerplate. Maybe there are some amazing ORMs for other stacks. But my time with .net was the only time I heavily utilized sql DBs.
• keredson 9y ago
  Regarding the article's point about managing your schema, there are only two real options IMHO: 1) "The database schema is the official definition." Programmatically generate what ever ORM objects (at build time) in a 1-to-1 fashion from a schema dump. This is the approach DKOs use: https://github.com/keredson/DKO https://github.com/keredson/DKO As long as the code generation step is done as part of the build process, you'll have none of the normal code generation headaches, and your build will fail if you've made a code incompatible schema change. 2) "Your ORM objects are are the official definition." And generate the schema definition automatically. The common process of this is that most "generate schema" functions are stupidly lazy, and drop the work of calculating the diff from an existing schema on the developer (forcing them to write migrations). This is unacceptable in my eyes, just as it would be if my version control software wanted me to write my own diffs by hand in order to make a commit. I strongly prefer automatically generated diffs, like in https://github.com/keredson/peewee-db-evolve https://github.com/keredson/peewee-db-evolve. So you can do non-destructive schema changes. It's a model I've re-implemented for any new ORM I wind up using.
  • cwbrandsma 9y ago
    There is a 3rd. You define your own data structure (in JSON as an example), and generate everything else off of that. I've done this a couple times (as well as the two items you listed), and this has worked the best for me.
• lmm 9y ago
  > But the damn migration issue is a real kick in the teeth: changing the model is no big deal in the application, but a real pain in the database. After all, databases are persistent whereas application data is not. ORMs simply get in the way here because they don't help manage data migration at all. If you ORM doesn't help you manage data migration, get a better one. Any ORM worth its salt should let you generate migrations; at least Django and Hibernate (+ Liquibase) can. I find the key to making everything work nicely is to let the definition in the application/ORM be the source of truth for what the DDL looks like. If you want a particular SQL table layout, figure out how to tell the ORM to generate it. If you try to retrofit an ORM onto an existing table schema (which it seems is this author's preferred approach), you're in for a world of pain. > These two things don't really get along because you can really only use database identifiers in the database (the ultimate destination of the data you're working with). > What this results in is having to manipulate the ORM to get a database identifier by manually flushing the cache or doing a partial commit to get the actual database identifier. Use UUIDs. Generate them in the application, but use them directly as identifiers (pkeys) in the database. > Something that Neward alludes to is the need for developers to handle transactions. Transactions are dynamically scoped, which is a powerful but mostly neglected concept in programming languages due to the confusion they cause if overused. This leads to a lot of boilerplate code with exception handlers and a careful consideration of where transaction boundaries should occur. It also makes you pass session objects around to any function/method that might have to communicate with the database. > The concept of a transaction translates poorly to applications due to their reliance on context based on time. As mentioned, dynamic scoping is one way to use this in a program, but it is at odds with lexical scoping, the dominant paradigm. Thus, you must take great care to know about the "when" of a transaction when writing code that works with databases and can make modularity tricky ("Here's a useful function that will only work in certain contexts"). Use a monad to represent "this function has to happen in a transaction", then all those problems go away.
• agentultra 9y ago
  In a recent project we went without an ORM. We took a tack similar to how PostgREST and PostGraphQL are structured. We use views in the public schema to build our objects. Functions constrain our mutations. Triggers respond to events and maintain consistency. It makes our web API code simple and hard to introduce errors that invalidate our customers’ data. Don’t miss having an ORM. Always seemed like more abstraction and complication than was necessary given recent advances in servers like Postgres.
  • steve-chavez 9y ago
    Why didn't you just use stock PostgREST, in which area did you found it lacking?(Curious)
    • agentultra 9y ago
      The reasons were more social than technical. I think PostgREST is a wonderful piece of software and not lacking in any regard that I am aware of.
• mantasDeveloper 9y ago
  What a cry-baby. Show your code! 95% that you are doing things wrong.
• gfiorav 9y ago
  This article is valuable since it's raising some interesting pitfalls it's good to know and avoid. That being said: you need to use the right tool for the job. It's just hilarious how people expect the new "foo framework/paradigm" to solve ALL the problems... jeez! It's nice to know and understand new paradigms, but you really need to evaluate your case. ORMs took away the complexity of 90% of web apps. All the "Model X has many model Y". If you step outside of that realm with the ORM then you're officially "fighting the framework", and bad things will happen.