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.
FYI, if you have a 1-2GB database, that whole sucker fits in RAM, so rejoice.
And the biggest advantage of an ORM is that you can trivially switch between databases which is often required for running automated tests. SQLite or H2 for development and then Oracle, SQL Server or Teradata for production is a very common pattern I've seen at many companies.
When it comes to optimizing a slow running query, having to step through layer after layer of nested view can be incredibly challenging.
create function thing (a, b) returns table as select * from table_1 left join table_2 on ...
select (only cols from table 1) from thing(a, b)
..and table_2 is not accessed
predicates from the topmost query will be pushed down to the function's query as well so the functions performance is generally equivalent to the adhoc version.
on the whole I find this strategy to be very effective at reducing the complexity of adhoc queries without performance penalties. in the case of indexed views it can greatly improve performance.
Unless you're fetching all the unneeded fields as well, in any decent DBMS both approaches would result in the exact same execution plan with the exact same performance, the unneeded parts/joins of the view wouldn't be executed.
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.
The orm is usually not the problem, it's usually that people don't know what they are doing and introduce serious performance issues.
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.
My opinion is that one uses an ORM for different situations than they would use SQL. Many applications can use an ORM and nothing else. Some can use an ORM for basic CRUD tasks and then raw SQL to do analysis or reporting.
It's also available standalone as "DataGrip", though I've never used it:
For me, that's Scala JVM on the server, ScalaTags/ScalaCSS/ScalaJS in the web browser, Slick for the ORM, and SBT for the build system. The bliss of learn once, program anywhere.
When this hits reality though, I use Relate https://github.com/lucidsoftware/relate (disclaimer: I'm a contributor)
It's SQL, but with minimal syntactic overhead.
sql"SELECT * FROM users WHERE email IN ($emails)".asList[User]()[1]: https://www.playframework.com/documentation/2.5.x/ScalaAnorm...
Disclaimer: i wrote SQLAlchemy.
Well said. I always think of this as the canonical example:
I don't know what rock I've been under, but didn't realize SQLAlchemy had the same idea w/ SQLAlchemy Core.
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.
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.
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
http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...
Not trying to attack you, just a cautionary tale that you should read
I want to get strong in SQL -- where/how do I start?
First thing to do would be to start printing the queries generated by the ORM to see what it is doing (though it produces some fairly verbose SQL, but its usually easy enough to understand).
Then when you are asked to get some one off numbers out of the database try doing it in SQL. In one query.
Its usually possible but takes a different way of thinking - in sets. Build up queries gradually. Start with the main table. Join in the next table and see the results. Add some conditions in your where clause or join conditions. See what the result is and how it changes.
As for thinking in sets, that is easily said but difficult to translate into words.
I guess describing the set of data that you want back from the database is a good start. My previous team leader would always describe the results in terms of "if x then y" (imperative thinking). When you are find yourself doing that, instead try to describe the data without the "if" statements and instead describe it as "the set where condition x and y are met". That will get you halfway there. Once you start thinking like that you will start to see the beauty in the relational model.
I was a good few years into my career before I started thinking this way - now I try to do as much work in the database as possible - it avoids a whole categories of bugs, usually keeps your code shorter and is one of the easiest ways to improve performance.
The PostgreSQL documentation is really good too.
So it's a perfectly fair comparison.
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.
If you're doing this you're missing out on the biggest advantage of the ORM, which is that it allows you to compose queries.
Using the Q objects, query expressions, and custom Queryset objects, you can filter objects by pretty much every imaginable criteria out there. This would be horrendously difficult and error-prone writing plain SQL.
The Django ORM, despite having limitations if you want to run analytics (and that's really a use case for with plain SQL excels), generally writes out exactly the same SQL I'd write by hand.
I do it all the time :) I love looking at long SQL queries that are formatted nicely and use good naming conventions.
It's also part of a more general philosophy I have of reducing dependencies whenever possible. With Django it makes sense to use the ORM wherever it suits you, since it's already built in, but there are times when you need to know raw SQL anyway (e.g. try populating a large database without COPY, only using the Django ORM).
1. I suggest the PostgreSQL documentation itself (https://www.postgresql.org/docs/current/static/). It's well-written, but still wordier than need be, and if you try to read it from start to finish, you will probably die. However, the beginning chapters are overviews of SQL. Once you feel in the mud, you are probably in very specific, technical chapters. You can just skim these, jump to ones that interest you at the moment, etc.
2. There also a separate wiki (https://wiki.postgresql.org/wiki/Main_Page). I haven't gone there much, but it is a nice complement. It has practical summaries of things that the main documentation spins out of control (like setting up replication). It also has some examples of how to to do really advanced things in SQL.
3. A well-reviewed, short book. The first book I read was SQL Demystified. It's a short and easy intro to SQL in general. Find something like that. I have tried hard to find good books on SQL, but most are huge tomes that will crush your soul.
4. Practice. For the past decade at my job I have been forced to learn complex SQL for various business reports, across a variety of tables. And I still feel like an intermediate.
My last employer lost 5M+ euros (not reveue but profit) every month because of excessive use of PL/SQL single row processing.
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.