Skip to content

Database Development Patterns

Leverage Cursor IDE and Claude Code for advanced database development. These patterns cover schema design, query optimization, migrations, performance tuning, and database-specific best practices across SQL and NoSQL systems.

  1. Open Cursor and create a new file
  2. Activate Agent mode (Cmd/Ctrl + I)
  3. Prompt: “Design a database schema for e-commerce with:
    • Users, products, orders, inventory
    • Proper relationships and constraints
    • Indexes for common queries
    • Audit fields (created_at, updated_at)
    • Soft delete support”
  4. Agent generates complete schema with explanations
-- Cursor prompt for query optimization:
"Optimize this slow query:
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at > '2024-01-01'
AND u.country = 'USA'
Consider:
- Index usage
- Query execution plan
- Covering indexes
- Partitioning strategies"

AI Query Analysis

Cursor/Claude can:

  • Analyze query execution plans
  • Suggest missing indexes
  • Rewrite queries for performance
  • Identify N+1 problems
  • Generate query benchmarks
-- Prompt: "Create analytics query using window functions for:
-- - Running totals
-- - Ranking within groups
-- - Moving averages
-- - Lag/lead comparisons
-- - Percentile calculations"
-- PostgreSQL-specific prompt:
"Implement PostgreSQL features:
- JSONB columns with indexes
- Full-text search with triggers
- Table partitioning by date
- Foreign data wrappers
- Row-level security
- Materialized views"
  1. Analyze Queries: “Use EXPLAIN ANALYZE to identify bottlenecks”
  2. Index Strategy: “Create partial and expression indexes”
  3. Vacuum Strategy: “Configure autovacuum for large tables”
  4. Connection Pooling: “Set up PgBouncer configuration”
-- MySQL optimization prompt:
"Optimize MySQL database for:
- High write throughput
- Proper character encoding
- InnoDB buffer pool sizing
- Query cache configuration
- Replication setup
- Partition pruning"

Read Replicas

-- Prompt: "Set up read replicas with:
-- - Master-slave configuration
-- - Read/write splitting
-- - Lag monitoring
-- - Failover strategy"

Sharding

-- Prompt: "Implement sharding for:
-- - User-based partitioning
-- - Geographic distribution
-- - Cross-shard queries
-- - Shard rebalancing"
// MongoDB schema design prompt:
"Design MongoDB collections for social media app:
- Denormalized for read performance
- Embedded vs referenced documents
- Compound indexes
- Aggregation pipelines
- Change streams setup
- Sharding strategy"
// Embedding pattern:
"Model one-to-many relationships with:
- Embedded documents for under 100 items
- Atomic updates
- Single query retrieval
- Size limitations considered"
-- Redis implementation prompt:
"Implement Redis caching for:
- Session storage with TTL
- Cache-aside pattern
- Write-through caching
- Pub/sub messaging
- Sorted sets for leaderboards
- HyperLogLog for counting"

Redis Use Cases

AI can implement:

  • Rate limiting with sliding windows
  • Distributed locks with Redlock
  • Real-time analytics
  • Queue implementations
  • Geospatial queries
-- Migration strategy prompt:
"Create migration system with:
- Versioned migrations
- Rollback capabilities
- Zero-downtime deployments
- Data migrations
- Schema validation
- Migration testing"
  1. Planning: “Analyze source and target schemas”
  2. ETL Pipeline: “Create extraction and transformation logic”
  3. Validation: “Implement data integrity checks”
  4. Cutover: “Plan zero-downtime migration strategy”
-- Time-series prompt:
"Design time-series database for IoT:
- Efficient timestamp indexing
- Data retention policies
- Continuous aggregates
- Downsampling strategies
- Compression techniques
- Query optimization"
// Graph database prompt:
"Model social network in Neo4j:
- User nodes and relationships
- Friend recommendations query
- Shortest path algorithms
- Community detection
- Performance optimization
- Cypher query patterns"
-- Database testing prompt:
"Create database tests for:
- Schema validation
- Constraint testing
- Performance benchmarks
- Data integrity checks
- Migration testing
- Backup/restore verification"

Synthetic Data

-- Prompt: "Generate test data:
-- - Realistic distributions
-- - Foreign key relationships
-- - Edge cases
-- - Performance test volumes"

Data Masking

-- Prompt: "Mask production data:
-- - PII anonymization
-- - Consistent masking
-- - Referential integrity
-- - Reversible for testing"
-- Monitoring setup prompt:
"Implement query monitoring:
- Slow query logging
- Query performance metrics
- Index usage statistics
- Lock monitoring
- Connection pool metrics
- Automated alerting"
  1. Metrics Collection: “Set up Prometheus exporters”
  2. Dashboards: “Create Grafana dashboards”
  3. Alerting: “Configure performance alerts”
  4. Analysis: “Implement query plan analysis”
Terminal window
# Backup implementation prompt:
"Create backup strategy with:
- Automated daily backups
- Point-in-time recovery
- Backup testing
- Offsite storage
- Encryption at rest
- Recovery procedures"
-- Security prompt:
"Implement database security:
- Role-based access control
- Row-level security
- Column encryption
- Audit logging
- SQL injection prevention
- Connection encryption"

Security Best Practices

AI helps implement:

  • Least privilege principle
  • Data encryption strategies
  • Compliance requirements
  • Security audit trails
  • Vulnerability scanning
# Multi-database prompt:
"Design polyglot architecture:
- PostgreSQL for transactions
- Redis for caching
- MongoDB for documents
- Elasticsearch for search
- Data synchronization
- Consistency strategies"
-- Structure prompts like:
"Optimize [query/schema] for [use case] considering:
- Current volume: [X] records
- Growth rate: [Y] per month
- Read/write ratio: [Z]
- Consistency requirements
- Performance SLAs"
-- Event sourcing implementation:
"Design event store with:
- Immutable event log
- Aggregate snapshots
- Event replay capability
- Projection updates
- Event versioning
- CQRS integration"
# Proxy configuration:
"Set up database proxy for:
- Connection pooling
- Query routing
- Load balancing
- Failover handling
- Query caching
- Security filtering"