Skip to main content
EVOKORE// BROWSE
>

./browse/prompts

11 NODES
πŸ€–system promptβ€’7 months ago

temporal-python-testing

Test Temporal workflows with pytest, time-skipping, and mocking

coding
⭐1
# Temporal Python Testing Strategies Comprehensive testing approaches for Temporal workflows using pytest, progressive disclosure resources for specific testing scenarios. ## When to Use This Skill - **Unit testing workflows** - Fast tests with time-skipping - **Integration testing** - Workflows with mocked activities - **Replay testing** - Validate determinism against production histories - **Local development** - Set up Temporal server and pytest - **CI/CD integration** - Automated testing pipelines - **Coverage strategies** - Achieve β‰₯80% test coverage ## Testing Philosophy **Recommended Approach** (Source: docs.temporal.io/develop/python/testing-suite): - Write majority as integration tests - Use pytest with async fixtures - Time-skipping enables fast feedback (month-long workflows β†’ seconds) - Mock activities to isolate workflow logic - Validate determinism with replay testing **Three Test Types**: 1. **Unit**: Workflows with time-skipping, activities with ActivityEnvironment 2. **Integration**: Workers with mocked activities 3. **End-to-end**: Full Temporal server with real activities (use sparingly) ## Available Resources This skill provides detailed guidance through progressive disclosure. Load specific resources based on your testing needs: ### Unit Testing Resources **File**: `resources/unit-testing.md` **When to load**: Testing individual workflows or activities in isolation **Contains**: - WorkflowEnvironment with time-skipping - ActivityEnvironment for activity testing - Fast execution of long-running workflows - Manual time advancement patterns - pytest fixtures and patterns ### Integration Testing Resources **File**: `resources/integration-testing.md` **When to load**: Testing workflows with mocked external dependencies **Contains**: - Activity mocking strategies - Error injection patterns - Multi-activity workflow testing - Signal and query testing - Coverage strategies ### Replay Testing Resources **File**: `resources/replay-testing.md` **When to load**: Validating determinism or deploying workflow changes **Contains**: - Determinism validation - Production history replay - CI/CD integration patterns - Version compatibility testing ### Local Development Resources **File**: `resources/local-setup.md` **When to load**: Setting up development environment **Contains**: - Docker Compose configuration - pytest setup and configuration - Coverage tool integration - Development workflow ## Quick Start Guide ### Basic Workflow Test ```python import pytest from temporalio.testing import WorkflowEnvironment from temporalio.worker import Worker @pytest.fixture async def workflow_env(): env = await WorkflowEnvironment.start_time_skipping() yield env await env.shutdown() @pytest.mark.asyncio async def test_workflow(workflow_env): async with Worker( workflow_env.client, task_queue="test-queue", workflows=[YourWorkflow], activities=[your_activity], ): result = await workflow_env.client.execute_workflow( YourWorkflow.run, args, id="test-wf-id", task_queue="test-queue", ) assert result == expected ``` ### Basic Activity Test ```python from temporalio.testing import ActivityEnvironment async def test_activity(): env = ActivityEnvironment() result = await env.run(your_activity, "test-input") assert result == expected_output ``` ## Coverage Targets **Recommended Coverage** (Source: docs.temporal.io best practices): - **Workflows**: β‰₯80% logic coverage - **Activities**: β‰₯80% logic coverage - **Integration**: Critical paths with mocked activities - **Replay**: All workflow versions before deployment ## Key Testing Principles 1. **Time-Skipping** - Month-long workflows test in seconds 2. **Mock Activities** - Isolate workflow logic from external dependencies 3. **Replay Testing** - Validate determinism before deployment 4. **High Coverage** - β‰₯80% target for production workflows 5. **Fast Feedback** - Unit tests run in milliseconds ## How to Use Resources **Load specific resource when needed**: - "Show me unit testing patterns" β†’ Load `resources/unit-testing.md` - "How do I mock activities?" β†’ Load `resources/integration-testing.md` - "Setup local Temporal server" β†’ Load `resources/local-setup.md` - "Validate determinism" β†’ Load `resources/replay-testing.md` ## Additional References - Python SDK Testing: docs.temporal.io/develop/python/testing-suite - Testing Patterns: github.com/temporalio/temporal/blob/main/docs/development/testing.md - Python Samples: github.com/temporalio/samples-python
πŸ‘0
πŸ‘οΈ0
πŸ€– Auto-discovered
πŸ€–system promptβ€’7 months ago

workflow-orchestration-patterns

Design durable workflows with Temporal for distributed systems.

coding
⭐1
# Workflow Orchestration Patterns Master workflow orchestration architecture with Temporal, covering fundamental design decisions, resilience patterns, and best practices for building reliable distributed systems. ## When to Use Workflow Orchestration ### Ideal Use Cases (Source: docs.temporal.io) - **Multi-step processes** spanning machines/services/databases - **Distributed transactions** requiring all-or-nothing semantics - **Long-running workflows** (hours to years) with automatic state persistence - **Failure recovery** that must resume from last successful step - **Business processes**: bookings, orders, campaigns, approvals - **Entity lifecycle management**: inventory tracking, account management, cart workflows - **Infrastructure automation**: CI/CD pipelines, provisioning, deployments - **Human-in-the-loop** systems requiring timeouts and escalations ### When NOT to Use - Simple CRUD operations (use direct API calls) - Pure data processing pipelines (use Airflow, batch processing) - Stateless request/response (use standard APIs) - Real-time streaming (use Kafka, event processors) ## Critical Design Decision: Workflows vs Activities **The Fundamental Rule** (Source: temporal.io/blog/workflow-engine-principles): - **Workflows** = Orchestration logic and decision-making - **Activities** = External interactions (APIs, databases, network calls) ### Workflows (Orchestration) **Characteristics:** - Contain business logic and coordination - **MUST be deterministic** (same inputs β†’ same outputs) - **Cannot** perform direct external calls - State automatically preserved across failures - Can run for years despite infrastructure failures **Example workflow tasks:** - Decide which steps to execute - Handle compensation logic - Manage timeouts and retries - Coordinate child workflows ### Activities (External Interactions) **Characteristics:** - Handle all external system interactions - Can be non-deterministic (API calls, DB writes) - Include built-in timeouts and retry logic - **Must be idempotent** (calling N times = calling once) - Short-lived (seconds to minutes typically) **Example activity tasks:** - Call payment gateway API - Write to database - Send emails or notifications - Query external services ### Design Decision Framework ``` Does it touch external systems? β†’ Activity Is it orchestration/decision logic? β†’ Workflow ``` ## Core Workflow Patterns ### 1. Saga Pattern with Compensation **Purpose**: Implement distributed transactions with rollback capability **Pattern** (Source: temporal.io/blog/compensating-actions-part-of-a-complete-breakfast-with-sagas): ``` For each step: 1. Register compensation BEFORE executing 2. Execute the step (via activity) 3. On failure, run all compensations in reverse order (LIFO) ``` **Example: Payment Workflow** 1. Reserve inventory (compensation: release inventory) 2. Charge payment (compensation: refund payment) 3. Fulfill order (compensation: cancel fulfillment) **Critical Requirements:** - Compensations must be idempotent - Register compensation BEFORE executing step - Run compensations in reverse order - Handle partial failures gracefully ### 2. Entity Workflows (Actor Model) **Purpose**: Long-lived workflow representing single entity instance **Pattern** (Source: docs.temporal.io/evaluate/use-cases-design-patterns): - One workflow execution = one entity (cart, account, inventory item) - Workflow persists for entity lifetime - Receives signals for state changes - Supports queries for current state **Example Use Cases:** - Shopping cart (add items, checkout, expiration) - Bank account (deposits, withdrawals, balance checks) - Product inventory (stock updates, reservations) **Benefits:** - Encapsulates entity behavior - Guarantees consistency per entity - Natural event sourcing ### 3. Fan-Out/Fan-In (Parallel Execution) **Purpose**: Execute multiple tasks in parallel, aggregate results **Pattern:** - Spawn child workflows or parallel activities - Wait for all to complete - Aggregate results - Handle partial failures **Scaling Rule** (Source: temporal.io/blog/workflow-engine-principles): - Don't scale individual workflows - For 1M tasks: spawn 1K child workflows Γ— 1K tasks each - Keep each workflow bounded ### 4. Async Callback Pattern **Purpose**: Wait for external event or human approval **Pattern:** - Workflow sends request and waits for signal - External system processes asynchronously - Sends signal to resume workflow - Workflow continues with response **Use Cases:** - Human approval workflows - Webhook callbacks - Long-running external processes ## State Management and Determinism ### Automatic State Preservation **How Temporal Works** (Source: docs.temporal.io/workflows): - Complete program state preserved automatically - Event History records every command and event - Seamless recovery from crashes - Applications restore pre-failure state ### Determinism Constraints **Workflows Execute as State Machines**: - Replay behavior must be consistent - Same inputs β†’ identical outputs every time **Prohibited in Workflows** (Source: docs.temporal.io/workflows): - ❌ Threading, locks, synchronization primitives - ❌ Random number generation (`random()`) - ❌ Global state or static variables - ❌ System time (`datetime.now()`) - ❌ Direct file I/O or network calls - ❌ Non-deterministic libraries **Allowed in Workflows**: - βœ… `workflow.now()` (deterministic time) - βœ… `workflow.random()` (deterministic random) - βœ… Pure functions and calculations - βœ… Calling activities (non-deterministic operations) ### Versioning Strategies **Challenge**: Changing workflow code while old executions still running **Solutions**: 1. **Versioning API**: Use `workflow.get_version()` for safe changes 2. **New Workflow Type**: Create new workflow, route new executions to it 3. **Backward Compatibility**: Ensure old events replay correctly ## Resilience and Error Handling ### Retry Policies **Default Behavior**: Temporal retries activities forever **Configure Retry**: - Initial retry interval - Backoff coefficient (exponential backoff) - Maximum interval (cap retry delay) - Maximum attempts (eventually fail) **Non-Retryable Errors**: - Invalid input (validation failures) - Business rule violations - Permanent failures (resource not found) ### Idempotency Requirements **Why Critical** (Source: docs.temporal.io/activities): - Activities may execute multiple times - Network failures trigger retries - Duplicate execution must be safe **Implementation Strategies**: - Idempotency keys (deduplication) - Check-then-act with unique constraints - Upsert operations instead of insert - Track processed request IDs ### Activity Heartbeats **Purpose**: Detect stalled long-running activities **Pattern**: - Activity sends periodic heartbeat - Includes progress information - Timeout if no heartbeat received - Enables progress-based retry ## Best Practices ### Workflow Design 1. **Keep workflows focused** - Single responsibility per workflow 2. **Small workflows** - Use child workflows for scalability 3. **Clear boundaries** - Workflow orchestrates, activities execute 4. **Test locally** - Use time-skipping test environment ### Activity Design 1. **Idempotent operations** - Safe to retry 2. **Short-lived** - Seconds to minutes, not hours 3. **Timeout configuration** - Always set timeouts 4. **Heartbeat for long tasks** - Report progress 5. **Error handling** - Distinguish retryable vs non-retryable ### Common Pitfalls **Workflow Violations**: - Using `datetime.now()` instead of `workflow.now()` - Threading or async operations in workflow code - Calling external APIs directly from workflow - Non-deterministic logic in workflows **Activity Mistakes**: - Non-idempotent operations (can't handle retries) - Missing timeouts (activities run forever) - No error classification (retry validation errors) - Ignoring payload limits (2MB per argument) ### Operational Considerations **Monitoring**: - Workflow execution duration - Activity failure rates - Retry attempts and backoff - Pending workflow counts **Scalability**: - Horizontal scaling with workers - Task queue partitioning - Child workflow decomposition - Activity batching when appropriate ## Additional Resources **Official Documentation**: - Temporal Core Concepts: docs.temporal.io/workflows - Workflow Patterns: docs.temporal.io/evaluate/use-cases-design-patterns - Best Practices: docs.temporal.io/develop/best-practices - Saga Pattern: temporal.io/blog/saga-pattern-made-easy **Key Principles**: 1. Workflows = orchestration, Activities = external calls 2. Determinism is non-negotiable for workflows 3. Idempotency is critical for activities 4. State preservation is automatic 5. Design for failure and recovery
πŸ‘0
πŸ‘οΈ0
πŸ€– Auto-discovered
πŸ€–system promptβ€’7 months ago

deployment-pipeline-design

Design multi-stage CI/CD pipelines with approval gates, security

coding
⭐1
# Deployment Pipeline Design Architecture patterns for multi-stage CI/CD pipelines with approval gates and deployment strategies. ## Purpose Design robust, secure deployment pipelines that balance speed with safety through proper stage organization and approval workflows. ## When to Use - Design CI/CD architecture - Implement deployment gates - Configure multi-environment pipelines - Establish deployment best practices - Implement progressive delivery ## Pipeline Stages ### Standard Pipeline Flow ``` β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β” β”Œβ”€β”€β”€β”€β”€β”€β” β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β” β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β” β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β” β”‚ Build β”‚ β†’ β”‚ Test β”‚ β†’ β”‚ Staging β”‚ β†’ β”‚ Approveβ”‚ β†’ β”‚Productionβ”‚ β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜ β””β”€β”€β”€β”€β”€β”€β”˜ β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜ β””β”€β”€β”€β”€β”€β”€β”€β”€β”˜ β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜ ``` ### Detailed Stage Breakdown 1. **Source** - Code checkout 2. **Build** - Compile, package, containerize 3. **Test** - Unit, integration, security scans 4. **Staging Deploy** - Deploy to staging environment 5. **Integration Tests** - E2E, smoke tests 6. **Approval Gate** - Manual approval required 7. **Production Deploy** - Canary, blue-green, rolling 8. **Verification** - Health checks, monitoring 9. **Rollback** - Automated rollback on failure ## Approval Gate Patterns ### Pattern 1: Manual Approval ```yaml # GitHub Actions production-deploy: needs: staging-deploy environment: name: production url: https://app.example.com runs-on: ubuntu-latest steps: - name: Deploy to production run: | # Deployment commands ``` ### Pattern 2: Time-Based Approval ```yaml # GitLab CI deploy:production: stage: deploy script: - deploy.sh production environment: name: production when: delayed start_in: 30 minutes only: - main ``` ### Pattern 3: Multi-Approver ```yaml # Azure Pipelines stages: - stage: Production dependsOn: Staging jobs: - deployment: Deploy environment: name: production resourceType: Kubernetes strategy: runOnce: preDeploy: steps: - task: ManualValidation@0 inputs: notifyUsers: "team-leads@example.com" instructions: "Review staging metrics before approving" ``` **Reference:** See `assets/approval-gate-template.yml` ## Deployment Strategies ### 1. Rolling Deployment ```yaml apiVersion: apps/v1 kind: Deployment metadata: name: my-app spec: replicas: 10 strategy: type: RollingUpdate rollingUpdate: maxSurge: 2 maxUnavailable: 1 ``` **Characteristics:** - Gradual rollout - Zero downtime - Easy rollback - Best for most applications ### 2. Blue-Green Deployment ```yaml # Blue (current) kubectl apply -f blue-deployment.yaml kubectl label service my-app version=blue # Green (new) kubectl apply -f green-deployment.yaml # Test green environment kubectl label service my-app version=green # Rollback if needed kubectl label service my-app version=blue ``` **Characteristics:** - Instant switchover - Easy rollback - Doubles infrastructure cost temporarily - Good for high-risk deployments ### 3. Canary Deployment ```yaml apiVersion: argoproj.io/v1alpha1 kind: Rollout metadata: name: my-app spec: replicas: 10 strategy: canary: steps: - setWeight: 10 - pause: { duration: 5m } - setWeight: 25 - pause: { duration: 5m } - setWeight: 50 - pause: { duration: 5m } - setWeight: 100 ``` **Characteristics:** - Gradual traffic shift - Risk mitigation - Real user testing - Requires service mesh or similar ### 4. Feature Flags ```python from flagsmith import Flagsmith flagsmith = Flagsmith(environment_key="API_KEY") if flagsmith.has_feature("new_checkout_flow"): # New code path process_checkout_v2() else: # Existing code path process_checkout_v1() ``` **Characteristics:** - Deploy without releasing - A/B testing - Instant rollback - Granular control ## Pipeline Orchestration ### Multi-Stage Pipeline Example ```yaml name: Production Pipeline on: push: branches: [main] jobs: build: runs-on: ubuntu-latest steps: - uses: actions/checkout@v4 - name: Build application run: make build - name: Build Docker image run: docker build -t myapp:${{ github.sha }} . - name: Push to registry run: docker push myapp:${{ github.sha }} test: needs: build runs-on: ubuntu-latest steps: - name: Unit tests run: make test - name: Security scan run: trivy image myapp:${{ github.sha }} deploy-staging: needs: test runs-on: ubuntu-latest environment: name: staging steps: - name: Deploy to staging run: kubectl apply -f k8s/staging/ integration-test: needs: deploy-staging runs-on: ubuntu-latest steps: - name: Run E2E tests run: npm run test:e2e deploy-production: needs: integration-test runs-on: ubuntu-latest environment: name: production steps: - name: Canary deployment run: | kubectl apply -f k8s/production/ kubectl argo rollouts promote my-app verify: needs: deploy-production runs-on: ubuntu-latest steps: - name: Health check run: curl -f https://app.example.com/health - name: Notify team run: | curl -X POST ${{ secrets.SLACK_WEBHOOK }} \ -d '{"text":"Production deployment successful!"}' ``` ## Pipeline Best Practices 1. **Fail fast** - Run quick tests first 2. **Parallel execution** - Run independent jobs concurrently 3. **Caching** - Cache dependencies between runs 4. **Artifact management** - Store build artifacts 5. **Environment parity** - Keep environments consistent 6. **Secrets management** - Use secret stores (Vault, etc.) 7. **Deployment windows** - Schedule deployments appropriately 8. **Monitoring integration** - Track deployment metrics 9. **Rollback automation** - Auto-rollback on failures 10. **Documentation** - Document pipeline stages ## Rollback Strategies ### Automated Rollback ```yaml deploy-and-verify: steps: - name: Deploy new version run: kubectl apply -f k8s/ - name: Wait for rollout run: kubectl rollout status deployment/my-app - name: Health check id: health run: | for i in {1..10}; do if curl -sf https://app.example.com/health; then exit 0 fi sleep 10 done exit 1 - name: Rollback on failure if: failure() run: kubectl rollout undo deployment/my-app ``` ### Manual Rollback ```bash # List revision history kubectl rollout history deployment/my-app # Rollback to previous version kubectl rollout undo deployment/my-app # Rollback to specific revision kubectl rollout undo deployment/my-app --to-revision=3 ``` ## Monitoring and Metrics ### Key Pipeline Metrics - **Deployment Frequency** - How often deployments occur - **Lead Time** - Time from commit to production - **Change Failure Rate** - Percentage of failed deployments - **Mean Time to Recovery (MTTR)** - Time to recover from failure - **Pipeline Success Rate** - Percentage of successful runs - **Average Pipeline Duration** - Time to complete pipeline ### Integration with Monitoring ```yaml - name: Post-deployment verification run: | # Wait for metrics stabilization sleep 60 # Check error rate ERROR_RATE=$(curl -s "$PROMETHEUS_URL/api/v1/query?query=rate(http_errors_total[5m])" | jq '.data.result[0].value[1]') if (( $(echo "$ERROR_RATE > 0.01" | bc -l) )); then echo "Error rate too high: $ERROR_RATE" exit 1 fi ``` ## Reference Files - `references/pipeline-orchestration.md` - Complex pipeline patterns - `assets/approval-gate-template.yml` - Approval workflow templates ## Related Skills - `github-actions-templates` - For GitHub Actions implementation - `gitlab-ci-patterns` - For GitLab CI implementation - `secrets-management` - For secrets handling
πŸ‘0
πŸ‘οΈ0
πŸ€– Auto-discovered
πŸ€–system promptβ€’7 months ago

postgresql-table-design

Design a PostgreSQL-specific schema. Covers best-practices, data

data
⭐1
# PostgreSQL Table Design ## Core Rules - Define a **PRIMARY KEY** for reference tables (users, orders, etc.). Not always needed for time-series/event/log data. When used, prefer `BIGINT GENERATED ALWAYS AS IDENTITY`; use `UUID` only when global uniqueness/opacity is needed. - **Normalize first (to 3NF)** to eliminate data redundancy and update anomalies; denormalize **only** for measured, high-ROI reads where join performance is proven problematic. Premature denormalization creates maintenance burden. - Add **NOT NULL** everywhere it’s semantically required; use **DEFAULT**s for common values. - Create **indexes for access paths you actually query**: PK/unique (auto), **FK columns (manual!)**, frequent filters/sorts, and join keys. - Prefer **TIMESTAMPTZ** for event time; **NUMERIC** for money; **TEXT** for strings; **BIGINT** for integer values, **DOUBLE PRECISION** for floats (or `NUMERIC` for exact decimal arithmetic). ## PostgreSQL β€œGotchas” - **Identifiers**: unquoted β†’ lowercased. Avoid quoted/mixed-case names. Convention: use `snake_case` for table/column names. - **Unique + NULLs**: UNIQUE allows multiple NULLs. Use `UNIQUE (...) NULLS NOT DISTINCT` (PG15+) to restrict to one NULL. - **FK indexes**: PostgreSQL **does not** auto-index FK columns. Add them. - **No silent coercions**: length/precision overflows error out (no truncation). Example: inserting 999 into `NUMERIC(2,0)` fails with error, unlike some databases that silently truncate or round. - **Sequences/identity have gaps** (normal; don't "fix"). Rollbacks, crashes, and concurrent transactions create gaps in ID sequences (1, 2, 5, 6...). This is expected behaviorβ€”don't try to make IDs consecutive. - **Heap storage**: no clustered PK by default (unlike SQL Server/MySQL InnoDB); `CLUSTER` is one-off reorganization, not maintained on subsequent inserts. Row order on disk is insertion order unless explicitly clustered. - **MVCC**: updates/deletes leave dead tuples; vacuum handles themβ€”design to avoid hot wide-row churn. ## Data Types - **IDs**: `BIGINT GENERATED ALWAYS AS IDENTITY` preferred (`GENERATED BY DEFAULT` also fine); `UUID` when merging/federating/used in a distributed system or for opaque IDs. Generate with `uuidv7()` (preferred if using PG18+) or `gen_random_uuid()` (if using an older PG version). - **Integers**: prefer `BIGINT` unless storage space is critical; `INTEGER` for smaller ranges; avoid `SMALLINT` unless constrained. - **Floats**: prefer `DOUBLE PRECISION` over `REAL` unless storage space is critical. Use `NUMERIC` for exact decimal arithmetic. - **Strings**: prefer `TEXT`; if length limits needed, use `CHECK (LENGTH(col) <= n)` instead of `VARCHAR(n)`; avoid `CHAR(n)`. Use `BYTEA` for binary data. Large strings/binary (>2KB default threshold) automatically stored in TOAST with compression. TOAST storage: `PLAIN` (no TOAST), `EXTENDED` (compress + out-of-line), `EXTERNAL` (out-of-line, no compress), `MAIN` (compress, keep in-line if possible). Default `EXTENDED` usually optimal. Control with `ALTER TABLE tbl ALTER COLUMN col SET STORAGE strategy` and `ALTER TABLE tbl SET (toast_tuple_target = 4096)` for threshold. Case-insensitive: for locale/accent handling use non-deterministic collations; for plain ASCII use expression indexes on `LOWER(col)` (preferred unless column needs case-insensitive PK/FK/UNIQUE) or `CITEXT`. - **Money**: `NUMERIC(p,s)` (never float). - **Time**: `TIMESTAMPTZ` for timestamps; `DATE` for date-only; `INTERVAL` for durations. Avoid `TIMESTAMP` (without timezone). Use `now()` for transaction start time, `clock_timestamp()` for current wall-clock time. - **Booleans**: `BOOLEAN` with `NOT NULL` constraint unless tri-state values are required. - **Enums**: `CREATE TYPE ... AS ENUM` for small, stable sets (e.g. US states, days of week). For business-logic-driven and evolving values (e.g. order statuses) β†’ use TEXT (or INT) + CHECK or lookup table. - **Arrays**: `TEXT[]`, `INTEGER[]`, etc. Use for ordered lists where you query elements. Index with **GIN** for containment (`@>`, `<@`) and overlap (`&&`) queries. Access: `arr[1]` (1-indexed), `arr[1:3]` (slicing). Good for tags, categories; avoid for relationsβ€”use junction tables instead. Literal syntax: `'{val1,val2}'` or `ARRAY[val1,val2]`. - **Range types**: `daterange`, `numrange`, `tstzrange` for intervals. Support overlap (`&&`), containment (`@>`), operators. Index with **GiST**. Good for scheduling, versioning, numeric ranges. Pick a bounds scheme and use it consistently; prefer `[)` (inclusive/exclusive) by default. - **Network types**: `INET` for IP addresses, `CIDR` for network ranges, `MACADDR` for MAC addresses. Support network operators (`<<`, `>>`, `&&`). - **Geometric types**: `POINT`, `LINE`, `POLYGON`, `CIRCLE` for 2D spatial data. Index with **GiST**. Consider **PostGIS** for advanced spatial features. - **Text search**: `TSVECTOR` for full-text search documents, `TSQUERY` for search queries. Index `tsvector` with **GIN**. Always specify language: `to_tsvector('english', col)` and `to_tsquery('english', 'query')`. Never use single-argument versions. This applies to both index expressions and queries. - **Domain types**: `CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '^[^@]+@[^@]+$')` for reusable custom types with validation. Enforces constraints across tables. - **Composite types**: `CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT)` for structured data within columns. Access with `(col).field` syntax. - **JSONB**: preferred over JSON; index with **GIN**. Use only for optional/semi-structured attrs. ONLY use JSON if the original ordering of the contents MUST be preserved. - **Vector types**: `vector` type by `pgvector` for vector similarity search for embeddings. ### Do not use the following data types - DO NOT use `timestamp` (without time zone); DO use `timestamptz` instead. - DO NOT use `char(n)` or `varchar(n)`; DO use `text` instead. - DO NOT use `money` type; DO use `numeric` instead. - DO NOT use `timetz` type; DO use `timestamptz` instead. - DO NOT use `timestamptz(0)` or any other precision specification; DO use `timestamptz` instead - DO NOT use `serial` type; DO use `generated always as identity` instead. ## Table Types - **Regular**: default; fully durable, logged. - **TEMPORARY**: session-scoped, auto-dropped, not logged. Faster for scratch work. - **UNLOGGED**: persistent but not crash-safe. Faster writes; good for caches/staging. ## Row-Level Security Enable with `ALTER TABLE tbl ENABLE ROW LEVEL SECURITY`. Create policies: `CREATE POLICY user_access ON orders FOR SELECT TO app_users USING (user_id = current_user_id())`. Built-in user-based access control at the row level. ## Constraints - **PK**: implicit UNIQUE + NOT NULL; creates a B-tree index. - **FK**: specify `ON DELETE/UPDATE` action (`CASCADE`, `RESTRICT`, `SET NULL`, `SET DEFAULT`). Add explicit index on referencing columnβ€”speeds up joins and prevents locking issues on parent deletes/updates. Use `DEFERRABLE INITIALLY DEFERRED` for circular FK dependencies checked at transaction end. - **UNIQUE**: creates a B-tree index; allows multiple NULLs unless `NULLS NOT DISTINCT` (PG15+). Standard behavior: `(1, NULL)` and `(1, NULL)` are allowed. With `NULLS NOT DISTINCT`: only one `(1, NULL)` allowed. Prefer `NULLS NOT DISTINCT` unless you specifically need duplicate NULLs. - **CHECK**: row-local constraints; NULL values pass the check (three-valued logic). Example: `CHECK (price > 0)` allows NULL prices. Combine with `NOT NULL` to enforce: `price NUMERIC NOT NULL CHECK (price > 0)`. - **EXCLUDE**: prevents overlapping values using operators. `EXCLUDE USING gist (room_id WITH =, booking_period WITH &&)` prevents double-booking rooms. Requires appropriate index type (often GiST). ## Indexing - **B-tree**: default for equality/range queries (`=`, `<`, `>`, `BETWEEN`, `ORDER BY`) - **Composite**: order mattersβ€”index used if equality on leftmost prefix (`WHERE a = ? AND b > ?` uses index on `(a,b)`, but `WHERE b = ?` does not). Put most selective/frequently filtered columns first. - **Covering**: `CREATE INDEX ON tbl (id) INCLUDE (name, email)` - includes non-key columns for index-only scans without visiting table. - **Partial**: for hot subsets (`WHERE status = 'active'` β†’ `CREATE INDEX ON tbl (user_id) WHERE status = 'active'`). Any query with `status = 'active'` can use this index. - **Expression**: for computed search keys (`CREATE INDEX ON tbl (LOWER(email))`). Expression must match exactly in WHERE clause: `WHERE LOWER(email) = 'user@example.com'`. - **GIN**: JSONB containment/existence, arrays (`@>`, `?`), full-text search (`@@`) - **GiST**: ranges, geometry, exclusion constraints - **BRIN**: very large, naturally ordered data (time-series)β€”minimal storage overhead. Effective when row order on disk correlates with indexed column (insertion order or after `CLUSTER`). ## Partitioning - Use for very large tables (>100M rows) where queries consistently filter on partition key (often time/date). - Alternate use: use for tables where data maintenance tasks dictates e.g. data pruned or bulk replaced periodically - **RANGE**: common for time-series (`PARTITION BY RANGE (created_at)`). Create partitions: `CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM ('2024-01-01') TO ('2024-02-01')`. **TimescaleDB** automates time-based or ID-based partitioning with retention policies and compression. - **LIST**: for discrete values (`PARTITION BY LIST (region)`). Example: `FOR VALUES IN ('us-east', 'us-west')`. - **HASH**: for even distribution when no natural key (`PARTITION BY HASH (user_id)`). Creates N partitions with modulus. - **Constraint exclusion**: requires `CHECK` constraints on partitions for query planner to prune. Auto-created for declarative partitioning (PG10+). - Prefer declarative partitioning or hypertables. Do NOT use table inheritance. - **Limitations**: no global UNIQUE constraintsβ€”include partition key in PK/UNIQUE. FKs from partitioned tables not supported; use triggers. ## Special Considerations ### Update-Heavy Tables - **Separate hot/cold columns**β€”put frequently updated columns in separate table to minimize bloat. - **Use `fillfactor=90`** to leave space for HOT updates that avoid index maintenance. - **Avoid updating indexed columns**β€”prevents beneficial HOT updates. - **Partition by update patterns**β€”separate frequently updated rows in a different partition from stable data. ### Insert-Heavy Workloads - **Minimize indexes**β€”only create what you query; every index slows inserts. - **Use `COPY` or multi-row `INSERT`** instead of single-row inserts. - **UNLOGGED tables** for rebuildable staging dataβ€”much faster writes. - **Defer index creation** for bulk loadsβ€”>drop index, load data, recreate indexes. - **Partition by time/hash** to distribute load. **TimescaleDB** automates partitioning and compression of insert-heavy data. - **Use a natural key for primary key** such as a (timestamp, device_id) if enforcing global uniqueness is important many insert-heavy tables don't need a primary key at all. - If you do need a surrogate key, **Prefer `BIGINT GENERATED ALWAYS AS IDENTITY` over `UUID`**. ### Upsert-Friendly Design - **Requires UNIQUE index** on conflict target columnsβ€”`ON CONFLICT (col1, col2)` needs exact matching unique index (partial indexes don't work). - **Use `EXCLUDED.column`** to reference would-be-inserted values; only update columns that actually changed to reduce write overhead. - **`DO NOTHING` faster** than `DO UPDATE` when no actual update needed. ### Safe Schema Evolution - **Transactional DDL**: most DDL operations can run in transactions and be rolled backβ€”`BEGIN; ALTER TABLE...; ROLLBACK;` for safe testing. - **Concurrent index creation**: `CREATE INDEX CONCURRENTLY` avoids blocking writes but can't run in transactions. - **Volatile defaults cause rewrites**: adding `NOT NULL` columns with volatile defaults (e.g., `now()`, `gen_random_uuid()`) rewrites entire table. Non-volatile defaults are fast. - **Drop constraints before columns**: `ALTER TABLE DROP CONSTRAINT` then `DROP COLUMN` to avoid dependency issues. - **Function signature changes**: `CREATE OR REPLACE` with different arguments creates overloads, not replacements. DROP old version if no overload desired. ## Generated Columns - `... GENERATED ALWAYS AS (<expr>) STORED` for computed, indexable fields. PG18+ adds `VIRTUAL` columns (computed on read, not stored). ## Extensions - **`pgcrypto`**: `crypt()` for password hashing. - **`uuid-ossp`**: alternative UUID functions; prefer `pgcrypto` for new projects. - **`pg_trgm`**: fuzzy text search with `%` operator, `similarity()` function. Index with GIN for `LIKE '%pattern%'` acceleration. - **`citext`**: case-insensitive text type. Prefer expression indexes on `LOWER(col)` unless you need case-insensitive constraints. - **`btree_gin`/`btree_gist`**: enable mixed-type indexes (e.g., GIN index on both JSONB and text columns). - **`hstore`**: key-value pairs; mostly superseded by JSONB but useful for simple string mappings. - **`timescaledb`**: essential for time-seriesβ€”automated partitioning, retention, compression, continuous aggregates. - **`postgis`**: comprehensive geospatial support beyond basic geometric typesβ€”essential for location-based applications. - **`pgvector`**: vector similarity search for embeddings. - **`pgaudit`**: audit logging for all database activity. ## JSONB Guidance - Prefer `JSONB` with **GIN** index. - Default: `CREATE INDEX ON tbl USING GIN (jsonb_col);` β†’ accelerates: - **Containment** `jsonb_col @> '{"k":"v"}'` - **Key existence** `jsonb_col ? 'k'`, **any/all keys** `?\|`, `?&` - **Path containment** on nested docs - **Disjunction** `jsonb_col @> ANY(ARRAY['{"status":"active"}', '{"status":"pending"}'])` - Heavy `@>` workloads: consider opclass `jsonb_path_ops` for smaller/faster containment-only indexes: - `CREATE INDEX ON tbl USING GIN (jsonb_col jsonb_path_ops);` - **Trade-off**: loses support for key existence (`?`, `?|`, `?&`) queriesβ€”only supports containment (`@>`) - Equality/range on a specific scalar field: extract and index with B-tree (generated column or expression): - `ALTER TABLE tbl ADD COLUMN price INT GENERATED ALWAYS AS ((jsonb_col->>'price')::INT) STORED;` - `CREATE INDEX ON tbl (price);` - Prefer queries like `WHERE price BETWEEN 100 AND 500` (uses B-tree) over `WHERE (jsonb_col->>'price')::INT BETWEEN 100 AND 500` without index. - Arrays inside JSONB: use GIN + `@>` for containment (e.g., tags). Consider `jsonb_path_ops` if only doing containment. - Keep core relations in tables; use JSONB for optional/variable attributes. - Use constraints to limit allowed JSONB values in a column e.g. `config JSONB NOT NULL CHECK(jsonb_typeof(config) = 'object')` ## Examples ### Users ```sql CREATE TABLE users ( user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email TEXT NOT NULL UNIQUE, name TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE UNIQUE INDEX ON users (LOWER(email)); CREATE INDEX ON users (created_at); ``` ### Orders ```sql CREATE TABLE orders ( order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(user_id), status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','CANCELED')), total NUMERIC(10,2) NOT NULL CHECK (total > 0), created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX ON orders (user_id); CREATE INDEX ON orders (created_at); ``` ### JSONB ```sql CREATE TABLE profiles ( user_id BIGINT PRIMARY KEY REFERENCES users(user_id), attrs JSONB NOT NULL DEFAULT '{}', theme TEXT GENERATED ALWAYS AS (attrs->>'theme') STORED ); CREATE INDEX profiles_attrs_gin ON profiles USING GIN (attrs); ```
πŸ‘0
πŸ‘οΈ0
πŸ€– Auto-discovered
πŸ€–system promptβ€’7 months ago

