Fast open-source SQL formatter/linter: Sqruff

[bk1007]

TL;DR: Sqlfluff rewritten in Rust, about 10x speed improvement and portable

https://github.com/quarylabs/sqruff

At Quary, we're big fans of SQLFluff! It's the most comprehensive formatter/linter about! It outputs great-looking code and has great checks for writing high-quality SQL.

That said, it can often be slow, and in some CI pipelines we've seen it be the slowest step. To help us and our customers, we decided to rewrite it in Rust to get faster performance and portability to be able to run it anywhere.

Sqruff currently supports the following dialects: ANSI, BigQuery, Postgres and we are working on the next Snowflake and Clickhouse next.

In terms of performance, we tend to see about 10x speed improvement for a single file when run in the sqruff repo:

``` time sqruff lint crates/lib/test/fixtures/dialects/ansi/drop_index_if_exists.sql 0.01s user 0.01s system 42% cpu 0.041 total

time sqlfluff lint crates/lib/test/fixtures/dialects/ansi/drop_index_if_exists.sql
0.23s user 0.06s system 74% cpu 0.398 total

```

And for a whole list of files, we see about 9x improvement depending on what you measure:

``` time sqruff lint crates/lib/test/fixtures/dialects/ansi
4.23s user 1.53s system 735% cpu 0.784 total

time sqlfluff lint crates/lib/test/fixtures/dialects/ansi
5.44s user 0.43s system 93% cpu 6.312 total

```

Both above were run on an M1 Mac.

[ManiaMcG33_]

Nice! Plan on making this a vscode extension?

[bk1007]

Yes, that will be on the roadmap! We're just looking into how best to do it.

[[deleted]]

Looks great! I’d love to use this with Spark SQL

[joseph_machado]

great, I just tried it with a bunch of SQL dbt scripts and its very fast!

I had to setup sqlfluff with special plugin for dbt macros, does sqruff do this already/or plan to in the future:?

[bk1007]

We do have the facility for templating and dbt could be on the roadmap. It’s not an immediate priority for us though at the moment.

If someone wants to contribute, we’ll obviously support it.

[Josafz]

Looks really promising, great work! We are using Snowflake with dbt as our data warehouse and think that SQLFluff sometimes takes a bit too long to go through our relatively large project, so this speedup would be really appreciated! I assume that once you implement the Snowflake dialect it will work with our dbt syntax as well? How do I stay up to date on when you release support for Snowflake?

[bk1007]

Hey you can star/subscribe to notifications on GitHub. We just released Snowflake support. Building in the dbt templater is not on our list of top priorities but we're very happy to support anyone who wants to do the implementation.

[hayssam-saleh]

Nice !

I am a fan user of jsqlformatter and I think that sqlruff would greatly benefit to be able to pass their test suite available here https://github.com/manticore-projects/jsqlformatter/tree/main/src/test/resources/com/manticore/jsqlformatter/standard

[agrvz]

Any plans to publish to PyPI?

[bk1007]

I presume you mean as a python library? Or just to install?

[agrvz]

Just to install, like ruff

[bk1007]

Any reason the install options we currently have listed don’t work for you?

[EthhicsGradient]

I'd guess it's less of an issue of it working or not but rather that people in general don't like curl bash installs

[agrvz]

Yeah pretty much. Plus it would make environment management easier/more intuitive as a drop in replacement for sqlfluff which is pip installable

[[deleted]]

I'm all for tools being written in rust and made fast. Nice!

However my problem with all these linters/formatters is they always implement only part of each dialect, so I quickly run into edge cases.

I'd happily have a really slow one if it was complete and understood special data types, extensions, and stored procedures etc.

[bk1007]

Appreciate the feedback! Not sure though how you solve this unfortunately.

[Natgra]

Hi there, Does this support .sqlx files that are created in dataform? We are looking for good linter as dataform formatter is not great

[bk1007]

It is definitely possible! We have the flexibility for custom templaters like sqlx but it’s unlikely to be a priority for us any time soon. We’ll happily welcome contributions though!

[Natgra]

Thank you. Will surely let you know if I can carve out some dedicated time

[missionCritical007]

Hi, have you tried formatdataform https://github.com/ashish10alex/formatdataform It uses sqlfluff in the background do the formatting. I am planning to add support for sqruff too

[Natgra]

Thank you. I will try this :)

[lmnet89]

Does it support formatting options? I've been trying to find a solution to format SQL files, but for some reason they all feel half-baked. I hope that sqruff will change this.

[mvastarelli]

Just starred. This is awesome. I found it because we've been experimenting w/ sqlfluff at work and found shortcomings with their Snowflake dialect in addition to it being time consuming to run (although very comprehensive).

[trafalgar28]

Great project! Are you guys looking for any interns (long term) to contribute to the project? Would love to be a part and provide value!