sql-formatter

sql-formatter copied to clipboard

Input data

select supplier_name,city from
(select * from suppliers join addresses on suppliers.address_id=addresses.id)
as suppliers
where supplier_id>:supplier_id
order by supplier_name asc,city desc;

Expected Output

select
  supplier_name,
  city
from
  (
    select
      *
    from
      suppliers
      join addresses on suppliers.address_id = addresses.id
  ) as suppliers
where
  supplier_id > :supplier_id
order by
  supplier_name asc,
  city desc;

Actual Output

select
  supplier_name,
  city
from
  (
    select
      *
    from
      suppliers
      join addresses on suppliers.address_id = addresses.id
  ) as suppliers
where
  supplier_id >: supplier_id
order by
  supplier_name asc,
  city desc;

Usage

Default query on the example page. For using sql in java with parameterized statements, ":supplier_id" syntax is used to pass named parameter's value After formatting this is changed with a space in between ": supplier_id".

This seems to be an issue similar to #323.

The main problem being that :name syntax is not part of the SQL language you're formatting. Currently SQL Formatter supports this syntax for DB2, PL/SQL and SQLite.

As I understand, you're using a Java API/driver to access some SQL server (don't know which, perhaps it's Postgres, perhaps it's MySQL, perhaps...).

We are currently using it for SQL Server. You are right, this is very specific to Java as these named parameters are converted to PreparedStatements. I presume that =: is not a valid SQL operator. Would it be possible to not apply formatting to it?

I think the best option here is to make this configurable.

The only potential source of conflict in MS SQL Server is the :: operator (also in PostgreSQL). But that really shouldn't be a problem. There's definitely a conflict in N1QL with its use of : in JSON-like data structures. So we can't add this syntax blindly to all dialects.

A slight hurdle is that currently the Tokenizer has been built with an assumption that the types of tokens supported by a language is fixed (e.g. not effected by any configuration options). But I think this shouldn't be too big of a problem. Just a bit of reorganization of the code needed.

Regarding the configuration option itself, there's currently four internal settings to configure this:

type TokenizerOptions = {
  // True to allow for positional "?" parameter placeholders
  positionalParams?: boolean;
  // Prefixes for numbered parameter placeholders to support, e.g. :1, :2, :3
  numberedParamTypes?: ('?' | ':' | '$')[];
  // Prefixes for named parameter placeholders to support, e.g. :name
  namedParamTypes?: (':' | '@' | '$')[];
  // Prefixes for quoted parameter placeholders to support, e.g. :"name"
  // The type of quotes will depend on `identifierTypes` option.
  quotedParamTypes?: (':' | '@' | '$')[];
};

One possibility is to simply expose these four settings directly. Probably better to group them inside one parent config option, like:

type TokenizerOptions = {
  paramTypes: {
    positional?: boolean;
    numbered?: ('?' | ':' | '$')[];
    named?: (':' | '@' | '$')[];
    quoted?: (':' | '@' | '$')[];
};

I'm not sure though whether we should allow one to completely override the parameter settings (e.g. turning ? placeholders off in dialect that supports them by default) or only allow addition of new placeholders. Probably better to allow complete override. Like the Transact-SQL documentation pretty much defines all the possible placeholders based on the driver used:

• @name with SqlClient
• ? with OldeDb or Odbc
• :name with OracleClient

But to make matters worse, @name syntax is also used for variables. Happy times :)

I think there are very limited variations across various programming languages which probably you are covering. These configurations should suffice.

Also, just as a note... this used to work properly till version 3.1.0

This is now released in 9.2.0.