preferRobustStmts

Diagnostic Category: lint/safety/preferRobustStmts

Since: vnext

Sources: - Inspired from: squawk/prefer-robust-stmts

Description

Prefer statements with guards for robustness in migrations.

When running migrations outside of transactions (e.g., CREATE INDEX CONCURRENTLY), statements should be made robust by using guards like IF NOT EXISTS or IF EXISTS. This allows migrations to be safely re-run if they fail partway through.

Examples

Invalid

CREATE INDEX CONCURRENTLY users_email_idx ON users (email);

code-block.sql:1:1 lint/safety/preferRobustStmts ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  ! Concurrent index creation should use IF NOT EXISTS.

  > 1 │ CREATE INDEX CONCURRENTLY users_email_idx ON users (email);
      │ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    2 │ 

  i Add IF NOT EXISTS to make the migration re-runnable if it fails.

DROP INDEX CONCURRENTLY users_email_idx;

code-block.sql:1:1 lint/safety/preferRobustStmts ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  ! Concurrent drop should use IF EXISTS.

  > 1 │ DROP INDEX CONCURRENTLY users_email_idx;
      │ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    2 │ 

  i Add IF EXISTS to make the migration re-runnable if it fails.

Valid

CREATE INDEX CONCURRENTLY IF NOT EXISTS users_email_idx ON users (email);

DROP INDEX CONCURRENTLY IF EXISTS users_email_idx;

How to configure

{
  "linter": {
    "rules": {
      "safety": {
        "preferRobustStmts": "error"
      }
    }
  }
}