data-module_skill

Overview

This skill provides an async DataModule for SQLAlchemy-enabled database access with connection pooling and request-scoped AsyncSession injection. It simplifies using AsyncSession in web routes, background jobs, and providers while offering sensible defaults and environment-driven configuration. The module supports auto-creating tables in development, explicit session factories for background work, and guidance for Alembic migrations.

How this skill works

On startup the DataModule configures an async SQLAlchemy engine and a session factory using environment variables prefixed with MYFY_DATA_. HTTP request handlers and providers receive a REQUEST-scoped AsyncSession automatically, ensuring one session per request. For background or singleton services you obtain sessions from the SessionFactory using an async context manager to ensure proper lifecycle and connection pooling.

When to use it

• Building async web routes that need database access with automatic session injection
• Running background tasks or workers that need manual session lifecycle control
• Managing connection pooling and tuning pool parameters for production databases
• Performing schema changes via Alembic while keeping runtime code separate
• Quick development setups where auto-creating tables may speed iterations

Best practices

• Use async DB drivers (asyncpg for PostgreSQL, aiosqlite for SQLite, aiomysql for MySQL)
• Keep sessions request-scoped; do not share sessions across requests or threads
• Use Alembic migrations for production; only enable auto_create_tables in development
• Tune pool_size, max_overflow, and pool_timeout to match expected concurrency
• Enable pool_pre_ping to catch stale connections before use
• Wrap multi-step DB changes in transactions and commit once when ready

Example use cases

• Route handler: inject AsyncSession into HTTP endpoints to run queries and commits
• Background worker: use SessionFactory.session_context() for manual session lifecycle in long-running jobs
• Provider/repository: create a REQUEST-scoped repository that receives an AsyncSession
• Local development: enable auto_create_tables with metadata to bootstrap schemas rapidly
• Migrations: configure Alembic target_metadata from your declarative Base and run autogenerate