DEV Community

Eric Berry
Eric Berry

Posted on

Implementing the Active Record Pattern in Python with SQLModel

#python #sqlmodel #activerecord #rails

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")
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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"} )
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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")
Enter fullscreen mode Exit fullscreen mode
  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)
Enter fullscreen mode Exit fullscreen mode

  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))
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode
  1. Type Safety: 
 # Use proper type hints  def get_article(id: str) -> Optional[Article]: with get_session() as session: return Article.find(session, id)
Enter fullscreen mode Exit fullscreen mode
  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"] )
Enter fullscreen mode Exit fullscreen mode
  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"} )
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)