We use this at my company to lint dbt sql code within our shared repositories. We are able to easily craft a set of rules that fit our style guides and ignore the rest.
Sqlfluff the SQL Linter for Humans
71–80 of 95 posts
Re: Sqlfluff the SQL Linter for Humans
#72Earlier quoted context omitted.
Code isn't meant to be read by computers - it's meant to be read by humans (including yourself in three years when you've forgotten everything). Code sugaring and style adherence are vital portions of retaining a maintainable and flexible codebase.
Yeah I know the spirit. I'm saying it amounts to absolutely nothing in readability. If there's any formatting at all, it's structure and not particular whitespace and comma placement conventions that make or break readability, the rest is code quality theater.
It does actually matter quite a bit in readability in my experience - when you have a bunch of noobs, and a bunch of senior folks, and a bunch of interns, and a bunch of contractors all slinging code around for review to each other.
Many folks will get sloppy (in syntax and cleanliness) or misunderstand things - which requires the reviewer be able to clearly and quickly see what they are trying to do to catch that - especially as deadlines approach. The more it slips, the more slipping becomes the norm, and the messier and harder to understand everything gets - which makes later work harder as well.
If what you're showing is the standard, I guarantee 25% or more of the requested changes (which already won't meet whatever standard ANYONE sets strictly when first proposed) will be even worse. If that even worse becomes standard, etc, etc.
Part of their job is to set the 'reasonable' ideal, and attempt to enforce it. It won't happen universally, or even necessarily often, but it pushes things more towards maintainability and obviousness, which is opposite of the normal trend in any group of people. The larger the group, the more of a problem it is, and the harder they need to work to do it.
Re: Sqlfluff the SQL Linter for Humans
#73There 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 ...
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.
Re: Sqlfluff the SQL Linter for Humans
#74Earlier quoted context omitted.
alt styling SELECT a, b, COUNT(*) as count FROM table WHERE cond1 AND cond2 GROUP BY a, b ORDER BY count DESC
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?
- 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!
Re: Sqlfluff the SQL Linter for Humans
#75Earlier quoted context omitted.
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?
- 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.
Re: Sqlfluff the SQL Linter for Humans
#76There 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
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?
Re: Sqlfluff the SQL Linter for Humans
#77Most 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.
Aliases rarely add clarity in my experience. An alias is just an extra lookup when I need context. It’s a waste of mental swap space.
Inlined subqueries are creating entirely new projections so they need descriptive names. If you are doing this you should consider a WITH anyway.
Re: Sqlfluff the SQL Linter for Humans
#78Earlier quoted context omitted.
The reason that those people care about formatting is that it matters when the code is used to communicate with other people, not just with machines. Having a consistent style within the team, means the team can communicate more easily - even if the net effect for an individual working on a codebase alone is minimal.
Yes but realistically, save for the code that someone who's really inexperienced or sloppy would write, fixating over whether someone is writing select a, b, c from foo where bar = 'quux' rather than SELECT a, b, c FROM foo WHERE bar = 'quux' is petty and immaterial to what the code communicates. Worse if it's about details within the roughly the same style. Edit: but if there's a code formatter that can be seamlessl…
In trivial examples when we are all relaxed then yes, you would be right. It’s non-trivial occasions when we are stressed and trying to read someone elses code at two in the morning when I really value strict code style guidelines.
The ability to read complex code when we are in situations where we are not functioning at 100% is vastly under valued when one talks about developers capacity for comprehension.
Re: Sqlfluff the SQL Linter for Humans
#79This 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.
Seems like a good thing to check for! So we added a rule for this: https://github.com/sqlfluff/sqlfluff/pull/1527
Including autofixing if wanted.
Will be in the next release.
Re: Sqlfluff the SQL Linter for Humans
#80After relying on Prettier for a while, it feels very strange to me to have a linter that complains over minor formatting differences instead of just a tool that automatically fixes them.