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

81–90 of 100 posts

Re: Literate SQL

#81

Post by weberc2 »

DaiPlusPlus wrote: post #79
weberc2 wrote: post #50

Earlier quoted context omitted.

While I prefer vanilla SQL to the current generation of ORMs, I think ORMs are trying to address a real problem--namely that SQL (despite being high level) isn't very abstraction-friendly. Abstractions in SQL can be expensive and often unpredictably so, despite how clever databases are today. I think part of the problem is the mismatch between the largely imperative OO paradigm and the largely declarative relational…

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.

Re: Literate SQL

#82

Post by weberc2 »

lgas wrote: post #63
weberc2 wrote: post #46

Earlier quoted context omitted.

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

Project 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

I'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!

Re: Literate SQL

#83

Post by PeterisP »

gregmac wrote: post #70
weavie wrote: post #67

Earlier quoted context omitted.

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

Worse, someone is using the view (that joins several tables) in a join to get one extra field, which would be a simple single join -- they just didn't bother to check where it came from because 95% of the time that view gets used, and no one questions it. I've seen it happen more than once.

Why would that be a problem?

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.

Re: Literate SQL

#84

Post by mshenfield »

teej wrote: post #16
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.

To make progress on this problem, I wrote a light SQL preprocessor to support INCLUDE statements. It enables me to write code like this: WITH frequently_bought_together AS ( INCLUDE frequently_bought_together.sql ) SELECT ... This allows way better isolation and reuse of business logic than before. In Redshift, I combine this with an assert user-defined function to enable writing unit tests in raw SQL. With all that…

It took me a second, but I can see why this would be handy. Whether you just use it in a with, or create a view from it, you have a single definition of the query that anyone can use. And you can test the INCLUDEd query. Neat.

Re: Literate SQL

#85

Post by lisper »

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 the WHERE before the SELECT makes even more sense.

Re: Literate SQL

#86

Post by throwaway91111 »

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…

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

Re: Literate SQL

#87

Post by flukus »

Daishiman wrote: post #73
vladsanchez wrote: post #66

Earlier quoted context omitted.

ORMs are for people who don't know SQL.

SQL is for people whose problem domain is so simple that they don't need to do things such as, I dunno, compose queries .

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

Re: Literate SQL

#88

Post by koolba »

paulddraper wrote: post #53
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…

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

1-2 GB? You can have a 1 TB fit into RAM these days. (And many of us do!)

Re: Literate SQL

#89

Post by 3131s »

Daishiman wrote: post #74
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.

> but I prefer to write raw SQL for all queries. 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…

> This would be horrendously difficult and error-prone writing plain SQL.

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

Re: Literate SQL

#90

Post by ubernostrum »

3131s wrote: post #38
enjoiful wrote: post #19

Earlier quoted context omitted.

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

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!
Post reply on HN