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.

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.