Skip to content

Format (pretty-print) SQL

This page is a recipe book for the SQL formatter that ships inside General SQL Parser. If you just want to take messy SQL and pretty-print it, copy the minimum example below and you are done. Read on only when you need to change how the output looks.

Every output on this page is real program output

The AST-formatter recipes were run in their own processes against Java 4.1.6 and .NET 4.1.0.7. The fault-tolerant Java example was run against Java 4.1.9. The results are pasted from the console. The separate-process part matters — see the newInstance() warning.

The minimum that works

Three steps: parse, make an options object, format.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.pp.para.GFmtOpt;
import gudusoft.gsqlparser.pp.para.GFmtOptFactory;
import gudusoft.gsqlparser.pp.stmtformatter.FormatterFactory;

public class FormatDemo {
    public static void main(String[] args) {
        String sql = "select e.last_name as name, e.commission_pct comm, "
                   + "e.salary*12 \"Annual Salary\" from scott.employees as e "
                   + "where e.salary>1000 or 1=1 "
                   + "order by e.first_name,e.last_name;";

        // 1. Parse first — the formatter walks the AST, not the text
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
        parser.sqltext = sql;
        if (parser.parse() != 0) {
            System.out.println(parser.getErrormessage());
            return;
        }

        // 2. Default style options
        GFmtOpt option = GFmtOptFactory.newInstance();

        // 3. Format
        System.out.println(FormatterFactory.pp(parser, option));
    }
}

Output:

1
2
3
4
5
6
7
8
SELECT   e.last_name      AS NAME,
         e.commission_pct comm,
         e.salary * 12    "Annual Salary"
FROM     scott.employees AS e
WHERE    e.salary > 1000
         OR 1 = 1
ORDER BY e.first_name,
         e.last_name;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
using System;
using gudusoft.gsqlparser;
using gudusoft.gsqlparser.pp.para;
using gudusoft.gsqlparser.pp.stmtformatter;

class FormatDemo
{
    static void Main()
    {
        var sql = "select e.last_name as name, e.commission_pct comm, "
                + "e.salary*12 \"Annual Salary\" from scott.employees as e "
                + "where e.salary>1000 or 1=1 "
                + "order by e.first_name,e.last_name;";

        // 1. Parse first — the formatter walks the AST, not the text
        var parser = new TGSqlParser(EDbVendor.dbvoracle);
        parser.sqltext = sql;
        if (parser.parse() != 0)
        {
            Console.WriteLine(parser.Errormessage);
            return;
        }

        // 2. Default style options
        GFmtOpt option = GFmtOptFactory.newInstance();

        // 3. Format
        Console.WriteLine(FormatterFactory.pp(parser, option));
    }
}

Output:

1
2
3
4
5
6
7
8
SELECT   e.last_name      AS NAME,
         e.commission_pct comm,
         e.salary * 12    "Annual Salary"
FROM     SCOTT.employees AS e
WHERE    e.salary > 1000
         OR 1 = 1
ORDER BY e.first_name,
         e.last_name;

Project setup, if you are starting from scratch:

1
2
3
4
5
mkdir formatdemo && cd formatdemo
dotnet new console
dotnet add package gudusoft.gsqlparser
# paste the code above into Program.cs
dotnet run

Note the default style: keywords are upper-cased and right-aligned in their own column, and values line up in a second column. Note too that name came out as NAME — the formatter treats some unquoted words as keywords. If that surprises you, set the casing options explicitly rather than relying on defaults.

The same input formats slightly differently in each edition

Compare the two tabs above: the schema is scott.employees on Java and SCOTT.employees on .NET, from identical input and identical default options. The editions classify some unquoted words differently (identifier vs non-reserved keyword), so default casing is not portable.

If you need byte-identical output across both editions, set caseIdentifier and caseKeywords explicitly instead of accepting the defaults.

The three moving parts of the AST formatter

Type What it is Why you need it
TGSqlParser The parser. Reads SQL text, builds an AST. The formatter walks the AST, so the SQL must parse first.
GFmtOpt Style options. 70 public fields on Java, 69 on .NET. Everything you tune lives here.
FormatterFactory.pp(parser, option) The formatter. Returns the formatted SQL as a string.

This is the traditional, parse-dependent pp(...) path. Java also provides a fault-tolerant pp2(...) path for SQL that cannot produce a complete AST.

Why is the namespace pp?

The formatter began as a pretty printer, so the package/namespace and the method pp(...) both keep the abbreviation. Read it as "pretty-print".

pp(parser, null) throws — it is not a shortcut

Passing null for the options does not return the original SQL untouched. It throws: NullPointerException on Java, NullReferenceException on .NET. Always pass a real GFmtOpt.

