Format SQL with Prettier

[nryberg]

Check for existing issues

• [X] Completed

Describe the feature

While some languages already have automatic code formatting built in, I'd like to use Prettier to help format SQL files.

https://prettier.io/docs/en/editors.html

Super happy to see SQL syntax highlighting as a plugin - would like to take it to the next level with SQL formatting.

If applicable, add mockups / screenshots to help present your vision of the feature

Have:

Want:

[SomeoneToIgnore]

Zed does not have any support for SQL in it, and the only current extension (from https://github.com/zed-industries/extensions/tree/main/extensions) that supports it, does not have the required prettier metadata: https://github.com/evrsen/zed-sql/blob/8f05732666dc29577933d5ad0b72ce5a6d6840ea/languages/sql/config.toml

It seems that the SQL maintainer has to update the config to have something similar to https://github.com/zed-industries/zed/blob/main/extensions/svelte/languages/svelte/config.toml#L17-L18 and release the new version.

[notpeter]

The SQL extension now specifies prettier_plugins = ["prettier-plugin-sql"] but this is not working for me.

See:

• https://github.com/zed-industries/zed/issues/19024

[tashi21]

It is not working for me either.

Settings file:

{
  "formatter": "auto",
  "languages": {
    "SQL": {
      "formatter": "prettier",
      "prettier": {
        "allowed": true,
        "parser": "sql",
        "plugins": ["prettier-plugin-sql"]
      },
      "tab_size": 2
    }
  }
}

Prettier debug logs: stderr: Resolved config: {}, will format file '/Path/to/file.sql' with options: {"printWidth":100,"useTabs":false,"tabWidth":2,"plugins":[],"parser":"sql","filepath":"/Path/to/file.sql"}

If I remove the prettier options from the settings file such that:

{
  "formatter": "auto",
  "languages": {
    "SQL": {
      "formatter": "prettier",
      "tab_size": 2
    }
  }
}

The debug logs read: stderr: Resolved config: {}, will format file '/Path/to/file.sql' with options: {"printWidth":100,"useTabs":false,"tabWidth":2,"plugins":[],"parser":null,"filepath":"/Path/to/file.sql"}

For some reason prettier is not picking up the "plugins" key from the settings file (even though in the default settings also it mentions "prettier-plugin-sql" in the plugins array. The only way I am able to include it is if I add a .prettierrc.json file in my project workspace with the "plugins" key and mentioning the plugin name there.

Debug logs: stderr: Resolved config: {"plugins":["prettier-plugin-sql"]}, will format file '/Path/to/file.sql' with options: {"printWidth":100,"useTabs":false,"tabWidth":2,"plugins":["prettier-plugin-sql"],"parser":"sql","filepath":"/Path/to/file.sql"}

[tashi21]

I am setting some configuration options for the plugin in my .prettierrc.json but they aren't being respected. The server debug logs show that these are being read, but do not get implemented when formatting.

{
  "plugins": ["prettier-plugin-sql"],
  "overrides": [
    {
      "files": "*.sql",
      "options": {
        "language": "postgresql",
        "dialect": "postgresql",
        "keywordCase": "upper",
        "dataTypeCase": "upper",
        "functionCase": "upper"
      }
    }
  ]
}

Because of this, none of the formatting actually happens, not that it doesn't use the given config options. Even the default formatting that happened before adding the "overrides" block.

[fresonn]

Ыtill have prettier error on specific syntax 😔

This likely happens because you're using the default "sql" dialect. If possible, please select a more specific dialect (like sqlite, postgresql, etc).

[binajmen]

it seems to me that the values passed to prettier are simply ignored? the formatting is working for simple cases, but will not when the need of specifying the language (postgresql) is expected.

    "SQL": {
      "hard_tabs": true,
      "tab_size": 2,
      "prettier": {
        "allowed": true,
        "plugins": ["prettier-plugin-sql"],
        "parser": "postgresql",
        "options": {
          "language": "postgresql"
        }
      }
    },

[firu11]

Same for me. If I set parser to "sql", it says:

This likely happens because you're using the default "sql" dialect.
If possible, please select a more specific dialect (like sqlite, postgresql, etc).

If I set parser to "postgresql":

ConfigError: Couldn't resolve parser "postgresql".

This is my config (NOT WORKING):

{
    ...

    "languages": {
        ...

        "SQL": {
            "formatter": "prettier",
            "prettier": {
                "allowed": true,
                "plugins": [
                    "prettier-plugin-sql"
                ],
                "parser": "postgresql",
                "options": {
                    "language": "postgresql"
                }
            }
        }
    },
    "formatter": "language_server"
}

[App1ePine]

I also encountered this problem：handling: ConfigError: Couldn't resolve parser "mysql". Has anyone solved the problem of formatting SQL in the Zed editor?

[notpeter]

I'm not actually sure how to set the dialect for prettier-plugin-sql which under the hood that uses sql-formatter-org/sql-formatter.

If anyone figures out how to specify this I would love to add this to the docs.

For the same thing, but with postgres, see also:

• https://github.com/zed-extensions/sql/issues/12

[notpeter]

Prettier uses prettier-plugin-sql which leverages sql-formatter-org/sql-formatter under the hood. You can download this with npm install -g sql-formatter.

I was able to get formatting working by bypassing prettier with the following zed settings:

  "languages": {
    "SQL": {
      "formatter": {
        "external": {
          "command": "sql-formatter",
          "arguments": ["--language", "mysql"]
        }
      }
    }
  },

@App1ePine Can you try this and see if it works for you? @Firu115 / @binajmen Can you try the same with postgresql instead of mysql?

[App1ePine]

Prettier uses prettier-plugin-sql which leverages sql-formatter-org/sql-formatter under the hood. You can download this with npm install -g sql-formatter.

I was able to get formatting working by bypassing prettier with the following zed settings:

"languages": { "SQL": { "formatter": { "external": { "command": "sql-formatter", "arguments": ["--language", "mysql"] } } } }, @App1ePine Can you try this and see if it works for you? @Firu115 / @binajmen Can you try the same with postgresql instead of mysql?

Great, this method works. sql-formatter can perform formatting normally. Thanks! @notpeter

[binajmen]

Prettier uses prettier-plugin-sql which leverages sql-formatter-org/sql-formatter under the hood. You can download this with npm install -g sql-formatter.

I was able to get formatting working by bypassing prettier with the following zed settings:

"languages": { "SQL": { "formatter": { "external": { "command": "sql-formatter", "arguments": ["--language", "mysql"] } } } }, @App1ePine Can you try this and see if it works for you? @Firu115 / @binajmen Can you try the same with postgresql instead of mysql?

It seems to work with postgresql as well, although I hadn't the chance to test this exhaustively on complex queries with postgresql-specific syntax: it seems this is what was blocking last time I had the issue. Will keep you posted if I notice something. Thank you!

[notpeter]

I've documented specifying sql-formatter as an external formatter here:

• https://github.com/zed-industries/zed/pull/32003

And removed the prettier-plugin-sql as the default formatter from the SQL extension here:

• https://github.com/zed-extensions/sql/pull/19

Users will now have to opt into autoformatting with the above settings which are now included in the Zed SQL Language Docs.

[sulgg]

I've documented specifying sql-formatter as an external formatter here:

* [Add sql language docs #32003](https://github.com/zed-industries/zed/pull/32003)

And removed the prettier-plugin-sql as the default formatter from the SQL extension here:

* [Remove prettier-plugin-sql zed-extensions/sql#19](https://github.com/zed-extensions/sql/pull/19)

Users will now have to opt into autoformatting with the above settings which are now included in the Zed SQL Language Docs.

Thanks! It worked for me but I had to rename the configuration file to .sql-formatter.json -- with the dot before.

[notpeter]

Thanks! It worked for me but I had to rename the configuration file to .sql-formatter.json -- with the dot before.

Thanks. Fixed in https://github.com/zed-industries/zed/pull/32312

[bbigras]

Can I have .sql-formatter.json per folder, or is it per project? I have some projects with multiple sql "languages".

[notpeter]

@bbigras From the sql-formatter readme:

The tool also accepts a JSON config file named .sql-formatter.json in the current or any parent directory, or with the --config option

I haven't tested (you should) to see whether it actually checks next to the file or in the working directory where it was invoked. I believe the current behavior of Zed is to invoke the formatter from the workspace root.

But if sql-formatter doesn't look next to the file, you could probably create a script that would change dir before invoking sql-formatter to get the behavior you're looking for.

Untested...

Instead of using:

  "languages": {
    "SQL": {
      "formatter": {
        "external": {
          "command": "sql-formatter",
          "arguments": ["--language", "mysql"]
        }
      }
    }
  },

Create a sql-formatter-here.sh and put it in your path (or inside you project)

#!/bin/bash
[ -z "$1" ] && { echo "Usage: $0 <filename>"; exit 1; }
dir=$(dirname "$1")
file=$(basename "$1")
cd "$dir" && sql-formatter "$file"

Then you could invoke:

  "languages": {
    "SQL": {
      "formatter": {
        "external": {
          "command": "sql-formatter-here.sh"
        }
      }
    }
  },

Let me know if you get something working.