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_], and at most
    // 35 characters, so the whole name fits the identifier limit and a later
    // run's orphan sweep can still read it.
    '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 one statement on the shadow database may run, and how
    // long it may wait for a lock. It is not a bound on the run as a whole:
    // provisioning, which copies the template or replays a dump, has none of
    // its own. 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 the database of a run that did not come through clean — a migration that failed, or one the run could not judge — for debugging. It is off by default, and when it is on, the kept database is reported out loud, never silently retained: each one is named in the run's findings as CAP.L0.UNDETERMINED_CAPTURE with the reason shadow_kept_on_failure, and a run that stops with an error names them in the error. sqlens:drift and sqlens:postdeploy --expect-shadow name them beside the reason no expectation could be built. On PostgreSQL that is the clone the migrations ran in and the template it was made from. Drop them when you are done. A later run's orphan sweep removes them once they are older than capture.shadow.orphan_after_seconds, like any other leftover, so raise that setting while you need them for longer.

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, the database name and the host. All three 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 host matters because the commonest setup keeps one database name in every environment and tells production apart by DB_HOST alone. A local .env pointed at prod-db.acme.internal or acme-production.cluster-….rds.amazonaws.com is read as production even when the database is called acme. Every host counts: the one a url resolves to, each entry of a host list, and each host a read block offers, because a replica of production is production for this question.

The check is asked about two connections: the one the run examines, and the one it creates and drops the throwaway databases on. They are the same unless you set capture.shadow.connection or capture.shadow.direct_connection, and then the second is where a database is created, filled and dropped. A shadow connection whose name, database or host reads as production is refused like a production target, and the confirmation question names both connections.

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.

The virgin database takes the encoding, locale provider and locale of your application database rather than the cluster's defaults, ICU rules included, so a migration runs in the shadow under the rules it meets for real. A LATIN1 database refuses a character a UTF8 shadow would accept, and the shadow has to refuse it too.

On MySQL, the shadow role needs every privilege on the shadow name pattern and none anywhere else:

GRANT ALL PRIVILEGES ON `sqlens\_shadow\_%`.* TO 'sqlens_shadow'@'%';

CREATE and DROP alone are not enough, because the role does more than create and drop the database. It replays your schema dump into it, and Laravel's schema:dump ends every MySQL dump with the rows of the migrations table as INSERT statements. Then the migrations run there, and they need ALTER, INDEX, REFERENCES and whatever data statements they contain. Scoped to the pattern, the grant still reaches no database but the throwaway ones, and the role needs no privilege on your application database.

Note: with partial_revokes enabled, MySQL treats _ and % in a database name literally rather than as wildcards, so the grant above then reaches only a database named sqlens_shadow_%. The shadow names are generated per run, so no database-level grant can name them in advance. SQLens reads the setting and reports the run as undetermined (shadow_insufficient_privileges) instead of trying. On such a server the privileges have to be granted on *.*, where they reach every database, and partial revokes can take them back from the databases the role must not touch.

Note: with binary logging on, which is MySQL's default, and log_bin_trust_function_creators off, MySQL creates a trigger or a stored function only for an account with SUPER, whatever its definer. The role above holds no global privilege, so it cannot replay a dump with either: the run comes back undetermined (shadow_mysql_replay_failed) and names error 1419. log_bin_trust_function_creators = 1 on the server lifts the requirement, and whether that is acceptable there is your decision, since MySQL calls it the less safe choice for replication. Stored procedures and events are not affected.

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. When both are set the direct one is used for the provisioning link, because template operations and CREATE DATABASE do not survive a transaction pooler. Everything that touches the throwaway database afterwards goes through the same connection: the migrations run on it, and sqlens:drift and the expectation check of sqlens:postdeploy read the reference schema through it, never through your application's connection.

A capture.shadow.connection that resolves to the same place as the connection being examined is refused rather than warned about — building the reference there would create and then drop a database on the instance under comparison. The run comes back undetermined with that named reason, before anything is opened.

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.

A server that refuses to create the throwaway database for a reason none of these checks names, one that takes no writes for example, comes back undetermined too (shadow_provisioning_failed). The finding quotes the server's own message, masked like every capture message, instead of the run ending in a stack trace.

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; on MySQL @@read_only, @@innodb_read_only and the replication channel). A replica target is a named undetermined, detected before any database is created. MySQL does not set read_only on a replica by itself, so a replica left writable is recognized by its channel in performance_schema.replication_connection_configuration, which the account reads only with GRANT SELECT ON performance_schema.replication_connection_configuration. Without that grant the check reads the two flags alone. The two channels group replication keeps on every member are not counted.
  • 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. The connection that holds longest — the shadow connection used for migrate and capture — is bounded before its first query, so a shadow run cannot 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, innodb_lock_wait_timeout, and lock_wait_timeout, which bounds the wait on the metadata lock an ALTER TABLE queues behind. MySQL has no bound for the run of a schema change itself: max_execution_time applies to read-only SELECT statements only, so a long ALTER TABLE in the shadow database runs to its end.

