Feature Request: Inline mode

[ericyangpan]

Describe the Feature
Sometimes I need inline mode. For example, 'select * from tbl' to 'SELECT * FROM tbl;'.

Why do you want this feature?
Inline mode is a frequent scene for simple SQL statement in docs.

[nene]

Seems like a pretty sensible request. Though it probably makes more sense for use in an editor plugin, e.g. to format a selected line.

It would actually be not that hard to implement. We already have some-sort of inline mode inside the formatter to format short parenthesized blocks. We'd need to extend this to also apply for other parts of SQL which currently trigger line breaks.

Perhaps another value for the indentStyle option, like indentStyle: 'inline' or indentStyle: 'none'.

@inferrinizzard what do you think of this?

[inferrinizzard]

Yes, definitely a use case for many SQL devs - this is along the lines of what the keywordNewline config was trying to achieve but now is easier with a parser structure

[ericyangpan]

Yes, definitely a use case for many SQL devs - this is along the lines of what the keywordNewline config was trying to achieve but now is easier with a parser structure

I saw it ware removed in changelog of keywordNewline

[borgogelli]

+1 It could be a very useful feature

[patdx]

This would be a cool feature to have. I have come across two different situations when I wished for this feature recently. I found a neat trick to get quite close:

import { format } from "sql-formatter";

console.log(
  format(
    `insert into my_table ( a, b, c, d ) values ( 1, "this is a very long value that wraps", "this is a very long value that wraps", "this is a very long value that wraps" )`,
    {
      language: "sqlite",
      tabWidth: 0,
      expressionWidth: 100000,
      keywordCase: "upper",
    }
  ).replace(/\n+/g, " ")
);

// Output:
// INSERT INTO my_table (a, b, c, d) VALUES (1, "this is a very long value that wraps", "this is a very long value that wraps", "this is a very long value that wraps")

Setting the expressionWidth to a really big value solves the issue with extra space instead of parenthesis, which looks weird on one line. Though I am not 100% sure if this works in all situations since it is kind of a hack. Having it as an official feature would give extra confidence about the reliability.

[adityatoshniwal]

@nene Any update on this?

[nene]

No real plans to implement this in SQL Formatter.

You might instead consider prettier-plugin-sql-cst, which does this dynamically. That is, if the query is one single line in the original source code and it's short enough to fit within line-length limit, it'll keep it on one line. This doesn't yet work for all the different SQL statements, but for the basic SELECT, INSERT, UPDATE, DELETE it should work.

In SQL Formatter I wouldn't be able to achieve such a dynamic implementation...

• The basic option is to have a setting to format all statements on a single line (regardless of how long). I'm not sure of how useful that would be.
• Another option is to add a line length limit. And then only do the single-line formatting when stuff fits on a single line. The downside of that is that removing a single character form multi-line statement can result in it being formatted on a single line, which could be somewhat annoying. But before I get here I should really implement an actual line-length option (instead of the current clumsy expression-length).
• Additional enhancement would be to only do the single-line formatting when the original source contains a newline. That would possibly be a larger architectural change as currently the formatter doesn't have any way of referring to the original source code.

Also, supporting different formatting styles adds quite a bit of overhead when maintaining this library. Currently I'd say it doesn't even do a good job of supporting a single style.

[adityatoshniwal]

Hi @nene,

The new lib lacks the customisations which this lib provides. So we're not able to move on.

[nene]

@adityatoshniwal If you would say which specific customization options are important to you, then I could say whether these are among the planned features or not.

[adityatoshniwal]

@adityatoshniwal If you would say which specific customization options are important to you, then I could say whether these are among the planned features or not.

Except tab size and use spaces, all is taken care by sql formatter. We're using it in pgAdmin4

[nene]

So..

