DROP SCHEMA CASCADE deleted everything in it: how to recover

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

DROP SCHEMA analytics CASCADE; drops the schema and everything in it: tables with their data, views, functions, sequences. CASCADE also drops objects in other schemas that depend on them, such as a view in public that reads an analytics table. In PostgreSQL, DDL is transactional, so an uncommitted DROP SCHEMA can be rolled back. Once committed, the schema comes back only from a backup.

How to recover the data

  • PostgreSQL inside an open transaction: run ROLLBACK.
  • Stop the jobs and applications that use the schema, so they do not recreate partial objects in it.
  • Restore a point-in-time copy from just before the drop into a separate instance, export the schema with pg_dump -n analytics and load it back with pg_restore or psql.
  • Recreate the dependent objects outside the schema that CASCADE removed (views, foreign keys); the DROP SCHEMA output lists them as notices.

Why it happens

A migration or cleanup script drops a schema it believes is unused, and CASCADE is added because the plain statement failed with "cannot drop schema analytics because other objects depend on it". That error was the warning: CASCADE silences it and removes the dependents too. In MySQL, DROP SCHEMA is a synonym for DROP DATABASE and also commits implicitly.

How to fix it

Use the default RESTRICT behaviour, which refuses to drop a schema that still contains objects, and drop its objects one by one in a reviewed, versioned migration. To retire a schema safely, rename it first and drop it only after nothing has failed for a while:

ALTER SCHEMA analytics RENAME TO analytics_retired;

How to stop it from happening again

VERICTO-012 · DROP SCHEMA · Severity: Critical · AST node: DropStmt

The statement the rule reports

-- migrations/0043_drop_analytics.sql
DROP SCHEMA analytics CASCADE;

Real CLI output

$ npx @vericto/vericto-cli@2.3.0 check --offline migrations/0043_drop_analytics.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/0043_drop_analytics.sql  BLOCKED  VERICTO-012 critical
    DropStmt
    fix: Use a versioned migration tool (Flyway, Prisma Migrate, Alembic, Rails migrations)
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))
  ✖ migrations/0043_drop_analytics.sql  BLOCKED  VERICTO-012 critical
      DropStmt
      fix: Use a versioned migration tool (Flyway, Prisma Migrate, Alembic, Rails migrations)

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-012] <reason>   (a reason is required)

The CLI blocks it and exits with code 1, so the CI job fails before the merge.

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-012" => find(stmts, |s| {
    s.kind == StatementKind::Drop
        && matches!(
            s.drop_object,
            Some(DropObjectKind::Schema) | Some(DropObjectKind::Database)
        )
})
.map(|s| violation(rule, s, Some(suggest_migration()))),

Frequently asked questions