It'd be great if you could release this extension on the open-vsx.org repository so people using Code - OSS and VSCodium binaries are able to install this extension.

Here is a guide on how to publish to open-vsx.

And thank you for all the effort on supporting this great sql formatter!

Describe the Feature
Would be helpful if there was a flavour for Snowflake's customised dialect.

Why do you want this feature?
Snowflake is the trending datawarehouse due it's unique features.

Info

• How would you be calling / using the script? NA
• What SQL language(s) does this apply to? NA
• What Node version? (If applicable) NA

Additional context
Add any other context about the feature here.

Yarn 2 features new Plug'n'Play that deprecates node_modules in favour of .pnp.cjs
Info: https://yarnpkg.com/features/pnp

Describe the bug

Multi-character operators in PostreSQL syntax are not recognized.

I prefer to have the denseOperators config set to false so that I can have nice spacing in my SQL for readability. Unfortunately with that configuration set, JSON and JSONB operators go from ->> to - > > which causes PostgreSQL to throw an error stating unexpected >.

More about those operators here: PostgreSQL Docs

Expected Output

Given the following SQL for PostgreSQL:

select
    id,
    data->>'name'
from
    table_with_jsonb_column
;

I expect to format as:

select
    id,
    data ->> 'name'
from
    table_with_jsonb_column
;

Actual Output

What is currently formatted as:

select
    id,
    data - > > 'name'
from
    table_with_jsonb_column
;

Usage

• How are you calling / using the script? (Please provide a code snippet, if applicable)
• What SQL language(s) does this apply to?
• What Node version? (If applicable)

I am using this through the VS Code extension which uses ^5.0.0-beta.1 of this package. I tried to confirm that it was not an issue with the extension as best I could, but apologies if it turns out this is a bug with the extension and not this package.

I believe these operators are likely only for PostreSQL dialects of SQL.

Additional context
Add any other context about the problem here.

Current newline mode is not the most streamlined it could be, it should avoid having a nested optional config and reduce down to:
always | never | lineWidth | number (itemCount)

For some keywords, they are only ever used as part of a larger statement that should be formatted as one piece
(ie. - Spark: ITEMS is only in HIVE COLLECTION ITEMS, TERMINATED is only in TERMINATED BY, etc.)

There may not be an easy of doing this besides hand checking every keyword

Describe the bug
When trying to parse oracle sql files no parser could be infered by prettier.

["INFO" - 11:42:13] File Info:
{
  "ignored": false,
  "inferredParser": null
}
["WARN" - 11:42:13] Parser not inferred, trying VS Code language.
["ERROR" - 11:42:13] Failed to resolve a parser, skipping file. If you registered a custom file extension, be sure to configure the parser.

To Reproduce
Open a .sql file with oracle psql code.
Run Format Document (force)

Expected behavior
File gets formatted by prettier-sql

This repository will be consumed by the upstream fork, please go ahead and use https://github.com/sql-formatter-org/sql-formatter.

Describe the bug
When using a DELIMITER there is an erroneous output. Try to format the following code:

DELIMITER $$

CREATE PROCEDURE sp_name()
BEGIN
  -- statements
END $$

DELIMITER ;

Expected Output

DELIMITER $$
CREATE PROCEDURE sp_name()
BEGIN
    -- statements
END $$
DELIMITER ;

Actual Output
There is no new line after BEGIN, but most importantly there is no space or even better a new line after END $$ and a space after DELIMITER.

DELIMITER $$
CREATE PROCEDURE sp_name()
BEGIN -- statements
END $$DELIMITER;

Usage

• How are you calling / using the script? (Please provide a code snippet, if applicable)
  prettier-sql -l mariadb -o with_delimiter.sql with_delimiter_formatted.sql
• What SQL language(s) does this apply to?
  MariaDB, MySQL
• What Node version?
  16

It would be very useful and help github repos maintenance if the tool supported .prettierrc config file to pass config options.
It the cli did support this then the vscode extension would support it to

