Literate SQL
modern-sql.com
modern-sql.com
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.
Disclaimer: i wrote SQLAlchemy.
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.
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.
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
I want to get strong in SQL -- where/how do I start?
The PostgreSQL documentation is really good too.
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).
So it's a perfectly fair comparison.
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.
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.
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.
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...
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:
My last employer lost 5M+ euros (not reveue but profit) every month because of excessive use of PL/SQL single row processing.
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.
When it comes to optimizing a slow running query, having to step through layer after layer of nested view can be incredibly challenging.
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.
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.
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.
http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...
Not trying to attack you, just a cautionary tale that you should read
For that matter, having some kind of SQL-oriented macro/preprocessor language would be fantastic. I guess GPP (General Preprocessor, https://logological.org/gpp) is always an option.
I recently refactored a long SQL query with a half-dozen with-expressions to a single query and increased the performance by an order of magnitude. They had used "with" to build up a query from a bunch of independent sets and then union them together and, while perfectly logical, it was a performance nightmare. In the end, I just took all the conditions that made up each query and combined it into one with the appropriate joins. I'd even argue that ultimately the finished product was easier to understand.
Like in everything, a balance is ideal. For reporting/analysis/testing purposes, I think nesting views and CTEs is more productive in the long run, while production application code is probably best kept as straightforward as possible.
Edit: Can't find anything. I could have sworn they said there were patches being worked on, however.
I believe Tom Lane has since said that there isn't any reason it should stay that way and the community has never received any guarantee that this was going to stay the same. It just hasn't been implemented. In other words, they're seeking contributions. I'd do it myself if I were even a remotely capable C programmer, but I'm not.
The advantage is that you'll be able to write full documentation amongst the SQL, present it in any order, and reuse chunks.
The disadvantage is that it outputs to stdout, so if that's no good for your task then it's no help.
Simple queries, you know, "SELECT foo, bar FROM baz WHERE lastChange > CURRENT_DATE" are easy.
But if you are facing the database of your ERP software (as I often am) whose vendor is very reluctant to tell you about its internal structure and how that interfaces with the ERP system (my gut feeling, though, is that we're lucky - SAP and Oracle are probably much less friendly to people poking around in their databases to create custom reports, hehe), using SQL and its interactive nature to explore the database is a lot of fun as long as the database design is relatively sane. Thank God our ERP vendor's programmers were not creative enough to do insane things.
(Well, they did one crazy thing - there are NO foreign keys to be found anywhere in that database, instead it is all faked with triggers. I think that's how people used SQLite before it supported foreign keys. But we're talking about Microsoft freaking SQL Server here; being derived from another enterprise-y RDBMS, I find it hard to believe that it would at some point have lacked foreign keys. Since the triggers DO check for referential integrity, why on earth did they no just use Foreign keys? What were they thinking?)
It gets a little mind-bending at times, but in a good way.
But explaining SQL queries of the non-trivial kind to somebody is intimidating. One of our accountants at one point expressed interest in learning SQL, because she would bug me with questions that I answered by running a few carefully worded queries. For some reason I find SQL relatively easy to understand but really, really hard to explain.
FROM table1, table2
SELECT col1, col2
WHERE ...
FROM table1 JOIN table2 ON condition ...
SELECT ...
WHERE [additional join conditions]
HAVING [filter conditions]
FROM (
FROM tables...
SELECT ...
WHERE
) SELECT...
WHERE ...
FROM (
FROM tables ...
SELECT ...
) JOIN [whatever]
SELECT ...
WHERE ...
Every improvement to every language ever started out with someone saying, "Hey, here's an idea..." And none of those ideas were ever "valid X" at the time they were proposed.
WITH frequently_bought_together AS (
INCLUDE frequently_bought_together.sql
)
SELECT ...
With all that together, I can trust analysts to update complex data assets and I can ask them to take any data issue investigation they've done and turn it into a re-usable test. Tests end up looking like:
CREATE TEMPORARY TABLE frequently_bought_together AS
INCLUDE frequently_bought_together.sql
;
SELECT f_assert(COUNT(*) > 0, 'Table is empty');
SELECT f_assert(COUNT(DISTINCT item_bought || item_recommended) = COUNT(*), 'Table is fanned out');
...