sqlens:drift — what the database holds against what the migrations describe

Your migrations describe a schema. Your database has one. sqlens:drift compares them.

php artisan sqlens:drift

The live side is the catalog of the connection you name, read through the sealed read-only session. The expectation side is a replay: the migration state applied for real into a throwaway database, whose catalog is then read with the same request.

A replay rather than a dump file, and the difference is not academic. A dump is a claim about what the migrations produce, made when somebody last regenerated it. A replay is the migrations themselves, run by the framework that will run them in production — and the two disagree exactly when it matters most: after a migration was edited, or added, and the dump was not refreshed.

Because the expectation side creates and drops a database, it runs behind the production guard and nowhere else.

It reports by default, and that decision is why the feature survives

The first run on a database that has been in production for years finds a decade of hand-made objects. A gate that turns red there is not adopted — it is switched off, and after that nothing is reported at all, which is strictly worse than a green exit somebody read.

So the shipped mode is report: it prints everything and exits clean.

mode: report
unexpected_in_database: 12 — exists in the database and in no migration
missing_in_database: 0 — the migrations describe it and the database does not have it
divergent: 3 — both sides have it and describe it differently
undetermined: 0
excluded: 0
compared: collation, column, constraint, index, table, view — and nothing else

The last line is the one that gives the others a meaning. undetermined says what could not be read; compared says what was never in the reading. The two catalog readers do not cover the same ground — nine object types on PostgreSQL, six on MySQL — and neither reads a stored routine, so an empty finding list is "nothing differs among these types" rather than "nothing differs".

That green exit is only defensible because the mode is printed with the findings. A run that exited clean without saying which mode produced it would be the silent green this package refuses.

The three ways two schemas disagree

Three and not two, because "different" is not one fact — the three have different causes and different remedies.

Finding	Means	Usual cause
DEPLOY.DRIFT.UNEXPECTED_IN_DATABASE	live has it, no migration describes it	a hotfix applied straight to production
DEPLOY.DRIFT.MISSING_IN_DATABASE	the migrations produce it, the database does not have it	a migration that failed, was skipped, or was rolled back
DEPLOY.DRIFT.DIVERGENT	both sides have it, described differently	an edited migration, or a hand-altered column

A fourth id, DEPLOY.DRIFT.UNCOMPARED, is not a way they disagree — it is the statement that a slice of the schema was never compared, because one side could not read that object type. It is the only one of the four that can be true while the other three come back empty and every line of the report is accurate.

The path from reporting to blocking

Three steps, in this order. Skipping the middle one is why drift gates get abandoned.

1. Report, and read what comes back

Run it. Expect a lot on a grown database, and expect most of it to be real.

2. Accept what you meant to keep

The reporting view somebody built by hand, the index a foreign system owns, the column that grew there historically — these are decisions, not drift. Record them once:

php artisan sqlens:drift --update-excludes

That writes every difference this run found into sqlens-drift-excludes.json, one entry per line, sorted by identity so a diff stays readable:

{
    "schema_version": 1,
    "entries": [
        {
            "type": "view",
            "name": "public.legacy_report",
            "attribute": null,
            "reason": "unexplained: replace this with why the difference is deliberate, or delete the entry"
        }
    ]
}

Every entry needs a reason, and the generated placeholder is refused by name. That is deliberate: an exclusion that does not say why it exists cannot be reviewed, and six months from now nobody can tell a decision from a finding somebody silenced on a Friday. A file straight out of --update-excludes therefore does not run until somebody edits it: replace each placeholder, or delete the line.

attribute narrows an exclusion to one part of a divergence — collation, default. Left null it accepts the object's disagreement whole, which also hides the day its type changes. Narrow it where you can.

The file lives in your repository, never in a database table. A decision about a schema belongs where decisions are reviewed: in a pull request, with an author and a date, and with its removal as visible as its addition. Point somewhere else with --exclude-file= or sqlens.deploy.drift.exclude_file.

An entry that matches nothing ends the run. Excludes nobody prunes grow into a ceiling under which real drift disappears, and the growth is invisible — forty stale lines look exactly like four that are still true. When a difference is resolved, delete its line.

The withdrawn differences stay counted in the summary (excluded: 4). An exclude file is not a blindfold: a report showing three findings where the comparison found forty would be accurate in every line and misleading as a whole.

3. Only then, block

php artisan sqlens:drift --fail-on-drift

or, once, in config/sqlens.php:

'deploy' => [
    'drift' => [
        'mode' => 'gate',
    ],
],

Anything that is not literally gate is report, a typo included — erring toward blocking on a value nobody can read would put a project into a mode it never asked for.

What an exclude file can never hide

A blind spot — an object type one side could not read — travels through the exclude file untouched, and the run stays inconclusive however many exclusions it carries.

That is the one rule this feature would be worth nothing without. A comparison that could not read indexes finds no index drift, prints zeroes and exits clean; every line of that report is accurate and the sentence a reader takes from it is not. Letting an exclusion silence it would turn "we cannot look here" into a clean bill of health with a reason attached.

Options

Option	Does
--connection=	the connection to compare; the application default when omitted
--format=	console, json, github, sarif or agent — the report goes to STDOUT, everything else to STDERR
--fail-on-drift	exit non-zero when the two sides disagree
--allow-undetermined	exit clean when the only thing blocking is what could not be read
--exclude-file=	where the accepted differences are recorded
--update-excludes	write this run's differences into that file, then stop
--force	skip the confirmation the production guard asks for

--update-excludes is housekeeping and ends the run: it writes, says what it wrote, and exits clean. It does not also judge — a command that gated on the very differences it had just written could never fail on the run that changed anything.

It carries NO time budget, and that is a decision rather than an oversight

sqlens:predeploy and sqlens:postdeploy both hold themselves to five seconds, enforced by a test and reported as a finding when they break it. sqlens:drift has no such number, deliberately.

The reason is that any number written here would be invented. Drift builds its expectation by replaying your migrations into a shadow database, and that is dominated by how much migration history you have: a project with 40 migrations and one with 4,000 are not the same measurement, and neither figure is about SQLens. An invented budget goes red on projects that are behaving perfectly, and the first fix anybody reaches for is to delete the check — so a made-up number is worse than none.

What replaces it is a statement about where the command belongs:

sqlens:drift is not a step in a deploy script's hot path. It is a CI or cron-adjacent run, outside the deploy window, where taking a minute costs nobody anything.

Run it on a schedule, or on a branch, or before a release — not between migrate --force and the moment traffic reaches the new release.

If you do want the comparison inside a deploy, that is sqlens:postdeploy --expect-shadow — off by default, behind the production guard, and deliberately outside that command's budget for exactly the reason above: its cost belongs to your migration history, not to the aftercare that runs unconditionally.

What it does not do

• It never writes to the database being examined. Not the live one; the throwaway database is the only thing it creates, and it drops it in a finally.
• It does not tell you which side is right. That a table exists in the database and in no migration is a fact; whether to write the migration or drop the table is a decision.
• It compares the canonical form of each side, so a difference that canonicalization erases is invisible here by design — and one it fails to erase is a finding the tool caused itself.
• Roles, grants and server settings are not compared. They are real drift and they matter, and they are the audit and security suites' subject.