Describe the bug
When formatting code with MAP keys that have . in them, the formatter adds a space between map name and index.

Expected Output

SELECT
    id,
    measures_map['bucket.lower'] AS bucket_lower,
    SUM(1) as cnt
FROM some.table
GROUP BY
    id,
    measures_map['bucket.lower']

Actual Output

SELECT
    id,
    measures_map ['bucket.lower'] AS bucket_lower,
    SUM(1) as cnt
FROM some.table
GROUP BY
   id,
   measures_map ['bucket_lower']

Note: if I cared about measures_map['somekey'] the formatter behaves perfectly as expected. I have not done an exhaustive search, but I see that _ are treated correctly, but . and - are not.

Usage

• How are you calling / using the script? (Please provide a code snippet, if applicable)
  I've tried through using Format Document and Format on Save

• What SQL language(s) does this apply to?
  I'm writing in Presto/Trino and have tried sql, bigquery, spark, and hive and get the same results

• What Node version? (If applicable)
  v17.9.0

Describe the bug
AS is trimmed in CAST function when the "Use AS in alias" option is "Never"

SELECT
  CAST(0 AS bit),
  'foo' AS bar

Expected Output

SELECT
  CAST(0 AS bit),
  'foo' bar

Actual Output

SELECT
  CAST(0 bit),
  'foo' bar

Usage

• How are you calling / using the script? (Please provide a code snippet, if applicable)
  https://inferrinizzard.github.io/prettier-sql/

• What SQL language(s) does this apply to?
  Any

https://github.com/inferrinizzard/prettier-sql-vscode/search?q=linesBetweenStatements

Currently, linesBetweenStatements are invalid.

but https://github.com/inferrinizzard/prettier-sql use linesBetweenQueries.

add clickhouse parser to your project

$ npx prettier-sql
npx: installed 2 in 1.681s
Cannot read property '1' of undefined

Need to print usage + improve CLI tool

Describe the bug
Trying to bring up the web app in Safari results in SyntaxError: Invalid regular expression: invalid group specifier name error in Console. Works fine in Chrome.
To Reproduce

1. do npm i prettier-sql in your project
2. start the project
3. Open the page in Safari
4. View Develop /Web Inspector/Console. Observe:
   SyntaxError: Invalid regular expression: invalid group specifier name

Expected behavior
App compiles and works fine.

Additional context
The regex that seems to be breaking it is /(?<=[^\s]+) (AS )?(?=[^\s]+,?$)/i in Formatter.ts
Per https://caniuse.com/js-regexp-lookbehind, LookBehind is not supported in Safari

Describe the bug
It removes the space between OR and REPLACE. in other words it converts OR REPLACE to ORREPLACE

To Reproduce

Try to format something like this one.
CREATE OR REPLACE FUNCTION "public" . "set_current_timestamp_updated_at"() RETURNS TRIGGER AS $$

Expected behavior
It should preserve the space between the OR and REPLACE Keywords. which should remain two words.

Screenshots

Usage

• Occurred with PostgreSQL, Not sure about other SQL standards
• VSCode version? Version 1.66.2
• What are your currently configured VSCode settings for the prettier-sql extension, if relevant to the issue?

// following setting is under the settings.json file. but no entry with the key "prettier-sql"
"[plsql]": {
"editor.defaultFormatter": "inferrinizzard.prettier-sql-vscode"
},

Additional context
Pl. comment if you need anything specific to hunt this bug.

Describe the Feature
See: sql-formatter-org#124.
Currently we have limited support for tabular formatting in the form of CommaPosition and TabularAlias configs.
Table creation statements would prove a bit more challenging as the exact table create syntaxes vary between languages, it may have to wait for v6 (AST parsing).

Why do you want this feature?
Tabular support would support clear structure in often large masses of SQL text.

Info

• How would you be calling / using the script?
  • All
• What SQL language(s) does this apply to?
  • All
• What Node version? (If applicable)
  • All

Additional context
Add any other context about the feature here.

