rewrite-format-sql

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

• Recipes
  • FormatSqlTextBlockByAnnotation
  • FormatSqlTextBlockByLanguageInjection
  • FormatSqlFile
• Configurable Options
• Examples
  • FormatSqlTextBlockByAnnotation example
  • FormatSqlTextBlockByLanguageInjection example
  • FormatSqlFile example
• Usage
  • Configuring in pom.xml
  • Without adding anything to the project
• Compatibility
• Changelog
• For Developers

Recipes

Below is a detailed description of each Recipe.

FormatSqlTextBlockByAnnotation

The io.github.mhagnumdw.recipes.FormatSqlTextBlockByAnnotation 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.

FormatSqlTextBlockByLanguageInjection

The io.github.mhagnumdw.recipes.FormatSqlTextBlockByLanguageInjection recipe formats SQL code in Java Text Blocks that are preceded by a // language=sql comment (case-insensitive).

This is the same language injection comment recognized by IntelliJ IDEA for SQL syntax highlighting.

FormatSqlFile

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

Configurable Options

The following options are applicable to FormatSqlTextBlockByAnnotation, FormatSqlTextBlockByLanguageInjection, and FormatSqlFile:

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	FormatSqlTextBlockByAnnotation: **/*.java FormatSqlTextBlockByLanguageInjection: **/*.java FormatSqlFile: **/*.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

FormatSqlTextBlockByAnnotation 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);
}

FormatSqlTextBlockByLanguageInjection example

Before

// language=sql
private static final String QUERY = """
    select * from users u inner join orders o on u.id = o.user_id where u.active = true order by u.name
    """;

After

// language=sql
private static final String QUERY = """
    select
        *
    from
        users u
        inner join orders o on u.id = o.user_id
    where
        u.active = true
    order by
        u.name""";

FormatSqlFile 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.recipes.FormatSqlTextBlockByAnnotation</recipe>
            <recipe>io.github.mhagnumdw.recipes.FormatSqlTextBlockByLanguageInjection</recipe>
            <recipe>io.github.mhagnumdw.recipes.FormatSqlFile</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.recipes.FormatSqlCustomConfig
recipeList:
  # Add the Recipes you want to use here
  - io.github.mhagnumdw.recipes.FormatSqlTextBlockByAnnotation:
      sqlDialect: "plsql"
  - io.github.mhagnumdw.recipes.FormatSqlTextBlockByLanguageInjection:
      sqlDialect: "plsql"
  - io.github.mhagnumdw.recipes.FormatSqlFile:
      sqlDialect: "plsql"

• 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.recipes.FormatSqlCustomConfig</recipe>

As in this example the FormatSqlCustomConfig recipe includes both FormatSqlTextBlockByAnnotation, FormatSqlTextBlockByLanguageInjection and FormatSqlFile 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.recipes.FormatSqlTextBlockByAnnotation,io.github.mhagnumdw.recipes.FormatSqlTextBlockByLanguageInjection,io.github.mhagnumdw.recipes.FormatSqlFile \
  -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.recipes.FormatSqlCustomConfig \
  -Drewrite.recipeArtifactCoordinates=io.github.mhagnumdw:rewrite-format-sql:1.0.0

• io.github.mhagnumdw.recipes.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.

Compatibility

These recipes are compatible with Java projects version 8 or higher.

For developers: The project requires JDK 21 to compile and run tests, but uses Java 8 for the recipes (src/main/java). More information in CONTRIBUTING.md.

Changelog

For a detailed list of changes, please see our CHANGELOG.md file.

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.