rhosocial-activerecord 1.0.0.dev23

A modern, Pythonic implementation of the ActiveRecord pattern, providing an elegant and intuitive interface for database operations with type safety and rich features.

rhosocial-activerecord ($\rho_{\mathbf{AR}}$)

A Modern, Standalone ActiveRecord Implementation for Python

Built on Pydantic Only · Full Type Safety · True Sync-Async Parity · AI-Native Design

⚠️ Development Stage: This project is under active development. APIs may change, and some features are not yet production-ready.

Why This Project?

1. ActiveRecord Pattern Is Intuitive

The ActiveRecord pattern—where a class represents a table and an instance represents a row—maps directly to how developers think:

user = User(name="Alice")  # Create
user.save()                # Persist
user.name = "Bob"          # Modify  
user.save()                # Update

Simple, consistent, and easy to reason about. This is what Python's been missing.

2. Python Lacks a Standalone ActiveRecord Ecosystem

	rhosocial-activerecord	SQLAlchemy	Django ORM
Pattern	ActiveRecord	Data Mapper	ActiveRecord (coupled)
Standalone	✅ Yes	✅ Yes	❌ Django only
Dependencies	Pydantic only	Self-contained	Django framework
Async	Native parity	2.0 via greenlet	Django 4.1+ only

• SQLAlchemy is excellent but follows the Data Mapper pattern—not ActiveRecord
• Django ORM is ActiveRecord but tightly coupled to Django; can't use it in FastAPI, Flask, or scripts without the entire Django stack
• We fill the gap: A standalone, modern, feature-complete ActiveRecord for all Python applications

3. Built From Scratch, Not a Wrapper

Traditional ORM Architecture: Your Code → ORM API → SQLAlchemy/Django → Database Driver → Database

Our Architecture: Your Code → rhosocial-activerecord → Database Driver → Database

We built this from the ground up with Pydantic as the only dependency. No SQLAlchemy underneath. No Django ORM wrapper. This means zero hidden complexity, complete SQL control, a smaller footprint, and a simpler mental model — one layer to understand, not three.

	rhosocial-activerecord	SQLAlchemy	Django ORM
Core dependency	Pydantic only	Standalone	Django framework
Query style	Expression objects + .to_sql()	Expression language or ORM	QuerySet chaining
SQL transparency	Every query exposes .to_sql()	Via compile()	Limited via .query
Sync/Async	Native parity (same API surface)	2.0 async via greenlet	Async views (Django 4.1+)

Architecture Highlights

Expression-Dialect Separation — Query structure and SQL generation are completely decoupled. Expressions define what you want; Dialects handle backend-specific SQL (SQLite, MySQL, PostgreSQL). Call .to_sql() on any query to inspect the generated SQL before execution.

True Sync-Async Parity — Native implementations, not async wrappers around sync code. Same method names, same patterns, just add await.

Type-First Design with Pydantic v2 — Every field is type-safe, validated, and IDE-friendly. Full autocomplete support, runtime validation, and no Any types in public APIs.

Quick Start

Installation

pip install rhosocial-activerecord

End-to-End Example

"""Save as demo.py and run with: python demo.py"""
from rhosocial.activerecord.model import ActiveRecord
from rhosocial.activerecord.backend.impl.sqlite import SQLiteBackend
from rhosocial.activerecord.backend.impl.sqlite.config import SQLiteConnectionConfig
from rhosocial.activerecord.backend.expression import ColumnDefinition, CreateTableExpression
from rhosocial.activerecord.backend.expression.statements import (
    ColumnConstraint, ColumnConstraintType
)
from rhosocial.activerecord.base import FieldProxy
from typing import ClassVar, Optional
from pydantic import Field


class User(ActiveRecord):
    __table_name__ = "users"
    id: Optional[int] = None # Primary key
    name: str = Field(max_length=100)
    email: str
    age: int = 0
    c: ClassVar[FieldProxy] = FieldProxy()


# Configure backend (in-memory SQLite for demo)
config = SQLiteConnectionConfig(database=":memory:")
User.configure(config, SQLiteBackend)