• Data type case, Function case, Identifier case, Keyword case. These options are hopefully soon to be implemented. In fact I started working on these yesterday. issue 13 Importantly though, these settings have many issues in SQL Formatter, because it's unable to properly distinguish keywords form identifiers. This is fundamentally unfixable in SQL Formatter. The prettier plugin doesn't have this issue.
• Expression width. This is really a pretty crappy feature. It's a poor-mans line-width limit. I'm ashamed of even having it. The prettier plugin supports the standard Prettier printWidth setting, which is much more useful.
• Lines between queries. This is by design not implemented in the prettier plugin. Instead of forcing one to have x number of empty lines always between each SQL statement, the prettier plugin allows user to group together multiple statements with no empty lines between them or separate them with a single line between them. Per the general Prettier philosophy, having more than one empty line between blocks of code serves no purpose. So it won't be implemented, but I'd argue that the way prettier plugin does this is much more flexible and useful.
• Logical operator new line: before/after Unlikely to be implemented as the prettier plugin tries to enforce one consistent style. It only implements the "before" option. On the flip side, the prettier plugin allows for more dynamic formatting - it only adds a newline before AND when the expression doesn't fit on a single line, unlike SQL Formatter which always enforces it. Though I wouldn't rule having this option out completely.
• New line before semicolon. This is another pretty odd feature. I think only very-very few people ever use it. Won't be implemented in the prettier plugin.
• Spaces around operators. The actual name of this setting in SQL Formatter is denseOperators. Some operators are always densely formatted (like :: in PostgreSQL) and some are always padded with spaces, like AND and OR. Additionally this setting also applies to unary + and -, which is quite bad. When you give SQL Formatter SELECT -foo it will format it as SELECT - foo, which is really not what most people would expect. The prettier plugin doesn't have the latter issue. So, this option won't be implemented in the prettier plugin, but I'd say it's better off without a setting that has such a confusing behavior.

I think what's more relevant for pgAdmin is the following:

• The PostgreSQL support in the prettier plugin is not at 100% yet, and it'll take time until it reaches that level. Unlike SQL Formatter, the prettier plugin tries to fully parse the SQL, so it will encounter many situations where it will simply crash. So if you want to adopt it, you'll definitely need to wrap the invocation of it inside a try-catch block. If it works, it will do a much better job at formatting, but if it doesn't it won't do any formatting. In contrast SQL Formatter will take all kinds of syntax thrown to it, but it can do pretty mediocre or even terrible formatting for various kinds of SQL statements.
• There are many issues in SQL Formatter that are fundamentally unfixable. Like distinguishing keywords/identifiers.
• I'm not actively developing SQL Formatter any more. It's in more of a maintenance mode. On the plus side, it'll be more stable with less of a chance of breaking changes in the future of it.

[adityatoshniwal]

Hi @nene,

Thank you for your work. Looking forward for PostgreSQL support. I do agree with you that some of the configs can/should be removed for greater good. Most people care about is casing. I am a JS developer, so if you need any help then I can contribute.

[nene]

Well, the main bottleneck for PostgreSQL support is implementation of the parser. You can track the progress here: nene/sql-parser-cst#40

I expect it to take years before it's 100% complete, this SQL dialect is just so big. A better question might be, when it's complete enough to be useful. For example it supports most of the usual SELECT/INSERT/UPDATE/DELETE syntax plus CREATE/DROP/ALTER TABLE. But if you wonder off the common path, you'll find that e.g. cursors-related syntax is not supported, or that custom operators aren't supported, or that the special syntax of some JSON- and XML-functions isn't supported.

[adityatoshniwal]

Well, the main bottleneck for PostgreSQL support is implementation of the parser. You can track the progress here: nene/sql-parser-cst#40

I expect it to take years before it's 100% complete, this SQL dialect is just so big. A better question might be, when it's complete enough to be useful. For example it supports most of the usual SELECT/INSERT/UPDATE/DELETE syntax plus CREATE/DROP/ALTER TABLE. But if you wonder off the common path, you'll find that e.g. cursors-related syntax is not supported, or that custom operators aren't supported, or that the special syntax of some JSON- and XML-functions isn't supported.

This might help - https://supabase.com/blog/postgres-language-server-implementing-parser#implementing-a-parser-for-postgres

[nene]

Thanks. That's an interesting read, but I'm not really looking for a new approach to implement a parser. I already have a parser which works well. I just need to make it support all of PostgreSQL syntax.

[nene]

Or to put it another way. I spent lots of time looking for a suitable parser, but found none as pretty much all the SQL parsers output just the AST, which excludes lots of information that I need for formatting. Like whether an expression is surrounded by parenthesis or not. So I opted to implement my own.

Switching to a different parser now would mean throwing away almost everything and starting from scratch. Or writing some sort of adapter, which would likely be almost the same amount of work as writing the parser in the first place.

[adityatoshniwal]

Thanks. That's an interesting read, but I'm not really looking for a new approach to implement a parser. I already have a parser which works well. I just need to make it support all of PostgreSQL syntax.

Got it. Are you also planning to include pl/pgsql?

[nene]

Planning, but not before I get the plain PostgreSQL more fully supported.