Skip to content

format

This command formats SQL and PL/SQL code in the workspace using the formatter options described below. This feature is only available with an Essential or Professional Subscription.

The files are formatted in place. They are expected to be under version control. Therefore, the content of changed files is replaced without creating a backup. The character set, the byte order mark and the line separator of a file are preserved. Files that cannot be formatted, for example due to syntax errors, are left unchanged. The reason is logged in the log file. The command returns exit code 1 if a formatted file cannot be written, otherwise 0.

dblinter [<options>] format [<args>]

See Options. Mandatory options are tenantName, userName, accessToken and configName.

Additionally, all formatter options described in the sections below, starting with Scope, can be used. Values are case-insensitive. For example, --formattingStrategy=normalised_compact and --formattingStrategy=NORMALISED_COMPACT are equivalent. Unset formatter options use their default values. Like any other option, a formatter option can also be set using an environment variable. For example, DBLINTER_TAB_SIZE=2 for tabSize. Store the formatter options in a property file to share them within a team.

--<argument>=<value>

It is not possible to set arguments using environment variables. Beside that, in dbLinter, both arguments and options are treated in the same way. Therefore, they can be used in any order. For example, they can be used before or after the command name. The only exception is <paths...>, which must always be placed at the end. We use options when the semantics are identical for each applicable command. Arguments are used when the semantics change for a command.

  • parallel
    The number of files formatted in parallel. The default is 1. To achieve better performance with higher values, you need enough free system resources. Also consider increasing the maximum heap space using the JAVA_OPTS environment variable. For example, export JAVA_OPTS=-Xmx=32G to set the maximum heap size to 32GB.

  • <paths...>
    The files and directories to format. By default, all files in the workspace are included/excluded according to the glob patterns defined in the configuration. Any files and directories that are passed are treated as an additional filter. In other words, files and directories outside of the workspace cannot be formatted.

Terminal window
dbLinter-Demo % dbLinter format \
--tenantName=Demo \
--userName=philipp.salvisberg+42@gmail.com \
--accessToken=$DBLINTER_DEMO_ACCESS_TOKEN \
--configName=Default \
--parallel=2 \
--keywordCase=upper \
./*.sql examples/Core-G-1*.sql

The console output for the dbLinter-Demo project on GitHub looks like this:

Using connection to jdbc:oracle:thin:@localhost:1521/freepdb1 with user DBL_READ
Processed examples/Core-G-1010.sql (1/25) in 0.075 sec. Formatted.
Processed examples/Core-G-1030.sql (2/25) in 0.201 sec. Formatted.
Processed examples/Core-G-1020.sql (3/25) in 0.283 sec. Formatted.
Processed examples/Core-G-1050.sql (4/25) in 0.028 sec. Formatted.
Processed examples/Core-G-1060.sql (5/25) in 0.011 sec. Formatted.
Processed examples/Core-G-1070.sql (6/25) in 0.003 sec. Formatted.
Processed examples/Core-G-1080.sql (7/25) in 0.033 sec. Formatted.
Processed examples/Core-G-1110.sql (8/25) in 0.022 sec.
Processed examples/Core-G-1120.sql (9/25) in 0.003 sec. Formatted.
Processed examples/Core-G-1130.sql (10/25) in 0.003 sec.
Processed examples/Core-G-1140.sql (11/25) in 0.002 sec. Formatted.
Processed examples/Core-G-1150.sql (12/25) in 0.001 sec. Formatted.
Processed examples/Core-G-1210.sql (13/25) in 0.029 sec.
Processed examples/Core-G-1040.sql (14/25) in 0.152 sec. Formatted.
Processed examples/Core-G-1220.sql (15/25) in 0.048 sec.
Processed examples/Core-G-1240.sql (16/25) in 0.040 sec.
Processed examples/Core-G-1230.sql (17/25) in 0.080 sec.
Processed examples/Core-G-1260.sql (18/25) in 0.008 sec.
Processed examples/Core-G-1250.sql (19/25) in 0.011 sec.
Processed examples/Core-G-1270.sql (20/25) in 0.006 sec.
Processed examples/Core-G-1280.sql (21/25) in 0.005 sec.
Processed examples/Core-G-1310.sql (22/25) in 0.003 sec.
Processed examples/Core-G-1920.sql (23/25) in 0.002 sec.
Processed examples/Core-G-1910.sql (24/25) in 0.010 sec. Formatted.
Processed install.sql (25/25) in 0.165 sec.
Formatted 12 of 25 files.

The examples illustrate each option with the default settings applied to all other options, unless an override is specified. Since formattingStrategy = LIGHTWEIGHT is the default, the result also depends on the input whitespace. SQL fragments are used where a complete statement is unnecessary. The defaults are marked with (default) in the option description.

The formatter is based on the IslandSQL grammar, which supports a subset of statements for OracleDB and PostgreSQL. Unsupported statements are passed by IslandSQL as hidden tokens. These tokens are not formatted, and the original whitespace is preserved.

To preserve the original whitespace of certain code sections, use the following case-insensitive marker comments:

  • @formatter:off or noformat start to disable formatting
  • @formatter:on or noformat end to enable formatting

Here is an example of a formatter off/on code section that is challenging for any formatter to format it in this way:

-- @formatter:off
select decode(dummy, 'X', 1
, 'Y', 2
, 'Z', 3
, 0) /* @formatter:on */
from dual;