Format invalid or unparseable SQL with Java

GSP Java has a second formatter entry point, FormatterFactory.pp2(...). It does not require you to call parse() first and does not require the whole script to be syntactically valid. It divides the input into regions:

  • parseable regions use the guarded AST formatter, preserving normal pp(...) output;
  • malformed regions use the token-preserving lexical recovery formatter;
  • valid and invalid statements can therefore be formatted in the same script.

This feature is currently Java-only. GSP .NET's FormatterFactory.pp(...) still requires a successful parse.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.pp.stmtformatter.FormatterFactory;
import gudusoft.gsqlparser.pp2.Pp2FormatOptions;
import gudusoft.gsqlparser.pp2.Pp2FormatResult;

public class TolerantFormatDemo {
    public static void main(String[] args) {
        String sql = "select 1 from dual; "
                   + "select from where; "       // deliberately invalid
                   + "select 2 from dual;";

        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
        parser.sqltext = sql;

        // No parser.parse() call is required.
        Pp2FormatOptions options = Pp2FormatOptions.defaults();
        Pp2FormatResult result = FormatterFactory.pp2(parser, options);

        System.out.println("Status: " + result.getStatus());
        System.out.println(result.getText());
    }
}

Output:

1
2
3
4
5
6
7
Status: OK_WITH_RECOVERY
SELECT 1
FROM   dual;
SELECT FROM
WHERE;
SELECT 2
FROM   dual;

OK_WITH_RECOVERY means output was produced without losing the malformed region, but at least one region could not use the AST formatter. Formatting does not validate or repair that SQL: SELECT FROM WHERE remains invalid, and its tokens stay in their original order.

Status Meaning Application action
OK Every region used the guarded AST formatter. Treat it like normal pp(...) output.
OK_WITH_RECOVERY Complete output was produced, but one or more regions used fallback formatting. Keep the output and surface getDiagnostics() when correctness matters.
FAILED An internal invariant prevented usable output for at least one region. Ordinary malformed SQL should not reach this status. Do not overwrite the input; log the diagnostics and preserve the original SQL.

result.getRegions() also reports the source offsets, regional status, and the actual renderer (GUARDED_AST, LEXICAL_ISLAND, or CONSERVATIVE) used for each statement region.

Choose the overload for your application

Use the structured result when an editor, CI job, or service needs to distinguish normal formatting from recovered formatting:

1
2
3
4
5
6
7
8
9
Pp2FormatResult result = FormatterFactory.pp2(parser, options);

for (gudusoft.gsqlparser.pp2.FormatDiagnostic diagnostic
        : result.getDiagnostics()) {
    System.err.println(diagnostic.getSeverity()
            + " [" + diagnostic.getStartOffset()
            + ".." + diagnostic.getEndOffset() + "]: "
            + diagnostic.getMessage());
}

If you only need the text, the convenience overload accepts the familiar GFmtOpt and returns a String:

1
2
GFmtOpt style = GFmtOptFactory.newInstance();
String formatted = FormatterFactory.pp2(parser, style);

To combine the structured result with the normal 70 Java style controls, copy them into Pp2FormatOptions:

1
2
3
4
5
6
GFmtOpt style = GFmtOptFactory.newInstance();
style.caseKeywords = TCaseOption.CoUppercase;
style.indentLen = 2;

Pp2FormatOptions options = Pp2FormatOptions.from(style);
Pp2FormatResult result = FormatterFactory.pp2(parser, options);

Pp2FormatOptions.from(...) copies the fields; it does not retain or mutate the caller's GFmtOpt.

Recovery-specific controls

Field Default Effect
tolerantMode true Reserved for a future strict mode. The current engine always recovers ordinary parse failures and does not read this field.
errorRegionStrategy PRESERVE Reserved strategy selector. The current engine does not yet read this field, so PRESERVE, LIGHT_FORMAT, and BEST_EFFORT currently produce the same dispatch behavior.
maxLineWidth 120 Reserved soft-wrap target. The current pp2 renderers do not read this field.
maxErrorRegionSize 10000 Caps the span tagged as an ERROR_REGION by lexical island recognition. The current layout pipeline records the boundary but does not use it to select a different renderer.
maxRegionParseChars 200000 Sends oversized regions directly to lexical recovery instead of attempting a regional parse.
commentPolicy PRESERVE Preserves comment placement. REANCHOR and REFLOW are experimental and are not fully implemented by the region assembler.
breakStatementsOnNewLine true Puts consecutive top-level statements that shared one physical line onto separate lines.
showIndentMarkers false Reserved diagnostic control. The current pp2 renderers do not read this field.
astOverlayEnabled false Enables the experimental AST-overlay annotator; it currently does not change rendered output.

