Database Query Performance Logger - Hooks

What it does

This is a PostToolUse hook (a bash script) that runs after bash, write, or edit. It does not connect to a database, time queries, or run EXPLAIN — it inspects the file you just touched with grep and prints text reminders. Specifically:

• N+1 heuristic: for files that look like queries, models, repositories, DAOs, or .sql, it greps for loops (for/while/forEach) combined with SELECT/query/find and prints a "possible N+1" reminder to use a JOIN or eager loading
• SQL anti-pattern hints (grep-based): flags SELECT *, flags SELECT without a LIMIT/TOP, and notes when WHERE clauses are present so you remember to index those columns
• Enable-logging reminders: if it finds postgresql.conf, my.cnf, or a package.json referencing Prisma/Sequelize/TypeORM, it prints how to turn on that stack's native query logging (PostgreSQL log_min_duration_statement, MySQL slow_query_log, or the ORM's logging option)
• Best-practices checklist: prints a short static list (indexing, eager loading, EXPLAIN/ANALYZE, connection pooling) as guidance — these are reminders, not measurements
• Local log: appends the analyzed file path, and any psql/mysql/sqlite command it sees, to .claude/logs/query-performance.log

Use Cases

• Catching easy-to-miss query anti-patterns (N+1 loops, SELECT *, missing LIMIT) as you write data-access code
• Reminding you to enable native query logging for your database or ORM during development
• Keeping a local record of which query files you edited and which database commands you ran

Installation

1. Create hooks directory: mkdir -p .claude/hooks
2. Create hook file: touch .claude/hooks/database-query-performance-logger.sh
3. Make executable: chmod +x .claude/hooks/database-query-performance-logger.sh
4. Add configuration from Hook Configuration section above to .claude/settings.json or ~/.claude/settings.json
5. Alternative: Use the interactive /hooks command in Claude Code

Config paths

• Local (not committed): .claude/settings.local.json
• User settings (global): ~/.claude/settings.json
• Project-wide (committed): .claude/settings.json

Requirements