The scope of the marker comments remains unchanged. This means that the standard formatting rules apply to these comments.

Indentation of out-of-scope and hidden tokens

Section titled “Indentation of out-of-scope and hidden tokens”

Tokens in conditional compilation directives and in shallow parsed clauses are indented according to the formatter options, but otherwise the original whitespace is preserved.

Leading comments are indented according to the current indentation level. The indentation of consecutive lines within multiline comments is determined by the difference in indentation between the comment token and the subsequent line.

Overall formatting strategy.

This strategy preserves the original whitespace between tokens and then applies only a subset of options. Therefore, the formatting result is determined by the existing whitespace, meaning that the same code can be formatted differently due to the optional nature of line breaks in various places.

This strategy places a space between tokens as a first formatter step and then all other options are applied. The original whitespace between tokens does not affect the result of the formatter, except in excluded regions where whitespace is preserved. This strategy generally produces fewer lines than the NORMALISED_EXPANDED strategy.

This strategy places a newline between tokens as the first formatter step and then all other options are applied. The original whitespace between tokens does not affect the result of the formatter, except in excluded regions where whitespace is preserved. This strategy generally produces more lines than the NORMALISED_COMPACT strategy.

Style of DML statements (SELECT, INSERT, UPDATE, DELETE, MERGE).

select
col1,
col2
from
tab1,
tab2;
select col1,
col2
from tab1,
tab2;
select col1,
col2
from tab1,
tab2;

LEFT_ALIGNED_KEYWORDS_INDENT_CONTINUATIONS

Section titled “LEFT_ALIGNED_KEYWORDS_INDENT_CONTINUATIONS”
select col1,
col2
from tab1,
tab2;
select col1,
col2
from tab1,
tab2;

Parentheses style for parameter declarations.

These examples use formattingStrategy = NORMALISED_EXPANDED. This option is ignored by LIGHTWEIGHT.

procedure p(
in_param1 in integer,
in_param2 in varchar
)
procedure p
(
in_param1 in integer,
in_param2 in varchar
)
procedure p(in_param1 in integer,
in_param2 in varchar)

Parentheses style for IN-lists.

These examples use formattingStrategy = NORMALISED_COMPACT and maxLineSize = 42. This option is ignored by LIGHTWEIGHT.

in (
11111111111, 22222222222, 33333333333,
44444444444, 55555555555, 66666666666,
77777777777, 88888888888, 99999999999
)
in
(
11111111111, 22222222222, 33333333333,
44444444444, 55555555555, 66666666666,
77777777777, 88888888888, 99999999999
)
in (11111111111, 22222222222, 33333333333,
44444444444, 55555555555, 66666666666,
77777777777, 88888888888, 99999999999)

Parentheses style for subqueries.

not exists (
select 42
from t
)
not exists
(
select 42
from t
)
not exists (select 42
from t)

Case transformation policy for SQL keywords.

ValueResult for AuToNoMoUs_TrAnSaCtIoN
UPPERAUTONOMOUS_TRANSACTION
LOWER (default)autonomous_transaction
INITCAPAutonomous_transaction
WORDCAPAutonomous_Transaction
PRESERVEAuToNoMoUs_TrAnSaCtIoN

Case transformation policy for SQL identifiers.

ValueResult for AbC_dEf
UPPERABC_DEF
LOWER (default)abc_def
INITCAPAbc_def
WORDCAPAbc_Def
PRESERVEAbC_dEf

