Formatting your SQL

php artisan sqlens:format          # rewrite
php artisan sqlens:format --check  # answer whether it would, and exit non-zero if so
php artisan sqlens:format --diff   # show what would change, and write nothing

--check is not a dry run. A dry run says what would happen and leaves somebody to read it; a check answers whether anything would and exits non-zero when the answer is yes. That is the difference between a report and a verdict, and a pipeline needs the second.

--diff shows the work; --check passes judgment

The two non-writing modes answer different questions, and the difference is the exit code:

	writes	prints	exit code
(no flag)	yes	which files it rewrote	clean unless something was undetermined
--diff	no	a unified diff per changed file	untouched — it is a view, not a verdict
--check	no	which files would change	non-zero when anything would

So --diff is what you run to decide whether to run the write, and --check is what a pipeline runs to fail a build. They compose: --check --diff prints the diff and returns the verdict.

--diff deliberately does not move the exit code. A command somebody ran to look at something should not fail their build — and a second, undocumented gate is exactly the kind of surprise that gets a tool switched off.

The output is an ordinary unified diff, with a/ and b/ paths, so git apply and patch -p1 take it as-is. Two details it gets right because they are otherwise invisible: a file gaining its final newline is shown (with the usual \ No newline at end of file marker), and so is a CRLF file being normalized to LF — both are changes the write really makes.

What the run could NOT use, and --strict-tools

auto picks the best backend that is actually installed, and falls back to the built-in core when the better one is absent. That fallback is correct — it is why the core exists — but it is never silent: the run names what it passed over and what installing it would buy.

sqlens:format: TOOL.PGFORMATTER.MISSING — `pgformatter` is not installed, so this run used a
different backend. It would have added the best available PostgreSQL formatting, …

That matters because the output is committed. A machine without pgFormatter formats with something else than the machine that has it, and the next run on the other machine rewrites every file. A run that told you nothing left you to discover which of the two you were on.

--strict-tools turns that loss into a failure, for the pipeline that wants the stronger promise:

php artisan sqlens:format --check --strict-tools   # fail unless the preferred backend is present

It exits with the undetermined-in-strict-mode code before formatting anything — a run that is going to fail should not first rewrite two hundred files with the wrong backend. --no-strict-tools forces it off for a run, and without either flag the configured sqlens.strict_tools decides. "Strict" wins if you pass both: there is no reading of "strict and not strict" that is true, and the stricter mistake surfaces a missing tool instead of quietly formatting with less than you asked for.

Doing without one on purpose

A backend you never intend to install is not a loss, and saying so stops the run reporting it as one. false is the third answer beside a path and null:

// config/sqlens.php
'format' => [
    'binaries' => [
        'pgformatter' => false,   // this project does without it
        'sqlfluff' => null,       // look on the search path
    ],
],

A backend switched off this way is never named as something the run would have added, and --strict-tools has nothing to fail over — which is the difference between a decision and a gap. A backend that is merely absent still is a gap, and still fails a strict run.

Naming one you switched off is refused rather than substituted, and the message says which key to look at:

php artisan sqlens:format --backend=pgformatter    # with binaries.pgformatter => false
# this project set `format.binaries.pgformatter` to false, so that backend is switched off …

The built-in core cannot be switched off. It is what makes sqlens:format work with no binaries at all, and a configuration able to disable everything could turn the suite into a run that answers undetermined for every file while looking configured.

Three backends behind one seam

Backend	Dialects	Notes
pgformatter	PostgreSQL	the best there is for Postgres, when pg_format is installed
sqlfluff	PostgreSQL, MySQL	the only external one that covers MySQL
php	both	built in, always present, no install step

auto picks the best AVAILABLE one that can express your style, in that order, and is the default. Availability is checked once per run, not per file — a backend that is not installed is skipped, and the built-in core takes over. A backend that is installed but cannot express your style is skipped as well: with leading_commas: true, the core formats even where pgFormatter is installed, and a missing backend is reported only when it could have served your style.

You name one with --backend=, and its sibling --dialect= decides which SQL grammar the run reasons about:

php artisan sqlens:format --backend=pgformatter     # auto | php | pgformatter | sqlfluff
php artisan sqlens:format --dialect=pgsql           # auto | pgsql | mysql

Both override the configured value for that run, and auto on either is the shipped default — the connection's driver decides the dialect, and availability decides the backend.

Naming one is a promise that it is installed. A named backend that cannot run is a refusal, not a quiet fallback:

sqlens:format: format_tool_missing — the backend you named is not installed on this machine,
and nothing is substituted for a backend you named

