SQL Formatting in CI/CD: Checks and Auto-fix

1. Problem: formatting drift needs a repeatable gate

When the same repository uses different formatter settings, formatting diffs return on every change. CI should decide whether a file already matches formatter output, not silently overwrite a developer's checkout. Write and read-only check are separate modes.

2. Minimal reproduction: check and write have different effects

This function injects a formatter rather than assuming a particular CLI or flag. Check reports a difference; write returns new content. A CI script can turn that result into its exit status.

type Format = (sql: string) => string;
type Mode = "check" | "write";
function processSql(source: string, mode: Mode, format: Format) {
  const formatted = format(source);
  const changed = formatted !== source;
  return { changed, output: mode === "write" ? formatted : source };
}
const format = (sql: string) => sql.trim() + "\n";
console.log(processSql("SELECT 1", "check", format)); // changed: true, original output
console.log(processSql("SELECT 1", "write", format)); // formatted output

3. Root cause: CI must fix its file scope and formatter config

Without fixed globs, dialect, line endings, and configuration version, local and CI results drift. Decide whether migrations, generated files, fixtures, and embedded SQL are in scope; files outside the scope should not be rewritten implicitly.

4. Recommendation: local write, CI check, diff means failure

Let developers run write before review. CI should run check, fail when formatter output differs, and print a diff or file list. Pin the formatter, dialect, and config. CI should not commit or overwrite the checkout as an unannounced repair.

5. Complete code: summarize failures and writes by mode

This example processes in-memory files to show CI logic. Repository reads, diff rendering, and exit codes belong to the project script; the function does not depend on one formatter CLI.

type Format = (sql: string) => string;
type Mode = "check" | "write";
type File = { path: string; content: string };
function formatFiles(files: File[], mode: Mode, format: Format) {
  const changed: File[] = [];
  const output = files.map((file) => {
    const next = format(file.content);
    if (next !== file.content) changed.push(file);
    return mode === "write" ? { ...file, content: next } : file;
  });
  return { changed, output, ok: mode === "write" || changed.length === 0 };
}
const format = (sql: string) => sql.trim() + "\n";
console.log(formatFiles([{ path: "query.sql", content: "SELECT 1" }], "check", format).ok); // false

6. Incorrect approaches

Writing files unconditionally in CI makes the workspace differ from the commit. Checking only some SQL creates false green jobs. Replacing a file with empty output after a formatter error hides parser or dialect failures. Mixing formatter checks with unrelated deployment or monitoring templates also obscures the reason for failure.

7. Boundaries: generated files, dialects, and exceptions

Decide whether migrations, snapshots, fixtures, SQL in comments, and multi-dialect directories are in scope. Exceptions need reviewable configuration rather than scattered path checks. An unknown vendor extension should fail with a location or be placed on an explicit retain list.

8. Verification

Test already formatted files, an extra space, line-ending changes, a wrong dialect, and a formatter exception. Assert that check fails on differences or errors, write changes only expected files, failure output includes paths, and CI does not commit generated results.

9. FAQ

Should CI auto-fix and commit? Reporting a diff is often easier to review; auto-commit requires an explicit repository policy.

Should formatting mismatch and formatter failure differ? Yes; the latter may be a parser or dialect problem, while the former is a style diff.

Why do local and CI results differ? Compare formatter version, dialect, config, line endings, and file scope.

10. Summary

A SQL formatter CI/CD gate fixes configuration and scope, uses write locally and read-only check in CI, and fails predictably on differences or formatter errors. Print paths and diffs, never silently rewrite or commit, and record dialect exceptions separately.