db-run-sql

name	db-run-sql
description	Execute SQL file or inline SQL with transaction safety and timing
agent	architect
subtask	false

Run SQL

Execute SQL file or inline SQL with transaction safety and timing

1. YOLO Mode - Fast, Autonomous (0-1 prompts)

• Autonomous decision making with logging
• Minimal user interaction
• Best for:* Simple, deterministic tasks

2. Interactive Mode - Balanced, Educational (5-10 prompts) [DEFAULT]

• Explicit decision checkpoints
• Educational explanations
• Best for:* Learning, complex decisions

3. Pre-Flight Planning - Comprehensive Upfront Planning

• Task analysis phase (identify all ambiguities)
• Zero ambiguity execution
• Best for:* Ambiguous requirements, critical work

Parameter:* mode (optional, default: interactive)

---

Acceptance Criteria

Purpose:* Definitive pass/fail criteria for task completion

Checklist:*

acceptance-criteria:
  - [ ] Data persisted correctly; constraints respected; no orphaned data
    type: acceptance-criterion
    blocker: true
    validation: |
      Assert data persisted correctly; constraints respected; no orphaned data
    error_message: "Acceptance criterion not met: Data persisted correctly; constraints respected; no orphaned data"

---

Error Handling

Strategy:* retry

Common Errors:*

1. Error:* Connection Failed

   • Cause:* Unable to connect to Neo4j database
   • Resolution:* Check connection string, credentials, network
   • Recovery:* Retry with exponential backoff (max 3 attempts)

2. Error:* Query Syntax Error

   • Cause:* Invalid Cypher query syntax
   • Resolution:* Validate query syntax before execution
   • Recovery:* Return detailed syntax error, suggest fix

3. Error:* Transaction Rollback

   • Cause:* Query violates constraints or timeout
   • Resolution:* Review query logic and constraints
   • Recovery:* Automatic rollback, preserve data integrity

---

Inputs

• sql (string): Either a file path or inline SQL statement

---

1. Determine Input Type

Check if input is file or inline SQL:

if [ -f "{sql}" ]; then
  echo "Mode: File"
  SQL_FILE="{sql}"
  SQL_MODE="file"
else
  echo "Mode: Inline SQL"
  SQL_MODE="inline"
  SQL_CONTENT="{sql}"
fi

2. Preview SQL

Show what will be executed:

echo "=========================================="
echo "SQL TO BE EXECUTED:"
echo "=========================================="

if [ "$SQL_MODE" = "file" ]; then
  cat "$SQL_FILE"
else
  echo "$SQL_CONTENT"
fi

echo ""
echo "=========================================="

3. Safety Checks

Warn about dangerous operations:

# Check for destructive operations
DANGEROUS_PATTERNS="DROP TABLE|TRUNCATE|DELETE FROM.WHERE.*1=1|UPDATE.WHERE.*1=1"

if echo "$SQL_CONTENT" | grep -Eiq "$DANGEROUS_PATTERNS"; then
  echo "⚠️  WARNING: Potentially destructive operation detected!"
  echo ""
  echo "Detected patterns:"
  echo "$SQL_CONTENT" | grep -Ei "$DANGEROUS_PATTERNS"
  echo ""
  echo "Database: $SUPABASE_DB_URL (redacted)"
  echo ""
  echo "Continue? Type 'I UNDERSTAND THE RISKS' to proceed:"
  read CONFIRM
  [ "$CONFIRM" = "I UNDERSTAND THE RISKS" ] || { echo "Aborted"; exit 1; }
fi

4. Transaction Mode Selection

Ask user about transaction handling:

Transaction mode:
1. auto   - Wrap in BEGIN/COMMIT (safe, rolls back on error)
2. manual - Execute as-is (file may have own transaction control)
3. read   - Read-only transaction (safe for queries)

Select mode (1/2/3):

5. Execute SQL

Run with selected transaction mode and timing:

echo "Executing SQL..."

if [ "$TRANSACTION_MODE" = "auto" ]; then
  # Wrapped transaction
  (
    echo "BEGIN;"
    if [ "$SQL_MODE" = "file" ]; then
      cat "$SQL_FILE"
    else
      echo "$SQL_CONTENT"
    fi
    echo "COMMIT;"
  ) | psql "$SUPABASE_DB_URL" \
      -v ON_ERROR_STOP=1 \
      --echo-errors \
      2>&1 | tee /tmp/dbsage_sql_output.txt

elif [ "$TRANSACTION_MODE" = "read" ]; then
  # Read-only transaction
  (
    echo "BEGIN TRANSACTION READ ONLY;"
    if [ "$SQL_MODE" = "file" ]; then
      cat "$SQL_FILE"
    else
      echo "$SQL_CONTENT"
    fi
    echo "COMMIT;"
  ) | psql "$SUPABASE_DB_URL" \
      -v ON_ERROR_STOP=1 \
      2>&1 | tee /tmp/dbsage_sql_output.txt

else
  # Manual mode (no wrapper)
  if [ "$SQL_MODE" = "file" ]; then
    psql "$SUPABASE_DB_URL" \
      -v ON_ERROR_STOP=1 \
      -f "$SQL_FILE" \
      2>&1 | tee /tmp/dbsage_sql_output.txt
  else
    psql "$SUPABASE_DB_URL" \
      -v ON_ERROR_STOP=1 \
      -c "$SQL_CONTENT" \
      2>&1 | tee /tmp/dbsage_sql_output.txt
  fi