sql-optimization-patterns

Master SQL query optimization, indexing strategies, and EXPLAIN

coding
⭐1
# SQL Optimization Patterns Transform slow database queries into lightning-fast operations through systematic optimization, proper indexing, and query plan analysis. ## When to Use This Skill - Debugging slow-running queries - Designing performant database schemas - Optimizing application response times - Reducing database load and costs - Improving scalability for growing datasets - Analyzing EXPLAIN query plans - Implementing efficient indexes - Resolving N+1 query problems ## Core Concepts ### 1. Query Execution Plans (EXPLAIN) Understanding EXPLAIN output is fundamental to optimization. **PostgreSQL EXPLAIN:** ```sql -- Basic explain EXPLAIN SELECT * FROM users WHERE email = 'user@example.com'; -- With actual execution stats EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com'; -- Verbose output with more details EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT u.*, o.order_total FROM users u JOIN orders o ON u.id = o.user_id WHERE u.created_at > NOW() - INTERVAL '30 days'; ``` **Key Metrics to Watch:** - **Seq Scan**: Full table scan (usually slow for large tables) - **Index Scan**: Using index (good) - **Index Only Scan**: Using index without touching table (best) - **Nested Loop**: Join method (okay for small datasets) - **Hash Join**: Join method (good for larger datasets) - **Merge Join**: Join method (good for sorted data) - **Cost**: Estimated query cost (lower is better) - **Rows**: Estimated rows returned - **Actual Time**: Real execution time ### 2. Index Strategies Indexes are the most powerful optimization tool. **Index Types:** - **B-Tree**: Default, good for equality and range queries - **Hash**: Only for equality (=) comparisons - **GIN**: Full-text search, array queries, JSONB - **GiST**: Geometric data, full-text search - **BRIN**: Block Range INdex for very large tables with correlation ```sql -- Standard B-Tree index CREATE INDEX idx_users_email ON users(email); -- Composite index (order matters!) CREATE INDEX idx_orders_user_status ON orders(user_id, status); -- Partial index (index subset of rows) CREATE INDEX idx_active_users ON users(email) WHERE status = 'active'; -- Expression index CREATE INDEX idx_users_lower_email ON users(LOWER(email)); -- Covering index (include additional columns) CREATE INDEX idx_users_email_covering ON users(email) INCLUDE (name, created_at); -- Full-text search index CREATE INDEX idx_posts_search ON posts USING GIN(to_tsvector('english', title || ' ' || body)); -- JSONB index CREATE INDEX idx_metadata ON events USING GIN(metadata); ``` ### 3. Query Optimization Patterns **Avoid SELECT \*:** ```sql -- Bad: Fetches unnecessary columns SELECT * FROM users WHERE id = 123; -- Good: Fetch only what you need SELECT id, email, name FROM users WHERE id = 123; ``` **Use WHERE Clause Efficiently:** ```sql -- Bad: Function prevents index usage SELECT * FROM users WHERE LOWER(email) = 'user@example.com'; -- Good: Create functional index or use exact match CREATE INDEX idx_users_email_lower ON users(LOWER(email)); -- Then: SELECT * FROM users WHERE LOWER(email) = 'user@example.com'; -- Or store normalized data SELECT * FROM users WHERE email = 'user@example.com'; ``` **Optimize JOINs:** ```sql -- Bad: Cartesian product then filter SELECT u.name, o.total FROM users u, orders o WHERE u.id = o.user_id AND u.created_at > '2024-01-01'; -- Good: Filter before join SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id WHERE u.created_at > '2024-01-01'; -- Better: Filter both tables SELECT u.name, o.total FROM (SELECT * FROM users WHERE created_at > '2024-01-01') u JOIN orders o ON u.id = o.user_id; ``` ## Optimization Patterns ### Pattern 1: Eliminate N+1 Queries **Problem: N+1 Query Anti-Pattern** ```python # Bad: Executes N+1 queries users = db.query("SELECT * FROM users LIMIT 10") for user in users: orders = db.query("SELECT * FROM orders WHERE user_id = ?", user.id) # Process orders ``` **Solution: Use JOINs or Batch Loading** ```sql -- Solution 1: JOIN SELECT u.id, u.name, o.id as order_id, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.id IN (1, 2, 3, 4, 5); -- Solution 2: Batch query SELECT * FROM orders WHERE user_id IN (1, 2, 3, 4, 5); ``` ```python # Good: Single query with JOIN or batch load # Using JOIN results = db.query(""" SELECT u.id, u.name, o.id as order_id, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.id IN (1, 2, 3, 4, 5) """) # Or batch load users = db.query("SELECT * FROM users LIMIT 10") user_ids = [u.id for u in users] orders = db.query( "SELECT * FROM orders WHERE user_id IN (?)", user_ids ) # Group orders by user_id orders_by_user = {} for order in orders: orders_by_user.setdefault(order.user_id, []).append(order) ``` ### Pattern 2: Optimize Pagination **Bad: OFFSET on Large Tables** ```sql -- Slow for large offsets SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 100000; -- Very slow! ``` **Good: Cursor-Based Pagination** ```sql -- Much faster: Use cursor (last seen ID) SELECT * FROM users WHERE created_at < '2024-01-15 10:30:00' -- Last cursor ORDER BY created_at DESC LIMIT 20; -- With composite sorting SELECT * FROM users WHERE (created_at, id) < ('2024-01-15 10:30:00', 12345) ORDER BY created_at DESC, id DESC LIMIT 20; -- Requires index CREATE INDEX idx_users_cursor ON users(created_at DESC, id DESC); ``` ### Pattern 3: Aggregate Efficiently **Optimize COUNT Queries:** ```sql -- Bad: Counts all rows SELECT COUNT(*) FROM orders; -- Slow on large tables -- Good: Use estimates for approximate counts SELECT reltuples::bigint AS estimate FROM pg_class WHERE relname = 'orders'; -- Good: Filter before counting SELECT COUNT(*) FROM orders WHERE created_at > NOW() - INTERVAL '7 days'; -- Better: Use index-only scan CREATE INDEX idx_orders_created ON orders(created_at); SELECT COUNT(*) FROM orders WHERE created_at > NOW() - INTERVAL '7 days'; ``` **Optimize GROUP BY:** ```sql -- Bad: Group by then filter SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id HAVING COUNT(*) > 10; -- Better: Filter first, then group (if possible) SELECT user_id, COUNT(*) as order_count FROM orders WHERE status = 'completed' GROUP BY user_id HAVING COUNT(*) > 10; -- Best: Use covering index CREATE INDEX idx_orders_user_status ON orders(user_id, status); ``` ### Pattern 4: Subquery Optimization **Transform Correlated Subqueries:** ```sql -- Bad: Correlated subquery (runs for each row) SELECT u.name, u.email, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count FROM users u; -- Good: JOIN with aggregation SELECT u.name, u.email, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id, u.name, u.email; -- Better: Use window functions SELECT DISTINCT ON (u.id) u.name, u.email, COUNT(o.id) OVER (PARTITION BY u.id) as order_count FROM users u LEFT JOIN orders o ON o.user_id = u.id; ``` **Use CTEs for Clarity:** ```sql -- Using Common Table Expressions WITH recent_users AS ( SELECT id, name, email FROM users WHERE created_at > NOW() - INTERVAL '30 days' ), user_order_counts AS ( SELECT user_id, COUNT(*) as order_count FROM orders WHERE created_at > NOW() - INTERVAL '30 days' GROUP BY user_id ) SELECT ru.name, ru.email, COALESCE(uoc.order_count, 0) as orders FROM recent_users ru LEFT JOIN user_order_counts uoc ON ru.id = uoc.user_id; ``` ### Pattern 5: Batch Operations **Batch INSERT:** ```sql -- Bad: Multiple individual inserts INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'); INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com'); INSERT INTO users (name, email) VALUES ('Carol', 'carol@example.com'); -- Good: Batch insert INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'), ('Bob', 'bob@example.com'), ('Carol', 'carol@example.com'); -- Better: Use COPY for bulk inserts (PostgreSQL) COPY users (name, email) FROM '/tmp/users.csv' CSV HEADER; ``` **Batch UPDATE:** ```sql -- Bad: Update in loop UPDATE users SET status = 'active' WHERE id = 1; UPDATE users SET status = 'active' WHERE id = 2; -- ... repeat for many IDs -- Good: Single UPDATE with IN clause UPDATE users SET status = 'active' WHERE id IN (1, 2, 3, 4, 5, ...); -- Better: Use temporary table for large batches CREATE TEMP TABLE temp_user_updates (id INT, new_status VARCHAR); INSERT INTO temp_user_updates VALUES (1, 'active'), (2, 'active'), ...; UPDATE users u SET status = t.new_status FROM temp_user_updates t WHERE u.id = t.id; ``` ## Advanced Techniques ### Materialized Views Pre-compute expensive queries. ```sql -- Create materialized view CREATE MATERIALIZED VIEW user_order_summary AS SELECT u.id, u.name, COUNT(o.id) as total_orders, SUM(o.total) as total_spent, MAX(o.created_at) as last_order_date FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name; -- Add index to materialized view CREATE INDEX idx_user_summary_spent ON user_order_summary(total_spent DESC); -- Refresh materialized view REFRESH MATERIALIZED VIEW user_order_summary; -- Concurrent refresh (PostgreSQL) REFRESH MATERIALIZED VIEW CONCURRENTLY user_order_summary; -- Query materialized view (very fast) SELECT * FROM user_order_summary WHERE total_spent > 1000 ORDER BY total_spent DESC; ``` ### Partitioning Split large tables for better performance. ```sql -- Range partitioning by date (PostgreSQL) CREATE TABLE orders ( id SERIAL, user_id INT, total DECIMAL, created_at TIMESTAMP ) PARTITION BY RANGE (created_at); -- Create partitions CREATE TABLE orders_2024_q1 PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2024-04-01'); CREATE TABLE orders_2024_q2 PARTITION OF orders FOR VALUES FROM ('2024-04-01') TO ('2024-07-01'); -- Queries automatically use appropriate partition SELECT * FROM orders WHERE created_at BETWEEN '2024-02-01' AND '2024-02-28'; -- Only scans orders_2024_q1 partition ``` ### Query Hints and Optimization ```sql -- Force index usage (MySQL) SELECT * FROM users USE INDEX (idx_users_email) WHERE email = 'user@example.com'; -- Parallel query (PostgreSQL) SET max_parallel_workers_per_gather = 4; SELECT * FROM large_table WHERE condition; -- Join hints (PostgreSQL) SET enable_nestloop = OFF; -- Force hash or merge join ``` ## Best Practices 1. **Index Selectively**: Too many indexes slow down writes 2. **Monitor Query Performance**: Use slow query logs 3. **Keep Statistics Updated**: Run ANALYZE regularly 4. **Use Appropriate Data Types**: Smaller types = better performance 5. **Normalize Thoughtfully**: Balance normalization vs performance 6. **Cache Frequently Accessed Data**: Use application-level caching 7. **Connection Pooling**: Reuse database connections 8. **Regular Maintenance**: VACUUM, ANALYZE, rebuild indexes ```sql -- Update statistics ANALYZE users; ANALYZE VERBOSE orders; -- Vacuum (PostgreSQL) VACUUM ANALYZE users; VACUUM FULL users; -- Reclaim space (locks table) -- Reindex REINDEX INDEX idx_users_email; REINDEX TABLE users; ``` ## Common Pitfalls - **Over-Indexing**: Each index slows down INSERT/UPDATE/DELETE - **Unused Indexes**: Waste space and slow writes - **Missing Indexes**: Slow queries, full table scans - **Implicit Type Conversion**: Prevents index usage - **OR Conditions**: Can't use indexes efficiently - **LIKE with Leading Wildcard**: `LIKE '%abc'` can't use index - **Function in WHERE**: Prevents index usage unless functional index exists ## Monitoring Queries ```sql -- Find slow queries (PostgreSQL) SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10; -- Find missing indexes (PostgreSQL) SELECT schemaname, tablename, seq_scan, seq_tup_read, idx_scan, seq_tup_read / seq_scan AS avg_seq_tup_read FROM pg_stat_user_tables WHERE seq_scan > 0 ORDER BY seq_tup_read DESC LIMIT 10; -- Find unused indexes (PostgreSQL) SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC; ``` ## Resources - **references/postgres-optimization-guide.md**: PostgreSQL-specific optimization - **references/mysql-optimization-guide.md**: MySQL/MariaDB optimization - **references/query-plan-analysis.md**: Deep dive into EXPLAIN plans - **assets/index-strategy-checklist.md**: When and how to create indexes - **assets/query-optimization-checklist.md**: Step-by-step optimization guide - **scripts/analyze-slow-queries.sql**: Identify slow queries in your database - **scripts/index-recommendations.sql**: Generate index recommendations
πŸ‘0
πŸ‘οΈ0
πŸ€– Auto-discovered
πŸ€–system promptβ€’7 months ago