That looks unhelpful for exactly one second and saves a day later. A silent substitution produces output you did not ask for, and the next machine — the one where the binary is installed — rewrites every file in the repository.

The same applies to a backend that does not handle your dialect: pgFormatter is written for PostgreSQL's grammar, and pointed at MySQL it mangles backtick-quoted identifiers while appearing to work.

The report format — and yes, --format on sqlens:format

php artisan sqlens:format --check --format=json    # the run as a document, on STDOUT
php artisan sqlens:format --check --format=github  # annotations a pull request renders

The option reads twice, and that is the deliberate choice: the suite is called format and so is the option. --reporter= would read better and would make this the one command in the package whose report format is asked for differently from the other seven. Consistency beats elegance.

console is the default and goes to STDERR, so a --check run can be piped without its verdict landing in whatever is reading the pipe. json and github go to STDOUT, so --format=json > report.json yields a file with nothing in it but the document — while a degradation notice still reaches the person watching.

An unknown value is a misconfiguration, never a quiet fall back to console, and it is refused before a single file is rewritten. A pipeline handed console text where it asked for a machine format fails somewhere else entirely, with nothing pointing back here.

What the JSON document holds

{
  "run": {
    "mode": "check",
    "backend": "php",
    "tool_version": null,
    "dialect": "pgsql",
    "style_fingerprint": "upper-4-trailing",
    "strict_tools": false,
    "sqlens": "0.3.0"
  },
  "files": [
    { "path": "database/sql/report.sql", "outcome": "formatted", "reason": null },
    { "path": "database/sql/view.sql", "outcome": "unchanged", "reason": null }
  ],
  "summary": { "scanned": 2, "changed": 1, "unchanged": 1, "undetermined": 0 }
}

run carries the same parameters the console header names, at fixed keys — they are what answers "why did two machines format this file differently?", and the commonest answer is that one had a binary the other did not.

Every scanned file appears, including the unchanged ones. The console names only what moved, because nobody reads two hundred unchanged lines; a machine filtering them costs nothing, and without them the artifact cannot answer whether the run looked at a given file at all.

outcome is the package's three values — formatted, unchanged, undetermined. Under --check, formatted means would be: that distinction lives in run.mode, once, rather than being spelled into every entry. files is sorted by path, so two runs over the same tree produce the same bytes.

What the GitHub annotations weigh

The weight follows the exit code, not the wording. A pull request shows three visual weights and nothing else, so an ::error beside a green check teaches a reviewer to distrust both.

Under	Annotation	Because
--check, file would change	::error	the build fails
a write run, file was rewritten	::notice	it was fixed; the build passes
--diff, file would change	::notice	a view never moves the exit code
any mode, file undetermined	::error	undetermined fails either way

Unchanged files get no annotation, and the run opens with one ::notice preamble carrying the run parameters. GitHub displays ten annotations per level and drops the rest without saying so, so past that the run emits nine and one line naming how many it withheld and which files they were — a truncated list that says it is truncated. The full list is always in --format=json.

sarif and agent are deliberately absent. They describe findings, and a format run produces none: accepting a format it cannot honestly produce is the same defect as hiding one it can.

Which files a run finds

Three keys, and they are the blast radius rather than a convenience: format is the only suite in this package that writes.

'format' => [
    'paths' => [],                         // empty: the app's registered migration paths
    'exclude' => ['database/schema/*'],    // globs, matched against the walked path
    'extensions' => ['sql'],               // without the dot
],

paths names the roots. Empty means the application's registered migration paths, which is right for most projects and wrong for the one that keeps its SQL in database/sql/, db/ or a legacy dump directory. Repo-relative; an absolute path is refused, because it pins the config to one machine.

The command line still wins: sqlens:format database/sql/report.sql and --path= are unioned and override the configured roots. A configured root that beat an explicit path would be the worse defect — the suite that writes must do what it was just told.

