Database Safety

Database Safety

CRITICAL: Database operations can be irreversible. This skill prevents data catastrophes.

Danger Levels

Pre-Operation Protocol

Before ANY Data Modification

Step 1: Identify the scope

-- Before DELETE: Count affected rows
SELECT COUNT(*) FROM users WHERE status = 'inactive';

-- Before UPDATE: Preview changes
SELECT id, current_value, 'new_value' as proposed_value
FROM table
WHERE condition;

-- Before DROP: Check foreign keys and dependencies
SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
    AND ccu.table_name = 'table_to_drop';

Step 2: Present impact to user

## Database Operation Impact Assessment

**Operation:** DELETE FROM users WHERE last_login < '2023-01-01'
**Database:** production_db
**Environment:** production ⚠️

**Impact:**
- Rows to be deleted: 8,234
- Table total rows: 45,678
- Percentage affected: 18%

**Related data that may be affected:**
- orders table: 12,456 rows reference these users (CASCADE DELETE)
- sessions table: 3,234 active sessions will be invalidated
- audit_logs: Will retain 8,234 orphaned references

**This operation is IRREVERSIBLE without a backup.**

Do you want to proceed? Please confirm with the exact count: "yes, delete 8234 rows"

For Critical Operations

DROP TABLE

⛔ **CRITICAL: DROP TABLE REQUESTED**

**Command:** DROP TABLE legacy_users

**Current state:**
- Table exists: ✓
- Row count: 15,234
- Size on disk: 45.2 MB
- Foreign key references: 3 tables
- Last backup: 2024-01-15 09:00:00 (2 days ago)

**This will permanently delete:**
- 15,234 records
- All indexes (4)
- All constraints (7)
- All triggers (2)

**Cascade impact:**
- orders.user_id: 23,456 rows will have NULL user_id (SET NULL)
- comments.author_id: 8,901 rows will be deleted (CASCADE)

**Before proceeding, I will:**
1. Create a backup of this table
2. Export data to JSON/CSV for recovery
3. Document the table schema for recreation if needed

Proceed with backup and drop? Type "yes, drop legacy_users permanently" to confirm.

DROP DATABASE

⛔⛔⛔ **EXTREME DANGER: DROP DATABASE REQUESTED**

**Command:** DROP DATABASE myapp_production

**⚠️ THIS WILL DESTROY THE ENTIRE DATABASE ⚠️**

**Database statistics:**
- Tables: 47
- Total rows: 1,234,567
- Size: 2.3 GB
- Created: 2022-03-15
- Last backup: UNKNOWN ⚠️

**I will NOT execute this command unless:**
1. You confirm this is NOT a production database
2. You have a verified backup
3. You provide the exact command with database name

This is almost never the right approach. Consider alternatives:
- Drop specific tables instead
- Use a fresh database for testing
- Restore from backup to a different database

If you truly need this, type: "I confirm DROP DATABASE myapp_production is intentional and I have backups"

TRUNCATE TABLE

⛔ **CRITICAL: TRUNCATE TABLE REQUESTED**

**Command:** TRUNCATE TABLE orders

**What TRUNCATE does:**
- Removes ALL rows instantly (no WHERE clause possible)
- Resets auto-increment counters
- Cannot be rolled back in most databases
- Faster than DELETE but more dangerous

**Current state:**
- Row count: 89,234
- Estimated recovery time: Not possible without backup

**Alternative if you want to delete specific rows:**
```sql
DELETE FROM orders WHERE created_at < '2023-01-01';

Confirm truncation by typing: "yes, truncate orders and delete 89234 rows"

## DELETE Safety

### DELETE Without WHERE Clause

**NEVER execute without warning:**

```markdown
🔴 **DANGEROUS: DELETE WITHOUT WHERE CLAUSE**

**Command:** DELETE FROM users

**This will delete ALL 45,678 rows in the users table.**

This is almost always a mistake. Did you mean:
- DELETE FROM users WHERE status = 'deleted'
- TRUNCATE TABLE users (if you really want all rows gone)
- DELETE FROM users WHERE id = specific_id

