Show HN: SQL Formatter | Hacker News Reader

Show HN: SQL Formatter

normaltool.com

26 pointspictur13 comments

Maybe it's just me, but I find the sample input more legible than the output.

I'm of the same opinion. I always wonder why SQL formatters do this. Is it because it's easier to diff queries with versioning tools? Because to me it's definitely not for legibility. The example shown here isn't even that bad compared to what I see regularly. We've got quiet a few queries longer than 100 sanely written lines running in BigQuery, sometimes I missclick the "format query" option and it always makes me wonder who the hell enjoys scrolling so much because suddenly the line count increases by an order or magnitude or more and the lines aren't much longer than 20 or so characters.

I think formatters are generally written by people who value consistency above other criteria, as many of the general conventions used when handwriting queries tend to break down as the complexity of the query increases. With these formatters I imagine complex queries would render in a more 'consistent' manner.

Yes, and that tends to be the case with formatters for programming languages in general.

I agree that the example input is more readable than the output, but I also think it's a bad example of messy SQL. sometimes it's nice to use a tool like this to break down a complex, appended-to-by-multiple-authors-over-the-years query just so you can understand the logic (and sometimes find unexpected logic bugs that nobody noticed before). at least, that's been my experience.

I make SQL queries even bigger, and for me it's not so much about reading but being able to copy and paste bits of it easily (easy meaning any line is just one "token"), to modify it or use parts of it elsewhere.

clickhouse-format does it better:

    SELECT COUNT(*)
    FROM table
    ;

    SELECT *
    FROM table
    WHERE (frozen_rand >= 123) AND (frozen_rand <= 456)
    ORDER BY RAND() ASC
    LIMIT 1000
    ;

It also has the "obfuscate" option that is useful for test reports.

    clickhouse-format --obfuscate

What does an obfuscate output look like

The example here is misformatted:

  frozen_rand BETWEEN % (rand_low) s
  AND % (rand_high) s

Should all be on one line, those aren't two different conditions.

I guess it's newlining on certain key words like AND which is ok if it's a new where condition but unnecessary if it's part of an between clause.

Shall we even discuss the weird SQL injection stuff there, with what looks like Python string formatting? Use bind parameters, people!

Thanks. This will be useful for quick queries without an install.

Otherwise there is also SQLFluff: https://sqlfluff.com/

I think everyone one has his own preference on formatting so the ability to customize such formatters would be the best feature.

Reply on news.ycombinator.com