What is NOT bounded, and why​

Three connections of the provisioning path run without these budgets, and knowing which matters if you point shadow mode at an instance you share:

  • The maintenance link that issues CREATE DATABASE … TEMPLATE, deliberately: cloning a real schema can take longer than the tight per-statement capture budget, so it runs on the server's own defaults.
  • The template connection and the MySQL replay, each of which replays a whole schema dump. These carry no stated reason and are a known gap rather than a decision.

Nothing bounds the run as a whole. capture.shadow.timeout is a budget per statement and per lock wait on the shadow database's own connection, so a run of ten migrations may take ten times as long, and the provisioning is not covered at all. What limits the three connections above is the guards that keep shadow mode off a production target in the first place. Shadow databases are created and dropped per run, so nothing here outlives it. A CI step that needs a ceiling on the whole run sets it in CI.

The budgets above 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. SQLENS_SHADOW_TIMEOUT sets the timeout per environment; a value that is not a positive integer keeps the shipped 120 seconds.

A session the server ends while a migration runs is not the migration's doing either: a terminated backend, a restart or a dropped network leaves the statement without a verdict. That migration is reported as undetermined (shadow_connection_lost) with the driver's own message, and the migrations after it as shadow_migrate_failed, the same as after a failure.

schema:dump is a prerequisite​

Both engines build the disposable database from your schema:dump artifact. MySQL has no CREATE DATABASE … TEMPLATE, so the dump is replayed into the disposable database itself before the pending migrations run on top. PostgreSQL replays it once into an empty template, proves that the template holds no rows, and clones the disposable database from it. Create the dump 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 or shadow_pgsql_schema_dump_missing), never a run against an empty database.

On MySQL the dump is replayed on a session of the server your application's database lives on, so it may only build the schema. What schema:dump writes is replayed: the tables, views, triggers, routines and events, indexes, ALTER TABLE, drops of those objects, rows and the table locks around them, session SETs, and transaction boundaries. The comments the MariaDB client writes are read as comments. A view, trigger, routine or event goes in without the DEFINER clause mysqldump gives it, so it belongs to the role that replays it, the way pg_dump --no-owner leaves the owners out on PostgreSQL: naming another account takes the global SET_ANY_DEFINER, which the role needs for nothing else. Anything that would reach past the disposable database refuses the whole dump before a statement runs, reported as undetermined with shadow_mysql_dump_refused and the line: USE, CREATE DATABASE and the other statements on a database, a SET of server-wide state, GRANT and account statements, an object named with its database (`app`.`users`), and any other statement a schema dump does not build a schema with. A dump written with mysqldump --databases or by a client that adds USE is one of these; regenerate it with php artisan schema:dump.

On PostgreSQL the file is pg_dump output written for psql: current pg_dump opens and closes each run with the psql commands \restrict and \unrestrict, and Laravel appends the rows of the migrations table as a COPY … FROM stdin block. Shadow mode reads the file the way psql does, skips both, and replays every other statement. The template therefore holds your structure and no rows. The run does not need the migrations rows: it calls each pending migration directly and never reads that table.

When the database cannot be provisioned​

A run that stops before it creates anything (the role may not create a database, another session holds the PostgreSQL template, the MySQL dump cannot be split into statements) reports every pending migration as undetermined, with the reason. What the run found out beyond the reason is written on STDERR by sqlens:lint, sqlens:drift and sqlens:postdeploy --expect-shadow: the sessions holding the template, or the line of the dump it could not read. The report keeps the reason alone, because the sessions on a server differ from one run to the next.

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 — and SQLENS_SHADOW_ORPHAN_AFTER_SECONDS sets it from the environment.

// 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.

A run that found leftovers says so in its own report, as LINT.SHADOW.ORPHANS: the databases it removed, or those it could not remove and why. A run the production guard blocked does not look, because it does not reach the server at all.

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.

The age is read on the database server's clock. A run names its database with the server's time, and a later run compares that name with the server's time again, so machines whose own clocks disagree, two CI runners or a container that slept, still agree on how old a database is. What the threshold assumes is that no run takes longer than it: capture.shadow.timeout bounds one statement, not a run.