The hard part of AI-assisted database work is not generating a migration. It is knowing whether the migration survives a real engine. A text review catches a missing index or a suspicious naming convention, but it often misses the failure that matters: a DROP COLUMN on a column a background job still selects from, or a DELETE that works on your laptop but not against a table with a generated column.
The current conversation about gating AI tools usually focuses on permissions. That matters, but the more practical seam for database work is deterministic verification. The model proposes a change; a separate, boring harness makes the change prove itself in a scratch environment before a human has to reason about it.
This article walks through a small harness that does exactly that. It takes a generated migration, applies it to a throwaway SQLite database, refuses destructive statements without rollback evidence, and exits non-zero when anything fails. The point is not the specific SQL dialect. The point is that proposal and verification stay separate.
Where a free model and a free server fit
One practical setup is to have MonkeyCode's free model access draft the migration and its free server option run the replay suite. Disclosure: This article was prepared as part of MonkeyCode's product outreach. The guard itself is provider-neutral. You can point the generator at any endpoint you already use, and you can run the harness on your laptop, in CI, or on an always-on server.
The model is useful for producing a first draft quickly, but it is not the right place to prove correctness. A model can generate SQL that parses locally and still destroys a column, locks a large table, or violates a constraint that only exists in production. Those failures are deterministic, so they should be checked by deterministic code.
The harness
Save this as prove_migration.py.
#!/usr/bin/env python3
import subprocess
import sys
import tempfile
from dataclasses import dataclass
@dataclass
class CheckResult:
name: str
passed: bool
detail: str
def run(cmd, cwd, timeout=120):
return subprocess.run(
cmd,
cwd=cwd,
capture_output=True,
text=True,
timeout=timeout,
)
def make_scratch_db(kind='sqlite'):
if kind == 'sqlite':
tmp = tempfile.mkdtemp()
return tmp, f'{tmp}/scratch.db'
raise NotImplementedError('swap in a Postgres container for non-SQLite checks')
def parse_migration(patch):
# Simplified. A production guard should use a real SQL parser.
statements = [s.strip() for s in patch.split(';') if s.strip()]
if not statements:
raise ValueError('no statements found')
return statements
def is_destructive(statement):
lowered = statement.lower()
destructive_markers = ['drop table', 'drop column', 'delete ', 'truncate']
return any(marker in lowered for marker in destructive_markers)
def check_migration(patch):
results = []
db_dir, db_path = make_scratch_db()
baseline_schema = 'CREATE TABLE orders(id INTEGER PRIMARY KEY, status TEXT);'
baseline = run(['sqlite3', db_path, baseline_schema], db_dir)
results.append(CheckResult('baseline_schema', baseline.returncode == 0, baseline.stderr.strip()[:200]))
statements = parse_migration(patch)
for index, statement in enumerate(statements, start=1):
result = run(['sqlite3', db_path, statement], db_dir)
detail = statement[:80]
if result.returncode != 0:
detail = result.stderr.strip()[:300]
results.append(CheckResult(f'statement_{index}', result.returncode == 0, detail))
for statement in statements:
if is_destructive(statement):
results.append(CheckResult(
'rollback_guard',
False,
f'destructive statement has no rollback evidence: {statement[:80]}',
))
return results
def prove_patch(patch):
results = check_migration(patch)
for result in results:
status = 'PASS' if result.passed else 'FAIL'
print(f'[{status}] {result.name}: {result.detail}')
return all(result.passed for result in results)
if __name__ == '__main__':
patch = sys.stdin.read()
ok = prove_patch(patch)
sys.exit(0 if ok else 1)
Run it by piping a generated migration into standard input:
python prove_migration.py < candidate_migration.sql
A non-destructive migration that adds a column will pass. A migration containing a bare DROP COLUMN will fail the rollback guard even if SQLite accepts the statement. That bluntness is deliberate. Destructive changes should never be allowed to ride through on syntax alone.
If your local machine keeps sleeping mid-replay, run the same script on a free server. The only real requirement is a Python runtime and the target database engine. A long-running replay suite is a better fit for an unattended server than for a laptop that may close its lid at the wrong moment.
Make the guard stricter than the model
The most important design choice is that the model is not part of the verification loop. Do not ask one model to generate a migration and then ask a model to review it without any executable intermediate step. A model can be confidently wrong twice. The harness should be dumb, readable, and indifferent to how persuasive the patch text sounds.
A useful starting rule set looks like this:
| Patch shape | Guard behavior | Reason |
|---|---|---|
| Adds a column or table | Runs the statement against scratch | Catches syntax and constraint failures |
Contains DROP or DELETE
|
Fails unless a separate rollback artifact is supplied | Destructive changes need explicit recovery evidence |
| Contains multiple statements | Runs each statement separately | Pinpoints the exact failing statement |
| Produces no statements | Fails immediately | Empty output is not a migration |
| Mentions untrusted functions | Blocks or routes to human review | Side effects are hard to replay safely |
The table is intentionally conservative. It is easier to soften a rule after seeing false positives than to discover too late that a destructive migration slipped through.
What this does not solve
This harness is not a replacement for a real migration tool. The naive semicolon splitter will fail on semicolons inside strings, functions, or procedural SQL. The SQLite scratch database will not catch behavior that only appears in Postgres, MySQL, or a specific extension. The rollback guard is heuristic; it blocks obvious destructive statements, but it does not reconstruct a rollback plan or verify point-in-time recovery.
It also does not protect against generated SQL that is syntactically valid but semantically wrong. A model can add a column with the wrong type, and the scratch database will accept it. That still requires a human to compare the change against the requested intent.
Finally, do not run untrusted generated SQL against a database that contains real customer data. The scratch database should always be disposable and isolated.
Who should not use this approach
Teams with a mandatory database administrator review for every migration may find this harness only mildly useful as a pre-review filter. Teams on regulated schemas, or teams with production migrations that require point-in-time recovery, should keep their existing change-management process and treat this only as a local sanity check. If you already have paid CI runners and a mature migration pipeline, you may not need a separate free server for the replay step, although the guard itself can still be worth copying.
The part worth keeping
The model is replaceable. The guard is the durable artifact. A proposed migration should survive a scratch replay, identify any destructive statement that lacks rollback evidence, and produce a machine-readable pass or fail signal before a human spends time on review. If you maintain a migration-heavy codebase, the guard is the part worth copying; the model can stay cheap and replaceable.
Top comments (0)