architecture-decision-records

Write and maintain Architecture Decision Records (ADRs) following

coding
⭐1
# Architecture Decision Records Comprehensive patterns for creating, maintaining, and managing Architecture Decision Records (ADRs) that capture the context and rationale behind significant technical decisions. ## When to Use This Skill - Making significant architectural decisions - Documenting technology choices - Recording design trade-offs - Onboarding new team members - Reviewing historical decisions - Establishing decision-making processes ## Core Concepts ### 1. What is an ADR? An Architecture Decision Record captures: - **Context**: Why we needed to make a decision - **Decision**: What we decided - **Consequences**: What happens as a result ### 2. When to Write an ADR | Write ADR | Skip ADR | | -------------------------- | ---------------------- | | New framework adoption | Minor version upgrades | | Database technology choice | Bug fixes | | API design patterns | Implementation details | | Security architecture | Routine maintenance | | Integration patterns | Configuration changes | ### 3. ADR Lifecycle ``` Proposed β†’ Accepted β†’ Deprecated β†’ Superseded ↓ Rejected ``` ## Templates ### Template 1: Standard ADR (MADR Format) ```markdown # ADR-0001: Use PostgreSQL as Primary Database ## Status Accepted ## Context We need to select a primary database for our new e-commerce platform. The system will handle: - ~10,000 concurrent users - Complex product catalog with hierarchical categories - Transaction processing for orders and payments - Full-text search for products - Geospatial queries for store locator The team has experience with MySQL, PostgreSQL, and MongoDB. We need ACID compliance for financial transactions. ## Decision Drivers - **Must have ACID compliance** for payment processing - **Must support complex queries** for reporting - **Should support full-text search** to reduce infrastructure complexity - **Should have good JSON support** for flexible product attributes - **Team familiarity** reduces onboarding time ## Considered Options ### Option 1: PostgreSQL - **Pros**: ACID compliant, excellent JSON support (JSONB), built-in full-text search, PostGIS for geospatial, team has experience - **Cons**: Slightly more complex replication setup than MySQL ### Option 2: MySQL - **Pros**: Very familiar to team, simple replication, large community - **Cons**: Weaker JSON support, no built-in full-text search (need Elasticsearch), no geospatial without extensions ### Option 3: MongoDB - **Pros**: Flexible schema, native JSON, horizontal scaling - **Cons**: No ACID for multi-document transactions (at decision time), team has limited experience, requires schema design discipline ## Decision We will use **PostgreSQL 15** as our primary database. ## Rationale PostgreSQL provides the best balance of: 1. **ACID compliance** essential for e-commerce transactions 2. **Built-in capabilities** (full-text search, JSONB, PostGIS) reduce infrastructure complexity 3. **Team familiarity** with SQL databases reduces learning curve 4. **Mature ecosystem** with excellent tooling and community support The slight complexity in replication is outweighed by the reduction in additional services (no separate Elasticsearch needed). ## Consequences ### Positive - Single database handles transactions, search, and geospatial queries - Reduced operational complexity (fewer services to manage) - Strong consistency guarantees for financial data - Team can leverage existing SQL expertise ### Negative - Need to learn PostgreSQL-specific features (JSONB, full-text search syntax) - Vertical scaling limits may require read replicas sooner - Some team members need PostgreSQL-specific training ### Risks - Full-text search may not scale as well as dedicated search engines - Mitigation: Design for potential Elasticsearch addition if needed ## Implementation Notes - Use JSONB for flexible product attributes - Implement connection pooling with PgBouncer - Set up streaming replication for read replicas - Use pg_trgm extension for fuzzy search ## Related Decisions - ADR-0002: Caching Strategy (Redis) - complements database choice - ADR-0005: Search Architecture - may supersede if Elasticsearch needed ## References - [PostgreSQL JSON Documentation](https://www.postgresql.org/docs/current/datatype-json.html) - [PostgreSQL Full Text Search](https://www.postgresql.org/docs/current/textsearch.html) - Internal: Performance benchmarks in `/docs/benchmarks/database-comparison.md` ``` ### Template 2: Lightweight ADR ```markdown # ADR-0012: Adopt TypeScript for Frontend Development **Status**: Accepted **Date**: 2024-01-15 **Deciders**: @alice, @bob, @charlie ## Context Our React codebase has grown to 50+ components with increasing bug reports related to prop type mismatches and undefined errors. PropTypes provide runtime-only checking. ## Decision Adopt TypeScript for all new frontend code. Migrate existing code incrementally. ## Consequences **Good**: Catch type errors at compile time, better IDE support, self-documenting code. **Bad**: Learning curve for team, initial slowdown, build complexity increase. **Mitigations**: TypeScript training sessions, allow gradual adoption with `allowJs: true`. ``` ### Template 3: Y-Statement Format ```markdown # ADR-0015: API Gateway Selection In the context of **building a microservices architecture**, facing **the need for centralized API management, authentication, and rate limiting**, we decided for **Kong Gateway** and against **AWS API Gateway and custom Nginx solution**, to achieve **vendor independence, plugin extensibility, and team familiarity with Lua**, accepting that **we need to manage Kong infrastructure ourselves**. ``` ### Template 4: ADR for Deprecation ```markdown # ADR-0020: Deprecate MongoDB in Favor of PostgreSQL ## Status Accepted (Supersedes ADR-0003) ## Context ADR-0003 (2021) chose MongoDB for user profile storage due to schema flexibility needs. Since then: - MongoDB's multi-document transactions remain problematic for our use case - Our schema has stabilized and rarely changes - We now have PostgreSQL expertise from other services - Maintaining two databases increases operational burden ## Decision Deprecate MongoDB and migrate user profiles to PostgreSQL. ## Migration Plan 1. **Phase 1** (Week 1-2): Create PostgreSQL schema, dual-write enabled 2. **Phase 2** (Week 3-4): Backfill historical data, validate consistency 3. **Phase 3** (Week 5): Switch reads to PostgreSQL, monitor 4. **Phase 4** (Week 6): Remove MongoDB writes, decommission ## Consequences ### Positive - Single database technology reduces operational complexity - ACID transactions for user data - Team can focus PostgreSQL expertise ### Negative - Migration effort (~4 weeks) - Risk of data issues during migration - Lose some schema flexibility ## Lessons Learned Document from ADR-0003 experience: - Schema flexibility benefits were overestimated - Operational cost of multiple databases was underestimated - Consider long-term maintenance in technology decisions ``` ### Template 5: Request for Comments (RFC) Style ```markdown # RFC-0025: Adopt Event Sourcing for Order Management ## Summary Propose adopting event sourcing pattern for the order management domain to improve auditability, enable temporal queries, and support business analytics. ## Motivation Current challenges: 1. Audit requirements need complete order history 2. "What was the order state at time X?" queries are impossible 3. Analytics team needs event stream for real-time dashboards 4. Order state reconstruction for customer support is manual ## Detailed Design ### Event Store ``` OrderCreated { orderId, customerId, items[], timestamp } OrderItemAdded { orderId, item, timestamp } OrderItemRemoved { orderId, itemId, timestamp } PaymentReceived { orderId, amount, paymentId, timestamp } OrderShipped { orderId, trackingNumber, timestamp } ``` ### Projections - **CurrentOrderState**: Materialized view for queries - **OrderHistory**: Complete timeline for audit - **DailyOrderMetrics**: Analytics aggregation ### Technology - Event Store: EventStoreDB (purpose-built, handles projections) - Alternative considered: Kafka + custom projection service ## Drawbacks - Learning curve for team - Increased complexity vs. CRUD - Need to design events carefully (immutable once stored) - Storage growth (events never deleted) ## Alternatives 1. **Audit tables**: Simpler but doesn't enable temporal queries 2. **CDC from existing DB**: Complex, doesn't change data model 3. **Hybrid**: Event source only for order state changes ## Unresolved Questions - [ ] Event schema versioning strategy - [ ] Retention policy for events - [ ] Snapshot frequency for performance ## Implementation Plan 1. Prototype with single order type (2 weeks) 2. Team training on event sourcing (1 week) 3. Full implementation and migration (4 weeks) 4. Monitoring and optimization (ongoing) ## References - [Event Sourcing by Martin Fowler](https://martinfowler.com/eaaDev/EventSourcing.html) - [EventStoreDB Documentation](https://www.eventstore.com/docs) ``` ## ADR Management ### Directory Structure ``` docs/ β”œβ”€β”€ adr/ β”‚ β”œβ”€β”€ README.md # Index and guidelines β”‚ β”œβ”€β”€ template.md # Team's ADR template β”‚ β”œβ”€β”€ 0001-use-postgresql.md β”‚ β”œβ”€β”€ 0002-caching-strategy.md β”‚ β”œβ”€β”€ 0003-mongodb-user-profiles.md # [DEPRECATED] β”‚ └── 0020-deprecate-mongodb.md # Supersedes 0003 ``` ### ADR Index (README.md) ```markdown # Architecture Decision Records This directory contains Architecture Decision Records (ADRs) for [Project Name]. ## Index | ADR | Title | Status | Date | | ------------------------------------- | ---------------------------------- | ---------- | ---------- | | [0001](0001-use-postgresql.md) | Use PostgreSQL as Primary Database | Accepted | 2024-01-10 | | [0002](0002-caching-strategy.md) | Caching Strategy with Redis | Accepted | 2024-01-12 | | [0003](0003-mongodb-user-profiles.md) | MongoDB for User Profiles | Deprecated | 2023-06-15 | | [0020](0020-deprecate-mongodb.md) | Deprecate MongoDB | Accepted | 2024-01-15 | ## Creating a New ADR 1. Copy `template.md` to `NNNN-title-with-dashes.md` 2. Fill in the template 3. Submit PR for review 4. Update this index after approval ## ADR Status - **Proposed**: Under discussion - **Accepted**: Decision made, implementing - **Deprecated**: No longer relevant - **Superseded**: Replaced by another ADR - **Rejected**: Considered but not adopted ``` ### Automation (adr-tools) ```bash # Install adr-tools brew install adr-tools # Initialize ADR directory adr init docs/adr # Create new ADR adr new "Use PostgreSQL as Primary Database" # Supersede an ADR adr new -s 3 "Deprecate MongoDB in Favor of PostgreSQL" # Generate table of contents adr generate toc > docs/adr/README.md # Link related ADRs adr link 2 "Complements" 1 "Is complemented by" ``` ## Review Process ```markdown ## ADR Review Checklist ### Before Submission - [ ] Context clearly explains the problem - [ ] All viable options considered - [ ] Pros/cons balanced and honest - [ ] Consequences (positive and negative) documented - [ ] Related ADRs linked ### During Review - [ ] At least 2 senior engineers reviewed - [ ] Affected teams consulted - [ ] Security implications considered - [ ] Cost implications documented - [ ] Reversibility assessed ### After Acceptance - [ ] ADR index updated - [ ] Team notified - [ ] Implementation tickets created - [ ] Related documentation updated ``` ## Best Practices ### Do's - **Write ADRs early** - Before implementation starts - **Keep them short** - 1-2 pages maximum - **Be honest about trade-offs** - Include real cons - **Link related decisions** - Build decision graph - **Update status** - Deprecate when superseded ### Don'ts - **Don't change accepted ADRs** - Write new ones to supersede - **Don't skip context** - Future readers need background - **Don't hide failures** - Rejected decisions are valuable - **Don't be vague** - Specific decisions, specific consequences - **Don't forget implementation** - ADR without action is waste ## Resources - [Documenting Architecture Decisions (Michael Nygard)](https://cognitect.com/blog/2011/11/15/documenting-architecture-decisions) - [MADR Template](https://adr.github.io/madr/) - [ADR GitHub Organization](https://adr.github.io/) - [adr-tools](https://github.com/npryce/adr-tools)
πŸ‘0
πŸ‘οΈ0
πŸ€– Auto-discovered
πŸ€–system promptβ€’7 months ago

