7 ms·
It's critical to have a good understanding of SQL, but once you do, ORMs with a functional syntax solve this problem for many of us. I know it's not possible fo
by caseymarquis 9y ago
It's critical to have a good understanding of SQL, but once you do, ORMs with a functional syntax solve this problem for many of us. I know it's not possible for some people to use ORMs as they can't risk the ORM making a performance mistake, but I think the solution is to improve ORMs to the point that writing raw SQL is akin to writing assembly instead of using a higher level language.
That said, we're not there yet. Using an ORM in 2017 without understanding what SQL you want it to produce IS NOT OKAY. Please learn SQL if you're going to use an ORM, and understand the SQL you are producing with the ORM.
Summed up, if you can, use an ORM to solve this problem.
- vinceguidry 9y agoFor anybody on Rails, you can call .to_sql on any ActiveRecord::Relation and it will return the exact SQL string that your code is producing. Really handy for troubleshooting, especially for associations.
- meditationapp 9y agoOf course, not all operations return a relation, such as count, update_all, delete, etc, even if they have a sql equivalent.
- zzzeek 9y agoYou can have functional SQL query languages that map directly to SQL without object oriented row mapping. Everyone can use these tools without performance issues. Disclaimer: i wrote SQLAlchemy.
- nerdponx 9y agoIs that really a disclaimer, or just the source for your claim? I'm sure you know this, but there are actually several "query builder" tools out there for Python, including SQLAlchemy Core. I'd love to see a side-by-side comparison.
- lobster_johnson 9y agoHe probably meant "disclosure", not "disclaimer".
- mshenfield 9y ago> You can have functional SQL query languages that map directly to SQL without object oriented row mapping. Well said. I always think of this as the canonical example: http://sqlkorma.com/ http://sqlkorma.com/ I don't know what rock I've been under, but didn't realize SQLAlchemy had the same idea w/ SQLAlchemy Core.
- Dangeranger 9y agoAnother excellent example of this is Elixir's Ecto library for composing SQL from modular fragments https://github.com/elixir-ecto/ecto/blob/master/README.md https://github.com/elixir-ecto/ecto/blob/master/README.md
- virtualwhys 9y ago> without object oriented row mapping Korma seems to do exactly that, N+1 lazy loaded queries included. SQLAlchemy, Esqueleto, Slick, Quill, and the like all generate precisely what you tell them to (i.e. queries semantically the same as what you'd write by hand). This is not to disparage Korma, it looks pretty cool, I'd certainly give it a look if I worked with Clojure.
- mshenfield 9y agoAgreed. The ORM vs raw SQL debate is endless, but I prefer using some kind of ORM-y thing when I need to interact with a relational database from a program. There are still cases where you might build a collection of raw queries (think business analysis using Hive/Impala/Spark SQL). I think it's important to approach it the same way you would a normal program: how can I make my intention clear, how can I verify it works as I expect, how can I reuse without reducing clarity. Also, just a little joke: https://i.imgflip.com/1rhhzl.jpg https://i.imgflip.com/1rhhzl.jpg
- weberc2 9y agoQuery builders are a sort of middle ground between raw SQL and ORMs. They give you database agnosticism and help to prevent syntax errors, etc, especially if your language is statically typed. But they also allow you control over the query performance.
- enjoiful 9y agoI love Django -- but what it does is mostly magic to me. That being said... I'm fairly competent in being effective in Django -- although not for advanced and efficient querying. I want to get strong in SQL -- where/how do I start?
- wesd 9y agoStart by having a strong understanding the various joins first: http://www.sql-join.com/sql-join-types/ http://www.sql-join.com/sql-join-types/
- NumberCruncher 9y agoI used w3schools for learning webdev related things but their SQL tutorial seems to be good as well. [1] https://www.w3schools.com/sql/default.asp https://www.w3schools.com/sql/default.asp
- walshemj 9y agoIf you can get an employer to pay for it the Into course that Oracle does is fairly good it certainly got me up to speed to be able to work on an oracle project.
- AlisdairO 9y ago[self plug] Take a look at https://pgexercises.com/ https://pgexercises.com/ . It's a learn-by-doing set of SQL exercises. Focused on Postgres, but the large majority is standard SQL.
- 3131s 9y agoWrite raw SQL queries. My feeling on the Django ORM is that it's useful for defining models and managing migrations, but I prefer to write raw SQL for all queries. The PostgreSQL documentation is really good too.
- collyw 9y agoThe ORM takes a whole lot of pain out of updates as well. Filtering also work fairly nicely for simple stuff, as putting conditions in a dictionary and passing it to filter(kwargs) is a lot nicer than messing about with SQL strings. I agree that the ORM is limited if you want moderately complex queries though.
- paulddraper 9y agoI see this opinion a lot. I don't quite understand it. Your DBMS isn't some ambivalent data store with simple universal semantics. I'm not just talking about SQL features like complex subselects or CTEs, but index hints, lock order, transactional visibility, non-trivial column constraints, index locks... These matter in appreciably sized public-facing (e.g. web) systems. Granted, with enough transactions and roundtrips to application logic, an ORM can mimic these. Now, I do know YAGNI; you might never get to a scale where an ORM poses a limitation. I just suppose I don't see large enough benefits of learning and using an ORM in the early stages, to outweigh the cost of switching to SQL in the (admittedly hypothetical) later stages.
- richardlblair 9y agoI think your post makes a lot of assumptions. I do not believe scale to be a reason not to use an ORM. If you pay attention to what you're doing you can scale a system that uses an ORM just fine. The orm is usually not the problem, it's usually that people don't know what they are doing and introduce serious performance issues.
- tfigment 9y agoMy db issues for last ten years have frequently been a mix of bad query plans and terrible orm layers. Doctrine in Symfony rarely yield good queries in mysql for us. We've been moving away from orm where possible because we don't need to support multiple databases. Further if I switch my db to postgres I'm changing out everything else anyway to remove php from the codebase. I didn't write the original code but my team has to maintain it. The orm makes that hard actually compared to staight sql in the end.
- weberc2 9y agoAs I understand it, the point of an ORM is to make relational data look and feel more like the objects in my program; I shouldn't have to think about the consequences of my data living on the other side of a network connection. Even if that isn't the official point, it's at least a widely-held misunderstanding. If you're going to write code that uses the ORM this way, you're going to have a bad time, but if you write your code intelligently, you end up using your ORM as a glorified query builder. I think an ORM makes more sense in a functional language since the interfaces are more likely to be declarative than imperative. For example, an in-memory List and a SQLQuery can both implement a filter() method that takes a function and applies it in the way most efficient to that data structure; in traditional OO languages, we usually loop over our in-memory structures explicitly, and doing that with a database is prohibitive.
- NumberCruncher 9y agoEven if you work with an ORM instead of writing raw SQL you should know when single row processing is OK and when bulk load / data set processing is a must. My last employer lost 5M+ euros (not reveue but profit) every month because of excessive use of PL/SQL single row processing.
- mistermann 9y agoHow could they lose that much $ due to single row processing? Having to buy that much more processing power?
- deleted 9y ago[deleted]
- weberc2 9y agoI think the solution is to upgrade SQL the language. There's a very low complexity threshold after which you have to trade readability/composability for decent performance (in other words, there are a lot of cases where a human can notice a permissible optimization, but a database can't because it might break correctness for some odd edge case). We're solving that problem with programming languages (notably Rust); I'm not convinced that there's something inherently different about relational databases.
- lgas 9y agoProject M36 is a database that implements a relational model and corresponding query language (TutorialD) based on (among other things) the ideas from Date and Darwen's Third Manifesto. TutorialD solves many of the problems present in SQL, especially around composability. https://github.com/agentm/project-m36 https://github.com/agentm/project-m36
- weberc2 9y agoI've heard of tutorial D before, but I haven't taken the time to learn it as I'm off the impression it isn't widely deployed. :( I'd love to hear any first hand experiences though!
- gregmac 9y agoI disagree about SQL being like assembly. It's a fairly high level language, because underneath the engine is figuring out how to use indexes and actually run your query. If you want to draw parallels, ORMs are more like writing code in a template language that's written in the language you're actually working in. I've spent far too much time fighting with ORMs trying to get the SQL I want generated. Additionally for complex queries, I often develop in a SQL manager interface, because it's so much more direct. When I'm done, I have a working SQL query - I can paste it in to my code and parameterize it, why should I spend even more time fiddling with an orm? I like "micro ORMs" that just map rows to objects. Generating INSERT and UPDATE statements is ok, and single table selects with a single where clause are sometimes ok. Beyond that, I'd rather just skip the middleman. That said, for small databases (a GB or two) that are reasonably well designed (e.g. 3NF), the speed of current I/O and optimization engines in databases is such that even the worst SQL will generally run fine - so if you find it faster to use ORM and understand the future pitfalls, go for it.
- paulddraper 9y agoAgreed. If anything SQL is declarative and more high level than most application code. FYI, if you have a 1-2GB database, that whole sucker fits in RAM, so rejoice.
- koolba 9y ago1-2 GB? You can have a 1 TB fit into RAM these days. (And many of us do!)
- gfody 9y agoI've found views, parameterized-views/table-valued-functions can be a nice compromise to keeping the SQL relatively simple and thus ORM friendly without sacrificing performance.
- weavie 9y agoThis can be dangerous if over used. It can be very tempting to quickly pull in a 'left join blah' to grab some fields you may need. But that blah view could be hiding some massive performance sapping beast. When it comes to optimizing a slow running query, having to step through layer after layer of nested view can be incredibly challenging.
- lucio 9y ago>but I think the solution is to improve ORMs http://blogs.tedneward.com/post/the-vietnam-of-computer-science/ http://blogs.tedneward.com/post/the-vietnam-of-computer-scie... Not trying to attack you, just a cautionary tale that you should read
- vladsanchez 9y agoORMs are for people who don't know SQL.
- specialist 9y ago...yet.
- Daishiman 9y agoSQL is for people whose problem domain is so simple that they don't need to do things such as, I dunno, compose queries.
- flukus 9y agoI agree, but all too often composable queries are for people that don't know who their users are or what the want. If you build workflow centric UI's then the need for composable queries generally goes away.
- throwaway91111 9y agoSome ORM patterns are great, others suck. For instance, the activerecord lifecycle callbacks can be very difficult to maintain and reason about, particularly after_commit hooks and transactions that create associations.