PgFormatter: A PostgreSQL SQL syntax beautifier | Hacker News Reader

PgFormatter: A PostgreSQL SQL syntax beautifier

github.com

129 pointsfanf231 comments

Pasting "SELECT 1,2," then clicking [FORMAT MY CODE] on their online demo[1] seems to break something. It renders the site header, but nothing else.

[1] http://sqlformat.darold.net/

Also, a similar tool was recently discussed: https://news.ycombinator.com/item?id=18087514

injection lol

Do we need to use caps for SQL keywords? My understanding is that it served a purpose back in the days of monochrome screens, but modern IDEs colour the keywords so caps are now redundant. Though I don't use caps even without colouring, I find them just a nuisance.

No, not at all. SQL is case agnostic unless you're talking about field collations or, in many systems, object names. It's also largely whitespace agnostic. Many RDBMSs will handle this just fine:

  select*from"MyTable"where"column"=3

Ah, the whitespace agnostic didn't even occur to me!

Depends on your IDE. My editor only picks up SQL in strings sometimes.

I got over all caps SQL keywords a very long time ago. If anything, I think it hurts readability while taking (me) longer to write.

I prefer them because they make grepping for SQL code easier. Also syntax coloring inside strings is not common.

A 100% Perl project in 2018 is a rare sight.

(Implementing a SQL formatter in pure SQL is not impossible, but would be quite a tour de force. Just a thought.)

The most productive language of Gilles, well-known in the Postgres community by the way, is perl, and he can do completely crazy things with it. See for example pgbadger (https://github.com/darold/pgbadger), one of his other projects.

Sometimes this (https://zeroturnaround.github.io/sql-formatter/) does a better job for me. Doesn't support Postgres explicitly but works most of the time.

Am I the only one irked by the concept of beautiful SQL? SQL is beautiful by itself. Pick a style (indents,caps,whatever) that fits the project at hand. I have found it useful to write the more mundane parts of a project like a regular paragraph with minimal whitespace, and do more whitespace in areas that are more complex or more likely need attention later. Depending too much on a standard style (ironically) lends itself to some pretty ugly code, eg. tangling too many joins and subqueries that would be far better handled by separate statements.

The stark difference between a formatter and a formatter officially blessed by the parent project, is that the former is far more vulnerable to having its output viewed with a jaundiced eye and the formatter being cast away.

I remember trying the one from TFA and NOPE-ing out immediately. Similarly, "sqlfmt" that was on HN recently. If there was an official Postgres formatter, I would probably persevere despite whatever oddities it would inevitably have.

The sqlfmt one pulls in the cockroachdb parser, and that team has a fairly big incentive to be compatible with PG syntax. Nobody is perfect, but it seems set up to stay current/correct.

I meant the style choices. But it did also choke on Identity Columns IIRC (https://blog.2ndquadrant.com/postgresql-10-identity-columns/).

Not knocking either project - just pondering adoption.

I'll have to see if I can adapt this to my somewhat idiosyncratic syntax:

  SELECT field, other_field
    FROM table
   WHERE some_thing IN (
         SELECT id 
           FROM other_table
         )
     AND other_field IS NOT NULL

(mostly it's right-aligning keywords, but there are a few other weird tweaks)

I do similar, keeps both keywords and fields vertically aligned.

I do too, but it breaks down on me for some specific:

* ORDER BY

* INNER JOIN

etc

sql-indent for Emacs supports that: https://github.com/alex-hhh/emacs-sql-indent

I tried your SQL in the demo, and it came out like this:

    SELECT
        field,
        other_field
    FROM
        TABLE
    WHERE
        some_thing IN (
            SELECT
                id
            FROM
                other_table)
            AND other_field IS NOT NULL

I'd have expected the last line to have one less indentation level...

I’ve adopted the same format myself, it reminds me of aligning numbers at the decimal point.

sqlfmt gets pretty close to this: https://sqlfum.pt/?n=30&indent=4&spaces=1&simplify=1&align=2...

I like Notepad++'s Poor Man's SQL Formatter plug-in. Works well for anything I do.

I've never heard of a code formatter called a "syntax beautifier" before. The phrase "syntax beautifier" sounds more like a compiler plug-in that actually changes the language syntax to something more beautiful.

It's a relatively common name. I would say it might even be more common than the alternative of "code formatter". I do agree that the syntax of the language isn't changing, but it does seem like the phrase that has stuck.

I think the beautifier part refers to it applying syntax highlighting to the web output.

I really hope there will be a sql formatter like prettier for javascript, I have tried Intellij's sql formater and mysql workbench's sql formatter, neither has some warts.

https://www.cockroachlabs.com/blog/sql-fmt-online-sql-format... was on here last week. It's a sql formatter based on the same paper prettier is.

Looks like they might need a formatter to beautify their README too.

I might be missing something, but it seems like a fairly high quality README file. What do you feel is wrong with it?

Do you really need to serve ads on your demo site?

Maybe you do, but that doesn't seem very OSS.

Reply on news.ycombinator.com