
What ORMs have taught me: just learn SQL (2014) - bsg75
http://wozniak.ca/what-orms-have-taught-me-just-learn-sql
======
StevePerkins
Ten years ago, there was a blog post every other week bemoaning ORM's. Ten
years ago, those posts often had merit.

In 2016, this sentiment is outdated. A few points:

1\. If you think that using an ORM means you don't have to learn SQL, then
you're going to have a bad time. This is where most of the bad press
originates... from people who never really learned SQL _or_ their chosen ORM.
An ORM provides type checking at your application layer, and typically better
performance (unless you plan on hand-rolling your own multi-level cache
system). But you still must understand relational database fundamentals.

2\. If you're not using an ORM, then you ultimately end up writing one. _And
doing a far worse job than the people who focus on that for a living._ It's no
different from people who "don't need a web framework", and then go on to re-
implement half of Rails or Spring (without accounting for any CSRF
protection). Learning any framework at a professional level is a serious time
investment, and many student beginners or quasi-professional cowboys don't
want to do that. So they act like their hand-rolled crap is a badge of honor.

3\. Ten years ago, it was a valid complaint that ORM's made custom or complex
SQL impossible, didn't play well with stored procedures, etc. But it's 2016
now, and this is as obsolete as criticizing Java by pointing to the EJB 2.x
spec. I can't think of a single major ORM framework today that doesn't make it
easy to drop down to custom SQL when necessary.

~~~
PeCaN
> If you're not using an ORM, then you ultimately end up writing one.

I disagree with this. A lot of things people use ORMs for are rather easily
solved with stored procedures, especially in Postgres where you can write
stored procedures in Perl, Ruby, etc. Validations, “fat models”, etc are all
managed with SQL easily (and this means you get that functionality from
_anywhere you access the database_, not just from your framework with an ORM).
For convenient access you can roll 30 lines of Perl to wrap DBI or whatever (I
use Perl for most web backends these days) and call your stored procedures in
normal syntax with a little metaprogramming.

Maybe this sort of scheme (heavy usage of stored procedures and offload almost
everything to the DB) doesn't work for everyone, but I like databases and it
works for me.

~~~
titanomachy
How do you manage version-control on stored procedures? Can they be checked in
with the rest of the application logic?

~~~
sopooneo
I myself am wary of stored procedures except in very specific and uncommon
circumstances. That said, you could absolutely version control your stored
procedures by creating them from within database migration files that are
version controlled by default.

~~~
platz
What is the source of the wariness?

~~~
Udik
Don't know about the parent, but I always felt that stored procedures end up
by incorporating a good share of the business logic, extracting it from the
main code of the application. This creates a messy situation in which you have
your business logic split up between two completely separate and different
systems, one of which (the stored procedures) is much harder to read, write,
maintain and test. That said, I also deeply hate ORMs, I much prefer to use
thinner layer to interface with a database (query builders and row mappers).

~~~
platz
I also do think putting too much logic in stored procs is a danger, and was/is
a scourge for some shops. But was curious if that is the primary objection.

~~~
snuxoll
I try to avoid anything but mapping or querying data in sprocs, but sometimes
they are a better place to put logic that is needed to ensure consistency.
Your database will outlive your application, or you will eventually need to
integrate data from a third party system - as a result your database engine is
always the last line of defense to protect your data, and if that means using
a sproc than so be it. I'll admit, I've written _some_ business logic into
stored functions in PostgreSQL, but it's mostly a background job to update
cached data because it is much faster to do it in the database than pulling a
bunch of records down to a service and sending them back up.

------
Gustek
I like to say "ORMs make easy thing easier and hard things harder".

What i mean by that is, any simple CRUD operations are much easier in ORM. The
hard things, i mean any complex queries that need more than one join you are
probably better of writing yourself.

In the end i prefer to do inserts, updates and deletes with ORM (or some other
database abstraction tools) but most SELECTs i write myself, fetching exactly
what i need and mapping result to objects if needed manually.

~~~
IndianAstronaut
Do uou use metadata from the db driver to do type mappings?

~~~
Gustek
Not sure what you asking about. Do you mean if I define database structure in
my code? The answer is: yes. I don't know about any database abstraction
library that would provide you with convenience methods to query database with
type checking and stuff without giving it the schema.

------
manigandham
Not this again. Why is this coming up at all in 2016? There isn't even a valid
debate here.

ORMs are a tool, that's it. The relational operations of SQL and the object-
oriented (or functional) logic of your application code are usually very
different and it's nice to have a mapper that lets you interact with your
app's language while it takes cares of automatically mapping it to SQL.

