Sqlfluff the SQL Linter for Humans • Hacker News Board

Re: Sqlfluff the SQL Linter for Humans

#81

Post by throwaway021212 » Wed, Oct 06, 2021, 11:32 PM UTC

dugmartin wrote: post #26

snidane wrote: post #11

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

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

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

Re: Sqlfluff the SQL Linter for Humans

#82

Post by mixmastamyk » Wed, Oct 06, 2021, 11:54 PM UTC

twoodfin wrote: post #49

Most of the rules seem reasonable, but what's the justification for this? Rule_L031: Avoid table aliases in from clauses and join conditions. I can't see what makes aliases clarifying in `select` or `where` but not in `join`. Also, inlined subqueries in a join must be given an alias, so there are cases where this rule can't be applied consistently.

Yes, that's often important. Project at work has lots of tables with very long names, which I alias frequently to obvious short names:

    schema.adjective_noun_adjective_noun as anan

Re: Sqlfluff the SQL Linter for Humans

#83

Post by mixmastamyk » Thu, Oct 07, 2021, 12:05 AM UTC

jdub wrote: post #13

I've been using sqlfluff with dbt for a while now, and not only has it been a delight to use, the maintainers are incredibly responsive.

Yeah, we can see that in this post today. I saw two issues implemented and fixed while reading it.

Re: Sqlfluff the SQL Linter for Humans

#84

Post by mixmastamyk » Thu, Oct 07, 2021, 12:06 AM UTC

amshank wrote: post #38

nycjay wrote: post #17

Sorry - didn't get a chance to go through the documentation much yet... A problem we are looking to catch early in the development process is for things we don't want folks to do, even if they are technically correct. For example, dropping a column or altering the datatype/nullability in such a way that the table goes into reorg pending. I know we can write our own regex to look for that specific syntax, but I've not…

It's pretty new functionality in sqlfluff, but it now supports user defined plugins for org-specific rules if you want to forbid something more obscure. Documentation is sketchy, but you can see the proof of concept here: https://github.com/sqlfluff/sqlfluff/tree/main/plugins/sqlfl...

Nice, need to forbid things like grants and setting owners that are being handled by a centralized routine.

Re: Sqlfluff the SQL Linter for Humans

#85

Post by OJFord » Thu, Oct 07, 2021, 12:52 AM UTC

snidane wrote: post #11

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

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.

Re: Sqlfluff the SQL Linter for Humans

#86

Post by extr » Thu, Oct 07, 2021, 1:04 AM UTC

marcosdumay wrote: post #60

extr wrote: post #22

Earlier quoted context omitted.

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

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…

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.

Re: Sqlfluff the SQL Linter for Humans

#87

Post by bobchadwick » Thu, Oct 07, 2021, 2:23 AM UTC

OJFord wrote: post #85

snidane wrote: post #11

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

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

Re: Sqlfluff the SQL Linter for Humans

#88

Post by fuckthemachine » Thu, Oct 07, 2021, 3:53 AM UTC

Is there a vim plugin for this yet? :)

Re: Sqlfluff the SQL Linter for Humans

#89

Post by tunetheweb » Thu, Oct 07, 2021, 4:59 AM UTC

fuckthemachine wrote: post #88

Is there a vim plugin for this yet? :)

https://github.com/dense-analysis/ale/pull/3927

Re: Sqlfluff the SQL Linter for Humans

#90

Post by roenxi » Thu, Oct 07, 2021, 8:09 AM UTC

extr wrote: post #22

snidane wrote: post #11

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

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

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