rewrite-format-sql

A set of OpenRewrite recipes for formatting SQL/HQL code.

• Recipes
  • FormatSqlBlockRecipe
  • FormatSqlFileRecipe
• Configurable Options
• Examples
  • FormatSqlBlockRecipe Example
  • FormatSqlFileRecipe Example
• Usage
  • Configuring in pom.xml
  • Without adding anything to the project
• For Developers

Recipes

Below is a detailed description of each Recipe.

FormatSqlBlockRecipe

The io.github.mhagnumdw.FormatSqlBlockRecipe recipe automatically formats SQL or HQL embedded in Text Blocks present in the following annotations:

• org.hibernate.annotations.processing.HQL
• org.hibernate.annotations.processing.SQL
• jakarta.data.repository.Query

Future enhancements may allow configuration of custom annotations. Please open an issue.

FormatSqlFileRecipe

The io.github.mhagnumdw.FormatSqlFileRecipe recipe automatically formats the content of SQL files.

Configurable Options

The following options are applicable to both FormatSqlBlockRecipe and FormatSqlFileRecipe:

Type	Name	Description	Example	Default Value
String	filePath	Optional. The path to the files that the Recipe should process. Accepts a glob expression; multiple patterns can be specified, separated by a semicolon ;. If omitted, processes all matching files.	**/*DAO.java **/*.sql	FormatSqlBlockRecipe: **/*.java FormatSqlFileRecipe: **/*.sql
String	sqlDialect	Optional. The SQL dialect to be used for formatting. Valid options: sql (StandardSql), mysql, postgresql, db2, plsql (Oracle PL/SQL), n1ql (Couchbase N1QL), redshift, spark, tsql (SQL Server Transact-SQL). Details here.	plsql	sql
String	indent	Optional. The string to be used for indentation.	" " for 2 spaces "\t" for a tab	4 spaces " "
Integer	maxColumnLength	Optional. The maximum length of a line before the formatter tries to break it.	100	120
Boolean	uppercase	Optional. Whether to convert SQL keywords to uppercase (not safe to use when the SQL dialect has case-sensitive identifiers).	true	false

Examples

FormatSqlBlockRecipe Example

Before

package com.mycompany;

import jakarta.data.repository.Query;

public interface HolidayRepository {
    @Query("""
        select h.*, c.name as country_name from Holiday h inner join Country c on h.country_id = c.id where h.year = :year and h.name != 'Christmas' order by h.name""")
    void findByYear(int year);
}

After

package com.mycompany;

import jakarta.data.repository.Query;

public interface HolidayRepository {
    @Query("""
        select
            h. *,
            c .name as country_name
        from
            Holiday h
            inner join Country c on h.country_id = c .id
        where
            h.year = :year
            and h.name != 'Christmas'
        order by
            h.name""")
    void findByYear(int year);
}

FormatSqlFileRecipe Example

Consider the following example.sql file:

select * from users where id = 1;
--
select d.name, c.name from City c inner join Department d on c.id = d.city_id;

After running the recipe, the example.sql file will be:

select
    *
from
    users
where
    id = 1;
--
select
    d.name,
    c .name
from
    City c
    inner join Department d on c .id = d.city_id;

There is an open issue regarding the space in some aliases: vertical-blank/sql-formatter#77

Usage

The usage modes are described below. It is possible to apply one or both Recipes.

Configuring in pom.xml

If you have a project and will run the recipe regularly, this is the recommended way.

Inside the plugins section, add:

<plugin>
    <groupId>org.openrewrite.maven</groupId>
    <artifactId>rewrite-maven-plugin</artifactId>
    <version>6.11.0</version>
    <configuration>
        <activeRecipes>
            <!-- Add the recipes you want to use here -->
            <recipe>io.github.mhagnumdw.FormatSqlBlockRecipe</recipe>
            <recipe>io.github.mhagnumdw.FormatSqlFileRecipe</recipe>
        </activeRecipes>
        <failOnDryRunResults>false</failOnDryRunResults>
    </configuration>
    <dependencies>
        <dependency>
            <groupId>io.github.mhagnumdw</groupId>
            <artifactId>rewrite-format-sql</artifactId>
            <version>1.0.0</version>
        </dependency>
    </dependencies>
</plugin>

Then run:

./mvnw rewrite:run

To customize the recipe configuration, you need to have a rewrite.yml file in the project root. Example:

---
type: specs.openrewrite.org/v1beta/recipe
name: io.github.mhagnumdw.FormatSqlCustomConfig
recipeList:
  # Add the Recipes you want to use here
  - io.github.mhagnumdw.FormatSqlBlockRecipe:
      sqlDialect: "plsql"
  - io.github.mhagnumdw.FormatSqlFileRecipe:
      sqlDialect: "mysql"

• This file is a way to create your own custom recipe from other recipes.
• The name attribute is arbitrary, but it's good if it relates to its purpose. It's the name of the custom recipe.
• The rewrite.yml file should be versioned.
• For more details about rewrite.yml, see here.

And change the <recipe> tag in pom.xml to:

<recipe>io.github.mhagnumdw.FormatSqlCustomConfig</recipe>

As in this example the FormatSqlCustomConfig recipe includes both FormatSqlBlockRecipe and FormatSqlFileRecipe recipes, in pom.xml it is only necessary to define the FormatSqlCustomConfig recipe.

Then run:

./mvnw rewrite:run

For more details on configuring OpenRewrite with Maven, see here.

Without adding anything to the project

This mode is indicated if your intention is to run the recipe only once.

./mvnw org.openrewrite.maven:rewrite-maven-plugin:run \
  -Drewrite.activeRecipes=io.github.mhagnumdw.FormatSqlBlockRecipe,io.github.mhagnumdw.FormatSqlFileRecipe \
  -Drewrite.recipeArtifactCoordinates=io.github.mhagnumdw:rewrite-format-sql:1.0.0

To customize the recipe configuration, you need to have the rewrite.yml file in the project root, as in the previous example.

Then run:

./mvnw org.openrewrite.maven:rewrite-maven-plugin:run \
  -Drewrite.activeRecipes=io.github.mhagnumdw.FormatSqlCustomConfig \
  -Drewrite.recipeArtifactCoordinates=io.github.mhagnumdw:rewrite-format-sql:1.0.0

• io.github.mhagnumdw.FormatSqlCustomConfig is the name defined in the rewrite.yml file.
• For a single recipe, you don't even need to have the rewrite.yml file to customize the configuration, see here.

For Developers

For details on how to contribute, set up the development environment, run tests, and other development-related information, please see our CONTRIBUTING.md file.