Skip to content

ComeOnOliver/skillshub / skills/aiskillstore/marketplace/0xdarkmatter/python-database-patterns

python-database-patterns

Verified

SQLAlchemy and database patterns for Python. Triggers on: sqlalchemy, database, orm, migration, alembic, async database, connection pool, repository pattern, unit of work.

skill-report.json

View on GitHub
{
  "schema_version": "2.0",
  "meta": {
    "generated_at": "2026-01-16T14:06:30.874Z",
    "slug": "0xdarkmatter-python-database-patterns",
    "source_url": "https://github.com/0xDarkMatter/claude-mods/tree/main/skills/python-database-patterns",
    "source_ref": "main",
    "model": "claude",
    "analysis_version": "3.0.0",
    "source_type": "community",
    "content_hash": "08f9de3f2eab4deb16e7529a2dd41f0b5d26f60267b8c27b046edacd2ad46b9b",
    "tree_hash": "b234d57d21e23b18285f58a7e4aa8d7a0c4d2950b515fd2730e39b7ed254e9a9"
  },
  "skill": {
    "name": "python-database-patterns",
    "description": "SQLAlchemy and database patterns for Python. Triggers on: sqlalchemy, database, orm, migration, alembic, async database, connection pool, repository pattern, unit of work.",
    "summary": "SQLAlchemy and database patterns for Python. Triggers on: sqlalchemy, database, orm, migration, alem...",
    "icon": "🗄️",
    "version": "1.0.0",
    "author": "0xDarkMatter",
    "license": "MIT",
    "category": "data",
    "tags": [
      "SQLAlchemy",
      "database",
      "Python",
      "ORM"
    ],
    "supported_tools": [
      "claude",
      "codex",
      "claude-code"
    ],
    "risk_factors": [
      "external_commands",
      "env_access",
      "scripts",
      "network"
    ]
  },
  "security_audit": {
    "risk_level": "safe",
    "is_blocked": false,
    "safe_to_publish": true,
    "summary": "Documentation-only skill containing educational SQLAlchemy patterns. No executable code, no network calls, no credential access, no malicious patterns detected. Static scanner generated false positives by misidentifying markdown code blocks as executable code.",
    "risk_factor_evidence": [
      {
        "factor": "external_commands",
        "evidence": [
          {
            "file": "references/connection-pooling.md",
            "line_start": 7,
            "line_end": 42
          },
          {
            "file": "references/connection-pooling.md",
            "line_start": 42,
            "line_end": 46
          },
          {
            "file": "references/connection-pooling.md",
            "line_start": 46,
            "line_end": 78
          },
          {
            "file": "references/connection-pooling.md",
            "line_start": 78,
            "line_end": 82
          },
          {
            "file": "references/connection-pooling.md",
            "line_start": 82,
            "line_end": 121
          },
          {
            "file": "references/connection-pooling.md",
            "line_start": 121,
            "line_end": 125
          },
          {
            "file": "references/connection-pooling.md",
            "line_start": 125,
            "line_end": 163
          },
          {
            "file": "references/connection-pooling.md",
            "line_start": 163,
            "line_end": 167
          },
          {
            "file": "references/connection-pooling.md",
            "line_start": 167,
            "line_end": 217
          },
          {
            "file": "references/connection-pooling.md",
            "line_start": 217,
            "line_end": 221
          },
          {
            "file": "references/connection-pooling.md",
            "line_start": 221,
            "line_end": 263
          },
          {
            "file": "references/connection-pooling.md",
            "line_start": 263,
            "line_end": 267
          },
          {
            "file": "references/connection-pooling.md",
            "line_start": 267,
            "line_end": 280
          },
          {
            "file": "references/connection-pooling.md",
            "line_start": 280,
            "line_end": 286
          },
          {
            "file": "references/connection-pooling.md",
            "line_start": 286,
            "line_end": 287
          },
          {
            "file": "references/connection-pooling.md",
            "line_start": 287,
            "line_end": 288
          },
          {
            "file": "references/connection-pooling.md",
            "line_start": 288,
            "line_end": 289
          },
          {
            "file": "references/connection-pooling.md",
            "line_start": 289,
            "line_end": 290
          },
          {
            "file": "references/migrations.md",
            "line_start": 7,
            "line_end": 16
          },
          {
            "file": "references/migrations.md",
            "line_start": 16,
            "line_end": 20
          },
          {
            "file": "references/migrations.md",
            "line_start": 20,
            "line_end": 79
          },
          {
            "file": "references/migrations.md",
            "line_start": 79,
            "line_end": 83
          },
          {
            "file": "references/migrations.md",
            "line_start": 83,
            "line_end": 104
          },
          {
            "file": "references/migrations.md",
            "line_start": 104,
            "line_end": 108
          },
          {
            "file": "references/migrations.md",
            "line_start": 108,
            "line_end": 140
          },
          {
            "file": "references/migrations.md",
            "line_start": 140,
            "line_end": 144
          },
          {
            "file": "references/migrations.md",
            "line_start": 144,
            "line_end": 191
          },
          {
            "file": "references/migrations.md",
            "line_start": 191,
            "line_end": 195
          },
          {
            "file": "references/migrations.md",
            "line_start": 195,
            "line_end": 217
          },
          {
            "file": "references/migrations.md",
            "line_start": 217,
            "line_end": 221
          },
          {
            "file": "references/migrations.md",
            "line_start": 221,
            "line_end": 244
          },
          {
            "file": "references/migrations.md",
            "line_start": 244,
            "line_end": 248
          },
          {
            "file": "references/migrations.md",
            "line_start": 248,
            "line_end": 271
          },
          {
            "file": "references/migrations.md",
            "line_start": 271,
            "line_end": 275
          },
          {
            "file": "references/migrations.md",
            "line_start": 275,
            "line_end": 285
          },
          {
            "file": "references/migrations.md",
            "line_start": 285,
            "line_end": 287
          },
          {
            "file": "references/migrations.md",
            "line_start": 287,
            "line_end": 291
          },
          {
            "file": "references/migrations.md",
            "line_start": 291,
            "line_end": 295
          },
          {
            "file": "references/migrations.md",
            "line_start": 295,
            "line_end": 322
          },
          {
            "file": "references/migrations.md",
            "line_start": 322,
            "line_end": 328
          },
          {
            "file": "references/migrations.md",
            "line_start": 328,
            "line_end": 329
          },
          {
            "file": "references/migrations.md",
            "line_start": 329,
            "line_end": 330
          },
          {
            "file": "references/migrations.md",
            "line_start": 330,
            "line_end": 331
          },
          {
            "file": "references/migrations.md",
            "line_start": 331,
            "line_end": 332
          },
          {
            "file": "references/migrations.md",
            "line_start": 332,
            "line_end": 336
          },
          {
            "file": "references/migrations.md",
            "line_start": 336,
            "line_end": 337
          },
          {
            "file": "references/migrations.md",
            "line_start": 337,
            "line_end": 338
          },
          {
            "file": "references/migrations.md",
            "line_start": 338,
            "line_end": 339
          },
          {
            "file": "references/migrations.md",
            "line_start": 339,
            "line_end": 340
          },
          {
            "file": "references/migrations.md",
            "line_start": 340,
            "line_end": 341
          },
          {
            "file": "references/migrations.md",
            "line_start": 341,
            "line_end": 342
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 7,
            "line_end": 38
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 38,
            "line_end": 42
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 42,
            "line_end": 83
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 83,
            "line_end": 87
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 87,
            "line_end": 117
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 117,
            "line_end": 121
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 121,
            "line_end": 144
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 144,
            "line_end": 148
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 148,
            "line_end": 198
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 198,
            "line_end": 202
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 202,
            "line_end": 224
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 224,
            "line_end": 228
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 228,
            "line_end": 252
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 252,
            "line_end": 256
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 256,
            "line_end": 284
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 284,
            "line_end": 290
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 290,
            "line_end": 291
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 291,
            "line_end": 292
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 292,
            "line_end": 293
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 293,
            "line_end": 294
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 294,
            "line_end": 295
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 295,
            "line_end": 296
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 296,
            "line_end": 297
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 297,
            "line_end": 298
          },
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 298,
            "line_end": 299
          },
          {
            "file": "references/transactions.md",
            "line_start": 7,
            "line_end": 38
          },
          {
            "file": "references/transactions.md",
            "line_start": 38,
            "line_end": 42
          },
          {
            "file": "references/transactions.md",
            "line_start": 42,
            "line_end": 67
          },
          {
            "file": "references/transactions.md",
            "line_start": 67,
            "line_end": 71
          },
          {
            "file": "references/transactions.md",
            "line_start": 71,
            "line_end": 123
          },
          {
            "file": "references/transactions.md",
            "line_start": 123,
            "line_end": 127
          },
          {
            "file": "references/transactions.md",
            "line_start": 127,
            "line_end": 151
          },
          {
            "file": "references/transactions.md",
            "line_start": 151,
            "line_end": 166
          },
          {
            "file": "references/transactions.md",
            "line_start": 166,
            "line_end": 198
          },
          {
            "file": "references/transactions.md",
            "line_start": 198,
            "line_end": 202
          },
          {
            "file": "references/transactions.md",
            "line_start": 202,
            "line_end": 234
          },
          {
            "file": "references/transactions.md",
            "line_start": 234,
            "line_end": 238
          },
          {
            "file": "references/transactions.md",
            "line_start": 238,
            "line_end": 270
          },
          {
            "file": "references/transactions.md",
            "line_start": 270,
            "line_end": 276
          },
          {
            "file": "references/transactions.md",
            "line_start": 276,
            "line_end": 277
          },
          {
            "file": "references/transactions.md",
            "line_start": 277,
            "line_end": 278
          },
          {
            "file": "references/transactions.md",
            "line_start": 278,
            "line_end": 279
          },
          {
            "file": "SKILL.md",
            "line_start": 16,
            "line_end": 39
          },
          {
            "file": "SKILL.md",
            "line_start": 39,
            "line_end": 43
          },
          {
            "file": "SKILL.md",
            "line_start": 43,
            "line_end": 66
          },
          {
            "file": "SKILL.md",
            "line_start": 66,
            "line_end": 70
          },
          {
            "file": "SKILL.md",
            "line_start": 70,
            "line_end": 92
          },
          {
            "file": "SKILL.md",
            "line_start": 92,
            "line_end": 96
          },
          {
            "file": "SKILL.md",
            "line_start": 96,
            "line_end": 127
          },
          {
            "file": "SKILL.md",
            "line_start": 127,
            "line_end": 131
          },
          {
            "file": "SKILL.md",
            "line_start": 131,
            "line_end": 149
          },
          {
            "file": "SKILL.md",
            "line_start": 149,
            "line_end": 155
          },
          {
            "file": "SKILL.md",
            "line_start": 155,
            "line_end": 156
          },
          {
            "file": "SKILL.md",
            "line_start": 156,
            "line_end": 157
          },
          {
            "file": "SKILL.md",
            "line_start": 157,
            "line_end": 158
          },
          {
            "file": "SKILL.md",
            "line_start": 158,
            "line_end": 159
          },
          {
            "file": "SKILL.md",
            "line_start": 159,
            "line_end": 160
          },
          {
            "file": "SKILL.md",
            "line_start": 160,
            "line_end": 161
          },
          {
            "file": "SKILL.md",
            "line_start": 161,
            "line_end": 165
          },
          {
            "file": "SKILL.md",
            "line_start": 165,
            "line_end": 166
          },
          {
            "file": "SKILL.md",
            "line_start": 166,
            "line_end": 167
          },
          {
            "file": "SKILL.md",
            "line_start": 167,
            "line_end": 168
          },
          {
            "file": "SKILL.md",
            "line_start": 168,
            "line_end": 172
          },
          {
            "file": "SKILL.md",
            "line_start": 172,
            "line_end": 179
          },
          {
            "file": "SKILL.md",
            "line_start": 179,
            "line_end": 180
          },
          {
            "file": "SKILL.md",
            "line_start": 180,
            "line_end": 183
          },
          {
            "file": "SKILL.md",
            "line_start": 183,
            "line_end": 184
          }
        ]
      },
      {
        "factor": "env_access",
        "evidence": [
          {
            "file": "references/connection-pooling.md",
            "line_start": 68,
            "line_end": 68
          },
          {
            "file": "references/connection-pooling.md",
            "line_start": 68,
            "line_end": 68
          },
          {
            "file": "references/migrations.md",
            "line_start": 41,
            "line_end": 41
          },
          {
            "file": "references/migrations.md",
            "line_start": 61,
            "line_end": 61
          },
          {
            "file": "references/migrations.md",
            "line_start": 34,
            "line_end": 34
          }
        ]
      },
      {
        "factor": "scripts",
        "evidence": [
          {
            "file": "references/sqlalchemy-async.md",
            "line_start": 8,
            "line_end": 13
          },
          {
            "file": "SKILL.md",
            "line_start": 44,
            "line_end": 48
          }
        ]
      },
      {
        "factor": "network",
        "evidence": [
          {
            "file": "skill-report.json",
            "line_start": 6,
            "line_end": 6
          }
        ]
      }
    ],
    "critical_findings": [],
    "high_findings": [],
    "medium_findings": [],
    "low_findings": [],
    "dangerous_patterns": [],
    "files_scanned": 7,
    "total_lines": 1690,
    "audit_model": "claude",
    "audited_at": "2026-01-16T14:06:30.874Z"
  },
  "content": {
    "user_title": "Implement Python database patterns with SQLAlchemy",
    "value_statement": "Writing database code is complex and error-prone. This skill provides battle-tested SQLAlchemy 2.0 patterns for models, queries, async sessions, transactions, and migrations.",
    "seo_keywords": [
      "SQLAlchemy",
      "Python database patterns",
      "database ORM",
      "async database",
      "Alembic migrations",
      "connection pooling",
      "repository pattern",
      "unit of work",
      "Claude",
      "Claude Code"
    ],
    "actual_capabilities": [
      "Define SQLAlchemy 2.0 models with mapped columns and type annotations",
      "Write queries using select, where, joins, and aggregates",
      "Configure async sessions with asyncpg and aiosqlite",
      "Implement transaction patterns with savepoints and isolation levels",
      "Set up Alembic migrations for schema changes",
      "Configure connection pooling for production workloads"
    ],
    "limitations": [
      "Does not generate complete database layers or model files",
      "Does not create database drivers or custom connectors",
      "Does not provide deployment or infrastructure code",
      "Requires existing knowledge of SQL fundamentals"
    ],
    "use_cases": [
      {
        "target_user": "Python developers new to SQLAlchemy",
        "title": "Learn SQLAlchemy 2.0",
        "description": "Get started with modern SQLAlchemy patterns for declarative models and type-safe queries"
      },
      {
        "target_user": "Backend engineers",
        "title": "Build async API backends",
        "description": "Implement FastAPI endpoints with async database sessions and transaction management"
      },
      {
        "target_user": "DevOps and platform engineers",
        "title": "Manage database schemas",
        "description": "Set up Alembic migrations and configure connection pools for production databases"
      }
    ],
    "prompt_templates": [
      {
        "title": "Define a model",
        "scenario": "Create SQLAlchemy model",
        "prompt": "Create a SQLAlchemy 2.0 model called Product with id, name, price, and category relationship"
      },
      {
        "title": "Write async query",
        "scenario": "Query with async session",
        "prompt": "Write an async function to fetch a user by email with their posts eagerly loaded"
      },
      {
        "title": "Handle transaction",
        "scenario": "Transfer funds atomically",
        "prompt": "Implement a transfer_funds function with pessimistic locking to prevent race conditions"
      },
      {
        "title": "Configure pool",
        "scenario": "Production pool settings",
        "prompt": "Show me how to configure connection pool size, overflow, and pre-ping for a production PostgreSQL async engine"
      }
    ],
    "output_examples": [
      {
        "input": "Create a User model with email unique constraint and a relationship to Post",
        "output": [
          "- Define User class with mapped columns",
          "- Add unique constraint on email field",
          "- Create one-to-many relationship to Post model",
          "- Use back_populates for bidirectional navigation"
        ]
      },
      {
        "input": "How do I set up async SQLAlchemy with FastAPI?",
        "output": [
          "- Create async engine with create_async_engine",
          "- Configure async_sessionmaker with expire_on_commit=False",
          "- Use FastAPI Depends for per-request session lifecycle",
          "- Implement proper commit and rollback handling"
        ]
      },
      {
        "input": "Configure connection pooling for high traffic",
        "output": [
          "- Set pool_size based on CPU cores and expected concurrency",
          "- Configure max_overflow for traffic spikes",
          "- Enable pool_pre_ping to detect stale connections",
          "- Set pool_recycle to prevent timeout issues"
        ]
      }
    ],
    "best_practices": [
      "Use SQLAlchemy 2.0 declarative style with Mapped and mapped_column for type safety",
      "Prefer session.execute with select() over legacy session.query() method",
      "Configure pool_pre_ping=True to catch stale connections before queries",
      "Use eager loading (selectinload/joinedload) to avoid N+1 queries in async contexts"
    ],
    "anti_patterns": [
      "Using lazy loading in async sessions causes MissingGreenlet errors",
      "Calling session.commit() inside session.begin() creates nested transaction issues",
      "Hardcoding database URLs instead of using environment configuration",
      "Skipping pool_recycle causing connections to timeout after long queries"
    ],
    "faq": [
      {
        "question": "What SQLAlchemy versions are supported?",
        "answer": "SQLAlchemy 2.0+ with Python 3.10+. Async requires asyncpg for PostgreSQL or aiosqlite for SQLite."
      },
      {
        "question": "Can I use this with FastAPI?",
        "answer": "Yes. The skill includes FastAPI dependency injection patterns for async database sessions."
      },
      {
        "question": "How do I size the connection pool?",
        "answer": "Start with pool_size=5 and max_overflow=10. Adjust based on concurrent users and query patterns."
      },
      {
        "question": "Is my data safe with these patterns?",
        "answer": "Yes. Patterns include transaction safety, optimistic locking, and proper session lifecycle management."
      },
      {
        "question": "Why are my async queries failing?",
        "answer": "Common causes include lazy loading without eager loading, uncommitted transactions, or connection pool exhaustion."
      },
      {
        "question": "How does this compare to raw SQL?",
        "answer": "SQLAlchemy provides type safety and abstraction while generating optimized SQL. Use text() for raw queries when needed."
      }
    ]
  },
  "file_structure": [
    {
      "name": "assets",
      "type": "dir",
      "path": "assets",
      "children": [
        {
          "name": "alembic.ini.template",
          "type": "file",
          "path": "assets/alembic.ini.template",
          "lines": 60
        }
      ]
    },
    {
      "name": "references",
      "type": "dir",
      "path": "references",
      "children": [
        {
          "name": "connection-pooling.md",
          "type": "file",
          "path": "references/connection-pooling.md",
          "lines": 298
        },
        {
          "name": "migrations.md",
          "type": "file",
          "path": "references/migrations.md",
          "lines": 343
        },
        {
          "name": "sqlalchemy-async.md",
          "type": "file",
          "path": "references/sqlalchemy-async.md",
          "lines": 300
        },
        {
          "name": "transactions.md",
          "type": "file",
          "path": "references/transactions.md",
          "lines": 287
        }
      ]
    },
    {
      "name": "SKILL.md",
      "type": "file",
      "path": "SKILL.md",
      "lines": 185
    }
  ]
}
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 117kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 117kUpdated Claude CodeCodexVerifiedInstall