Autoformatting

[benjamin-kirkbride]

Supabase likely has already discussed these things internally, but figured I would throw my opinion into the wind and see what others think, as this is a topic that I have spent a lot of time thinking about and playing around with.

Autoformatting Configuration

I am a big believer in highly opinionated syntax formatting, a la black, gofmt, Prettier, etc

This helps users (even if they don't know it) by reducing bikeshedding possibilities and enforcing consistent style, and it helps the autoformatter developers by not having to support a exponential computational configuration explosion.

Some would argue that with how much history SQL has that an opinionated formatter would not be popular because there is so much history and differing opinion on how to format things, but I think that the explosion in popularity of black and prettier have proven that argument to be invalid; people are desperate to not have to think about where to put the newlines.

(I am not saying there should be zero config available, just that we should be very aggressive about limiting them. An obvious config option would be line length.)

Idempotency and Determinism

It is very important that the autoformatting is both idempotent and deterministic (perhaps with a few intentional and documented exceptions)

Basically, any syntax that is interpreted to the same AST should result in the same syntax output (assuming the same config).

Canonical DDL Format

Postgres (kind of?) already does this for DDL: pg_dump (I believe) has a deterministic formatted output for DDL. Do we want our autoformatter to be the same format as that? I don't think that should be a goal unless we can somehow extract whatever library/module does it (like we do with libpg_query) and use that. Honestly even if we could it probably still isn't a great idea, as I assume it was not designed for this purpose, and likely it only supports DDL.

Scope

What is the scope of the autoformatter? This is where other formatters fall short.

• DML: SELECT, INSERT, UPDATE, and DELETE will obviously be supported 😅, but what about:
  • CTEs
  • Window Functions
  • JSON Operations
  • Arrays
  • Composite types
  • Joins (including lateral joins)
  • RETURNING
• DDL: might seem obvious, but DDL in Postgres is a lot.
  • Views
  • FDW
  • Stored procs
  • Functions
  • Custom types
  • Inheritance
  • extensions
  • Schemas
  • Permissions
  • Triggers
  • Temp and unlogged tables
  • Partitioning
  • Tablespaces
  • Collations
• PL/pgSQL: Does libpg_query support PL/pgSQL?

What Format?

(I am assuming here that we agree with my above section)

Keyword Case

SELECT * FROM table;

vs

select * from table;

This honestly may be appropriate as a config, as I think people care a lot about which is used, and it (seems) trivial to support both.

Comma Placement

SQL (including Postgres flavored SQL) is notorious for lacking DX niceties such as trailing commas (pls Tom). (very related to the "New Lines" section below)

select a
    , b
    , count(*) as count
from table;

vs

select 
    a,
    b,
    count(*) as count
from table;

New Lines

When do we break a list/clause into multiple lines? This might be an instance where we take into consideration existing formatting, but this is very tricky. See black magic trailing comma.

The obvious case where we have a newline is when we hit the max line length.

Indentation

2 characters? 4 characters? try to vertically align things (please god no)? Indentation characters might be a reasonable config option..

[kiwicopple]

as a fan of Prettier, I agree with this approach. It's going to be tough to reach consensus here on what the defaults should be - I can already see this becoming one of the most contentious Discussions.

I haven't seen any "anointed" Postgres formatting rules (eg: officially recommend by PG for pg_dump, docs, etc), so if anyone knows please share them.

We'll do our best to accommodate everyone's preference. We won't be able to make everyone happy here (and I'm saying that as a lowercase writer), so let's just aim for the least-worst option with sensible configuration options

[9mm]

I think pgFormatter does a good job https://github.com/darold/pgFormatter

[psteinroe]

hey @benjamin-kirkbride,

once again, thanks for the very valuable discussion!

An auto formatter is definitely on the roadmap. let my try to answer your questions and share the current state of thoughts:

Autoformatting Configuration

fully agree with copple here. If there is a "standard" we will use it. if not, I think pgFormatter does a good job here. but either way, it will be configurable to deviate from the standard. the biggest question to answer will be the default setting for the keyword casing I guess. 😄

Canonical DDL Format

Fully agree here, I think our use case is different than the one from pg_dump.

Scope

This is where we can shine. we are using the same code that postgres itself uses to parse sql. hence, everything that is standard postgresql is supported. there are a few things that aren't though:

• extensions that introduce a special syntax
• PL/pgSQL is supported only experimentally. the parser outputs a json. we might be able to integrate that and support PL/pgSQL formatting

What Format

fully agree on all of the points. we will define an optioned default, but let people configure it. I don't have a strong opinion here. I would first research default settings that other tools use, make a proposal, and then have the community decide ultimately.

[Revadike]

Looks like the supabase docs already favor a sql format. I'd love a default or even optional config that matches that.

[Revadike]

https://supabase.com/docs/guides/getting-started/ai-prompts/code-format-sql

[Revadike]

I'd love an autoformat option in vscode that triggers onsave, much like ESLint does!

[Revadike]

It can use the defaults as defined in here:
https://supabase.com/docs/guides/getting-started/ai-prompts/code-format-sql