fi

EXIT_CODE=$?

6. Check Results

Display execution summary:

echo ""
echo "=========================================="
echo "EXECUTION SUMMARY"
echo "=========================================="

if [ $EXIT_CODE -eq 0 ]; then
  echo "✅ SUCCESS"
else
  echo "❌ FAILED (Exit code: $EXIT_CODE)"
  echo ""
  echo "Error output saved to: /tmp/dbsage_sql_output.txt"
  exit $EXIT_CODE
fi

# Count affected rows (if available in output)
ROWS_AFFECTED=$(grep -oP 'INSERT 0 \K\d+|UPDATE \K\d+|DELETE \K\d+' /tmp/dbsage_sql_output.txt | head -1)
if [ -n "$ROWS_AFFECTED" ]; then
  echo "Rows affected: $ROWS_AFFECTED"
fi

# Execution time (if using \timing in psql)
EXEC_TIME=$(grep -oP 'Time: \K[\d.]+' /tmp/dbsage_sql_output.txt | tail -1)
if [ -n "$EXEC_TIME" ]; then
  echo "Execution time: ${EXEC_TIME}ms"
fi

---

Output

Display final summary:

✅ SQL EXECUTED SUCCESSFULLY

Mode:           {file|inline}
Transaction:    {auto|manual|read}
Rows affected:  {count}
Duration:       {time}ms

Output saved to: /tmp/dbsage_sql_output.txt

Next steps:
- Verify results in database
- Check for expected side effects
- Update application if schema changed

---

Example 1: Run SQL File

run-sql supabase/migrations/20240101_add_users.sql

Example 2: Inline Query

run-sql "SELECT COUNT(*) FROM users WHERE created_at > NOW() - INTERVAL '7 days'"

Example 3: Multi-Line Inline

run-sql "
  UPDATE users
  SET last_login = NOW()
  WHERE id = 'user-123'
  RETURNING *;
"

Example 4: Complex Script

run-sql "
  DO $$
  DECLARE
    user_count INTEGER;
  BEGIN
    SELECT COUNT(*) INTO user_count FROM users;
    RAISE NOTICE 'Total users: %', user_count;
  END $$;
"

---

1. Destructive Operation Detection

Automatically warns for:

• DROP TABLE
• TRUNCATE
• DELETE FROM ... WHERE 1=1
• UPDATE ... WHERE 1=1

2. Transaction Modes

Auto Mode (Recommended):*

• Wraps SQL in BEGIN/COMMIT
• Automatic rollback on error
• Safe for modifications

Manual Mode:*

• For files with own transaction control
• Use when script has multiple transactions
• More control, less safety

Read Mode:*

• Read-only transaction
• Cannot modify data
• Safe for queries/exploration

3. Error Handling

• ON_ERROR_STOP=1 stops on first error
• Transaction rolls back on error (auto mode)
• Full error output preserved

---

Enable Timing

# Add timing to all queries
psql "$SUPABASE_DB_URL" << 'EOF'
\timing on
{your_sql_here}
EOF

Verbose Output

# Show all SQL commands
psql "$SUPABASE_DB_URL" --echo-all -f script.sql

Save Output to File

# Redirect output
psql "$SUPABASE_DB_URL" -f script.sql > output.txt 2>&1

Interactive Mode

# Drop into psql shell
psql "$SUPABASE_DB_URL"

---

1. Query Data

SELECT
  id,
  email,
  created_at
FROM users
WHERE created_at > NOW() - INTERVAL '1 day'
ORDER BY created_at DESC
LIMIT 10;

2. Update Records

UPDATE users
SET
  last_login = NOW(),
  login_count = login_count + 1
WHERE id = 'user-123'
RETURNING *;

3. Bulk Operations

-- Update all inactive users
UPDATE users
SET status = 'archived'
WHERE last_login < NOW() - INTERVAL '1 year'
  AND status = 'active';

4. Data Analysis

-- Aggregation query
SELECT
  DATE_TRUNC('day', created_at) AS day,
  COUNT(*) AS new_users,
  COUNT(*) FILTER (WHERE email_verified) AS verified
FROM users
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY day
ORDER BY day DESC;

---

psql Meta-Commands

Useful commands when in psql interactive mode:

\dt              -- List tables
\d table_name    -- Describe table
\df              -- List functions
\dv              -- List views
\l               -- List databases
\c database      -- Connect to database
\timing on       -- Enable query timing
\x on            -- Expanded display mode
\q               -- Quit
\?               -- Help

---

Error Handling

If execution fails:

1. Check error message in output
2. Review SQL syntax
3. Verify table/column names exist
4. Check permissions
5. For transaction errors, check constraints

Common errors:

• Syntax error:* Review SQL syntax
• Relation does not exist:* Table/view not found
• Column does not exist:* Typo in column name
• Permission denied:* Need appropriate role/permissions

---

Security Notes

• Never* run untrusted SQL
• Always review SQL before executing
• Use read-only mode for untrusted queries
• Be careful with dynamic SQL
• Consider using prepared statements for user input

---

References

• PostgreSQL psql Documentation
• PostgreSQL SQL Commands