Reserved Keyword lists can include the full list of reservedKeywords, but some additional support is required:

• dedupe when building complete list
• ensure tokenizer priority always matches higher priority categories first

See: sql-formatter-org#147 and sql-formatter-org#172 in upstream

Describe the bug
The semicolon is in the wrong position when semicolon: newline and keywordPosition: tenSpace____

Expected Output

   SELECT a
     FROM b
          ;

Actual Output

   SELECT a
     FROM b
;

Usage

• How are you calling / using the script? (Please provide a code snippet, if applicable)
  • Demo Page
• What SQL language(s) does this apply to?
  • All
• What Node version? (If applicable)
  • N/a

Additional context
Add any other context about the problem here.

Certain SQL formatters, such as Spark, have duplicate keywords in their reservedWords list (ie. between ODBC and future lists).

• these can be deduped with Sets when constructing the master reservedWord list

Describe the bug
A clear and concise description of what the bug is.

To Reproduce
Steps to reproduce the behavior:

1. Open a .sql script file
2. Press ALT+SHIFT+f
3. See error

Expected behavior
SQL formatted

Error
Unable to format SQL: TypeError: Cannot read properties of undefined (reading 'value')
Source: Prettier SQL VSCode (Extension)

Usage

VS Code version: 1.66.2
Extension version: 0.3.0
Settings:
{
"[sql]": {
"editor.defaultFormatter": "inferrinizzard.prettier-sql-vscode"
}
"editor.defaultFormatter": "esbenp.prettier-vscode",
"editor.detectIndentation": true,
"files.insertFinalNewline": true,
"files.trimFinalNewlines": true,
"files.trimTrailingWhitespace": true,
"files.eol": "\n"
}

hello .
remove 'as' keyword when format sql

clickhouse script is :

create MATERIALIZED VIEW ahe.vw_last_tasks to ahe.vw_device_events_statistics (
  `device_id` String,
  `last_task_time` SimpleAggregateFunction(anyLast, Nullable(DateTime('Asia/Tehran'))),
  `last_task_name` SimpleAggregateFunction(anyLast, Nullable(String)),
  `last_task_rows` SimpleAggregateFunction(anyLast, Nullable(UInt16))
) as
select
  device_id,
  create_time  as last_task_time,
  profile_type as last_task_name,
  rows_count   as last_task_rows
from
  ahe.response_log as pl;

converted to :

create MATERIALIZED VIEW ahe.vw_last_tasks to ahe.vw_device_events_statistics (
  `device_id` String,
  `last_task_time` SimpleAggregateFunction(anyLast, Nullable(DateTime('Asia/Tehran'))),
  `last_task_name` SimpleAggregateFunction(anyLast, Nullable(String)),
  `last_task_rows` SimpleAggregateFunction(anyLast, Nullable(UInt16))
) 
select
  device_id,
  create_time  as last_task_time,
  profile_type as last_task_name,
  rows_count   as last_task_rows
from
  ahe.response_log as pl;

Usage

• How are you calling / using the script?
  • I'm using https://inferrinizzard.github.io/prettier-sql/
• What SQL language(s) does this apply to?
  • The bug will not occur only if I use AWS or DB2

Describe the bug
Bugs occur when there are CASE statements in SUM().

1. When there is only one SUM(CASE ...), like

select sum(case a when foo then bar end) from quaz

Expected Output

SELECT
  SUM(CASE a
  WHEN foo
  THEN bar END
)
FROM
  quaz

Actual Output

SELECT
  SUM(CASE a
  WHEN foo
  THEN barEND
)
FROM
  quaz

Note there should be a space before the END .

2. When there is something else after SUM(CASE ...), like

select sum(case a when foo then bar end), b from quaz

Expected Output

SELECT
  SUM(CASE a
  WHEN foo
  THEN bar END
  )
  , b
FROM
  quaz

Actual Output

An Error Occurred, please report this at:
https://github.com/inferrinizzard/prettier-sql/issues