python-resilience

Python resilience patterns including automatic retries, exponential

coding
⭐1
# Python Resilience Patterns Build fault-tolerant Python applications that gracefully handle transient failures, network issues, and service outages. Resilience patterns keep systems running when dependencies are unreliable. ## When to Use This Skill - Adding retry logic to external service calls - Implementing timeouts for network operations - Building fault-tolerant microservices - Handling rate limiting and backpressure - Creating infrastructure decorators - Designing circuit breakers ## Core Concepts ### 1. Transient vs Permanent Failures Retry transient errors (network timeouts, temporary service issues). Don't retry permanent errors (invalid credentials, bad requests). ### 2. Exponential Backoff Increase wait time between retries to avoid overwhelming recovering services. ### 3. Jitter Add randomness to backoff to prevent thundering herd when many clients retry simultaneously. ### 4. Bounded Retries Cap both attempt count and total duration to prevent infinite retry loops. ## Quick Start ```python from tenacity import retry, stop_after_attempt, wait_exponential_jitter @retry( stop=stop_after_attempt(3), wait=wait_exponential_jitter(initial=1, max=10), ) def call_external_service(request: dict) -> dict: return httpx.post("https://api.example.com", json=request).json() ``` ## Fundamental Patterns ### Pattern 1: Basic Retry with Tenacity Use the `tenacity` library for production-grade retry logic. For simpler cases, consider built-in retry functionality or a lightweight custom implementation. ```python from tenacity import ( retry, stop_after_attempt, stop_after_delay, wait_exponential_jitter, retry_if_exception_type, ) TRANSIENT_ERRORS = (ConnectionError, TimeoutError, OSError) @retry( retry=retry_if_exception_type(TRANSIENT_ERRORS), stop=stop_after_attempt(5) | stop_after_delay(60), wait=wait_exponential_jitter(initial=1, max=30), ) def fetch_data(url: str) -> dict: """Fetch data with automatic retry on transient failures.""" response = httpx.get(url, timeout=30) response.raise_for_status() return response.json() ``` ### Pattern 2: Retry Only Appropriate Errors Whitelist specific transient exceptions. Never retry: - `ValueError`, `TypeError` - These are bugs, not transient issues - `AuthenticationError` - Invalid credentials won't become valid - HTTP 4xx errors (except 429) - Client errors are permanent ```python from tenacity import retry, retry_if_exception_type import httpx # Define what's retryable RETRYABLE_EXCEPTIONS = ( ConnectionError, TimeoutError, httpx.ConnectTimeout, httpx.ReadTimeout, ) @retry( retry=retry_if_exception_type(RETRYABLE_EXCEPTIONS), stop=stop_after_attempt(3), wait=wait_exponential_jitter(initial=1, max=10), ) def resilient_api_call(endpoint: str) -> dict: """Make API call with retry on network issues.""" return httpx.get(endpoint, timeout=10).json() ``` ### Pattern 3: HTTP Status Code Retries Retry specific HTTP status codes that indicate transient issues. ```python from tenacity import retry, retry_if_result, stop_after_attempt import httpx RETRY_STATUS_CODES = {429, 502, 503, 504} def should_retry_response(response: httpx.Response) -> bool: """Check if response indicates a retryable error.""" return response.status_code in RETRY_STATUS_CODES @retry( retry=retry_if_result(should_retry_response), stop=stop_after_attempt(3), wait=wait_exponential_jitter(initial=1, max=10), ) def http_request(method: str, url: str, **kwargs) -> httpx.Response: """Make HTTP request with retry on transient status codes.""" return httpx.request(method, url, timeout=30, **kwargs) ``` ### Pattern 4: Combined Exception and Status Retry Handle both network exceptions and HTTP status codes. ```python from tenacity import ( retry, retry_if_exception_type, retry_if_result, stop_after_attempt, wait_exponential_jitter, before_sleep_log, ) import logging import httpx logger = logging.getLogger(__name__) TRANSIENT_EXCEPTIONS = ( ConnectionError, TimeoutError, httpx.ConnectError, httpx.ReadTimeout, ) RETRY_STATUS_CODES = {429, 500, 502, 503, 504} def is_retryable_response(response: httpx.Response) -> bool: return response.status_code in RETRY_STATUS_CODES @retry( retry=( retry_if_exception_type(TRANSIENT_EXCEPTIONS) | retry_if_result(is_retryable_response) ), stop=stop_after_attempt(5), wait=wait_exponential_jitter(initial=1, max=30), before_sleep=before_sleep_log(logger, logging.WARNING), ) def robust_http_call( method: str, url: str, **kwargs, ) -> httpx.Response: """HTTP call with comprehensive retry handling.""" return httpx.request(method, url, timeout=30, **kwargs) ``` ## Advanced Patterns ### Pattern 5: Logging Retry Attempts Track retry behavior for debugging and alerting. ```python from tenacity import retry, stop_after_attempt, wait_exponential import structlog logger = structlog.get_logger() def log_retry_attempt(retry_state): """Log detailed retry information.""" exception = retry_state.outcome.exception() logger.warning( "Retrying operation", attempt=retry_state.attempt_number, exception_type=type(exception).__name__, exception_message=str(exception), next_wait_seconds=retry_state.next_action.sleep if retry_state.next_action else None, ) @retry( stop=stop_after_attempt(3), wait=wait_exponential(multiplier=1, max=10), before_sleep=log_retry_attempt, ) def call_with_logging(request: dict) -> dict: """External call with retry logging.""" ... ``` ### Pattern 6: Timeout Decorator Create reusable timeout decorators for consistent timeout handling. ```python import asyncio from functools import wraps from typing import TypeVar, Callable T = TypeVar("T") def with_timeout(seconds: float): """Decorator to add timeout to async functions.""" def decorator(func: Callable[..., T]) -> Callable[..., T]: @wraps(func) async def wrapper(*args, **kwargs) -> T: return await asyncio.wait_for( func(*args, **kwargs), timeout=seconds, ) return wrapper return decorator @with_timeout(30) async def fetch_with_timeout(url: str) -> dict: """Fetch URL with 30 second timeout.""" async with httpx.AsyncClient() as client: response = await client.get(url) return response.json() ``` ### Pattern 7: Cross-Cutting Concerns via Decorators Stack decorators to separate infrastructure from business logic. ```python from functools import wraps from typing import TypeVar, Callable import structlog logger = structlog.get_logger() T = TypeVar("T") def traced(name: str | None = None): """Add tracing to function calls.""" def decorator(func: Callable[..., T]) -> Callable[..., T]: span_name = name or func.__name__ @wraps(func) async def wrapper(*args, **kwargs) -> T: logger.info("Operation started", operation=span_name) try: result = await func(*args, **kwargs) logger.info("Operation completed", operation=span_name) return result except Exception as e: logger.error("Operation failed", operation=span_name, error=str(e)) raise return wrapper return decorator # Stack multiple concerns @traced("fetch_user_data") @with_timeout(30) @retry(stop=stop_after_attempt(3), wait=wait_exponential_jitter()) async def fetch_user_data(user_id: str) -> dict: """Fetch user with tracing, timeout, and retry.""" ... ``` ### Pattern 8: Dependency Injection for Testability Pass infrastructure components through constructors for easy testing. ```python from dataclasses import dataclass from typing import Protocol class Logger(Protocol): def info(self, msg: str, **kwargs) -> None: ... def error(self, msg: str, **kwargs) -> None: ... class MetricsClient(Protocol): def increment(self, metric: str, tags: dict | None = None) -> None: ... def timing(self, metric: str, value: float) -> None: ... @dataclass class UserService: """Service with injected infrastructure.""" repository: UserRepository logger: Logger metrics: MetricsClient async def get_user(self, user_id: str) -> User: self.logger.info("Fetching user", user_id=user_id) start = time.perf_counter() try: user = await self.repository.get(user_id) self.metrics.increment("user.fetch.success") return user except Exception as e: self.metrics.increment("user.fetch.error") self.logger.error("Failed to fetch user", user_id=user_id, error=str(e)) raise finally: elapsed = time.perf_counter() - start self.metrics.timing("user.fetch.duration", elapsed) # Easy to test with fakes service = UserService( repository=FakeRepository(), logger=FakeLogger(), metrics=FakeMetrics(), ) ``` ### Pattern 9: Fail-Safe Defaults Degrade gracefully when non-critical operations fail. ```python from typing import TypeVar from collections.abc import Callable T = TypeVar("T") def fail_safe(default: T, log_failure: bool = True): """Return default value on failure instead of raising.""" def decorator(func: Callable[..., T]) -> Callable[..., T]: @wraps(func) async def wrapper(*args, **kwargs) -> T: try: return await func(*args, **kwargs) except Exception as e: if log_failure: logger.warning( "Operation failed, using default", function=func.__name__, error=str(e), ) return default return wrapper return decorator @fail_safe(default=[]) async def get_recommendations(user_id: str) -> list[str]: """Get recommendations, return empty list on failure.""" ... ``` ## Best Practices Summary 1. **Retry only transient errors** - Don't retry bugs or authentication failures 2. **Use exponential backoff** - Give services time to recover 3. **Add jitter** - Prevent thundering herd from synchronized retries 4. **Cap total duration** - `stop_after_attempt(5) | stop_after_delay(60)` 5. **Log every retry** - Silent retries hide systemic problems 6. **Use decorators** - Keep retry logic separate from business logic 7. **Inject dependencies** - Make infrastructure testable 8. **Set timeouts everywhere** - Every network call needs a timeout 9. **Fail gracefully** - Return cached/default values for non-critical paths 10. **Monitor retry rates** - High retry rates indicate underlying issues
πŸ‘0
πŸ‘οΈ0
πŸ€– Auto-discovered
πŸ€–system promptβ€’7 months ago

python-resource-management

Python resource management with context managers, cleanup patterns,

coding
⭐1
# Python Resource Management Manage resources deterministically using context managers. Resources like database connections, file handles, and network sockets should be released reliably, even when exceptions occur. ## When to Use This Skill - Managing database connections and connection pools - Working with file handles and I/O - Implementing custom context managers - Building streaming responses with state - Handling nested resource cleanup - Creating async context managers ## Core Concepts ### 1. Context Managers The `with` statement ensures resources are released automatically, even on exceptions. ### 2. Protocol Methods `__enter__`/`__exit__` for sync, `__aenter__`/`__aexit__` for async resource management. ### 3. Unconditional Cleanup `__exit__` always runs, regardless of whether an exception occurred. ### 4. Exception Handling Return `True` from `__exit__` to suppress exceptions, `False` to propagate them. ## Quick Start ```python from contextlib import contextmanager @contextmanager def managed_resource(): resource = acquire_resource() try: yield resource finally: resource.cleanup() with managed_resource() as r: r.do_work() ``` ## Fundamental Patterns ### Pattern 1: Class-Based Context Manager Implement the context manager protocol for complex resources. ```python class DatabaseConnection: """Database connection with automatic cleanup.""" def __init__(self, dsn: str) -> None: self._dsn = dsn self._conn: Connection | None = None def connect(self) -> None: """Establish database connection.""" self._conn = psycopg.connect(self._dsn) def close(self) -> None: """Close connection if open.""" if self._conn is not None: self._conn.close() self._conn = None def __enter__(self) -> "DatabaseConnection": """Enter context: connect and return self.""" self.connect() return self def __exit__( self, exc_type: type[BaseException] | None, exc_val: BaseException | None, exc_tb: TracebackType | None, ) -> None: """Exit context: always close connection.""" self.close() # Usage with context manager (preferred) with DatabaseConnection(dsn) as db: result = db.execute(query) # Manual management when needed db = DatabaseConnection(dsn) db.connect() try: result = db.execute(query) finally: db.close() ``` ### Pattern 2: Async Context Manager For async resources, implement the async protocol. ```python class AsyncDatabasePool: """Async database connection pool.""" def __init__(self, dsn: str, min_size: int = 1, max_size: int = 10) -> None: self._dsn = dsn self._min_size = min_size self._max_size = max_size self._pool: asyncpg.Pool | None = None async def __aenter__(self) -> "AsyncDatabasePool": """Create connection pool.""" self._pool = await asyncpg.create_pool( self._dsn, min_size=self._min_size, max_size=self._max_size, ) return self async def __aexit__( self, exc_type: type[BaseException] | None, exc_val: BaseException | None, exc_tb: TracebackType | None, ) -> None: """Close all connections in pool.""" if self._pool is not None: await self._pool.close() async def execute(self, query: str, *args) -> list[dict]: """Execute query using pooled connection.""" async with self._pool.acquire() as conn: return await conn.fetch(query, *args) # Usage async with AsyncDatabasePool(dsn) as pool: users = await pool.execute("SELECT * FROM users WHERE active = $1", True) ``` ### Pattern 3: Using @contextmanager Decorator Simplify context managers with the decorator for straightforward cases. ```python from contextlib import contextmanager, asynccontextmanager import time import structlog logger = structlog.get_logger() @contextmanager def timed_block(name: str): """Time a block of code.""" start = time.perf_counter() try: yield finally: elapsed = time.perf_counter() - start logger.info(f"{name} completed", duration_seconds=round(elapsed, 3)) # Usage with timed_block("data_processing"): process_large_dataset() @asynccontextmanager async def database_transaction(conn: AsyncConnection): """Manage database transaction.""" await conn.execute("BEGIN") try: yield conn await conn.execute("COMMIT") except Exception: await conn.execute("ROLLBACK") raise # Usage async with database_transaction(conn) as tx: await tx.execute("INSERT INTO users ...") await tx.execute("INSERT INTO audit_log ...") ``` ### Pattern 4: Unconditional Resource Release Always clean up resources in `__exit__`, regardless of exceptions. ```python class FileProcessor: """Process file with guaranteed cleanup.""" def __init__(self, path: str) -> None: self._path = path self._file: IO | None = None self._temp_files: list[Path] = [] def __enter__(self) -> "FileProcessor": self._file = open(self._path, "r") return self def __exit__( self, exc_type: type[BaseException] | None, exc_val: BaseException | None, exc_tb: TracebackType | None, ) -> None: """Clean up all resources unconditionally.""" # Close main file if self._file is not None: self._file.close() # Clean up any temporary files for temp_file in self._temp_files: try: temp_file.unlink() except OSError: pass # Best effort cleanup # Return None/False to propagate any exception ``` ## Advanced Patterns ### Pattern 5: Selective Exception Suppression Only suppress specific, documented exceptions. ```python class StreamWriter: """Writer that handles broken pipe gracefully.""" def __init__(self, stream) -> None: self._stream = stream def __enter__(self) -> "StreamWriter": return self def __exit__( self, exc_type: type[BaseException] | None, exc_val: BaseException | None, exc_tb: TracebackType | None, ) -> bool: """Clean up, suppressing BrokenPipeError on shutdown.""" self._stream.close() # Suppress BrokenPipeError (client disconnected) # This is expected behavior, not an error if exc_type is BrokenPipeError: return True # Exception suppressed return False # Propagate all other exceptions ``` ### Pattern 6: Streaming with Accumulated State Maintain both incremental chunks and accumulated state during streaming. ```python from collections.abc import Generator from dataclasses import dataclass, field @dataclass class StreamingResult: """Accumulated streaming result.""" chunks: list[str] = field(default_factory=list) _finalized: bool = False @property def content(self) -> str: """Get accumulated content.""" return "".join(self.chunks) def add_chunk(self, chunk: str) -> None: """Add chunk to accumulator.""" if self._finalized: raise RuntimeError("Cannot add to finalized result") self.chunks.append(chunk) def finalize(self) -> str: """Mark stream complete and return content.""" self._finalized = True return self.content def stream_with_accumulation( response: StreamingResponse, ) -> Generator[tuple[str, str], None, str]: """Stream response while accumulating content. Yields: Tuple of (accumulated_content, new_chunk) for each chunk. Returns: Final accumulated content. """ result = StreamingResult() for chunk in response.iter_content(): result.add_chunk(chunk) yield result.content, chunk return result.finalize() ``` ### Pattern 7: Efficient String Accumulation Avoid O(nΒ²) string concatenation when accumulating. ```python def accumulate_stream(stream) -> str: """Efficiently accumulate stream content.""" # BAD: O(nΒ²) due to string immutability # content = "" # for chunk in stream: # content += chunk # Creates new string each time # GOOD: O(n) with list and join chunks: list[str] = [] for chunk in stream: chunks.append(chunk) return "".join(chunks) # Single allocation ``` ### Pattern 8: Tracking Stream Metrics Measure time-to-first-byte and total streaming time. ```python import time from collections.abc import Generator def stream_with_metrics( response: StreamingResponse, ) -> Generator[str, None, dict]: """Stream response while collecting metrics. Yields: Content chunks. Returns: Metrics dictionary. """ start = time.perf_counter() first_chunk_time: float | None = None chunk_count = 0 total_bytes = 0 for chunk in response.iter_content(): if first_chunk_time is None: first_chunk_time = time.perf_counter() - start chunk_count += 1 total_bytes += len(chunk.encode()) yield chunk total_time = time.perf_counter() - start return { "time_to_first_byte_ms": round((first_chunk_time or 0) * 1000, 2), "total_time_ms": round(total_time * 1000, 2), "chunk_count": chunk_count, "total_bytes": total_bytes, } ``` ### Pattern 9: Managing Multiple Resources with ExitStack Handle a dynamic number of resources cleanly. ```python from contextlib import ExitStack, AsyncExitStack from pathlib import Path def process_files(paths: list[Path]) -> list[str]: """Process multiple files with automatic cleanup.""" results = [] with ExitStack() as stack: # Open all files - they'll all be closed when block exits files = [stack.enter_context(open(p)) for p in paths] for f in files: results.append(f.read()) return results async def process_connections(hosts: list[str]) -> list[dict]: """Process multiple async connections.""" results = [] async with AsyncExitStack() as stack: connections = [ await stack.enter_async_context(connect_to_host(host)) for host in hosts ] for conn in connections: results.append(await conn.fetch_data()) return results ``` ## Best Practices Summary 1. **Always use context managers** - For any resource that needs cleanup 2. **Clean up unconditionally** - `__exit__` runs even on exception 3. **Don't suppress unexpectedly** - Return `False` unless suppression is intentional 4. **Use @contextmanager** - For simple resource patterns 5. **Implement both protocols** - Support `with` and manual management 6. **Use ExitStack** - For dynamic numbers of resources 7. **Accumulate efficiently** - List + join, not string concatenation 8. **Track metrics** - Time-to-first-byte matters for streaming 9. **Document behavior** - Especially exception suppression 10. **Test cleanup paths** - Verify resources are released on errors
πŸ‘0
πŸ‘οΈ0
πŸ€– Auto-discovered
πŸ€–system promptβ€’7 months ago

