Sqlfluff the SQL Linter for Humans • Hacker News Board

Re: Sqlfluff the SQL Linter for Humans

#21

Post by ErunamoJAZZ » Wed, Oct 06, 2021, 2:17 PM UTC

I wanted to use this linter, but they don't support many things that are used in real production code. By example, a simple postgres function like this one can't be parsed:

  CREATE OR REPLACE FUNCTION public.setof_test()
   RETURNS SETOF text
   LANGUAGE sql
   STABLE STRICT
  AS $function$
    select unnest(array['hi', 'test'])
  $function$
  ;

Re: Sqlfluff the SQL Linter for Humans

#22

Post by extr » Wed, Oct 06, 2021, 2:24 PM 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

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.

Re: Sqlfluff the SQL Linter for Humans

#23

Post by kcolford » Wed, Oct 06, 2021, 2:24 PM 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

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.

Re: Sqlfluff the SQL Linter for Humans

#24

Post by fnord123 » Wed, Oct 06, 2021, 2:32 PM UTC

kcolford wrote: post #23

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

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

Re: Sqlfluff the SQL Linter for Humans

#25

Post by mrbungie » Wed, Oct 06, 2021, 2:38 PM UTC

forgetfulness wrote: post #15

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

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

Re: Sqlfluff the SQL Linter for Humans

#26

Post by dugmartin » Wed, Oct 06, 2021, 2:53 PM 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

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

Re: Sqlfluff the SQL Linter for Humans

#27

Post by Genmutant » Wed, Oct 06, 2021, 3:01 PM UTC

ThePadawan wrote: post #2

This is a linter which complains about spaces and newlines, but lets "WHERE col = NULL" pass (which can never be meaningful) just fine? Obviously this depends on the rules configuration, but just judging that first smell test, it seems very ill thought out.

In MS SQL Server, with ANSI_NULLS set to OFF, it will select all rows having a NULL value for col. AFAIK not used that often.

Re: Sqlfluff the SQL Linter for Humans

#28

Post by forgetfulness » Wed, Oct 06, 2021, 3:07 PM UTC

mrbungie wrote: post #25

forgetfulness wrote: post #15

Earlier quoted context omitted.

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?

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.

Re: Sqlfluff the SQL Linter for Humans

#29

Post by forgetfulness » Wed, Oct 06, 2021, 3:10 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 ...

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

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

Re: Sqlfluff the SQL Linter for Humans

#30

Post by forgetfulness » Wed, Oct 06, 2021, 3:14 PM UTC

laumars wrote: post #18

tptacek wrote: post #16

I wondered how much good you could do with an SQL linter that didn't have any access to the underlying schema, and the answer seems to be "mostly whitespace and capitalization issues".

Makes you wonder if this tool would be better a code formatter rather than linter?

It seems to have a formatter option in the `fix` argument you can give it.

The authors should probably be selling it on that point since it seems to be its strength, it doesn't have many rules along the lines of the static analysis that people look for in linters.

Some people (tech leads, CTOs) care a lot about formatting but I wouldn't make teammates go through a linter for that, it's a rather surface concern.