Skip to content

README.md

Latest commit

 

History

History
233 lines (170 loc) · 7.63 KB

README.md

File metadata and controls

233 lines (170 loc) · 7.63 KB
Raw

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

PyPI version Python Tests Coverage Status Apache 2.0 License Powered by vistart

rhosocial ActiveRecord Logo

SQL Server Backend for rhosocial-activerecord

T-SQL Support · Enterprise Features · Sync & Async

Note: This is a backend implementation for rhosocial-activerecord. It cannot be used standalone.

Why This Backend?

1. SQL Server-Specific Optimizations

Feature This Backend Generic Solutions
RETURNING/OUTPUT Native OUTPUT clause Manual SELECT-after-write
Temporal Tables FOR SYSTEM_TIME Application-level audit logs
Partitioning PARTITION schemes/functions Manual sharding
Sequences Native CREATE SEQUENCE Identity/application counters

2. True Sync-Async Parity

Same API surface for both sync and async operations:

# Sync
users = User.query().where(User.c.age >= 18).all()

# Async - just add await
users = await User.query().where(User.c.age >= 18).all()

3. Built for Production

  • Connection pooling with configurable pool sizes
  • Transaction support with proper isolation levels
  • Error mapping from SQL Server error codes to Python exceptions
  • Type adapters for SQL Server-specific data types

Quick Start

Installation

pip install rhosocial-activerecord-sqlserver

Basic Usage

from rhosocial.activerecord.model import ActiveRecord
from rhosocial.activerecord.backend.impl.sqlserver import SQLServerBackend
from rhosocial.activerecord.backend.impl.sqlserver.config import SQLServerConnectionConfig
from typing import Optional

class User(ActiveRecord):
    __table_name__ = "users"
    id: Optional[int] = None
    name: str
    email: str

# Configure
config = SQLServerConnectionConfig(
    host="localhost",
    port=1433,
    database="myapp",
    username="sa",
    password="YourStrong!Passw0rd"
)
User.configure(config, SQLServerBackend)

# Use
user = User(name="Alice", email="alice@example.com")
user.save()

# Query with parameter binding
results = User.query().where("email = ?", ("alice@example.com",)).all()

💡 AI Prompt: "How do I configure connection pooling for SQL Server?"

SQL Server-Specific Features

OUTPUT / RETURNING

Retrieve inserted or updated rows with the OUTPUT clause:

# INSERT with OUTPUT
user = User(name="Alice", email="alice@example.com")
user.save()
print(user.id)  # Populated automatically via OUTPUT

Temporal Tables

Query historical state with FOR SYSTEM_TIME:

# As of a specific time
User.query().where(
    "user_id = ? FOR SYSTEM_TIME AS OF ?",
    (1, "2026-06-30T12:00:00"),
).all()

Partitioned Tables

SQL Server partition schemes and functions:

# Partitioned table DDL support
class Orders(ActiveRecord):
    __table_name__ = "orders"
    __partition__ = {
        "column": "order_date",
        "scheme": "ps_order_date",
    }
    order_id: int
    order_date: datetime

Sequences

Native sequence support:

# Use a sequence-backed column
class Ticket(ActiveRecord):
    __table_name__ = "tickets"
    ticket_no: int  # NEXT VALUE FOR ticket_seq

Requirements

  • Python: 3.9+ (including 3.13t/3.14t free-threaded builds)
  • Core: rhosocial-activerecord>=1.0.0
  • Driver: pyodbc>=5.0.0 (with ODBC Driver 17+ for SQL Server)

SQL Server Version Compatibility

Feature Min Version Notes
Basic operations 2017+ Core functionality
Temporal tables 2016+ FOR SYSTEM_TIME
Sequences 2012+ CREATE SEQUENCE
Partitioning 2008+ Partition functions/schemes
Columnstore indexes 2014+ Analytics acceleration
Memory-optimized tables 2014+ In-memory OLTP
PIVOT/UNPIVOT 2005+ Row-to-column transforms
OUTPUT clause 2005+ RETURNING equivalent
JSON functions 2016+ JSON_VALUE, JSON_QUERY
STRING_AGG 2017+ String aggregation
UTF-8 collation 2019+ _UTF8 collations

Recommended: SQL Server 2019+ for optimal feature support.

Get Started with AI Code Agents

This project supports AI-assisted development. Clone and open in your preferred tool:

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

Example AI Prompts

  • "How do I configure connection pooling for SQL Server?"
  • "Show me how to use temporal tables"
  • "How do I use the OUTPUT clause with INSERT?"
  • "Create a model with a partitioned table"

For Any LLM

Feed the documentation files in docs/ to your preferred LLM for context-aware assistance.

Testing

⚠️ CRITICAL: Tests MUST run serially. Do NOT use pytest -n auto or parallel execution.

# Run all tests
PYTHONPATH=src pytest tests/

# Run specific feature tests
PYTHONPATH=src pytest tests/rhosocial/activerecord_sqlserver_test/feature/basic/
PYTHONPATH=src pytest tests/rhosocial/activerecord_sqlserver_test/feature/query/

See the Testing Documentation for details.

Documentation

Comparison with Other Backends

Feature SQL Server PostgreSQL SQLite
RETURNING ✅ OUTPUT ✅ RETURNING ✅ RETURNING
Temporal Tables ✅ ❌ ❌
Sequences ✅ Native ✅ Native ⚠️ Emulated
Arrays ❌ ✅ Native ❌
JSON Type ⚠️ Functions only ✅ JSONB ⚠️ JSON1 extension

💡 AI Prompt: "When should I choose SQL Server over PostgreSQL for my project?"

Contributing

We welcome contributions! See CONTRIBUTING.md for guidelines.

License

Apache License 2.0 — Copyright © 2026 vistart

Built with ❤️ by the rhosocial team

GitHub · Documentation · PyPI