# Create table using DDL expression (type-safe, no raw SQL)
create_table = CreateTableExpression(
    dialect=User.__backend__.dialect,
    table_name="users",
    columns=[
        ColumnDefinition("id", "INTEGER",
            constraints=[ColumnConstraint(ColumnConstraintType.PRIMARY_KEY)]),
        ColumnDefinition("name", "VARCHAR(100)",
            constraints=[ColumnConstraint(ColumnConstraintType.NOT_NULL)]),
        ColumnDefinition("email", "VARCHAR(255)"),
        ColumnDefinition("age", "INTEGER"),
    ]
)
User.__backend__.execute(create_table)

# Insert
alice = User(name="Alice", email="[e-mail]", age=30)
alice.save()

# Query with type-safe expressions
adults = User.query().where(User.c.age >= 18).all()

# Inspect generated SQL without executing
sql, params = User.query().where(User.c.age >= 18).to_sql()
# SQL: SELECT * FROM "users" WHERE "users"."age" >= ?
# Params: (18,)

Relationships

from rhosocial.activerecord.model import ActiveRecord
from rhosocial.activerecord.backend.impl.sqlite import SQLiteBackend
from rhosocial.activerecord.backend.impl.sqlite.config import SQLiteConnectionConfig
from rhosocial.activerecord.backend.expression import ColumnDefinition, CreateTableExpression
from rhosocial.activerecord.backend.expression.statements import (
    ColumnConstraint, ColumnConstraintType,
    TableConstraint, TableConstraintType,
    ForeignKeyConstraint, ReferentialAction
)
from rhosocial.activerecord.base import FieldProxy
from rhosocial.activerecord.relation import HasMany, BelongsTo
from typing import ClassVar, Optional


class Author(ActiveRecord):
    __table_name__ = "authors"
    id: Optional[int] = None
    name: str
    c: ClassVar[FieldProxy] = FieldProxy()
    posts: ClassVar[HasMany["Post"]] = HasMany(foreign_key="author_id")


class Post(ActiveRecord):
    __table_name__ = "posts"
    id: Optional[int] = None
    title: str
    author_id: int
    c: ClassVar[FieldProxy] = FieldProxy()
    author: ClassVar[BelongsTo["Author"]] = BelongsTo(foreign_key="author_id")


# Configure backend
config = SQLiteConnectionConfig(database=":memory:")
Author.configure(config, SQLiteBackend)
Post.__backend__ = Author.__backend__

# Create tables using DDL expressions
dialect = Author.__backend__.dialect

Author.__backend__.execute(CreateTableExpression(
    dialect=dialect,
    table_name="authors",
    columns=[
        ColumnDefinition("id", "INTEGER",
            constraints=[ColumnConstraint(ColumnConstraintType.PRIMARY_KEY)]),
        ColumnDefinition("name", "TEXT"),
    ]
))

Author.__backend__.execute(CreateTableExpression(
    dialect=dialect,
    table_name="posts",
    columns=[
        ColumnDefinition("id", "INTEGER",
            constraints=[ColumnConstraint(ColumnConstraintType.PRIMARY_KEY)]),
        ColumnDefinition("title", "TEXT"),
        ColumnDefinition("author_id", "INTEGER"),
    ],
    constraints=[
        ForeignKeyConstraint(
            columns=["author_id"],
            reference_table="authors",
            reference_columns=["id"],
            on_delete=ReferentialAction.CASCADE
        )
    ]
))

# Eager loading — one query, no N+1
authors = Author.query().with_("posts").all()
for author in authors:
    print(f"{author.name}: {[p.title for p in author.posts()]}")

Async Parity: All sync APIs have async counterparts. For async models, use AsyncActiveRecord, AsyncHasMany, and AsyncBelongsTo instead. The API surface is identical—just add await.

⚠️ Note: The built-in SQLite async backend is currently for testing only. For other backends (MySQL, PostgreSQL, etc.), async support depends on the specific implementation.

Features

All features support both sync and async APIs with identical method names—just add await.

