How to format SQL using the command line
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
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.
