How to match CLI formatting with VS Code Prettier-SQL format?

[waynesbrain]

I wanted to use the same formatting from the CLI as the VS Code Prettier-SQL formatter does and I found this repo from that page.

So I installed sql-formatter as a dev dependency and created a .sql-formatter.json file with:

{
  "$schema": "https://raw.githubusercontent.com/sql-formatter-org/sql-formatter/refs/heads/master/schema.json",
  "language": "postgresql",
  "expressionWidth": 100,
  "keywordCase": "upper",
  "logicalOperatorNewline": "after",
  "linesBetweenQueries": 0,
  "tabWidth": 2,
  "useTabs": false,
  "dataTypeCase": "upper"
}

Trying to match my VS Code Prettier-SQL settings, which are:

{
    "Prettier-SQL.expressionWidth": 100,
    "Prettier-SQL.keywordCase": "upper",
    "Prettier-SQL.logicalOperatorNewline": "after",
    "Prettier-SQL.SQLFlavourOverride": "postgresql",
    "Prettier-SQL.linesBetweenQueries": 0,
}

But the CLI doesn't format nearly the same way as the extension. Do I have to use an older version of sql-formatter or something?

Here is how it formats with the CLI

CREATE TABLE "rev_log" (
  "id" bigserial PRIMARY KEY NOT NULL,
  "schema_name" TEXT NOT NULL,
  "table_name" TEXT NOT NULL,
  "relid" "oid" NOT NULL,
  "action" TEXT NOT NULL,
  "at" TIMESTAMP WITH TIME ZONE NOT NULL,
  "session_user_name" TEXT,
  "tx" BIGINT,
  "app" TEXT,
  "addr" "inet",
  "port" INTEGER,
  "data" JSONB,
  "query" TEXT
);

And here is how it formats with VS Code

CREATE TABLE
  "rev_log" (
    "id" bigserial PRIMARY KEY NOT NULL,
    "schema_name" TEXT NOT NULL,
    "table_name" TEXT NOT NULL,
    "relid" "oid" NOT NULL,
    "action" TEXT NOT NULL,
    "at" TIMESTAMP WITH TIME ZONE NOT NULL,
    "session_user_name" TEXT,
    "tx" BIGINT,
    "app" TEXT,
    "addr" "inet",
    "port" INTEGER,
    "data" JSONB,
    "query" TEXT
  );

[waynesbrain]

Turns out that the answer is indeed to use an older version, specifically sql-formatter@^12.0.2 (12.2.4 works fine even though the extension installs with 12.0.2.)

With that, the only difference between CLI and VS Code formatting is that the CLI adds a final newline and VS Code Prettier-SQL does not.

[nene]

Well... if you really want to use that outdated & unmaintained VSCode extension, then yes, you only option is to also use an old version of this library.

I would recommend though switching to the official sql-formatter-vscode extension. Then you could use both the latest version of the command line tool and the latest version of the VSCode plugin.

[waynesbrain]

Thanks!!

I certainly had the oldness of the old version in mind as I discovered the workaround. Also the name Prettier-SQL kinda threw me off because previously I was under the impression that it was officially related to prettier or something, so I probably will switch the VS Code extension that I'm using very soon.

[waynesbrain]

After looking into pgFormatter for a little while today, I actually decided to just use the newest version as you suggested here because it's very close to what is coming out of pgFormatter and pgFormatter has it's own issues. Thanks again!

[nene]

Alternatively, as you're using PostgreSQL, you might want to try prettier-plugin-sql-cst, which does a much better job at formatting SQL, and you can seamlessly also use it from command line like any other prettier plugin. The caveat is that it doesn't have full PostgreSQL support. Then again, SQL Formatter doesn't have a full support either, but it usually fails more gracefully.

[waynesbrain]

I decided to go a different route and I asked "What is the best Postgres formatter from any programming language anywhere" and Brave search said pgFormatter which looks to be somewhat officially accepted by Postgres since I found mention of it also on postgresql.org

So I tried pgFormatter here and it seems good and then I saw that it works anywhere you can run Perl which is fine for a dev-time tool IMO (I'm on Manjaro, so fine with me) and then I found this pgFormatter VS Code extension and a related npm wrapper for pgFormatter library from the same developer of the VS code extension. I suppose I won't need the npm lib because I just make straight .sh scripts for my developer commands.

Idk if this will work out. I think I had tried this VS Code extension in the past and maybe back then it was a PITA to switch between postgres-only and other dialects in VS Code, but I can't remember. Anyway - I'm just babbling now 🤣 Thanks for your help