The problem with SQL is that its abstraction capabilities are pretty crappy, and the code is accordingly verbose, hard to understand, bug prone and tightly coupled. ORM’s (atleast the good ones) present you with a way to abstract common relational operations into simpler easily composable terms.
Heres an example in SQLAlchemy, a Python ORM, although this probably works the similarly in other ORMs. (sorry for the long post, but I think the illustrative examples are worth it)
Simple selecting is not all that much different:
print Customer.query.filter_by(country=“USA”).order_by(Customer.company_name)
SELECT customer.id AS customer_id, customer.name AS customer_name, customer.country AS customer_country, customer.company_name AS customer_company_name
FROM customer
WHERE customer.country = %(customer_country)s ORDER BY customer.company_name
But lets say we need only the rich customers that have more than 1M total cash on their accounts:
print Customer.query.filter_by(country=“USA”).filter(select([func.sum(Account.balance)]).where(Account.customer_id == Customer.id).as_scalar() 10**6).order_by(Customer.company_name)
SELECT customer.id AS customer_id, customer.name AS customer_name, customer.country AS customer_country, customer.company_name AS customer_company_name
FROM customer
WHERE customer.country = %(customer_country)s AND (SELECT sum(account.balance)
FROM account
WHERE account.customer_id = customer.id) %(literal)s ORDER BY customer.company_name
This is pretty much the same on both sides, with ORM code being terser but not exactly less complex or that much shorter. But important part is, we probably need to know the total worth of our customers in many places, so we want to abstract that part out. To do this we add to our mapper the following property declaration:
total_cash=column_property(select([func.sum(account_table.c.balance)],
account_table.c.customer_id == customer_table.c.id).label(‘total_cash’), deferred=True)
Now that query becomes:
print Customer.query.filter_by(country=“USA”).filter(Customer.total_cash 10**6).order_by(Customer.company_name)
SELECT customer.id AS customer_id, customer.name AS customer_name, customer.country AS customer_country, customer.company_name AS customer_company_name
FROM customer
WHERE customer.country = %(customer_country)s AND (SELECT sum(account.balance)
FROM account
WHERE account.customer_id = customer.id) %(literal)s ORDER BY customer.company_name
Now I’d say the ORM has a clear advantage in clarity, if this is used in many places code duplication is lessened and the definition of total cash is in one single place so we can update the definition of that with little effort.
This also helps the separation of concerns. For example the predicates that a resultset has to match are the concern of the business logic side of things, while additional entities that need to be joined are determined by how we are going to display that resultset.
For example, the presentation layer might know that it is going to also display all the account details of the returned customers so we can add join for that independently of the rest of the query:
print Customer.query.filter_by(country=“USA”).filter(Customer.total_cash 10**6).order_by(Customer.company_name).options(eagerload(‘accounts’))
SELECT customer.id AS customer_id, customer.name AS customer_name, customer.country AS customer_country, customer.company_name AS customer_company_name, account_1.id AS account_1_id, account_1.customer_id AS account_1_customer_id, account_1.name AS account_1_name, account_1.balance AS account_1_balance
FROM customer LEFT OUTER JOIN account AS account_1 ON customer.id = account_1.customer_id
WHERE customer.country = %(customer_country)s AND (SELECT sum(account.balance)
FROM account
WHERE account.customer_id = customer.id) %(literal)s ORDER BY customer.company_name, account_1.id
The presentation also might deal with paging the results and know that we only need the first 10 customers, so add that too:
print Customer.query.filter_by(country=“USA”).filter(Customer.total_cash 10**6).order_by(Customer.company_name).options(eagerload(‘accounts’))[0:10]
SELECT customer.id AS customer_id, customer.name AS customer_name, customer.country AS customer_country, customer.company_name AS customer_company_name, account_1.id AS account_1_id, account_1.customer_id AS account_1_customer_id, account_1.name AS account_1_name, account_1.balance AS account_1_balance
FROM (SELECT customer.id AS customer_id, customer.company_name AS customer_company_name
FROM customer
WHERE customer.country = %(customer_country)s AND (SELECT sum(account.balance)
FROM account
WHERE account.customer_id = customer.id) %(literal)s ORDER BY customer.company_name
LIMIT 10 OFFSET 0) AS tbl_row_count, customer LEFT OUTER JOIN account AS account_1 ON customer.id = account_1.customer_id
WHERE customer.id = tbl_row_count.customer_id ORDER BY tbl_row_count.customer_company_name, account_1.id
Now that SQL could be cut down a bit by using select-star and formatted better, but it definitely isn’t easier to write, understand or maintain than the ORM query. And that isn’t even a particularly large query.