Per-Request Query Caching

CQL includes a powerful per-request query caching system that automatically caches SQL query results during a single request. This eliminates duplicate database queries within the same request while ensuring cache isolation between requests.

Key Features

Automatic SQL deduplication: The same SQL query with identical parameters will hit the cache instead of the database if executed again during the same request
Per-request lifecycle: Cache is automatically cleared at the start of each new request
Thread-safe: Safe to use in multi-threaded applications
Zero configuration: Just add middleware to your web framework
Memory efficient: Configurable cache size with automatic eviction
Framework agnostic: Works with all popular Crystal web frameworks

How It Works

The caching system works at the SQL level by:

Generating a unique cache key from the SQL query and parameters
Checking the cache before executing any database query
Storing results in memory after the first execution
Returning cached results for subsequent identical queries
Clearing the cache at the end of each request

Quick Start

1. Enable in Web Framework

Kemal

require "cql/cache/middleware"

# Enable per-request query caching
before_all do |env|
  CQL::Cache::Middleware::Kemal.before_request(env)
end

after_all do |env|
  CQL::Cache::Middleware::Kemal.after_request(env)
end

Lucky

require "cql/cache/middleware"

# In src/actions/application_action.cr
abstract class ApplicationAction < Lucky::Action
  include CQL::Cache::Middleware::Lucky
end

Amber

require "cql/cache/middleware"

# Create a pipe in src/pipes/query_cache_pipe.cr
class QueryCachePipe < Amber::Pipe::Base
  include CQL::Cache::Middleware::Amber
end

# In config/routes.cr
pipeline :web, [QueryCachePipe]

Azu

require "cql/cache/middleware"

# Option 1: Using HTTP Handler
server = HTTP::Server.new([
  CQL::Cache::Middleware::Azu::Handler.new,
  YourAzuApp.new
])

# Option 2: In Controllers
class ApplicationController < Azu::Controller
  include CQL::Cache::Middleware::Azu::Controller
end

# Option 3: Manual hooks
app = Azu::Application.new
app.before { |ctx| CQL::Cache::Middleware::Azu.before_request(ctx) }
app.after { |ctx| CQL::Cache::Middleware::Azu.after_request(ctx) }

# Option 4: Convenience setup
CQL::Cache::Middleware::Azu.setup!(app)

Generic HTTP::Server

require "cql/cache/middleware"

server = HTTP::Server.new([
  CQL::Cache::Middleware::HTTP.new,
  # ... other handlers
])

2. Manual Integration

For custom frameworks or manual control:

require "cql/cache/middleware"

# At the start of each request
CQL::Cache::Middleware::Manual.start_request("optional-request-id")

# Your application logic with CQL queries here
user = User.find(1)
posts = user.posts.where(published: true).all
# If this query runs again in the same request, it hits the cache

# At the end of each request
CQL::Cache::Middleware::Manual.end_request

Example Usage

# Start a new request context
CQL::Cache::Middleware::Manual.start_request

# First execution - hits database
users = User.where(active: true).all
puts "First query executed"

# Second execution - hits cache (same SQL)
users_cached = User.where(active: true).all
puts "Second query cached"

# Different query - hits database again
inactive_users = User.where(active: false).all
puts "Different query executed"

# Check cache statistics
stats = CQL::Cache::RequestQueryCacheHelper.stats
puts "Cache hits: #{stats["hits"]}"
puts "Cache misses: #{stats["misses"]}"
puts "Hit rate: #{stats["hit_rate_percent"]}%"

# End request - clears cache
CQL::Cache::Middleware::Manual.end_request

Advanced Usage

Block-Based Request Scoping

CQL::Cache::Middleware::Manual.with_request("request-123") do
  # All queries in this block are cached
  user = User.find(1)
  user = User.find(1) # Cache hit

  posts = user.posts.all
  posts = user.posts.all # Cache hit
end
# Cache automatically cleared when block exits

Enable/Disable Caching

# Disable caching globally
CQL::Cache::RequestQueryCacheHelper.enabled = false

# Re-enable caching
CQL::Cache::RequestQueryCacheHelper.enabled = true

# Check if caching is enabled
if CQL::Cache::RequestQueryCacheHelper.enabled?
  puts "Caching is active"
end

Cache Statistics and Monitoring

stats = CQL::Cache::RequestQueryCacheHelper.stats

puts "Cache enabled: #{stats["enabled"]}"
puts "Current request ID: #{stats["request_id"]}"
puts "Cache size: #{stats["size"]}"
puts "Max cache size: #{stats["max_size"]}"
puts "Cache hits: #{stats["hits"]}"
puts "Cache misses: #{stats["misses"]}"
puts "Hit rate: #{stats["hit_rate_percent"]}%"

What Gets Cached

The caching system works with all CQL query methods:

User.all - SELECT queries
User.find(1) - Single record lookups
User.where(active: true).count - Aggregate queries
User.where(name: "John").first - Limited queries
Complex joins and subqueries
Custom SQL via Query#get, Query#all, etc.

