Query building is a key part of backend development, determining how applications interact with databases.
Developers can write raw SQL for full control and fine-tuned performance, or use ORM queries to work with data through high-level abstractions that improve readability and reduce boilerplate code.
Each approach has trade-offs between flexibility, safety, and productivity.
Database migrations manage changes to the database schema over time.
Tools like Alembic and Django migrations allow developers to version, apply, and roll back schema changes in a controlled and repeatable way.
Migrations ensure that database structure stays in sync across development, testing, and production environments.
Raw SQL Queries: Power and Precision
Raw SQL gives you direct control over the database, bypassing abstractions for maximum efficiency. It's ideal when ORMs fall short in complex scenarios, but requires careful handling to avoid security risks.
Raw SQL shines in performance-critical paths or when leveraging database-specific features. Let's explore its strengths, pitfalls, and safe usage in Python.
When to Use Raw SQL: Start with raw SQL for ad-hoc analysis, reporting, or optimizing bottlenecks where ORMs generate suboptimal queries.
1. High-performance needs: Bulk operations or analytical queries (e.g., complex JOINs with window functions).
2. Database-specific optimizations: PostgreSQL's JSONB queries or MySQL's full-text search.
3. Legacy integration: Interfacing with existing schemas or views.
Example in SQLAlchemy (core engine)
from sqlalchemy import create_engine, text
engine = create_engine("postgresql://user:pass@localhost/db")
with engine.connect() as conn:
result = conn.execute(text("SELECT users.id, COUNT(orders.id) FROM users JOIN orders ON users.id = orders.user_id GROUP BY users.id HAVING COUNT(orders.id) > 5"))
for row in result:
print(row)This raw query runs efficiently, avoiding ORM overhead.
Risks and Best Practices
Raw SQL exposes you to injection attacks and maintenance issues if not managed well.
1. Use parameterized queries to prevent SQL injection: text("SELECT * FROM users WHERE id = :id").bindparams(id=user_id).
2. Escape dynamic content with database drivers (e.g., psycopg2 for PostgreSQL).
3. Profile queries with EXPLAIN ANALYZE in PostgreSQL to identify slow spots.
4. Version control SQL files in your repo for reproducibility.
ORM Queries: Abstraction for Productivity
Object-Relational Mapping (ORM) translates Python objects into SQL, abstracting boilerplate for faster development.
Popular in Python via SQLAlchemy or Django ORM, it prioritizes readability over raw speed.
ORMs excel in CRUD-heavy apps, reducing errors through type safety and relationships. They generate SQL under the hood but let you focus on business logic.
Core Concepts and Syntax
ORM queries build on model definitions, using query builders for filters, joins, and aggregations.
SQLAlchemy ORM Example (define models first)
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
engine = create_engine("sqlite:///example.db")
Session = sessionmaker(bind=engine)
session = Session()
# Query example
users = session.query(User).filter(User.name.like('%John%')).order_by(User.id.desc()).all()This fetches filtered users safely, with auto-generated SQL.
Django ORM Example:
from myapp.models import User
users = User.objects.filter(name__icontains='John').order_by('-id')Key Features
Query Optimization Techniques
ORMS can bloat SQL—tune them for production.
1. Use selectinload() (SQLAlchemy) or select_related() (Django) for N+1 query fixes.
2. Paginate with limit()/offset() or Django's Paginator.
3. Profile with tools like sqlalchemy-utils or Django Debug Toolbar.
4. Hybrid properties for computed fields without extra queries.
Raw SQL Vs. ORM
Choosing between raw SQL and ORM depends on your app's needs—speed vs. developer velocity. Raw SQL wins for one-off analytics; ORMs scale for evolving apps.
Consider a e-commerce dashboard needing top users by order value.
Raw SQL (PostgreSQL)
SELECT u.id, u.name, SUM(o.total) as revenue
FROM users u JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name ORDER BY revenue DESC LIMIT 10;SQLAlchemy ORM:
from sqlalchemy import func
top_users = session.query(User.id, User.name, func.sum(Order.total).label('revenue'))\
.join(Order).group_by(User.id, User.name)\
.order_by(func.sum(Order.total).desc()).limit(10).all()Database Migrations: Schema Evolution Done Right
Migrations version-control your database schema, applying changes incrementally across environments.
They prevent "it works on my machine" disasters in teams.
Alembic and Django migrations automate ALTER statements, with rollback support and conflict detection.
Alembic Migrations for SQLAlchemy
Alembic integrates seamlessly with SQLAlchemy, supporting online/offline modes (latest in 1.13+).
Setup and Workflow
1. Install: pip install alembic.
2. Init: alembic init alembic.
3. Configure: alembic.ini with your engine URL.
4. Auto-generate: alembic revision --autogenerate -m "Add user email".
5. Apply: alembic upgrade head.
6. Rollback: alembic downgrade -1.
Example Migration (autogenerated file)
from alembic import op
import sqlalchemy as sa
def upgrade():
op.add_column('users', sa.Column('email', sa.String(), nullable=True))
def downgrade():
op.drop_column('users', 'email')Best Practices:
1. Review autogenerated migrations manually.
2. Use --sql flag for dry-run scripts.
3. Branch per feature for Git-friendly merges.
Django Migrations
Django's migrations are opinionated, model-driven, and battle-tested in production.
Workflow
1. Change models.py: (e.g., email = models.EmailField()).
2. Generate: python manage.py makemigrations.
3. Apply: python manage.py migrate.
4. Squash for cleanup: python manage.py squashmigrations app 0005.
Squashed Migration Example
Combines multiple into one for faster deploys.
Pro Tip: Use alembic batch for zero-downtime ALTERs on large tables; Django's RunPython for data migrations.
Class Sessions
Sales Campaign
We have a sales campaign on our promoted courses and products. You can purchase 1 products at a discounted price up to 15% discount.