Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Sqlfluff the SQL Linter for Humans

sqlfluff.com

Post reply on HN
Print view View original

1–10 of 95 posts

Re: Sqlfluff the SQL Linter for Humans

#2

Post by ThePadawan »

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.

Re: Sqlfluff the SQL Linter for Humans

#4

Post by justusw »

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.

I guess it depends on what you want out of a linter e.g. if you want it to check just syntax or semantics as well. Is “= NULL” syntactically correct?

Re: Sqlfluff the SQL Linter for Humans

#5

Post by KronisLV »

This is a nice idea, but my problem with most of these tools is that they work with pure SQL/JS/whatever files, which isn't always the case.

For example, if you use something like myBatis, you'll probably build the SQL dynamically, which could be stored in a mapper XML file: https://mybatis.org/mybatis-3/dynamic-sql.html

Not only that, but certain frameworks have you embedding SQL inside of the main application code, when you're not using a DSL like HQL: https://www.tutorialspoint.com/hibernate/hibernate_query_lan... and https://www.tutorialspoint.com/hibernate/hibernate_native_sq...

Furthermore, there seems to be this odd separation between "regular SQL" and "procedural SQL" in some DBVSes, such as in Oracle - there you might have to use "DECLARE ... BEGIN ... END;" in certain contexts, or maybe "EXECUTE IMMEDIATE". Then again, Oracle isn't among the supported dialects, which is perfectly understandable, because of both how the DBVS is positioned, as well as because of how large an undertaking supporting it would be: https://docs.sqlfluff.com/en/stable/dialects.html

In short, the only passable tools that i've found for checking SQL are those that are integrated within the IDE, or using a specialized IDE with any plugins for specific technologies, like JetBrains DataGrip (commercial project): https://www.jetbrains.com/datagrip/

But then you run into the issue of code style preferences: some companies have style guides where you have to use reserved words in uppercase with the dev created content in lowercase, like:

  SELECT some_column, another_column, yet_another_column AS one_more 
  FROM some_table 
  WHERE some_column > SYSDATE
while certain tools (like SQL Developer) are more than happy to use autocomplete which doesn't work well with that:

  SELECT (start typing in "som") FROM some_table ...
  Oracle will autocomplete to: SOME_COLUMN
And then you run into people's personal preferences, for example some format the columns that they want to select like this:

  SELECT
      some_column
    , another_column
    , yet_another_column
  FROM ...
which makes sense when you want to edit single lines, but could have been avoided by the SQL standard allowing dangling commas, like some other languages do, for example:

  SELECT
    some_column,
    another_column,
    yet_another_column,
  FROM ...
but as it currently stands, that's not possible and the other way of formatting code also tends to break linters. And since the actual formatting of SQL doesn't matter as much as it would in Python, we end up with every IDE and other tool having their own preferences.

I guess the lesson here is to avoid complexity as much as possible and just settle on whatever works.

Re: Sqlfluff the SQL Linter for Humans

#6

Post by tomhallett »

I am spec’ing a data pipeline right now and the team is so excited to try this out. Here’s a great intro video about the tool and their motivations: https://m.youtube.com/watch?v=veYB9uh0RCM&t=1105s

My goal is to use it with a Rockset database, so I’ll see how far I can get with one of the existing dialects, or how hard it is to extend an existing one to make one for Rockset

Re: Sqlfluff the SQL Linter for Humans

#7

Post by ThePadawan »

justusw wrote: post #4
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.

I guess it depends on what you want out of a linter e.g. if you want it to check just syntax or semantics as well. Is “= NULL” syntactically correct?

A linter is supposed to tell you "you are probably making a mistake."

"= NULL" is as syntactically correct in SQL as "if (x = null)" in C.

If it wasn't, code highlighting or compiling would break, both of which are much much more obvious.

Re: Sqlfluff the SQL Linter for Humans

#9

Post by epberry »

Fascinating to see the focus on SQL and continuous integration, https://docs.sqlfluff.com/en/stable/inthewild.html#inthewild.... DBT has really changed things in that regard. @tomhallett's YT video seems to describe using this with a pre-release build of DBT to tightly couple it to sqlfluff.

Re: Sqlfluff the SQL Linter for Humans

#10

Post by [deleted] »

ThePadawan wrote: post #7
justusw wrote: post #4

Earlier quoted context omitted.

I guess it depends on what you want out of a linter e.g. if you want it to check just syntax or semantics as well. Is “= NULL” syntactically correct?

A linter is supposed to tell you "you are probably making a mistake." "= NULL" is as syntactically correct in SQL as "if (x = null)" in C. If it wasn't, code highlighting or compiling would break, both of which are much much more obvious.

[deleted]
Post reply on HN