exclude takes globs. A repo-relative glob is anchored for you, so database/schema/* matches the absolute paths the scanner actually walks. vendor/, node_modules/ and storage/ are never walked into and are deliberately not on this list: they are not a preference. A root you name explicitly is still a root — "never descends into" is about where a walk wanders, not about whether a directory name appears in a path.

extensions widens the set to other files that hold nothing but SQL — .ddl, .psql, .pgsql. ⚠️ php and phtml are reserved and cannot be added. A Laravel migration is a PHP file whose SQL, where there is any, lives in a heredoc; running a SQL formatter over one reads the whole file as a statement and rewrites it. That is a destroyed migration, reported as reformatted. The configuration validator refuses the value, and the run drops it again — two guards on two paths, because validation happens when somebody asks for it and a format run happens whenever somebody types the command.

An empty extensions list is refused for the same family of reasons: a run that looked at no files and reported a clean tree is the one answer a formatter must never give. An empty exclude list is the opposite — see below.

Symlinks are not followed

A link out of a configured root is a way for the writing suite to rewrite a file outside its blast radius, and a link back in is a way for the walk never to finish. Both are refused by rejecting the link itself, which needs no cycle detection: a link is not a file this project keeps here, whatever it points at.

The order is byte-wise, on purpose

Paths are sorted with strcmp semantics, never by the ambient collation. The same tree therefore produces the same order under C and under de_DE.UTF-8 — a --check report that reordered itself by environment could not be diffed against yesterday's, which is the whole reason the list is sorted.

Formatting schema:dump output is an opt-in that SAYS SO

database/schema/*.sql is written by schema:dump, from the database, in whatever shape the dumper produces. Reformatting one makes the next dump a large diff against a file nobody edits — visible on every deploy. So it is excluded by default.

A project that hand-maintains its schema file takes the entry out:

'exclude' => [],

and the run then names the dump files it touched:

sqlens:format: formatting 1 generated schema dump file(s) — `database/schema/*` is excluded by
default and this project took the entry out of `sqlens.format.exclude`. The next `schema:dump`
overwrites them: database/schema/pgsql-schema.sql

An opt-in that lives only in a config file is invisible during the review of the diff it produces.

.sql files only — and that is a scope decision, not a gap

⚠️ A Laravel migration is a PHP file. Running a SQL formatter over one does not format the SQL inside its heredoc: it reads PHP as SQL and rewrites the whole file as though it were a statement. That is not a bad diff, it is a destroyed migration — reported as reformatted.

So the scanner takes .sql files and nothing else, and it refuses a .php file even when you name it explicitly with --path. That is not an override you can take: it is the outcome above, asked for by accident.

Formatting SQL inside a heredoc needs a PHP parser, a way to find the heredocs that hold SQL, and a way to write the formatted text back at the original indentation. It is a real feature and it is deliberately not in 1.x.

Generated schema dumps are left alone too. database/schema/*.sql is written by schema:dump from the database, in whatever shape the dumper produces — reformatting one makes the next dump a large diff against a file nobody edits, visible on every deploy.

What the formatter will never do

The built-in core promises four things: consistent keyword casing, consistent indentation, one clause per line, commas where you asked. It is deliberately narrow, and everything it does not do is a decision:

• It never touches a string literal. where note = 'select from where' keeps every byte — upper-casing the inside of a literal changes what a comparison matches, silently, on a statement that still looks correct.
• It never touches a quoted identifier. "select" is a column somebody named badly; the quoting is what makes it exact.
• It never touches a dollar-quoted body. $$ … $$ and $tag$ … $tag$ hold data — most often a whole function body in PL/pgSQL, Python or JavaScript — and a formatter that reflowed one would produce a file that looks formatted and holds destroyed code. A $1 placeholder and an identifier like a$b are correctly not read as tags.
• It never touches a comment, and never lets one end up in front of code. A -- or # comment keeps its line, and whatever followed it starts the next one: joined onto the comment, a second WHERE condition would be commented out and the statement would run with one condition fewer.
• It never splits a token the server reads as one. >=, <>, ||, ::, ->>, @> and MySQL's <=> stay whole, and so do E'…', U&'…', B'…', X'…', N'…', a character set introducer such as _utf8mb4'…', a number with its exponent (1.5e-3) and a MySQL variable (@v, @@session.sql_mode). Where a token ends is read the way PostgreSQL or MySQL reads it, including MySQL's backslash escapes in strings and its rule that -- starts a comment only before whitespace.
• It leaves a word it does not know exactly as written. The keyword list is closed. An unrecognized keyword keeps your casing, which is at worst inconsistent — a list that guessed would upper-case a column called state and produce a diff nobody asked for.

And it is idempotent: formatting formatted output returns the same bytes. Without that, --check and a write run disagree about whether a file is clean, and every commit rewrites every file.

A file it cannot format is REPORTED, never skipped

undetermined: database/migrations/2026_01_01_000000_x.php: format_unparsable — …

An undetermined file moves the exit code in both modes. In --check that is obvious; in a write run it matters more, because the tree now holds a mix of formatted files and one nobody could format — and a clean exit would say otherwise.

What pgFormatter or SQLFluff prints is checked before it is written. Both were measured writing a different statement: pgFormatter 5.11 turns U&'d\0061t' into U & 'd\0061t' and joins the lines of a Python function body, and SQLFluff 4.3.0 on MySQL splits N'abc' into N 'abc' and reads 5--1 as a comment. So the output is read back token by token and compared with your statement. Lines, spaces and the case of bare words may differ, != may become <>, and a comment may move to another line. Anything else and the file is left as it was:

undetermined: database/sql/functions.sql: format_tool_output_rejected — the output no longer reads as the same statement, so it is not used: "U&'d\0061t'" became "U"

A function body in SQL or PL/pgSQL (and a DO block) is compared as code, so pgFormatter may lay it out. A body in any other language has to come back byte for byte.

A backend that cannot express one of your style options reports that too, rather than ignoring it. pgFormatter refuses leading_commas and a non-default line_width. It has flags for both, and neither gives you the style: --comma-start also rewrites every comma inside parentheses as ,, and --wrap-limit indents the wrapped lines with tabs. Either would be rewritten by the next backend that does honor the setting.

The style

// config/sqlens.php
'format' => [
    'backend' => 'auto',
    'dialect' => 'auto',
    'style' => [
        'indent' => 4,
        'uppercase_keywords' => true,
        'leading_commas' => false,
        'line_width' => 100,
    ],
],

Four options, and the fewness is the point: every option is a decision two people will disagree about forever, and a formatter's value comes from ending that argument rather than parameterizing it. leading_commas is the one purely aesthetic entry, and it is there because it is the one people actually argue about — leading commas make a git diff of an added column one line instead of two.

line_width needs a backend that wraps to a column

The built-in core breaks lines on structure, not on a column — one per selected expression, one per clause, one per parenthesis level. That is deliberate: a break at column 80 lands wherever the character count happens to fall, which for SQL is usually the middle of an expression.

So the core cannot honor line_width, and it says so rather than ignoring it. Set it to anything other than the shipped default and a core run answers:

format_style_not_expressible — this backend cannot express: line_width

Use the sqlfluff backend if you need a column bound; pgFormatter refuses a width too, for the reason above. Leaving line_width at its default is the ordinary case and changes nothing.

⚠️ It did not always say so. Until this was fixed the core read the option, folded it into the style fingerprint, and then ignored it — so a project that set it saw a report claiming a different style and files that came back byte for byte identical. A dropped option with no signal is the same class of harm as a dropped finding.

It runs with no database at all

That is the point, not a fallback. sqlens:format is most useful in a fresh checkout, in a pre-commit hook, on a machine with nothing installed — and a formatter that needed a connection to reformat a text file would be unusable in exactly those places.

That works because a named dialect never reads a connection at all — not because the dialect is guessed. Name it once, in --dialect or in sqlens.format.dialect, and no database is involved anywhere in the run.

With auto the dialect follows the configured connection. Where there is none, the run refuses rather than picking one:

sqlens:format: format_dialect_unknown — no connection named a driver, so the dialect could not
be resolved. Name it with --dialect=pgsql, --dialect=mysql, or in sqlens.format.dialect — it is
not guessed, because comment syntax and keyword case differ per dialect and a guess rewrites the
file for the wrong engine.

Why a refusal and not a best effort. The formatter is not dialect-neutral, at two points that change bytes. # starts a comment in MySQL and does not in PostgreSQL, so a guessed PostgreSQL re-sets a MySQL comment as tokens. And PostgreSQL folds unquoted identifiers to lower case, so every keyword is safe to upper-case there, while MySQL has 21 unreserved keywords that can name a table — comment, view, json among them. A MySQL file formatted as PostgreSQL turns INSERT INTO comment into INSERT INTO COMMENT, and on a Linux MySQL with lower_case_table_names=0 that names a table which does not exist. Without --check the original has already been overwritten.

An engine this package does not support is named, never mapped onto its nearest neighbor:

format_dialect_unsupported — the connection uses `sqlite`, which this package supports on no
suite … applying them to another engine would produce advice that is confident, specific, and
about a different product.

--dialect short-circuits all of it. With it, the run reads no connection at all.

Files are written safely, or not at all

Writes go through a temporary file in the same directory, then a rename. Two reasons, both about destroying work:

• file_put_contents() opens the file before it has the bytes, so a process killed in between leaves an empty file where a migration was. Over a whole directory, one interrupted run can empty dozens.
• The temporary file is a sibling and not in /tmp, because rename() is atomic only within one filesystem — a cross-device rename silently degrades into copy-then-delete, which is the non-atomic write this avoids.

A file whose content would not change is not rewritten at all. An unnecessary write updates the mtime, restarts every watcher, rebuilds every cache keyed on it, and shows up in git status as a modification with an empty diff.