Live data from Hacker News

Sqlfmt: an opinionated online SQL formatter

cockroachlabs.com

91–94 of 94 posts

Re: Sqlfmt: an opinionated online SQL formatter

#91
post #43

Earlier quoted context omitted.

Hey, thank you for the formatter! I really like the way it formats statements. Although I have found two issues: 1. It lowercases table and field names, rendering the formatted statement useless for a project with not exclusively lowercased names. Could you please fix this? 2. I'd appreciate accepting common placeholder "?" as a valid part of the SQL statement so that we can format queries with placeholders. Would th…

The formatter adheres to CockroachDB rules which is why it acts like you describe. Case is insensitive in names unless you double quote it. Postgres-style (CockroachDB is one) uses "$1" for placeholders instead of "?". Adding "?" would complicate our grammar enough that it's not worth adding it. sqlfmt is not super useful for non-postgres grammars.

I understand it about placeholders. Fair enough. I can do quick s/\?/\$1/g, no prob.

Still how about not lowercasing identifiers? This is actually a blocker, I'm sure you see it:)

Re: Sqlfmt: an opinionated online SQL formatter

#92
What I want: an SQL (actually, it could be just Postgres as I only use that) parser that takes two schema declarations and calculates the commands needed to go from one to the other, so we can have automatic migrations.

For example, if A is

  CREATE TABLE person (
    id serial PRIMARY KEY,
    name text
  )
and B is

  CREATE TABLE person (
    id serial PRIMARY KEY,
    name text,
    age int
  )
Then this tool would output

  ALTER TABLE person ADD COLUMN age int

Re: Sqlfmt: an opinionated online SQL formatter

#93
post #36

Why all the SCREAMING? Is something wrong? It's 2018, we aren't using teletypes with bad shift keys and we all have syntax coloring IDEs, we don't need to scream out all our KEYWORDS like we were still writing COBOL or FORTRAN in the 1960s. It's become my biggest pet peeve with most SQL formatters. I think sentence cased SQL is far more readable. Select * from sometable where SomeColumn > 3 But I realize I'm a minori…

Yours is a fair opinion and I can't come up with any reason why it's wrong. Personally I like it because it clearly separates keywords from objects (tables, columns, databases, etc). This is very much like the tabs vs. spaces debate.

Syntax highlighting already clearly separates keywords from everything else, and more flexibly to individual taste. (The keywords being both uppercase and bright bold purple on the linked page here particularly caught my personal distaste.)

Certainly there are still far too many places people are writing/reading/interacting with SQL without the benefit of syntax highlighting, but we have the technology to solve that (and in many cases need to solve that; a lot of the places that SQL is embedded and the outer language has no idea about are opportunities for SQL injection attacks).

> This is very much like the tabs vs. spaces debate.

It's sadly worse than that. It's something like 60 years of momentum versus user/developer experience and readability concerns.

Despite my under-appreciated jokes that most SQL databases probably don't need to be shouted out to do their jobs (Oracle maybe being an exception. ;), it actually is a serious readability issue.

We have decades of research showing that people read/scan by word shape and that lower-case and mixed-case is far faster and easier to scan/read than all caps. 'from' has a more interesting and relatively more unique shape than 'FROM', making it easier to spot. (Especially in a monospace language where the outermost shape of all four letter all caps words is essentially the same width and height rectangle.) (If you try a syntax highlighting scheme that does something more interesting than bolding keywords, italicizing them with a good italic monospace font, the lower-case word 'from' can be almost beautiful and very easy to find. A nice benefit to syntax highlighters is that sort of variation is a personal option between you and your editors of choice.)

Typesetters have known for a long time that all caps is slower to read, and should be used sparingly because it exhausts readers.

Sentence-casing SQL statements benefits from the readability of all the English we read everywhere else.

I make jokes about shouting in our database queries, because it's sadly easier than changing more than a half-century of momentum in the status quo, but I do think that all-caps keywords in SQL statements is a huge papercut in dev productivity/ergonomics that we have the technology to fix but apparently not the interest/willpower.

Re: Sqlfmt: an opinionated online SQL formatter

#94
post #90
post #17

Author here. Based on feedback I am going to make a sqlfmt binary for easy editor integration. You can currently already do this by using the CockroachDB binary ( https://www.cockroachlabs.com/docs/releases/v2.1.0-beta.2018... ) but understandably people want a more minimal solution.

Hey! I love your tool. Any chance of allowing keywords that are specific to a SQL implementation? (e.g. PostgreSQL) If I enter "CREATE MATERIALIZED VIEW ... WITH DATA;" in for example, it would tell me "materialized" and "with data" is wrong.

As CockroachDB matures these kinds of things will slowly get added.

But sqlfmt doesn't (currently) have a goal to format all SQL, just cockroach SQL.

Post reply on HN