Menu
Execute SQL file or inline SQL with transaction safety and timing
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
Based on SOC occupation classification
Execute story development with selectable automation modes to accommodate different developer preferences, skill levels, and story complexity.
Set up a Kord-aligned documentation baseline (no legacy frameworks)
Create Next Story Task methodology and workflow
Validate Next Story Task methodology and workflow
advanced-elicitation methodology and workflow
No checklists needed - this task facilitates brainstorming sessions, validation is through user interaction methodology and workflow
| name | db-run-sql |
| description | Execute SQL file or inline SQL with transaction safety and timing |
| agent | architect |
| subtask | false |
Execute SQL file or inline SQL with transaction safety and timing
Parameter:* mode (optional, default: interactive)
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"
Strategy:* retry
Common Errors:*
Error:* Connection Failed
Error:* Query Syntax Error
Error:* Transaction Rollback
sql (string): Either a file path or inline SQL statementCheck 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
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 "=========================================="
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
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):
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=$?
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
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
run-sql supabase/migrations/20240101_add_users.sql
run-sql "SELECT COUNT(*) FROM users WHERE created_at > NOW() - INTERVAL '7 days'"
run-sql "
UPDATE users
SET last_login = NOW()
WHERE id = 'user-123'
RETURNING *;
"
run-sql "
DO $$
DECLARE
user_count INTEGER;
BEGIN
SELECT COUNT(*) INTO user_count FROM users;
RAISE NOTICE 'Total users: %', user_count;
END $$;
"
Automatically warns for:
DROP TABLETRUNCATEDELETE FROM ... WHERE 1=1UPDATE ... WHERE 1=1Auto Mode (Recommended):*
Manual Mode:*
Read Mode:*
ON_ERROR_STOP=1 stops on first error# Add timing to all queries
psql "$SUPABASE_DB_URL" << 'EOF'
\timing on
{your_sql_here}
EOF
# Show all SQL commands
psql "$SUPABASE_DB_URL" --echo-all -f script.sql
# Redirect output
psql "$SUPABASE_DB_URL" -f script.sql > output.txt 2>&1
# Drop into psql shell
psql "$SUPABASE_DB_URL"
SELECT
id,
email,
created_at
FROM users
WHERE created_at > NOW() - INTERVAL '1 day'
ORDER BY created_at DESC
LIMIT 10;
UPDATE users
SET
last_login = NOW(),
login_count = login_count + 1
WHERE id = 'user-123'
RETURNING *;
-- Update all inactive users
UPDATE users
SET status = 'archived'
WHERE last_login < NOW() - INTERVAL '1 year'
AND status = 'active';
-- 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;
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
If execution fails:
Common errors: