Skip to main content

Shadow mode: setup, required privileges, and topologies

Shadow mode is the one part of SQLens that asks for more than read-only access. It runs your migrations for real against a disposable database and reads the resulting schema back, so it sees what pretend mode structurally cannot: a runtime error a migration only hits on real data or state, and a migration whose SQL depends on a query result. This page is what you need to know before you turn it on — what it requires, what it does, and what it deliberately will not do.

If you have not yet, read Capture modes first: shadow is the truth mode you reach for when a pretend run reports an undetermined it cannot resolve.

When to use it, and what it costs​

Start in pretend mode — it covers the overwhelming majority of migrations and needs nothing but read access. Reach for shadow mode when a run reports an undetermined whose reason points here, because only a real run can answer:

  • Runtime errors. A UNIQUE constraint added over columns that already hold duplicates, a NOT NULL column added to a populated table — these pass pretend (nothing executes) and fail in production. Shadow runs them for real and reports the failure as CAP.L0.MIGRATE_ERROR with the driver's own SQLSTATE.
  • Result-dependent migrations. A chunk() loop, an if ($query->exists()) branch, a backfill — pretend sees empty results, so the SQL it captures is a strict subset of what really runs. Shadow executes for real, so the branch runs.

It costs more than pretend: it provisions and tears down a database, and it runs only behind the production guard. Running a migration for real is exactly what a safety tool must never do to the wrong database, so the guard is not optional.

What it never does​

  • It never clones your live database. The shadow database is built from your schema — a PostgreSQL template database, or a replayed MySQL schema:dump, never a copy of your live database — with the pending migrations run on top. Your production rows are never copied anywhere. Shadow needs your schema, not your data.
  • It never writes to or locks your source database. The only thing it does to the connection you point it at is read the catalog and state (is this a replica? is it pooled?). All creation, migration, and teardown happen on the disposable database and its own connection.
  • It never leaves a database behind. The throwaway database is dropped after the run; a process killed mid-run is cleaned up by the orphan sweep on a later run (it only ever touches databases carrying the shadow prefix).

What it makes possible: replaying down()​

Because shadow mode owns a database it is allowed to break, it is also the only place a rollback can be tested. sqlens:lint --shadow --roundtrip replays up → down → up in the throwaway database and reports whether down() is a real inverse — a question that cannot be answered by reading SQL. It executes down() for real, so it is refused everywhere else; see the roundtrip for what it reports and what it refuses.

Setup​

Shadow mode is configured under capture.shadow in config/sqlens.php:

'shadow' => [
    // The connection to clone. null uses the run's resolved connection.
    'connection' => null,

    // A direct (non-pooled) connection to provision through. Template and
    // CREATE DATABASE operations break behind a transaction pooler
    // (PgBouncer), so set this to a connection that bypasses one. null lets
    // SQLens use the source connection only when it is provably direct.
    'direct_connection' => null,

    // The prefix of the throwaway database name. Only [a-z0-9_].
    'database_prefix' => 'sqlens_shadow_',

    // The environments the shadow mode may run in. It refuses to run
    // anywhere else, and the environment check is not overridable by --force.
    'allowed_environments' => ['local', 'testing'],

    // Whether to keep the throwaway database when a run fails, for
    // debugging. Off by default — a database nobody drops is a leak.
    'keep_on_failure' => false,

    // How many seconds the whole shadow provisioning and migration may take.
    // Positive; zero would mean "wait forever", the harm this prevents.
    'timeout' => 120,
],
  • allowed_environments is a hard gate: shadow refuses to run in any environment not listed, and --force does not override it. --force answers the confirmation question and nothing else — it is how a pipeline with nobody at the keyboard says "yes, I mean it". It moves no gate: neither the environment list above, nor the refusal to run against a connection that looks like production. Both of those are checked before the confirmation is even considered.
  • keep_on_failure keeps a failed run's database for debugging. It is off by default, and when it is on, the kept database is reported out loud — never silently retained.

What "looks like production" means​

There is no field in a Laravel connection that declares one production, so the check reads the names you already use: the connection name and the database name. Both are split into segments on _, -, . and whitespace, and a segment counts when it is one of production, prod, live or starts with one. So production_replica, acme-prod, prod2 and livedb are all read as production, while reproduce and probe are not — a marker has to be a segment, not a coincidence inside a longer word.

The matching is deliberately generous, because the two ways of being wrong do not cost the same. Refusing a database that was not production costs you a run. Allowing one that was costs you the database.

