I think that our love/hate for ORMs stems from the 80/20 rule.

For 80% apps/workloads/programmers, ORMs are pretty helpful (in that they save you computation time, thinking time, etc).

For 20% of cases, the ORM tends to get in the way. In these cases, the ORM doesn't prevent you from needing to know SQL, it doesn't give you performance gains, and it actively obfuscates what's "really going on". These cases make your job harder, and are what stick up in your mind when you think about ORMs.

At my place of work, we use an ORM (SQLAlchemy), but codegen the "annoying/boring" bits of ORM code (model/class specification, table/index creation). IME, it's wonderful and good – until I start wondering about how much memory this query is going to take up, 5 minutes after the client has made it. I know that SQLAlchemy probably caches things, but I have no idea how it's cache policy is set. When will my query be garbage collected? How much memory should I request for my app?

I can probably find this out by digging into the SQLAlchemy docs, but I haven't yet so uncertainty looms. TFA's "I'd much rather spend an extra bit of time typing, but this will save me time reading ORM's documentation, optimizing my queries, and most importantly debugging" rings true to me.

With raw SQL, answering these questions might be more straightforward – but I only ask these questions 20% of the time! Anyways, there's some food for thought. :)

I love orms.

They make a simple crud operation so easy, you don't even have to think about them.

Every remotely competent developer will however tell you that there are always cases where the orm is a bad fit. And that's exactly why basically all orms let you write your own queries.

There is really no reason not to use an orm. Just don't ever frown on people doing anything more advanced than a middle join without it.

You don't need ORM for simple CRUD operations. All you need is a function you can pass a table name and a map of column names/values you want to insert/update.

Until you want to be able to sort and filter and do all of the other things that most CRUD applications do.

Once you're there, if you're doing SQL directly, now you're manipulating strings in your code, instead of being able to build some kind of object that then generates a query for you.

Yeah if you’re doing lots of CRUD that approach quickly becomes an exercise in writing your own (bad, buggy) ORM.

Not at all. The only cumbersome thing about SQL, that is common enough and unpleasant to always spell out by hand are basic INSERT and UPDATE commands.

I use a set of 3-4 functions that I reuse pretty much in all my programs for this. It's no ORM, as it doesn't map classes to tables/data in the database. It's just a shortcut to generate and execute INSERT/UPDATE commands on arbitrary tables with arbitrary columns.

Also it has zero bugs, because you can hardly create bugs in something so simple.

> you can hardly create bugs in something so simple.

You may have underestimated how bad things are in some of the unicorns..

1. Creating your own query builder before understanding the purpose of bind param: https://medium.com/tokopedia-engineering/dynamic-sql-query-b...

2. ???

3. Profit from SQL injection: https://www.youtube.com/watch?v=PKkNjOweWTE

It sounds like you are conflating an ORMs with query builders?

Query builders and ORMs often go hand in hand in my experience, as the ORM will expose the ability to perform custom querying capabilities around the specific object you're working with; however, you're correct in that I am more considering query builders than ORMs directly in this instance.

Reducing boilerplate can be very valuable. Why would you want to spend time writing yet another query when you could be focusing on the parts of the application that deliver value to your customer?

Maintainability is also something to think about. The classes you write for the ORM implicitly document the content model. It can be very hard to decipher the same information from a "show tables" and reading a bunch of SQL SELECT statements, especially if they are all mixed up in the code and broken up with string concatenation and variables. Future maintainers will be able to read the documentation for the ORM which will probably be better than trying to understand a bunch of custom code to deal with complex queries.

For an example of this, notice how in the article the author's "linking table" was nowhere to be found in the ORM section. That got abstracted away! Someone having to analyze the tables directly will be running into those sorts of things all over the place, which is more mental overhead for understanding the system.

I suppose it depends on your flavor of ORM. My experience using Django has been amazing from day one, and support for more exotic operations has improved by orders of magnitude since I last used it.

I think a good ORM is one that strives to make 80-90% of operations ridiculously easy, leaving edge cases to the developer who can always drop down to a raw query as needed. Django does that fairly well, IMHO, and my limited experience with SQLAlchemy felt pretty underwhelming in comparison

EDIT: Though admittedly I did not have access at the RDBMS level to write VIEWs to match my business needs back when I was using Django. If I did, then perhaps I could have chosen that approach – though I would have to forgo all the useful django-admin stuff that comes out of the box, so there's always a tradeoff...

