A committed DROP TABLE removes the table, its rows, indexes and triggers, and SQL has no undo. On PostgreSQL, DDL is transactional: if you ran it inside a BEGIN that is still open, ROLLBACK brings the table back. On MySQL, DROP TABLE commits implicitly, so it cannot be rolled back. After the commit, the table comes back only from a backup or point-in-time recovery. The same applies when an ORM command dropped it for you: prisma migrate reset, prisma db push --force-reset, drizzle-kit push --force or SQLAlchemy's drop_all() all end in DROP statements.
How to recover the data
- Stop whatever ran it (the migration job, the test run, the agent) so it does not run again against the restored database.
- PostgreSQL: restore a base backup plus WAL to a separate instance with
recovery_target_timejust before the drop, then move the table back withpg_dump -t tablefrom the restored instance into production. On a managed service, use its point-in-time restore, which creates a new instance (Amazon RDS, Google Cloud SQL). - MySQL: restore the last full backup to a separate server and replay the binary log up to just before the
DROPevent (mysqlbinlog --stop-position), then dump that table and load it into production. - If the whole database was reset (a
migrate resetor--force-reset), restore the whole instance from its last backup instead: every table was dropped and recreated empty.
Why it happens
A DROP TABLE reaches production in three usual ways: a migration or script meant for another environment (the wrong DATABASE_URL or .env), a development command run against production (the ORM resets below), and an AI coding agent that hits a schema drift or a failing migration and "fixes" it by resetting. Prompts the tools show before losing data are answered by whoever runs them, including an agent, or skipped with a --force flag.
How to fix it
Separate the credentials: development and test commands should not be able to reach production, and the production role used by the application or an agent should not own the tables or their schema, since only those owners (or a superuser) can drop them. Apply production schema changes only through reviewed migration files, never with db push or reset. Before dropping a table that is in use, rename it or revoke access first and drop it in a later release, once nothing has failed.
When the ORM drops the tables for you
These commands are meant for a development database. Each one ends in DROP statements when it points at production:
prisma migrate reset: drops the database or schema, recreates it and reapplies every migration.prisma db push --force-reset: resets the database before pushing the schema. A plainprisma db pushasks before a change that loses data, or proceeds with--accept-data-loss.drizzle-kit push --force: applies the schema diff and auto-approves the statements that lose data.- SQLAlchemy
Base.metadata.drop_all(engine): drops every table in the metadata, often from a test fixture; it hits production when the test run loads the production.env.
The public issues below are each one of these cases with an AI coding agent. Two things stop them regardless of the tool: production credentials that the development and test tooling never receive, and a check on the SQL itself. The ORM always sends plain DROP statements over the connection, which is where the proxy sees them; and the migration-file workflows (prisma migrate dev --create-only, prisma migrate diff --script, drizzle-kit generate) produce .sql files the CLI and the GitHub Action can check before they ship.
"cannot drop table … because other objects depend on it"
PostgreSQL refuses the drop while views or foreign keys depend on the table, and lists them:
ERROR: cannot drop table users because other objects depend on it
DETAIL: constraint orders_user_id_fkey on table orders depends on table users
HINT: Use DROP ... CASCADE to drop the dependent objects too.Treat that error as a review step, not an obstacle. CASCADE drops each object in the DETAIL lines with the table: dependent views disappear and the referencing tables lose their foreign key, so nothing stops them from pointing at rows that no longer exist.
How to stop it from happening again
VERICTO-010 · DROP TABLE or DROP DATABASE · Severity: Critical · AST node: DropStmt
The statement the rule reports
-- migrations/0042_drop_users.sql
DROP TABLE users;
Real CLI output
$ npx @vericto/vericto-cli@2.3.0 check --offline migrations/0042_drop_users.sql
note: offline mode: 42 standard rules at default severities, engine 5.1.0. Custom rules, overrides, sensitive columns, allowlists and the audit trail apply online only.
! migrations/0042_drop_users.sql FLAGGED VERICTO-010 critical
DropStmt
fix: Use a versioned migration tool (Flyway, Prisma Migrate, Alembic, Rails migrations)
0 blocked · 0 allowed · 1 flagged · 0 monitored (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))
In a migration file the CLI marks it FLAGGED and exits with 0: a schema change in a migration is usually intended, so it is raised for review instead of blocked. With --fail-on flag (or fail_on = "flag" in .vericto.toml) it exits with 1 and the job fails. Outside a migration (--sql, stdin or another file) it blocks, as the proxy and MCP check_sql do.
Try it free, no account
The CLI checks your .sql files on your machine with the 42 standard rules: no sign-up, no API key, and no SQL leaves your computer. It is open source (AGPL-3.0-only).
npx @vericto/vericto-cli@2.3.0 check --offline migrations/
On every pull request
The vericto/check-sql GitHub Action (Apache-2.0) runs the same offline check on the .sql files a PR changes and annotates the findings on the PR. Save it as .github/workflows/vericto.yml:
on: pull_request
jobs:
vericto:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v7
with: { fetch-depth: 0 }
- uses: vericto/check-sql@v1
with: { offline: true, upload-sarif: false }
At runtime, for AI agents
The SQL an AI agent writes never goes through a PR. The Vericto TCP proxy checks each statement between the application or agent and the database, and the MCP check_sql tool lets the agent check SQL before it runs it. Both use the runtime profile, which also blocks schema changes.
The AST condition in the engine
The rule is a deterministic predicate over the AST, in src/rules/evaluator.rs of the open source engine (AGPL-3.0-only), at this page's commit:
"VERICTO-010" => find(stmts, |s| {
s.kind == StatementKind::Drop
&& matches!(
s.drop_object,
Some(DropObjectKind::Table) | Some(DropObjectKind::Database)
)
})
.map(|s| violation(rule, s, Some(suggest_migration()))),
Frequently asked questions
Related rules
- VERICTO-012: DROP SCHEMA
- VERICTO-011: TRUNCATE TABLE
- VERICTO-015: ALTER TABLE DROP COLUMN
- VERICTO-001: DELETE without WHERE clause
More context
- prisma migrate reset or db push --force-reset deleted the data
- drizzle-kit push --force data loss
- SQLAlchemy drop_all() ran against production
- An AI agent deleted the production database: how to recover
- anthropics/claude-code#27063: drizzle-kit push --force wiped a production database
- anthropics/claude-code#36183: prisma db push --force-reset on production
- anthropics/claude-code#33183: prisma db push against production, reset accepted
- openai/codex#42480: a test fixture ran drop_all() with the production .env loaded
- When an AI agent runs destructive SQL on production
- DBA Stack Exchange: how to undo a DROP TABLE statement
- PostgreSQL docs: DROP TABLE