ORMs & Typing (SQLModel)

🏗️ ORMs & Typing (SQLAlchemy & SQLModel)

For robust Data Engineering applications, using an Object-Relational Mapper (ORM) provides a safe, typed way to interact with databases.

🏗️ 1. Why use an ORM?

Type Safety: Avoid SQL injection and catch errors early.
Abstraction: Write Python code that works with PostgreSQL, MySQL, or SQLite.
Migration Management: Use Alembic to manage database schema changes.

🚀 2. SQLModel: The Modern Way

SQLModel combines SQLAlchemy (the engine) and Pydantic (the validation) into one library.

from sqlmodel import Field, SQLModel, create_engine, Session, select

# Define a single model for both Database and API validation
class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: int | None = Field(default=None, index=True)

sqlite_url = "sqlite:///database.db"
engine = create_engine(sqlite_url)

def create_hero(name: str, secret_name: str, age: int):
    hero = Hero(name=name, secret_name=secret_name, age=age)
    with Session(engine) as session:
        session.add(hero)
        session.commit()

# Querying with type hints
def get_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.age > 20)
        return session.exec(statement).all()

🛠️ 3. Performance & SQLAlchemy

While ORMs are convenient, they can be slow for bulk operations.

Performance Best Practices:

Batch Insertion: Use session.bulk_insert_mappings() or insert().values([...]).
Eager Loading: Use joinedload or subqueryload to avoid N+1 query problems.
Async SQLAlchemy: Use create_async_engine for high-concurrency web applications.

🚦 4. Database Migrations (Alembic)

Always use a migration tool to track schema changes.

alembic init alembic
alembic revision --autogenerate -m "Add hero table"
alembic upgrade head