4 ms·

Besides raw performance, why would I ever want to write SQL instead of using some type of ORM or query builder??

• pizza234 5y ago
  At least in Rails, DDL migrations syntax is not really part of the ORM syntax; it's something separate, that mimics SQL. I recognize and support the advantages of using an ORM in application code, but I find the migrations syntax (which, again, is different) a useless cognitive burden.
• cogman10 5y ago
  When you write SQL yourself you get 2 major advantages over (many) ORMs and query builders. 1. There's no surprises in the SQL generated (or when it's generated) and when you are interacting with the DB. In the worst case for ORMs, simply changing a field on a ORM controlled model can result in IO with the DB. That can be pretty surprising 2. SQL, once learned, is fairly straight forward and easy to read. Once you are comfortable with it, even complex CTEs don't take too much effort to grok. I'd argue that there are minor readability gains from ORMs (in general). The biggest value add to ORMs is integration with things like intellisense. However, IDEs like Intellij are becoming context sensitive such that you can still get that intellisense even when writing SQL in something like Java. Right tool and job and everything. However, writing SQL is often treated as if it were assembly or something. SQL is not that. It's a fairly high level DSL for set operations.
  • hpen 5y ago
    Nice so I actually do know SQL somewhat and use it extensively at work. I have started using other options in my side projects and found it to be more streamlined. Just curious where ORMs fail really.
    • cogman10 5y ago
      I definitely understand the draw of ORMs. The big issue with SQL in most programming languages is the pretty large impedance. It's like writing go in a Java source file. Doable, yes, but not something anyone really likes to do. However, ORMs can easily cut the other way. So, it's all about making sure you know what you are doing.
  • nhoughto 5y ago
    Yep, a migration script (if you have data you care about) is very important, it should be reviewed before being excluded. It has the power to blow away your data if done poorly. So having an explicit SQL statement (whether hand crafted or generated and exported) that is visible is super important, it means you are thinking about this important step, so its visible and harder to forget. The classic problem with letting your ORM manage migration is if you are updating your database entity model as part of a wider change, in your ORM and you rename a column, does it know its a rename? or is it going to DROP COLUMN + ADD COLUMN? and lead to data-loss? To cover these cases and all the infinite other permutation you end up needing to give the ORM more and more state about the past, making it just a more complex migration. Making the migration explicit SQL and decoupled from the ORM forces thought, a human is unlikely to write DROP COLUMN..
• saurik 5y ago
  The language tends to be more powerful and make more sense (specifically than the craziness of ORMs, not vs. query builders that often at least try to have reasonable semantics), and despite most of my friends who love ORMs seemingly having to keep re-learning how to use a database every few years due to whatever platform fad everyone is rabid about (both due to shifting frameworks and libraries), my knowledge of SQL has managed to just get stronger over the past quarter century of my using SQL. The mental model you get from actually specifying the query you want is also, I will claim, really important: I believe it helps you decide on what the correct API even is to access your data, such that when you later attempt to add abstractions or move the bits around to different machines/locations, it becomes easier to manage. I'll make a second (though I do believe "weaker" point): you are frankly likely to want to port your entire project at some point to some new framework / language, and if most of your system's behavior is just gluing SQL statements together with input models, that's going to be super easy. (The counter-argument, meanwhile, always feels really lame to me: "you might want to change your database at some point". Not only do people effectively never do that, but somehow even projects that use ORMs almost never achieve true database agnosticism either. Meanwhile, such a change is something that seems to come up a lot less often than "the awkward framework I'm using is now considered deprecated", in the grand scheme of things that are almost no database solutions that have ever mattered anyway, and the differences between them are sufficiently small vs. the differences between ORM framework concepts that porting has never felt like that big of a deal, unless you are literally going all-in on some database-specific component in which case there was likely a reason you were doing that and the ORM likely wasn't going to be useful anyway.)
• davidhyde 5y ago
  An ORM (object-oriented mapping) is, by its nature, a wrapper on top of a language designed to work with relational data in third normal form (think of this format as reducing data duplication). Objects are more like graphs. Developers like to abstract above complexity and are often tempted to skip learning SQL altogether when using ORMs. You can get away with this when there are low volumes of data or database operations map cleanly with basic CRUD program operations. To answer you question though, using SQL is not just about raw performance, it is also about not having to copy large volumes of data from the database to the application server for processing. So in that regard it saves memory. The reason for this is that not all ORM operations can be cleanly mapped to SQL. SQL is a simple language and it is sometimes easier to reason about what the developer is trying to do rather than reading application code. It is also sometimes easier to run stand-alone sql against the database so it's useful for troubleshooting. Of course there are many downsides but you asked for some good reasons to use it :)
• LAC-Tech 5y ago
  Because it's easier.