For production controls today, expose maxRegionParseChars and breakStatementsOnNewLine. Keep the reserved and experimental fields at their defaults until their render paths are completed.

pp2(...) reads sqltext, not sqlfilename

For a file, read its contents and assign them to parser.sqltext. Setting only parser.sqlfilename produces an empty pp2 input. The formatsql demo shows file loading, dialect selection, recovery diagnostics, and the /tolerant command-line switch.

A .NET-only trap: newInstance() is a shared singleton

This one will bite you in a real application, and it is the reason every recipe on this page was measured in a separate process.

Java .NET
GFmtOptFactory.newInstance() returns a fresh object yes no — the same instance every time
Mutating the result affects later callers no yes

On .NET, this sequence leaves the second format lower-cased too:

1
2
3
4
5
6
var a = GFmtOptFactory.newInstance();
a.caseKeywords = TCaseOption.CoLowercase;      // intended to be local to this call

var b = GFmtOptFactory.newInstance();          // NOT a fresh object
Console.WriteLine(ReferenceEquals(a, b));      // True
Console.WriteLine(b.caseKeywords);             // CoLowercase — leaked

So on .NET, treat the options object as global mutable state. Either build it once at startup and never change it again, or reset every field you touched before handing control back. A library that formats SQL on behalf of callers should not mutate the shared instance at all.

Reading from a file, writing the result back

For the traditional pp(...) formatter, set sqlfilename instead of sqltext; the parser handles UTF-8 and UTF-16 with or without BOM, so do not pre-load the file yourself. Java's fault-tolerant pp2(...) is the exception: it reads parser.sqltext, as described above.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqlfilename = "/scripts/employees.sql";

if (parser.parse() != 0) {
    System.out.println(parser.getErrormessage());
    return;
}

String formatted = FormatterFactory.pp(parser, GFmtOptFactory.newInstance());
java.nio.file.Files.write(
    java.nio.file.Paths.get("employees-formatted.sql"),
    formatted.getBytes(java.nio.charset.StandardCharsets.UTF_8));

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
var parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqlfilename = @"C:\scripts\employees.sql";

if (parser.parse() != 0)
{
    Console.WriteLine(parser.Errormessage);
    return;
}

string formatted = FormatterFactory.pp(parser, GFmtOptFactory.newInstance());
File.WriteAllText("employees-formatted.sql", formatted);

Write to a new path rather than over the input while you are still developing — a formatter bug that eats a script is much less painful when the original survives.

Picking the dialect

The traditional pp(...) formatter is only as good as the parse, and the parse depends on the vendor. If your script does not parse, that is often the wrong EDbVendor rather than a formatter problem: the error looks like state:NNNN(10102) near: SOMETOKEN(line,col).

The dialect still matters to Java pp2(...): it drives token classification, statement boundaries, and the regional parse attempts. Recovery lets formatting continue after a syntax error; it does not make vendor selection irrelevant.

Java exposes 45 vendor constants and .NET 23 — see Database Compatibility for the full picture, including the three .NET constants that throw. Note that dbvaccess is a distinct constant that routes to the T-SQL grammar, not an alias of dbvmssql.

Customisation recipes

Every knob is a field on GFmtOpt. The style enums live in their own namespace/package — gudusoft.gsqlparser.pp.para.styleenums — which is a fourth import beyond the three in the minimum example. Miss it and the recipes below do not compile.

1
2
3
import gudusoft.gsqlparser.pp.para.styleenums.TCaseOption;
import gudusoft.gsqlparser.pp.para.styleenums.TAlignStyle;
import gudusoft.gsqlparser.pp.para.styleenums.TLinefeedsCommaOption;

1
using gudusoft.gsqlparser.pp.para.styleenums;

Recipe 1 — lower-case keywords

1
2
3
GFmtOpt option = GFmtOptFactory.newInstance();
option.caseKeywords = TCaseOption.CoLowercase;
String formatted = FormatterFactory.pp(parser, option);

For select e.last_name as name, e.commission_pct comm from scott.employees as e where e.salary>1000;:

1
2
3
4
select e.last_name      as name,
       e.commission_pct comm
from   scott.employees as e
where  e.salary > 1000;

1
2
3
GFmtOpt option = GFmtOptFactory.newInstance();
option.caseKeywords = TCaseOption.CoLowercase;
string formatted = FormatterFactory.pp(parser, option);

Same input:

1
2
3
4
select e.last_name      as name,
       e.commission_pct comm
from   SCOTT.employees as e
where  e.salary > 1000;

