flask-querymonitor 1.0.3

Detect and eliminate N+1 queries in Flask-SQLAlchemy

Flask-QueryMonitor

Detect and eliminate N+1 queries in your Flask-SQLAlchemy application - Catch performance problems before they hit production with automatic query monitoring and detailed logging.

Why Flask-QueryMonitor?

N+1 queries are one of the most common performance killers in web applications. This package helps you:

• ✅ Detect N+1 queries automatically during development
• ✅ Monitor slow queries with configurable thresholds
• ✅ Track query counts per request with response headers
• ✅ Zero performance impact in production (when disabled)
• ✅ Battle-tested at wallmarkets

The N+1 Problem

# ❌ BAD: N+1 query problem (1 + N queries)
products = Product.query.all()  # 1 query
for product in products:
    print(product.category.name)  # N queries (one per product!)

# ✅ GOOD: Eager loading (1 query)
products = Product.query.options(joinedload(Product.category)).all()
for product in products:
    print(product.category.name)  # No additional queries!

Flask-QueryMonitor automatically detects the first pattern and warns you!

Features

🔍 Automatic N+1 Detection

Warns when a single request executes too many queries (default: >20)

⏱️ Slow Query Logging

Logs queries that exceed your performance threshold (default: 100ms)

📊 Request-Level Metrics

Adds X-Query-Count and X-Query-Time-Ms headers to every response

🎯 SQLAlchemy Integration

Uses SQLAlchemy event listeners - works with any Flask-SQLAlchemy app

🔧 Configurable Thresholds

Customize what counts as "too many" or "too slow"

Installation

pip install flask-querymonitor

Quick Start

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_querymonitor import QueryMonitor

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app.db'
app.config['QUERY_MONITORING_ENABLED'] = True
app.config['SLOW_QUERY_THRESHOLD_MS'] = 100

db = SQLAlchemy(app)
monitor = QueryMonitor(app)

# That's it! Query monitoring is now active

Usage Examples

Basic Monitoring

from flask import Flask
from flask_querymonitor import QueryMonitor

app = Flask(__name__)
monitor = QueryMonitor(app)

@app.route('/products')
def list_products():
    products = Product.query.all()
    # If this triggers N+1 queries, you'll see a warning!
    return render_template('products.html', products=products)

What You'll See in Logs

N+1 Query Detection

WARNING: HIGH QUERY COUNT: GET /products - 47 queries in 823ms - Potential N+1!
  
Hint: Check for lazy-loaded relationships. Consider using joinedload() or selectinload().

Slow Query Detection

WARNING: SLOW QUERY (234ms): 
SELECT products.id, products.name, products.price 
FROM products 
WHERE products.category_id = 5 
ORDER BY products.created_at DESC
LIMIT 100

Location: /app/routes/products.py:45 in list_products()

Response Headers (Debug Mode)

HTTP/1.1 200 OK
X-Query-Count: 12
X-Query-Time-Ms: 145.23
Content-Type: text/html

Real-World Examples

Example 1: Detecting N+1 in Product Listings

# ❌ This will trigger a warning
@app.route('/products')
def list_products():
    products = Product.query.all()  # 1 query
    return render_template('products.html', products=products)

# Template accesses product.category.name for each product
# Result: 1 + N queries!
# QueryMonitor logs: "WARNING: HIGH QUERY COUNT: 47 queries"

Fix:

# ✅ Fixed with eager loading
@app.route('/products')
def list_products():
    products = Product.query.options(
        joinedload(Product.category),
        joinedload(Product.images)
    ).all()  # 1 query with JOINs
    return render_template('products.html', products=products)

# Result: 1 query total!
# QueryMonitor logs: "INFO: GET /products - 1 query in 45ms"

Example 2: Finding Slow Queries

@app.route('/analytics/dashboard')
def analytics_dashboard():
    # This query is slow!
    stats = db.session.execute("""
        SELECT 
            DATE(created_at) as date,
            COUNT(*) as count,
            SUM(total) as revenue
        FROM orders
        WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 YEAR)
        GROUP BY DATE(created_at)
    """).fetchall()
    
    return render_template('dashboard.html', stats=stats)

# QueryMonitor logs:
# "WARNING: SLOW QUERY (1247ms): SELECT DATE(created_at)..."

Fix:

# Add an index on created_at
# Or use a materialized view
# Or cache the results

Example 3: API Endpoint Optimization

@app.route('/api/users/<int:user_id>/orders')
def get_user_orders(user_id):
    user = User.query.get(user_id)  # 1 query
    
    # ❌ N+1: Each order loads its items separately
    orders = user.orders  # Lazy load triggers N queries
    
    return jsonify([{
        'id': order.id,
        'total': order.total,
        'items': [item.product.name for item in order.items]  # More N+1!
    } for order in orders])

