ALTER TABLE … DROP COLUMN removes the column and every value in it, and once committed there is no SQL to bring it back. PostgreSQL does not rewrite the table (the column is marked dropped and its space is reclaimed by later rewrites), but the data is no longer readable. To recover the values, restore a backup or point-in-time recovery into a separate instance, then re-add the column and copy the values back by primary key. On PostgreSQL, a DROP COLUMN inside an open transaction is undone with ROLLBACK; on MySQL, DDL commits implicitly.
How to recover the data
- Stop the code that writes to the table if it depends on the column, or roll back the deploy that dropped it.
- Restore a backup or point-in-time recovery to a separate instance, at a time just before the
ALTER TABLE. - Re-add the column in production (
ALTER TABLE users ADD COLUMN email text;) and copy the values from the restored instance by primary key, for example by exportingid, emailand running anUPDATE … FROMa staging table.
Why it happens
Columns are dropped by migrations that clean up the schema, and the risk is in the timing: the column is still read by the running code, by another service, by a view or by a report. A drop can also arrive with no review at all: an ORM that syncs the schema from the models, or an AI agent that changes the schema while investigating something else.
How to fix it
Drop a column in two releases. First stop using it: remove it from the code (in Rails, self.ignored_columns; in other ORMs, remove it from the model) and deploy. Then drop it in a later migration, once nothing reads it. On a busy PostgreSQL table, set a lock timeout, because the ALTER TABLE needs an exclusive lock and waiting for it blocks every query behind it:
SET lock_timeout = '5s';
ALTER TABLE users DROP COLUMN IF EXISTS email;On MySQL 8.0.29 and later, DROP COLUMN supports ALGORITHM=INSTANT.
When an AI agent drops the column
In anthropics/claude-code#46684, a coding agent asked only to investigate ran two ALTER TABLE … DROP COLUMN statements against a live production database. Nobody reviewed them because they never went through a migration file or a pull request: the agent sent them over its own connection. That is the case a CI check cannot see, and the reason the same rule also runs at runtime: through the proxy, or through MCP check_sql before the agent executes, a DROP COLUMN is blocked. In CI, where a migration that drops a column is usually intended, it is flagged for review.
How to stop it from happening again
VERICTO-015 · ALTER TABLE DROP COLUMN · Severity: High · AST node: AlterTableStmt > AlterTableCmd.subtype = DROP_COLUMN
The statement the rule reports
-- migrations/0045_drop_email.sql
ALTER TABLE users DROP COLUMN email;
Real CLI output
$ npx @vericto/vericto-cli@2.3.0 check --offline migrations/0045_drop_email.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/0045_drop_email.sql FLAGGED VERICTO-015 high
AlterTableStmt > AlterTableCmd.subtype = DROP_COLUMN
fix: Use soft-delete (nullable column) or a versioned migration tool
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.
The corrected version
-- migrations/0045_drop_email.sql
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;
$ npx @vericto/vericto-cli@2.3.0 check --offline migrations/0045_drop_email.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.
0 blocked · 1 allowed · 0 flagged · 0 monitored (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))
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-015" => find(stmts, |s| {
s.kind == StatementKind::AlterTable
&& matches!(s.alter_table_kind, Some(AlterTableKind::DropColumn))
})
.map(|s| {
let mut v = violation(
rule,
s,
Some("Use soft-delete (nullable column) or a versioned migration tool".to_string()),
);
v.ast_node_path = "AlterTableStmt > AlterTableCmd.subtype = DROP_COLUMN".to_string();
v
}),
Frequently asked questions
Related rules
- VERICTO-016: ALTER TABLE RENAME (table or column)
- VERICTO-018: ALTER TABLE ALTER COLUMN TYPE
- VERICTO-010: DROP TABLE or DROP DATABASE
- VERICTO-017: ALTER TABLE DROP CONSTRAINT (FK/PK/CHECK)
More context
- Claude Code dropped a column or deleted a production database
- drizzle-kit push --force data loss
- anthropics/claude-code#46684: unauthorized DROP COLUMN on a live production database
- When an AI agent runs destructive SQL on production
- Stack Overflow: dropping a column in Postgres on a large dataset
- PostgreSQL docs: ALTER TABLE