The four casing values, identical in both editions:

TCaseOption value Effect
CoUppercase SELECT — the default for keywords
CoLowercase select
CoInitCap Select
CoNoChange leave as written

Apply them independently to five different token classes:

Field on GFmtOpt Affects
caseKeywords SELECT, FROM, WHERE, JOIN, …
caseIdentifier unquoted table and column names
caseQuotedIdentifier identifiers in "..." or [...]
caseFuncname function names such as count, sum
caseDatatype VARCHAR, INT, NUMBER, …

Recipe 2 — indentation

The option-setting code below is character-for-character the same in Java and C#:

1
2
3
4
GFmtOpt option = GFmtOptFactory.newInstance();
option.indentLen = 4;
option.useTab    = false;   // set true for real tabs
option.tabSize   = 4;       // visual tab width, only used when useTab = true

indentLen does not do what its name suggests

Setting indentLen = 4 produced output identical to the default in both editions for select a, b from t where a=1;:

1
2
3
4
SELECT a,
       b
FROM   t
WHERE  a = 1;

The default layout aligns continuation lines under a keyword column rather than indenting by a fixed number of spaces, so indentLen has no visible effect on ordinary SELECT statements. It applies to constructs that genuinely nest — BEGIN/END blocks, CASE expressions, sub-selects. Do not reach for it expecting to change SELECT list alignment; use the *Style fields for that.

Recipe 3 — comma at the start of the line

1
2
GFmtOpt option = GFmtOptFactory.newInstance();
option.selectColumnlistComma = TLinefeedsCommaOption.LfBeforeComma;

1
2
3
4
SELECT e.last_name       AS NAME
       ,e.commission_pct comm
       ,e.salary * 12    "Annual Salary"
FROM   scott.employees AS e;

1
2
GFmtOpt option = GFmtOptFactory.newInstance();
option.selectColumnlistComma = TLinefeedsCommaOption.LfBeforeComma;

1
2
3
4
SELECT e.last_name       AS NAME
       ,e.commission_pct comm
       ,e.salary * 12    "Annual Salary"
FROM   SCOTT.employees AS e;

TLinefeedsCommaOption has three values, not two:

Value Effect
LfAfterComma a, at end of line (default)
LfBeforeComma ,a at start of line, no space after the comma
LfbeforeCommaWithSpace , a at start of line, with a space

If you want the classic comma-first style with a space, LfbeforeCommaWithSpace is the one you want — LfBeforeComma packs the comma against the column name.

The same field exists as selectFromclauseComma, parametersComma, and defaultCommaOption (which catches everything else).

Recipe 4 — wrap the column list instead of stacking it

1
2
3
GFmtOpt option = GFmtOptFactory.newInstance();
option.selectColumnlistStyle = TAlignStyle.AsWrapped;   // default is AsStacked
option.selectFromclauseStyle = TAlignStyle.AsWrapped;

1
2
3
SELECT e.last_name AS NAME, e.commission_pct comm, e.salary * 12 "Annual Salary"
FROM   scott.employees AS e
WHERE  e.salary > 1000;

1
2
3
GFmtOpt option = GFmtOptFactory.newInstance();
option.selectColumnlistStyle = TAlignStyle.AsWrapped;   // default is AsStacked
option.selectFromclauseStyle = TAlignStyle.AsWrapped;

1
2
3
SELECT e.last_name AS NAME, e.commission_pct comm, e.salary * 12 "Annual Salary"
FROM   SCOTT.employees AS e
WHERE  e.salary > 1000;

TAlignStyle has exactly two values: AsStacked (one item per line, the default) and AsWrapped (fill the line, then break).

Recipe 5 — line up AND / OR under WHERE

Same code in both languages:

1
2
GFmtOpt option = GFmtOptFactory.newInstance();
option.andOrUnderWhere = true;

Identical in both editions:

1
2
3
4
5
SELECT 1
FROM   t
WHERE  e.salary > 1000
   AND e.commission_pct IS NOT NULL
   AND e.last_name LIKE 'A%';

Recipe 6 — tighter spacing inside expressions

Same code in both languages:

1
2
3
GFmtOpt option = GFmtOptFactory.newInstance();
option.wsPaddingOperatorArithmetic      = false;   // e.salary*12, not e.salary * 12
option.wsPaddingParenthesesInExpression = false;   // (1+2), not ( 1 + 2 )

Identical in both editions:

1
2
3
SELECT e.salary*12,
       (1+2)
FROM   t;

Recipe 7 — multi-statement scripts

Nothing special is required: every statement in sqltext is formatted, in order.

