Hacker News Reader

Comment by snidane | Hacker News Reader

There surely is a need for a sql linter, but if it wants to be strict about syntax style, it should better familiarize itself with not-noob styles first.

Eg. some people like to align the clauses vertically and use a leading, not trailing, comma.

    select a
         , b
         , count(*) as count
      from table
     where cond1
       and cond2
     group by a, b
     order by count desc

View on HN

Yes! I write SQL this way. I grant the comma thing is more a matter of taste, but aligning clauses vertically and maximizing rivers is super useful to me and it forever mystifies me that it isn't standard. This is an extreme example, but I like to keep join clauses vertically aligned as well:

    select a
         , b
         , count(*) as count
      from table
      join table2 on table.key1                    = table2.key1
                 and table.key2                    = table2.key2
                 and DATEADD('day', 1, table.key3) = table2.key3
     where cond1
       and cond2
     group by a, b
     order by count desc

For me this lets me read the SQL query almost like a picture at first glance, identifying blocks of rivers as semantically connected groups of statements. Some SQL formatters out there love to put join clauses at the same indentation level as the join itself, drives me nuts.

That formatting makes my eyes bleed.

Do you format your code this way by hand or does your IDE help you? Also, I can only imagine how noisy your diffs would be when you make changes and have to indent everything.

I'll use PyCharm's SQL formatter sometimes to massage spaghetti code that someone else wrote, but mostly I just write it this way by hand.

I will say my use case is probably different than others, I'm a Data Scientist so I write a ton of throwaway SQL and stuff that will only be seen by me. If I'm dealing with an existing SQL codebase or production stuff, definitely try to keep it more easily diff-able (like left justifying the keywords, really only caring about rivers in the join/where clauses), or just follow whatever patterns are already there.