DDL Expressions

Type-safe schema definition without raw SQL:

• CreateTableExpression — Create tables with columns and constraints
• DropTableExpression — Drop tables with IF EXISTS support
• AlterTableExpression — Add/drop columns, rename tables
• CreateIndexExpression — Create indexes with IF NOT EXISTS
• CreateViewExpression — Create views from query expressions

Query Builders

Three core query types, each with full sync/async parity:

• ActiveQuery / AsyncActiveQuery — Model-based queries with WHERE, JOIN, ORDER BY, LIMIT, aggregations, and eager loading via .with_()
• CTEQuery / AsyncCTEQuery — Common Table Expressions (WITH clauses) for recursive queries and complex multi-step operations
• SetOperationQuery / AsyncSetOperationQuery — UNION, INTERSECT, EXCEPT operations

Relationships

Type-safe relationship descriptors with eager loading support:

• BelongsTo / AsyncBelongsTo — Child-to-parent associations
• HasOne / AsyncHasOne — One-to-one parent-to-child
• HasMany / AsyncHasMany — One-to-many parent-to-children

Field Mixins

Reusable mixins for common model behaviors:

• OptimisticLockMixin — Version-based concurrency control
• SoftDeleteMixin — Logical deletion with deleted_at timestamp
• TimestampMixin — Auto-managed created_at and updated_at
• UUIDMixin — UUID primary keys

Model Events

Lifecycle hooks for custom business logic:

• Model Events — before_insert, after_insert, before_update, after_update, before_delete, after_delete

For details, see the documentation.

Backend Support

Backend	Package	Sync	Async
SQLite	Built-in	✅ Stable	⚠️ Testing only
MySQL/MariaDB	rhosocial-activerecord-mysql	🔄 In progress	🔄 In progress
PostgreSQL	rhosocial-activerecord-postgres	🔄 In progress	🔄 In progress
Oracle	rhosocial-activerecord-oracle	📋 Planned	📋 Planned
SQL Server	rhosocial-activerecord-sqlserver	📋 Planned	📋 Planned

Requirements

• Python: 3.8+ (including 3.13t/3.14t free-threaded builds)
• Core Dependency: Pydantic 2.10+ (Python 3.8) or 2.12+ (Python 3.9+)
• SQLite: 3.25+ (for the built-in backend)

See Python Version Support for detailed compatibility.

Get Started with AI Code Agents

This project ships with built-in configurations for AI code agents and editors. Clone the repo and launch your preferred tool — project-specific skills, commands, and context files are discovered automatically.

git clone https://github.com/rhosocial/python-activerecord.git
cd python-activerecord

CLI Code Agents

Tool	How to start	What's included
Claude Code	claude	CLAUDE.md project instructions + .claude/skills/ (5 skills) + .claude/commands/
OpenCode	opencode	.opencode/commands/ (8 slash commands) + .opencode/hints.yml
Codex	codex	AGENTS.md project context

Editors

Cursor and Windsurf users can open the project folder directly. Both editors benefit from the CLAUDE.md and AGENTS.md context files at the project root, as well as the docs/LLM_CONTEXT.md structured reference.

For Any LLM

Feed docs/LLM_CONTEXT.md to your preferred LLM for a structured overview of the project's architecture, module map, and key concepts.

What can AI agents do?

See the AI-Assisted Development Guide for concrete examples of what AI agents can help you accomplish with this project — from generating models and queries to implementing new backends and running tests.

Documentation

• Getting Started Guide — Installation and basic usage
• AI-Assisted Development — Using AI code agents with this project
• Modeling Guide — Defining models, fields, and relationships
• Querying Guide — Complete query builder documentation
• Backend Development — Creating custom database backends
• Architecture Overview — Module structure and design decisions
• LLM Context — Structured context for AI assistants
• API Reference — Full API documentation

Contributing

We welcome contributions! Please see CONTRIBUTING.md for guidelines.

License

Apache License 2.0 — Copyright © 2026 vistart

---

Built with ❤️ by the rhosocial team

GitHub · Documentation · PyPI