The main point that one should know SQL as much as possible before using ORMs, I agree with fully. The point that applications should be written by quick-prototyping with an ORM, then replacing the ORM entirely with raw SQL, I could not disagree with more. Since he is using my own ORM (SQLAlchemy) as his example, I'd like to point out (as many of you know I always do) that SQLAlchemy's entire approach is one of exposing the relational model at all times. There is virtually no need to drop out of using the Query and/or select() constructs into raw SQL strings, as these constructs can represent SQL fully and more or less directly, not really any different in concept than when the relational database itself parses an incoming SQL string into a tokenized parse tree internally. The results returned are tuples. If your tuples happen to line up with the attributes in your object model, then you can tell it to get objects back. There is no sharp red line between "I'm using an ORM!" vs. "I'm using SQL!". With a mature tool like SQLAlchemy you're using <i>both</i>, and the tool is there to <i>automate</i> your work with SQL, not to replace it.<p>So of course, learn SQL as fully as possible. But I recommend using an ORM that allows you to make full use of your SQL knowledge at all times.
For a while I used to ask interview candidates to explain the difference between WHERE and HAVING, to see if they'd ever done anything beyond the basics. I'm still not sure if that's too hard, but people who could answer it did tend to do much better in the rest of the interview as well.
If you really want to master non-trivial SQL learn to think about filtering, joining, and combining data via set theory. Once you can logically break down the set you're trying to get to the rest then just becomes learning/looking-up syntax.<p>The other big piece of advice is the tried and true incremental approach. The more complicated something is, the more likely I am to use the SQL client the way one uses a REPL and incrementally write the query:<p><pre><code> 1. Write basic SELECT
2. Add another clause (WHERE filter, GROUP BY, etc...)
3. Execute (syntax/sanity test)
4. Finish or Goto step 2
</code></pre>
Just like everything else in programming it's amazing how much simpler things are when you just piece them together one step at a time.<p>[1]: <a href="https://en.wikipedia.org/wiki/Set_theory" rel="nofollow">https://en.wikipedia.org/wiki/Set_theory</a><p>[2]: <a href="http://seanmehan.globat.com/blog/2011/12/20/set-theory-and-sql-concepts/" rel="nofollow">http://seanmehan.globat.com/blog/2011/12/20/set-theory-and-s...</a>
He left out an important reason to know and use SQL directly: you should always be aware how you are using your indices. This goes beyond making an index for a column in the table: you also need to know whether the query will map well to a btree index. Mobile, with its low resources, makes index optimization even more important; apple's Core Data can easily kill your app.
A class of query I love that scares off a lot of developers is a correlated sub-query, where the subquery references a value from the outer query. For example, finding all employees with at least one assignment:<p><pre><code> SELECT *
FROM employees e
WHERE EXISTS (SELECT 1
FROM assignments a
WHERE a.employee_id = e.id)
</code></pre>
For a while in Oracle this was a <i>lot</i> faster than IN/NOT IN. I'm not sure if that's still the case, or if it's true for other systems. I believe I read that in Postgres the query planner does the same thing whether you use EXISTS/NOT EXISTS or IN/NOT IN.<p>EDIT: This kind of query is great with Rails scopes, because you can write something like this:<p><pre><code> class Employee
scope :with_assignments, where(<<-EOQ)
EXISTS (SELECT 1
FROM assignments a
WHERE a.employee_id = employees.id)
EOQ
end
</code></pre>
and that is easily composeable with other scopes/conditions/etc since it doesn't force you to use any joins. Yay for mixing SQL with your ORM!
Everyone uses an ORM. You use a well known, documented, and supported ORM, or you're writing your own wether you realize it or not.<p>Don't believe me?<p>1. Do you have objects?
2. Do you have relational data?<p>There's the O and the R. How do you get them together? That's where the M comes in. You use a library that knows how to do the M, or you do your own M with a bunch of getters and setters, for loops and case statements.<p>Eventually, any little change to the database becomes a regression nightmare.<p>Once you find yourself saying "I know, I'll build a code generator to create these DAOs", that's when you should finally realize you should have used a real ORM. Sadly, many people still won't get it at this point and will go ahead with the code generator.
I don't understand how it is possible to develop anything database-related without knowing SQL. Are these people not querying the database directly to figure out what it contains (during development, debugging, testing etc etc)?
I once interviewed a guy with a <i>Masters</i> in computing (of some sort, I forget) who didn't know SQL. He'd been developing for years, but lived entirely in .NET land and just used ORMs.<p>Absolutely crazy.
Most of my experience is with the Django ORM. The point of the django ORM is not to replace SQL knowledge, even though that is feasible. The main point is reusability.<p>I had to work with SQL through PHP for a while and I found myself "composing" SQL queries in a myriad of ways. I tried to not repeat myself, but it felt like the Django ORM would have gone a lot further in cleaning up the query-building.<p>In conjunction with Django forms and Django Admin, maybe even the template language, the ORM makes query construction reusable.<p>One of the kickers is the ability to unify object construction from table columns. It's easy to convert a string or number to some Python field. It's more elaborate with Decimal, Json, or whatever you want to cook up.
What are some good resources for methodically learning SQL? Like many of the other devs I know, I learned a hodge podge of SQL while working on other projects, but I've never had any formal or comprehensive training on it. Ideally I'm looking for a book or two, and I don't mind if they start simple as long as they're comprehensive and recent enough to be relevant to modern RDBMSs.
An ORM isn't necessarily complex. It just needs to map a tuple to an object.<p>Where things get complex is when your framework starts introducing other concepts such as query generators, unit-of-work, caching, lazy-loading, etc.<p>One of the most critical features is query generation, which I think is the point of this article. Simple queries are pretty easy to abstract, such as loading rows by primary key or querying based off a simple index. Other queries, especially aggregate queries, get tricky fast. I argue that often it is much harder and more work to construct an appropriate query via your frameworks query generator.<p>Fortunately many good frameworks allow you to essentially write the exact SQL to be executed and the rest of the framework (mapping, caching, unit-of-work) "just works" with the results.
The article has the good premise, of course you should learn SQL, but it goes over the top and makes some false presumptions for the sake of the argument:<p>'Think about it, though: it’s absurd that you would even need to learn any SQL at all! The very nature of an ORM is to bypass SQL.'<p>I think the nature of ORM is to map object oriented code to relation data. So not to bypass, but to pass between the two conveniently. Conveniences that ORMs do automatically that otherwise you do manually are type checking, sql sanitization, and merging logic (methods) with the data in the same class. Knowing SQL does not make the above tasks any easier, so does not, in any way, prompt dropping ORMs.<p>Also, there is the wrong use of 'begs the question' in the same paragraph :)
This was also discussed in Rob Conery's speech on Norwegian Developer's Conference:
<a href="http://ndc2011.macsimum.no/mp4/Day2%20Thursday/Track1%201140-1240.mp4" rel="nofollow">http://ndc2011.macsimum.no/mp4/Day2%20Thursday/Track1%201140...</a>
I am not an ORM hater. I like ORMs a lot.<p>But the point that one needs to know SQL even if one is using an ORM is just obvious to me. It boggles and scares me that anyone thinks they can be a competent web developer without knowing SQL.<p>I was going to say "...if they use an rdbms, maybe they just use some NoSQL and can get away without it." But you know what, nope, not even that caveat -- if you don't know SQL and rdbms, you aren't going to be competent to know if some nosql is right the choice, or which one, either.
For those interested in sharpening their SQL skills I have found these two books to be a great resource.<p>1. Joe Celko's Trees and Hierarchies in SQL for Smarties
2. Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL
The nice thing about SQL is that it doesn't take very long to learn how to use it and then it's incredibly useful any time you have a database to interact with. I think all novice programmers should take a crack at it.
I see no reason why one programmer can't learn just about everything related to their application. I expect the programmers who work for me to be experts in SQL, CSS, and everything in between.
I dont understand how its possible for someone to be the "best python programmer you know" yet that person doesn't know enough SQL to display data from a table...?