If you truly want to delete all rows, use TRUNCATE instead (it's faster and more explicit about intent).

I will NOT execute DELETE without WHERE. Please provide a WHERE clause or confirm TRUNCATE.

DELETE With WHERE Clause

## DELETE Operation Preview

**Command:** DELETE FROM users WHERE status = 'inactive' AND last_login < '2023-01-01'

**Rows to be deleted:** 1,234

**Sample of rows that will be deleted:**
| id | email | status | last_login |
|----|-------|--------|------------|
| 456 | user1@example.com | inactive | 2022-05-15 |
| 789 | user2@example.com | inactive | 2022-11-20 |
| ... | ... | ... | ... |

**Verification query (run this first):**
```sql
SELECT COUNT(*), MIN(last_login), MAX(last_login)
FROM users
WHERE status = 'inactive' AND last_login < '2023-01-01';

Proceed with deletion? Type "yes, delete 1234 inactive users" to confirm.

## UPDATE Safety

### UPDATE Without WHERE Clause

```markdown
🔴 **DANGEROUS: UPDATE WITHOUT WHERE CLAUSE**

**Command:** UPDATE users SET role = 'user'

**This will update ALL 45,678 rows in the users table.**

This will:
- Set ALL users to role 'user'
- Including admin accounts (currently 23)
- Including system accounts (currently 5)

**You may have meant:**
```sql
UPDATE users SET role = 'user' WHERE role IS NULL;
UPDATE users SET role = 'user' WHERE id = specific_id;

I will NOT execute UPDATE without WHERE. Please provide a WHERE clause.

### UPDATE With WHERE Clause

```markdown
## UPDATE Operation Preview

**Command:** UPDATE products SET price = price * 1.1 WHERE category = 'electronics'

**Rows to be updated:** 234

**Sample of changes:**
| id | name | current_price | new_price |
|----|------|---------------|-----------|
| 101 | iPhone | 999.00 | 1098.90 |
| 102 | MacBook | 1299.00 | 1428.90 |
| ... | ... | ... | ... |

**Total price impact:** $23,456.00 increase across all products

Proceed with update? Type "yes, update 234 products" to confirm.

Batch Operations Safety

For operations affecting many rows:

// Safe batch deletion pattern
async function safeBatchDelete(table: string, condition: string, batchSize: number = 1000) {
  // Get total count first
  const total = await db.query(`SELECT COUNT(*) FROM ${table} WHERE ${condition}`);

  console.log(`Will delete ${total} rows in batches of ${batchSize}`);
  console.log('Press Ctrl+C within 10 seconds to abort...');
  await sleep(10000);

  let deleted = 0;
  while (deleted < total) {
    const result = await db.query(`
      DELETE FROM ${table}
      WHERE ${condition}
      LIMIT ${batchSize}
    `);

    deleted += result.rowCount;
    console.log(`Deleted ${deleted}/${total} rows`);

    // Allow interruption between batches
    await sleep(100);
  }

  return deleted;
}

Transaction Safety

-- ALWAYS use transactions for multi-step operations
BEGIN;

-- Your operations here
DELETE FROM order_items WHERE order_id = 123;
DELETE FROM orders WHERE id = 123;

-- Preview the result before committing
SELECT * FROM orders WHERE id = 123; -- Should be empty

-- If everything looks good:
COMMIT;

-- If something went wrong:
ROLLBACK;

Environment Protection

Production Safeguards

// Add to your application
function isDangerousQuery(sql: string): boolean {
  const dangerous = [
    /DROP\s+(TABLE|DATABASE)/i,
    /TRUNCATE/i,
    /DELETE\s+FROM\s+\w+\s*$/i,  // DELETE without WHERE
    /UPDATE\s+\w+\s+SET\s+.*$/i, // UPDATE without WHERE
  ];

  return dangerous.some(pattern => pattern.test(sql));
}

function executeQuery(sql: string) {
  if (process.env.NODE_ENV === 'production' && isDangerousQuery(sql)) {
    throw new Error(`Dangerous query blocked in production: ${sql.substring(0, 50)}...`);
  }

  return db.query(sql);
}

Read Replica Routing

// Route dangerous queries awareness
function getConnection(sql: string) {
  const isWrite = /INSERT|UPDATE|DELETE|DROP|TRUNCATE|ALTER/i.test(sql);

  if (isWrite) {
    console.log('⚠️ Write operation detected, using primary database');
    return primaryConnection;
  }

  return replicaConnection;
}

Backup Before Dangerous Operations

# Quick backup commands

# PostgreSQL - specific table
pg_dump -t table_name database > table_backup_$(date +%Y%m%d_%H%M%S).sql

# MySQL - specific table
mysqldump database table_name > table_backup_$(date +%Y%m%d_%H%M%S).sql

# Full database backup
pg_dump -Fc database > full_backup_$(date +%Y%m%d_%H%M%S).dump

Verification Queries

Always run these BEFORE destructive operations:

-- Verify DELETE scope
EXPLAIN DELETE FROM users WHERE condition;
SELECT COUNT(*) FROM users WHERE condition;

-- Verify UPDATE scope
SELECT * FROM table WHERE condition LIMIT 10;
SELECT COUNT(*) FROM table WHERE condition;

-- Verify no cascade surprises
SELECT
    tc.table_name,
    tc.constraint_name,
    rc.delete_rule
FROM information_schema.referential_constraints rc
JOIN information_schema.table_constraints tc
    ON rc.constraint_name = tc.constraint_name
WHERE rc.delete_rule = 'CASCADE';

Best Practices Summary