TRUNCATE TABLE by mistake: can you roll it back?

VERICTO-011 Critical In CI (offline CLI): BLOCKED

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 ROLLBACK before 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 with pg_dump -t orders --data-only or INSERT ... 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 the TRUNCATE; 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