For select id from t1; select name from t2 where id = 1; update t1 set name = 'x' where id = 2;:

1
2
3
4
5
6
7
8
SELECT id
FROM   t1;
SELECT name
FROM   t2
WHERE  id = 1;
UPDATE t1
SET    name = 'x'
WHERE  id = 2;

Same input:

1
2
3
4
5
6
7
8
SELECT ID
FROM   t1;
SELECT NAME
FROM   t2
WHERE  ID = 1;
UPDATE t1
SET    NAME = 'x'
WHERE  ID = 2;

Two things to notice. There is no blank line between statements — if you want one, insert it yourself. And this is the clearest example of the casing divergence: .NET upper-cased id and name while Java left them alone, from the same input.

With the traditional formatter, one syntax error makes parse() return non-zero. Check getSyntaxErrors() / SyntaxErrors to find which statement failed. Java applications that still need best-effort output can run the complete script through pp2(...) and inspect Pp2FormatResult.getDiagnostics().

Recipe 8 — format a folder of .sql files

Construct a new parser per file. The parser holds the AST of the last script it parsed, and reusing one across files is more error-prone than the few microseconds a fresh instance costs.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
import java.nio.charset.StandardCharsets;
import java.nio.file.*;
import java.util.stream.Stream;

try (Stream<Path> paths = Files.walk(Paths.get("/scripts"))) {
    for (Path path : (Iterable<Path>) paths.filter(p -> p.toString().endsWith(".sql"))::iterator) {
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
        parser.sqlfilename = path.toString();
        if (parser.parse() != 0) {
            System.err.println(path + ": " + parser.getErrormessage());
            continue;
        }
        String formatted = FormatterFactory.pp(parser, GFmtOptFactory.newInstance());
        Files.write(path, formatted.getBytes(StandardCharsets.UTF_8));
        System.out.println("formatted " + path);
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
foreach (var path in Directory.EnumerateFiles(@"C:\scripts", "*.sql", SearchOption.AllDirectories))
{
    var parser = new TGSqlParser(EDbVendor.dbvoracle) { sqlfilename = path };
    if (parser.parse() != 0)
    {
        Console.Error.WriteLine($"{path}: {parser.Errormessage}");
        continue;
    }

    string formatted = FormatterFactory.pp(parser, GFmtOptFactory.newInstance());
    File.WriteAllText(path, formatted);
    Console.WriteLine($"formatted {path}");
}

On .NET, hoist the GFmtOpt out of the loop rather than calling newInstance() each time — it returns the same object anyway, so the call inside the loop is misleading.

Comments

Block comments (/* ... */) and line comments (-- ...) are preserved, in both editions. They do affect layout: a leading block comment pushes the whole statement across, because the formatter aligns to the comment.

For /* header */ select a, -- trailing / b from t where a=1;:

1
2
3
4
/* header */ SELECT a, -- trailing
                    b
             FROM   t
             WHERE  a = 1;

If comments vanish, you almost certainly modified the AST between parse() and pp(...), which can disconnect comment tokens from the nodes that owned them. Format first, then modify.

The full option surface

GFmtOpt carries 70 public fields in Java and 69 in .NET. Use the dedicated edition reference for every field, its exact default, allowed enum values, interactions, limitations, and edition-specific behavior:

Those pages are generated from the Javadoc and .NET XML documentation beside the fields in source. That matters for controls built on top of the library: reserved compatibility fields and unsupported output selectors are listed, but clearly distinguished from effective presentation settings.

Browse the API reference for the complete list; the field names are descriptive, so searching for the relevant word usually finds the knob.

Troubleshooting

parse() returned non-zero, so pp(...) did not format the SQL

The AST formatter cannot run if the parse fails. Check the vendor first — TOP 10, CONNECT BY and LIMIT 10,20 are all dialect-specific. Then read the error message and getSyntaxErrors() / SyntaxErrors for line and column. On Java, use FormatterFactory.pp2(...) when preserving and formatting malformed input is the intended behavior.

Output is empty

You called pp(...) before parse(). The AST formatter walks the tree that parse() builds; without it there is nothing to walk. Java pp2(...) does not need a prior parse, but it does require the input in parser.sqltext.

A NullPointerException / NullReferenceException from pp(...)

You passed null as the options argument. That is not a no-op shortcut — build a GFmtOpt with GFmtOptFactory.newInstance().

My style change affected other parts of the application (.NET)

GFmtOptFactory.newInstance() returns a shared singleton on .NET. See the warning above.

Output differs between the Java and .NET builds

Expected for default options — the editions upper-case unquoted identifiers differently. Set caseIdentifier and caseKeywords explicitly for portable output.

See also