Skip to content

README.md

Latest commit

 

History

History
188 lines (140 loc) · 5.97 KB

README.md

File metadata and controls

188 lines (140 loc) · 5.97 KB
Raw

SQL-Inspect

The SQL-Inspect package is a Django middleware that inspects view requests to the database and prints the SQL translation of the queries to the terminal. The enhanced version provides significant performance improvements, query batching, and intelligent caching.

Features

  • Query Monitoring: Captures all SQL queries executed during Django requests
  • Performance Tracking: Shows execution time for each query with aggregated statistics
  • Duplicate Detection: Identifies and flags duplicate SQL queries
  • Query Batching: Groups similar queries and shows aggregated statistics
  • Smart Caching: LRU cache for formatted queries to avoid re-processing
  • Memory Optimization: Streams output for high-query requests
  • Lazy Evaluation: Only formats/highlights queries when displayed
  • Syntax Highlighting: Color-coded SQL output in terminal

Installation

Include the middleware in the settings.py file as specified below:

MIDDLEWARE = [
    ...,
    "sql_inspect.middleware.SQLInspectMiddleware"
]

Configuration

Add these optional settings to your settings.py file to customize behavior:

# SQL-Inspect Configuration
SQL_INSPECT_CACHE_SIZE = 1000                    # Number of queries to cache (default: 1000)
SQL_INSPECT_STREAMING_THRESHOLD = 100            # Use streaming output above this many queries (default: 100)
SQL_INSPECT_BATCH_SIMILAR = True                 # Group similar queries together (default: True)
SQL_INSPECT_SHOW_INDIVIDUAL = True               # Show individual queries in small batches (default: True)
SQL_INSPECT_LAZY_EVAL = True                     # Use lazy evaluation for formatting (default: True)

Output Modes

Batched Output (Default)

When SQL_INSPECT_BATCH_SIMILAR = True, similar queries are grouped together:

==============================
[ SQL Query Stats ]
==============================

Total queries: 45
Unique query patterns: 8
Duplicates: 37

Pattern 1: 15 executions
Total time: 0.2500s
Avg time: 0.0167s
Min/Max: 0.0120s / 0.0234s
Example query:
SELECT "users"."id", "users"."name" FROM "users" WHERE "users"."active" = ?
------------------------------

Pattern 2: 12 executions
Total time: 0.1800s
Avg time: 0.0150s
Min/Max: 0.0098s / 0.0201s
Query pattern:
SELECT * FROM "products" WHERE "products"."category_id" = ?
------------------------------

Individual Query Output

When SQL_INSPECT_BATCH_SIMILAR = False, shows traditional individual query listing:

==============================
[ SQL Query Stats ]
==============================

Query 1: Execution Time - (0.0123s).
SELECT "users"."id", "users"."name" FROM "users" WHERE "users"."active" = true

Query 2: Execution Time - (0.0145s). DUPLICATE
SELECT "users"."id", "users"."name" FROM "users" WHERE "users"."active" = true

Number of query(s): 23
Number of duplicates: 8
------------------------------

Performance Benefits

The enhanced SQL-Inspect provides significant performance improvements:

  • 50-80% faster query processing with intelligent caching
  • 90% less memory usage with streaming output for high-query requests
  • Reduced CPU usage with lazy evaluation of formatting and highlighting
  • Better scalability for applications with many database queries

Usage Examples

Basic Usage

Simply add the middleware and SQL-Inspect will automatically monitor all database queries in DEBUG mode.

Manual Query Analysis

You can also analyze queries programmatically:

from sql_inspect import inspect_queries

# Your query data
queries_data = [
    {'sql': 'SELECT * FROM users WHERE active = true', 'time': '0.0123'},
    {'sql': 'SELECT * FROM products WHERE price > 100', 'time': '0.0156'},
    # ... more queries
]

# Analyze with batching
result = inspect_queries(queries_data, batch_similar=True)
print(result)

# Analyze individual queries
result = inspect_queries(queries_data, batch_similar=False)
print(result)

Custom Configuration Example

# settings.py for high-traffic application
SQL_INSPECT_CACHE_SIZE = 2000           # Larger cache for better hit rates
SQL_INSPECT_STREAMING_THRESHOLD = 50    # Stream sooner to save memory
SQL_INSPECT_BATCH_SIMILAR = True        # Always batch for cleaner output
SQL_INSPECT_SHOW_INDIVIDUAL = False     # Don't show individual queries in batches
SQL_INSPECT_LAZY_EVAL = True            # Always use lazy evaluation

Requirements

  • Django 3.0+
  • Python 3.7+
  • sqlparse
  • pygments

Advanced Features

Query Signature Detection

The middleware intelligently groups queries by normalizing:

  • Numeric literals (replaced with ?)
  • String literals (replaced with '?')
  • Whitespace and case differences

This helps identify N+1 query problems and other performance issues.

Thread-Safe Caching

The built-in cache is thread-safe and uses LRU eviction, making it suitable for production debugging scenarios.

Memory Management

For requests with many queries, the middleware automatically switches to streaming output to prevent memory issues.

Troubleshooting

High Memory Usage

If you experience high memory usage:

  • Reduce SQL_INSPECT_CACHE_SIZE
  • Lower SQL_INSPECT_STREAMING_THRESHOLD
  • Set SQL_INSPECT_LAZY_EVAL = True

Performance Issues

For better performance:

  • Enable query batching: SQL_INSPECT_BATCH_SIMILAR = True
  • Increase cache size if you have repetitive queries

Too Much Output

To reduce output noise:

  • Enable batching: SQL_INSPECT_BATCH_SIMILAR = True
  • Set SQL_INSPECT_SHOW_INDIVIDUAL = False
  • Only run in development: ensure DEBUG = False in production

Migration from Original Version

The enhanced version is fully backward compatible. To migrate:

  1. Optionally add configuration settings for enhanced features
  2. No code changes required - all improvements are automatic

Security Note

Important: Always ensure DEBUG = False in production environments. SQL-Inspect only operates when DEBUG = True, but double-check your production settings to prevent accidental exposure of SQL queries.