benjie/prettier-plugin-pg

[WIP] Plugin for prettier to support formatting of PostgreSQL-flavour SQL, including function bodies in SQL, pgSQL, PLV8, plpython, etc.

prettier-plugin-pg's Issues

Failed to build and run the tests

I wanted to try out this plugin, but failed to build it locally on MacOS Monterey.

First off I was missing Python. Then it probably wasn't happy with the version of Python:

gyp ERR! configure error 
gyp ERR! stack Error: Command failed: /usr/bin/python3 -c import sys; print "%s.%s.%s" % sys.version_info[:3];
gyp ERR! stack   File "<string>", line 1
gyp ERR! stack     import sys; print "%s.%s.%s" % sys.version_info[:3];
gyp ERR! stack                       ^
gyp ERR! stack SyntaxError: invalid syntax

Looks like the problem stems from pg-query-native-latest which uses node-gyp 3.8.0.

The docs for node-gyp say that there are problems with running it on MacOS Catalina or later and the advised solution is to upgrade to node-gyp >= 7.

I tried swapping pg-query-native-latest 1.1.1 with pg-query-native 1.3.1 which uses node-gyp 8.2 (also renaming typings/pg-query-native-latest.d.ts to typings/pg-query-native.d.ts and changing references to pg-query-native-latest elsewhere). This did fix the installation. I was also able to compile the code with tsc. But the tests failed to run, with error messages like:

 FAIL   test  tests/BEGIN/jsfmt.spec.js
  ● Test suite failed to run

    Node doesn't have location:

    {
      TransactionStmt: {
        kind: 0,
        options: [
          {
            DefElem: {
              defname: 'transaction_deferrable',
              arg: { A_Const: [Object] },
              defaction: 0
            }
          }
        ]
      }
    }

    Parent node:

    {
      Document: {
        statements: [
          { TransactionStmt: { kind: 0, options: [Array] } },
          { TransactionStmt: { kind: 0, options: [Array] } }
        ],
        doc_location: 0,
        doc_len: 50
      },
      comments: [ { LineComment: true, value: '-- Hello!', start: 0, end: 9 } ],
      start: 0,
      end: 50
    }

Maybe renaming the project to prettier-postgresql?

or prettier-postgre-sql?

(to much the rest of the languages)

Implement more SQL statements

I've added a selection of the statements I see as the most commonly used in a list here. To create the test folder for a statement, run:

npm run add-statement "CREATE FUNCTION"

then create an SQL file such as tests/CREATE_FUNCTION/basic.sql and add some SQL to it.

• SELECT
• INSERT
• UPDATE
• DELETE
• TRUNCATE

---

• CREATE TABLE
• ALTER TABLE
• DROP TABLE

---

• CREATE INDEX
• ALTER INDEX
• DROP INDEX

---

• BEGIN
• SET
• COMMIT
• ROLLBACK

---

• CREATE SCHEMA
• DROP SCHEMA

---

• CREATE EXTENSION
• DROP EXTENSION

---

• GRANT
• REVOKE
• ALTER DEFAULT PRIVILEGES
• CREATE POLICY
• DROP POLICY

---

• CREATE FUNCTION
• ALTER FUNCTION
• DROP FUNCTION
• DO

---

• CREATE TRIGGER
• DROP TRIGGER

---

• CREATE VIEW
• ALTER VIEW
• DROP VIEW
• CREATE MATERIALIZED VIEW
• ALTER MATERIALIZED VIEW
• REFRESH MATERIALIZED VIEW
• DROP MATERIALIZED VIEW

---

• COMMENT ON COLUMN
• COMMENT ON FUNCTION
• COMMENT ON TABLE

---

• CREATE TYPE
• DROP TYPE

---

• CREATE DOMAIN
• ALTER DOMAIN
• DROP DOMAIN

---

• LISTEN
• UNLISTEN
• NOTIFY

---

• VACUUM

Is this project still alive?

If not, are there any alternatives?

Finish converting `print.js` to use the prettier helpers

print.js was originally based on this file to give us a head start:

https://github.com/pyramation/pg-query-parser/blob/8a83b18bfd3ff85d40f10ea1d679e4605a8b1022/src/deparser.js

It's been reformatted to not be class-based, but the code still needs quite a bit of re-writing. When you're confused what the code is doing it's probably because it's broken - look at the relevant part of the old code to see what the intent was.

