Don't write a formatted file if the formatting didn't change the file

[wucke13]

Describe the bug
When I format a file which already is in proper format, it gets rewritten and the mtime changes. This is sub-optimal in multiple cases.

Example 1: I format all SQL files in a project automatically. They where already correctly formatted. I keep editing them in my editor, but as I want to save my changes, the editor now complains: "The file has been changed since the last write by us, do you want to overwrite or reload (discarding local changes)?". Its reasonable for the editor to ask this, but its a nuisance in the workflow.

Example 2: I use treefmt to format my projects, its a meta-formatter (it in term runs sql-formatter, taplo, prettier, ...). However, treefmt uses the combination of mtime and file size to quickly detect if a file was changed. For the CI mode of treefmt (just check if all files are properly formatted), the check fails if there is a changed file. As sql-formatter changes the mtime, the CI check always fails. The upstream issue for this one is numtide/treefmt-nix#400 , and there I got asked to open an issue here; I do however agree with them that changing mtime when the file was in fact not modified is a bug.

Expected behavior
mtime of formatted files only changes when they are truly changed, e.g. the diff between before/after formatting is not empty.

Actual behavior
mtime changes whenever sql-formatter is executed, regardless of whether modifications where made.

Usage

• Using treefmt, configured with treefmt-nix, I format my *.sql files
• I use this for sqlite only
• SQL Formatter 15.6.6

[karlhorky]

@wucke13 have you tried using sql-formatter in Prettier via prettier-plugin-sql? Maybe that would be a workaround for you?

It appears that this does not change the modified time:

$ ls -al /Users/k/p/sql/bT7WPJ2q7FuqjP8H4ePK7v.sql
-rw-r--r--  1 k  staff   1.3K Jan 17  2025 /Users/k/p/sql/bT7WPJ2q7FuqjP8H4ePK7v.sql

$ pnpm prettier /Users/k/p/sql/bT7WPJ2q7FuqjP8H4ePK7v.sql --write
sql/bT7WPJ2q7FuqjP8H4ePK7v.sql 57ms (unchanged)

$ ls -al /Users/k/p/sql/bT7WPJ2q7FuqjP8H4ePK7v.sql
-rw-r--r--  1 k  staff   1.3K Jan 17  2025 /Users/k/p/sql/bT7WPJ2q7FuqjP8H4ePK7v.sql

Config:

prettier.config.js

/** @type {import('prettier').Config} */
const prettierConfig = {
  plugins: ['prettier-plugin-sql'],
  singleQuote: true,
};

/** @type {import('prettier-plugin-sql').SqlBaseOptions} */
const prettierPluginSqlConfig = {
  language: 'postgresql',
  keywordCase: 'upper',
  identifierCase: 'lower',
  dataTypeCase: 'lower',
  functionCase: 'lower',
};

const config = {
  ...prettierConfig,
  ...prettierPluginSqlConfig,
};

export default config;

[wucke13]

@mkatychev oh wow, I didn't know that was possible. I do not configure prettier at all, and the invocation of it goes through treefmt-nix, so I'm a bit reluctant to find out how to apply your suggestion to my particular setup.

However this is definitely a good work around for most folks that rely on a configured prettier already, so thank you!

[nene]

Also, for SQLite you might want to try out prettier-plugin-sql-cst. It's a native prettier extension and uses the Prettier formatting engine under the hood, and it understands SQL syntax way better than sql-formatter as it fully parses the SQL. Unlike prettier-plugin-sql, which just wraps this library within a prettier plugin.

[nene]

I'm gonna close this issue as I think you have enough of good workarounds. You're the first one ever to complain about this file-writing behavior and I'd like to keep the number of open issues manageable.