For 99% of database ops where it's pulling some stuff out, editing some fields
and then saving it back, an ORM will save you a massive amount of time with
better safety, security and performance. Even complicated
queries/mappings/procedures run pretty well and you can always write raw SQL
when you need it. Modern ORMs will even let you execute custom SQL and still
give you conveniences like easy parameterization and hydrating the results
back as objects.

Maybe I've been spoiled with the .NET ecosystem with great ORMs like
NHibernate, EntityFramework, and Dapper (along with C# features like Linq) but
it seems like most of these complaints are from people who use shitty ORMs or
can't comprehend that it's just a tool that they can choose to use or skip, at
very granular method by method level. There's absolutely no reason for any
extreme here.

~~~
CuriousSkeptic
My experience with hibernate, entity framework, nhibernate, and lastly dapper
has basically left me thinking Dapper is all you'll ever want. I can't imagine
a use case where I'd rather opt for NHibernate or EF at the moment.

(I might add, Dapper in combination with C#6 even gives me enough type saftey
to be happy. String interpolation and the nameof operator complements dappers
DTO approach nicely)

Since you seem to have similar experiences Im curious. When would you ditch
dapper for the others?

~~~
mistermann
Do you use a Dapper extension for populating and persisting entities? (Last I
looked at it as I recall this wasn't default functionality.)

~~~
Renner1
Dapper does have very low-level basic functionality to insert or update
entities. You need to write the SQL yourself so this gets very painful very
quickly for complicated models where you want to persist child objects.

That someone would say "Dapper is all they need" leads me to think they only
work on very small projects. You will drown in large projects if you use
Dapper everywhere. Better to use a high level ORM like EntityFramework and
sprinkle in some Dapper in performance critical code.

The only time EF creates performance problems is when the developer doesn't
understand the concepts behind SQL. This isn't an ORM problem, it's a training
problem.

~~~
CuriousSkeptic
As I see it, if your model is trivially mapped, you could probably go with
something like ormlite on top of dapper. But if you need to custom map you
model anyway you might just as well write SQL to map it as some custom dsl
mapping thing for the ORM in question.

And, I don't use dapper for performance, I actually like its transparency and
friction free interface to the db. Let's me get things done without
workarounds. And most importantly, lets me use ssms with a repl flow, copying
sql verbatim between vs and ssms.

------
maxxxxx
I think ORMs are a great tool to get something off the ground quickly. Like
with most tools you will hit a point where they make things more difficult and
then it's probably time to switch to SQL only or mix SQL with ORM especially
for performance critical queries. In most applications I have seen the ORM
provided a lot of value but there were cases where it needed to be augmented
with raw SQL.

I never understand why people want one or the other exclusively. Both have
their place.

I think stored procedures would be very useful if they integrated better with
source control and the app code. Maybe we need an ORM for stored procedures
that automatically creates stored procedures from the project code?

~~~
fishtoaster
I completely agree.

90% of the queries in my app are no more complex than selecting from a table
with a simple condition. I definitely find

    
    
      users = User.where(has_foo: true).limit(10)
    

to be a lot more readable than

    
    
      rows = connection.exec_query("SELECT * FROM users WHERE has_foo=true LIMIT 10")
      users = rows.map { |row| User.build(row) }
    

(And that's an example with no user-provided input)

Likewise, any app of sufficient size seems to end up with a handful of queries
that really are a pain (or impossible) to cram into an ORM. Trying to do so
would result in an unreadable mess, and using raw sql improves the situation
immensely.

~~~
zepolen
Here's the thing, anyone who knows SQL will find the second one readable, and
only Ruby programmers who have used ActiveRecord will know how the first one
does.

~~~
mattdw
Really? connection.exec_, .map, .build – there's a lot more non-SQL going on
in the second example than the first. The first may be syntactically far from
SQL, but the use of familiar vocabulary makes it pretty understandable from a
SQL point-of-view.

~~~
platz
And what are the connection and allocation semantics of the first? (Yes, the
second way is more explicit.)

------
nodamage
Just once I'd like to see someone write an opinion about ORMs without
resorting to sweeping generalizations ("Vietnam of computer science") or
making assumptions about how they are being used ("it wasn't a good fit for my
use case so therefore it must never be").

Maybe, just maybe, it's possible that ORMs are useful for solving certain
types of problems and less suitable for other types. If you work on web stuff
or applications that are report-oriented where most of the time you're just
fetching data (possibly with complex queries) and rendering it to display,
then maybe ORMs aren't a good fit.

On the other hand if you work on client-side apps where your objects are
backed by a database but are otherwise long-lived, then sometimes the other
features (beyond SQL generation) that ORMs provide come in handy (tracking
units of work over the object graph, maintaining an identity map for
consistent access to objects, and providing change notifications when an
object or collection of objects is manipulated).

If you've never needed any of these features that's perfectly fine. I've never
to needed to use a bulldozer either. But I'm not going around declaring
bulldozers useless just because I've only ever needed to use a shovel.

~~~
notyourwork
Is this not the general sentiment of any piece of software? It was implemented
to serve a purpose, if that purpose is not sufficing a goal of yours it is not
relevant to you.

~~~
nodamage
Indeed. It would be nice though, if people, having realized that a particular
tool isn't useful for their particular goals, could still acknowledge that it
could be useful in other situations, instead of blaming the tool itself.

------
gregopet
I've come to love jOOQ, the Java library that I can write type-safe pure SQL
in (and with code that is immediately understandable to anyone who knows SQL).
It provides mechanisms to smooth the clash of Java/SQL worlds, but otherwise
never assumes it is smarter than the wisdom accumulated through decades of
database usage. I've come to appreciate many aspects of databases again that
tools like Hibernate try to hide from me.

[http://www.jooq.org/](http://www.jooq.org/)

I do still use Hibernate, probably because my usual framework of choice makes
it so easy to, but anything above medium complexity goes through jOOQ
nowadays.

~~~
_pmf_
I was intrigued by jOOQ, but the huge amount of generated code for internal
metadata of the DB was a bit offputting. I don't want 4MB of generated classes
for 3 very small tables.

~~~
lukaseder
Use the code generator's <inputSchema> or <excludes> flag or other means and
you'll only get those 3 small tables generated...

~~~
_pmf_
Ah, thank you, this does the trick; I first tried to use my database name as
inputSchema, but I had to use "public" in PG.

~~~
lukaseder
I see, yes. The database corresponds to jOOQ's Catalog, not the Schema (except
in MySQL, which mixes up these concepts)

------
vinceguidry
The thing I love about ActiveRecord is that it makes it easy to, anywhere you
want, and at any level of the abstraction stack you want, to just toss in SQL
fragments. This gets you the best of both worlds, the fluidity of being able
to just define methods on model objects, and the ability to utilize database
tech to the fullest. You can take any query and call .to_sql on it and it
shows you exactly what it's passing to the database. I prefer Sequel's
semantics but ActiveRecord is an excellent workhorse that I have no problems
relying on.

~~~
sirn
But how do you do:

    
    
        SELECT
          posts.*,
          (SELECT COUNT(1) FROM comments WHERE post_id = posts.id) AS comments_count
        FROM posts;
    

In ActiveRecord, without 1+N queries, or caching comments_count in a column
somewhere?

Admittedly, that was not the best example. The last time I need something more
intertwined than a simple COUNT in subquery, the answer was "give up and just
use Arel." But at this point it is no longer quite ActiveRecord, but rather a
SQL without strings.

This is my biggest gripe against the Active Record pattern in general, as it
ties its model too tightly to the underlying database. It is convenient for a
simple CRUD tasks, which may fit about 90% of use case, but that's not the
only thing the database is capable of.

~~~
fbonetti
That's really easy.

    
    
        Post.select("posts.*, count(comments.*) as comments_count").joins(:comments)

~~~
jeltz
That is not the same query at all. You need to do a left join and a group by
for it to become the same query. Wihout a left join posts without comments
wont show up in the result.

~~~
fbonetti
You're totally right. My example is an inner join. I will admit that doing
outer joins in ActiveRecord is quite painful. Here's a more realistic example,
which is not nearly as pretty:

    
    
      Post.select("posts.*, COUNT(comments.*) AS comments_count").joins("LEFT JOIN comments ON comments.post_id = posts.id").group(:comments)

------
Pawka
Haven't red all comments and maybe somebody already mentioned this, but here
are my few cents:

I've worked with multiple ORM frameworks during my career. And I always
reached the point where developer needs focus to framework internals instead
of delivering new features.

Yes, usually most situation which developer didn't expected can be solved by
configuring ORM framework, changing some configuration, providing additional
parameters or calling some methods, but what that means? You need fully
understand your framework if you want to be sure you application does exactly
what you want and not misbehave.

So next time when you'll be thinking grab ORM to make things simpler, ask
yourself if you really have enough time to fully learn the framework?

------
aidos
I'm a bit surprised by these criticisms from someone using sqlalchemy. I had
the same opinions of orms in the past until I learnt sqlalchemy.

 _Partial objects, attribute creep, and foreign keys_ : defer the loading of
columns [1] to suit your use-case. You can even have different mappings that
deal with different subsets of columns depending on your situation.

 _Data retrieval_ : sqlalchemy is so good for querying that I've mostly
stopped using sql for anything other than complex exploratory analysis work.
Sure, you need to know sql to be effective, but whatevs - eg Window functions?
Done [2]

 _Dual schema dangers_ : I understand the concern, but again, you can choose
to map this as you please and I don't see what doing raw sql gains you here.
You need to change the schema, if that affects your application, you need to
change your application, if it doesn't, do you need to change your orm code? I
routinely migrate my db and release changes to the orm code later.

 _Identities_ : I admit there are a couple of times I need to flush in my app
outside of the normal lifecycle, which I don't like. With sqlalchemy, for the
most part, you just connect objects and don't worry about the ids.

 _Transactions_ : Whatever happens you'll need some sort of transaction
boundary in your code. Removing the orm doesn't gain you anything there, does
it?

I've written about some of this before on here so I won't rehash it
[https://news.ycombinator.com/item?id=9180831](https://news.ycombinator.com/item?id=9180831)

There's a time and a place for sql, orms and storedprocs. The more you know
about each of them, the more effective you can be. As ever, learn the tools
and never throw the baby out with the bathwater.

[1]
[http://docs.sqlalchemy.org/en/latest/orm/loading_columns.htm...](http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html)

[2]
[http://docs.sqlalchemy.org/en/latest/core/tutorial.html#wind...](http://docs.sqlalchemy.org/en/latest/core/tutorial.html#window-
functions)

------
joostdevries
I prefer to use functional frameworks. They 1) express the sql with map,
flatmap, filter, groupBy etc functions 2) use classes that are equivalent to
tuples.

Since I develop in Scala both are very intuïtive to write.

Apparently Linq for F# is really good as well. As someone else mentioned in a
comment.

I get a programming interface that doesn't attempt to hide sql and still my
code is fully typed. And it's really easy to make your query logic modular:
make the sql fragment a function that you can call. Like any other code you
reuse.

I can't think of an advantage of ORM over this functional approach. Apart from
ORM being more well known.

------
SchizoDuckie
I, like many other people, also took a stab at my ORM / ActiveRecord lib
(First for PHP on MySQL, later ported it to Javascript on WebSQL) and I've
been following exactly the sentiment that a people here seem to have to: Don't
make your ORM want to do everything. If you are going to need computed N+1
queries, write a View, or use custom SQL.

ORM / ActiveRecord is a great pattern imo, but if you want to make it do
_everything_ , like so many other techniques, you're gonna end up with a
behemoth of a thing.

I've tried many different ORMs in the last couple of years, and while mine may
not be the most complete or have a sexy API for joining custom query
parameters into the results, I still feel my own provides the one and only
syntax / workflow 'as it should be' in Javascript:

    
    
        var serie = new Serie();
        serie.name = 'Arrow';
        serie.TVDB_ID = '257655';
        serie.Persist().then(function(result) {
          console.log("Serie persisted! ", result);
        });
    

I've got loads of examples with jsfiddles if you want to find out more :-)

[https://github.com/SchizoDuckie/CreateReadUpdateDelete.js](https://github.com/SchizoDuckie/CreateReadUpdateDelete.js)

 _(browser that supports WebSQL required, obviously)_

------
jeffdavis
Using an ORM is for advanced users, and I think it's a bad default for
beginners. A good one certainly saves time when you know how to use it, but it
also adds complexity and narrows the perception of what a database can do.

Narrowing the perspective is especially bad for beginners, because some
options will simply never occur to them and the ORM will prevent learning by
osmosis.

Let's say you need reliable, efficient cache invalidation (say, to re-render a
static page when the data changes). Triggers and LISTEN/NOTIFY in postgres
might save a huge amount of time (and even more in maintenance costs) over a
custom solution that probably isn't right anyway.

Or maybe you need to prevent schedule conflicts. Postgres offers range types
and exclusion constraints, which aren't supported in all ORMs.

Or to keep a remote system reliably in sync (even if it's a very different
kind of system), logical decoding is a very powerful feature.

But how would you encounter any of these potential solutions if you are always
behind an ORM?

------
khorpy
When considering using ORM we need to answer simple questions: does it help to
decrease code size? In most cases ORM code is same size as SQL query.
Secondly, does it hide complexity? No, it just adds up one more layer that in
fact increases complexity and makes it harder to debug. Finally, does it
protect us from errors or impoves code quality? This is rarely the case.

Instead of using ORM I prefer moving data retrieval code to database layer
with help of views. There is also updatable views that we can use to simplify
database updates on code side and sometimes avoid using transactions.
Separation of code and data logic is great concern when deciding to implement
ORM. Nowdays database are very smart and convinient so there is no need to use
ORM.

~~~
collyw
In most cases its smaller (in the Django ORM anyway).

myobject, created = MyObject.objects.get_or_create(field1='1' , field2=field2)

That would take a number of lines. One query to check if the object exists,
another to create it / retrieve it, plus application code to deal with that
SQL.

~~~
khorpy
Ok, for this particular example, is operation wrapped in transaction? How will
code evalute if we need to check some condition on field2 and occasionally
update it?

------
dkarapetyan
Never seen a large enough project that relies on an ORM be anything other than
a giant mess. I mean never. The conclusion is correct. From an application
perspective the db is just another API and should be treated that way and the
ORM should just be thought of as a convenient DSL for creating queries on top
of that API.

~~~
nostrademons
To be fair, I've never seen a large project be anything other than a giant
mess. This is the nature of large projects. If it's not a giant mess, you are
probably leaving user happiness (= $$$) on the table.

~~~
dkarapetyan
And it comes at the expense of craftsmanship and programmer happiness. Lately
I've been thinking how much initial architecture affects the mental well-being
of all subsequent programmers that join the project. This is one of the
reasons there is such a high turn-over and burnout rate in programming. If
you're not consistently paying back technical debt and improving the
architecture of the project then you're paying for it in other ways with the
revolving door of programmers.

Then again if you're a big enough business it probably doesn't matter as long
as customers are paying you. All you have to do is plug-in another
cog/programmer into the machine when the previous one wears out.

~~~
nostrademons
All this is true, but it's also the reason why programmers are paid as much as
they are. In general, in a market economy, you are paid to do things that
other people don't want to do. The whole point of a market is to create an
incentive to do things that are unpleasant.

Beautiful code does exist in the software world. It's usually found in hobby,
open-source, and research projects without profit motives, userbases,
deadlines, and all those other complications that result in shitty hacks.

In the meantime, you could look at the crap that is most large-scale codebases
as a barrier to entry that keeps demand for programmers high. Enjoy the money
you get for cleaning up other peoples' messes, and then use that to buy time
to make the beautiful, useless stuff.

------
Animats
ORMs tend to assume that there is "the application" with "its database". If
the application changes, so does the database. If the data is used by more
than one application, it's better to have the data defined in the database and
write applications as database clients.

~~~
vrdabomb5717
At the big tech firm I work at, there's a best practice where any database
(whether that's a traditional RDBMS or a NoSQL client) is abstracted away by a
microservice with a defined API, and every other application that wants to get
that data needs to interact with the microservice. That way, the database
schema can change without it affecting multiple applications. There's still
the traditional mismatch between ORM and database, but doesn't feel as painful
because only a single application is using that data, and that application can
have special knowledge of the persistence layer underneath (meaning, use
database hints if necessary, defer to raw SQL, etc.)

~~~
prodigal_erik
We have decades of research into filtering, joining, and aggregating across a
complex set of tables and views. With microservices you have to roll your own
query planning and stream all the intermediate results on the wire even when
you're throwing away most of them.

~~~
elcritch
Unfortunately, SQL servers aren't generally scalable. Offloading joins and
aggregation onto (inexpensive) app servers can increase over all system
performance, despite lack of advanced query planning.

~~~
collyw
Bollocks. SQL scales fine unless you are stupid in the way you use it. All the
crap performance I have seen in the last couple of years have been from doing
crap like you suggest - doing joins and aggregation at the application level.
Our front page currently makes over 1000 database calls because of this sort
of nonsense.

------
ccleve
ORMs can alleviate a lot of tedium, until you get to a level of complexity
where they start to create it.

I created my own ORM-like access library which seems to work pretty well.

[https://github.com/dieselpoint/norm](https://github.com/dieselpoint/norm)

Comments welcome.

------
pwilsony
"quasi-professional cowboy" here. I have used both ORM and direct SQL systems
with the problems outlined above. I finally gave up and wrote my own row
mapper:

1\. Using the DB Schema, generate stored procedures to load and save data
together with code/validation in partial C# classes.

2\. Transport data in XML format using single letter (or two) table/element
and column/attribute names (auto generated). Use .Net to automatically build/
serialize the XML into objects. SQL Server/.Net have some nice XML features
that make this simple.

This means that the only user access to the database is through authorized
stored procedures. The users have no access to database views or tables.

I have implemented the means to load and save arbitrary hierarchical objects
(e.g. an Order/Order Lines). A single request to the database can return a
complex three level object which would otherwise take hundreds to round trips
to load.

I agree with the observation that this would be hell to maintain. However, the
people likely to maintain this system would be in just a different hell if
they had to work with Hibernate or MS Entity Data Model.

------
tfigment
I written a fair number of C# LOB apps and use LINQ quite a bit with mysql. I
don't even want to talk about Java and some of its ORMs as its too painful to
think about. I agree with the sentiment of the post but in compiled languages
I really want an ORM to simplify unpacking result sets. LINQ is great when it
works but joins sort of suck as well as calling in-built sql functions and it
can some times generate highly unoptimized queries. I frequently pull in data
via LINQ to SQL and then massage the data with LINQ for objects where I have
better control of performance and operations.

Trying to write pure SQL leads to lots of manual unpacking of the result set
which I generally dislike and is much harder to maintain and doesn't work well
in practice compared to when LINQ actually works.

I think maybe what I've really learned is use scripting / loosely coupled type
systems when working with SQL. In python, I usually just call sql directly
rather than use sqlalchemy and its fine because of the loose typing and result
set unpacking isn't terrible.

~~~
bunderbunder
On C#, Dapper's useful, but not perfect, for letting you write your own
queries and then making it easy to unpack the result sets.

Unfortunately, it relies on property setters for doing the unpacking, so it
doesn't interact super well with your code if you like to avoid unnecessary
mutability.

The only publicly-available lightweight ORM I know of that does a good job
with that is the SQL type provider in F#.Data. That one is head-and-shoulders
above any other option I've found for working with databases. It does require
that you write your data access layer in F#, though, which may make it a hard
one to sell at work.

~~~
tfigment
Thanks. That actually doesn't look that bad. It would be nice to have
anonymous type objects but I recognize the difficulty in that. This looks like
a nice compromise. Now if they could also fix passing in arrays as part of a
parameterized query "select x from y where z in ?" where ? is a collection of
strings or integers it would be perfect but I think that is a driver/interface
problem.

Edit: Looks like it actually does says it does support the enumerable but I'm
questioning if it will do what I want. Will have to test out but I'm guessing
there is a limit to the size of the array.

~~~
bunderbunder
Dapper accepts anonymous objects for passing query parameters. It will return
an expando if you don't specify the type of object you want back, too. That
can be convenient, but I don't personally like having dynamic objects running
too wild in my code so I end up manually mapping it to another type before
returning anyway. (AutoMapper can help here.)

The IEnumerable support will generate (parameterized, I believe) inline arrays
in the query. Not really my favorite, but it works.

The thing that I couldn't get over, and which ultimately led me to write my
own micro-ORM at my last job, was the weak support for table-valued
parameters. But I gather they've fixed that since then, so hopefully it's not
a big deal anymore.

------
oliwarner
What reading articles on how bad ORMs are has taught me: More people need to
use Django.

Django does a lot and does it well.

~~~
collyw
I wouldn't say the ORM is Djangos strongest point. Its great for simple stuff,
but not great for any moderately complex queries (e.g. extra conditions on a
join, subqueries, conditional aggregates - though I think that last one may be
solved in the most recent version).

I don't think they have done a bad job with the ORM, but you really ought to
know SQL as well.

~~~
jsmeaton
I think the ORM is Djangos strongest point. Take the ORM away and then
ModelForms and Admin go with it. I think Django makes little sense if you're
not using the ORM.

That said, you're absolutely right that it is really bad at certain types of
queries. There's no way to really influence joins. Subqueries are coming
([https://github.com/django/django/pull/6478](https://github.com/django/django/pull/6478)),
and conditional aggregates became possible in Django 1.8 with Expressions
support (which I authored). Queries that map over many to many relations that
have .exclude() or .annotate() generally produce incorrect results. Most of
the problems with the ORM are as a result of Django handling joins for the
user - which is also one of its biggest strengths.

You should absolutely know SQL if you're using an ORM. If you're doing
anything other than basic CRUD, you have to know SQL. An ORM should give you
an escape hatch to write that custom SQL. Django's ORM does not save you from
all the problems mentioned in the article.

------
chillacy
> ORMs are more detriment than benefit

The author mentions Hibernate and SQLAlchemy, which are both DataMapper ORMs.
But what about Active Record? It's true that AR will provide even more
abstraction and distance from the database, but it also provides a lot more
convenience, which for me in small CRUD projects (as most are) has been worth
the downsides.

And as another posted mentioned, you can always optimize by replacing slow AR
queries with custom SQL ones, and restructuring your database as your project
scales.

------
EToS
I love ORMs, they help abstract the database technology which has been really
useful in a recent project switching an app from MSSQL to PostgreSQL. I also
personally as developer appreciate the programmatic syntax.

I would caveat the performance issue, sometimes there is no better way,
however i would always try to make things work in the ORM first before
switching into native SQL to get the job done.

~~~
nommm-nommm
> they help abstract the database technology

If you are going to do anything even remotely complex you're going to need to
know that database technology otherwise your ORM is going to spit out queries
that are dead stupid and a performance nightmare (pages taking 1 full minute
to load). Then you end up fighting with the ORM to do what you want.

That's my experience.

I guess there are projects that are so simple the database layer can be
abstracted away but I've never worked on one.

------
vladsanchez
I've always said: "ORMs are for people who don't know SQL!"

~~~
pixie_
ORMs are not for people who don't know SQL.

The secret to using an ORM is knowing exactly the SQL it will generate
underneath. The advantage to using one is static typing as well as writing
orders or magnitude less code than SQL. As well as orders of magnitude more
understandable code than the equivalent SQL.

If you work on heavy enterprise applications the ORM can be your best friend
or worst enemy. It comes down to knowing the tool you're using inside and out.
It will make you much more productive in the long run. And sometimes yea, you
need straight SQL or a stored proc to get the job done. That's OK.

------
Waterluvian
What django gave us out of the box would have been impossible with SQL in the
same timeframe with the resources we had. If we had the luxury to do things
properly and fully the first time, we never would have made it to market with
our robots.

This isn't to say that one day we won't throw away django or use more SQL. We
just had real world schedules to deal with.

------
gautamdivgi
Oh finally!!! I've typically had a ambivalent relationship with ORMs. The mass
of config files and auto generated code is a huge pain - especially when
things go wrong. Doing multiple DBs can also be an issue and requires messing
around with config/annotations, what not...

Just write the SQL :) - relational algebra is not that hard...

------
brightball
As much as I advocate for properly using SQL, I still want an ORM
(lightweight) to manage the repetitious small stuff that happens repeatedly
throughout an application. I'm all about custom SQL or stored procedures when
the time comes though.

------
abraae
I find ORMs great in some scenarios.

Take a really thick client, like say a graphical diagramming tool that makes
your machine's fan whir loudly when you start it up. Here an ORM can be a
great tool to efficiently manage a constantly evolving, large cache of your
hot objects, and keep them syched with the backing RDBMS.

Big batch programs can be another good use case.

Server side web apps and API servers though are the opposite of this. Web
pages and API responses should be fast and small, so we don't normally build
up a big cache, and in a stateless architecture we are normally throwing the
cache away at the end of each request. In this case raw SQL is often easier
than work with.

------
hitr
If you dont want to deal with quirks of ORM,use a lightweight microORM. You
basically get some productivity, simple type checking,consistent way to access
data , sql injection protection and you will deal with hand rolled sql
queries.

------
YeGoblynQueenne
Sigh. Whenever I have to hurt myself at work on such issues, I find myself
thinking of the web that could have been, if only...

Let me backtrack a bit. There's a much better way to do things than the way we
do it right now. A way that completely obviates the need for ORMs, or indeed
any way to deal with the interface between application and database.

You see, there would be no object-relational Impedance Mismatch (and therefore
no need for such clunky kludges like ORMs) if the database and the application
were both written in languages of the same paradigm, either both OOP or both
relational. Ideally, that would be a single language, that could handle both
data and application logic.

There is such a language: Prolog [1]. Prolog is implemented as a relational
database. So your program itself _is_ the database. There is no separation
between data and operation and therefore no ORIM and no need for ORMs, or
stored procedures or anything, really.

And, yes, it's perfectly possible to do your web dev in Prolog. Here, see this
explainer on the Swi-Prolog website, titled "Can I replace a LAMP stack with
SWI-Prolog?":

[http://www.swi-prolog.org/FAQ/PrologLAMP.txt](http://www.swi-
prolog.org/FAQ/PrologLAMP.txt)

Hint: Yes. Yes, you can. You can replace LAMP (or LAM-whatever) with an LP
stack, where all you need on top of your OS is Prolog itself. Its built-in
Definite Clause Grammars notation [2] can be used to parse and generate
javascript, html, xml, css, YAML, whatever you like. Swi-Prolog even offers
translation to RDF, which is as natural as you can expect given RDF is also a
relational language.

Here's a big fat tutorial to help you started:

www.pathwayslms.com/swipltuts/html/index.html

Can this be done _for real_? OMG yes it can. The Swi-Prolog website itself
runs on an LP stack. There's a few more websites that do too:

www.pathwayslms.com/swipltuts/html/index.html

In short: you don't need to hurt yourself as badly as you 're currently doing
it. There's no other need than of course, nobody wants to learn Prolog. I know
that, I've made my peace with that and I've spent all of my so-far career
hurting myself against the ORIM just like everyone else.

... but we could have had a better web.

___________________

[1]
[https://en.wikipedia.org/wiki/Prolog](https://en.wikipedia.org/wiki/Prolog)

[2]
[https://en.wikipedia.org/wiki/Definite_clause_grammar](https://en.wikipedia.org/wiki/Definite_clause_grammar)

~~~
jsmeaton
This sounds really interesting, but I'm having a lot of trouble trying to
follow how all that code fits together - from what code I could find. I also
strongly dislike the object/function based approach to constructing HTML
(which - funnily enough, is just like using an ORM to construct SQL which I
like heh).

Really, I don't see enough offered to counteract what little pain I feel using
an ORM. The LP actually looks more painful (to me). Obviously I'm totally
unfamiliar with this stack which makes it hard for me to see where the
benefits lie.

~~~
YeGoblynQueenne
>> Really, I don't see enough offered to counteract what little pain I feel
using an ORM.

In that case you'd probably find no benefit in giving up the use of one,
regardless of what replaced it.

------
vbezhenar
Hibernate is a very complex library. SQL+JDBC on the other hand is easy (JDBC
is tedious, some complex SQL might be hard, but generally it's easy). On the
other hand Hibernate could significantly reduce development time, if
developers know it and application is a goot fit (CRUD, filters).

My advice is to avoid ORMs unless your project is big and its database schema
is a good fit. And if it is, don't think, that ORM is easy, it's not. Learn
how it works, read its source code, understand its inner workings. Or find
someone who does.

I didn't have much experience with other ORMs, though.

------
sopooneo
I expect I am approaching things improperly, but the only time I end up
fighting ORM's is when I am trying to creation complex aggregation queries, or
other responses used on "reporting" pages.

------
kuharich
Previous discussion:
[https://news.ycombinator.com/item?id=8133835](https://news.ycombinator.com/item?id=8133835)

------
eternalban
Domain objects in the middle tier only make sense if there is pervasive reuse
of component objects. I extensively chased the unicorn of component
orientation but it never pans out.

The _logical_ place for domain objects is in the Database. Yes, stored
procedures, with object layer mappings of _views_. But the reality of the
practice is that (a) the toolchain for the DB backend is quite firmly stuck in
80s and (b) most IT programmers lack SQL skills.

------
booleanbetrayal
The trick to using ORM is being smarter than it, so that you can ultimately
rewrite everything it once did for you incredibly inefficiently.

------
kayman
Use the best tool for the job.

ORM's might do it 80% of the time. The other 20%, most of the mature ORMs
provide a way to execute your own sql.

------
bluejekyll
I'm curious how the author would like MyBatis, which is just a thin wrapper
for calling SQL, and then translating results to POJOs.

I like it, and it, IMO, keeps the API distinct from the access in the DB. You
have to write your own SQL, which is good and bad. Huge amounts of control and
performance, but higher portability costs.

~~~
pbreit
Also Records for Python:
[https://github.com/kennethreitz/records](https://github.com/kennethreitz/records)

------
stana
One important benefit of using an ORM is not being locked in with a particular
DB server tech. Good luck migrating large app not using ORM, from, say, MySQL
to Postgres if, for example, Postgres replication is something you are all of
sudden interested in.

~~~
collyw
Has anyone ever actually migrated a database on a non trivial application? I
want to go from MySQl to Postgres, but I am not sure if its worth the risk /
effort. We got told about the standard nature of SQL in University, and how
you could swap engines 'easily'. Then we started using them in real life and
the reality of that situation hit.

------
JelteF
For me the time I loved SQLAlchemy the most was the time when I migrated my
database from MySQL to Postgres. Almost all of the issues people were warning
me about did not matter as SQLAlchemy caught them automatically.

------
dools
I came to the same conclusion, and wrote this ORM for SQL lovers:

[https://github.com/iaindooley/PluSQL](https://github.com/iaindooley/PluSQL)

------
intrasight
There are two types of ORMs 1\. ORMs for SQL dummies 2\. ORMs for SQL gurus
You really need to be clear, when discussing ORMs, which type you are
discussing.

------
lukaseder
Use ORMs for complex CRUD. Use SQL for complex queries.

------
kefka_p
Enterprise Objects Framework just worked.

Pretty much every other ORM I've worked with has been terrible by comparison.

------
raspasov
(= 'ORM 'OMG) #Clojure

------
Grue3
SQL is a write-only language. You better hope you don't have to rewrite your
complicated raw SQL queries in the future.

~~~
bdcravens
You can write well written modular SQL that supports changes and can be read
by a competent developer.

~~~
Grue3
Not in my experience. Please show me actual examples of "well-written modular
SQL" in a real application.

~~~
Something1234
Would modular sql be wrapping up subqueries inside of views, and then
selecting from those views?

~~~
jsmeaton
Common Table Expressions (CTEs) help with modular SQL.