Default: 120, valid: 40 to 1000.
Maximum line size in characters. It should be seen as a target. Where possible and reasonable, the formatter will insert line breaks to ensure that lines do not exceed this target. Generally, the formatter prioritises other options over the maxLineSize. This can result in line breaks being skipped to avoid overruns. This may occur, for example, when adding a trailing comma. Furthermore, it is expected that the limit will be exceeded when it is low, and the identifiers are long.

Short expressions are controlled independently by shortExpressionSize. Its default (60) is half the default maxLineSize (120), but changing maxLineSize does not change shortExpressionSize.

Default: 60, valid: 1 to 1000.
Maximum size of a short expression in characters. Expressions whose size is less than or equal to shortExpressionSize are collapsed. In a subsequent step, the expression is split into multiple lines if it exceeds the maxLineSize. Setting the shortExpressionSize to low or high values can influence the normalisation strategy. In other words, it can make sense to set shortExpressionSize to an extreme value.

Default: 3, valid: 1 to 8.
Number of spaces per indentation level (tab). A statement and certain keywords within a statement increase or decrease the indentation level. Depending on other options such as dmlStyle, parameterDeclarationStyle, inListStyle, and subqueryStyle, the indentation may not be a multiple of the tab size.

The semantics are the same as for LSP FormattingOptions tabSize.

Default: true.
Use spaces rather than tabs for indentation. Technically, the formatter first indents using tabSize spaces per level. If insertSpaces is disabled, it converts leading spaces to tabs as a final step. The result should look the same, provided your editor’s tab size matches the formatter’s indentation size.

The semantics are the same as for LSP FormattingOptions insertSpaces.

Default: true.
Trim trailing whitespace on a line.

The semantics are the same as for LSP FormattingOptions trimTrailingWhitespace.

Default: true.
Remove duplicate blank lines. In other words, blank lines are collapsed to a single blank line.

Default: true.
Trim all newlines at the start of the file before the first content line. However, the indentation of the first content line is preserved.

Default: true.
Trim all newlines after the final newline at the end of the file. This option has no effect if there is no final newline at the end of the file.

The semantics are the same as for LSP FormattingOptions trimFinalNewlines.

Default: true.
Insert a newline character at the end of the file if one does not exist. In combination with trimFinalNewlines, this option ensures that the file ends with a single newline character.

The semantics are the same as for LSP FormattingOptions insertFinalNewline.

Strategy for inserting blank lines between statements. Ignored if formattingStrategy = LIGHTWEIGHT.

The following examples are based on the options formattingStrategy = NORMALISED_COMPACT and maxLineSize = 33.

Never insert blank lines between statements. This option is useful for compact formatting.

select count(*) from t1;
select col2
from t1
where col1 = 42;
delete from t2 where col1 = 42;
commit;

Always insert a blank line between statements. This option is useful for expanded formatting.

select count(*) from t1;
select col2
from t1
where col1 = 42;
delete from t2 where col1 = 42;
commit;

Insert a blank line between each statement, depending on how many lines it contains. This option is useful for controlling the spacing between statements in a more granular way.

select count(*) from t1;
select col2
from t1
where col1 = 42;
delete from t2 where col1 = 42;
commit;

Default: 2, valid: 1 to 1000.
Minimum number of lines in a statement before blank lines are added around it. Applicable only with option blankLinesBetweenStatementMode = BY_STATEMENT_LINES.

Preserve statements that fit on a single line. Ignored when formattingStrategy = NORMALISED_EXPANDED.

select col1, col2 from t where col3 = 42 order by col2;
select col1, col2
from t
where col3 = 42
order by col2;

Preserve case expressions that fit on a single line. The case expression must not exceed the shortExpressionSize option.

The example uses shortExpressionSize = 100 to demonstrate the effect of this option.

select ename,
case when sal > 3000 then 'high' when sal > 2000 then 'medium' else 'low' end as sal_range
from emp;
select ename,
case
when sal > 3000 then
'high'
when sal > 2000 then
'medium'
else
'low'
end as sal_range
from emp;

Place commas at the beginning of continuation lines.

select col1
, col2
, col3
from t1;
select col1,
col2,
col3
from t1;

Place the concatenation operator || at the beginning of continuation lines. Applies only to expressions that exceed the shortExpressionSize option.

The example uses shortExpressionSize = 10 to demonstrate the effect of this option.

select col1
|| col2
|| col3 as result
from t1;
select col1 ||
col2 ||
col3 as result
from t1;

Default: true.
Place logical operators (AND/OR) at the beginning of continuation lines.

select col1
from t1
where col2 = 42
and col3 = 43
or col4 = 44;
select col1
from t1
where col2 = 42 and
col3 = 43 or
col4 = 44;

