pony

pony Best Practices

Pony ORM simplifies database interactions with Pythonic syntax and powerful optimizations. Adhere to these rules for robust, high-performance applications.

1. Always Use db_session for Transaction Management

The db_session context manager is non-negotiable. It ensures proper transaction handling, automatic commits, and rollbacks, preventing resource leaks and data inconsistencies.

❌ BAD: Direct database operations without a session.

from pony.orm import *
from decimal import Decimal

db = Database('sqlite', ':memory:')

class Product(db.Entity):
    name = Required(str)
    price = Required(Decimal)

db.generate_mapping(create_tables=True)

# This will fail or lead to unexpected behavior in production
p = Product(name='Laptop', price=Decimal('1200.00'))
db.commit() # Manual commit is error-prone and easily forgotten

✅ GOOD: Encapsulate all database work within db_session.

from pony.orm import *
from decimal import Decimal

db = Database('sqlite', ':memory:')

class Product(db.Entity):
    name = Required(str)
    price = Required(Decimal)

db.generate_mapping(create_tables=True)

@db_session
def create_product(name: str, price: Decimal) -> Product:
    p = Product(name=name, price=price)
    return p # Auto-committed on exit if no exception

with db_session:
    laptop = create_product('Laptop', Decimal('1200.00'))
    print(f"Created product: {laptop.name}")

2. Leverage Pythonic Query Syntax for Optimization

Pony translates generator expressions and lambdas into optimized SQL, automatically handling N+1 problems and complex joins. Avoid manual SQL unless absolutely necessary.

❌ BAD: Manual filtering or inefficient data access.

@db_session
def get_expensive_products_bad():
    all_products = list(select(p for p in Product)) # Fetches all products
    expensive_products = [p for p in all_products if p.price > Decimal('1000')] # Filters in Python
    return expensive_products

✅ GOOD: Let Pony generate optimized SQL.

@db_session
def get_expensive_products_good() -> list[Product]:
    # Pony translates this directly to SQL: SELECT ... WHERE price > 1000
    return select(p for p in Product if p.price > Decimal('1000'))[:] # Use [:] to fetch all results