TRUNCATE TABLE orders; removes every row of orders at once. In PostgreSQL, TRUNCATE is transactional: if you ran it after BEGIN and have not committed, ROLLBACK brings the rows back. In MySQL, TRUNCATE causes an implicit commit and cannot be rolled back. Once committed, the rows are only in your backups or point-in-time recovery.
How to recover the data
- PostgreSQL inside an open transaction: run
ROLLBACKbefore anything else. - Stop the writers that insert into the table, so new rows do not get mixed with the ones you restore.
- Restore a point-in-time copy from just before the
TRUNCATE(PostgreSQL PITR, or the managed service's point-in-time restore) into a separate instance, then copy the table back withpg_dump -t orders --data-onlyorINSERT ... SELECT. - On MySQL, restore the last full backup into a separate server and replay the binary log with
mysqlbinlog --stop-position(or--stop-datetime) up to just before theTRUNCATE; then copy the table back.
Why it happens
TRUNCATE is a fast way to empty a table, so it shows up in reset scripts, test fixtures and cleanup jobs. Those scripts then run against the wrong connection string, or an agent picks TRUNCATE to "clean up" a table. It has no WHERE, does not fire ON DELETE triggers, and with CASCADE in PostgreSQL it also empties every table that references it through a foreign key.
How to fix it
Keep TRUNCATE out of code that can reach production, and do not grant the TRUNCATE privilege to application or agent roles. To remove old data, delete by a condition, in batches if the table is large:
DELETE FROM orders WHERE created_at < NOW() - INTERVAL '90 days';For test resets, point the script at a database whose name or host cannot be production.
How to stop it from happening again
VERICTO-011 · TRUNCATE TABLE · Severity: Critical · AST node: TruncateStmt
The statement the rule reports
-- reset.sql
TRUNCATE TABLE orders;
Real CLI output
$ npx @vericto/vericto-cli@2.3.0 check --offline reset.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.
✖ reset.sql BLOCKED VERICTO-011 critical
TruncateStmt
fix: DELETE FROM {table} WHERE created_at < NOW() - INTERVAL '90 days'
1 blocked · 0 allowed · 0 flagged · 0 monitored (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))
✖ Vericto blocked 1 statement (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))
✖ reset.sql BLOCKED VERICTO-011 critical
TruncateStmt
fix: DELETE FROM {table} WHERE created_at < NOW() - INTERVAL '90 days'
This is Vericto enforcing a rule — the exit code is intentional, not a crash.
To allow a finding on purpose, add an inline comment on the statement:
-- vericto:ignore[VERICTO-011] <reason> (a reason is required)
The CLI blocks it and exits with code 1, so the CI job fails before the merge.
The corrected version
-- reset.sql
DELETE FROM orders WHERE created_at < NOW() - INTERVAL '90 days';
$ npx @vericto/vericto-cli@2.3.0 check --offline reset.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-011" => find(stmts, |s| s.kind == StatementKind::Truncate)
.map(|s| violation(rule, s, suggest_truncate(s))),
Frequently asked questions
Related rules
- VERICTO-001: DELETE without WHERE clause
- VERICTO-010: DROP TABLE or DROP DATABASE
- VERICTO-012: DROP SCHEMA