Introduction

In this article, I’m going to show you how to format SQL using the command line and transform a single-line SQL statement into a multi-line SQL string that’s way more readable.

Single-line SQL strings

As I explained in this article, logging your SQL queries is very important when using a data access framework that generates statements on your behalf, be it Hibernate or jOOQ.

If you’re using Spring or Spring Boot, then my favorite SQL logging framework is datasource-proxy, which besides logging, allows us to detect N+1 query issues during testing.

Once you add datasource-proxy, SQL statements are going to be logged as follows:

2022-03-30 10:22:18.274 DEBUG 6152 --- [io-8080-exec-10] n.t.d.l.l.SLF4JQueryLoggingListener      : 
Name:dataSource, Connection:6, Time:0, Success:True
Type:Prepared, Batch:False, QuerySize:1, BatchSize:0
Query:["select visits0_.pet_id as pet_id4_6_0_, visits0_.id as id1_6_0_, visits0_.id as id1_6_1_, visits0_.visit_date as visit_da2_6_1_, visits0_.description as descript3_6_1_, visits0_.pet_id as pet_id4_6_1_ from visits visits0_ where visits0_.pet_id=?"]
Params:[(1)]

While you could format the SQL statement at logging time, this is undesirable as it can complicate the log parsing logic that extracts log info and aggregates it (e.g., Elastic Stack). Therefore, we need a way to format SQL statements on-demand only for the queries we are interested in analyzing.

For more details about how to configure datasource-proxy with Spring Boot, check out this article.

SQLFormat Dot Org

If you ever attended my awesome trainings and workshops, then you already know that I’m using SQLFormat to format SQL statements generated by the test cases we are running during the training.

What’s great about this service is that it offers an API we could use to format SQL statements.

Depending on the OS you are using, you can choose one of the provided options to call the SQLFormat service. In my case, since I’m running on Windows, I created the following PowerShell script:

# Payload in hashtable
$body = @{
    sql            = $args[0]
    reindent       = 1
    indent_width   = 3
    # identifier_case   ="upper"
    keyword_case   = "upper"
    strip_comments = 1
}

# Prepare hashtable to be used in the invocation
$params = @{
    Uri         = 'https://sqlformat.org/api/v1/format'
    Method      = 'POST'
    Body        = $body
    ContentType = "application/x-www-form-urlencoded"
}

# Invoke using hashtables
$response = Invoke-RestMethod @params
write-host $response.result

To make it easier to call this Powershell script, I created the following sqlformat.bat Windows batch script in the same folder where the Powershell script is located:

@echo off

Powershell.exe -File %~dp0\sqlformat.ps1 "%*"

All my Windows batch scripts are stored in a folder that’s included in the PATH environment variable, so I can execute them directly from the command line without providing the script path location.

Format an SQL query using the command line

When extracting the SQL statement from the previous log entry, we can simply pass it to the sqlformat command, and we will get the following result:

Awesome, right?

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

The SQLFromat website makes it very easy to parse your SQL statements, and you can easily expose its functionality as a command-line tool.

Insert details about how the information is going to be processed

DOWNLOAD NOW