Add DuckDB Dialect Support

sql-formatter-org / sql-formatter

A whitespace formatter for different query languages

https://sql-formatter-org.github.io/sql-formatter/

MIT License

2.23k stars 392 forks source link

Add DuckDB Dialect Support #738

Open hughcameron opened 2 months ago

hughcameron commented 2 months ago

This pull request adds support for the DuckDB SQL dialect to the SQL Formatter library.

Description:

Extends the supported dialects to include DuckDB.
Updates the documentation (README.md and potentially others) to reflect the addition of DuckDB support.
Includes any necessary tests to ensure proper formatting for DuckDB queries.

Benefits:

Users working with DuckDB can now leverage the SQL Formatter library for consistent and readable SQL code.
Enhances the overall library coverage by including a popular in-memory database.

Testing:

A test suite is included to verify accurate formatting for various DuckDB queries.

Please review the changes and provide feedback.

codesandbox-ci[bot] commented 2 months ago

This pull request is automatically built and testable in CodeSandbox.

To see build info of the built libraries, click here or the icon next to each commit SHA.

nene commented 2 months ago

Thanks for the PR. A few quick questions and thoughts:

What's the relationship between DuckDB and PostgreSQL? I saw in DuckDB docs that it uses PostgreSQL parser. But does it actually support all of the PostgreSQL syntax? Like, does it support all these operators that you have listed?
You probably have listed too many keywords. Best to only include reserved keywords. Otherwise you'll have common field names like id, name, location, type detected as keywords and converted to uppercase when using keywordCase: "upper".
Your data types list seems to be missing some basic stuff like INT, CHAR, ARRAY.

I'm pretty busy this week... not sure how much time I have to properly review this.

nene commented 2 months ago

For bonus points, you can update the wiki with information about DuckDB. That will also make it easier for me to review this. Otherwise I'll have to go and figure out all of this about DuckDB by myself.

PMassicotte commented 2 months ago

I was about to submit a PR also, I will make some comment in your code.

PMassicotte commented 2 months ago

https://github.com/PMassicotte/sql-formatter/tree/duckdb-dialect

hughcameron commented 2 months ago

Thanks for the comments above. The errors from the test suite are now down to five:

DuckDBFormatter
    ✕ supports ARRAY[] literals (2 ms)
    ✕ dataTypeCase option does NOT affect ARRAY[] literal case
    ✕ keywordCase option affects ARRAY[] literal case
    ✕ dataTypeCase option affects ARRAY type case (2 ms)
    ✕ supports array slice operator

@nene - I'll look into filling out the wiki 👍

nene commented 2 months ago

To fix ARRAY[] tests:

add ARRAY to dataTypes and remove it from keywords and functions lists.

To fix the bar[1:] test:

remove BAR from list of functions

I would guess the builtin BAR() function is rarely used. Well... at least I failed to find documentation of it, because it's super hard to google as Postgres docs contain loads of foo and bar in example code. I think it's better for the formatter to also support the more common use case of bar as a name used in example code.

PS. Make sure to run yarn pretty. (Looks like that currently also changes the .pre-commit-hooks.yaml file... you can let Prettier to reformat that, or leave it as-is. Either way is fine.)

hughcameron commented 2 months ago

The test suite is passing completely now 🎉

I'll collate some notes for the wiki over the next week. Are there any other steps needed before merging?

nene commented 2 months ago

Thanks. I don't think there's anything else.