Cache Key Generation

Cache keys are generated using:

The complete SQL query string
All query parameters
MD5 hash for consistent, short keys

This ensures that only truly identical queries hit the cache.

Performance Benefits

Typical performance improvements:

2-10x faster for repeated queries
Reduced database load during high traffic
Lower query latency for complex reports
Better scalability with caching layers

Best Practices

1. Use with Read-Heavy Operations

Per-request caching works best for:

Dashboard queries that aggregate data
Navigation menus loaded multiple times
User permissions checked repeatedly
Lookup tables accessed frequently

2. Monitor Cache Statistics

# Add to your monitoring/logging
after_all do |env|
  stats = CQL::Cache::RequestQueryCacheHelper.stats
  if stats["hits"].as(Int64) > 0
    Log.info { "Query cache: #{stats["hits"]} hits, #{stats["misses"]} misses (#{stats["hit_rate_percent"]}% hit rate)" }
  end
  CQL::Cache::Middleware::Kemal.after_request(env)
end

3. Configure Cache Size

# Configure maximum cache entries per request (default: 1000)
cache = CQL::Cache::RequestQueryCache.new(max_size: 500)

Limitations

Per-request only: Cache doesn't persist across requests
Memory storage: Cache is stored in application memory
Read queries only: Only SELECT queries benefit from caching
Identical SQL required: Slight variations in SQL won't hit cache

Thread Safety

The caching system is fully thread-safe and can be used in:

Multi-threaded web servers
Concurrent request handling
Background job processing
Fiber-based concurrency

Troubleshooting

Cache Not Working

Check if enabled: CQL::Cache::RequestQueryCacheHelper.enabled?
Verify middleware setup: Ensure before/after hooks are called
Check SQL consistency: Use cache stats to see hit/miss ratios
Monitor memory usage: Large result sets might impact performance

Debugging Cache Behavior

# Enable detailed logging
Log.setup do |c|
  c.bind "cql.cache", :debug, Log::IOBackend.new
end

# Check what's in cache
stats = CQL::Cache::RequestQueryCacheHelper.stats
puts "Cache size: #{stats["size"]} entries"

Azu Framework Integration

The Azu framework is a Crystal application development toolkit with expressive, elegant syntax. CQL's per-request query caching integrates seamlessly with Azu through multiple approaches:

Handler-Based Integration

require "azu"
require "cql/cache/middleware"

class BlogApp < Azu::Application
  # Enable per-request query caching
  use CQL::Cache::Middleware::Azu::Handler.new

  get "/articles" do |ctx|
    # First query - hits database
    articles = Article.published.includes(:author)

    # If this same query runs again in the same request
    # (e.g., in a partial or helper), it hits the cache
    popular_articles = Article.published.includes(:author)

    render_template "articles/index.html", {
      articles: articles,
      popular: popular_articles
    }
  end

  get "/dashboard" do |ctx|
    # Multiple related queries that might be repeated
    user_count = User.count                    # Database hit
    article_count = Article.count              # Database hit
    published_count = Article.published.count # Database hit

    # If any of these queries run again, they hit the cache
    stats = {
      users: User.count,                       # Cache hit
      articles: Article.count,                 # Cache hit
      published: Article.published.count       # Cache hit
    }

    render_json stats
  end
end

BlogApp.run(port: 3000)

Controller-Based Integration

# Base controller with caching enabled
class ApplicationController < Azu::Controller
  include CQL::Cache::Middleware::Azu::Controller
end

class UsersController < ApplicationController
  def index
    # These queries will be automatically cached per request
    @users = User.where(active: true).all
    @user_count = User.count # Will hit cache if same query

    render "users/index"
  end

  def dashboard
    # Complex queries that benefit from caching
    @active_users = User.where(active: true).includes(:posts)
    @recent_posts = Post.recent.includes(:author)

    # If these queries are repeated (e.g., in partials), they hit cache
    render "dashboard"
  end
end

Manual Hook Integration

app = Azu::Application.new

# Add cache lifecycle hooks
app.before do |context|
  CQL::Cache::Middleware::Azu.before_request(context)
end

app.after do |context|
  CQL::Cache::Middleware::Azu.after_request(context)
end

# Your routes...
app.get "/" do |ctx|
  # All queries in this request will be cached automatically
  users = User.all
  posts = Post.published

  render_template "index.html", {users: users, posts: posts}
end

Integration Examples

See the complete working examples for different frameworks:

examples/per_request_query_cache_demo.cr - General usage patterns
examples/azu_query_cache_demo.cr - Azu-specific integration examples

These examples demonstrate:

Basic usage patterns
Web framework integration
Performance comparisons
Statistics monitoring
Error handling
Real-world application patterns

The per-request query caching system provides significant performance benefits with minimal setup, making it an excellent addition to any CQL-based application, especially those built with the Azu framework.

PreviousCentralized Cache Configuration NextRedis Cache Configuration

Last updated 4 months ago

Was this helpful?