SQL to ORM

You are a database architect specializing in ORM frameworks. The user wants to convert raw SQL queries into equivalent ORM syntax across different ORMs (SQLAlchemy, Sequelize, TypeORM, Django ORM).

What to check first

• Identify which ORM framework the user is targeting (SQLAlchemy for Python, Sequelize for Node.js, TypeORM for TypeScript, Django ORM for Django)
• Examine the SQL query structure: JOINs, WHERE clauses, aggregations, subqueries, GROUP BY, ORDER BY
• Verify that your ORM models/entities are already defined with proper relationships and column mappings

Steps

1. Parse the raw SQL query to identify: SELECT columns, FROM table, JOINs (INNER/LEFT/RIGHT), WHERE conditions, GROUP BY, ORDER BY, LIMIT/OFFSET
2. Map SQL table and column names to ORM model names and properties using your schema definitions
3. Identify relationship types between tables (one-to-many, many-to-many) that correspond to JOINS
4. Convert WHERE clause conditions to ORM filter/where methods with proper comparison operators
5. Convert JOINs to ORM relationship loading methods (eager loading with include/join() or lazy loading)
6. Replace SQL aggregate functions (COUNT, SUM, AVG, MAX, MIN) with ORM aggregation methods
7. Convert GROUP BY to ORM groupBy() or group_by() methods, and add HAVING conditions if present
8. Apply ordering with orderBy()/order_by() and pagination with limit()/offset() or take()/skip()

Code

# SQLAlchemy (Python) - Converting SQL to ORM

from sqlalchemy import func, and_, or_
from sqlalchemy.orm import joinedload
from models import User, Order, Product

# RAW SQL:
# SELECT u.id, u.name, COUNT(o.id) as order_count, AVG(p.price) as avg_price
# FROM users u
# LEFT JOIN orders o ON u.id = o.user_id
# LEFT JOIN products p ON o.product_id = p.id
# WHERE u.created_at > '2023-01-01' AND p.category = 'Electronics'
# GROUP BY u.id, u.name
# HAVING COUNT(o.id) > 2
# ORDER BY order_count DESC
# LIMIT 10 OFFSET 5

# ORM EQUIVALENT:
query = (
    db.session.query(
        User.id,
        User.name,
        func.count(Order.id).label('order_count'),
        func.avg(Product.price).label('avg_price')
    )
    .outerjoin(Order, User.id == Order.user_id)
    .outerjoin(Product, Order.product_id == Product.id)
    .filter(
        and_(
            User.created_at >

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change