Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Literate SQL

modern-sql.com

Post reply on HN
Print view View original

91–100 of 100 posts

Re: Literate SQL

#91

Post by felixge »

threeseed wrote: post #80
gregmac wrote: post #37

Earlier quoted context omitted.

I 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…

Actually more often than not ORMs are going to provide better performance than having people inexperienced with SQL writing it themselves. Especially when it comes to batching, pagination and joins and the intelligent use of in memory caching rather than doing everything in database. And the biggest advantage of an ORM is that you can trivially switch between databases which is often required for running automated te…

Running your tests against a different DB than what you're using is an anti-pattern IMO. You'll not be able to trust the results, and you'll be limited to the lowest common denominator of the databases your using.

Re: Literate SQL

#92

Post by mikaelhardo »

weberc2 wrote: post #81
DaiPlusPlus wrote: post #79

Earlier quoted context omitted.

I advocate ORM use for guaranteed type-safety - so long as SQL queries are processed as strings inside your application there's always the risk of column names and types not matching your entity classes - by using an ORM that generates SQL based on your classes (e.g. Entity Framework with Linq) it promotes mismatches to compile-time errors. This alone makes it worthwhile.

How does an ORM know what your database schema is at compile time? Honest question.

You either generate a model based on your database, or generate your database from your model. That way the database and model are always in sync and you get errors in the code. In Entity Framework this is called either "Code first" or "Database first"

Re: Literate SQL

#93

Post by kierenj »

lisper wrote: post #85

Why not just reverse the SELECT and FROM clauses? FROM table1, table2 SELECT col1, col2 WHERE ... or even better FROM table1 JOIN table2 ON condition ... SELECT ... WHERE [additional join conditions] HAVING [filter conditions] Then the compound version looks like: FROM ( FROM tables... SELECT ... WHERE ) SELECT... WHERE ... or FROM ( FROM tables ... SELECT ... ) JOIN [whatever] SELECT ... WHERE ... Actually, putting…

Because that's invalid SQL?

Re: Literate SQL

#94

Post by weberc2 »

mshenfield wrote: post #14
caseymarquis wrote: post #8

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 ye…

Agreed. 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 wo…

Query 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.

Re: Literate SQL

#95

Post by 3131s »

ubernostrum wrote: post #90
3131s wrote: post #38

Earlier quoted context omitted.

Write 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.

Meh. My feeling is that Django and other web frameworks are too magical -- I always worry they might be doing something inefficient. That's why I write all my web applications in hand-tuned assembly. It even has reusable abstractions for when I need them, and nice hand-formatted assembly is super readable!

You're seriously comparing SQL to assembly? There's a point where it doesn't make sense to add another layer, another dependency, etc.

Re: Literate SQL

#96

Post by ubernostrum »

3131s wrote: post #95
ubernostrum wrote: post #90

Earlier quoted context omitted.

Meh. My feeling is that Django and other web frameworks are too magical -- I always worry they might be doing something inefficient. That's why I write all my web applications in hand-tuned assembly. It even has reusable abstractions for when I need them, and nice hand-formatted assembly is super readable!

You're seriously comparing SQL to assembly? There's a point where it doesn't make sense to add another layer, another dependency, etc.

In both the "never use an ORM" and "always use assembly" cases, the argument is based entirely on having full control of what's going on and not trusting any intermediate layer to get it right or eke out every last femtosecond worth of performance.

So it's a perfectly fair comparison.

Re: Literate SQL

#97

Post by lisper »

kierenj wrote: post #93
lisper wrote: post #85

Why not just reverse the SELECT and FROM clauses? FROM table1, table2 SELECT col1, col2 WHERE ... or even better FROM table1 JOIN table2 ON condition ... SELECT ... WHERE [additional join conditions] HAVING [filter conditions] Then the compound version looks like: FROM ( FROM tables... SELECT ... WHERE ) SELECT... WHERE ... or FROM ( FROM tables ... SELECT ... ) JOIN [whatever] SELECT ... WHERE ... Actually, putting…

Because that's invalid SQL?

So was the WITH clause until someone decided it should be valid and added it.

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.

Re: Literate SQL

#98

Post by Semaphor »

zamalek wrote: post #25
mshenfield wrote: post #2

I came to this page looking for ways to parameterize and re-use raw SQL. with (and create view for multiple queries) fulfill the case where the unit of reuse is a filtered view on one or more tables.

With SQL Server you can create TVFs (table-valued functions). So long as they are 'inline' (lacking BEGIN and END) they will be inlined into any query that uses them. If your SQL fu is up to scratch they are extremely powerful - e.g. CROSS APPLYing them is one of the best ways I've seen to do reusable row-level filtering.

Would you happen to have a link that goes into further detail?

Re: Literate SQL

#99

Post by oweiler »

felixge wrote: post #91
threeseed wrote: post #80

Earlier quoted context omitted.

Actually more often than not ORMs are going to provide better performance than having people inexperienced with SQL writing it themselves. Especially when it comes to batching, pagination and joins and the intelligent use of in memory caching rather than doing everything in database. And the biggest advantage of an ORM is that you can trivially switch between databases which is often required for running automated te…

Running your tests against a different DB than what you're using is an anti-pattern IMO. You'll not be able to trust the results, and you'll be limited to the lowest common denominator of the databases your using.

This so much! Furthermore any application of reasonable size will make use of database-specific functionality.

Re: Literate SQL

#100

Post by krylon »

It took me a long time to learn SQL well enough to appreciate its beauty.

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.

Post reply on HN