SQLAlchemy Core vs ORM

 

Introduction

SQLAlchemy provides two powerful ways to interact with databases:

  • Core (SQL Expression Language) → More control, closer to SQL
  • ORM (Object Relational Mapping) → More abstraction, Pythonic

Choosing the right approach can significantly impact:

  • Performance
  • Maintainability
  • Scalability

What is SQLAlchemy Core?

Core is a low-level API that lets you write SQL-like expressions using Python. Best for:

  • Complex queries
  • Performance-critical operations
  • Fine-grained SQL control

Example: SQLAlchemy Core

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String

engine = create_engine("sqlite:///example.db")
metadata = MetaData()

users = Table(
    "users",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String),
)

metadata.create_all(engine)

# Insert
with engine.connect() as conn:
    conn.execute(users.insert().values(name="John"))

# Select
with engine.connect() as conn:
    result = conn.execute(users.select())
    for row in result:
        print(row)


What is SQLAlchemy ORM?

ORM allows you to work with Python classes instead of SQL tables.Best for:

  • Application development
  • Business logic
  • Faster development

Example: SQLAlchemy ORM

from sqlalchemy.orm import declarative_base, sessionmaker
from sqlalchemy import Column, Integer, String, create_engine

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True)
    name = Column(String)

engine = create_engine("sqlite:///example.db")
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

# Insert
user = User(name="Alice")
session.add(user)
session.commit()

# Select
users = session.query(User).all()
for u in users:
    print(u.name)

Core vs ORM – Side-by-Side Comparison

Feature    Core    ORM
Level            Low-level        High-level
Syntax            SQL-like        Python classes
Learning Curve            Medium        Easy
Performance            Faster        Slightly slower
Flexibility            High        Moderate
Use Case            Complex queries        App logic

Key Differences Explained

1. Abstraction Level

  • Core → You think in SQL
  • ORM → You think in Python objects

2.Performance

  • Core is generally faster
  • ORM adds a small overhead (object mapping)

Example:

  • Bulk operations → Core is better
  • Simple CRUD → ORM is perfect

3.Complexity Handling

  • Complex joins, subqueries → Core wins
  • Relationships, models → ORM wins

4.Development Speed

  • ORM → Faster to write & maintain
  • Core → More verbose but precise 

Real-World Use Cases E-commerce Application

Feature        Best Choice
User management        ORM
Order relationships        ORM
Analytics queries        Core
Reports (aggregations)        Core

Data Analytics System

Use Core

  • Heavy aggregations
  • Complex joins
  • Performance-critical queries

Web Application (FastAPI / Django)

Use ORM

  • Clean code
  • Faster development
  • Easy relationships 

Can You Use Both Together? (YES)

This is one of SQLAlchemy’s biggest strengths!

# ORM query
users = session.query(User).all()

# Core query inside ORM project
from sqlalchemy import text

result = session.execute(text("SELECT * FROM users"))

Best practice:

  • Use ORM for most work
  • Use Core when needed

Performance Optimization Tips

  • Use Core for bulk inserts
  • Use ORM with:
    • joinedload() (avoid N+1 problem)
    • Proper indexing
  • Avoid unnecessary .all() calls

Common Mistakes

  • Using ORM for heavy analytics queries
  • Avoiding Core completely
  • Mixing both without understanding
  • Ignoring performance impact 

Comments

Post a Comment

Popular posts from this blog

Database Integration in FastAPI (SQLAlchemy CRUD)

  Introduction So far, we’ve built APIs without storing data.But real-world applications need a database to: Store user data. Manage products. Persist application state. In this blog, we’ll connect FastAPI with a database using SQLAlchemy and perform CRUD operations . What is SQLAlchemy? SQLAlchemy is a powerful Python ORM (Object Relational Mapper) that allows you to: Interact with databases using Python code. Avoid writing raw SQL queries. Work with models instead of tables. Step 1: Install Dependencies pip install sqlalchemy (For SQLite, no extra driver needed) Project Structure Update app/ ├── main.py ├── database/ │ ├── connection.py ├── models/ │ ├── item.py ├── schemas/ │ ├── item.py ├── routes/ │ ├── item.py Step 2: Database Connection app/database/connection.py from sqlalchemy import create_engine from sqlalchemy . orm import sessionmaker , declarative_base DATABASE_URL = "sqlite:///./test.db" engine = create_engine ( DAT...
Read more

Middleware & CORS in FastAPI

  Introduction As your API grows, you may need to: Log requests Modify responses Handle cross-origin requests This is where Middleware and CORS (Cross-Origin Resource Sharing) come into play.They help control how requests and responses flow through your application. What is Middleware? Middleware is a function that runs: Before a request reaches your route After a response is returned Think of it as a pipeline layer between client and server. Request Flow with Middleware Client → Middleware → Route → Middleware → Response Creating Custom Middleware from fastapi import FastAPI , Request import time app = FastAPI () @ app . middleware ( "http" ) async def log_requests ( request : Request , call_next ): start_time = time . time() response = await call_next ( request ) process_time = time . time() - start_time print ( f"Request: { request . url} | Time: { process_time } " ) return response What This Middle...
Read more

Python Data Handling

Python Data Handling for Beginners In Python, data handling means storing, changing, and using data in different ways. In this post, you will learn: Lists Tuples Dictionaries Sets String Methods Type Conversion 1. Python Lists :Lists store many values in one variable.                fruits = ["apple", "banana", "mango"]                                                                       Method                          Use                                            append()          ...
Read more