python-testing-patterns

Implement comprehensive testing strategies with pytest, fixtures,

coding
⭐1
# Python Testing Patterns Comprehensive guide to implementing robust testing strategies in Python using pytest, fixtures, mocking, parameterization, and test-driven development practices. ## When to Use This Skill - Writing unit tests for Python code - Setting up test suites and test infrastructure - Implementing test-driven development (TDD) - Creating integration tests for APIs and services - Mocking external dependencies and services - Testing async code and concurrent operations - Setting up continuous testing in CI/CD - Implementing property-based testing - Testing database operations - Debugging failing tests ## Core Concepts ### 1. Test Types - **Unit Tests**: Test individual functions/classes in isolation - **Integration Tests**: Test interaction between components - **Functional Tests**: Test complete features end-to-end - **Performance Tests**: Measure speed and resource usage ### 2. Test Structure (AAA Pattern) - **Arrange**: Set up test data and preconditions - **Act**: Execute the code under test - **Assert**: Verify the results ### 3. Test Coverage - Measure what code is exercised by tests - Identify untested code paths - Aim for meaningful coverage, not just high percentages ### 4. Test Isolation - Tests should be independent - No shared state between tests - Each test should clean up after itself ## Quick Start ```python # test_example.py def add(a, b): return a + b def test_add(): """Basic test example.""" result = add(2, 3) assert result == 5 def test_add_negative(): """Test with negative numbers.""" assert add(-1, 1) == 0 # Run with: pytest test_example.py ``` ## Fundamental Patterns ### Pattern 1: Basic pytest Tests ```python # test_calculator.py import pytest class Calculator: """Simple calculator for testing.""" def add(self, a: float, b: float) -> float: return a + b def subtract(self, a: float, b: float) -> float: return a - b def multiply(self, a: float, b: float) -> float: return a * b def divide(self, a: float, b: float) -> float: if b == 0: raise ValueError("Cannot divide by zero") return a / b def test_addition(): """Test addition.""" calc = Calculator() assert calc.add(2, 3) == 5 assert calc.add(-1, 1) == 0 assert calc.add(0, 0) == 0 def test_subtraction(): """Test subtraction.""" calc = Calculator() assert calc.subtract(5, 3) == 2 assert calc.subtract(0, 5) == -5 def test_multiplication(): """Test multiplication.""" calc = Calculator() assert calc.multiply(3, 4) == 12 assert calc.multiply(0, 5) == 0 def test_division(): """Test division.""" calc = Calculator() assert calc.divide(6, 3) == 2 assert calc.divide(5, 2) == 2.5 def test_division_by_zero(): """Test division by zero raises error.""" calc = Calculator() with pytest.raises(ValueError, match="Cannot divide by zero"): calc.divide(5, 0) ``` ### Pattern 2: Fixtures for Setup and Teardown ```python # test_database.py import pytest from typing import Generator class Database: """Simple database class.""" def __init__(self, connection_string: str): self.connection_string = connection_string self.connected = False def connect(self): """Connect to database.""" self.connected = True def disconnect(self): """Disconnect from database.""" self.connected = False def query(self, sql: str) -> list: """Execute query.""" if not self.connected: raise RuntimeError("Not connected") return [{"id": 1, "name": "Test"}] @pytest.fixture def db() -> Generator[Database, None, None]: """Fixture that provides connected database.""" # Setup database = Database("sqlite:///:memory:") database.connect() # Provide to test yield database # Teardown database.disconnect() def test_database_query(db): """Test database query with fixture.""" results = db.query("SELECT * FROM users") assert len(results) == 1 assert results[0]["name"] == "Test" @pytest.fixture(scope="session") def app_config(): """Session-scoped fixture - created once per test session.""" return { "database_url": "postgresql://localhost/test", "api_key": "test-key", "debug": True } @pytest.fixture(scope="module") def api_client(app_config): """Module-scoped fixture - created once per test module.""" # Setup expensive resource client = {"config": app_config, "session": "active"} yield client # Cleanup client["session"] = "closed" def test_api_client(api_client): """Test using api client fixture.""" assert api_client["session"] == "active" assert api_client["config"]["debug"] is True ``` ### Pattern 3: Parameterized Tests ```python # test_validation.py import pytest def is_valid_email(email: str) -> bool: """Check if email is valid.""" return "@" in email and "." in email.split("@")[1] @pytest.mark.parametrize("email,expected", [ ("user@example.com", True), ("test.user@domain.co.uk", True), ("invalid.email", False), ("@example.com", False), ("user@domain", False), ("", False), ]) def test_email_validation(email, expected): """Test email validation with various inputs.""" assert is_valid_email(email) == expected @pytest.mark.parametrize("a,b,expected", [ (2, 3, 5), (0, 0, 0), (-1, 1, 0), (100, 200, 300), (-5, -5, -10), ]) def test_addition_parameterized(a, b, expected): """Test addition with multiple parameter sets.""" from test_calculator import Calculator calc = Calculator() assert calc.add(a, b) == expected # Using pytest.param for special cases @pytest.mark.parametrize("value,expected", [ pytest.param(1, True, id="positive"), pytest.param(0, False, id="zero"), pytest.param(-1, False, id="negative"), ]) def test_is_positive(value, expected): """Test with custom test IDs.""" assert (value > 0) == expected ``` ### Pattern 4: Mocking with unittest.mock ```python # test_api_client.py import pytest from unittest.mock import Mock, patch, MagicMock import requests class APIClient: """Simple API client.""" def __init__(self, base_url: str): self.base_url = base_url def get_user(self, user_id: int) -> dict: """Fetch user from API.""" response = requests.get(f"{self.base_url}/users/{user_id}") response.raise_for_status() return response.json() def create_user(self, data: dict) -> dict: """Create new user.""" response = requests.post(f"{self.base_url}/users", json=data) response.raise_for_status() return response.json() def test_get_user_success(): """Test successful API call with mock.""" client = APIClient("https://api.example.com") mock_response = Mock() mock_response.json.return_value = {"id": 1, "name": "John Doe"} mock_response.raise_for_status.return_value = None with patch("requests.get", return_value=mock_response) as mock_get: user = client.get_user(1) assert user["id"] == 1 assert user["name"] == "John Doe" mock_get.assert_called_once_with("https://api.example.com/users/1") def test_get_user_not_found(): """Test API call with 404 error.""" client = APIClient("https://api.example.com") mock_response = Mock() mock_response.raise_for_status.side_effect = requests.HTTPError("404 Not Found") with patch("requests.get", return_value=mock_response): with pytest.raises(requests.HTTPError): client.get_user(999) @patch("requests.post") def test_create_user(mock_post): """Test user creation with decorator syntax.""" client = APIClient("https://api.example.com") mock_post.return_value.json.return_value = {"id": 2, "name": "Jane Doe"} mock_post.return_value.raise_for_status.return_value = None user_data = {"name": "Jane Doe", "email": "jane@example.com"} result = client.create_user(user_data) assert result["id"] == 2 mock_post.assert_called_once() call_args = mock_post.call_args assert call_args.kwargs["json"] == user_data ``` ### Pattern 5: Testing Exceptions ```python # test_exceptions.py import pytest def divide(a: float, b: float) -> float: """Divide a by b.""" if b == 0: raise ZeroDivisionError("Division by zero") if not isinstance(a, (int, float)) or not isinstance(b, (int, float)): raise TypeError("Arguments must be numbers") return a / b def test_zero_division(): """Test exception is raised for division by zero.""" with pytest.raises(ZeroDivisionError): divide(10, 0) def test_zero_division_with_message(): """Test exception message.""" with pytest.raises(ZeroDivisionError, match="Division by zero"): divide(5, 0) def test_type_error(): """Test type error exception.""" with pytest.raises(TypeError, match="must be numbers"): divide("10", 5) def test_exception_info(): """Test accessing exception info.""" with pytest.raises(ValueError) as exc_info: int("not a number") assert "invalid literal" in str(exc_info.value) ``` ## Advanced Patterns ### Pattern 6: Testing Async Code ```python # test_async.py import pytest import asyncio async def fetch_data(url: str) -> dict: """Fetch data asynchronously.""" await asyncio.sleep(0.1) return {"url": url, "data": "result"} @pytest.mark.asyncio async def test_fetch_data(): """Test async function.""" result = await fetch_data("https://api.example.com") assert result["url"] == "https://api.example.com" assert "data" in result @pytest.mark.asyncio async def test_concurrent_fetches(): """Test concurrent async operations.""" urls = ["url1", "url2", "url3"] tasks = [fetch_data(url) for url in urls] results = await asyncio.gather(*tasks) assert len(results) == 3 assert all("data" in r for r in results) @pytest.fixture async def async_client(): """Async fixture.""" client = {"connected": True} yield client client["connected"] = False @pytest.mark.asyncio async def test_with_async_fixture(async_client): """Test using async fixture.""" assert async_client["connected"] is True ``` ### Pattern 7: Monkeypatch for Testing ```python # test_environment.py import os import pytest def get_database_url() -> str: """Get database URL from environment.""" return os.environ.get("DATABASE_URL", "sqlite:///:memory:") def test_database_url_default(): """Test default database URL.""" # Will use actual environment variable if set url = get_database_url() assert url def test_database_url_custom(monkeypatch): """Test custom database URL with monkeypatch.""" monkeypatch.setenv("DATABASE_URL", "postgresql://localhost/test") assert get_database_url() == "postgresql://localhost/test" def test_database_url_not_set(monkeypatch): """Test when env var is not set.""" monkeypatch.delenv("DATABASE_URL", raising=False) assert get_database_url() == "sqlite:///:memory:" class Config: """Configuration class.""" def __init__(self): self.api_key = "production-key" def get_api_key(self): return self.api_key def test_monkeypatch_attribute(monkeypatch): """Test monkeypatching object attributes.""" config = Config() monkeypatch.setattr(config, "api_key", "test-key") assert config.get_api_key() == "test-key" ``` ### Pattern 8: Temporary Files and Directories ```python # test_file_operations.py import pytest from pathlib import Path def save_data(filepath: Path, data: str): """Save data to file.""" filepath.write_text(data) def load_data(filepath: Path) -> str: """Load data from file.""" return filepath.read_text() def test_file_operations(tmp_path): """Test file operations with temporary directory.""" # tmp_path is a pathlib.Path object test_file = tmp_path / "test_data.txt" # Save data save_data(test_file, "Hello, World!") # Verify file exists assert test_file.exists() # Load and verify data data = load_data(test_file) assert data == "Hello, World!" def test_multiple_files(tmp_path): """Test with multiple temporary files.""" files = { "file1.txt": "Content 1", "file2.txt": "Content 2", "file3.txt": "Content 3" } for filename, content in files.items(): filepath = tmp_path / filename save_data(filepath, content) # Verify all files created assert len(list(tmp_path.iterdir())) == 3 # Verify contents for filename, expected_content in files.items(): filepath = tmp_path / filename assert load_data(filepath) == expected_content ``` ### Pattern 9: Custom Fixtures and Conftest ```python # conftest.py """Shared fixtures for all tests.""" import pytest @pytest.fixture(scope="session") def database_url(): """Provide database URL for all tests.""" return "postgresql://localhost/test_db" @pytest.fixture(autouse=True) def reset_database(database_url): """Auto-use fixture that runs before each test.""" # Setup: Clear database print(f"Clearing database: {database_url}") yield # Teardown: Clean up print("Test completed") @pytest.fixture def sample_user(): """Provide sample user data.""" return { "id": 1, "name": "Test User", "email": "test@example.com" } @pytest.fixture def sample_users(): """Provide list of sample users.""" return [ {"id": 1, "name": "User 1"}, {"id": 2, "name": "User 2"}, {"id": 3, "name": "User 3"}, ] # Parametrized fixture @pytest.fixture(params=["sqlite", "postgresql", "mysql"]) def db_backend(request): """Fixture that runs tests with different database backends.""" return request.param def test_with_db_backend(db_backend): """This test will run 3 times with different backends.""" print(f"Testing with {db_backend}") assert db_backend in ["sqlite", "postgresql", "mysql"] ``` ### Pattern 10: Property-Based Testing ```python # test_properties.py from hypothesis import given, strategies as st import pytest def reverse_string(s: str) -> str: """Reverse a string.""" return s[::-1] @given(st.text()) def test_reverse_twice_is_original(s): """Property: reversing twice returns original.""" assert reverse_string(reverse_string(s)) == s @given(st.text()) def test_reverse_length(s): """Property: reversed string has same length.""" assert len(reverse_string(s)) == len(s) @given(st.integers(), st.integers()) def test_addition_commutative(a, b): """Property: addition is commutative.""" assert a + b == b + a @given(st.lists(st.integers())) def test_sorted_list_properties(lst): """Property: sorted list is ordered.""" sorted_lst = sorted(lst) # Same length assert len(sorted_lst) == len(lst) # All elements present assert set(sorted_lst) == set(lst) # Is ordered for i in range(len(sorted_lst) - 1): assert sorted_lst[i] <= sorted_lst[i + 1] ``` ## Test Design Principles ### One Behavior Per Test Each test should verify exactly one behavior. This makes failures easy to diagnose and tests easy to maintain. ```python # BAD - testing multiple behaviors def test_user_service(): user = service.create_user(data) assert user.id is not None assert user.email == data["email"] updated = service.update_user(user.id, {"name": "New"}) assert updated.name == "New" # GOOD - focused tests def test_create_user_assigns_id(): user = service.create_user(data) assert user.id is not None def test_create_user_stores_email(): user = service.create_user(data) assert user.email == data["email"] def test_update_user_changes_name(): user = service.create_user(data) updated = service.update_user(user.id, {"name": "New"}) assert updated.name == "New" ``` ### Test Error Paths Always test failure cases, not just happy paths. ```python def test_get_user_raises_not_found(): with pytest.raises(UserNotFoundError) as exc_info: service.get_user("nonexistent-id") assert "nonexistent-id" in str(exc_info.value) def test_create_user_rejects_invalid_email(): with pytest.raises(ValueError, match="Invalid email format"): service.create_user({"email": "not-an-email"}) ``` ## Testing Best Practices ### Test Organization ```python # tests/ # __init__.py # conftest.py # Shared fixtures # test_unit/ # Unit tests # test_models.py # test_utils.py # test_integration/ # Integration tests # test_api.py # test_database.py # test_e2e/ # End-to-end tests # test_workflows.py ``` ### Test Naming Convention A common pattern: `test_<unit>_<scenario>_<expected_outcome>`. Adapt to your team's preferences. ```python # Pattern: test_<unit>_<scenario>_<expected> def test_create_user_with_valid_data_returns_user(): ... def test_create_user_with_duplicate_email_raises_conflict(): ... def test_get_user_with_unknown_id_returns_none(): ... # Good test names - clear and descriptive def test_user_creation_with_valid_data(): """Clear name describes what is being tested.""" pass def test_login_fails_with_invalid_password(): """Name describes expected behavior.""" pass def test_api_returns_404_for_missing_resource(): """Specific about inputs and expected outcomes.""" pass # Bad test names - avoid these def test_1(): # Not descriptive pass def test_user(): # Too vague pass def test_function(): # Doesn't explain what's tested pass ``` ### Testing Retry Behavior Verify that retry logic works correctly using mock side effects. ```python from unittest.mock import Mock def test_retries_on_transient_error(): """Test that service retries on transient failures.""" client = Mock() # Fail twice, then succeed client.request.side_effect = [ ConnectionError("Failed"), ConnectionError("Failed"), {"status": "ok"}, ] service = ServiceWithRetry(client, max_retries=3) result = service.fetch() assert result == {"status": "ok"} assert client.request.call_count == 3 def test_gives_up_after_max_retries(): """Test that service stops retrying after max attempts.""" client = Mock() client.request.side_effect = ConnectionError("Failed") service = ServiceWithRetry(client, max_retries=3) with pytest.raises(ConnectionError): service.fetch() assert client.request.call_count == 3 def test_does_not_retry_on_permanent_error(): """Test that permanent errors are not retried.""" client = Mock() client.request.side_effect = ValueError("Invalid input") service = ServiceWithRetry(client, max_retries=3) with pytest.raises(ValueError): service.fetch() # Only called once - no retry for ValueError assert client.request.call_count == 1 ``` ### Mocking Time with Freezegun Use freezegun
πŸ‘0
πŸ‘οΈ0
πŸ€– Auto-discovered
πŸ€–system promptβ€’7 months ago