> my limited experience with SQLAlchemy felt pretty underwhelming in comparison

That's interesting, because I usually hear of people having the exact opposite experience. The appeal of SQLAlchemy is that it lets you work at any level arbitrarily—you can use its high-level operations and abstractions for the 80-90% of common cases, but then drop down to its lower level and write complex queries where needed (including CTEs, window functions, stored procedures, etc), without ever resorting to writing SQL by hand. Django's ORM, in comparison, is very limited in the types of queries you can write with it, and is difficult and unintuitive to optimize.

Edit: That said, the Django ORM has improved by leaps and bounds over the past few years. These days, I'd say it gives you a way to cover about 90% of cases in an efficient manner (even if it has a bit of a learning curve). But if you're consistently doing more complex queries, I think SQLAlchemy is vastly superior.

ORM is good when you were going to have to be converting your query results into Python/Ruby/PHP objects defined by your framework. Why write that code yourself if the framework can handle it for you? Particularly if it's a common CRUD app.

"SQL is too complicated!"

I agree with you. I've experience with a few ORM apps, and, frankly, they always suck. In the beginning everything is perfect because an ORM makes writing trivial DB apps trivial. Then your app grows up and needs more features. Now the ORM only gets in the way, and you can't excise it easily. Now you're in a world of hurt.

Still waiting for a Golang ORM as awesome as Python's SQLAlchemy.

I like the code generation approach of SQLBoiler: https://github.com/volatiletech/sqlboiler

> How does it save time when I have to learn the ORM language, which probably has a lot less support and users?

The ORM is just an API to your database in your programming language. If you an handle any other API, you can handle an ORM.

It's has obvious benefits like automatically handling mapping database columns to objects, doing simple querying without lots of strings, etc.

All the time allegedly saved is also more than compensated for when the ORM makes bad queries that are roughly impossible to fix.

Frequently the easiest path to perform a complex query with an ORM results in N+1 performance disasters. The inevitable retort is something like "but the ORM has better ways to do that." Of course it does; ORM implementers aren't incompetent. The key word is "easiest," meaning the ORM user doesn't have to spend time learning anything beyond simply chaining method calls. In production such work goes haywire when resolving some related entity causes tens of millions of round trips.

Without an ORM the easiest path is to write a single join, giving the database optimizer a fair shot at correctly optimizing the query, including over time as the database evolves. Again, yes an ORM can encapsulate the equivalent query. Guess what; for every time these ORM capabilities are actually utilized there are probably hundreds of expediently written N+1 queries because the programmer either didn't know better or was indifferent.

To a pragmatic mind this is the inevitable outcome when a tool makes the inefficient solution the easiest thing to do.

I look at this as not playing to core competencies.

SQL's strength is that it's declarative.

By wrapping an ORM around it, suddenly you have a DB engine talking to an ORM engine through a declarative interface. There simply isn't enough information and context passed across the interface to properly optimize.

So in the end, it's like trying to run two optimizing compilers in series, where either knows the specs of the other.

It's the good old "pit of success" story. When doing the right thing is extra work, you will find countless examples of people doing the wrong thing. The only way to always do the right thing is to make that the easiest possible way.

I've only worked with a couple ORMs, but they all allowed me to write my own queries if I wanted to. The ORM generated 90%, but when there was a performance win I just wrote my own.

Yes, this happens with complicated schemas with complicated queries. ORMs account for this. Almost every major ORM allows you to override those "bad" queries when needed with your own manual SQL calls. It's an old problem with an old solution that works in production.

Most ORMs allow you to write SQL, no? So why not just use that when appropriate?

So, I'm the 'database guy' and I come in when the DB is on fire. I find the bad query(ies) and tell the application developer they need to do it another way. (And suggest ways)

Then they tell me, it's too hard / impossible to do something else, and that's after it took them two weeks to figure out how the query is formed.

For these applications, it's best to ensure their DB is isolated from other DB needs and let the tire fire burn as a signal to others, because I can help a lot with performance, but adding indexes can only help so much.

