sqlmodel-crud — Skillopedia

SQLModel CRUD Skill Overview Expert guidance for SQLModel database models and CRUD operations, including Pydantic integration, async session management, query building with joins, and relationship configuration for ERP entities like Student, Fee, and Attendance. When This Skill Applies This skill triggers when users request: - Models : "Student model", "SQLModel", "database model", "table=True" - CRUD Operations : "Create student", "Read fees", "Update attendance", "Delete record" - Query Building : "Query with join", "select statement", "where clause", "pagination" - Relationships : "Foreign…

)\n password_hash: str\n role: StudentRole = Field(default=StudentRole.STUDENT)\n class_id: Optional[str] = Field(default=None, foreign_key=\"class.id\", index=True)\n created_at: datetime = Field(default_factory=datetime.utcnow)\n updated_at: datetime = Field(default_factory=datetime.utcnow)\n\n # Relationships\n class_obj: Optional[\"Class\"] = Relationship(back_populates=\"students\")\n fees: List[\"Fee\"] = Relationship(back_populates=\"student\")\n attendance: List[\"Attendance\"] = Relationship(back_populates=\"student\")\n\n class Config:\n from_attributes = True\n\nclass Class(SQLModel, table=True):\n \"\"\"Class model for organizing students\"\"\"\n id: Optional[str] = Field(default=None, primary_key=True)\n name: str = Field(min_length=2, max_length=50)\n grade_level: int = Field(ge=1, le=12)\n academic_year: str = Field(max_length=9, pattern=r'^\\d{4}-\\d{4}

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.

)\n created_at: datetime = Field(default_factory=datetime.utcnow)\n\n # Relationships\n students: List[Student] = Relationship(back_populates=\"class_obj\")\n\nclass Fee(SQLModel, table=True):\n \"\"\"Fee model linked to students\"\"\"\n id: Optional[str] = Field(default=None, primary_key=True)\n student_id: str = Field(foreign_key=\"student.id\", index=True)\n amount: float = Field(gt=0)\n description: str = Field(max_length=500)\n status: str = Field(default=\"pending\", pattern=r'^(pending|paid|overdue|waived)

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.

)\n due_date: datetime\n paid_date: Optional[datetime] = None\n created_at: datetime = Field(default_factory=datetime.utcnow)\n updated_at: datetime = Field(default_factory=datetime.utcnow)\n\n # Relationships\n student: Optional[Student] = Relationship(back_populates=\"fees\")\n\nclass Attendance(SQLModel, table=True):\n \"\"\"Attendance model linked to students\"\"\"\n id: Optional[str] = Field(default=None, primary_key=True)\n student_id: str = Field(foreign_key=\"student.id\", index=True)\n date: datetime = Field(index=True)\n status: str = Field(pattern=r'^(present|absent|late|excused)

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.

)\n notes: Optional[str] = None\n created_at: datetime = Field(default_factory=datetime.utcnow)\n\n # Relationships\n student: Optional[Student] = Relationship(back_populates=\"attendance\")\n```\n\n**Requirements:**\n- Use `table=True` for database tables\n- Add `index=True` for frequently queried fields\n- Use `unique=True` for email and other unique fields\n- Add `foreign_key` for relationships\n- Use Pydantic validators (min_length, max_length, pattern, ge, gt)\n- Use `Relationship` with `back_populates` for bidirectional links\n- Include `created_at` and `updated_at` timestamps\n\n### 2. CRUD Operations: Async Session Management\n\n```python\n# crud/student.py\nfrom sqlmodel import select, and_\nfrom sqlalchemy.ext.asyncio import AsyncSession\nfrom typing import List, Optional\nfrom datetime import datetime\n\nfrom models.student import Student\nfrom schemas.student import StudentCreate, StudentUpdate\n\nclass StudentCRUD:\n \"\"\"CRUD operations for Student model\"\"\"\n\n @staticmethod\n async def create(db: AsyncSession, student_data: StudentCreate) -> Student:\n \"\"\"Create a new student\"\"\"\n student = Student(\n name=student_data.name,\n email=student_data.email,\n phone=student_data.phone,\n password_hash=student_data.password_hash,\n role=student_data.role,\n class_id=student_data.class_id,\n )\n db.add(student)\n await db.commit()\n await db.refresh(student)\n return student\n\n @staticmethod\n async def get_by_id(db: AsyncSession, student_id: str) -> Optional[Student]:\n \"\"\"Get student by ID\"\"\"\n result = await db.execute(\n select(Student).where(Student.id == student_id)\n )\n return result.scalar_one_or_none()\n\n @staticmethod\n async def get_by_email(db: AsyncSession, email: str) -> Optional[Student]:\n \"\"\"Get student by email\"\"\"\n result = await db.execute(\n select(Student).where(Student.email == email)\n )\n return result.scalar_one_or_none()\n\n @staticmethod\n async def get_all(\n db: AsyncSession,\n skip: int = 0,\n limit: int = 100,\n class_id: Optional[str] = None,\n role: Optional[str] = None,\n ) -> List[Student]:\n \"\"\"Get all students with pagination and filters\"\"\"\n query = select(Student)\n\n if class_id:\n query = query.where(Student.class_id == class_id)\n if role:\n query = query.where(Student.role == role)\n\n query = query.offset(skip).limit(limit).order_by(Student.created_at.desc())\n\n result = await db.execute(query)\n return result.scalars().all()\n\n @staticmethod\n async def update(\n db: AsyncSession,\n student_id: str,\n student_data: StudentUpdate,\n ) -> Optional[Student]:\n \"\"\"Update a student\"\"\"\n student = await StudentCRUD.get_by_id(db, student_id)\n if not student:\n return None\n\n update_data = student_data.model_dump(exclude_unset=True)\n for field, value in update_data.items():\n setattr(student, field, value)\n\n student.updated_at = datetime.utcnow()\n await db.commit()\n await db.refresh(student)\n return student\n\n @staticmethod\n async def delete(db: AsyncSession, student_id: str) -> bool:\n \"\"\"Delete a student\"\"\"\n student = await StudentCRUD.get_by_id(db, student_id)\n if not student:\n return False\n\n await db.delete(student)\n await db.commit()\n return True\n\n @staticmethod\n async def count(\n db: AsyncSession,\n class_id: Optional[str] = None,\n role: Optional[str] = None,\n ) -> int:\n \"\"\"Count students with filters\"\"\"\n query = select(Student)\n\n if class_id:\n query = query.where(Student.class_id == class_id)\n if role:\n query = query.where(Student.role == role)\n\n result = await db.execute(query)\n return len(result.scalars().all())\n```\n\n**Requirements:**\n- Use `AsyncSession` for async database operations\n- Use `select()`, `where()`, `offset()`, `limit()`, `order_by()`\n- Return `Optional[T]` for single items, `List[T]` for lists\n- Use `scalar_one_or_none()` for single results\n- Use `scalars().all()` for list results\n- Handle transactions with commit/rollback\n- Update `updated_at` timestamp on modifications\n\n### 3. Query Building: Joins, Filters, Pagination\n\n```python\n# queries/advanced.py\nfrom sqlmodel import select, and_, or_, func\nfrom sqlalchemy.ext.asyncio import AsyncSession\nfrom typing import Optional\n\nfrom models.student import Student, Fee, Attendance, Class\n\nclass StudentQueries:\n \"\"\"Advanced student queries with joins\"\"\"\n\n @staticmethod\n async def get_student_with_fees(\n db: AsyncSession,\n student_id: str,\n ) -> Optional[Student]:\n \"\"\"Get student with all their fees\"\"\"\n result = await db.execute(\n select(Student)\n .where(Student.id == student_id)\n .options(\n # Eager load fees relationship\n selectinload(Student.fees)\n )\n )\n return result.scalar_one_or_none()\n\n @staticmethod\n async def get_students_with_pending_fees(\n db: AsyncSession,\n skip: int = 0,\n limit: int = 100,\n ) -> List[Student]:\n \"\"\"Get students with pending fee balance\"\"\"\n # Subquery to find students with pending fees\n subquery = (\n select(Fee.student_id)\n .where(Fee.status == \"pending\")\n .distinct()\n )\n\n result = await db.execute(\n select(Student)\n .where(Student.id.in_(subquery))\n .offset(skip)\n .limit(limit)\n .order_by(Student.name)\n )\n return result.scalars().all()\n\n @staticmethod\n async def get_student_attendance_summary(\n db: AsyncSession,\n student_id: str,\n month: int,\n year: int,\n ) -> dict:\n \"\"\"Get attendance summary for a student in a month\"\"\"\n from datetime import datetime\n\n start_date = datetime(year, month, 1)\n end_date = datetime(year, month + 1, 1) if month \u003c 12 else datetime(year + 1, 1, 1)\n\n result = await db.execute(\n select(\n func.count().label(\"total_days\"),\n func.sum(\n case((Attendance.status == \"present\", 1), else_=0)\n ).label(\"present_days\"),\n func.sum(\n case((Attendance.status == \"absent\", 1), else_=0)\n ).label(\"absent_days\"),\n func.sum(\n case((Attendance.status == \"late\", 1), else_=0)\n ).label(\"late_days\"),\n )\n .where(\n and_(\n Attendance.student_id == student_id,\n Attendance.date >= start_date,\n Attendance.date \u003c end_date,\n )\n )\n )\n row = result.one()\n return {\n \"total_days\": row.total_days or 0,\n \"present_days\": row.present_days or 0,\n \"absent_days\": row.absent_days or 0,\n \"late_days\": row.late_days or 0,\n }\n\n @staticmethod\n async def search_students(\n db: AsyncSession,\n search_term: str,\n skip: int = 0,\n limit: int = 100,\n ) -> List[Student]:\n \"\"\"Search students by name or email\"\"\"\n search_pattern = f\"%{search_term}%\"\n\n result = await db.execute(\n select(Student)\n .where(\n or_(\n Student.name.ilike(search_pattern),\n Student.email.ilike(search_pattern),\n )\n )\n .offset(skip)\n .limit(limit)\n .order_by(Student.name)\n )\n return result.scalars().all()\n\n @staticmethod\n async def get_students_by_class(\n db: AsyncSession,\n class_id: str,\n include_inactive: bool = False,\n ) -> List[Student]:\n \"\"\"Get all students in a class\"\"\"\n query = select(Student).where(Student.class_id == class_id)\n\n if not include_inactive:\n # Assuming there's an is_active field\n query = query.where(Student.is_active == True)\n\n query = query.order_by(Student.name)\n\n result = await db.execute(query)\n return result.scalars().all()\n\n @staticmethod\n async def get_fee_statistics(\n db: AsyncSession,\n class_id: Optional[str] = None,\n ) -> dict:\n \"\"\"Get fee statistics, optionally filtered by class\"\"\"\n base_query = select(Fee)\n\n if class_id:\n # Join with Student to filter by class\n base_query = (\n select(Fee)\n .join(Student, Student.id == Fee.student_id)\n .where(Student.class_id == class_id)\n )\n\n result = await db.execute(\n select(\n func.count().label(\"total_fees\"),\n func.sum(Fee.amount).label(\"total_amount\"),\n func.sum(\n case((Fee.status == \"paid\", Fee.amount), else_=0)\n ).label(\"total_collected\"),\n func.sum(\n case((Fee.status == \"pending\", Fee.amount), else_=0)\n ).label(\"total_pending\"),\n )\n )\n row = result.one()\n return {\n \"total_fees\": row.total_fees or 0,\n \"total_amount\": float(row.total_amount or 0),\n \"total_collected\": float(row.total_collected or 0),\n \"total_pending\": float(row.total_pending or 0),\n }\n```\n\n**Requirements:**\n- Use `selectinload()` for eager loading relationships\n- Use `subquery` for complex filtering\n- Use `and_()`/`or_()` for compound conditions\n- Use `func.count()`, `func.sum()` for aggregations\n- Use `case()` for conditional aggregation\n- Use `ilike()` for case-insensitive search\n- Use `order_by()` for sorting\n- Use `offset()`/`limit()` for pagination\n\n### 4. Relationships: ForeignKey and back_populates\n\n```python\n# models/relationships.py\nfrom sqlmodel import SQLModel, Field, Relationship\nfrom datetime import datetime\nfrom typing import Optional, List\n\nclass Parent(SQLModel, table=True):\n \"\"\"Parent model linked to students\"\"\"\n id: Optional[str] = Field(default=None, primary_key=True)\n name: str\n email: str = Field(unique=True)\n phone: Optional[str]\n\n # One-to-Many: Parent can have multiple students\n students: List[\"Student\"] = Relationship(\n back_populates=\"parent\",\n link_model=\"student_parent_link\" # Many-to-many through table\n )\n\nclass StudentParentLink(SQLModel, table=True):\n \"\"\"Many-to-many link table for Student and Parent\"\"\"\n student_id: str = Field(foreign_key=\"student.id\", primary_key=True)\n parent_id: str = Field(foreign_key=\"parent.id\", primary_key=True)\n relationship_type: str = Field(default=\"father\") # father, mother, guardian\n\n# Updated Student with many-to-many relationship\nclass Student(SQLModel, table=True):\n id: Optional[str] = Field(default=None, primary_key=True)\n name: str\n email: str = Field(unique=True)\n\n # Many-to-Many: Students can have multiple parents\n parents: List[Parent] = Relationship(\n back_populates=\"students\",\n link_model=StudentParentLink\n )\n\n# One-to-One relationship example\nclass StudentProfile(SQLModel, table=True):\n \"\"\"One-to-one profile for student\"\"\"\n id: Optional[str] = Field(default=None, primary_key=True)\n student_id: str = Field(foreign_key=\"student.id\", unique=True)\n bio: Optional[str] = None\n avatar_url: Optional[str]\n preferences: Optional[str] = None # JSON string for flexible data\n\n student: Optional[Student] = Relationship()\n\n# Self-referential relationship\nclass Employee(SQLModel, table=True):\n \"\"\"Employee with manager relationship\"\"\"\n id: Optional[str] = Field(default=None, primary_key=True)\n name: str\n email: str = Field(unique=True)\n manager_id: Optional[str] = Field(foreign_key=\"employee.id\")\n\n # Self-referential relationship\n manager: Optional[\"Employee\"] = Relationship(\n back_populates=\"subordinates\",\n sa_relationship_kwargs={\"remote_side\": \"[Employee.id]\"}\n )\n subordinates: List[\"Employee\"] = Relationship(back_populates=\"manager\")\n```\n\n**Requirements:**\n- Use `foreign_key` for relational links\n- Use `back_populates` for bidirectional relationships\n- Use `unique=True` for one-to-one relationships\n- Use link tables for many-to-many relationships\n- Use `remote_side` for self-referential relationships\n- Use `sa_relationship_kwargs` for advanced SQLAlchemy options\n\n## Output Requirements\n\n### Code Files\n\n1. **Models**:\n - `models/__init__.py`\n - `models/student.py`\n - `models/fee.py`\n - `models/attendance.py`\n - `models/class.py`\n\n2. **CRUD Operations**:\n - `crud/__init__.py`\n - `crud/student.py`\n - `crud/fee.py`\n - `crud/attendance.py`\n\n3. **Advanced Queries**:\n - `queries/__init__.py`\n - `queries/advanced.py`\n\n### Integration Requirements\n\n- **@fastapi-app**: Use models in FastAPI routes\n- **@fastapi-app/dependencies**: DB session dependency\n- **@api-client**: Type-safe responses for frontend\n\n### Documentation\n\n- **PHR**: Create Prompt History Record for schema design\n- **ADR**: Document relationship choices, indexing strategy\n- **Comments**: Document complex queries and relationships\n\n## Workflow\n\n1. **Design Schema**\n - Identify entities (Student, Fee, Attendance, etc.)\n - Define relationships (one-to-many, many-to-many)\n - Add constraints (unique, foreign keys, indexes)\n\n2. **Create Models**\n - Define SQLModel classes with `table=True`\n - Add fields with types and validators\n - Configure relationships with `back_populates`\n\n3. **Build CRUD Operations**\n - Implement create, read, update, delete\n - Add pagination and filtering\n - Handle async sessions\n\n4. **Create Advanced Queries**\n - Implement joins and subqueries\n - Add aggregations and summaries\n - Search and filter functionality\n\n5. **Test and Optimize**\n - Test all CRUD operations\n - Verify relationships work correctly\n - Optimize slow queries with indexes\n\n## Quality Checklist\n\nBefore completing any SQLModel implementation:\n\n- [ ] **Indexes on freq queries**: Add index=True for commonly filtered/sorted fields\n- [ ] **Constraints FK/unique**: Use foreign_key for relations, unique=True for emails\n- [ ] **Typed results**: Return properly typed Optional[T] or List[T]\n- [ ] **Async support**: Use AsyncSession for all operations\n- [ ] **Relationships**: Use back_populates for bidirectional links\n- [ ] **Validation**: Use Field validators (min_length, max_length, pattern)\n- [ ] **Timestamps**: Include created_at and updated_at\n- [ ] **Cascading**: Configure delete behavior for relationships\n- [ ] **Eager loading**: Use selectinload for relationship access\n- [ ] **Error handling**: Handle IntegrityError, foreign key violations\n\n## Common Patterns\n\n### Student Model with CRUD\n\n```python\n# models/student.py\nclass Student(SQLModel, table=True):\n id: Optional[str] = Field(default=None, primary_key=True)\n name: str = Field(min_length=2, max_length=100, index=True)\n email: str = Field(unique=True, index=True)\n is_active: bool = Field(default=True)\n created_at: datetime = Field(default_factory=datetime.utcnow)\n\n fees: List[\"Fee\"] = Relationship(back_populates=\"student\")\n```\n\n### CRUD Fees\n\n```python\n# crud/fee.py\nclass FeeCRUD:\n @staticmethod\n async def create(db: AsyncSession, fee_data: FeeCreate) -> Fee:\n fee = Fee(**fee_data.model_dump())\n db.add(fee)\n await db.commit()\n await db.refresh(fee)\n return fee\n\n @staticmethod\n async def get_by_id(db: AsyncSession, fee_id: str) -> Optional[Fee]:\n result = await db.execute(\n select(Fee).where(Fee.id == fee_id)\n )\n return result.scalar_one_or_none()\n\n @staticmethod\n async def get_pending_by_student(db: AsyncSession, student_id: str) -> List[Fee]:\n result = await db.execute(\n select(Fee)\n .where(\n and_(\n Fee.student_id == student_id,\n Fee.status == \"pending\"\n )\n )\n .order_by(Fee.due_date)\n )\n return result.scalars().all()\n```\n\n### Query with Join\n\n```python\n# Get students with their class name using join\nasync def get_students_with_class(db: AsyncSession) -> List[dict]:\n result = await db.execute(\n select(\n Student.id,\n Student.name,\n Student.email,\n Class.name.label(\"class_name\"),\n )\n .outerjoin(Class, Student.class_id == Class.id)\n .order_by(Student.name)\n )\n return [dict(row._mapping) for row in result]\n```\n\n## Database Setup\n\n```python\n# database.py\nfrom sqlmodel import create_engine, SQLModel\nfrom sqlalchemy.ext.asyncio import create_async_engine, AsyncSession\nfrom sqlalchemy.orm import sessionmaker\nimport os\n\nDATABASE_URL = os.getenv(\n \"DATABASE_URL\",\n \"postgresql+asyncpg://user:password@localhost:5432/erp_db\"\n)\n\n# Async engine\nasync_engine = create_async_engine(DATABASE_URL, echo=True)\nasync_session_maker = sessionmaker(\n async_engine,\n class_=AsyncSession,\n expire_on_commit=False,\n)\n\nasync def init_db():\n \"\"\"Initialize database tables\"\"\"\n async with async_engine.begin() as conn:\n await conn.run_sync(SQLModel.metadata.create_all)\n\nasync def get_db() -> AsyncSession:\n async with async_session_maker() as session:\n try:\n yield session\n await session.commit()\n except Exception:\n await session.rollback()\n raise\n finally:\n await session.close()\n```\n\n## Migrations with Alembic\n\n```python\n# alembic/env.py\nfrom sqlalchemy import pool\nfrom sqlalchemy.engine import Connection\nfrom sqlalchemy.ext.asyncio import async_engine_from_url\nfrom alembic import context\n\n# Import models for autogenerate\nfrom models.student import Student\nfrom models.fee import Fee\nfrom models.attendance import Attendance\n\ntarget_metadata = SQLModel.metadata\n\n# alembic revision --autogenerate -m \"Initial migration\"\n# alembic upgrade head\n```\n\n## References\n\n- SQLModel Documentation: https://sqlmodel.tiangolo.com\n- SQLAlchemy Relationships: https://docs.sqlalchemy.org/en/20/orm/relationships.html\n- SQLAlchemy Async: https://docs.sqlalchemy.org/en/20/orm/extensions/asyncio.html\n- Pydantic Validation: https://docs.pydantic.dev/latest/usage/validators/\n- Alembic Migrations: https://alembic.sqlalchemy.org/en/latest/\n---","attachment_filenames":["skill-report.json"],"attachments":[{"filename":"skill-report.json","content":"{\n \"schema_version\": \"2.0\",\n \"meta\": {\n \"generated_at\": \"2026-01-16T18:10:14.189Z\",\n \"slug\": \"awais68-sqlmodel-crud\",\n \"source_url\": \"https://github.com/Awais68/hackathon-2-phase-ii-full-stack-web-app/tree/main/.claude/skills/sqlmodel-crud\",\n \"source_ref\": \"main\",\n \"model\": \"claude\",\n \"analysis_version\": \"3.0.0\",\n \"source_type\": \"community\",\n \"content_hash\": \"92aee1a31aa0be83ae389fad19c7a08fd2f5057ccdaa88bc6ef8e0531c625eea\",\n \"tree_hash\": \"9f635de84606029012c9eda771af6f800161bbeadb780e226dbdf49cdba7c747\"\n },\n \"skill\": {\n \"name\": \"sqlmodel-crud\",\n \"description\": \"Use when creating SQLModel database models, CRUD operations, queries with joins, or relationships.\\nNOT when non-database operations, plain SQL, or unrelated data handling.\\nTriggers: \\\"SQLModel\\\", \\\"database model\\\", \\\"CRUD\\\", \\\"create/read/update/delete\\\", \\\"query\\\", \\\"ForeignKey\\\", \\\"relationship\\\".\\n\",\n \"summary\": \"Use when creating SQLModel database models, CRUD operations, queries with joins, or relationships.\\nN...\",\n \"icon\": \"🗄️\",\n \"version\": \"1.0.0\",\n \"author\": \"Awais68\",\n \"license\": \"MIT\",\n \"category\": \"data\",\n \"tags\": [\n \"sqlmodel\",\n \"database\",\n \"crud\",\n \"pydantic\",\n \"sqlalchemy\"\n ],\n \"supported_tools\": [\n \"claude\",\n \"codex\",\n \"claude-code\"\n ],\n \"risk_factors\": [\n \"network\",\n \"filesystem\",\n \"external_commands\",\n \"env_access\"\n ]\n },\n \"security_audit\": {\n \"risk_level\": \"safe\",\n \"is_blocked\": false,\n \"safe_to_publish\": true,\n \"summary\": \"Pure documentation skill containing guidance examples for SQLModel database operations. No executable code, no network calls, no filesystem access beyond reading the skill file itself. All static findings are false positives from the scanner misinterpreting markdown code blocks as shell commands and field names as cryptographic patterns.\",\n \"risk_factor_evidence\": [\n {\n \"factor\": \"network\",\n \"evidence\": [\n {\n \"file\": \"skill-report.json\",\n \"line_start\": 6,\n \"line_end\": 6\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 696,\n \"line_end\": 696\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 697,\n \"line_end\": 697\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 698,\n \"line_end\": 698\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 699,\n \"line_end\": 699\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 700,\n \"line_end\": 700\n }\n ]\n },\n {\n \"factor\": \"filesystem\",\n \"evidence\": [\n {\n \"file\": \"skill-report.json\",\n \"line_start\": 6,\n \"line_end\": 6\n }\n ]\n },\n {\n \"factor\": \"external_commands\",\n \"evidence\": [\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 28,\n \"line_end\": 97\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 97,\n \"line_end\": 100\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 100,\n \"line_end\": 101\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 101,\n \"line_end\": 102\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 102,\n \"line_end\": 103\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 103,\n \"line_end\": 105\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 105,\n \"line_end\": 105\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 105,\n \"line_end\": 106\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 106,\n \"line_end\": 106\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 106,\n \"line_end\": 110\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 110,\n \"line_end\": 223\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 223,\n \"line_end\": 226\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 226,\n \"line_end\": 227\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 227,\n \"line_end\": 227\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 227,\n \"line_end\": 227\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 227,\n \"line_end\": 227\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 227,\n \"line_end\": 227\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 227,\n \"line_end\": 228\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 228,\n \"line_end\": 228\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 228,\n \"line_end\": 229\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 229,\n \"line_end\": 230\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 230,\n \"line_end\": 232\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 232,\n \"line_end\": 236\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 236,\n \"line_end\": 405\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 405,\n \"line_end\": 408\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 408,\n \"line_end\": 409\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 409,\n \"line_end\": 410\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 410,\n \"line_end\": 410\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 410,\n \"line_end\": 411\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 411,\n \"line_end\": 411\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 411,\n \"line_end\": 412\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 412,\n \"line_end\": 413\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 413,\n \"line_end\": 414\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 414,\n \"line_end\": 415\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 415,\n \"line_end\": 415\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 415,\n \"line_end\": 419\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 419,\n \"line_end\": 481\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 481,\n \"line_end\": 484\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 484,\n \"line_end\": 485\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 485,\n \"line_end\": 486\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 486,\n \"line_end\": 488\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 488,\n \"line_end\": 489\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 489,\n \"line_end\": 496\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 496,\n \"line_end\": 497\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 497,\n \"line_end\": 498\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 498,\n \"line_end\": 499\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 499,\n \"line_end\": 500\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 500,\n \"line_end\": 503\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 503,\n \"line_end\": 504\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 504,\n \"line_end\": 505\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 505,\n \"line_end\": 506\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 506,\n \"line_end\": 509\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 509,\n \"line_end\": 510\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 510,\n \"line_end\": 532\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 532,\n \"line_end\": 534\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 534,\n \"line_end\": 570\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 570,\n \"line_end\": 580\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 580,\n \"line_end\": 584\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 584,\n \"line_end\": 615\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 615,\n \"line_end\": 619\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 619,\n \"line_end\": 633\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 633,\n \"line_end\": 637\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 637,\n \"line_end\": 672\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 672,\n \"line_end\": 676\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 676,\n \"line_end\": 692\n }\n ]\n },\n {\n \"factor\": \"env_access\",\n \"evidence\": [\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 644,\n \"line_end\": 644\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 644,\n \"line_end\": 644\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 644,\n \"line_end\": 644\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 645,\n \"line_end\": 645\n },\n {\n \"file\": \"SKILL.md\",\n \"line_start\": 650,\n \"line_end\": 650\n }\n ]\n }\n ],\n \"critical_findings\": [],\n \"high_findings\": [],\n \"medium_findings\": [],\n \"low_findings\": [],\n \"dangerous_patterns\": [],\n \"files_scanned\": 2,\n \"total_lines\": 878,\n \"audit_model\": \"claude\",\n \"audited_at\": \"2026-01-16T18:10:14.189Z\"\n },\n \"content\": {\n \"user_title\": \"Create SQLModel database models and CRUD operations\",\n \"value_statement\": \"Creating SQLModel database models requires understanding Pydantic integration, async sessions, and relationship configuration. This skill provides expert patterns for building type-safe database operations with proper validation.\",\n \"seo_keywords\": [\n \"sqlmodel\",\n \"sqlmodel crud\",\n \"database models\",\n \"pydantic validation\",\n \"sqlalchemy\",\n \"fastapi database\",\n \"python orm\",\n \"claude code sqlmodel\",\n \"claude sqlmodel\",\n \"codex sqlmodel\"\n ],\n \"actual_capabilities\": [\n \"Create SQLModel table=True models with Pydantic validators\",\n \"Implement async CRUD operations with AsyncSession\",\n \"Build queries with joins, filters, and pagination\",\n \"Configure one-to-many, many-to-many, and self-referential relationships\",\n \"Add field constraints with min_length, max_length, pattern, and foreign keys\",\n \"Use eager loading with selectinload for relationship access\"\n ],\n \"limitations\": [\n \"Does not execute database migrations automatically\",\n \"Does not provide database connection pooling configuration\",\n \"Does not handle authentication or authorization logic\",\n \"Requires separate setup for database engine and session management\"\n ],\n \"use_cases\": [\n {\n \"target_user\": \"Backend developers\",\n \"title\": \"Build type-safe FastAPI endpoints\",\n \"description\": \"Create SQLModel models and CRUD operations for FastAPI routes with proper Pydantic validation and async database sessions.\"\n },\n {\n \"target_user\": \"Full-stack developers\",\n \"title\": \"Design database schema\",\n \"description\": \"Design entities with relationships including one-to-many, many-to-many, and one-to-one patterns with proper foreign keys.\"\n },\n {\n \"target_user\": \"Data engineers\",\n \"title\": \"Write complex queries\",\n \"description\": \"Build advanced queries with joins, subqueries, aggregations, conditional logic, and pagination for reporting systems.\"\n }\n ],\n \"prompt_templates\": [\n {\n \"title\": \"Basic model\",\n \"scenario\": \"Create a simple SQLModel table\",\n \"prompt\": \"Create a SQLModel table=True model called Product with id, name, price, and description fields including proper validation constraints.\"\n },\n {\n \"title\": \"CRUD operations\",\n \"scenario\": \"Build CRUD for a model\",\n \"prompt\": \"Write async CRUD operations for a Product model including create, get_by_id, get_all with pagination, update, and delete methods using AsyncSession.\"\n },\n {\n \"title\": \"Relationships\",\n \"scenario\": \"Link models with relationships\",\n \"prompt\": \"Create SQLModel models for Order and OrderItem with one-to-many relationship including back_populates and proper foreign key configuration.\"\n },\n {\n \"title\": \"Complex queries\",\n \"scenario\": \"Build advanced query\",\n \"prompt\": \"Write a query to get orders with their items using eager loading with selectinload, filtering by date range, and ordering by created_at descending.\"\n }\n ],\n \"output_examples\": [\n {\n \"input\": \"Create a Student model with name, email, and enrollment date fields\",\n \"output\": [\n \"Created Student model with id (UUID primary key), name (2-100 chars required), email (unique index), enrollment_date (datetime), and timestamps\",\n \"Added table=True for database table creation\",\n \"Configured optional is_active field with default True\",\n \"Ready to use with CRUD operations\"\n ]\n },\n {\n \"input\": \"Build a query to find all students with pending fees in a class\",\n \"output\": [\n \"Wrote subquery to find students with pending fee status\",\n \"Used join with Student table filtered by class_id\",\n \"Added pagination with offset and limit\",\n \"Ordered results by student name alphabetically\"\n ]\n }\n ],\n \"best_practices\": [\n \"Use table=True for all database models and separate Pydantic models for request/response schemas\",\n \"Add index=True on frequently filtered fields and unique=True on email and other business keys\",\n \"Always use AsyncSession for async applications and handle transactions with commit/rollback\"\n ],\n \"anti_patterns\": [\n \"Do not use from_attributes = True without understanding the security implications of mass assignment\",\n \"Avoid mixing SQLModel and Pydantic models in the same class definition without proper separation\",\n \"Do not skip adding created_at and updated_at timestamps for audit trails\"\n ],\n \"faq\": [\n {\n \"question\": \"What Python versions are supported?\",\n \"answer\": \"SQLModel requires Python 3.8 or later for production use.\"\n },\n {\n \"question\": \"What databases work with SQLModel?\",\n \"answer\": \"SQLModel supports PostgreSQL, MySQL, SQLite, and other databases compatible with SQLAlchemy async drivers.\"\n },\n {\n \"question\": \"How do I integrate with FastAPI?\",\n \"answer\": \"Use FastAPI Depends() to inject AsyncSession from a sessionmaker factory connected to your SQLModel engine.\"\n },\n {\n \"question\": \"Is my data safe with this skill?\",\n \"answer\": \"Yes. This skill only provides guidance patterns. Data safety depends on your database configuration, connection security, and access controls.\"\n },\n {\n \"question\": \"Why are my queries slow?\",\n \"answer\": \"Add index=True on filtered fields, use eager loading with selectinload for relationships, and avoid N+1 query patterns.\"\n },\n {\n \"question\": \"How does this compare to SQLAlchemy?\",\n \"answer\": \"SQLModel is built on SQLAlchemy and Pydantic. It provides simpler syntax while maintaining full SQLAlchemy capability and type safety.\"\n }\n ]\n },\n \"file_structure\": [\n {\n \"name\": \"SKILL.md\",\n \"type\": \"file\",\n \"path\": \"SKILL.md\",\n \"lines\": 701\n }\n ]\n}\n","content_type":"application/json; charset=utf-8","language":"json","size":17643,"content_sha256":"b38e96ea9ba917b1d4337a0dd764c3d92b4707302d39921e218bb5e347d4322b"}],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"SQLModel CRUD Skill","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Overview","type":"text"}]},{"type":"paragraph","content":[{"text":"Expert guidance for SQLModel database models and CRUD operations, including Pydantic integration, async session management, query building with joins, and relationship configuration for ERP entities like Student, Fee, and Attendance.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"When This Skill Applies","type":"text"}]},{"type":"paragraph","content":[{"text":"This skill triggers when users request:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Models","type":"text","marks":[{"type":"strong"}]},{"text":": \"Student model\", \"SQLModel\", \"database model\", \"table=True\"","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"CRUD Operations","type":"text","marks":[{"type":"strong"}]},{"text":": \"Create student\", \"Read fees\", \"Update attendance\", \"Delete record\"","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Query Building","type":"text","marks":[{"type":"strong"}]},{"text":": \"Query with join\", \"select statement\", \"where clause\", \"pagination\"","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Relationships","type":"text","marks":[{"type":"strong"}]},{"text":": \"ForeignKey\", \"back_populates\", \"relationship\", \"linked models\"","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Validation","type":"text","marks":[{"type":"strong"}]},{"text":": \"Pydantic validators\", \"field constraints\", \"indexes\"","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Core Rules","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"1. Models: table=True with Pydantic Validation","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"# models/student.py\nfrom sqlmodel import SQLModel, Field, Relationship\nfrom datetime import datetime\nfrom typing import Optional, List\nfrom enum import Enum\n\nclass StudentRole(str, Enum):\n STUDENT = \"student\"\n TEACHER = \"teacher\"\n ADMIN = \"admin\"\n\nclass Student(SQLModel, table=True):\n \"\"\"Student model with relationships to fees and attendance\"\"\"\n id: Optional[str] = Field(default=None, primary_key=True)\n name: str = Field(min_length=2, max_length=100, index=True)\n email: str = Field(unique=True, index=True)\n phone: Optional[str] = Field(default=None, pattern=r'^\\+?[\\d\\s-]+

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.

)\n notes: Optional[str] = None\n created_at: datetime = Field(default_factory=datetime.utcnow)\n\n # Relationships\n student: Optional[Student] = Relationship(back_populates=\"attendance\")","type":"text"}]},{"type":"paragraph","content":[{"text":"Requirements:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"table=True","type":"text","marks":[{"type":"code_inline"}]},{"text":" for database tables","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Add ","type":"text"},{"text":"index=True","type":"text","marks":[{"type":"code_inline"}]},{"text":" for frequently queried fields","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"unique=True","type":"text","marks":[{"type":"code_inline"}]},{"text":" for email and other unique fields","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Add ","type":"text"},{"text":"foreign_key","type":"text","marks":[{"type":"code_inline"}]},{"text":" for relationships","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use Pydantic validators (min_length, max_length, pattern, ge, gt)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"Relationship","type":"text","marks":[{"type":"code_inline"}]},{"text":" with ","type":"text"},{"text":"back_populates","type":"text","marks":[{"type":"code_inline"}]},{"text":" for bidirectional links","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Include ","type":"text"},{"text":"created_at","type":"text","marks":[{"type":"code_inline"}]},{"text":" and ","type":"text"},{"text":"updated_at","type":"text","marks":[{"type":"code_inline"}]},{"text":" timestamps","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"2. CRUD Operations: Async Session Management","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"# crud/student.py\nfrom sqlmodel import select, and_\nfrom sqlalchemy.ext.asyncio import AsyncSession\nfrom typing import List, Optional\nfrom datetime import datetime\n\nfrom models.student import Student\nfrom schemas.student import StudentCreate, StudentUpdate\n\nclass StudentCRUD:\n \"\"\"CRUD operations for Student model\"\"\"\n\n @staticmethod\n async def create(db: AsyncSession, student_data: StudentCreate) -> Student:\n \"\"\"Create a new student\"\"\"\n student = Student(\n name=student_data.name,\n email=student_data.email,\n phone=student_data.phone,\n password_hash=student_data.password_hash,\n role=student_data.role,\n class_id=student_data.class_id,\n )\n db.add(student)\n await db.commit()\n await db.refresh(student)\n return student\n\n @staticmethod\n async def get_by_id(db: AsyncSession, student_id: str) -> Optional[Student]:\n \"\"\"Get student by ID\"\"\"\n result = await db.execute(\n select(Student).where(Student.id == student_id)\n )\n return result.scalar_one_or_none()\n\n @staticmethod\n async def get_by_email(db: AsyncSession, email: str) -> Optional[Student]:\n \"\"\"Get student by email\"\"\"\n result = await db.execute(\n select(Student).where(Student.email == email)\n )\n return result.scalar_one_or_none()\n\n @staticmethod\n async def get_all(\n db: AsyncSession,\n skip: int = 0,\n limit: int = 100,\n class_id: Optional[str] = None,\n role: Optional[str] = None,\n ) -> List[Student]:\n \"\"\"Get all students with pagination and filters\"\"\"\n query = select(Student)\n\n if class_id:\n query = query.where(Student.class_id == class_id)\n if role:\n query = query.where(Student.role == role)\n\n query = query.offset(skip).limit(limit).order_by(Student.created_at.desc())\n\n result = await db.execute(query)\n return result.scalars().all()\n\n @staticmethod\n async def update(\n db: AsyncSession,\n student_id: str,\n student_data: StudentUpdate,\n ) -> Optional[Student]:\n \"\"\"Update a student\"\"\"\n student = await StudentCRUD.get_by_id(db, student_id)\n if not student:\n return None\n\n update_data = student_data.model_dump(exclude_unset=True)\n for field, value in update_data.items():\n setattr(student, field, value)\n\n student.updated_at = datetime.utcnow()\n await db.commit()\n await db.refresh(student)\n return student\n\n @staticmethod\n async def delete(db: AsyncSession, student_id: str) -> bool:\n \"\"\"Delete a student\"\"\"\n student = await StudentCRUD.get_by_id(db, student_id)\n if not student:\n return False\n\n await db.delete(student)\n await db.commit()\n return True\n\n @staticmethod\n async def count(\n db: AsyncSession,\n class_id: Optional[str] = None,\n role: Optional[str] = None,\n ) -> int:\n \"\"\"Count students with filters\"\"\"\n query = select(Student)\n\n if class_id:\n query = query.where(Student.class_id == class_id)\n if role:\n query = query.where(Student.role == role)\n\n result = await db.execute(query)\n return len(result.scalars().all())","type":"text"}]},{"type":"paragraph","content":[{"text":"Requirements:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"AsyncSession","type":"text","marks":[{"type":"code_inline"}]},{"text":" for async database operations","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"select()","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"where()","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"offset()","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"limit()","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"order_by()","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Return ","type":"text"},{"text":"Optional[T]","type":"text","marks":[{"type":"code_inline"}]},{"text":" for single items, ","type":"text"},{"text":"List[T]","type":"text","marks":[{"type":"code_inline"}]},{"text":" for lists","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"scalar_one_or_none()","type":"text","marks":[{"type":"code_inline"}]},{"text":" for single results","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"scalars().all()","type":"text","marks":[{"type":"code_inline"}]},{"text":" for list results","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Handle transactions with commit/rollback","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Update ","type":"text"},{"text":"updated_at","type":"text","marks":[{"type":"code_inline"}]},{"text":" timestamp on modifications","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"3. Query Building: Joins, Filters, Pagination","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"# queries/advanced.py\nfrom sqlmodel import select, and_, or_, func\nfrom sqlalchemy.ext.asyncio import AsyncSession\nfrom typing import Optional\n\nfrom models.student import Student, Fee, Attendance, Class\n\nclass StudentQueries:\n \"\"\"Advanced student queries with joins\"\"\"\n\n @staticmethod\n async def get_student_with_fees(\n db: AsyncSession,\n student_id: str,\n ) -> Optional[Student]:\n \"\"\"Get student with all their fees\"\"\"\n result = await db.execute(\n select(Student)\n .where(Student.id == student_id)\n .options(\n # Eager load fees relationship\n selectinload(Student.fees)\n )\n )\n return result.scalar_one_or_none()\n\n @staticmethod\n async def get_students_with_pending_fees(\n db: AsyncSession,\n skip: int = 0,\n limit: int = 100,\n ) -> List[Student]:\n \"\"\"Get students with pending fee balance\"\"\"\n # Subquery to find students with pending fees\n subquery = (\n select(Fee.student_id)\n .where(Fee.status == \"pending\")\n .distinct()\n )\n\n result = await db.execute(\n select(Student)\n .where(Student.id.in_(subquery))\n .offset(skip)\n .limit(limit)\n .order_by(Student.name)\n )\n return result.scalars().all()\n\n @staticmethod\n async def get_student_attendance_summary(\n db: AsyncSession,\n student_id: str,\n month: int,\n year: int,\n ) -> dict:\n \"\"\"Get attendance summary for a student in a month\"\"\"\n from datetime import datetime\n\n start_date = datetime(year, month, 1)\n end_date = datetime(year, month + 1, 1) if month \u003c 12 else datetime(year + 1, 1, 1)\n\n result = await db.execute(\n select(\n func.count().label(\"total_days\"),\n func.sum(\n case((Attendance.status == \"present\", 1), else_=0)\n ).label(\"present_days\"),\n func.sum(\n case((Attendance.status == \"absent\", 1), else_=0)\n ).label(\"absent_days\"),\n func.sum(\n case((Attendance.status == \"late\", 1), else_=0)\n ).label(\"late_days\"),\n )\n .where(\n and_(\n Attendance.student_id == student_id,\n Attendance.date >= start_date,\n Attendance.date \u003c end_date,\n )\n )\n )\n row = result.one()\n return {\n \"total_days\": row.total_days or 0,\n \"present_days\": row.present_days or 0,\n \"absent_days\": row.absent_days or 0,\n \"late_days\": row.late_days or 0,\n }\n\n @staticmethod\n async def search_students(\n db: AsyncSession,\n search_term: str,\n skip: int = 0,\n limit: int = 100,\n ) -> List[Student]:\n \"\"\"Search students by name or email\"\"\"\n search_pattern = f\"%{search_term}%\"\n\n result = await db.execute(\n select(Student)\n .where(\n or_(\n Student.name.ilike(search_pattern),\n Student.email.ilike(search_pattern),\n )\n )\n .offset(skip)\n .limit(limit)\n .order_by(Student.name)\n )\n return result.scalars().all()\n\n @staticmethod\n async def get_students_by_class(\n db: AsyncSession,\n class_id: str,\n include_inactive: bool = False,\n ) -> List[Student]:\n \"\"\"Get all students in a class\"\"\"\n query = select(Student).where(Student.class_id == class_id)\n\n if not include_inactive:\n # Assuming there's an is_active field\n query = query.where(Student.is_active == True)\n\n query = query.order_by(Student.name)\n\n result = await db.execute(query)\n return result.scalars().all()\n\n @staticmethod\n async def get_fee_statistics(\n db: AsyncSession,\n class_id: Optional[str] = None,\n ) -> dict:\n \"\"\"Get fee statistics, optionally filtered by class\"\"\"\n base_query = select(Fee)\n\n if class_id:\n # Join with Student to filter by class\n base_query = (\n select(Fee)\n .join(Student, Student.id == Fee.student_id)\n .where(Student.class_id == class_id)\n )\n\n result = await db.execute(\n select(\n func.count().label(\"total_fees\"),\n func.sum(Fee.amount).label(\"total_amount\"),\n func.sum(\n case((Fee.status == \"paid\", Fee.amount), else_=0)\n ).label(\"total_collected\"),\n func.sum(\n case((Fee.status == \"pending\", Fee.amount), else_=0)\n ).label(\"total_pending\"),\n )\n )\n row = result.one()\n return {\n \"total_fees\": row.total_fees or 0,\n \"total_amount\": float(row.total_amount or 0),\n \"total_collected\": float(row.total_collected or 0),\n \"total_pending\": float(row.total_pending or 0),\n }","type":"text"}]},{"type":"paragraph","content":[{"text":"Requirements:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"selectinload()","type":"text","marks":[{"type":"code_inline"}]},{"text":" for eager loading relationships","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"subquery","type":"text","marks":[{"type":"code_inline"}]},{"text":" for complex filtering","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"and_()","type":"text","marks":[{"type":"code_inline"}]},{"text":"/","type":"text"},{"text":"or_()","type":"text","marks":[{"type":"code_inline"}]},{"text":" for compound conditions","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"func.count()","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"func.sum()","type":"text","marks":[{"type":"code_inline"}]},{"text":" for aggregations","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"case()","type":"text","marks":[{"type":"code_inline"}]},{"text":" for conditional aggregation","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"ilike()","type":"text","marks":[{"type":"code_inline"}]},{"text":" for case-insensitive search","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"order_by()","type":"text","marks":[{"type":"code_inline"}]},{"text":" for sorting","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"offset()","type":"text","marks":[{"type":"code_inline"}]},{"text":"/","type":"text"},{"text":"limit()","type":"text","marks":[{"type":"code_inline"}]},{"text":" for pagination","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"4. Relationships: ForeignKey and back_populates","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"# models/relationships.py\nfrom sqlmodel import SQLModel, Field, Relationship\nfrom datetime import datetime\nfrom typing import Optional, List\n\nclass Parent(SQLModel, table=True):\n \"\"\"Parent model linked to students\"\"\"\n id: Optional[str] = Field(default=None, primary_key=True)\n name: str\n email: str = Field(unique=True)\n phone: Optional[str]\n\n # One-to-Many: Parent can have multiple students\n students: List[\"Student\"] = Relationship(\n back_populates=\"parent\",\n link_model=\"student_parent_link\" # Many-to-many through table\n )\n\nclass StudentParentLink(SQLModel, table=True):\n \"\"\"Many-to-many link table for Student and Parent\"\"\"\n student_id: str = Field(foreign_key=\"student.id\", primary_key=True)\n parent_id: str = Field(foreign_key=\"parent.id\", primary_key=True)\n relationship_type: str = Field(default=\"father\") # father, mother, guardian\n\n# Updated Student with many-to-many relationship\nclass Student(SQLModel, table=True):\n id: Optional[str] = Field(default=None, primary_key=True)\n name: str\n email: str = Field(unique=True)\n\n # Many-to-Many: Students can have multiple parents\n parents: List[Parent] = Relationship(\n back_populates=\"students\",\n link_model=StudentParentLink\n )\n\n# One-to-One relationship example\nclass StudentProfile(SQLModel, table=True):\n \"\"\"One-to-one profile for student\"\"\"\n id: Optional[str] = Field(default=None, primary_key=True)\n student_id: str = Field(foreign_key=\"student.id\", unique=True)\n bio: Optional[str] = None\n avatar_url: Optional[str]\n preferences: Optional[str] = None # JSON string for flexible data\n\n student: Optional[Student] = Relationship()\n\n# Self-referential relationship\nclass Employee(SQLModel, table=True):\n \"\"\"Employee with manager relationship\"\"\"\n id: Optional[str] = Field(default=None, primary_key=True)\n name: str\n email: str = Field(unique=True)\n manager_id: Optional[str] = Field(foreign_key=\"employee.id\")\n\n # Self-referential relationship\n manager: Optional[\"Employee\"] = Relationship(\n back_populates=\"subordinates\",\n sa_relationship_kwargs={\"remote_side\": \"[Employee.id]\"}\n )\n subordinates: List[\"Employee\"] = Relationship(back_populates=\"manager\")","type":"text"}]},{"type":"paragraph","content":[{"text":"Requirements:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"foreign_key","type":"text","marks":[{"type":"code_inline"}]},{"text":" for relational links","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"back_populates","type":"text","marks":[{"type":"code_inline"}]},{"text":" for bidirectional relationships","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"unique=True","type":"text","marks":[{"type":"code_inline"}]},{"text":" for one-to-one relationships","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use link tables for many-to-many relationships","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"remote_side","type":"text","marks":[{"type":"code_inline"}]},{"text":" for self-referential relationships","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"sa_relationship_kwargs","type":"text","marks":[{"type":"code_inline"}]},{"text":" for advanced SQLAlchemy options","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Output Requirements","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Code Files","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Models","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"models/__init__.py","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"models/student.py","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"models/fee.py","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"models/attendance.py","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"models/class.py","type":"text","marks":[{"type":"code_inline"}]}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"CRUD Operations","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"crud/__init__.py","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"crud/student.py","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"crud/fee.py","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"crud/attendance.py","type":"text","marks":[{"type":"code_inline"}]}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Advanced Queries","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"queries/__init__.py","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"queries/advanced.py","type":"text","marks":[{"type":"code_inline"}]}]}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Integration Requirements","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"@fastapi-app","type":"text","marks":[{"type":"strong"}]},{"text":": Use models in FastAPI routes","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"@fastapi-app/dependencies","type":"text","marks":[{"type":"strong"}]},{"text":": DB session dependency","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"@api-client","type":"text","marks":[{"type":"strong"}]},{"text":": Type-safe responses for frontend","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Documentation","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"PHR","type":"text","marks":[{"type":"strong"}]},{"text":": Create Prompt History Record for schema design","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"ADR","type":"text","marks":[{"type":"strong"}]},{"text":": Document relationship choices, indexing strategy","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Comments","type":"text","marks":[{"type":"strong"}]},{"text":": Document complex queries and relationships","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Workflow","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Design Schema","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Identify entities (Student, Fee, Attendance, etc.)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Define relationships (one-to-many, many-to-many)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Add constraints (unique, foreign keys, indexes)","type":"text"}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Create Models","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Define SQLModel classes with ","type":"text"},{"text":"table=True","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Add fields with types and validators","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Configure relationships with ","type":"text"},{"text":"back_populates","type":"text","marks":[{"type":"code_inline"}]}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Build CRUD Operations","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Implement create, read, update, delete","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Add pagination and filtering","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Handle async sessions","type":"text"}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Create Advanced Queries","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Implement joins and subqueries","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Add aggregations and summaries","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Search and filter functionality","type":"text"}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Test and Optimize","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Test all CRUD operations","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Verify relationships work correctly","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Optimize slow queries with indexes","type":"text"}]}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Quality Checklist","type":"text"}]},{"type":"paragraph","content":[{"text":"Before completing any SQLModel implementation:","type":"text"}]},{"type":"checkbox_list","attrs":{"id":null},"content":[{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Indexes on freq queries","type":"text","marks":[{"type":"strong"}]},{"text":": Add index=True for commonly filtered/sorted fields","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Constraints FK/unique","type":"text","marks":[{"type":"strong"}]},{"text":": Use foreign_key for relations, unique=True for emails","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Typed results","type":"text","marks":[{"type":"strong"}]},{"text":": Return properly typed Optional[T] or List[T]","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Async support","type":"text","marks":[{"type":"strong"}]},{"text":": Use AsyncSession for all operations","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Relationships","type":"text","marks":[{"type":"strong"}]},{"text":": Use back_populates for bidirectional links","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Validation","type":"text","marks":[{"type":"strong"}]},{"text":": Use Field validators (min_length, max_length, pattern)","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Timestamps","type":"text","marks":[{"type":"strong"}]},{"text":": Include created_at and updated_at","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Cascading","type":"text","marks":[{"type":"strong"}]},{"text":": Configure delete behavior for relationships","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Eager loading","type":"text","marks":[{"type":"strong"}]},{"text":": Use selectinload for relationship access","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Error handling","type":"text","marks":[{"type":"strong"}]},{"text":": Handle IntegrityError, foreign key violations","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Common Patterns","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Student Model with CRUD","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"# models/student.py\nclass Student(SQLModel, table=True):\n id: Optional[str] = Field(default=None, primary_key=True)\n name: str = Field(min_length=2, max_length=100, index=True)\n email: str = Field(unique=True, index=True)\n is_active: bool = Field(default=True)\n created_at: datetime = Field(default_factory=datetime.utcnow)\n\n fees: List[\"Fee\"] = Relationship(back_populates=\"student\")","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"CRUD Fees","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"# crud/fee.py\nclass FeeCRUD:\n @staticmethod\n async def create(db: AsyncSession, fee_data: FeeCreate) -> Fee:\n fee = Fee(**fee_data.model_dump())\n db.add(fee)\n await db.commit()\n await db.refresh(fee)\n return fee\n\n @staticmethod\n async def get_by_id(db: AsyncSession, fee_id: str) -> Optional[Fee]:\n result = await db.execute(\n select(Fee).where(Fee.id == fee_id)\n )\n return result.scalar_one_or_none()\n\n @staticmethod\n async def get_pending_by_student(db: AsyncSession, student_id: str) -> List[Fee]:\n result = await db.execute(\n select(Fee)\n .where(\n and_(\n Fee.student_id == student_id,\n Fee.status == \"pending\"\n )\n )\n .order_by(Fee.due_date)\n )\n return result.scalars().all()","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Query with Join","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"# Get students with their class name using join\nasync def get_students_with_class(db: AsyncSession) -> List[dict]:\n result = await db.execute(\n select(\n Student.id,\n Student.name,\n Student.email,\n Class.name.label(\"class_name\"),\n )\n .outerjoin(Class, Student.class_id == Class.id)\n .order_by(Student.name)\n )\n return [dict(row._mapping) for row in result]","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Database Setup","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"# database.py\nfrom sqlmodel import create_engine, SQLModel\nfrom sqlalchemy.ext.asyncio import create_async_engine, AsyncSession\nfrom sqlalchemy.orm import sessionmaker\nimport os\n\nDATABASE_URL = os.getenv(\n \"DATABASE_URL\",\n \"postgresql+asyncpg://user:password@localhost:5432/erp_db\"\n)\n\n# Async engine\nasync_engine = create_async_engine(DATABASE_URL, echo=True)\nasync_session_maker = sessionmaker(\n async_engine,\n class_=AsyncSession,\n expire_on_commit=False,\n)\n\nasync def init_db():\n \"\"\"Initialize database tables\"\"\"\n async with async_engine.begin() as conn:\n await conn.run_sync(SQLModel.metadata.create_all)\n\nasync def get_db() -> AsyncSession:\n async with async_session_maker() as session:\n try:\n yield session\n await session.commit()\n except Exception:\n await session.rollback()\n raise\n finally:\n await session.close()","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Migrations with Alembic","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"# alembic/env.py\nfrom sqlalchemy import pool\nfrom sqlalchemy.engine import Connection\nfrom sqlalchemy.ext.asyncio import async_engine_from_url\nfrom alembic import context\n\n# Import models for autogenerate\nfrom models.student import Student\nfrom models.fee import Fee\nfrom models.attendance import Attendance\n\ntarget_metadata = SQLModel.metadata\n\n# alembic revision --autogenerate -m \"Initial migration\"\n# alembic upgrade head","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"References","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"SQLModel Documentation: https://sqlmodel.tiangolo.com","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"SQLAlchemy Relationships: https://docs.sqlalchemy.org/en/20/orm/relationships.html","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"SQLAlchemy Async: https://docs.sqlalchemy.org/en/20/orm/extensions/asyncio.html","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Pydantic Validation: https://docs.pydantic.dev/latest/usage/validators/","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Alembic Migrations: https://alembic.sqlalchemy.org/en/latest/","type":"text"}]}]}]},{"type":"hr","attrs":{"markup":"---"}}]},"metadata":{"date":"2026-06-05","name":"sqlmodel-crud","author":"@skillopedia","source":{"stars":336,"repo_name":"marketplace","origin_url":"https://github.com/aiskillstore/marketplace/blob/HEAD/skills/awais68/sqlmodel-crud/SKILL.md","repo_owner":"aiskillstore","body_sha256":"faf762e1c585b4c1449dba77e0a64c1f6bb7e8eb902e75fb1f246c8db63e4f03","cluster_key":"c6449cc5745be5455aefe3cdd7e858694b0da7d03eb8c45909614a477bc269c1","clean_bundle":{"format":"clean-skill-bundle-v1","source":"aiskillstore/marketplace/skills/awais68/sqlmodel-crud/SKILL.md","attachments":[{"id":"678154b4-aad9-54ae-9353-300198ec56aa","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/678154b4-aad9-54ae-9353-300198ec56aa/attachment.json","path":"skill-report.json","size":17643,"sha256":"b38e96ea9ba917b1d4337a0dd764c3d92b4707302d39921e218bb5e347d4322b","contentType":"application/json; charset=utf-8"}],"bundle_sha256":"5eee8eb4fd37303d6f3e1957f7d3fd2de9dcf699820ade864f310f850ce9cc28","attachment_count":1,"text_attachments":1,"attachment_storage":"skillopedia-attachments-v1","binary_attachments":0,"excluded_attachments":[]},"cluster_size":1,"skill_md_path":"skills/awais68/sqlmodel-crud/SKILL.md","import_metadata":{"date":"2026-06-05","author":"@skillopedia","version":"v1","category":"data-analytics","category_label":"Data"},"exact_dupes_collapsed_into_this":0},"version":"v1","category":"data-analytics","import_tag":"clean-skills-v1","description":"Use when creating SQLModel database models, CRUD operations, queries with joins, or relationships.\nNOT when non-database operations, plain SQL, or unrelated data handling.\nTriggers: \"SQLModel\", \"database model\", \"CRUD\", \"create/read/update/delete\", \"query\", \"ForeignKey\", \"relationship\".\n"}},"renderedAt":1782979417310}

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.