Tooling for autoformatting sql template literals

[bttger]

I'm currently using prettier to autoformat my js/ts files. This works quite nice when using a query builder. Unfortunately my code gets quite messy when using sql template literals. What are your workflows to format your literals? Is there some tool or prettier plugin available? I was only able to find prettier plugins for plain sql files.

I wish there was some plugin for prettier available which autoformats the literals like it formats objects (see here for an example). Also, there is this library available to autoformat SQL strings. I've just tested it and it works great.

If no-one knows of a good solution, I'd look into prettier plugins and see if I could implement it myself.

[porsager]

I don't use any kind of auto formatter, I do it all by hand, that way it ends up just how I like it ☺️

I do think it would be really cool to be able to lint the sql written though, so that is a bit along the lines of autoformatting and syntax highlighting it.

Curious to see what you come up with,

[bttger]

Just found this sql literals linter. Is this what you mean?

Unfortunately, this linter, if I understand correctly, requires Pearl to be installed and only recognizes formatting issues. But IMO all formatting rules are opinionated anyway, so why would I want to bother with formatting warnings if I could just let it autoformat it for me? Thus, I think a linter should only warn me about things that can't be automatically fixed or that are simply wrong. The autoformatter, on the other hand, should care only about formatting (i.e. not changing the logic). Both should be configurable.

The advantage that I see with autoformatting is that it frees my brain from thinking about formatting, speeds up the development, and prevents discussions with coworkers in the code review. Also, in the past I have (probably) misused formatters for linting purposes in a way where I quickly wrote some ugly stuff in a one-liner, hit save and then see if things could be autoformatted. If not, there must be some error in the syntax (though I still need to find the error on my own 😄).

I was curious about what rules, other than formatting, an sql linter could check for and found SQLFluff. Compared to the other ones that I found, this one looks like the most comprehensive sql linter. The core group's rules are mostly formatting related but the all group has some really convenient rules that definitely help for better code quality.

Considering that the library that I've mentioned in my initial comment only does formatting, it would probably good to use SQLFluff directly. It offers a fix and lint command. All rules that can be autofixed should be excluded from linting. The problems are probably to call the API from Nodejs since it's a Python library and to swap the expressions inside of template literals so that SQLFluff can parse the queries. Also, to integrate it into my workflow, I'd need both an eslint and a prettier plugin. The latter has higher priority for me. Will look into it again when I have more time :)

[porsager]

Just found this. Is this what you mean?

No, by linter I meant something that checks for syntax errors :) We could look into using Postgres.js itself to connect to the database and use .describe() to check the query. It requires a local db with the correct schema to exist, but could be cool.

SQLFluff also looks nice - probably not a bad idea.