bash-defensive-patterns

Master defensive Bash programming techniques for production-grade

coding
⭐1
# Bash Defensive Patterns Comprehensive guidance for writing production-ready Bash scripts using defensive programming techniques, error handling, and safety best practices to prevent common pitfalls and ensure reliability. ## When to Use This Skill - Writing production automation scripts - Building CI/CD pipeline scripts - Creating system administration utilities - Developing error-resilient deployment automation - Writing scripts that must handle edge cases safely - Building maintainable shell script libraries - Implementing comprehensive logging and monitoring - Creating scripts that must work across different platforms ## Core Defensive Principles ### 1. Strict Mode Enable bash strict mode at the start of every script to catch errors early. ```bash #!/bin/bash set -Eeuo pipefail # Exit on error, unset variables, pipe failures ``` **Key flags:** - `set -E`: Inherit ERR trap in functions - `set -e`: Exit on any error (command returns non-zero) - `set -u`: Exit on undefined variable reference - `set -o pipefail`: Pipe fails if any command fails (not just last) ### 2. Error Trapping and Cleanup Implement proper cleanup on script exit or error. ```bash #!/bin/bash set -Eeuo pipefail trap 'echo "Error on line $LINENO"' ERR trap 'echo "Cleaning up..."; rm -rf "$TMPDIR"' EXIT TMPDIR=$(mktemp -d) # Script code here ``` ### 3. Variable Safety Always quote variables to prevent word splitting and globbing issues. ```bash # Wrong - unsafe cp $source $dest # Correct - safe cp "$source" "$dest" # Required variables - fail with message if unset : "${REQUIRED_VAR:?REQUIRED_VAR is not set}" ``` ### 4. Array Handling Use arrays safely for complex data handling. ```bash # Safe array iteration declare -a items=("item 1" "item 2" "item 3") for item in "${items[@]}"; do echo "Processing: $item" done # Reading output into array safely mapfile -t lines < <(some_command) readarray -t numbers < <(seq 1 10) ``` ### 5. Conditional Safety Use `[[ ]]` for Bash-specific features, `[ ]` for POSIX. ```bash # Bash - safer if [[ -f "$file" && -r "$file" ]]; then content=$(<"$file") fi # POSIX - portable if [ -f "$file" ] && [ -r "$file" ]; then content=$(cat "$file") fi # Test for existence before operations if [[ -z "${VAR:-}" ]]; then echo "VAR is not set or is empty" fi ``` ## Fundamental Patterns ### Pattern 1: Safe Script Directory Detection ```bash #!/bin/bash set -Eeuo pipefail # Correctly determine script directory SCRIPT_DIR="$(cd -- "$(dirname -- "${BASH_SOURCE[0]}")" && pwd -P)" SCRIPT_NAME="$(basename -- "${BASH_SOURCE[0]}")" echo "Script location: $SCRIPT_DIR/$SCRIPT_NAME" ``` ### Pattern 2: Comprehensive Function Templat ```bash #!/bin/bash set -Eeuo pipefail # Prefix for functions: handle_*, process_*, check_*, validate_* # Include documentation and error handling validate_file() { local -r file="$1" local -r message="${2:-File not found: $file}" if [[ ! -f "$file" ]]; then echo "ERROR: $message" >&2 return 1 fi return 0 } process_files() { local -r input_dir="$1" local -r output_dir="$2" # Validate inputs [[ -d "$input_dir" ]] || { echo "ERROR: input_dir not a directory" >&2; return 1; } # Create output directory if needed mkdir -p "$output_dir" || { echo "ERROR: Cannot create output_dir" >&2; return 1; } # Process files safely while IFS= read -r -d '' file; do echo "Processing: $file" # Do work done < <(find "$input_dir" -maxdepth 1 -type f -print0) return 0 } ``` ### Pattern 3: Safe Temporary File Handling ```bash #!/bin/bash set -Eeuo pipefail trap 'rm -rf -- "$TMPDIR"' EXIT # Create temporary directory TMPDIR=$(mktemp -d) || { echo "ERROR: Failed to create temp directory" >&2; exit 1; } # Create temporary files in directory TMPFILE1="$TMPDIR/temp1.txt" TMPFILE2="$TMPDIR/temp2.txt" # Use temporary files touch "$TMPFILE1" "$TMPFILE2" echo "Temp files created in: $TMPDIR" ``` ### Pattern 4: Robust Argument Parsing ```bash #!/bin/bash set -Eeuo pipefail # Default values VERBOSE=false DRY_RUN=false OUTPUT_FILE="" THREADS=4 usage() { cat <<EOF Usage: $0 [OPTIONS] Options: -v, --verbose Enable verbose output -d, --dry-run Run without making changes -o, --output FILE Output file path -j, --jobs NUM Number of parallel jobs -h, --help Show this help message EOF exit "${1:-0}" } # Parse arguments while [[ $# -gt 0 ]]; do case "$1" in -v|--verbose) VERBOSE=true shift ;; -d|--dry-run) DRY_RUN=true shift ;; -o|--output) OUTPUT_FILE="$2" shift 2 ;; -j|--jobs) THREADS="$2" shift 2 ;; -h|--help) usage 0 ;; --) shift break ;; *) echo "ERROR: Unknown option: $1" >&2 usage 1 ;; esac done # Validate required arguments [[ -n "$OUTPUT_FILE" ]] || { echo "ERROR: -o/--output is required" >&2; usage 1; } ``` ### Pattern 5: Structured Logging ```bash #!/bin/bash set -Eeuo pipefail # Logging functions log_info() { echo "[$(date +'%Y-%m-%d %H:%M:%S')] INFO: $*" >&2 } log_warn() { echo "[$(date +'%Y-%m-%d %H:%M:%S')] WARN: $*" >&2 } log_error() { echo "[$(date +'%Y-%m-%d %H:%M:%S')] ERROR: $*" >&2 } log_debug() { if [[ "${DEBUG:-0}" == "1" ]]; then echo "[$(date +'%Y-%m-%d %H:%M:%S')] DEBUG: $*" >&2 fi } # Usage log_info "Starting script" log_debug "Debug information" log_warn "Warning message" log_error "Error occurred" ``` ### Pattern 6: Process Orchestration with Signals ```bash #!/bin/bash set -Eeuo pipefail # Track background processes PIDS=() cleanup() { log_info "Shutting down..." # Terminate all background processes for pid in "${PIDS[@]}"; do if kill -0 "$pid" 2>/dev/null; then kill -TERM "$pid" 2>/dev/null || true fi done # Wait for graceful shutdown for pid in "${PIDS[@]}"; do wait "$pid" 2>/dev/null || true done } trap cleanup SIGTERM SIGINT # Start background tasks background_task & PIDS+=($!) another_task & PIDS+=($!) # Wait for all background processes wait ``` ### Pattern 7: Safe File Operations ```bash #!/bin/bash set -Eeuo pipefail # Use -i flag to move safely without overwriting safe_move() { local -r source="$1" local -r dest="$2" if [[ ! -e "$source" ]]; then echo "ERROR: Source does not exist: $source" >&2 return 1 fi if [[ -e "$dest" ]]; then echo "ERROR: Destination already exists: $dest" >&2 return 1 fi mv "$source" "$dest" } # Safe directory cleanup safe_rmdir() { local -r dir="$1" if [[ ! -d "$dir" ]]; then echo "ERROR: Not a directory: $dir" >&2 return 1 fi # Use -I flag to prompt before rm (BSD/GNU compatible) rm -rI -- "$dir" } # Atomic file writes atomic_write() { local -r target="$1" local -r tmpfile tmpfile=$(mktemp) || return 1 # Write to temp file first cat > "$tmpfile" # Atomic rename mv "$tmpfile" "$target" } ``` ### Pattern 8: Idempotent Script Design ```bash #!/bin/bash set -Eeuo pipefail # Check if resource already exists ensure_directory() { local -r dir="$1" if [[ -d "$dir" ]]; then log_info "Directory already exists: $dir" return 0 fi mkdir -p "$dir" || { log_error "Failed to create directory: $dir" return 1 } log_info "Created directory: $dir" } # Ensure configuration state ensure_config() { local -r config_file="$1" local -r default_value="$2" if [[ ! -f "$config_file" ]]; then echo "$default_value" > "$config_file" log_info "Created config: $config_file" fi } # Rerunning script multiple times should be safe ensure_directory "/var/cache/myapp" ensure_config "/etc/myapp/config" "DEBUG=false" ``` ### Pattern 9: Safe Command Substitution ```bash #!/bin/bash set -Eeuo pipefail # Use $() instead of backticks name=$(<"$file") # Modern, safe variable assignment from file output=$(command -v python3) # Get command location safely # Handle command substitution with error checking result=$(command -v node) || { log_error "node command not found" return 1 } # For multiple lines mapfile -t lines < <(grep "pattern" "$file") # NUL-safe iteration while IFS= read -r -d '' file; do echo "Processing: $file" done < <(find /path -type f -print0) ``` ### Pattern 10: Dry-Run Support ```bash #!/bin/bash set -Eeuo pipefail DRY_RUN="${DRY_RUN:-false}" run_cmd() { if [[ "$DRY_RUN" == "true" ]]; then echo "[DRY RUN] Would execute: $*" return 0 fi "$@" } # Usage run_cmd cp "$source" "$dest" run_cmd rm "$file" run_cmd chown "$owner" "$target" ``` ## Advanced Defensive Techniques ### Named Parameters Pattern ```bash #!/bin/bash set -Eeuo pipefail process_data() { local input_file="" local output_dir="" local format="json" # Parse named parameters while [[ $# -gt 0 ]]; do case "$1" in --input=*) input_file="${1#*=}" ;; --output=*) output_dir="${1#*=}" ;; --format=*) format="${1#*=}" ;; *) echo "ERROR: Unknown parameter: $1" >&2 return 1 ;; esac shift done # Validate required parameters [[ -n "$input_file" ]] || { echo "ERROR: --input is required" >&2; return 1; } [[ -n "$output_dir" ]] || { echo "ERROR: --output is required" >&2; return 1; } } ``` ### Dependency Checking ```bash #!/bin/bash set -Eeuo pipefail check_dependencies() { local -a missing_deps=() local -a required=("jq" "curl" "git") for cmd in "${required[@]}"; do if ! command -v "$cmd" &>/dev/null; then missing_deps+=("$cmd") fi done if [[ ${#missing_deps[@]} -gt 0 ]]; then echo "ERROR: Missing required commands: ${missing_deps[*]}" >&2 return 1 fi } check_dependencies ``` ## Best Practices Summary 1. **Always use strict mode** - `set -Eeuo pipefail` 2. **Quote all variables** - `"$variable"` prevents word splitting 3. **Use [[]] conditionals** - More robust than [ ] 4. **Implement error trapping** - Catch and handle errors gracefully 5. **Validate all inputs** - Check file existence, permissions, formats 6. **Use functions for reusability** - Prefix with meaningful names 7. **Implement structured logging** - Include timestamps and levels 8. **Support dry-run mode** - Allow users to preview changes 9. **Handle temporary files safely** - Use mktemp, cleanup with trap 10. **Design for idempotency** - Scripts should be safe to rerun 11. **Document requirements** - List dependencies and minimum versions 12. **Test error paths** - Ensure error handling works correctly 13. **Use `command -v`** - Safer than `which` for checking executables 14. **Prefer printf over echo** - More predictable across systems ## Resources - **Bash Strict Mode**: http://redsymbol.net/articles/unofficial-bash-strict-mode/ - **Google Shell Style Guide**: https://google.github.io/styleguide/shellguide.html - **Defensive BASH Programming**: https://www.lifepipe.net/
πŸ‘0
πŸ‘οΈ0
πŸ€– Auto-discovered
πŸ€–system promptβ€’7 months ago

