database-safety

Database Safety

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

Danger Levels

Level	Operations	Requires
⛔ CRITICAL	DROP DATABASE, DROP TABLE, TRUNCATE	Backup + explicit confirmation
🔴 HIGH	DELETE without WHERE, UPDATE without WHERE	Explicit confirmation + scope verification
🟠 MEDIUM	DELETE with WHERE, UPDATE with WHERE	Scope verification + preview
🟢 LOW	SELECT, INSERT, CREATE	Standard execution

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

DO	DON'T
Always use WHERE clause	Run DELETE/UPDATE without WHERE
Preview with SELECT first	Trust row counts blindly
Use transactions	Commit automatically
Backup before destructive ops	Assume you can recover
Verify environment	Run production commands casually
Use batch operations	Delete millions in one query
Check cascade effects	Ignore foreign key relationships