• Claude Code CLI installed
• Bash shell available
• jq command-line JSON processor (the script parses the hook's JSON stdin with jq)
• A project using PostgreSQL, MySQL, SQLite, Prisma, Sequelize, or TypeORM (the script detects these by file presence — no specific version, extension, or database connection is required, since it never queries the database itself)

Hook Configuration

{
  "hooks": {
    "postToolUse": {
      "script": "./.claude/hooks/database-query-performance-logger.sh",
      "matchers": ["bash", "write", "edit"]
    }
  }
}

Hook Script

#!/usr/bin/env bash

# Read the tool input from stdin
INPUT=$(cat)
TOOL_NAME=$(echo "$INPUT" | jq -r '.tool_name')
FILE_PATH=$(echo "$INPUT" | jq -r '.tool_input.file_path // .tool_input.path // ""')
COMMAND=$(echo "$INPUT" | jq -r '.tool_input.command // ""')

# Configuration
SLOW_QUERY_THRESHOLD_MS=${SLOW_QUERY_THRESHOLD_MS:-1000}
LOG_FILE=".claude/logs/query-performance.log"

# Create log directory if it doesn't exist
mkdir -p "$(dirname "$LOG_FILE")"

# Function to check for query files
check_query_file() {
  local file=$1

  if [ -z "$file" ]; then
    return 1
  fi

  # Check if file contains SQL or database queries
  if [[ "$file" == *.sql ]] || \
     [[ "$file" == *query* ]] || \
     [[ "$file" == *model* ]] || \
     [[ "$file" == *repository* ]] || \
     [[ "$file" == *dao* ]]; then
    return 0
  fi

  return 1
}

# Function to analyze query patterns
analyze_query_patterns() {
  local file=$1

  echo "🔍 Analyzing query patterns in: $file" >&2

  if [ ! -f "$file" ]; then
    return
  fi

  # Check for N+1 query patterns (loops with queries)
  if grep -n "for\|while\|forEach" "$file" | head -5 | grep -q .; then
    if grep -i "SELECT\|query\|find" "$file" >/dev/null 2>&1; then
      echo "⚠️ Potential N+1 query pattern detected" >&2
      echo "💡 Consider using JOIN or eager loading instead of queries in loops" >&2
    fi
  fi

  # Check for SELECT * patterns
  if grep -i "SELECT \*" "$file" >/dev/null 2>&1; then
    echo "⚠️ SELECT * detected - consider specifying columns explicitly" >&2
    echo "💡 Reduces data transfer and improves performance" >&2
  fi

  # Check for missing LIMIT clauses
  if grep -i "SELECT" "$file" | grep -iv "LIMIT\|TOP" >/dev/null 2>&1; then
    echo "💡 Consider adding LIMIT clauses to prevent unbounded result sets" >&2
  fi

  # Check for unindexed WHERE clauses
  if grep -i "WHERE" "$file" >/dev/null 2>&1; then
    echo "📊 WHERE clauses detected - ensure columns are indexed" >&2
  fi

  # Log analysis timestamp
  echo "[$(date -u +"%Y-%m-%dT%H:%M:%SZ")] Analyzed: $file" >> "$LOG_FILE"
}

# Function to check for slow query logs
check_slow_query_logs() {
  echo "📈 Checking for slow query logs..." >&2

  # PostgreSQL slow query log
  if [ -f "postgresql.conf" ] || [ -f "pg_log/postgresql.log" ]; then
    echo "🐘 PostgreSQL detected" >&2
    echo "💡 Enable slow query logging: log_min_duration_statement = $SLOW_QUERY_THRESHOLD_MS" >&2
  fi

  # MySQL slow query log
  if [ -f "my.cnf" ] || [ -f "/etc/mysql/my.cnf" ]; then
    echo "🐬 MySQL detected" >&2
    echo "💡 Enable slow query log: slow_query_log = 1" >&2
  fi

  # Check for ORM query logging
  if [ -f "package.json" ]; then
    if grep -q "sequelize\|typeorm\|prisma" package.json 2>/dev/null; then
      echo "📦 ORM detected - query logging available" >&2
      echo "💡 Enable logging in ORM configuration for query performance insights" >&2
    fi
  fi
}

# Main execution
if check_query_file "$FILE_PATH"; then
  echo "🗃️ Database query file detected: $FILE_PATH" >&2
  analyze_query_patterns "$FILE_PATH"
  check_slow_query_logs

  # Performance tips
  echo "" >&2
  echo "🎯 Query Performance Best Practices:" >&2
  echo "   • Use indexes on frequently queried columns" >&2
  echo "   • Avoid N+1 queries with eager loading" >&2
  echo "   • Use EXPLAIN/ANALYZE to understand query plans" >&2
  echo "   • Monitor slow queries > ${SLOW_QUERY_THRESHOLD_MS}ms" >&2
  echo "   • Use connection pooling for better resource management" >&2

elif [[ "$COMMAND" == *"psql"* ]] || [[ "$COMMAND" == *"mysql"* ]] || [[ "$COMMAND" == *"sqlite"* ]]; then
  echo "🗃️ Database command detected" >&2
  echo "⏱️ Query execution started at: $(date)" >&2
  echo "[$(date -u +"%Y-%m-%dT%H:%M:%SZ")] Database command: $COMMAND" >> "$LOG_FILE"
fi

exit 0

Examples

Database Query Performance Logger Hook Script

Complete hook script that detects query files and analyzes performance patterns

#!/usr/bin/env bash
INPUT=$(cat)
TOOL_NAME=$(echo "$INPUT" | jq -r '.tool_name')
FILE_PATH=$(echo "$INPUT" | jq -r '.tool_input.file_path // .tool_input.path // ""')
SLOW_QUERY_THRESHOLD_MS=${SLOW_QUERY_THRESHOLD_MS:-1000}
LOG_FILE=".claude/logs/query-performance.log"
mkdir -p "$(dirname "$LOG_FILE")"
if [[ "$FILE_PATH" == *.sql ]] || [[ "$FILE_PATH" == *query* ]] || [[ "$FILE_PATH" == *model* ]]; then
  echo "Database query file detected: $FILE_PATH" >&2
  if grep -n "for\|while\|forEach" "$FILE_PATH" | head -5 | grep -q .; then
    if grep -i "SELECT\|query\|find" "$FILE_PATH" >/dev/null 2>&1; then
      echo "Potential N+1 query pattern detected" >&2
      echo "Consider using JOIN or eager loading instead of queries in loops" >&2
    fi
  fi
  if grep -i "SELECT \\*" "$FILE_PATH" >/dev/null 2>&1; then
    echo "SELECT * detected - consider specifying columns explicitly" >&2
  fi
  echo "[$(date -u +"%Y-%m-%dT%H:%M:%SZ")] Analyzed: $FILE_PATH" >> "$LOG_FILE"
fi
exit 0

Hook Configuration

Complete hook configuration for .claude/settings.json to enable query performance logging on file write/edit

{
  "hooks": {
    "postToolUse": {
      "script": "./.claude/hooks/database-query-performance-logger.sh",
      "matchers": ["bash", "write", "edit"]
    }
  }
}

SQL Query Analysis

Enhanced hook script that analyzes SQL queries for missing LIMIT clauses and unindexed WHERE clauses

#!/usr/bin/env bash
INPUT=$(cat)
FILE_PATH=$(echo "$INPUT" | jq -r '.tool_input.file_path // .tool_input.path // ""')
if [[ "$FILE_PATH" == *.sql ]]; then
  if grep -i "SELECT" "$FILE_PATH" | grep -iv "LIMIT\|TOP" >/dev/null 2>&1; then
    echo "Consider adding LIMIT clauses to prevent unbounded result sets" >&2
  fi
  if grep -i "WHERE" "$FILE_PATH" >/dev/null 2>&1; then
    echo "WHERE clauses detected - ensure columns are indexed" >&2
  fi
fi
exit 0

ORM Query Logging Detection

Enhanced hook script that detects ORM frameworks and suggests query logging configuration

#!/usr/bin/env bash
INPUT=$(cat)
FILE_PATH=$(echo "$INPUT" | jq -r '.tool_input.file_path // .tool_input.path // ""')
if [ -f "package.json" ]; then
  if grep -q "prisma" package.json 2>/dev/null; then
    echo "Prisma detected - enable query logging in PrismaClient configuration" >&2
    echo "log: [{ emit: 'stdout', level: 'query' }]" >&2
  elif grep -q "sequelize" package.json 2>/dev/null; then
    echo "Sequelize detected - enable query logging in Sequelize configuration" >&2
    echo "logging: console.log" >&2
  elif grep -q "typeorm" package.json 2>/dev/null; then
    echo "TypeORM detected - enable query logging in DataSource configuration" >&2
    echo "logging: ['query', 'error']" >&2
  fi
fi
exit 0

PostgreSQL Slow Query Configuration

Enhanced hook script that suggests PostgreSQL slow query logging and pg_stat_statements configuration

#!/usr/bin/env bash
SLOW_QUERY_THRESHOLD_MS=${SLOW_QUERY_THRESHOLD_MS:-1000}
if [ -f "postgresql.conf" ] || [ -f "pg_log/postgresql.log" ]; then
  echo "PostgreSQL detected" >&2
  echo "Enable slow query logging: log_min_duration_statement = $SLOW_QUERY_THRESHOLD_MS" >&2
  echo "Enable pg_stat_statements: shared_preload_libraries = 'pg_stat_statements'" >&2
fi
exit 0

Troubleshooting

Hook triggers on every file but query analysis shows nothing

Verify file path matching patterns in check_query_file. Add specific matchers for your ORM/query files. Check grep patterns match your SQL syntax (PostgreSQL vs MySQL syntax differences). Verify file extensions match your project structure.

N+1 detection gives false positives on batch operations

Hook flags loops with queries regardless of batching. Add @performance-safe comments to suppress warnings. Refine regex to detect batch/eager loading keywords like includes(), with(), or join(). Consider adding batch operation detection patterns.

Slow query threshold environment variable not respected

Export SLOW_QUERY_THRESHOLD_MS before hook runs. Check bash environment inheritance from shell config. Set in .clauderc: export SLOW_QUERY_THRESHOLD_MS=500 for global override. Verify environment variable is accessible in hook execution context.

Query log file grows too large in active development

Implement log rotation: mv query-performance.log query-performance.$(date +%Y%m%d).log periodically. Use logrotate or cleanup hook. Add log size check with truncation at 10MB threshold. Consider log retention policies.

ORM query logging suggestions appear but feature is enabled

Hook checks package.json presence, not active config. Suppress by adding ORM_LOGGING_ENABLED=true env var. Update hook to detect active logging from config files (ormconfig.json, database.yml). Verify ORM configuration files are accessible.

PostgreSQL pg_stat_statements not detected

Verify pg_stat_statements extension is installed: CREATE EXTENSION pg_stat_statements. Check postgresql.conf includes shared_preload_libraries = pg_stat_statements. Restart PostgreSQL after configuration changes. Verify PostgreSQL version 17+ compatibility.

Prisma query logging not working despite configuration

Verify Prisma 5.x compatibility. Check PrismaClient log configuration includes query level. Ensure log output is visible in development environment. Test with simple query to verify logging is active.

TypeORM query logging shows no output

Verify TypeORM 0.3.x compatibility. Check DataSource logging configuration includes query level. Ensure logging is enabled in development environment. Verify DataSource is properly initialized with logging options.

• Source repositorygithub.com 2026-07-18T07:31:52+00:00
• Documentationcode.claude.com