This looks nearly the same, but it's much easier to write and generates less spurious diffs:

    select a
        , b
        , count(*) as count
    from table
    join table2
        on table.key1 = table2.key1
        and table.key2 = table2.key2
        and DATEADD('day, 1, table.key3) = table2.key3
    where cond1
        and cond2
    group by a, b
    order by count desc

The largest difference is that the equal operators aren't aligned anymore, but on my opinion, aligning them is really not worth the cost.

Nice changes, I like it! I will say I really like aligning the join clauses, but I'll try writing things this way for a little while and see if it sticks.

The only thing I require from an SQL formatter is to make typos and boolean mistakes super easy to spot, especially as it grows in size. At first glance it might seem strange, but I like indentation on even the first term of an AND.

    SELECT
        a,
        b,
        count(*) as count
    FROM
        table
    JOIN
        table2
    ON
            table.key1 = table2.key1
        AND
            table.key2 = table2.key2
        AND
            -- 1 day apart
            DATEADD('day', 1, table.key3) = table2.key3
    WHERE
            cond1
        AND
            cond2
        AND
            (
                cond3
            OR
                cond4
            )
    GROUP BY
        (a, b)
    ORDER BY
        count desc,
        a asc
    LIMIT
        1000
    ;

Yes, that's a lot of whitespace, but it helps for complex queries.

Well, there's an inherent conflict between making individual queries easier to read and making your entire code easier to read.

How much whitespace you need is completely dependent on what exactly you are writing.

> it forever mystifies me that it isn't standard

I can give a few thoughts on why here:

- it doesn't work for collaborative efforts unless everyone is using the same linter or IDE with the same configuration. Not friendly to edits outside of that ecosystem (e.g. open source). - there are other ways of writing SQL (e.g. dbt style guide) which are still very readable while also being much faster to write by hand without the need for an auto-formatter. - some of these patterns such as the from and join indentations have nothing to do with readability and are purely stylistic. Nothing wrong with that, but there's nothing objectively better about this pattern - it just comes down to how you're used to reading SQL.

> and it forever mystifies me that it isn't standard

I can take a stab at justifying that - commas are used to form lists (eg, 1, 2, 3) and text (like "order by count") are supposed to be left justified.

You may be right that there are good reasons to format SQL while ignoring the commas and rules for writing English text. I personally think you are right. But if most people believed that then there'd be enough support to rewrite SQL with a decent modern syntax. Say allowing training commas before a from.

So the existence of SQL is, in a weird way, evidence that your view is uncommon.

If SQL would just allow a trailing comma on the last column, none of this leading comma shit would be necessary. I still use trailing commas, because code readability is more important that writeability IMO, but the fact that I'm forced to sympathize with such an ugly shit sandwich is really frustrating.

Can't sympathize with the clause alignment though. And I can't figure out why someone who does leading commas for editability would chose a style that forces you to use different indentation levels for different lines. Oh wait, you wanna make that join a left join? Time to go re-indenting my whole damn query...

It's not even an improvement in readability...who actually reads text like it's a column format? It's not a spreadsheet.

Yes, despite knowing how to do it right those damn comma issues always come up at the worst time and to add insult to injury they're reported by most of the tools I have available like they're totally insoluble to the computer (even though far more taxing things are routinely fixed automatically).

The code you get with a trailing comma is valid SQL just like the one you get without, and both have different meanings.

The language is just too lenient with syntax, what is quite on the line for a "human legible" language from the late 70's and earlier 80's, but a really undesirable characteristic in practice. Unfortunately, nobody is doing a "better SQL", just radically different things that often not even support the same DB engines. And those have a real hard time getting popular, so we are stuck with the late 70's speculative innovations.

I hate you and and the awful layout you have just introduced me to... Also, that is how I will be formatting all my SQL from now on.

alt styling

    SELECT 
        a, b, COUNT(*) as count
    FROM
        table
    WHERE
        cond1 AND cond2
    GROUP BY
        a, b
    ORDER BY
        count DESC

That's the one I'm familiar with. The capitalized keywords help give you that Relational Database feel.

Capitalized keywords and section declarations on their own lines helps with quick comprehension for me at least

I've seen this one in the wild and have objections to it.

  - why capitalize KEYWORDS. We're no longer in 1970s. We use colors
  - why waste so much space. Why not just put table on the same line with from. With joins this just bloats up
  - for complicated conditions they have to be put on their own lines. Do I give each AND a separate line and waste ever more space?

My editors and code review tools do not format or highlight sql for me when it's embedded in other files - eg java. I find capitalized keywords to be very convenient. Of course at that point, I can't run sqfluff on it either but this thread has already devolved fairly quickly.

- Half my SQL queries are written inside some other script, like Python or bash, so capitalization is nice.

- Space is not wasted when it it makes code more readable.

- Yes, and also give the columns in select their own lines. So nice to read. Space well spend!

Good points. Maybe there is a difference between a SQL script embedded occasionally somewhere in python, for which this style probably makes sense.

For heavy SQL codebases found in data analytics, there are better styles.

I’ve always formatted my SQL like this, but I don’t remember learning it from anyone else or seeing anyone else do it.

Or the ultimate pedant (pedultimate?) order which matches the SQL order of operations (which for some queries can be illuminating):

    from ...
    where ...
    group ...
    having ...
    select ...
    order by ...
    limit ...

Standard SQL and most dialects enforce that in the syntax anyway no?

Edit: misread and select was in the middle in that comment

No, all databases support the common order of SELECT ... FROM ... WHERE ....

You're right, I somehow missed that select was in a different order.

Is that legal SQL? I'd love to write in that order.

When working in Big Query, it would be so nice to first declare the table, so the online IDE can autocomplete columns.

You might like QUEL, precursor to SQL (and the original language of Ingres and Postgres) where range variables came first.

https://en.wikipedia.org/wiki/QUEL_query_languages#Usage

Most of the SQLFluff style was based around the Mozilla SQL style guide and the dbt labs style guide: https://docs.telemetry.mozilla.org/concepts/sql_style.html https://github.com/dbt-labs/corp/blob/master/dbt_style_guide...

The GitLab SQL style guide isn't far off either: https://about.gitlab.com/handbook/business-technology/data-t...

Rather than supporting _all_ ways of formatting SQL, the aim of the tool was to fairly flexibly support the most mainstream ones with a view to becoming more opinionated in future to converge on a more unified style in future. Similar to what black has done with python.

This whole discussion here shows that the best time to introduce a linter/autoformatter is at the same time as the language it's supposed to lint/format (like Go did). Otherwise you will have interminable discussions and everyone will want their favorite style supported. I'm afraid that ship has sailed for SQL though...

Leading comma sort of works for SQL, since 'a' will often be a much less likely to change ID or something. Really I just wish it allowed a final trailing comma though, so each could be on its own line:

    select
      a,
      b,
    from ...

without the comma diff when you add one to the end.

BigQuery supports trailing commas in select statements: https://cloud.google.com/bigquery/docs/reference/standard-sq...

Leading comma is configurable, but I wouldn't know if the linter allows for that layout. Can't say I've seen it before, is it specific to a particular development environment?

It’s an informal way some people write SQL if they’re developing a script.

I’ve done it this way before because it was quicker to edit, and and remove lines. But it’s not a format I’d particularly like to share with others because it looks weird.

I've seen it a lot in Data Science/Analytics environments, as it allows for faster query iterations than trailing commas.

Yes you can find sources advocating for leading commas and it's commonly used. I have "Google BigQuery: The Definitive Guide" on my desktop, it was written by senior Google employees involved in the steering and use of the product inside, for what it's worth, and it advocates for leading comma and SHOUTING THE KEYWORDS.

I personally disobey both recommendations (I mean, the book is worth it for other reasons) but both those conventions are very commonly used.

I meant more the the "center" layout that the commenter above was describing, it'd seem quite laborious to maintain by hand in most environments, if not using a particular one that supports it out of the box.

jetbrain IDEs have it configured by default for most situations, so its generally just an auto-format away if you're actually writing sql and not embedding sql queries in some other code.

Please do not use leading commas. It doesn’t make any sense. Where else do you ever use them? Do you use them with an IN clause? What about in JSON?

could a synonym for "more not-noob" perhaps be "more esoteric"?

Reply on news.ycombinator.com