Everywhere that's currently using deparse needs to go - we've changed the signature of the visitors so they need to be used with print now instead.

Old code was like this:

['A_Indices'](node) {
  // ...
}

New code generally follows this pattern:

['A_Indices'](path, options, print) {
  const node = path.getValue(); // < This is the same node as in the old code
  // ...
}

Here's come common patterns in the ... section above:

Old code:

    if (node.lidx) {
      return format('[%s:%s]', this.deparse(node.lidx), this.deparse(node.uidx));
    }

    return format('[%s]', this.deparse(node.uidx));

New code:

    if (node.lidx) {
      return concat(['[', path.call(print, 'lidx'), ':', path.call(print, 'uidx'), ']']);
    }

    return concat(['[', path.call(print, 'uidx'), ']']);

Old code:

    const fields = node.fields.map(field => {
      return this.deparse(field);
    });

    return fields.join('.');

New code:

    return join('.', path.map(print, 'fields'));

Note the helpers:

• path.call(callback, path1, path2, ...) - will call callback passing a new FastPath object that represents node[path1][path2] - note that the path elements can be strings (for objects) or numbers (for arrays).
• path.map(callback, path1, path2, ...) - as path.call except the entry identified by node[path1][path2] is expected to be an array, so the callback is called for each entry of that array.

---

Since prettier is happy dealing with strings it's wise to start at the bottom (i.e. the "statement" AST nodes) and then work your way up through the tree until all branches and leaves are prettier Doc elements.

Handy reference:

• Prettier plugin doc
• Prettier builder commands
• FastPath src
• Prettier python plugin's printer - a little alliteration there for you 😉
• Prettier chat

SQLite Support?

Hi! Thanks for working on this project! I was wondering if it will work with sqlite sql. I dont know all the syntax, but so far as I know, sqlite is nearly a superset of standard sql

Add support for comments

It seems that the native PostgreSQL query parser simply treats comments as whitespace (and drops them). See: pganalyze/libpg_query#15

To support comments we're either going to have to do something ugly with regexps (ick), write our own AST parser (ugh), or dive into the C-code of libpg_query, maybe starting around here: https://github.com/lfittl/libpg_query/blob/cd9e4b33cef948461c0354a81a23082b16c6a8d1/src/postgres/src_backend_parser_parser.c#L43-L70

This is a major issue. Without comments, we shouldn't really consider progressing.

Humbly Checking for Status Updates

First off, thanks for all the incredible things you do for the open source commuinity @benjie. You're definitely an inspiration on multiple levels. Hopefully this issue doesn't come as a nuisance so please feel free to close if this just isn't on your radar right now.

I went down a crazy rabbit hole today trying to find some kind of tool I could use inside of our repo to apply consistent formatting to Postgres SQL migrations and a bunch of more complex Postgres Functions that we manage. I'm spoiled into assuming that in the Node ecosystem these types of tools are readily accessible but there doesn't seem to be many good options out there. This project definitely seems the most promising both because of it's integration with Prettier and the architectural approach you're taking.

Was just wondering if you plan to revisit and continue work on this or if it's on the backburner because of your other more high profile projects (totally understandable)? Anyways, thanks again and I just wanted to give a huge thumbs up that if this ever got released it would be something that would definitely help out both the org I work for and me personally!

deprecate duplicate node module

deprecate this node module https://www.npmjs.com/package/prettier-plugin-pg-sql

Alternative formatters before this is ready

While we're waiting for prettier-plugin-pg to be ready (see #10) here are some alternatives that you may have success with already today:

1. prettier-plugin-embed and prettier-plugin-sql by @JounQin and @Sec-ant - uses sql-formatter by @nene
2. eslint-plugin-sql and eslint-plugin-unicorn by @gajus, @sindresorhus, @fregante, @mmkal, @fisker and @npdev45, but very opinionated / not so configurable - uses pg-formatter by @gajus
3. @potygen/prettier-plugin-pgsql by @ivank - uses custom Prettier AST formatter

Convert to use Prettier test infra

In the other plugins I have set up we've copied Prettier's run_spec infrastructure as it super nice to add and review tests.

run_spec: https://github.com/prettier/prettier-php/blob/master/tests_config/run_spec.js
Example test directory: https://github.com/prettier/prettier-php/tree/master/tests/declare

Should be easy to set up but better do it sooner than later 😄