sql-formatter-org / sql-formatter Goto Github PK

According to stackoverflow the INTO part in INSERT is optional in some implementations, e.g. SQL Server, MySQL.

When using the demo using the input:

SELECT supplier_name, city FROM suppliers
WHERE supplier_id > 500
ORDER BY supplier_name ASC, city DESC;

INSERT suppliers (supplier_name)
VALUES ('test')

I am getting:

SELECT
  supplier_name,
  city
FROM
  suppliers
WHERE
  supplier_id > 500
ORDER BY
  supplier_name ASC,
  city DESC;INSERT suppliers (supplier_name) -- <---- INSERT should be on next line
VALUES
  ('test')

Perhaps it could be simply added in /src/languages/StandardSqlFormatter.js within the const reservedToplevelWords.

Unfortunately I don't have the time/possibility to test this by myself, so I hope the dev team can pick this up, thanks!

Example:
Original:

SELECT test, тест FROM table;

Result:

SELECT
  test,
  т е с т
FROM
  table;

Microsoft T-SQL when containing DECLARE tags, results in a format such as:

DECLARE @param1 VARCHAR(4) = 'asdf';DECLARE @param2 VARCHAR(4) = 'qwer';DECLARE @param3 VARCHAR(4) = 'fdsa';

Which ends up rather messy if you have a lot of declarations.

For me it looks like that this package has a little issue with BigInt values.

For further informations, you can read at this Github issue laravel/telescope#171

I've started looking into using this library to format Esper EPL Queries. EPL mostly uses Standard SQL syntax so the library works great. EPL, though uses different comments; // instead of --.

I'd be interested to know what you think the best way of supporting the EPL queries would be? I've not played around with the code yet, but it looks like I would need to update this line here - https://github.com/zeroturnaround/sql-formatter/blob/master/src/languages/StandardSqlFormatter.js#L101
though I wondered if there was a way to pass in this value when calling the formatter?

I don't know yet what other differences there are between Standard SQL & EPL but would it be possible to add EPL to the languages, initially with the comment changes, and then updating for any other differences?
I'm happy to do the work and open a PR for whatever you think would be the best way to tackle this.

Thanks! 🙏 👍

Most sql formats are have reserved keywords, SELECT, FROM, WHERE, as uppercase. It would be nice to add in a configuration option to convert these words to uppercase.

.. and add a link to README.md.

Some examples:

• https://github.com/reactjs/redux/blob/master/CONTRIBUTING.md
• https://github.com/sasstools/sass-lint/blob/develop/CONTRIBUTING.md

for example:

select * from suppliers
format to
SELECT * FROM suppliers

Is there any solution? thanks a lot!

The scientific notation A * 1e-4 in sql formatted to A * 1e -4, there is a space after e.
Jsql parser throws this exception:

net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "-" "-" at line 8, column 58.

Source Sql:

SELECT A * 1e-4 FROM TABLE1 WHERE 1 = 1

Formatted Sql:

SELECT
  A * 1e -4
FROM
  TABLE1
WHERE
  1 = 1

Use select input for that. Maybe we should add some ZT promo also (logo, website).

Hi there,

Can you add support to make this library bower-installable?

I know the name is already taken by this fork, which doesn't seem to be under active development or maintenance (this is what we used until now, but we want to move to your fork as it solves multiple issues that are still open there) but maybe you can publish it under a different name..

hi!

i get code like this:

and i want format to like this:

but i got this:

can you resolve this problem? thanks , i use sql-format "react-router-dom": "^4.1.1",

It would be good to be able to call this tool from the command line e.g.:

Piping into it some SQL

echo "SELECT * FROM FOO_TABLE;" | sql-formatter

Or providing a SQL file path as an input parameter

sql-formatter --file foo-file.sql

Could sql-formatter convert all SQL keywords to UPPERCASE? This makes it easier to quickly read through large SQL scripts.

Would be great to have alignment of column aliases. For example,

Input:

SELECT sup_name AS supplier_name, cty AS city FROM suppliers
WHERE supplier_id > 500
ORDER BY supplier_name ASC, city DESC;

Current output:

SELECT
  sup_name AS supplier_name,
  cty AS city
FROM
  suppliers
WHERE
  supplier_id > 500
ORDER BY
  supplier_name ASC,
  city DESC;

Expected output:

SELECT
  sup_name AS supplier_name,
  cty      AS city
FROM
  suppliers
WHERE
  supplier_id > 500
ORDER BY
  supplier_name ASC,
  city DESC;

Could sql-formatter support PostgreSQL syntax?