# QueryMonitor: "WARNING: 156 queries in 2.3 seconds!"

Fix:

@app.route('/api/users/<int:user_id>/orders')
def get_user_orders(user_id):
    # ✅ Eager load everything in one query
    orders = Order.query.filter_by(user_id=user_id).options(
        joinedload(Order.items).joinedload(OrderItem.product)
    ).all()
    
    return jsonify([{
        'id': order.id,
        'total': order.total,
        'items': [item.product.name for item in order.items]
    } for order in orders])

# QueryMonitor: "INFO: 1 query in 89ms"

Configuration

# Enable/disable monitoring (disable in production for performance)
app.config['QUERY_MONITORING_ENABLED'] = True

# Threshold for "slow query" warnings (milliseconds)
app.config['SLOW_QUERY_THRESHOLD_MS'] = 100

# Threshold for "too many queries" warnings
app.config['HIGH_QUERY_COUNT_THRESHOLD'] = 20

# Add query metrics to response headers (debug mode only)
app.config['QUERY_HEADERS_ENABLED'] = True

# Log query details (SQL, parameters, stack trace)
app.config['QUERY_LOGGING_VERBOSE'] = False

Environment-Based Configuration

import os

if os.getenv('FLASK_ENV') == 'development':
    app.config['QUERY_MONITORING_ENABLED'] = True
    app.config['QUERY_HEADERS_ENABLED'] = True
    app.config['QUERY_LOGGING_VERBOSE'] = True
else:
    # Disable in production for performance
    app.config['QUERY_MONITORING_ENABLED'] = False

How It Works

SQLAlchemy Event Listeners

Flask-QueryMonitor uses SQLAlchemy's event system to track queries:

1. before_cursor_execute - Records query start time
2. after_cursor_execute - Calculates query duration
3. Request context - Aggregates queries per request
4. Response - Adds headers and logs warnings

Performance Impact

• Development: Negligible (<1ms overhead per query)
• Production: Zero (when disabled)
• Memory: ~200 bytes per query tracked

Best Practices

1. Use in Development, Not Production

# ✅ Good
if app.debug:
    QueryMonitor(app)

# ❌ Bad - adds overhead in production
QueryMonitor(app)  # Always enabled

2. Fix N+1 Queries with Eager Loading

# Use joinedload for one-to-one and many-to-one
products = Product.query.options(joinedload(Product.category)).all()

# Use selectinload for one-to-many and many-to-many
users = User.query.options(selectinload(User.orders)).all()

3. Add Database Indexes

# If you see slow queries on WHERE clauses, add indexes
class Product(db.Model):
    __tablename__ = 'products'
    
    id = db.Column(db.Integer, primary_key=True)
    category_id = db.Column(db.Integer, db.ForeignKey('categories.id'), index=True)
    created_at = db.Column(db.DateTime, index=True)

4. Use Query Result Caching

from flask_caching import Cache

cache = Cache(app)

@app.route('/products')
@cache.cached(timeout=300)
def list_products():
    # Expensive query, but cached for 5 minutes
    products = Product.query.options(joinedload(Product.category)).all()
    return render_template('products.html', products=products)

Testing

import pytest
from flask_querymonitor import QueryMonitor

def test_query_monitoring(app, db):
    monitor = QueryMonitor(app)
    
    with app.test_client() as client:
        response = client.get('/products')
        
        # Check headers
        assert 'X-Query-Count' in response.headers
        query_count = int(response.headers['X-Query-Count'])
        
        # Ensure we're not doing N+1
        assert query_count < 5, f"Too many queries: {query_count}"

Production Usage

This package is used in production at:

• wallmarkets - Multi-vendor marketplace
• Reduced average query count from 47 to 3 per request
• Improved page load times by 60%
• Caught dozens of N+1 queries during development

Troubleshooting

"No queries are being logged"

Make sure:

1. QUERY_MONITORING_ENABLED = True
2. You're using Flask-SQLAlchemy
3. Queries are actually being executed

"Too many false positives"

Adjust thresholds:

app.config['HIGH_QUERY_COUNT_THRESHOLD'] = 50  # Increase threshold
app.config['SLOW_QUERY_THRESHOLD_MS'] = 200    # Only log very slow queries

"Performance impact in production"

Disable monitoring:

app.config['QUERY_MONITORING_ENABLED'] = False

Contributing

Contributions welcome! Please:

1. Fork the repository
2. Create a feature branch
3. Add tests for new functionality
4. Submit a pull request

License

MIT License - see LICENSE file for details

Support

• 📚 Documentation
• 🐛 Issue Tracker
• 💬 Discussions

Related Packages

• flask-supercache - Caching to reduce query load
• flask-ratelimit-simple - Rate limiting
• flask-security-headers - Security utilities

---

Made with ❤️ by the wallmarkets Team