- Home
- Skills
- Data & Databases
- SQL Migration Safety Check: Catch locking DDL pre-deploy
More screenshots
Works with the AI Agents you already use
SQL Migration Safety Check: Catch locking DDL pre-deploy
A one-line migration can lock a production table. Scan Postgres and MySQL migration files for blocking DDL, risky NOT NULL adds and non-concurrent indexes, with a safer alternative for each.
$15
SQL Migration Safety Check: Catch locking DDL pre-deploy
Example session with this skill installed
Review references/unsafe.sql for a busy PostgreSQL example_events table. Use Markdown output, identify lock and rollout risks, and give staged replacement SQL. The file has explicit BEGIN/COMMIT, so do not add --transaction.
- Read your context and instructions
- Compiled the sql migration safety
Gate: FAIL (fail-on warning). 1 file, 5 statements, 9 findings: 2 errors and 7 warnings, each with a safer alternative.
| Severity | Check | Line | Safer alternative |
|---|---|---|---|
| ERROR | add-not-null | 3 | Add nullable, backfill in committed batches, validate a NOT VALID not-null CHECK, then SET NOT NULL. |
| ERROR | index-blocking | 5 | Use CREATE INDEX CONCURRENTLY outside a transaction; inspect invalid indexes after failures. |
| WARNING | mixed-ddl-update | 4 | Commit schema changes separately and backfill in bounded, independently committed batches. |
| WARNING | lock-timeout | 3, 4, 5 | Set a short positive lock_timeout before work, for example SET lock_timeout = '2s'. |
| WARNING | statement-timeout | 3, 4, 5 | Set a positive statement_timeout before work, for example SET statement_timeout = '5min'. |
Actual stdout from python3 -I -B scripts/migration_check.py references/unsafe.sql --format markdown (exit 1):
# SQL migration safety review
Files: 1; statements: 5; findings: 9; gate: FAIL (fail-on warning).
- WARNING lock-timeout — references/unsafe.sql:3
Why: No recognized positive lock_timeout is active; waiting DDL can queue behind locks and block later work.
Alternative: Set a short positive lock_timeout before work, for example SET lock_timeout = '2s'; choose a suitable retry policy.
- WARNING statement-timeout — references/unsafe.sql:3
Why: No recognized positive statement_timeout is active; slow operations can retain locks without a time budget.
Alternative: Set a positive statement_timeout before work, for example SET statement_timeout = '5min'; size it for the operation.
- ERROR add-not-null — references/unsafe.sql:3
Why: ADD COLUMN NOT NULL without DEFAULT can fail on existing rows and takes ACCESS EXCLUSIVE.
Alternative: Add nullable, backfill in committed batches, validate a NOT VALID not-null CHECK, then SET NOT NULL.
- WARNING lock-timeout — references/unsafe.sql:4
Why: No recognized positive lock_timeout is active; waiting DDL can queue behind locks and block later work.
Alternative: Set a short positive lock_timeout before work, for example SET lock_timeout = '2s'; choose a suitable retry policy.
- WARNING statement-timeout — references/unsafe.sql:4
Why: No recognized positive statement_timeout is active; slow operations can retain locks without a time budget.
Alternative: Set a positive statement_timeout before work, for example SET statement_timeout = '5min'; size it for the operation.
- WARNING lock-timeout — references/unsafe.sql:5
Why: No recognized positive lock_timeout is active; waiting DDL can queue behind locks and block later work.
Alternative: Set a short positive lock_timeout before work, for example SET lock_timeout = '2s'; choose a suitable retry policy.
- WARNING statement-timeout — references/unsafe.sql:5
Why: No recognized positive statement_timeout is active; slow operations can retain locks without a time budget.
Alternative: Set a positive statement_timeout before work, for example SET statement_timeout = '5min'; size it for the operation.
- ERROR index-blocking — references/unsafe.sql:5
Why: Ordinary CREATE INDEX takes SHARE and blocks writes for the build.
Alternative: Use CREATE INDEX CONCURRENTLY outside a transaction; inspect invalid indexes after failures.
- WARNING mixed-ddl-update — references/unsafe.sql:4
Why: DDL locks are retained until transaction end; a potentially large UPDATE prolongs them and adds row locks/WAL.
Alternative: Commit schema changes separately and backfill in bounded, independently committed batches.
Connects securely to your tools. The creator never sees your data.
About this skill
The problem You're about to ship a migration against a busy table. One innocent-looking ALTER or CREATE INDEX can hold a lock that queues every write behind it, and a DROP can't be taken back. You want a second pair of eyes on the SQL before the runner touches production.
See it before you buy: real input, real output Command (from the package folder, exit code 1, 0.025 s):
python3 -I -B scripts/migration_check.py references/unsafe.sql --format markdown
Before: references/unsafe.sql (excerpt)
ALTER TABLE example_events ADD COLUMN label text NOT NULL;
UPDATE example_events SET label = 'EXAMPLE';
CREATE INDEX example_label_idx ON example_events(label);
After: lines copied from the script's output (excerpt):
Files: 1; statements: 5; findings: 9; gate: FAIL (fail-on warning).
- ERROR add-not-null — references/unsafe.sql:3
Alternative: Add nullable, backfill in committed batches, validate a NOT VALID not-null CHECK, then SET NOT NULL.
- ERROR index-blocking — references/unsafe.sql:5
Alternative: Use CREATE INDEX CONCURRENTLY outside a transaction; inspect invalid indexes after failures.
- WARNING mixed-ddl-update — references/unsafe.sql:4
What the numbers show
- Ran on all 346 up-migrations in open-source Lemmy (2,675 statements): 6,216 findings in about 0.2 seconds.
- I checked 20 findings by hand, covering all 13 checks that fired: 18 were real things to review and 2 were false alarms.
- Honest limit: 4,804 of the findings are 'no lock or statement timeout set' reminders; the 20 were picked for coverage, not as an accuracy rate; MySQL is tested on hand-written examples only.
What you get
- A single Python script that reads your .sql files and flags risky patterns. It knows 16 checks for PostgreSQL and 10 for MySQL (one, the DELETE-without-WHERE check, is shared).
- Each finding gives the file, line, severity, why it's risky and a safer way to do it, as JSON or Markdown.
- An exit code you can use as a CI gate, on warnings or only on errors, plus a flag for runners that wrap each file in a transaction.
- Copy-ready staged SQL for the usual fixes: nullable-then-backfill columns, concurrent indexes, NOT VALID constraints, guarded MySQL DDL and batched deletes.
- The agent runs the script, puts the dangerous findings first and adapts the staged SQL once it knows your schema. 41 test cases and 6 saved sample outputs ship with it.
Measured on real data (full detail)
I ran it on every up-migration in the open-source Lemmy project (commit dd033a3d243e33d3fc51070c8dadf1477748e13f):
| Input | What the script reported | What I found reading them |
|---|---|---|
| 346 files, 2,675 statements, 603,991 bytes | 6,216 findings: 5,310 warnings and 906 errors | Checked 20 by hand: 18 were real things to review, 2 were false alarms |
Most of that volume, 4,804 findings, is "no lock or statement timeout set in this file". That's a nudge to check your settings, not 4,804 bugs. The 20 I checked were picked to cover all 13 checks that fired, so treat 18 of 20 as a sanity check, not an accuracy rate. Both false alarms were on a table the same migration had just emptied. Scanning all 346 files in one run took about 0.2 seconds. MySQL support is tested only on hand-written examples, not a real project. Details are in references/benchmarks.md.
Known limitations
- It pattern-matches SQL text. It doesn't connect to your database, so it can't see table sizes, existing schema or how long a lock would really last.
- It errs on the side of warning. An index on a table created in the same migration still gets flagged, and so do type changes your PostgreSQL version might do cheaply. A PASS is not a deploy approval.
- It can't see inside functions, procedures or dynamic SQL. It flags them for you to read. MySQL files with /*! */ comments, DELIMITER or SQL_MODE changes are rejected.
- The staged SQL needs your schema and your call on data you'd remove. None of the examples were run against a live database.
It runs locally with no network access and writes nothing unless you redirect the output.
Who it's for Backend engineers and DBAs reviewing plain SQL migrations before deploy, and anyone who wants a local check in CI. If you use an ORM, feed it the rendered SQL.
Requires
Python 3.8 or newer, no extra packages and no database server. Input is UTF-8 .sql files up to 2 MB each. PostgreSQL is the default. Pass --dialect mysql for MySQL 8.0 with InnoDB. You need to know whether your migration runner wraps each file in a transaction.
Price and guarantee $15 one time, yours to use with any agent that can run Python. Covered by Agensi's money-back guarantee if it doesn't do what this page promises.
Lexical static review only; no grammar validation or live DB connection. Maximum file size 2MB. Supports Postgres and MySQL only. Does not detect indirect dynamic SQL or procedural bodies.
How to use
- 1
Connect your agent to the Agensi MCP
Copy the prompt and paste it into Claude, ChatGPT, Cursor or any agent that supports MCP. It sets up the connection for you. Manual setup
- 2
Sign in to Agensi
Your agent shows a sign-in link. Sign in and click Allow.
- 3
Ask for what you need
Your agent loads this skill and uses it on your task. It always asks before any payment.
Reviews
No reviews yet
Be one of the first to try it. Every listed skill goes through the trust checks below.
Security scanned
Checked by our 8-point scan before listing
Fresh listing
Recently published to Agensi
Money-back guarantee
Doesn't do what its page promises? You get your money back.
Trust & safety
Security scan
Security scan passed 15 days ago