Spark SQL is pretty similar to normal SQL. Unfortunately, there are a couple keywords that don't format. Example:

SELECT *
FROM titus_logs LATERAL VIEW json_tuple(other_properties['fields'], 'MESSAGE', '_TRANSPORT', '_HOSTNAME') 
fields as message,
                             transport,
                             hostname 
                LATERAL VIEW json_tuple(other_properties['environment'], 'ec2Region', 'ec2AZ') 
                environment as region,
                                                   az
WHERE dateint >= 20181006
  AND dateint <= 20181010
  AND message LIKE "Linux version%"
LIMIT 10;

LATERAL VIEW should be on two different lines. Should I create a new SQL dialect, or somehow extend standard sql?

Currently the formatter doesn't handle the FETCH keyword, so a formatted query results into:

SELECT
  supplier_name,
  city
FROM
  suppliers FETCH FIRST 2 ROWS ONLY

I expect it to format to (correct me if wrong):

SELECT
  supplier_name,
  city
FROM
  suppliers 
FETCH FIRST 2 ROWS ONLY

According to https://en.wikipedia.org/wiki/Select_(SQL)#FETCH_FIRST_clause this is standard SQL, but not supported by all dialects. However, PostgreSQL supports it which is commonly used...

I thought this was an easy fix additionally, but turns out it's a bit more tricky 😞 First I tried adding the FETCH keyword to reservedNewlineWords for the sqlFormatter, but it turns it into:

SELECT
  supplier_name,
  city
FROM
  suppliers 
  FETCH FIRST 2 ROWS ONLY // this is indented, I don't think it should be

Then I also tried adding the FETCH keyword to reservedToplevelWords for the sqlFormatter, but it turns it into:

SELECT
  supplier_name,
  city
FROM
  suppliers 
FETCH 
  FIRST 2 ROWS ONLY

http://cassandra.apache.org/doc/latest/cql/

Hi,

Thank you for putting this together, I think it very clever. I'm having a play with the code to try and get it to format the query how I write SQL and I was wondering if you could help. Let's say I wanted to format the query like so.

SELECT COLUMN1
,      COLUMN2
FROM   TABLE1
WHERE  COLUMN1 = 'string'
GROUP  BY COLUMN1
,      COLUMN2

Any pointers on how I could do that? I think its just a case of moving the type "word" up to the top level key words, and the commas left aligned. I say just! I've been having a play but I think it's quite difficult!

Thanks.

https://www.tutorialspoint.com/jpa/jpa_jpql.htm

Before format:

DROP PROCEDURE IF EXISTS `proc_while1`;
DELIMITER ;;
CREATE PROCEDURE proc_while1(IN n int)
BEGIN
DECLARE s int;
DECLARE i int;
SET s = 0;
SET i = 0;
WHILE s <= n DO
insert into test1 value(s,i);
set s = s + 1;
set i = i + 1;
END WHILE;
END
;;
DELIMITER ;
call proc_while1(500);

sql-formatter will remove SPACE DELIMITER ;; around semicolon，this cause execution exceptions.

Take a look at this example using Oracle recursive sub queries. Setting the ordering at the end of them messes up the formatting

How it currently formats

WITH t1(id, parent_id) AS (
  -- Anchor member.
  SELECT
    id,
    parent_id
  FROM
    tab1
  WHERE
    parent_id IS NULL
  UNION ALL
    -- Recursive member.
  SELECT
    t2.id,
    t2.parent_id
  FROM
    tab1 t2,
    t1
  WHERE
    t2.parent_id = t1.id
) SEARCH BREADTH FIRST BY id
SET
  order1,
  another AS (
    SELECT
      *
    FROM
      dual
  )
SELECT
  id,
  parent_id
FROM
  t1
ORDER BY
  order1;

