Implementing the Active Record Pattern in Python with SQLModel

While working with SQLModel in Python, I found myself missing the elegant database interactions of Rails. With a lot of assistance from Claude, I discovered this pattern for bringing Active Record-style elegance to Python while maintaining its strong typing benefits.

When transitioning from Ruby on Rails to Python, many developers miss the elegance of Active Record. While Python's SQLAlchemy (and by extension, SQLModel) takes a different approach by default, we can implement a similar pattern that brings the convenience of Rails-style models to our Python applications while maintaining type safety and following Python best practices.

The Active Record Pattern

The Active Record pattern, popularized by Ruby on Rails, treats database records as objects with methods for database operations. Instead of using separate repository classes or data access objects (DAOs), the model itself knows how to interact with the database.

For example, in Rails you might write:

# Find a record
user = User.find(123)

# Update it
user.name = "New Name"
user.save

# Create a new record
post = Post.create(title: "Hello World")

Implementing in Python with SQLModel

While Python's SQLModel doesn't provide this pattern out of the box, we can implement it with a base class that provides these familiar operations. Here's how:

1. The Base CRUD Model

First, we create a base class that implements common CRUD operations:

from typing import TypeVar, List, Optional, Tuple
from datetime import datetime
import uuid
from sqlmodel import SQLModel, Session, select
from sqlalchemy import func

T = TypeVar("T", bound="CRUDModel")

class CRUDModel(SQLModel):
    id: str = Field(
        default_factory=lambda: str(uuid.uuid4()),
        primary_key=True
    )
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)

    @classmethod
    def all(cls: type[T], session: Session) -> List[T]:
        statement = select(cls)
        return session.exec(statement).all()

    @classmethod
    def find(cls: type[T], session: Session, id: str) -> Optional[T]:
        statement = select(cls).where(cls.id == id)
        return session.exec(statement).first()

    @classmethod
    def create(cls: type[T], session: Session, **kwargs) -> T:
        db_obj = cls(**kwargs)
        session.add(db_obj)
        session.commit()
        session.refresh(db_obj)
        return db_obj

    def update(self: T, session: Session, **kwargs) -> T:
        kwargs['updated_at'] = datetime.utcnow()
        for key, value in kwargs.items():
            setattr(self, key, value)
        session.add(self)
        session.commit()
        session.refresh(self)
        return self

    def delete(self: T, session: Session) -> None:
        session.delete(self)
        session.commit()

    @classmethod
    def paginate(
        cls: type[T],
        session: Session,
        page: int = 1,
        per_page: int = 20
    ) -> Tuple[List[T], int]:
        statement = select(cls)
        total = session.exec(select(func.count()).select_from(statement)).one()

        offset = (page - 1) * per_page
        results = session.exec(
            statement.offset(offset).limit(per_page)
        ).all()

        return results, total

2. Using the Pattern in Your Models

With our base class defined, we can create models that inherit from it:

class Article(CRUDModel, table=True):
    title: str = Field(..., description="Article title")
    content: str = Field(..., description="Article content")
    status: str = Field(default="draft")

    # Relationships
    comments: List["Comment"] = Relationship(
        back_populates="article",
        sa_relationship_kwargs={"cascade": "all, delete-orphan"}
    )

3. Using the Models

Now we can use our models with a familiar Rails-like syntax, while maintaining Python's explicit session management:

from db.session import get_session

# List all articles
with get_session() as session:
    articles = Article.all(session)

# Find a specific article
with get_session() as session:
    article = Article.find(session, "some-uuid")
    if article:
        print(f"Found: {article.title}")

# Create a new article
with get_session() as session:
    article = Article.create(
        session,
        title="My New Article",
        content="Some content here"
    )

# Update an article
with get_session() as session:
    article = Article.find(session, "some-uuid")
    if article:
        updated = article.update(
            session,
            title="Updated Title",
            content="New content"
        )

# Delete an article
with get_session() as session:
    article = Article.find(session, "some-uuid")
    if article:
        article.delete(session)

# Pagination
with get_session() as session:
    articles, total = Article.paginate(session, page=2, per_page=10)

Key Differences from Rails

While this pattern brings Rails-like convenience to Python, there are some important differences to note:

1. Explicit Session Management: Python requires explicit session management, which promotes better understanding of database transactions.

# Python with SQLModel
with get_session() as session:
    article = Article.create(session, title="Hello")

# vs Rails
article = Article.create(title: "Hello")

1. Type Safety: Python's type hints provide better IDE support and catch errors earlier.

class Article(CRUDModel, table=True):
    title: str  # Type safety!
    views: int = Field(default=0)

1. Class Methods: Python uses explicit @classmethod decorators for operations that don't require an instance.

2. Error Handling: Python encourages explicit exception handling:
   

with get_session() as session:
    try:
        article = Article.find(session, "non-existent")
        if article is None:
            raise HTTPException(status_code=404, detail="Article not found")
    except Exception as e:
        # Handle other database errors
        raise HTTPException(status_code=500, detail=str(e))

Best Practices

When using this pattern in Python, keep these best practices in mind:

1. Always Use Context Managers:

   # Good
   with get_session() as session:
       article = Article.create(session, title="Hello")

   # Not Good
   session = get_session()
   article = Article.create(session, title="Hello")
   session.close()

1. Type Safety:

   # Use proper type hints
   def get_article(id: str) -> Optional[Article]:
       with get_session() as session:
           return Article.find(session, id)

1. Validation:

   class Article(CRUDModel, table=True):
       title: str = Field(..., min_length=1, max_length=100)
       status: str = Field(
           default="draft",
           validate_default=True,
           validator=lambda x: x in ["draft", "published"]
       )

1. Relationship Management:

   class Article(CRUDModel, table=True):
       # Use cascade deletes appropriately
       comments: List["Comment"] = Relationship(
           back_populates="article",
           sa_relationship_kwargs={"cascade": "all, delete-orphan"}
       )

Conclusion

The Active Record pattern can be effectively implemented in Python while maintaining type safety and following Python best practices. While it requires more explicit session management than Rails, it provides similar convenience while giving developers more control over database operations.

This pattern is particularly useful for:

• Teams transitioning from Rails to Python
• Projects that prefer model-centric database operations
• Applications where type safety and explicit session management are important

Remember that this is just one approach to database operations in Python. SQLModel and SQLAlchemy support other patterns like repositories or data access objects, which might be more appropriate for certain use cases.

Resources

• SQLModel Documentation
• FastAPI with SQLModel
• SQLAlchemy Documentation
• Type Hints in Python