Automatic formatter for SQL

[jml]

Describe the feature

dbt fmt, a sub-command that automatically formats the SQL in a dbt project.

Ideally, this could be run as a pre-commit hook. It would edit files in place, operating in much the same way as black or go fmt

Describe alternatives you've considered

• https://pypi.org/project/sqlparse/ – does a very bad job with complex queries, doesn't understand that SQL might contain Jinja2 templates. Underlying parser is somewhat dodgy.
• Writing my own – fun but I don't have any time. Also, would have to decide between my own use case (BigQuery) vs something more general
• Manually formatting SQL – what we're doing now. It's a pain, and it's a waste of human creativity and energy to manually correct ones colleagues about formatting in code reviews

There's a case to be made for a super general SQL-over-Jinja2 formatter, to let people choose whether they want things like upper case keywords, but I haven't met a single person who has started using an opinionated formatter like black or go fmt who hasn't been willing to sacrifice their personal preferences for never having to care about formatting ever again.

Additional context

Not database specific, but there may well be gotchas with syntax varying between databases.

Who will this benefit?

This will primarily benefit people writing transformations, as it will mean that they won't have to care about formatting.

It will secondarily benefit code reviewers, who will no longer have to check for these things, which in turn means that teams writing analytic SQL with dbt will be able to move faster.

[drewbanin]

great idea @jml! This is something we've been mulling over in the #linters channel in dbt Slack. I love love love the idea of this, and I started a (very rough) version of this over here: https://github.com/fishtown-analytics/dbt-sql-formatter

If you're in the market for something that exists today, check out sqlfluff. This is very much on our radar too though, and we're looking forward to integrating something like this directly into dbt someday :)

[alanmcruickshank]

Heya guys - sqlfluff has had a ton of upgrades recently, particularly on linting and fixing code for data warehouses like snowflake and bigquery.

I expect the next release (in a few weeks) might be worth doing some basic testing with (if you want sneak peaks, feel free to check out the current master branch). I'd also love to do some work on a public api (so that you can import sqlfluff and get sensible responses) so that it could be incorporated into dbt.

If either of you have any suggestions on what you'd want to see from a public api for sqlfluff, feel free to lodge a github issue for the kind of thing you'd like to see. This is definitely on the outline roadmap for things I'd like to support before the next major release.

[mikaelene]

A linter that works with PyCharm (and preferably every other tool there is) would be magic.

[astorrs]

sqlfluff has support for dbt these days which is a good interim solution.

[mamatucci]

crazy there is still nothing so basic in dbt cloud!!! :P
I usually checkout locally and format on my code env and push back to git before the last execution to validate all is fine in dbt cloud ...

[ismailsimsek]

This feature is now implemented by opendbt project with further features. PR memiiso/opendbt#18

commands are

opendbt sqlfluff lint
opendbt sqlfluff fix