There is one exception, and it exists because the generous rule met a name half of Laravel uses: livewire is not read as production, though it starts with live. Naming the database after the application is what Herd, Valet and laravel new all produce, so acme_livewire_db was being refused as a production connection on developer machines. The exemption is for that segment alone — livewire_production is still production.

If a name of yours is read as production and should not be, that is worth reporting rather than working around: the list of coincidences is meant to grow, and the alternative — a narrower rule on the markers themselves — would drop livedb and proddb with it.

Required privileges​

This is the least-privilege exception, and it is stated plainly rather than hidden. Shadow mode uses a dedicated role, separate from your runtime AND your migration role — the same discipline SQLens's own security suite recommends to users, so the tool does not quietly widen the very privileges it flags.

On PostgreSQL, the shadow role needs only CREATEDB:

CREATE ROLE sqlens_shadow WITH LOGIN PASSWORD '…' CREATEDB;

A non-superuser with CREATEDB can create only databases it owns and can build a virgin database from template0 — it never needs superuser, and never owner rights on your application database. That is exactly enough to provision the disposable database and no more.

On MySQL, the shadow role needs CREATE and DROP, scoped to the shadow name pattern so it can touch nothing else:

GRANT CREATE, DROP ON `sqlens\_shadow\_%`.* TO 'sqlens_shadow'@'%';

Note: with partial_revokes enabled, MySQL treats _ and % in a database name literally rather than as wildcards. If your server runs with partial_revokes on, grant against the literal names your database_prefix produces instead.

Point shadow mode at this role with a dedicated connection (the pattern Prisma calls a shadowDatabaseUrl) via capture.shadow.connection or capture.shadow.direct_connection.

If a managed database offers neither CREATEDB/CREATE nor a provided shadow connection, shadow mode degrades to undetermined with a named reason — never a silent pass. See Understanding undetermined.

Topologies​

  • PgBouncer in transaction mode → a direct connection is mandatory, not a tip. CREATE DATABASE … TEMPLATE and the other template operations shadow mode needs cannot run behind a transaction pooler, which hands each statement a potentially different backend. Shadow detects this before issuing any DDL and stops with an actionable reason; set capture.shadow.direct_connection to a connection that bypasses the pooler.
  • Read/write split → capture always runs against the write instance. A read replica has a different state than its primary and forbids the writes provisioning needs, so shadow resolves the write host explicitly rather than trusting Laravel's connection heuristic, and probes the target before creating anything (pg_is_in_recovery() on PostgreSQL, @@read_only on MySQL). A replica target is a named undetermined, detected before any database is created.
  • Managed databases without CREATEDB → named degradation, not a hard failure. As above, a missing privilege with no provided shadow connection is reported as undetermined, so a green run never means "shadow silently did nothing".

The session bounds itself​

Shadow mode holds itself to the same "first, do no harm" it enforces on your migrations. Every connection it opens is bounded before its first query so a shadow run can never sit on an instance holding a statement or a lock:

  • PostgreSQL: statement_timeout, lock_timeout, and idle_in_transaction_session_timeout (a shadow run holds a transaction open across a whole migrate), plus application_name = 'sqlens' so a DBA watching pg_stat_activity sees exactly what SQLens is doing.
  • MySQL: max_execution_time and innodb_lock_wait_timeout.

All of these derive from the single capture.shadow.timeout (seconds), which the config refuses to let be zero or unbounded — so the defense can never be turned off. If one of these budgets fires, it is reported as undetermined (shadow_session_timeout), never a migration failure and never a passed-through exception.

MySQL: schema:dump is a prerequisite​

MySQL has no CREATE DATABASE … TEMPLATE, so shadow mode rebuilds the disposable database by replaying your schema:dump artifact and then running the pending migrations on top. Create it once with:

php artisan schema:dump

Keep the resulting database/schema/<connection>-schema.sql under version control and refresh it when your baseline schema changes — a missing or unreadable dump is reported as undetermined (shadow_mysql_schema_dump_missing), never a run against an empty database.

Cleaning up after a run that was killed​

capture.shadow.orphan_after_seconds is how old a leaked shadow database has to be before a later run removes it. It ships at 3600 — one hour.

// config/sqlens.php
'capture' => ['shadow' => ['orphan_after_seconds' => 3600]],

The sweep covers the one cleanup path a try/finally cannot: a process killed mid-run — an OOM kill, a pipeline timeout, a Ctrl-C — never reaches its own teardown, so what it left behind is dropped by a later run instead. On PostgreSQL the more expensive leftover is the template, which carries the whole project schema.

The age is what makes that safe: without it, a sweep could drop the shadow database of a run that is still using it. A value below an hour is raised to an hour rather than honored — lowering it would trade a disk-space annoyance for the one mistake this path must never make.