Manage Database Session

manage-database-session

This skill covers patterns for managing database sessions and models.

Trigger: Load when working with database interactions.

Quick Reference

Critical Patterns (Summary)

• Create a database session: Use get_db() to create a session for database operations.
• Define a database model: Utilize Base to define your database models.

Critical Patterns (Detailed)

Create a database session

Use get_db() to create a session for database operations, ensuring proper management of database connections.

from apps.server.app.models.database import get_db

async def some_database_operation():
    async with get_db() as session:
        # Perform database operations
        pass

Define a database model

Utilize Base to define your database models, which will serve as the foundation for your ORM mappings.

from apps.server.app.models.database import Base
from sqlalchemy import Column, Integer, String

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)

When to Use

• When creating a new database session for CRUD operations.
• When defining new models for your database schema.

Commands

python -m alembic upgrade head

Anti-Patterns

Don't: Use raw SQL queries directly

Using raw SQL queries bypasses the ORM and can lead to security vulnerabilities and maintenance issues.

# BAD
async def raw_query():
    await db.execute("SELECT * FROM users")