SQL Formatter

[nedtwigg]

I'd ilke a SQL formatter (specifically for formatting Flyway db migration scripts). Hibernate has BasicFormatterImpl and DDLFormatterImpl. They don't do a ton, but they're better than nothing.

The General SQL Parser is really neat, but there's no free version we can distribute. Maaaaybe they'd be interested in publishing a "format-only" version along with our lib? Might be a marketing opportunity for them. I'll email them and invite them to this thread.

Regardless, there are different ways that folks might wanna format sql, but there isn't a language-specific plugin to hang onto like there has been scala, kotlin, gradle, etc.

Here's my proposal:

spotless {
    sql {
        target 'somedir' // have to specify target manually
        hibernateBasicFormatter('optionalHibernateVersion')
        hibernateDdlFormatter('optionalHibernateVersion')
...

If General SQL Parser is interested, we could maybe have something like this:

spotless {
    sql {
        target 'somedir' // have to specify target manually
        generalSqlParser('optionalVersion').dialect(POSTGRES).someOtherOption(SOMEOPT)
...

[nedtwigg]

The hibernate parser can't handle multiple SQL statements, e.g.

ALTER TABLE userinfo ALTER COLUMN id drop DEFAULT;
ALTER TABLE userinfo drop constraint userinfo_pkey cascade;

We could try to detect the ; ourselves and pass the statements to the formatter one-at-a-time, but that is impossible with only regexes.

It looks like the best opensource SQL prettifiers that can handle multiple statements are:

• jdorn/sql-formatter (php)
• darold/pgFormatter (perl)

Writing to disk and spawning a VM for every call to FormatterStep.format() is very expensive, but it would open up a world of non-JVM formatters, as required by #119. Since it turns out this is harder than I thought, I'm gonna put this on the back burner for now. But at some point I'll submit some code to core to make it easier to write formatters that shell out to non-JVM environments. It would be great if we could support this and #119 even if they're slow, and then we can always port things to faster integrations in the future.

[baptistemesta]

There is also a sql formatter implemented in dbeaver:
https://github.com/serge-rider/dbeaver/blob/af7c4eb421a5e98c7da38f5f2b70f90933a5812d/plugins/org.jkiss.dbeaver.model/src/org/jkiss/dbeaver/model/sql/format/tokenized/SQLTokenizedFormatter.java

I don't know if it can be reused.

[nedtwigg]

Thanks! Can't tell if it supports multiple SQL statements or not, and it's not published to maven central. We could maybe copy-paste the code and give credit. They also have support for external formatters, so it appears that they don't love their own implementation.

[baptistemesta]

I'm not sure but I use this formatter in dbeaver itself and it work well for me.
Anyway I would be interested in having this feature also. (the more code spotless keep formatted the happier I am :) )
If you need some hands on this I could try to contribute.

[nedtwigg]

A PR would be great! I added the skeleton of SQL support in the PR above, I would start from there. Be sure to read the contrib guide as well.

If you can copy DBeaver's implementation over without any deps, then it can go in lib. If it needs deps, then it can go in lib-extra.

I would double-check that DBeaver can support multiple SQL statements. If it doesn't support multiple SQL statements in one file, would it still work for you? It wouldn't for me, but if it's enough for you I'm all for it :)

[nedtwigg]

Released in 3.7.0 thanks to @baptistemesta.