Introducing sqlfmt, an auto-formatter for dbt SQL

Show and Tell
1

Since becoming an analyst and learning SQL, I have formatted my SQL queries in every possible way. After meeting the folks at dbt Labs (nee Fishtown) four years ago, I was inspired by their SQL style guide; after spending more time writing python and using black, I desperately wanted an auto formatter like black for dbt SQL.

Over the past few months, I have been hacking away at that tool, and today it works with dbt jinja and creates well-formatted, modern-looking SQL. It’s not a linter (use SQLFluff for that) – it just does one thing and does it well.

You can try it at sqlfmt.com, or check out the repo on Github. Or if you like to live dangerously, pipx install shandy-sqlfmt

It’s still really early innings. There are more features and integrations I’d like to build (and I’m sure plenty of bugs yet to be found). I would really, really value any contribution from this community, whether that’s feedback, an issue, or a PR. Thanks for checking it out; please let me know what you think!

10 Likes
2

Installed and started playing around with it over the weekend. Great work.

I love an opinionated formatter: we’ve been using black for a while in our Python libraries, and it really reduces the cognitive (and conversational) load. So I’m excited for an equivalent for our SQL files. (And I like that it starts from the dbt Labs style guide.)

1 Like
3

Thanks for contributing!

4

Hi,

Is it possible to force the ands to align here?

Autoformatted:

select *
from historic_answers ha
where
    subject = 'Math' and learningobjective = "Addition" and dt_utc > date(
        '2020-10-01'
    ) and testschool = false

Desired:

select *
from historic_answers ha
where 1=1
    and subject = 'Math' 
    and learningobjective = "Addition" 
    and dt_utc > date('2020-10-01') 
    and testschool = false

Or does this go against some of the fundamental rules of your formatter?

5

I agree your solution is better. It’s been on my mind to improve the formatting of chained boolean operators with parentheses for some time. Can you open an issue for this?

1 Like
6

Good that you mention it, should I open two issues:

  1. Force separate where clauses on single lines (even if they don’t exceed max width)
  2. Improve formatting of chained boolean operators with parentheses

Or do you see these two issues as one?

P.S. it would be awesome if there was a way to change the line length in the interactive web version :slight_smile: I use it a lot when showcasing it to people but the default line length is a bit short for most people

7

Sorry, my notifications for discourse started going to Spam! #2 is what I had in mind, it basically implies #1… the real issue today is the interaction between the parentheses-driven line splitting and merging and the boolean operator-driven line splitting and merging.

Re: line-length online, sure thing! I’ll create an issue for that one on the sqlfmt.com repo

8

Just wanted to say thanks for making this! I’m using it on on a project at work and so far it’s been great!

1 Like
9

@janmeppe Line length config is now available on sqlfmt.com: click “Configure Formatting” next to the blue button for the available options.

1 Like
10

No way you can hack this or sqlfluff into dbt cloud… any option to have decent formattation on dbt cloud as we speak or coming soon?

11

Not at the moment, but hopefully one day :crossed_fingers:

2 Likes
12

The new dbt Cloud IDE has a format button that is powered by sqlfmt! It’s in Beta right now, GA early next year

1 Like
13

great feature - I hope it can be customised to match your/my formatting preference

14

hey on the http://sqlfmt.com/ website you are able to configure the max line length. Is this config (and other settings) possible in dbt?

15

No, formatting in dbt Cloud is currently not configurable. You can configure line length if you download the sqlfmt CLI and use it with dbt Core.

See: Installation | sqlfmt
And: Configuring sqlfmt | sqlfmt

1 Like
16

shame… I hope this comes in future releases of dbt Cloud

17

I tried this over some of our SQL code but I see that it reorders the columns in my query, is this intended behaviour?

18

No, it shouldn’t reorder columns. Can you please share an example either here or by opening an issue on GitHub?

19

I guess it is expected? If you look at rule “Rule_ST06” in SQLfluff, that is exactly what is happening with sqlfmt as well… I assume those rules are also used by sqlfmt? Or how does sqlfmt use SQLfluff?

20

sqlfmt and sqlfluff are two completely different projects. This post is about sqlfmt.com, which is the pypi package shandy-sqlfmt. Unfortunately the PyPI package called sqlfmt is a separate, abandoned project that uses sqlfluff under the hood. Is it possible you installed sqlfmt instead of shandy-sqlfmt?

1 Like