Place PL/SQL labels on a separate line.

begin
<<a_simple_loop>>
for i in 1..10 loop
null;
end loop a_simple_loop;
end;
begin
<<a_simple_loop>> for i in 1..10 loop
null;
end loop a_simple_loop;
end;

Place the LOOP keyword on a separate line.

begin
for i in 1..10
loop
null;
end loop;
end;
begin
for i in 1..10 loop
null;
end loop;
end;

Place the THEN keyword on a separate line.

begin
if to_char(sysdate, 'DD-MM') = '01-01'
then
sys.dbms_output.put_line('New Year!');
end if;
end;
begin
if to_char(sysdate, 'DD-MM') = '01-01' then
sys.dbms_output.put_line('New Year!');
end if;
end;

Place the ON keyword of join clauses on a separate line.

select e.ename, d.dname
from emp e
join dept d
on e.deptno = d.deptno;
select e.ename, d.dname
from emp e
join dept d on e.deptno = d.deptno;

Insert a space after commas.

select ename, sal from emp;
select ename,sal from emp;

Insert a space after commas in datatype declarations. For these commas, this option takes precedence over spaceAfterComma.

declare
l_value number(10, 2);
begin
null;
end;
declare
l_value number(10,2);
begin
null;
end;

Insert spaces around operators.

select 1 + 2 * 3 as result;
select 1+2*3 as result;

Add spaces after opening and before closing parentheses.

select ( 1 + 2 ) * 3, json { 'key': 'value' }, json [ 1, 2, 3 ];
select (1 + 2) * 3, json {'key': 'value'}, json [1, 2, 3];

Add spaces before opening and after closing parentheses.

select 1 * (2 + 3) * 4;
select 1 *(2 + 3)* 4;

To achieve a more condensed expression, combine this option with spaceAroundOperators = false.

select 1*(2+3)*4;

Default: false.
Insert a space between a function/procedure/method name and its opening parenthesis. At this boundary, this option takes precedence over spaceAroundParentheses.

select round (42.42), json [42], json {'key': 'value'};
select round(42.42), json[42], json{'key': 'value'};

Align type declarations vertically.

declare
l_num number(10,2);
l_string varchar2(100);
l_date date;
co_constant constant integer := 42;
begin
null;
end;
declare
l_num number(10,2);
l_string varchar2(100);
l_date date;
co_constant constant integer := 42;
begin
null;
end;

Align column aliases vertically.

select ename as employee_name,
deptno as department_number,
sal salary
from emp;
select ename as employee_name,
deptno as department_number,
sal salary
from emp;

Align table aliases vertically.

select e.ename, d.dname
from emp e
join dept d on e.deptno = d.deptno;
select e.ename, d.dname
from emp e
join dept d on e.deptno = d.deptno;

Align named arguments => vertically.

begin
my_procedure(
in_a => 42,
in_bcde => 'Hello',
io_fghijklmn => l_var1,
out_opqr => l_var2
);
end;
begin
my_procedure(
in_a => 42,
in_bcde => 'Hello',
io_fghijklmn => l_var1,
out_opqr => l_var2
);
end;

Align assignment operators := vertically.

begin
l_a := 1;
l_bcdefgh := 2;
l_ijk := 3;
end;
begin
l_a := 1;
l_bcdefgh := 2;
l_ijk := 3;
end;

Align equality operators = vertically.

select col3
from t
where col1 = 42
and longer_col2 = 43;
select col3
from t
where col1 = 42
and longer_col2 = 43;

Align logical operators (AND/OR) with SQL keyword columns.

select col4
from t
where col1 = 42
and col2 = 43
or col3 = 44;
select col4
from t
where col1 = 42
and col2 = 43
or col3 = 44;

Aligns existing leading asterisks in multiline comments. Missing asterisks are not added.

Aligns the asterisks in multiline comments but does not add missing asterisks. The indentation of lines without leading asterisks is preserved.

/*
* This is a comment.
* It has multiple lines.
*/

The indentation of all lines is preserved.

Aligns trailing single-line comments within the same SQL statement.

If there are multiple single-line comments in a SQL statement, they are aligned after the space before them has been normalised.

select first_name,
last_name,
email -- contact information
from employees
where phone_number is null -- we need to gather missing phone numbers
/

The indentation is preserved in the lightweight formatting strategy, and a single space before the comment is enforced in normalised formatting strategies.

select first_name,
last_name,
email -- contact information
from employees
where phone_number is null -- we need to gather missing phone numbers
/