G-0010

🆓

Warning

Always format database code consistently.

General • No Check or SQL-based Test available

Rule Details

Severity Level: Minor
Quality Area: Maintainability
Fixing times: 1 mins

Local-easy
Database Support: OracleDB (v7.3.4 +)
PostgreSQL (v7.0.3 +)

Reason

Consistent formatting makes database code easier to read, understand and review. It reduces the cognitive effort required to navigate unfamiliar code. It also prevents personal formatting preferences from producing unnecessary differences between files.

Using a deterministic formatter also ensures that version control diffs focus on meaningful changes, making reviews simpler and changes easier to track over time.

Rules

Rule	Description
1	Keywords and identifiers are written in lowercase. It used to be good practice to use uppercase keywords and lowercase identifiers to help visualise code structure. But practically all editors support more or less advanced colour highlighting, similar to the examples in these guidelines. Hence, we are now recommending all lowercase, as this is easier and faster for the brain to process. You may choose to prefer the old rule; however, it is important to always be consistent, for example, keywords always in uppercase and identifiers always in lowercase. Use the `FormatterGeneralKeywordCase` and `FormatterGeneralIdentifierCase` parameters to configure the formatter.
2	3-space indentation. Tabs are not used because the indentation depends on the editor configuration. We want to ensure that the code looks the same, independent of the editor used. Hence, no tabs. But why not use 8 spaces? That's the traditional value for a tab. When writing a package function, the code in the body has an indentation of 3. That's 24 characters as a starting point for the code. We think it's too much. Especially if we try to keep a line below 100 or 80 characters. Other good options would be 2 or 4 spaces. We settled for 3 spaces as a compromise. The indentation is still clearly visible, but doesn't use too much space. Use the `FormatterGeneralSpacesPerIndentation` parameter to define the number of spaces used per indentation level.
3	One command per line. Every SQL command, PL/SQL or PL/pgSQL statement must start on a new line.
4	Keywords `loop`, `else`, `elsif`, `end if`, `when` on a new line. Please note that the `loop` keyword after `end` remains on the same line. The `when` keyword in a `continue` or `exit` statement, or in an iterator, also remains on the same line. Exceptions are also made for single-line statements/expressions where enforcing a line break would cause the code to be bloated.
5	Commas in front of separated elements. Use the `FormatterLineBreakOnComma` parameter to configure leading or trailing commas and the `FormatterWhiteSpaceAfterComma` parameter to configure whether a space should be added after a comma. Please note that no space is added after a comma within data types.
6	Call parameters aligned, operators aligned, values aligned. Use the `FormatterAlignmentNamedArguments`, `FormatterAlignmentTypeDeclarations` and `FormatterAlignmentColumnAndTableAliases` parameters to configure alignments.
7	SQL keywords are right-aligned within a SQL command. Use the `FormatterAlignmentRightAlignKeywords` parameter to configure the alignment of keywords in SQL statements.

Example

Non-Compliant Example

create or replace package body employee_api is
procedure set_salary(in_employee_id in integer)is
co_employee_id constant employees.employee_id%type:=in_employee_id;

cursor c_employees(p_employee_id in employees.employee_id%type)is
select last_name
,first_name
,salary
from employees
where employee_id=p_employee_id
order by last_name
,first_name;

r_employee c_employees%rowtype;
l_new_salaryemployees.salary%type;
begin
open c_employees(p_employee_id=>co_employee_id);
fetch c_employees into r_employee;
close c_employees;

new_salary(in_employee_id=>in_employee_id
,out_salary=>l_new_salary);

-- Check whether salary has changed
if r_employee.salary<>l_new_salary then
update employees
set salary=l_new_salary
where employee_id=in_employee_id;
end if;
end set_salary;
end employee_api;

Compliant Solution - ★★★★★

create or replace package body employee_api is
   procedure set_salary(in_employee_id in integer) is
      co_employee_id constant employees.employee_id%type := in_employee_id;

      cursor c_employees(p_employee_id in employees.employee_id%type) is
         select last_name
               ,first_name
               ,salary
           from employees
          where employee_id = p_employee_id
          order by last_name
               ,first_name;

      r_employee     c_employees%rowtype;
      l_new_salary   employees.salary%type;
   begin
      open c_employees(p_employee_id => co_employee_id);
      fetch c_employees into r_employee;
      close c_employees;

      new_salary(in_employee_id => in_employee_id
                ,out_salary     => l_new_salary);

      -- Check whether salary has changed
      if r_employee.salary <> l_new_salary then
         update employees
            set salary = l_new_salary
          where employee_id = in_employee_id;
      end if;
   end set_salary;
end employee_api;

Parameters

Use parameters to customize the rule to your needs.

Parameter	Description	Default Value
FormatterLineBreakAfterSelect	Line break after select, from, where keywords and alike. Valid: true, false	false
FormatterGeneralIdentifierCase	Case of identifers. Valid: upper, lower, initcap, keep.	keep
FormatterLineBreakOnCondition	Line break on conditions (if/case/while). Valid: 1=indented actions, inlined conditions; 2=line breaks only after actions; 3=line breaks after actions and conditions; 4=indented actions and conditions	1
FormatterWhiteSpaceAroundOperators	Whitespace around operators. Valid: true, false.	true
FormatterLineBreakOnConcatenation	Line break on concatenation (\|\|). Valid: before, after.	before
FormatterLineBreakOnBooleanConnector	Line break on boolean and/or. Valid: before, after.	before
DisableAllQuickFix	Comma-separated list of rules for which a quick fix should not be applied to all the problems in a file.	Core G-3130
FormatterGeneralLightweight	Use lightweight formatter. Valid: true, false.	true
FormatterAlignmentColumnAndTableAliases	Align column and table aliases. Valid: true, false.	false
FormatterLineBreakOnJoin	Line break on first join keyword (inner, outer, join, left, right, etc.). Valid: true, false.	true
FormatterAlignmentNamedArguments	Align named arguments (=>). Valid: true, false.	true
FormatterGeneralKeywordCase	Case of keywords. Valid: upper, lower, initcap, keep.	keep
FormatterGeneralSpacesPerIndentation	Number of spaces for an indentation level. Valid: 2, 3, 4, 8.	3
FormatterWhiteSpaceAfterComma	Whitespace after commas. Valid: true, false.	true
FormatterGeneralLongSingleLineComments	Treatment of long single-line comments. Valid: wrap-sl, wrap-ml, keep.	keep
FormatterGeneralMaxLineSize	Maximum number of characters per line. Valid: 40 to 4000.	120
FormatterLineBreakOnComma	Line break on comma. Valid: before, after.	before
FormatterLineBreakOnSubquery	Line break on subquery. Valid: true, false.	true
FormatterWhiteSpaceAroundParenthesis	Whitepace around parenthesis. Valid: default, inside, outside, no.	default
FormatterAlignmentTypeDeclarations	Align type declarations. Valid: true, false.	true
FormatterAlignmentRightAlignKeywords	Right-align keywords. Valid: true, false.	true

References

similar to Trivadis Code Style
Describes formatting rules on a higher level.
similar to Trivadis PL/SQL Formatter Settings
GitHub repository with formatter settings for Trivadis Code Style