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

11–20 of 100 posts

Re: Literate SQL

#11

Post by vinceguidry »

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…

For anybody on Rails, you can call .to_sql on any ActiveRecord::Relation and it will return the exact SQL string that your code is producing. Really handy for troubleshooting, especially for associations.

Re: Literate SQL

#12

Post by [deleted] »

gjvc wrote: post #6
nerdponx wrote: post #5

I wish CTEs ("common table expression", i.e. a "with clause") had the same performance as a subquery in PSQL. I always assumed they'd be implemented as a kind of macro that expanded to a subquery. Is there a good reason for this distinction? 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 op…

For the uninitiated, what is GPP ?

[deleted]

Re: Literate SQL

#13

Post by zzzeek »

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…

You can have functional SQL query languages that map directly to SQL without object oriented row mapping. Everyone can use these tools without performance issues.

Disclaimer: i wrote SQLAlchemy.

Re: Literate SQL

#14

Post by mshenfield »

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 works as I expect, how can I reuse without reducing clarity.

Also, just a little joke: https://i.imgflip.com/1rhhzl.jpg

Re: Literate SQL

#15

Post by theprotocol »

nerdponx wrote: post #5

I wish CTEs ("common table expression", i.e. a "with clause") had the same performance as a subquery in PSQL. I always assumed they'd be implemented as a kind of macro that expanded to a subquery. Is there a good reason for this distinction? 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 op…

Regarding postgres `with`: I thought the same as you, that it'd be implemented as a macro, and only just found out that it's not the case thanks to your comment. I'd love to hear more about this decision.

Re: Literate SQL

#16

Post by teej »

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 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');
    ...
It has made a huge difference in how we write SQL.

Re: Literate SQL

#18

Post by nerdponx »

zzzeek wrote: post #13
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…

You can have functional SQL query languages that map directly to SQL without object oriented row mapping. Everyone can use these tools without performance issues. Disclaimer: i wrote SQLAlchemy.

Is that really a disclaimer, or just the source for your claim?

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.

Re: Literate SQL

#19

Post by enjoiful »

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…

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?

Re: Literate SQL

#20

Post by mshenfield »

zzzeek wrote: post #13
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…

You can have functional SQL query languages that map directly to SQL without object oriented row mapping. Everyone can use these tools without performance issues. Disclaimer: i wrote SQLAlchemy.

> You can have functional SQL query languages that map directly to SQL without object oriented row mapping.

Well said. I always think of this as the canonical example:

http://sqlkorma.com/

I don't know what rock I've been under, but didn't realize SQLAlchemy had the same idea w/ SQLAlchemy Core.

Post reply on HN