Stack Trace:
TypeError: Cannot read properties of null (reading '1')
    at https://unpkg.com/prettier-sql@latest/dist/prettier-sql.min.js:1:9589
    at Array.map (<anonymous>)
    at https://unpkg.com/prettier-sql@latest/dist/prettier-sql.min.js:1:9539
    at I.value (https://unpkg.com/prettier-sql@latest/dist/prettier-sql.min.js:1:9715)
    at I.value (https://unpkg.com/prettier-sql@latest/dist/prettier-sql.min.js:1:8987)
    at I.value (https://unpkg.com/prettier-sql@latest/dist/prettier-sql.min.js:1:8800)
    at Module.VT (https://unpkg.com/prettier-sql@latest/dist/prettier-sql.min.js:1:184856)
    at HTMLTextAreaElement.format (https://inferrinizzard.github.io/prettier-sql/index.js:42:31)

Describe the bug
When using the setting:
Prettier-SQL.aliasAS": "never"

It will remove the necessary AS from WITH or CREATE/REPLACE clauses, leading to invalid SQL.

Unformatted Code

create or replace table 'test.example_table' as
with cte as (select 'test' as example)

Expected Output

CREATE OR REPLACE TABLE 'test.example_table' **AS**
WITH
  cte **AS** (
    SELECT
      'test' example
  )

Actual Output

create
OR REPLACE table 'test.example_table'
WITH
  cte (
    SELECT
      'test' example
  )

Usage

• How are you calling / using the script? (Please provide a code snippet, if applicable)
  • VS Code Extension / Format Selection
• What SQL language(s) does this apply to?
  • Confirmed with sql-bigquery
• What Node version? (If applicable)
  • N/A

Additional context
There is a separate but possibly related bug when using Prettier-SQL.aliasAS": "alweys", where an AS is inserted in incorrect places after closing parens ). However, I can't document thoroughly at this time.

Wanted to say thanks for forking and maintaining this project.

It went completely off my radar and I haven't even looked at this for over a year now. (I blame GMail for detecting all sql-formatter-related e-mail coming from github as spam, but that's just an excuse, I really have been lazy). I'm glad there's been somebody who's taken it on to himself to maintain and move this project forward.

Speaking of forward, I think it would be for the better for everybody using this library if these two forks came back together. Looking at NPM downloads and Github starts, sql-formatter still has a much larger user-base, although it's seriously outdated. I'm not sure what's the best path for doing this merger yet. I'm open to transferring the ownership completely to you and renaming it to prettier-sql (which BTW, I think is a much better name).

I haven't really looked much into the changes that you have done. I'll take some time to look through them. I've noticed some overall things like addition of new query languages (bigquery, hive), a config file, switch to TypeScript and VSCode plugin. So far I like what I see.

Hoping to hear your thoughts,
Rene Saarsoo (original maintainer of sql-formatter).

PS. Sorry for hijacking "Issues" for this communication.

hi.

when using formatter in clickhouse script "AS" removed.

create LIVE VIEW ahe.lv_ahe_commands (`create_time` DateTime('Asia/Tehran'), `cnt` UInt64) as
select create_time, count() as cnt
from ahe.ahe_commands
where (create_time > addMinutes(now('Asia/Tehran'), -60)) and (is_expire = 0) and (is_processed = 0)
group by create_time
order by create_time ASC;

Describe the bug

It doesn't work with partial text selection to format.

When using "Format Selection" and prettier-sql is your default formatter for sql I get the following warning/error:
"Visual Studio Code
Configure Default Formatter
Extension 'Prettier SQL VSCode' is configured as formatter but it cannot format 'SQL'-files
[Configure...] [Cancel]"

When using "Format Selection With..." The option to use "prettier-sql" don't show up in VSCode dialog to choose a formatter

To Reproduce
Steps to reproduce the behavior:

1. Set Prettier SQL as default formatter in VSCODE settings JSON:
   "[sql]": {
   "editor.defaultFormatter": "inferrinizzard.prettier-sql-vscode"
   }

2. Open or create an SQL document

3. Select some text on the document

4. Click mouse right button for context menu

5. Select "Format Selection"

6. See Warning/error

7. Choose "Cancel"

8. Repeat step 3 and 4

9. Select "Format Selection With..."

10. check that "prettier-sql" don't show up in VSCODE dialog

Expected behavior
prettier-sql should be able to format selected text on the sql documet

Screenshots
N/A

Usage

• What SQL language(s) does this apply to? any
• What VSCode version are you using? 1.67.2
• What are your currently configured VSCode settings for the prettier-sql extension, if relevant to the issue? Not relevant

Additional context
N/A

Describe the Feature

• Create a prettier plug-in @prettier/plugin-sql
• Optional: remove standalone VSCode plugin - it's simpler to avoid maintenance and focus on functionality within Prettier

Why do you want this feature?

Firstly, thank you for your great work! This formatter has much more usable settings than prettier-plugin-pg with sql-formatter that misses even basic settings like line width.

However, there're some good features of prettier:

• lint/format all files in project
• project based config and ignore files (also respected by VSCode prettier plug-in) that can be committed to repository and shared across team, this was already requested in #61

Rather than implement and maintain that, it's imo simpler to publish a prettier plug-in and have benefits not only of the functionality, but also larger adoption in prettier community

Info

• How would you be calling / using the script?
  • prettier from command line
  • existing lint/format scripts in package.json using prettier
  • VSCode prettier plug-in
• What SQL language(s) does this apply to? any
• What Node version? (If applicable) n/a

See: sql-formatter-org#167, sql-formatter-org#150, sql-formatter-org#135,

List of missing ops:
Postgres: (sql-formatter-org#126)

• ||
• :=
• =>

Unspecified/Multiple:

• =>
• !=

hi.

please support sql format .

MERGE INTO ahe_s.tbl_meters met USING temp.device_cosem co_dict
ON co_dict.cosem_name = met.meter_no
WHEN MATCHED
THEN
UPDATE
SET
  met.meter_no = co_dict.device_id7,
  met.logical_name = co_dict.cosem_name

to
thiq query :

MERGE INTO ahe_s.tbl_meters met USING temp.device_cosem co_dict
ON co_dict.cosem_name = met.meter_no
WHEN MATCHED THEN
UPDATE SET
  met.meter_no = co_dict.device_id7,
  met.logical_name = co_dict.cosem_name

Adjacent parts of a token often get clipped or spaced when they should be included with its dependent token (See: sql-formatter-org#123, sql-formatter-org#173, sql-formatter-org#166)

Describe the bug

'DELETE' is not aligned when using keywordPosition="tenSpaceRight"

Expected Output

   DELETE cc
     FROM Sales.CreditCard AS cc
    WHERE cc.ExpYear < '2003'
      AND cc.ModifiedDate < DATEADD(YEAR, -1, GETUTCDATE());

Actual Output

DELETE cc
     FROM Sales.CreditCard AS cc
    WHERE cc.ExpYear < '2003'
      AND cc.ModifiedDate < DATEADD(YEAR, -1, GETUTCDATE());

Usage

Current system (vs-code)

Version: 1.64.1 (user setup)
Commit: d6ee99e4c045a6716e5c653d7da8e9ae6f5a8b03
Date: 2022-02-07T17:16:22.147Z
Electron: 13.5.2
Chromium: 91.0.4472.164
Node.js: 14.16.0
V8: 9.1.269.39-electron.0
OS: Windows_NT x64 10.0.22000

I'm using [email protected] and assigned it to default formatter for SQL.

// settings.json
{
  "[sql]": {
    "editor.tabSize": 4,
    "editor.defaultFormatter": "inferrinizzard.prettier-sql-vscode",
  },
  "Prettier-SQL.ignoreTabSettings": false,
  "Prettier-SQL.keywordPosition": "tenSpaceRight",
  "Prettier-SQL.breakBeforeBooleanOperator": true,
}

VSCode extension can live in a vscode folder, as a treat.