When I've had similar experiences with people running bad queries from strings, it's a lot easier to get traction on fixes. Even when they think my suggestions are crazy (nobody likes it when I suggest a client side join, however, sometimes it's significantly faster)

I agree mostly. I do like C#/LINQ based ORMs because as the name implies - it is integrated into the language and the “ORM” is treated like a first class citizen and it separates the query from the provider that translates it the destination. But even with LINQ, when you get into complicated queries or you have to do a left join, the syntax leaves a lot to be desired.

Also, it can get obtuse when you try to take advantage of any of its complex features.

I will never choose a real ORM for a green field project. The most I will usually want is something simple that takes in a SQL query and projects the result onto an object without all of the overhead - a Micro ORM like Stack Overflow’s Dapper.

And I agree, by the time you create DTOs and add attributes and the entire ceremony around ORMs. It really doesn’t reduce the boilerplate.

I am very happy with ORM.

I'm currently working on an e-shop. I need to display many tables with different filters.

Administrator wants products starting with string? OK: if filter['category']: qs = qs.filter ... Administrator wants products without category? Ok: if filter['category']: qs = qs.filter ... Add multilang supprt? Ok: subclass model, mark fields and translatable, run migrate script

I can combine any filters, ORM automatically creats query with necessary joins, subqueries, group by expressions etc.

If i didn't use ORM i would have to write code that generate select with cca 200 ifs because almost every filter can generate join or add fields to group by or generate subselect etc.

This is about the only use case where I find ORMs useful

Also a problem solved by less magic libraries, like MyBatis, jOOQ, SQLAlchemy Core, etc.

Inserting data into the database is usually where ORM's save the most time.

The friction depends on the situation * x number of teams x number of dBs x number of packages And the permutations (of x 1,2,many)

When it’s 1 team 1 package you can get away with whatever when it’s many DB many teams many packages you are going to find it very hard to refactor change the scheme when you don’t have a central location for the DB abstraction

In my experience an ORM will cause issues if used thoughtlessly throughout an application. Objects created by an ORM can be used "naively" and cause many round trips to the database, very inefficient queries, etc., but they boilerplate they can save is really helpful also.

I worked on a simple Python CRUD app project a few years ago with some folks who were absolutely against using an ORM for those reasons. There was so much code written that basically just took tuples returned from the DB and instantiated Python objects with them (and vice versa for inserts) that making changes to anything was absolutely maddening. I find that SQLAlchemy Core can be used very simply to essentially take a DB query and return Python objects.

Have you ever tried it? Your coworkers are right. It also helps you avoid common mistakes like SQL injection. When the ORM doesn't work for a specific case, they usually have ways where you can override the ORM and use your own SQL calls. Virtually every major, popular ORM does this across different languages (can personally confirm with Ruby, Java, & Python ORMs - confident that JS ones support it too).

SQL injection is prevented by not using user input as a part of the SQL query. It's orthogonal concern to whether to use ORM or not.

I'm not sure I understand since there are common cases where you have no choice but to deal with user input. Sure you can manually escape user input in your manual sql calls, but if you're doing that manually there's no hard guarantee that you'll always escape that input vs using an ORM.

Christ, no, don't escape user input in SQL. Just use parameterized queries.

I disagree, and SQLi is not simply prevented by avoiding user input. There are many cases where you need to use user input for a SQL query and its a valid pattern, and can be done securely with Prepared Statements or Parameterization.

Using an ORM discourages you from writing SQL queries and it also automatically parameterized queries. This is a good thing! In fact, from experience, the single easiest way to mitigate a naive developer from introducing SQLi is requiring them to use an ORM. People should not be constructing SQL statements by hand today, its too easy to mess this up. SQLi shouldnt be a thing in 2019, but, it is.

My point is it is VERY MUCH not orthogonal, its very much related. Avoid naive SQLi, use an ORM. Directly related.

ORM builds on top of mechanisms for prepared statements or parametrization.

Yes, ORM API can perhaps limit developers to such an extent, that they can't construct SQL themselves, and thus can't make the mistakes leading to SQL injection.

You can't avoid use user input. How would you login with a username and password without user input?

Django's ORM will sanitize input when you pass it in as raw SQL.

SQL query is a string. (that's where you don't pass the user input, not even quoted/escaped, as a policy) User input is passed "out of band", meaning not as a part of the query string. How exactly that happens depends on the RDBMS's particular client/server interface.

That's all that's enough to avoid SQL injections. And it has nothing to do with ORM.

That is orthogonal. There are ORMs which make SQL injection easy and there are those which make it hard, just as for SQL connector libraries.

This is a matter of interface design.

Which ORMs make SQL Injection easy to happen? From my experience input sanitation is one of the main features of every major ORM. I am not aware of any popular ORM that makes it easy for SQL injection to occur.