SQLModel CRUD Skill

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: "ForeignKey", "back_populates", "relationship", "linked models"
• Validation: "Pydantic validators", "field constraints", "indexes"

Core Rules

1. Models: table=True with Pydantic Validation

# models/student.py
from sqlmodel import SQLModel, Field, Relationship
from datetime import datetime
from typing import Optional, List
from enum import Enum

class StudentRole(str, Enum):
    STUDENT = "student"
    TEACHER = "teacher"
    ADMIN = "admin"

class Student(SQLModel, table=True):
    """Student model with relationships to fees and attendance"""
    id: Optional[str] = Field(default=None, primary_key=True)
    name: str = Field(min_length=2, max_length=100, index=True)
    email: str = Field(unique=True, index=True)
    phone: Optional[str] = Field(default=None, pattern=r'^\+?[\d\s-]+$')
    password_hash: str
    role: StudentRole = Field(default=StudentRole.STUDENT)
    class_id: Optional[str] = Field(default=None, foreign_key="class.id", index=True)