bats-testing-patterns

Master Bash Automated Testing System (Bats) for comprehensive shell

coding
⭐1
# Bats Testing Patterns Comprehensive guidance for writing comprehensive unit tests for shell scripts using Bats (Bash Automated Testing System), including test patterns, fixtures, and best practices for production-grade shell testing. ## When to Use This Skill - Writing unit tests for shell scripts - Implementing test-driven development (TDD) for scripts - Setting up automated testing in CI/CD pipelines - Testing edge cases and error conditions - Validating behavior across different shell environments - Building maintainable test suites for scripts - Creating fixtures for complex test scenarios - Testing multiple shell dialects (bash, sh, dash) ## Bats Fundamentals ### What is Bats? Bats (Bash Automated Testing System) is a TAP (Test Anything Protocol) compliant testing framework for shell scripts that provides: - Simple, natural test syntax - TAP output format compatible with CI systems - Fixtures and setup/teardown support - Assertion helpers - Parallel test execution ### Installation ```bash # macOS with Homebrew brew install bats-core # Ubuntu/Debian git clone https://github.com/bats-core/bats-core.git cd bats-core ./install.sh /usr/local # From npm (Node.js) npm install --global bats # Verify installation bats --version ``` ### File Structure ``` project/ β”œβ”€β”€ bin/ β”‚ β”œβ”€β”€ script.sh β”‚ └── helper.sh β”œβ”€β”€ tests/ β”‚ β”œβ”€β”€ test_script.bats β”‚ β”œβ”€β”€ test_helper.sh β”‚ β”œβ”€β”€ fixtures/ β”‚ β”‚ β”œβ”€β”€ input.txt β”‚ β”‚ └── expected_output.txt β”‚ └── helpers/ β”‚ └── mocks.bash └── README.md ``` ## Basic Test Structure ### Simple Test File ```bash #!/usr/bin/env bats # Load test helper if present load test_helper # Setup runs before each test setup() { export TMPDIR=$(mktemp -d) } # Teardown runs after each test teardown() { rm -rf "$TMPDIR" } # Test: simple assertion @test "Function returns 0 on success" { run my_function "input" [ "$status" -eq 0 ] } # Test: output verification @test "Function outputs correct result" { run my_function "test" [ "$output" = "expected output" ] } # Test: error handling @test "Function returns 1 on missing argument" { run my_function [ "$status" -eq 1 ] } ``` ## Assertion Patterns ### Exit Code Assertions ```bash #!/usr/bin/env bats @test "Command succeeds" { run true [ "$status" -eq 0 ] } @test "Command fails as expected" { run false [ "$status" -ne 0 ] } @test "Command returns specific exit code" { run my_function --invalid [ "$status" -eq 127 ] } @test "Can capture command result" { run echo "hello" [ $status -eq 0 ] [ "$output" = "hello" ] } ``` ### Output Assertions ```bash #!/usr/bin/env bats @test "Output matches string" { result=$(echo "hello world") [ "$result" = "hello world" ] } @test "Output contains substring" { result=$(echo "hello world") [[ "$result" == *"world"* ]] } @test "Output matches pattern" { result=$(date +%Y) [[ "$result" =~ ^[0-9]{4}$ ]] } @test "Multi-line output" { run printf "line1\nline2\nline3" [ "$output" = "line1 line2 line3" ] } @test "Lines variable contains output" { run printf "line1\nline2\nline3" [ "${lines[0]}" = "line1" ] [ "${lines[1]}" = "line2" ] [ "${lines[2]}" = "line3" ] } ``` ### File Assertions ```bash #!/usr/bin/env bats @test "File is created" { [ ! -f "$TMPDIR/output.txt" ] my_function > "$TMPDIR/output.txt" [ -f "$TMPDIR/output.txt" ] } @test "File contents match expected" { my_function > "$TMPDIR/output.txt" [ "$(cat "$TMPDIR/output.txt")" = "expected content" ] } @test "File is readable" { touch "$TMPDIR/test.txt" [ -r "$TMPDIR/test.txt" ] } @test "File has correct permissions" { touch "$TMPDIR/test.txt" chmod 644 "$TMPDIR/test.txt" [ "$(stat -f %OLp "$TMPDIR/test.txt")" = "644" ] } @test "File size is correct" { echo -n "12345" > "$TMPDIR/test.txt" [ "$(wc -c < "$TMPDIR/test.txt")" -eq 5 ] } ``` ## Setup and Teardown Patterns ### Basic Setup and Teardown ```bash #!/usr/bin/env bats setup() { # Create test directory TEST_DIR=$(mktemp -d) export TEST_DIR # Source script under test source "${BATS_TEST_DIRNAME}/../bin/script.sh" } teardown() { # Clean up temporary directory rm -rf "$TEST_DIR" } @test "Test using TEST_DIR" { touch "$TEST_DIR/file.txt" [ -f "$TEST_DIR/file.txt" ] } ``` ### Setup with Resources ```bash #!/usr/bin/env bats setup() { # Create directory structure mkdir -p "$TMPDIR/data/input" mkdir -p "$TMPDIR/data/output" # Create test fixtures echo "line1" > "$TMPDIR/data/input/file1.txt" echo "line2" > "$TMPDIR/data/input/file2.txt" # Initialize environment export DATA_DIR="$TMPDIR/data" export INPUT_DIR="$DATA_DIR/input" export OUTPUT_DIR="$DATA_DIR/output" } teardown() { rm -rf "$TMPDIR/data" } @test "Processes input files" { run my_process_script "$INPUT_DIR" "$OUTPUT_DIR" [ "$status" -eq 0 ] [ -f "$OUTPUT_DIR/file1.txt" ] } ``` ### Global Setup/Teardown ```bash #!/usr/bin/env bats # Load shared setup from test_helper.sh load test_helper # setup_file runs once before all tests setup_file() { export SHARED_RESOURCE=$(mktemp -d) echo "Expensive setup" > "$SHARED_RESOURCE/data.txt" } # teardown_file runs once after all tests teardown_file() { rm -rf "$SHARED_RESOURCE" } @test "First test uses shared resource" { [ -f "$SHARED_RESOURCE/data.txt" ] } @test "Second test uses shared resource" { [ -d "$SHARED_RESOURCE" ] } ``` ## Mocking and Stubbing Patterns ### Function Mocking ```bash #!/usr/bin/env bats # Mock external command my_external_tool() { echo "mocked output" return 0 } @test "Function uses mocked tool" { export -f my_external_tool run my_function [[ "$output" == *"mocked output"* ]] } ``` ### Command Stubbing ```bash #!/usr/bin/env bats setup() { # Create stub directory STUBS_DIR="$TMPDIR/stubs" mkdir -p "$STUBS_DIR" # Add to PATH export PATH="$STUBS_DIR:$PATH" } create_stub() { local cmd="$1" local output="$2" local code="${3:-0}" cat > "$STUBS_DIR/$cmd" <<EOF #!/bin/bash echo "$output" exit $code EOF chmod +x "$STUBS_DIR/$cmd" } @test "Function works with stubbed curl" { create_stub curl "{ \"status\": \"ok\" }" 0 run my_api_function [ "$status" -eq 0 ] } ``` ### Variable Stubbing ```bash #!/usr/bin/env bats @test "Function handles environment override" { export MY_SETTING="override_value" run my_function [ "$status" -eq 0 ] [[ "$output" == *"override_value"* ]] } @test "Function uses default when var unset" { unset MY_SETTING run my_function [ "$status" -eq 0 ] [[ "$output" == *"default"* ]] } ``` ## Fixture Management ### Using Fixture Files ```bash #!/usr/bin/env bats # Fixture directory: tests/fixtures/ setup() { FIXTURES_DIR="${BATS_TEST_DIRNAME}/fixtures" WORK_DIR=$(mktemp -d) export WORK_DIR } teardown() { rm -rf "$WORK_DIR" } @test "Process fixture file" { # Copy fixture to work directory cp "$FIXTURES_DIR/input.txt" "$WORK_DIR/input.txt" # Run function run my_process_function "$WORK_DIR/input.txt" # Compare output diff "$WORK_DIR/output.txt" "$FIXTURES_DIR/expected_output.txt" } ``` ### Dynamic Fixture Generation ```bash #!/usr/bin/env bats generate_fixture() { local lines="$1" local file="$2" for i in $(seq 1 "$lines"); do echo "Line $i content" >> "$file" done } @test "Handle large input file" { generate_fixture 1000 "$TMPDIR/large.txt" run my_function "$TMPDIR/large.txt" [ "$status" -eq 0 ] [ "$(wc -l < "$TMPDIR/large.txt")" -eq 1000 ] } ``` ## Advanced Patterns ### Testing Error Conditions ```bash #!/usr/bin/env bats @test "Function fails with missing file" { run my_function "/nonexistent/file.txt" [ "$status" -ne 0 ] [[ "$output" == *"not found"* ]] } @test "Function fails with invalid input" { run my_function "" [ "$status" -ne 0 ] } @test "Function fails with permission denied" { touch "$TMPDIR/readonly.txt" chmod 000 "$TMPDIR/readonly.txt" run my_function "$TMPDIR/readonly.txt" [ "$status" -ne 0 ] chmod 644 "$TMPDIR/readonly.txt" # Cleanup } @test "Function provides helpful error message" { run my_function --invalid-option [ "$status" -ne 0 ] [[ "$output" == *"Usage:"* ]] } ``` ### Testing with Dependencies ```bash #!/usr/bin/env bats setup() { # Check for required tools if ! command -v jq &>/dev/null; then skip "jq is not installed" fi export SCRIPT="${BATS_TEST_DIRNAME}/../bin/script.sh" } @test "JSON parsing works" { skip_if ! command -v jq &>/dev/null run my_json_parser '{"key": "value"}' [ "$status" -eq 0 ] } ``` ### Testing Shell Compatibility ```bash #!/usr/bin/env bats @test "Script works in bash" { bash "${BATS_TEST_DIRNAME}/../bin/script.sh" arg1 } @test "Script works in sh (POSIX)" { sh "${BATS_TEST_DIRNAME}/../bin/script.sh" arg1 } @test "Script works in dash" { if command -v dash &>/dev/null; then dash "${BATS_TEST_DIRNAME}/../bin/script.sh" arg1 else skip "dash not installed" fi } ``` ### Parallel Execution ```bash #!/usr/bin/env bats @test "Multiple independent operations" { run bash -c 'for i in {1..10}; do my_operation "$i" & done wait' [ "$status" -eq 0 ] } @test "Concurrent file operations" { for i in {1..5}; do my_function "$TMPDIR/file$i" & done wait [ -f "$TMPDIR/file1" ] [ -f "$TMPDIR/file5" ] } ``` ## Test Helper Pattern ### test_helper.sh ```bash #!/usr/bin/env bash # Source script under test export SCRIPT_DIR="${BATS_TEST_DIRNAME%/*}/bin" # Common test utilities assert_file_exists() { if [ ! -f "$1" ]; then echo "Expected file to exist: $1" return 1 fi } assert_file_equals() { local file="$1" local expected="$2" if [ ! -f "$file" ]; then echo "File does not exist: $file" return 1 fi local actual=$(cat "$file") if [ "$actual" != "$expected" ]; then echo "File contents do not match" echo "Expected: $expected" echo "Actual: $actual" return 1 fi } # Create temporary test directory setup_test_dir() { export TEST_DIR=$(mktemp -d) } cleanup_test_dir() { rm -rf "$TEST_DIR" } ``` ## Integration with CI/CD ### GitHub Actions Workflow ```yaml name: Tests on: [push, pull_request] jobs: test: runs-on: ubuntu-latest steps: - uses: actions/checkout@v3 - name: Install Bats run: | npm install --global bats - name: Run Tests run: | bats tests/*.bats - name: Run Tests with Tap Reporter run: | bats tests/*.bats --tap | tee test_output.tap ``` ### Makefile Integration ```makefile .PHONY: test test-verbose test-tap test: bats tests/*.bats test-verbose: bats tests/*.bats --verbose test-tap: bats tests/*.bats --tap test-parallel: bats tests/*.bats --parallel 4 coverage: test # Optional: Generate coverage reports ``` ## Best Practices 1. **Test one thing per test** - Single responsibility principle 2. **Use descriptive test names** - Clearly states what is being tested 3. **Clean up after tests** - Always remove temporary files in teardown 4. **Test both success and failure paths** - Don't just test happy path 5. **Mock external dependencies** - Isolate unit under test 6. **Use fixtures for complex data** - Makes tests more readable 7. **Run tests in CI/CD** - Catch regressions early 8. **Test across shell dialects** - Ensure portability 9. **Keep tests fast** - Run in parallel when possible 10. **Document complex test setup** - Explain unusual patterns ## Resources - **Bats GitHub**: https://github.com/bats-core/bats-core - **Bats Documentation**: https://bats-core.readthedocs.io/ - **TAP Protocol**: https://testanything.org/ - **Test-Driven Development**: https://en.wikipedia.org/wiki/Test-driven_development
πŸ‘0
πŸ‘οΈ0
πŸ€– Auto-discovered