Add list of linters / editor plugins implementing the styleguide to the readme

[ckrack]

Hi, I'm creating this issue as a follow-up to a question on twitter. The idea is to be able to follow this issue to be notified, when there are plugins/linters available.

Ideas for implementation:

• Eslint plugin like gajus/eslint-plugin-sql
• php-cs-fixer plugin
• standalone linter like sqllint

[pragunbhutani]

Following

I would also like to know if someone has tried to build an auto-formatter/prettifier based on this style guide?

[treffynnon]

I would prefer to go with a standalone option that could be integrated by others into tool specific wrappers like plugins for eslint, etc. This way it can be used by more people across varied build architectures.

[treffynnon]

This is the closest I have seen so far. It is from the CockroachDB labs by @mjibson - an example of the SQL output compared to a query specified in the style guide: example on sqlfum.pt

It also comes as a standalone go binary as well; that can be configured to format SQL on save in an editor etc.

There was some limited discussion on making it compatible with the sqlstyle.guide on Twitter: https://twitter.com/mjibson/status/1118234708117266433 but this doesn't appear to have been completed.

The code is available over at https://github.com/mjibson/sqlfmt

[emlazzarin]

I will pay a bounty to whoever implements a command line standalone formatter/linter that adheres to this guide.

[treffynnon]

I have not looked closely yet, but perhaps SQLFluff might work too.

[powerman]

It would be nice to have SQLFluff config as close as possible to the guide.

[powerman]

No, SQLFluff is unable to do the most important part of this style, but there is open feature request/discussion for it: https://github.com/sqlfluff/sqlfluff/issues/5379

[powerman]

:tada: I've found really good enough solution. It's a Prettier community plugin prettier-plugin-sql.

Here is my ~/.prettierrc.yml:

plugins:
  - prettier-plugin-sql
overrides:
  - files:
      - '*.sql'
    options:
      # Values: sql bigquery db2 db2i hive mariadb mysql n1ql postgresql plsql redshift
      # singlestoredb snowflake spark sqlite transactsql tsql trino
      language: postgresql
      # Values: preserve upper lower
      keywordCase: upper
      dataTypeCase: upper
      functionCase: upper
      identifierCase: preserve
      # Values: standard tabularLeft tabularRight
      indentStyle: tabularRight
      expressionWidth: 96

and here is result of processing all SQL mentioned in https://www.sqlstyle.guide/ in a single file:

   SELECT file_hash -- stored ssdeep hash
     FROM file_system
    WHERE file_name = '.vimrc';

/* Updating the file record after writing to the file */
   UPDATE file_system
      SET file_modified_date = '1980-02-22 13:19:01.00000',
          file_size = 209732
    WHERE file_name = '.vimrc';

   SELECT first_name
     FROM staff;

   SELECT first_name AS fn
     FROM staff AS s1
     JOIN students AS s2 ON s2.mentor_id = s1.staff_num;

   SELECT SUM(s.monitor_tally) AS monitor_total
     FROM staff AS s;

   SELECT model_num
     FROM phones AS p
    WHERE p.release_date > '2014-09-30';

(
   SELECT f.species_name,
          AVG(f.height) AS average_height,
          AVG(f.diameter) AS average_diameter
     FROM flora AS f
    WHERE f.species_name = 'Banksia'
       OR f.species_name = 'Sheoak'
       OR f.species_name = 'Wattle'
 GROUP BY f.species_name,
          f.observation_date
)
UNION ALL
(
   SELECT b.species_name,
          AVG(b.height) AS average_height,
          AVG(b.diameter) AS average_diameter
     FROM botanic_garden_flora AS b
    WHERE b.species_name = 'Banksia'
       OR b.species_name = 'Sheoak'
       OR b.species_name = 'Wattle'
 GROUP BY b.species_name,
          b.observation_date
);

   SELECT a.title,
          a.release_date,
          a.recording_date
     FROM albums AS a
    WHERE a.title = 'Charcoal Lane'
       OR a.title = 'The New Danger';

   INSERT INTO albums (title, release_date, recording_date)
   VALUES ('Charcoal Lane', '1990-01-01 01:01:01.00000', '1990-01-01 01:01:01.00000'),
          ('The New Danger', '2008-01-01 01:01:01.00000', '1990-01-01 01:01:01.00000');

   UPDATE albums
      SET release_date = '1990-01-01 01:01:01.00000'
    WHERE title = 'The New Danger';

   SELECT a.title,
          a.release_date,
          a.recording_date,
          a.production_date -- grouped dates together
     FROM albums AS a
    WHERE a.title = 'Charcoal Lane'
       OR a.title = 'The New Danger';

   SELECT r.last_name
     FROM riders AS r
    INNER JOIN bikes AS b ON r.bike_vin_num = b.vin_num
      AND b.engine_tally > 2
    INNER JOIN crew AS c ON r.crew_chief_last_name = c.last_name
      AND c.chief = 'Y';

   SELECT r.last_name
     FROM riders AS r
     JOIN bikes AS b ON r.bike_vin_num = b.vin_num;

   SELECT r.last_name,
          (
             SELECT MAX(YEAR (championship_date))
               FROM champions AS c
              WHERE c.last_name = r.last_name
                AND c.confirmed = 'Y'
          ) AS last_championship_year
     FROM riders AS r
    WHERE r.last_name IN (
             SELECT c.last_name
               FROM champions AS c
              WHERE YEAR (championship_date) > '2008'
                AND c.confirmed = 'Y'
          );

   SELECT CASE postcode
                    WHEN 'BN1' THEN 'Brighton'
                    WHEN 'EH1' THEN 'Edinburgh'
          END AS city
     FROM office_locations
    WHERE country = 'United Kingdom'
      AND opening_time BETWEEN 8 AND 9
      AND postcode IN ('EH1', 'BN1', 'NN1', 'KW1');

   CREATE TABLE staff (
          PRIMARY KEY (staff_num),
          staff_num INT NOT NULL,
          first_name VARCHAR(100) NOT NULL,
          pens_in_drawer INT NOT NULL,
          CONSTRAINT pens_in_drawer_range CHECK (pens_in_drawer BETWEEN 1 AND 99)
          );