5 ms·

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

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

forgetfulness 5y ago
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?
- laumars 5y ago
  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.
- mrbungie 5y ago
  I've seen it a lot in Data Science/Analytics environments, as it allows for faster query iterations than trailing commas.
  - forgetfulness 5y ago
    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.
    - y4mi 5y ago
      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.
extr 5y ago
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.
- new_guy 5y ago
  That formatting makes my eyes bleed.
- RussianCow 5y ago
  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.
  - extr 5y ago
    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.
- marcosdumay 5y ago
  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.
kcolford 5y ago
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.
- fiddlerwoaroof 5y ago
  I’ve always formatted my SQL like this, but I don’t remember learning it from anyone else or seeing anyone else do it.
- fnord123 5y ago
  alt styling SELECT a, b, COUNT(*) as count FROM table WHERE cond1 AND cond2 GROUP BY a, b ORDER BY count DESC
  - EamonnMR 5y ago
    That's the one I'm familiar with. The capitalized keywords help give you that Relational Database feel.
  - munk-a 5y ago
    Capitalized keywords and section declarations on their own lines helps with quick comprehension for me at least
  - snidane 5y ago
    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?
    - bntyhntr 5y ago
      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.
dugmartin 5y ago
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 ...
- forgetfulness 5y ago
  Standard SQL and most dialects enforce that in the syntax anyway no? Edit: misread and select was in the middle in that comment
  - CamouflagedKiwi 5y ago
    No, all databases support the common order of SELECT ... FROM ... WHERE ....
    - forgetfulness 5y ago
      You're right, I somehow missed that select was in a different order.
- wodenokoto 5y ago
  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.
- throwaway021212 5y ago
  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 https://en.wikipedia.org/wiki/QUEL_query_languages#Usage
darksaints 5y ago
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.
- nmstoker 5y ago
  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).
  - marcosdumay 5y ago
    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.
amshank 5y ago
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://docs.telemetry.mozilla.org/concepts/sql_style.html https://github.com/dbt-labs/corp/blob/master/dbt_style_guide.md 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-team/platform/sql-style-guide/ 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.
data_ders 5y ago
could a synonym for "more not-noob" perhaps be "more esoteric"?
rob74 5y ago
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...
mulmen 5y ago
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?
OJFord 5y ago
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.
- bobchadwick 5y ago
  BigQuery supports trailing commas in select statements: https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#trailing_commas https://cloud.google.com/bigquery/docs/reference/standard-sq...

forgetfulness 5y ago

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?

laumars 5y ago
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.
mrbungie 5y ago
I've seen it a lot in Data Science/Analytics environments, as it allows for faster query iterations than trailing commas.
- forgetfulness 5y ago
  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.
  - y4mi 5y ago
    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.

extr 5y ago

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.

new_guy 5y ago
That formatting makes my eyes bleed.
RussianCow 5y ago
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.
- extr 5y ago
  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.
marcosdumay 5y ago
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.

kcolford 5y ago

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.

fiddlerwoaroof 5y ago
I’ve always formatted my SQL like this, but I don’t remember learning it from anyone else or seeing anyone else do it.
fnord123 5y ago
alt styling SELECT a, b, COUNT(*) as count FROM table WHERE cond1 AND cond2 GROUP BY a, b ORDER BY count DESC
- EamonnMR 5y ago
  That's the one I'm familiar with. The capitalized keywords help give you that Relational Database feel.
- munk-a 5y ago
  Capitalized keywords and section declarations on their own lines helps with quick comprehension for me at least
- snidane 5y ago
  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?
  - bntyhntr 5y ago
    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.

dugmartin 5y ago

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

forgetfulness 5y ago
Standard SQL and most dialects enforce that in the syntax anyway no? Edit: misread and select was in the middle in that comment
- CamouflagedKiwi 5y ago
  No, all databases support the common order of SELECT ... FROM ... WHERE ....
  - forgetfulness 5y ago
    You're right, I somehow missed that select was in a different order.
wodenokoto 5y ago
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.
throwaway021212 5y ago
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 https://en.wikipedia.org/wiki/QUEL_query_languages#Usage

darksaints 5y ago

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.

nmstoker 5y ago
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).
- marcosdumay 5y ago
  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.

amshank 5y ago

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://docs.telemetry.mozilla.org/concepts/sql_style.html https://github.com/dbt-labs/corp/blob/master/dbt_style_guide.md 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-team/platform/sql-style-guide/ 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.

data_ders 5y ago

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

rob74 5y ago

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

mulmen 5y ago

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?

OJFord 5y ago

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.

bobchadwick 5y ago
BigQuery supports trailing commas in select statements: https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#trailing_commas https://cloud.google.com/bigquery/docs/reference/standard-sq...