使用Spotless+Prettier格式化XML内嵌SQL失败，求解决方案

问题场景 #

尝试用Spotless格式化包含SQL语句的Liquibase XML文件，但运行时出现SQL解析器无法识别的错误。

示例XML文件 #

<?xml version="1.0" encoding="UTF-8" ?>
<databaseChangeLog
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.9.xsd">
    <changeSet id="0195ed19-d3c2-7eb4-9322-55ace4932598">
        <sql splitStatements="true">
            <![CDATA[
            CREATE TABLE IF NOT EXISTS students
            (
                id character varying(18) NOT NULL,
                name character varying(32) NOT NULL
            )
            ]]>
        </sql>
        <rollback>
            <sql splitStatements="true">
                <![CDATA[
                DROP TABLE IF EXISTS students
                ]]>
            </sql>
        </rollback>
    </changeSet>
</databaseChangeLog>

使用的Spotless配置 #

format("xml") {
    target("tt.xml")
    toggleOffOn()
    prettier().config(mapOf("parser" to "html", "tabWidth" to 4))
    withinBlocks("SQL Statements", "<![CDATA[", "]]>") {
        prettier(mapOf("prettier" to "3.5.3", "prettier-plugin-sql" to "0.18.0")).config(
            mapOf(
                "parser" to "sql",
                "language" to "sql",
                "tabWidth" to 4,
            ),
        )
    }
}

错误信息 #

There were 1 lint error(s), they must be fixed or suppressed.
  tt.xml:LINE_UNDEFINED toggle(com.diffplug.spotless.npm.SimpleRestClient$SimpleRestResponseException) Unexpected response status code at /prettier/format [HTTP 500] -- (Error while formatting: ConfigError: Couldn't resolve parser "sql".) (...)
  Resolve these lints or suppress with `suppressLintsFor`

解决方案 #

1. 修正SQL解析器配置参数 #

prettier-plugin-sql不支持直接使用sql作为解析器标识，需要替换为对应数据库方言的解析器，比如PostgreSQL用postgresql、MySQL用mysql。修改后配置示例：

withinBlocks("SQL Statements", "<![CDATA[", "]]>") {
    prettier(mapOf("prettier" to "3.5.3", "prettier-plugin-sql" to "0.18.0")).config(
        mapOf(
            "parser" to "postgresql", // 替换为你的SQL对应的方言解析器
            "tabWidth" to 4,
        ),
    )
}

2. 确保版本兼容 #

Prettier 3.x与prettier-plugin-sql 0.18.0存在兼容性问题，可降级Prettier到2.x版本：

prettier(mapOf("prettier" to "2.8.8", "prettier-plugin-sql" to "0.18.0"))

3. 改用Spotless内置SQL格式化工具 #

放弃prettier插件，直接使用Spotless原生支持的SQL格式化工具，比如sqlfmt：

withinBlocks("SQL Statements", "<![CDATA[", "]]>") {
    sqlfmt().config(mapOf("indent_width" to 4))
}

或者pgFormatter：

withinBlocks("SQL Statements", "<![CDATA[", "]]>") {
    pgFormatter().options("--indent 4")
}

4. 清理CDATA块冗余空格 #

添加trim()处理CDATA块内的前置/后置空格，避免解析干扰：

withinBlocks("SQL Statements", "<![CDATA[", "]]>") {
    trim()
    prettier(mapOf("prettier" to "2.8.8", "prettier-plugin-sql" to "0.18.0")).config(
        mapOf("parser" to "postgresql", "tabWidth" to 4)
    )
}

内容的提问来源于stack exchange，提问作者nicholasnet