Notice the uneeded extra level of indentation at another AS (

How it should be formatted

WITH t1(id, parent_id) AS (
  -- Anchor member.
  SELECT
    id,
    parent_id
  FROM
    tab1
  WHERE
    parent_id IS NULL
  UNION ALL
    -- Recursive member.
  SELECT
    t2.id,
    t2.parent_id
  FROM
    tab1 t2,
    t1
  WHERE
    t2.parent_id = t1.id
) SEARCH BREADTH FIRST BY id SET order1,
another AS (
  SELECT
    *
  FROM
    dual
)
SELECT
  id,
  parent_id
FROM
  t1
ORDER BY
  order1;

Hi,

Can you publish the index.d.ts for this package so that its easily usable with typescript projects?

Formatting a sql string that's really long (30k) results in the following error:

/*+ USE_HASH (F2 C3) NO_USE_NL(F2 C3) */ ---- F3: Workload with Transpose FACT_NMs and Ratio using Total CAT (C3)

Is there anyway I could make this work?

Thanks

I have a problem while formatting a column called ENDDATE where the formatter will add a space between the 2 words resulting in an invalid sql query.

To recreate problem, try it out in demo at: https://zeroturnaround.github.io/sql-formatter/

Input:
SELECT ENDDATE from table1

Will result in:
SELECT END DATE from table1

When the language parameter is anything else than "n2ql" or "db2" then we should throw an error indicating that given language is unsupported.

https://msdn.microsoft.com/en-us/library/ms181765.aspx
http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm

I suggest formatting it this way:

SELECT
  cust_last_name,
  CASE credit_limit
    WHEN 100 THEN 'Low'
    WHEN 5000 THEN 'High'
    ELSE 'Medium'
  END
FROM customers;

Hi, I'm using your library for formatting SQL queries.
It works perfectly, thank you for your job.
There was discovered a minor issue with Postgresql queries though sasha-alias/sqltabs#100
The two colons in Postgresql are used for casting a value to a certain type.
For example SELECT '1'::int will cast a string to an integer.
sql-formatter makes such queries not valid by inserting a space in between of two colons:

SELECT
  '1': :int

I would like (at least the option: { newLinesBetweenStatements: default 1 }) for extra newlines between statements.

SELECT
    *
FROM
    x;
SELECT
    *
FROM
    y;

could instead become (with { newLinesBetweenStatements: 3 }):

SELECT
    *
FROM
    x;


SELECT
    *
FROM
    y;

Here: mtxr/vscode-sqltools#97

Issue Type

• Bug
• Enhancement
• Feature Request
• Question
• Other

Prerequisites (For bugfixes)

• Are you running the latest version?
• Did you check the logs?
• Did you check the Setup?

Description

I've got a similar bug to this issue (#87) when formatting simple queries with comments :

SELECT * FROM user;

-- RESET ALL PASSWORD REQUESTS

UPDATE user SET password_requested_at = NULL, confirmation_token = NULL;

Steps to Reproduce (For bugfixes)

1. Create a .sql file with file content type set as SQL
2. Put those simple queries into the file
3. Apply format document command

Expected behavior: [What you expected to happen]

The file should be formatted as bellow, the comment should keep its new lines (before and after)

SELECT
    *
FROM
    user;

-- RESET ALL PASSWORD REQUESTS

UPDATE
    user
SET
    password_requested_at = NULL,
    confirmation_token = NULL;

Actual behavior: [What actually happened]

The comment is after the last sql part without any new lines.

SELECT
    *
FROM
    user;-- RESET ALL PASSWORD REQUESTS
UPDATE
    user
SET
    password_requested_at = NULL,
    confirmation_token = NULL;

Versions

• Version: v0.15.0 (vscode 1.26.1)
• OS: fedora 28
• SGDB: PostgresSQL (but it doesn't look like to be related to a specific database vendor)

This whole setup for tests feels way too complicated:

• There's little point of using Karma when our tests don't need to run in Browser environment e.g. to interact with DOM.
• Jasmine too is written with browser-testing primarily in mind.
• This whole issue with only one require "babel-polyfill" is a nuisance.

With Mocha one would not need to write any complex configuration files or integrate with Webpack to support ES6. One could use a single command like:

$ mocha --compilers js:babel-register "test/**/*Test.js"

With Chain there's minor difference in the format of assertions, but that mostly means replacing toEqual() with .to.equal().

PS. One thing I don't know about is integration with code coverage - but I suppose it should not be too difficult either.

Could sql-formatter support MySQL syntax?

select * from table1 表名

DB2 has introduced a lot of new reserved words and now it is uncool to scoll so long to see the actual configuration. It is also hard to see what is different between Standard and DB2 SQL.

Move reserved words lists into separate files and separate each word with a line break.

The point what need to fix

<select id="language">
    <option value="sql">
        SQL
    </option>
    <option value="n1ql">
        N1QL
    </option>
    <option value="db2">
        DB2
    </option>
</select>

Suggest

<select id="language">
    <option value="sql">
        SQL
    </option>
    <option value="n1ql">
        N1QL
    </option>
    <option value="db2">
        DB2
    </option>
    <option value="pl/sql">
        PL/SQL
    </option>
</select>

Thank you for reading.

Given this input

CREATE TABLE items (
  a INT PRIMARY KEY,
  b TEXT
);

I'm getting this output

CREATE TABLE items (a INT PRIMARY KEY, b TEXT);

Hellow, I'm using your library for formatting SQL queries. Language pl/sql for oracle.
It works good, thank you for your job.

Will explain please why spaces are inserted? When I use $ or the russian language symbols in a table name, for example: select prod1 from base$customer or select прод1 from customer.
It is possible to check according to link https://zeroturnaround.github.io/sql-formatter/

This should clearly be configurable.

But the current implementation is a bit lacking. It considers the length of the parenthesized expression in isolation, but it should also consider the current indentation, because the whole point of it is really to limit how long the lines can be. By taking the indentation into the picture, we can name the config option to something like maxLineLength which would be instantly understandable, v/s something like maxParenthesizedExpressionLength.

following

sqlFormatter.format(`SELECT ?`, { params: [ "Sophie's choice" ] })

should yield (note the escaped '):

SELECT
    'Sophie''s choice'

currently it yields:

SELECT
    Sophie's choice

meanwhile, for anyone interested, you can wrap the param yourself before passing:

params.map( param => {

    return typeof param === "string"
        ? "'" + param.replace(/'/g, "''") + "'"
        : param;
});

when I input some sql that contains Chinese vocabulary ,it Separate with space.But in some of the existing web sites that can format SQL,it can be format correctly without space.Please resolve this question.
try this example use your sql-formatter:

select
  dt,
  eventid,
  abid,
  parameters ['btn'],
  count(1) as 次数,
  count (distinct udid) as 人数
from
  matrix_log.dwv_bobo_sdk
where
  dt = '20180122'
  and hour = '05'
limit
  5

Some system end line with \r\n, and the LINE_COMMENT_REGEX is /^((?:#|--).*?(?:\n|$))/.
So the situation like -- some line comment xxx \r\n balabala cant not be formatted right.

Hope to support this situation. Many thanks!

Hi,

I really like this package that I see a lot of potentials to format the SQL of my project.
However, I failed to make it work with my code, which is a component mixed with html and JSX:

import React, { Component } from 'react'
import { Container, Header } from 'semantic-ui-react'
import sqlFormatter from "sql-formatter"

export default class Query extends Component {
  render() {
    return (
        <div>
            SELECT <br />
            FROM {this.props.table} <br />
            WHERE <br />
            GROUP BY <br />
            {/* if the counter = the number of total tables, we are at the last query. 
            No need to append Union all */}
            {this.props.counter !== this.props.mainTablesCount &&
              <div>UNION ALL</div>
            }
        </div>
    );
  }
}

I tried to wrap the entire <div> within sqlFormatter.format(), but not successful. I guess that's due to the JSX codes within it?

Thank you in advance for any help you could provide.
CQ

When the SQL statements too long, it will cost too long time to format, about 10 seconds or more. Can be optimized for this?

when I formatter

select * from xxx

to

select
  *
from
  xxx

I want to send to back-end

select * from xxx

not

select↵  *↵from↵  xxx

how can I do?

SELECT testäätötest FROM test

is formatted into:

SELECT
  test ä ä t ö test
FROM
  test

It seems the characters ä and ö cause a space to be inserted before and after the character, where ever they are being used. There are probably more characters that have the same issue, these are just two particular ones that popped up.

I've noticed that MySQL Triggers do not get formatted correctly. Any idea why this is the case?

SELECT
@totleCount := (
select
count(*)
FROM
xl_exercise
WHERE
read_article_id = #{articleId}
)

formatted to

SELECT
@totleCount: = (
select
count(*)
FROM
xl_exercise
WHERE
read_article_id = #{articleId}
)

shouldn't you use require to import the script ?
when i try to run it, it tells me

Error: Cannot find module 'babel-runtime/helpers/classCallCheck'
    at Function.Module._resolveFilename (module.js:326:15)
    at Function.Module._load (module.js:277:25)
    at Module.require (module.js:354:17)
    at require (internal/module.js:12:17)
    at Object.<anonymous> (/home/lucas/dev/fivegoal_reservationsapi/node_modules/sql-formatter/lib/languages/N1qlFormatter.js:5:24)
    at Module._compile (module.js:410:26)
    at Object.Module._extensions..js (module.js:417:10)
    at Module.load (module.js:344:32)
    at Function.Module._load (module.js:301:12)
    at Module.require (module.js:354:17)
    at require (internal/module.js:12:17)
    at Object.<anonymous> (/home/lucas/dev/fivegoal_reservationsapi/node_modules/sql-formatter/lib/sqlFormatter.js:5:22)
    at Module._compile (module.js:410:26)
    at Object.Module._extensions..js (module.js:417:10)
    at Module.load (module.js:344:32)
    at Function.Module._load (module.js:301:12)

https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html