Formatting comments, new lines are not kept about sql-formatter HOT 9 CLOSED

I'm also noticing one more case that ends up eating my statement terminator semicolon:

Query:

SELECT * FROM user
-- no where clause here
;

Expected:

SELECT
  *
FROM
  user 
-- no where clause here
;

Actual:

SELECT
  *
FROM
  user -- no where clause here;

@mtxr I'm happy to help fix, have you worked on this at all?

from sql-formatter.

Digging into the code on this, I do not think that my issue is related to this.

My issue happens because sql-formatter removes whitespace, including newlines, when adding the semicolon at the end of the line.

For what it's worth, I don't see evidence to support that sql-formatter while preserve multiple line breaks between any token that it parses. Your desired output may require sql-formatter to be more flexible with preserving existing whitespace in some cases.

from sql-formatter.

Formatting of comments is a tricky thing in general.

At the moment sql-formatter tries to place all line-comments at the end of the preceding code. An alternative would be to always put the comment on a line of its own, but of course that would then break those comments that were meant to be on the same line as code.

I think to properly solve it, we'll need to distinguish at parse time between comments between code and on the same line as code, and then format them accordingly in output. Then you could get the following:

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

Another problem (exemplified above) is that sql-formatter doesn't really understand multiple SQL queries. Originally it was built to solve the task of formatting just a single query. We'll need to teach it, that ; ends a query. Then we could separate queries with empty line (not 100% sure if that would be good - so fare nobody has complained about queries not being separated by empty line) and get something like this:

SELECT
    *
FROM
    user;

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

So yeah. I'd consider it definitely a bug. But it requires quite a bit of work.

from sql-formatter.

The simpler approach is to first implement the understanding of multiple queries. Perhaps it can be as simple as just whenever we see ; - it's the end of the query.

That could solve the concrete issue brought up in this ticket. Though issues with comments inside queries would still remain. But I'd suspect comments between queries to be more common use case.

from sql-formatter.

Tested on 2.3.2:

-- This is the first statement
select * from foo where color = "purple";

-- This is the second statement
select * from foo join bar on foo.id = bar.foo_id where bar.count > 100;

-- This is the first statement
select
  *
from
  foo
where
  color = "purple";-- This is the second statement
select
  *
from
  foo
  join bar on foo.id = bar.foo_id
where
  bar.count > 100;

from sql-formatter.

@zeroturnaround any updates on this?

from sql-formatter.

select col1, col2
-- comment 1
from
tbl1
left join tbl2 ON tb1.col1 = tbl2.col1
-- comment 2
left join tbl3 ON tbl1.col2 = tbl3.col3

expected output
select
-- comment 1
col1,
col2
from
tb1
left join tbl2 ON
tb1.col1 = tbl2.col1
-- comment 2
left join tbl3 ON
tbl1.col2 = tbl3.col3

actual output
select
-space- comment 1
col1,
col2
from
tb1
left join tbl2 ON
tb1.col1 = tbl2.col1
-space- comment 2
left join tbl3 ON
tbl1.col2 = tbl3.col3

there's a space btn the dash lines in my output, making the comments to be invalid sql comments

from sql-formatter.

Would love more than one newline between statements in general.

from sql-formatter.

This should be fixed now with the 3.1.0 release.

from sql-formatter.