
What ORMs have taught me: just learn SQL (2014) - ausjke
http://woz.posthaven.com/what-orms-have-taught-me-just-learn-sql
======
zzzeek
> If you're using an RDBMS, bite the bullet and learn SQL.

If this person spent all that time using Hibernate and then SQLAlchemy, and
all that time did _not_ know SQL, then their suffering and bad experiences
make complete sense. You absolutely need to know SQL if you're going to use an
ORM effectively. Good ORMs are there to automate the repetitive tasks of
composing largely boilerplate DML statements, facilitating query composition,
providing abstraction for database-specific and driver-specific quirks,
providing patterns to map object graphs to relational graphs, and marshaling
rows between your object model and database rows - that last one is something
your application needs to do whether or not you write raw SQL, so you'll end
up inventing that part yourself without an ORM (I recommend doing so, on a
less critical project, to learn the kinds of issues that present themselves).
None of those things should be about "hiding SQL", and you need to learn SQL
first before you work with an ORM.

~~~
alexandercrohde
I think there are 3 main reasons ORMs came into common use:

1\. As a reaction to common SQL injection from poor libraries not implementing
parameterized queries. (2004 or so)

2\. Novice engineers not wanting to learn SQL (look I learned how to make a
blog in RoR, and I like mongo!)

3\. As a theoretical abstraction above the data-store (as though you might
someday be able to switch the data-store beneath the ORM)

1 has been solved, 2 was never okay (point of the article), and 3 isn't really
okay either because it's too leaky (performance specifics).

~~~
JamesBarney
4\. The pain in the butt of writing and maintaining your own mapping code.

5\. Type checking all your queries.

6\. In code query composability.

But I totally agree that ORMs are too leaky of an abstraction for 2 to be
really useful, and that 3 is much harder than it appears to be.

~~~
pmontra
+1 for these three points.

But I'm really surprised every time people tell me they look at the schema as
defined into the ORM instead of at the table in the database.

I'm really jaw dropped the few times I know somebody doesn't even know SQL,
only the ORM. Maybe they look at it as if it were the reaction of somebody
that thinks you must know assembly if you want to program Ruby, Python or Node
(I don't.) Still, if you work with a database you must know it's internal
language, SQL or NoSQL. Your going to need it or build a mess.

And involving a DBA early in the project can make your database at least twice
as fast, with the right schema and the right queries. Then you translate that
into the ORM you want to use.

~~~
dizzystar
Woah, who are you interacting with that not knowing SQL is rare?

In my experience, nearly no one knows SQL, and the attitude seems to be that
learning it at all is a waste of mental bandwidth.

On the other hand, I wonder if knowing none at all is better than knowing a
little.

~~~
pmontra
I expect that everybody with a degree knows SQL and I'm realizing that I could
be wrong. Maybe sometimes I'm the only one in the room that knows it. I'll
check it next time I'm at a technical event leaning on the backend side.

~~~
tonyedgecombe
I haven't written very much SQL in my career, there is quite a lot of
development that doesn't use it.

------
3riverdev
(Bias: I'm one of the Hibernate ORM committers.)

Hibernate (and presumably any ORM) was never intended to be a complete
abstraction of anything-SQL. Like others have mentioned here, an understanding
of SQL must be had _before_ using an ORM. The ORM is one piece to the puzzle,
not a shield to prevent you from having to touch SQL.

One pattern I typically use is a take on CQRS: Hibernate for
writing/updating/fetching/deleting a single instance of deeply-relational
object model, SQL (I like jOOQ) for larger-scale fetches and any bulk actions.

Shameless plug for a write-up I put together last year:
[https://www.3riverdev.com/hibernate-orm-jooq-hikaricp-
transa...](https://www.3riverdev.com/hibernate-orm-jooq-hikaricp-transactions-
and-spring-an-sql-cqrs-tutorial/)

~~~
lmm
> Hibernate (and presumably any ORM) was never intended to be a complete
> abstraction of anything-SQL. Like others have mentioned here, an
> understanding of SQL must be had before using an ORM.

Disagree. You need to understand the relational model, but you don't need to
understand SQL-the-language. I've written plenty of successful systems using
hibernate without needing to touch SQL, and am much happier for it.

~~~
_grep_
I agree with you, but I think people (for better or worse) are using the two
terms interchangeably in this thread.

------
manigandham
One of the most endless and pointless debates.

99% of the time, an ORM is fantastic and will make you more productive while
providing performance, security, and maintainability. They come in many sizes
from thin wrappers around a db connection to full-featured frameworks.

For the other 1%, use raw SQL, or perhaps a query building tool to help with
parameterization, composability, etc. In fact, modern ORMs will even let you
input raw SQL and handle the conversion back to objects if you need it.

Saying ORMs are always wrong is just as dumb of a statement as saying all
database access needs to be in raw SQL. They are just a tool and abstraction,
like everything else you use in software development. You know the right time
to use it.

That being said, not knowing SQL at all means a lack of general understanding
in how relational databases work and will almost always cause problems.

~~~
bunderbunder
All of these endless debates seem to boil down to two different groups who
work in two different problem domains talking past each other.

I've personally never seen an ORM lead to success in the long run. But I also
work in a space where queries frequently end up involving something that ORMs
typically don't handle well: merge statements and pivot statements, window
functions, management of the lock escalation policy to fine-tune performance,
temp tables... The list is endless.

What I have not ever worked on is a relatively basic CRUD datastore. Which I
realize is what most people are using databases for. So at this point, I'm
putting my money on ORMs being a hole in one for that application. Because,
otherwise, I just can't reconcile a statement like, "99% of the time, an ORM
is fantastic" with the reality I'm living in. In my career, 100% of the time,
when an ORM was present, it was invariably the single biggest piece of
technical debt.

~~~
manigandham
Yes, you are talking the 1% of use-cases, which is probably more like 10%
these days with more complex software. Most business apps are just CRUD, but
if you're doing analytics queries and such with tabular/pivot/nested result
sets, then an ORM isn't going to do much for you.

SQL is the database interface so of course using it directly without
abstraction helps you get all the power and control. I have seem some cases
though where a query-builder with a solid DSL can be a good middle-ground.

------
ulkesh
I guess my experience is limited, but I’ve not seen much of this despite
working in an ORM environment with around 75 entities for the last few years
(my recent experience anyway, the rest goes back 16 years). Maybe that is
small potatoes, I don’t know, but I’ve found that anyone who understands JPA
well enough can work to avoid any pitfalls of using ORM. It seems to me that
having a good mix of understanding SQL and ORM is a good thing; and especially
understanding exactly what the ORM system is doing for you and how it is doing
it. Dropping ORM altogether sounds like a bad idea since it provides a number
of built-in security features as well as an abstract modeling paradigm that is
fairly easy to conceive and maintain; provided, of course, that you learn to
say “No” to protect the integrity of the model (such as rejecting the
attribute creep the article warns about).

I have found, in my experience, that people who tend to want to write SQL over
ORM usually want to do so because they simply know SQL better. That’s okay,
there is nothing wrong with that. But that doesn’t immediately mean ORM
systems are bad. No need to be tribal about it.

The problem I see is that many new software developers these days sometimes
can’t see the forest for the trees because they dwell too much on what they
think is better instead of simply seeing the software and abstractions as
nothing more than tools in the tool belt. It happens everywhere — PC vs Mac,
iOS vs Android, Scala vs Java, SQL vs ORM. It’s fine to have opinions, I have
many, but as I’ve aged I’ve become acutely aware that my biases are almost
solely rooted in the limitations of my understanding.

------
jeswin
The post is from 2014, so I won't be too harsh here. The author's problem is
with some specific flavors of ORM he's used, and shouldn't be generalized.
Hibernate's expressiveness is/was crippled by Java itself. C# ORMs on the
other hand are way better because they benefit from LINQ which adds queries
natively into the language. Other more expressive languages have excellent
ORMs as well.

The objective of ORMs is not to replace 100% of your queries. That 10% might
still require SQL or Stored Procs and that's fine.

ORMs give you:

1\. Type safe queries

2\. Ability to refactor easily, click to rename prop

3\. Not having to handcode joins if objects are related

    
    
      customers.select(c => { cust: c, orders: c.orders })
    

4\. Lazy evaluation and composition (C# examples)

    
    
      //If getOrders() returned a query expression
      getOrders().where(o => o.city === "London")
    
      //You extend it further
      getLondonOrders().where(o => o.total > 200)
    
      //^ These queries aren't executed yet.

~~~
eru
Is LINQ actually an ORM? It looks like a DSL to do relational stuff in C#, but
I don't see where the 'O' part of ORM fits in especially not in your examples
to show off LINQ's power and convenience.

~~~
jeswin
LINQ is not an ORM. My examples were showing easy, lazy evaluation. My C# is
rusty, but the examples mostly hold I think.

The following gets executed on iteration (of expensiveOrders), the lambda is
compiled into a Function and run on each item in orders:

    
    
      IEnumerable<T> orders = ...;
      const expensiveOrders = orders.Where(o => o.total > 100)
    

The following gets compiled into an expression tree, which an ORM can analyze
and convert to SQL. Basically it's just "Code as Data".

    
    
      IQueryable<T> orders = ...;
      const expensiveOrders = orders.Where(o => o.total > 100)
    

The language's ability to treat code as data allows the programmer to express
queries in native language syntax and pass it to an ORM (such as EF or Linq to
Sql) for execution on a data store.

Add: Where does the 'O' part fit in? You build a entities (in plain C#) with
relationships to each other, and you could do stuff like:

    
    
      //Pseudo-code
      db.Customers.Where(c => ...)
      db.Save(customer);
    

Edit: clarified - executed on iteration, not immediately.

~~~
eru
Thanks. My quip was just that objects (as in object-oriented-programming)
aren't the right abstraction. Having some kind of mapper in your language
between the database and the entities your program is dealing with is useful,
and you showed that functional programming is a more friendly host than oop.

(Logic programming might also be workable?)

~~~
int_19h
Objects are fine, it's classes that are often limiting. Consider the very
first example above (corrected to be valid):

    
    
       var r = customers.Select(c => new { cust = c, orders = c.Orders })
    

This gives you an IEnumerable (basically, a forward-only sequence) of objects
- but these objects are of an anonymous type that was implicitly defined by
"new".

------
red_admiral
My 0.02 BTC on the matter:

Object-oriented programming 101 assumes that all your objects are in memory,
in a graph, so you can do things like person.getFriends()get(0).getName()
[assuming the person in question has >0 friends]. Each step in the graph is
essentially a pointer dereference, costing a constant effort.

(If your data is small enough to fit in memory, that's what you should
generally be doing. People who use hadoop for half a GB of data are usually
doing it wrong.)

A relational database assumes that all your data fits on disk, but only a
subset of it will be in RAM at any one time (and you generally have a network
round trip every time you change that subset). This means you need a
completely different way of thinking; this difference is sometimes called the
"object-relational impedance mismatch". This is not to do with SQL and OOP
just being different APIs for the same thing, they are designed for very
different use cases.

ORM tries to pretend that this difference doesn't matter, and works quite well
in simple cases when it really doesn't matter.

My standard example why it sometimes does matter: PersonDAO.fetchAll().size()
is silly because it forces the database to fetch all Person objects, send them
over the network, your application creates the necessary objects for them -
and then you throw it all away again because all you needed was the number of
people. PersonDAO.count() is much better, even if you have to implement it
yourself.

If you don't like the syntax of SQL, sure - use a query builder. In C# or Java
you can even get some kind of type safety that way. But you need to understand
the difference between an object graph and a relational database to use either
of them efficiently, long before you get to advanced ideas such as window
functions.

~~~
spdionis
> My standard example why it sometimes does matter:
> PersonDAO.fetchAll().size() is silly because it forces the database to fetch
> all Person objects, send them over the network, your application creates the
> necessary objects for them - and then you throw it all away again because
> all you needed was the number of people. PersonDAO.count() is much better,
> even if you have to implement it yourself.

I mean, PersonDAO.fetchAll().size() is just bad code. You probably need to
know how to write ifs properly before programming? You need to know about
databases before using an ORM.

~~~
red_admiral
> You need to know about databases before using an ORM.

Pretty much the TL;DR of my whole post.

------
blaisio
I think saying "Just use SQL" is probably a bad idea. You'll most likely end
up implementing an ORM anyway, or you will end up with your model code mixed
up everywhere with your views.

I do think a lot of people use ORMs as a crutch, which sucks. Also, ORMs often
provide too much abstraction, forcing people who actually know SQL to relearn
how to do everything the way the ORM happens to like it. I should not have to
learn twice as much to be productive due to an abstraction.

What I prefer are really lightweight ORMs that give me models which I can then
enhance with custom code. I don't need an ORM that supports plugins or
inheritance or a dozen different kinds of joins. All of that can be done more
efficiently with custom code.

Also, I think SQL builders are really useful. I think a lot of people conflate
SQL builders with ORMs but they're actually very different problems.

~~~
zkomp
No. You should really wind up with a DAL. Define some stored procedures for
accessing and working on the data and use only stored procedures.

No need for ORM, and no inline sql logic in your application code.

~~~
scarface74
Just use stored procedures? Then you lose the ability to do unit testing
without a database dependency, it's a lot easier to rollback code than to
rollback code and stored procedures as one and you don't get full visibility
on what the code is doing just by looking at the source code.

~~~
ZenoArrow
> "Then you lose the ability to do unit testing without a database dependency"

Not really, you just mock the database calls in the code you're unit testing.

~~~
scarface74
If all of your business logic is in the stored procedures, what are you
actually testing?

And I realize that being able to test queries without database dependencies,
only really applies to a few languages that treat queries as a first class
citizen in the language like C# and Linq where you can mock out your actual
Linq provider - replace the EF context with in memory List<T> \- and still
test your Linq queries.

~~~
ZenoArrow
> "If all of your business logic is in the stored procedures, what are you
> actually testing?"

Depends on what you want to test. Can either write unit tests for the stored
procedures or unit tests for the code that makes use of those stored
procedures.

~~~
scarface74
And then when you write "unit tests" for stored procedures with a lot of
developers you get slow "unit tests" that don't scale across multiple
developers because of Comte toon issues.

~~~
ZenoArrow
> "because of Comte toon issues"

Qué?

~~~
scarface74
Damn auto correct. That should have been "contention issues".

------
saryant
I have to agree. ORMs can be great when an application is just starting,
because that's when you're writing the most tedious queries and statements,
but beyond that I personally find that ORMs just get in the way.

As soon as I need to write something more complicated than select-by-id I end
up reaching straight for SQL. Otherwise I have to learn both the ORM's query
API or DSL _and_ have the proper mental model for how it translates to actual
SQL.

Or I could just write SQL and be done with it. No mysteries.

Maybe this is just me, but I've never written a join in an ORM that I had the
slightest bit of confidence in.

~~~
ProblemFactory
Any discussion on ORMs needs to consider what sort of app and queries you are
writing.

ORMs are _fantastic_ for the very common case:

* Fetch 20 rows and display them as a table,

* Fetch 1 row by primary key and display it as a form,

* Write updated fields from the form back into the 1 row in the database.

Anything more complicated, and direct SQL starts being more attractive. But
for the common case that ORMs are designed for, they are a major productivity
boost.

------
qwerty456127
The first time I've seen an ORM I was fascinated by this seemingly beautiful
idea. But I have quickly realized that it's almost useless in real life
projects, plain old SQL seems just much much better. Now I don't understand
why would anybody use an ORM actually.

Also, basic SQL can be easily taught in as little as 10 minutes (I have been
initially taught it at middle school during MS Office Query, Access and VBA
class). An image of a programmer that can't use SQL (I don't mean advanced
cases which can indeed be a bit tricky but these are far beyond the powers of
any ORMs anyway) seems really bizarre to me.

~~~
meesterdude
> But I have quickly realized that it's almost useless in real life projects

there are plenty of real life projects out there that would beg to differ

~~~
qwerty456127
Sure. I mean in all the real life projects I've seen from the inside, using
ORM instead of SQL felt like roaming a forest on a gyroboard.

------
nine_k
_Object_ -relational mapping is in many cases an excessively leaky
abstraction. I try keep away from it.

A DSL for writing SQL in a nice, composable way is a useful thing.

Some libraries, like SQLAlchemy, provide both levels, not insisting on using
the object mapper.

~~~
jes5199
SQLalchemy is kind of a pain in the ass to actually use, though. The DSL
doesn't feel very Pythonic, it's weird and confusing. The way it traverses the
Object graph when loading associations between models is magical and opaque
and I could never predict when it was going to automatically work and when it
wouldn't.

I actually would rather be writing Ruby on Rails, because it's _less magic_
than SQLalchemy.

~~~
sethammons
The one that bit me with sqlalchemy is joins. You have to structure your
sqlalchemy object in a specific way to do joins. Usually I write my sql query
then spend half an hour trying to convert it to sqlalchemy.

~~~
nine_k
Having PK relations defined was usually sufficient for me. I like SQLAlchemy
for its _straightforward_ mapping of SQL.

I don't usually work on the "model" level, though; I work on "table" level.

------
bjt
He says he likes SQLAlchemy, but doesn't say why. I'm interested to know that
though.

To me, the big win with SQLAlchemy is that it separates the SQL expression
layer from the ORM layer so cleanly. I can write very complicated queries with
the expression layer that wouldn't be possible at the ORM layer, and do it in
a much more composable way than concatenating SQL strings. Example:
[http://btubbs.com/postgres-search-with-facets-and-
location-a...](http://btubbs.com/postgres-search-with-facets-and-location-
awareness.html)

------
grangerg
It depends on what you're after.

I'm very comfortable in SQL, and would prefer to write my queries. But our
team has grown, and we've found that developers _say_ they know SQL, but they
really don't. In general, I've found that it's not the syntax that messes
people up. There's a significant mental "jump" between the usual procedural
coding paradigm and the "set based" paradigm offered by SQL. Some people just
never catch on.

So we're going to start using an ORM (Entity Framework Core 2) so that the
"mere mortal" developers can pitch in. I know there's a way to run raw SQL, so
I know that when we find spots where the ORM fails, we can just rewrite it
with some good SQL if we decide that's best.

But maybe that'll never happen? We've been trying to do simpler SQL stuff as
of late so that the heavier lifting in the system is done by the
application/client instead of the database (bottleneck). As the database sees
simpler, less unique queries, more stay in the plan cache, indexes are more
reliably hit as expected, and performance increases.

------
jtchang
Why are we even having this debate? There are some ORMs that bring so much
value to the table that you'd be stupid not to use them. Example being
Django's ORM or SqlAlchemy.

Also be specific in what ORM you are comparing to raw SQL. Are you talking
about Hibernate or SQLAlchemy. Are you talking about a query builder?

Good ORMs help tremendously with maintainability and security. They also let
you drop down to raw SQL when needed.

I don't think rails would have been as popular if you had to use SQL.

~~~
djrobstep
I've used SQLalchemy a lot. I've even written a keyset paging extension for
SQLalchemy.

But recently I've switched to writing stored procedures and calling them
directly, instead of going through an ORM for everything... And it's so much
easier.

~~~
wasted_intel
Keeping logic in the database like that means you can’t version those
procedures alongside the rest of your code. That’s a pretty big downside.

~~~
0xCMP
I mean you could, but it’d need to be through some kind of migration system to
update it.

~~~
other_herbert
Flyway for Java... For stored procedures you use the repeatable syntax that
way it checks the checksum of the file and if it doesn't match what is in the
migration table it will run it... Easy and always up to date... That's with
Java anyway...

------
anonfunction
If you have not read The Vietnam of Computer Science[1] I highly suggest it.

[http://blogs.tedneward.com/post/the-vietnam-of-computer-
scie...](http://blogs.tedneward.com/post/the-vietnam-of-computer-science/)

------
westurner
ORMs:

\- Are maintainable by a team. "Oh, because that seemed faster at the time."

\- Are unit tested: eventually we end up creating at least structs or objects
anyway, and then that needs to be the same everywhere, and then the
abstraction is wrong because "everything should just be functional like SQL"
until we need to decide what you called "the_initializer2".

\- Can make it very easy to create maintainable test fixtures which raise
exceptions when the schema has changed but the test data hasn't.

\- Prevent SQL injection errors by consistently parametrizing queries and
appropriately quoting for the target SQL dialect. (One of the Top 25 most
frequent vulnerabilities). This is especially important because _most_ apps
GRANT both UPDATE and DELETE; if not CREATE TABLE and DROP TABLE to the sole
app account.

\- Make it much easier to port to a new database; or run tests with SQLite.
With raw SQL, you need the table schema in your head and either comprehensive
test coverage or to review every single query (and the whole function
preceding db.execute(str, *params))

\- May be the performance bottleneck for certain queries; which you can
identify with code profiling and selectively rewrite by hand if adding an
index and hinting a join or lazifying a relation aren't feasible with the non-
SQLAlchemy ORM that you must use.

\- Should provide a way to generate the query at dev or compile-time.

\- Should make it easy to DESCRIBE the query plans that code profiling
indicates are worth hand-optimizing (learning SQL is sometimes not the same as
learning how a particular database plans a query over tables without indexes)

\- Make managing db migrations pretty easy.

\- SQLAlchemy really is great. SQLAlchemy has eager loading to solve the N+1
query problem. Django is often more than adequate; and has had
prefetch_related() to solve the N+1 query problem since 1.4. Both have an easy
way to execute raw queries (that all need to be reviewed for migrations). Both
are much better at paging without allocating a ton of RAM for objects and
object attributes that are irrelevant now.

\- Make denormalizing things from a transactional database with referential
integrity into JSON really easy; which webapps and APIs very often need to do.

Is there a good JS ORM? Maybe in TypeScript?

~~~
atsjie
I've used [http://bookshelfjs.org/](http://bookshelfjs.org/) but I'd stay away
from it, only felt cumbersome. No productivity gain.

It's built on top a query builder Knex
([http://knexjs.org/](http://knexjs.org/)) which is decent.

------
andrewstuart
I'd rather learn the ins and outs, problems and issues, highs and lows, of SQL
rather than an ORM.

ORM require just as much investment in time and even then you still need to
learn the sql to get the ORM to do what you want it to do.

SQLAlchemy on Python is a truly fine piece of software but in the end it was
much simpler and felt more powerful for me to write the SQL. And not even hard
BTW.

I only have a limited amount of time available for learning and if I can trim
out an entire class of technology (i.e. the ORM) then that's a whole bunch of
stuff I just don't need to spend time learning.

~~~
mythrwy
I prefer SQL to ORMs as well for exactly the reason you state. It's more
comfortable and it's less of a mystery.

But you don't always get to pick what code you'll be working with and if you
are working with others in a web framework pretty good chance you'll be
learning an ORM anyway.

Also really long SQL statements are no fun. Like debugging a whole program
written in one line. I think sometimes there is a temptation to get
excessively clever with SQL.

------
majewsky
> just learn SQL

This is great advice in general for everyone in a role that even slightly
touches on ops.

In my day-to-day work, I frequently observe that knowing some SQL (esp. joins,
and aggregate functions like SUM/MAX with GROUP BY and HAVING) turns you into
some sort of mighty wizard for most people. They're trying to debug a problem
in their service and not making progress for hours, and you just walk straight
into psql, take a look at the schema, do a few SELECTs, and zoom in on the
problem.

Yet nobody seems to consider SQL a valuable skill. I guess it's not buzzwordy
enough.

------
atsjie
The most cringeworthy thing I heard about ORM's actually happened two weeks
ago when I explained our use of a query builder rather than an ORM. The new
senior developer was talking about speed (??? uhm… k...) and the benefit of
being able to switch between PostgreSQL and... MongoDB. I just cringed up,
didn't know what to say. Using the same domain model in an RDMBS as a Document
Store? I really didn't know how to respond to that.

On the other side you have juniors, just starting development and working with
an ORM/ODM the first time. It's not uncommon for them to spend years in
development while learning very little SQL at all, hardly understanding the
database (apart from what they learn in school). I don't blame them. I hardly
know TCP/IP at all and still I write technology for it everyday. But ORMs are
a more leaky abstraction; a lot of complexity is made easier by just switching
to SQL.

More principal points:

\- Why do we want to abstract away further from the most important part of the
business: data. Data usually outlives the applications built on top of it.

\- Best practice is to keep backend API's stateless and requests short-lived.
ORM's promote the use of state. In client-side applications state is a much
more interesting long-lived thing, but a backend API these days? It has
actually become a lot simpler there since I've started doing development; few
modern backend servers render views these days.

I understand why the author (re-)embraced stored procedures but I never will.
Blame the overzealous PL/SQL Oracle seniors I've met, god forbid any human
bestows such complexity on it's fellow colleagues. It's also hard to automate
tests for them and promote it through DTAP alongside an application, hence I
keep putting all logic in the application.

I can imagine if the language and ecosystem are very strongly geared towards
ORM you shouldn't try to do anything else. That's just painful. But in most
languages other than Java and C# I'd make a good consideration if you really
need/want an ORM (coming from Java, I never took non ORM design seriously
there, but perhaps I shouldn't have).

Not sure where I'll stand in a few years time on this… but for now just happy
with plain old SQL and query builders.

~~~
ldarcyftw
ORM stands for Object-Relational Mapping (wikipedia). It is just a way to map
domain objects to tables. There is no "promotion the use of state" in that
definition. It is your choice to start using state in a (mis)designed manner
but please don't blame ORMs for that.

~~~
atsjie
Sorry, I should have been more precise. Not all ORM's are designed the same,
far from it.

Many ORMs are built using the Unit of Work / Data Mapping patterns. Such ORM's
map your data into a separate domain model and manage this model for you. If
your orm has something like an "EntityManager" it has likely implemented this
Unit of Work pattern.

A key thing the Unit of Work achieves is to commit changes to the database in
a single transaction. You often need to update multiple records in an atomic
way within enterprise software.

You might not be faced with such challenges in a simple app, but in monolothic
enterprise software it's a core feature of what a good backend server does.

Active Record-based ORMs or query builders aid you only a little in this task;
they expose the transaction handling logic so much so that it starts to read
as a normal SQL database transaction (and might only be cumbersome to use at
worst). Here you, the programmer manages it, similar to a normal SQL
transaction.

The Unit-of-Work based ORM is more intelligent. It manages the database
transaction for you and figures out any changes that were made to the managed
entities. In my experience all Java-built enterprise software (I used
Hibernate, EclipseLink and Toplink) are designed this way and make heavy use
of it. I've used it with Doctrine in PHP quite a lot, and my guess is C#'s
Entity Framework is also built around such concepts. That is a big slice of
the ORM market.

Here is where the state comes in; different parts of the applications
contribute to creating a single database transaction until flush-time. You as
a programmer should know when "flush time" actually happens and understand
which entities were marked dirty. That is a lot of hidden state that is
managed for you; it is in fact the core of what such ORM's do; managing state
until it's ready to be flushed. To make it really advanced, powerful ORMs (the
popular enterprisey ones) do a lot of caching too, at different times and at
different scopes.

When tackling with such tools the distance to normal SQL becomes very large. I
think that is where quite a bit of the hate comes from. It's become very
powerful magic.

I've been in places where I had to really understand how this magic works to
solve serious performance issues with it. I learned a lot, solving problems
that shouldn't have existed in the first place.

I don't like magic. It makes me hide in the corner and cry a little.

My comment was targeted towards the UoW / Data Mapper stuff and much less so
to Active Record ORM's.

------
Boxxed
My main complaint with ORMs is that the workflow goes like this:

1\. Roughly imagine the query you want to execute

2\. Map said query into whatever annoying interface the ORM actually exposes

3\. Attempt to reason about its behavior because it probably isn't exactly
what you envisioned in step 1

I would much rather just write the damn query in the first place.

------
jmadsen
An ORM is not a "query builder"

An ORM turns your result into a collection of objects. THAT is what an ORM is
for and about.

The fact that they are built on top of query builders and lighten the load
when developing is just a handy side effect.

~~~
default-kramer
To me, it hinges on what you mean by "a collection of objects." If it is just
a list C-like structs, that's great. If it involves lazy-loaded child
collections, inheritance hierarchies, or any kind of behavior at all, that
makes me worried.

~~~
spronkey
Yeah, I agree. Lazy loading in ORMs what happens when people want OO
databases. And by OO databases, I mean everything to act like it's in an in-
memory collection.

Sounds nice, but in reality, latencies, networks, massive data sets, atomicity
of operations, and a whole host of other annoyances get in the way.

... so it ends up being simpler and easier in the long run to be very explicit
about your interactions with data stores. Took me a long time to get to this
point.

------
Shorel
I do agree every one who needs to get data from an SQL database should know
SQL, and mostly, should know about indexes and how and why queries can be
slow.

But a query builder is extremely useful and in any complex application, if you
don't use one, you end up bulding one yourself, which may not be a very good
idea if you don't understand things like query injection.

So learn SQL, learn ORM, and choose in a case by case basis.

~~~
sanderjd
I wish there was a commonly agreed upon name for "query builder" that isn't
"ORM". Actually mapping relational data to objects often rubs people the wrong
way, for good reasons, but the query-building layer underneath is pretty
universally useful.

But in general I agree with you: Why _just_ learn SQL? Learn all the layers!

------
nottorp
You can write more efficient C(++) if you know assembly, even if you don't
write anything in assembly.

You can write more efficient python if you know C(++) and the various
tradeoffs between data structures, even if you don't write any C++ and don't
do any data structure by hand in the current project.

...

You can use an ORM more efficiently if you know SQL. Same thing isn't it?

That assumes the ORM doesn't completely get in the way, of course.

------
dahdum
Agree so much on this, efficient stored procedures are a godsend when
refactoring applications and are excellent at backup authorization checks.

------
andybak
Like static vs dynamic typing and frameworks vs libraries this is an endless
debate that rarely sheds much light on the topic.

The people who use ORMs daily are likely to favour them and those that think
they are the devil's work are unlikely to have intimate experience of a range
of different ORMs.

------
bmarkovic
It's "Active Record" style ORMs like Hibernate that are the culprit, and the
way many developers utilize them to avoid any contact with the realities of
RDBMs which leads to data access antipatterns which lead to poor performance
(multiple needless queries per request etc.).

Another thing people need to really give up on is the pipe dream of switching
databases -- you're not going to do it. I've never seen one single case of
people actually utilizing ORM to actually change RDBMs they store data in.

Not all ORMs are like that and the best solutions are the ones like
Knex/Objecion where an ORM (Objection in this case) is nice abstraction for
single-object access/writing and underlying SQL builder (Knex) is fully
exposed and used for everything else.

~~~
NoGravitas
I think it's misleading to think about switching databases on an ongoing
application as the use case for database independence. More important use
cases:

1\. Using SQLite for unit testing and a real RDBMS for integration testing,
acceptance testing, and production. 2\. When you are writing a library that
will be used by different projects, not all within a single organization
(e.g., a Free Software project). 3\. When you are providing a product that the
end-user may want to use with different choices of database (e.g. forum
software, Nextcloud, etc.)

------
default-kramer
I've found ORMs (such as Entity Framework) great for operations that can be
described as "find a single thing by PK and update it." For read operations
I've favored this strategy: "Imagine the ideal result set for the task at
hand. Use SQL to deliver that result set. Do the rest of the work in your app
language of choice."

Edit: I guess I should clarify that I would favor using any library that maps
result sets to lists of objects. And I would consider that to be part of "do
the rest of the work in your app language of choice."

~~~
taspeotis
Yeah EF is great for CRUD. The way I distinguish whether EF is going to be
used or not is simply whether the workload is OLTP or OLAP. At OLTP EF excels.
It's terrible at OLAP (ORMs generally are) so I'll drop to raw ADO.NET and (if
lots of data has to go in to SQL Server) table valued parameters.

Use the right tool for the job.

------
keredson
Regarding the article's point about managing your schema, there are only two
real options IMHO:

1) "The database schema is the official definition."

Programmatically generate what ever ORM objects (at build time) in a 1-to-1
fashion from a schema dump. This is the approach DKOs use:
[https://github.com/keredson/DKO](https://github.com/keredson/DKO) As long as
the code generation step is done as part of the build process, you'll have
none of the normal code generation headaches, and your build will fail if
you've made a code incompatible schema change.

2) "Your ORM objects are are the official definition."

And generate the schema definition automatically. The common process of this
is that most "generate schema" functions are stupidly lazy, and drop the work
of calculating the diff from an existing schema on the developer (forcing them
to write migrations). This is unacceptable in my eyes, just as it would be if
my version control software wanted me to write my own diffs by hand in order
to make a commit. I strongly prefer automatically generated diffs, like in
[https://github.com/keredson/peewee-db-
evolve](https://github.com/keredson/peewee-db-evolve). So you can do non-
destructive schema changes. It's a model I've re-implemented for any new ORM I
wind up using.

~~~
cwbrandsma
There is a 3rd. You define your own data structure (in JSON as an example),
and generate everything else off of that.

I've done this a couple times (as well as the two items you listed), and this
has worked the best for me.

------
ganonm
In my current project (work) I've chosen to use JDBI for communication between
model and database. For me it is the perfect level of abstraction whereby it
still feels like I'm just writing SQL queries but also avoiding a lot of the
boilerplate code that comes with having to manually manage DB connections.

I still have to write the actual code that maps the result set to objects, but
I've found this is a very small tax to pay, _especially_ with Kotlin's data
classes.

I used an ORM in a previous job (RoR ActiveRecord). I didn't find it an
altogether horrible experience, but there where many cases where we would get
these 'leaky abstractions' in the wrong direction from the model -> database
schema. There were also a lot of cases where we would realise that some
ActiveRecord query we were doing was unintentionally loading entire tables
into memory (our fault, not ActiveRecord's fault). This was usually remedied
quite easily by just RTFMing the docs, but my feeling is we could have avoided
it in the first place if we'd used a lower level strategy.

Personally, when developing a new feature I always like to start by thinking
about the database representation first and then working my way up. I think
having this sensitivity to how it should be represented in the database can
allow you to avoid many of the pitfalls that may come with using a more opaque
ORM.

Another valuable attribute I get from using JDBI is it's dead easy to mock
(like heavyweight ORMs), so unit testing stuff that interacts with it is super
simple.

------
agentultra
In a recent project we went without an ORM.

We took a tack similar to how PostgREST and PostGraphQL are structured. We use
views in the public schema to build our objects. Functions constrain our
mutations. Triggers respond to events and maintain consistency.

It makes our web API code simple and hard to introduce errors that invalidate
our customers’ data.

Don’t miss having an ORM. Always seemed like more abstraction and complication
than was necessary given recent advances in servers like Postgres.

~~~
steve-chavez
Why didn't you just use stock PostgREST, in which area did you found it
lacking?(Curious)

~~~
agentultra
The reasons were more social than technical. I think PostgREST is a wonderful
piece of software and not lacking in any regard that I am aware of.

------
brucephillips
> ORMs, however, encourage this use and often make writing precise projections
> as tedious as they are in SQL.

"As tedious as SQL" is not an argument to use SQL instead.

~~~
jjoonathan
Every extra abstraction layer comes at the cost of complexity, maintenance,
and capability, and therefore inherits the burden of proving its worth. ORMs
frequently claim to be less tedious than SQL. Failure to deliver on that
promise is a reason to cut them out.

------
dola
A lot of the reasons listed in this article actually made me shift from
sophisticated ORMs like Hibernate back to a query based approach. A really
nice framework for this (in Java) is jOOQ which gives you the possibility to
write typesafe SQL via code generation.
[https://www.jooq.org/](https://www.jooq.org/)

(I'm not at all affiliated with jOOQ - just a happy user)

~~~
gregopet
Another happy jOOQ user here! I'm ditching Hibernate wherever I can in favour
of it. And using it with Kotlin instead of Java makes it almost a form of
poetry.. I've seriously been thinking about writing a blog post, something
along the lines of "jOOQ: how I learned to love the database again"

I've been thinking about what makes jOOQ so good and a huge part of it is
brilliant engineering: SQL clauses are mapped almost 1:1 into reasonable and
understandable code while the author spends huge effort to cover new features
as databases introduce them without turning his product into a mess or
introducing API breaks in every major new version. That's hard.. but awesome!
:)

------
jim_combinator
LOL! I got fired from a project for making this argument. OMG, just get your
data from sql statements or some sort of cache and be done with it...wtf.

------
wybiral
I prefer to use SQL directly as well.

But some points to be made in favor of ORMs (some of them, anyway):

* Multiple backend support to handle different SQL engines.

* Minimized risk of accidental injection.

* Migrations.

~~~
qaq
Migrations are orthogonal to ORMs.

~~~
brucephillips
No. Migrations can be auto generated from Model changes.

------
lmm
> But the damn migration issue is a real kick in the teeth: changing the model
> is no big deal in the application, but a real pain in the database. After
> all, databases are persistent whereas application data is not. ORMs simply
> get in the way here because they don't help manage data migration at all.

If you ORM doesn't help you manage data migration, get a better one. Any ORM
worth its salt should let you generate migrations; at least Django and
Hibernate (+ Liquibase) can. I find the key to making everything work nicely
is to let the definition in the application/ORM be the source of truth for
what the DDL looks like. If you want a particular SQL table layout, figure out
how to tell the ORM to generate it. If you try to retrofit an ORM onto an
existing table schema (which it seems is this author's preferred approach),
you're in for a world of pain.

> These two things don't really get along because you can really only use
> database identifiers in the database (the ultimate destination of the data
> you're working with).

> What this results in is having to manipulate the ORM to get a database
> identifier by manually flushing the cache or doing a partial commit to get
> the actual database identifier.

Use UUIDs. Generate them in the application, but use them directly as
identifiers (pkeys) in the database.

> Something that Neward alludes to is the need for developers to handle
> transactions. Transactions are dynamically scoped, which is a powerful but
> mostly neglected concept in programming languages due to the confusion they
> cause if overused. This leads to a lot of boilerplate code with exception
> handlers and a careful consideration of where transaction boundaries should
> occur. It also makes you pass session objects around to any function/method
> that might have to communicate with the database.

> The concept of a transaction translates poorly to applications due to their
> reliance on context based on time. As mentioned, dynamic scoping is one way
> to use this in a program, but it is at odds with lexical scoping, the
> dominant paradigm. Thus, you must take great care to know about the "when"
> of a transaction when writing code that works with databases and can make
> modularity tricky ("Here's a useful function that will only work in certain
> contexts").

Use a monad to represent "this function has to happen in a transaction", then
all those problems go away.

------
yason
An ORM is both a more native way to represent data rows in the application and
a convenient wrapper to automate much of dealing with SQL and result sets. But
only a fool would wish to see an ORM as an object-based abstraction over SQL.

Dynamic languages such as Lisp and Python can often make-do without a specific
ORM layer because it's easy to stash data into lists and dictionaries. If you
can read rows from your result set into a list, make dicts or structs out of
each row, and pass around that list or its entries to various functions you've
just implicitly implemented a LRM: lispy relational mapping. But sometimes it
just fits to create objects out of rows.

Simple wrappers will do for an ORM: the best kind always make it clear that
you're just using a machinery to operate an SQL database instead of updating
an object and then "saving" it back to disk in the end.

------
sreque
I really enjoy seeing this debate because I hope more anti-ORMers unite and we
see less use of ORMs in future projects. There are other disadvantages of ORMs
that I haven't seen mentioned much:

1) low-level performance. Even if, and it's big if, you manage to get your ORM
to generate somewhere near the optimal query, ORMs in my experience are always
_significantly_ slower than hand-written sql. When I last benchmarked, I
couldn't get hibernate to be any better than 4x as slow as JDBC, and keep in
mind that's pure CPU overhead.

Think your service is I/O bound? It's probably not, and it's probably your ORM
to blame. This may be less of an issue for a dynamic language like Python, but
I see it as a much bigger issue for Java/C# and friends.

2) Debugging/understandability. Did you know that hibernate maintains a cache
of every object you load in a session until you flush it? I didn't, until we
had an outage because our service OOM'ed while loading too much data without
flushing.

Do you know how exactly your ORM is loading and saving data and when?
Depending on your use of the various lazy-loading and storing features of your
ORM, it can be very difficult to reason about when and how your ORM is talking
to your database.

Do you know how your ORM is integrating with your cache, which is likely
memcached? Why is your ORM integrating into itself the concept of a cache in
the first place? In my experience, hibernate gets caching wrong, and that's
not entirely its fault. It's difficult to get caching right in the general
case. But I would rather be forced to think about caching up front and get it
right for my use case rather than try to understand how Hibernate is doing it
and working around its mistakes and limitations.

The common theme is that the use of an ORM makes it incredibly more difficult
to understand, reason about, and debug your application rather than using a
simpler library. In my experience, this alone makes an ORM not pull its
weight.

~~~
huherto
I kind of agree with you.

Using an ORM saves your from having to implement a lot of code. But you still
have to understand how everything works. ORMs makes it look easy, but there is
a lot of magic involved that you need to understand sooner or later.

------
Demiurge
This type article of article has come up countless times before. Why not both?
Using both, you let the ORM handle regular, boring CRUD, validation,
repeatable exercises, and write tricky joins, aggregates, function calls that
ORMs don't do well in SQL. Every decent ORM supports dropping to SQL.

------
misterbowfinger
Ah yes - the proverbial "ORMs are bad, just learn SQL" post. This is analogous
to saying "don't use frameworks". Sound ridiculous? Yes, yes it is.

The law of leaky abstractions applies to many, many things, ORMs included. I
would also argue they apply in different degrees, usually related to the
design of the ORM (the post mentions SQLAlchemy vs. Hibernate, for example).

But consider the following:

1) Why do people still use ORMs? Exactly.

2) Question 1 but s/ORM/framework_or_widely-used-library

3) ORMs allow you to develop faster, and deliver value

4) Beginners already have a hard time coding, designing, and understanding
what they're doing. ORMs provide a nice abstraction over underlying data
stores

5) Although fraught with peril, ORMs provide a common interface that'd give
_some_ help if you switch data stores

6) Multi-line SQL statements are a huge pain in some languages

~~~
atsjie
> This is analogous to saying "don't use frameworks". Sound ridiculous? Yes,
> yes it is.

When developing API's in Golang or for microservice / serverless architectures
not using a framework might actually make a lot of sense. Also microframeworks
(trimmed-down versions compared to opinionated frameworks) are very popular in
almost any language.

~~~
icebraining
I'd say that Golang rather comes with a (simple) framework in the standard
library, so you don't need a third-party one. After all,
http.Handle/HandleFunc clearly follow the Hollywood Principle:
[http://wiki.c2.com/?HollywoodPrinciple](http://wiki.c2.com/?HollywoodPrinciple)

------
dang
Large previous discussions:

[https://news.ycombinator.com/item?id=11981045](https://news.ycombinator.com/item?id=11981045)

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

------
jdc0589
I have had exactly one positive experience with ORMs: nHibernate + fluent
nHibernate config with .net's IQueryable expression interface. That is the
ONLY time I have used an ORM and felt like I had enough control to do what I
needed for edge cases and still the a perceived productivity boost; granted,
there was what seemed like a large learning curve at the time. That said, I
must prefer to just write queries myself and use an object mapper (Dapper,
etc...) to eliminate a lot of the boilerplate.

Maybe there are some amazing ORMs for other stacks. But my time with .net was
the only time I heavily utilized sql DBs.

------
beagle3
Many posts say that an ORM is a good idea for the type safety / vulnerability
/ composibility / portability aspects. But that’s actually independent from
the object mapping aspects - e.g. web2py provides all of the above in its DAL
without the object mapping. It does sort-of abstract over SQL variants with
Python query syntax, but no objects are involved.

Nim’s Ormin is still in not fully functional but is providing the same in a
static language with straight up SQL.

My experience is that the O aspect of ORM is where it doesn’t help (and often
gets in the way); if that’s your experience, consider DAL and Ormin

------
gfiorav
This article is valuable since it's raising some interesting pitfalls it's
good to know and avoid.

That being said: you need to use the right tool for the job.

It's just hilarious how people expect the new "foo framework/paradigm" to
solve ALL the problems... jeez! It's nice to know and understand new
paradigms, but you really need to evaluate your case.

ORMs took away the complexity of 90% of web apps. All the "Model X has many
model Y". If you step outside of that realm with the ORM then you're
officially "fighting the framework", and bad things will happen.

------
groue
There are ORMs that address partial records, multi-threading troubles brought
by lazy loading, uniquing, auto-updating records, and, importantly, put raw
SQL on the same level as the query builder. I think of
[Diesel]([http://diesel.rs](http://diesel.rs)) and
[GRDB.swift]([https://github.com/groue/GRDB.swift/blob/master/Documentatio...](https://github.com/groue/GRDB.swift/blob/master/Documentation/WhyAdoptGRDB.md)).

------
robgibbons
> ...in order to use ORMs effectively, you still need to know SQL. My
> contention with ORMs is that, if you need to know SQL, just use SQL since it
> prevents the need to know how non-SQL gets translated to SQL.

This is presuming that you will never come across ORM being used almost
exclusively in any future projects. After all, ORM doesn't seem to be going
anywhere (even with all the hate against it). One could make an argument that
learning both effectively would provide a better general foundation.

------
api
The problem with ORMs is that they're an attempt to hack relational models
into languages that lack them. The thing nobody has ever done is add relations
as first class citizens to a language. Instead we have only maps, arrays, and
lists in our languages and we have to shoehorn richer data into that.

That's why hierarchical document databases have had a resurgence. At least
they match the data model if the language.

------
jaequery
Are there programmers these days that actually don't know SQL? I find that
hard to believe, that's gotta be like a CS 101 course.

------
adamnemecek
I don't hear people complaining about say LINQ. Maybe it's just that your
language and/or ORM or integration thereof suck?

~~~
Someone1234
LINQ isn't a ORM. I assume you mean Entity Framework?

EF definitely has the foreign key issue. We have around a thousand tables, we
tried to generate the classes for all of them including foreign keys, problem
is that when you create a context that references even only a single table, it
will load everything that is foreign keyed including siblings of siblings of
siblings, until you run out of memory.

Only way around it is to not set up foreign keys which massively diminishes
the value of using EF, so we wound up creating two copies of each table's
classes, one with and one without foreign keys. That causes its own issues.

~~~
flukus
Sounds like you disabled lazy loading and that forced everything to be loaded
at once. That's a user error, not a EF fault.

This is a common theme I've seen with people blaming ORM's for being slow,
it's the devs not using them appropriately more than the ORM's themselves. Not
to say that they don't have their own issues.

~~~
Someone1234
With or without lazy loading enabled the result was the same. Generating the
structure took seconds and went OOM with enough tables.

LazyLoading impacts what data is retrieved from the database (or more to the
point when), this is a structural issue before a query was even sent to the
database. It would die while generating the query, not sending the query or
populating the result.

You likely should have asked for more information before concluding it was
"user error."

~~~
useerup
I have been working with EF for years now. It has it quirks - but this is
_not_ something that I have ever experienced, nor have I heard of anything
like it before today.

What I have heard of is traversing the entire graph and causing cascading
loading of navigational properties. Yes, I have done that. Something like
AutoMapper will do that to you, if you are not careful. Been there and done
that.

How did you determine that it OOMed while _generating the query_?

~~~
Someone1234
> How did you determine that it OOMed while generating the query?

By looking at the call stack when the exception was thrown (and the fact that
nothing hit the database).

------
cryptonector
Some people, when faced with a database problem, choose to use an ORM on top
of an RDBMS. Those people now have three problems.

The problems are: the original problem, the expressive and performance
disaster that is every ORM ever, and the layered and hidden RDBMS whose
peculiarities nonetheless always find a way to leak through the ORM
abstraction.

Fun stuff.

Do what TFA says: just learn SQL.

~~~
Demiurge
I've been using Django + Django ORM for a decade, and it has covered vast
majority of database usage. Entire applications written with zero SQL, and
clean DRY code describing data and allowing to use it in the code. And yet
other application written with lots of SQL.

The real question is, why anyone using an ORM not learning SQL, how the the
thing they are mapping to objects actually works?

------
tuespetre
This topic has been done to death and the rate at which comments have been
made since this was posted tells me that we either haven’t learned much of
anything as a collective, or we just like to rehash the same talking points
because... we can.

Know how and when to use ORMs. Probably learn SQL first. Don’t believe that
any shiny bullet is silver.

Or in short: “No”

------
exabrial
It's not my quote but it goes along something like in order to use a layer of
abstraction effectively you need to know 1 layer deeper.

Honestly what are you doing using an orm without knowing sql? The point isn't
to hide sql, it's to automate repetitive tasks. Who goes to learn Angular.js
without first knowing JavaScript or html?

------
joshtopher
Learning SQL is all well and good but what am I supposed to do then? I can't
stand writing SQL in string literals

~~~
syntheticcdo
Use a query builder, for example [http://knexjs.org/](http://knexjs.org/) for
Node.

------
esfandia
What's the current state of object-oriented databases? Any progress since good
old Gemstone? Why was Gemstone unsuccessful? Cost? Performance? Tie-in with
Smalltalk? Is there any promising OODB being used these days? Because now that
would take care of the OO-relational impedance problem.

------
eecc
Yup, take those Repository designs, where you can’t join on two tables unless
stars align very specifically. Of course there are very good technical reasons
for that, but isn’t this because the abstractions are leaking onto each other?
What’s the added value of the extra layer then?

------
sametmax
Again, ORM haters are missing the point.

It's not about avoiding to write SQL, it's about to have standardized API on
which all your architecture can count.

Why do you think Django was so successful ?

Because it was built in a way that allowed a rich and powerful ecosystem to
flourish.

The Django ORM is not the best out there and it's doing plenty of silly
things. If you don't know SQL and you use it you will be in a world of pain.

However.

Because Django features this ORM it can:

\- provide auto-generated forms from db model, outputting HTML and validating
user inputs, saving changes automatically to the DB.

\- provide auto-generated CRUD views from the db model, that you can extend at
will.

\- provide auto-generated admin

\- provide tookits and helpers to deal with your data: signals, various forms
of getters, native object casting, advanced validation, better error
messages...

\- provide entry points for extending the data manipulation API, in a generic
way (fields, managers, etc)

\- provide tooling for migrations

\- provide auth and permissions

\- provide user input cleaning and escaping

\- automatically deals with value normalization: encoding, timezones,
text/number formats, currencies... There is one entry points for those where
you can put custom code, and you don't need custom code most of the time since
somebody did the work for you more often than not.

\- ensure all django projects look the same, so that it's very easy to move
from team to team or train people

\- formalize the schema, which became a the documentation and only source of
truth for your data, that is commited to your VCS. Wannan know what a Django
project is all about ? Check urls.py, settings.py and models.py. Done.

The cherry for this cake is of course the fact 3rd party modules (so called
django "apps") can leverage that, which lead to the amazing ecosystem Django
has.

\- auto-generate REST views from model (eg: django-rest-framework), again that
you can tweak as much as you want.

\- dozens of auth backends.

\- data manipulation (workflow, filtering, dashboard, analytics) that just
work.

\- tags, search, comments, registration and all those stuff you alway rewrite
otherwise.

And because they all use the ORM, they are all compatible with each others.
And they all work on Mysql, Oracle, SQlite and Postgres, like the entire rest
of the framework, out of the box, for free.

You want to do that in any other framework (except RoR) ? You'll get a lib
that do half of it, and let the persistence and API integration work to you.
And it will not play with others. And that will be integrated differently on
another project. If you have a lib at all ! Oh, and you have to use the proper
DB. If you are corporate or startup, it won't be the same one and you better
hope the lib author is in your shoes.

All that stuff is easy in Django because you have a centralized, easy to
inspect, standard, shareable definition of each of your model in one place.

That's what ORM are for. Not "doh, SQL is hard".

Now you could get some part of those benefits by creating central models using
schemas untied to the DB, such as marshmallow. It would be an interesting
take, but my guess is that you will end up with interfacing it with your DB
with some kind of layer, that would look like an ORM anyway.

------
gigatexal
Disagree on joins and foreign keys: normalized tables make for smaller tables
in my experience. Sure you might denomalize for reporting but it’s best to
normalize and then demoralize as needed not avoid foreign keys because you
have to write a large number of joins

------
randomdrake
"What ORMs have taught me: just learn SQL"

Could be retitled:

"I should have learned SQL before dealing with ORMs."

Every single point the author brings up comes seems to come down to simple
database design, lazy development, or not understanding their tools. They
really don't seem to have anything to with ORMs or query languages.

Any screwdriver can make for a bad hammer and some screws may go in with a
large enough mallet.

> _Perhaps the most subversive issue I 've had with ORMs is "attribute creep"
> or "wide tables"_

Normalization of data is required whether you're using a query language or an
ORM to access it. The fact that ORMs make it easy to "hide" the fact that
you've added 500 columns to a table isn't the ORM's fault.

> _Knowing how to write SQL becomes even more important when you attempt to
> actually write queries using an ORM. This is especially important when
> efficiency is a concern._

What do they think the ORMs are doing? Magical incantations over the disks?
The ORMs are just using queries too. You can write really horrifically bad
queries in a query language and also abuse ORMs, but that doesn't make either
one bad. Most ORMs can let you see precisely the SQL they are creating. If
not, the database will surely log the queries for you and let you know what's
going on.

> _The problem is that you end up having a data definition in two places: the
> database and your application._

Welcome to the fact that we have multi-layered technology? There's always
going to be discrepancies between the layers that have to be ironed out
because no data designs are perfect or future-proof. The author then attempts
to bring migrations into the picture as if database migrations are somehow
just not a problem if you aren't using ORMs (hint: database migrations have
always been tough even in very well-design systems).

> _Dealing with entity identities is one of those things that you have to keep
> in mind at all times when working with ORMs, forcing you to write for two
> systems while only have the expressivity of one. What this results in is
> having to manipulate the ORM to get a database identifier by manually
> flushing the cache or doing a partial commit to get the actual database
> identifier._

Sounds like a pretty frustrating example, but I've worked with at least 10
different ORMs I can think of off of the top of my head and not a single of
them required "manually flushing a cache" or a "partial commit" to "get the
actual database identifier." I wouldn't write this up as being an issue with
ORMs or that this problem would be magically fixed by only writing SQL either.

> _Transactions. Something that Neward alludes to is the need for developers
> to handle transactions. Transactions are dynamically scoped, which is a
> powerful but mostly neglected concept in programming languages due to the
> confusion they cause if overused._

Transactions are pretty straightforward and I cannot agree with: "The concept
of a transaction translates poorly to applications due to their reliance on
context based on time."

Transactions don't care about time at all. They care about _order_ and making
_sure_ that things are completed in a certain series of steps. This actually
translates _very_ well to applications, especially when you have processes
that take a long time, where you don't want something to happen unless another
thing happens first.

While a decently-written article, this comes across as someone who learned
about ORMs more deeply than databases, discovered the flaws that ORMs have,
and decided that query languages must be the only way forward.

This ignores the fact that we created and adopted ORMs _after_ struggling
through years of rigid queries smattered throughout code.

Writing bare queries has a time and a place, but ORMs have saved countless
hours of development time, and allowed for vastly improved longevity of code.

Don't throw the baby out with the bathwater.

------
justaaron
yes yes yes! the point is NOT "learn sql" you already know sql, FINE!

the point is: use sql, not orms!

migrations are best done in pure sql, i'll contend that model-inflation is
best done in pure sql also.

another thing: if the format is json, that's already a nested "joined" blob of
usable data! it's what the end result of a sql join would achieve, the client
often just has to drill into that data blob and everything needed for the
entity in question is already there!

------
kapv89
If you are working with Node.js and Postgresql, give
tabel([https://github.com/fractaltech/tabel](https://github.com/fractaltech/tabel))
ORM a shot. It is an unconventional ORM that works with pure JS objects and
arrays, instead of "Model" classes, and "Collection" classes, and what not. It
has quite a few other nifty features too.

------
hota_mazi
What ORM's have taught me: be comfortable with both SQL and at least one ORM,
and use each appropriately.

------
federicoc
ORM isn't a query builder and that's not related to learn or not SQL.

------
leeoniya
query builders are the sweet spot.

~~~
pbecotte
I agree- Sqlalchemy without the ORM is pretty much the best code I've seen.

~~~
dfee
How does the ORM detract from sqlalchemy? I’ve been very happy with it for
years, and I’m not clear on where an advantage is explicitly not using
mappers.

------
mythz
You can do both. My preference is to use "Micro ORMs" which provide a thin,
light-weight, typed RDBMS-agnostic API around the most popular CRUD operations
but also allow you to execute custom parameterized SQL when you need to run
more complex queries but still let you use their fast mapping to populate
clean POCOs/POJOs to take the tedium away from forcing you to become a manual
bookkeeper from extracting the results into a more manageable form.

.NET has particularly nice support for developing typed ORM's by utilizing
typed Expressions which lets you parse the syntax tree of the expression
(instead of executing it) so you can generate the appropriate SQL that matches
the intent of the expression. You can check out a live example of what that
looks like for C# in:

[http://gistlyn.com/?gist=84129042921da413661c96545a63e541&co...](http://gistlyn.com/?gist=84129042921da413661c96545a63e541&collection=991db51e44674ad01d3d318b24cf0934)

Although the development experience is more productive using the rich intelli-
sense inside any C# IDE. It's not just the Type Safety and producitivy that
typed APIs offer, ORM's also provide built-in conventions for converting RDBMS
types into the most appropriate language data type and their typed
abstractions take care of generating the appropriate RDBMS specific SQL for
each supported RDBMS.

A lot of the stigma of using ORMs is from "Heavy ORMs" which constantly fight
the leaky abstraction of mapping a Relational Data Model into a Hierarchical
Object Model which I've never seen an implementation I've liked, they're
always inefficient and expose APIs that make it difficult to know what SQL is
generated or have any ideas which APIs perform hidden perf-killing N+1 queries
behind the scenes. Many Heavy ORMs want to maitain entire control over the
source code used to interface with your RDBMS. They should be separated from
"Micro ORMs" which are loosely coupled so it only needs a DB connection a Type
definition that matches the RDBMS table or schema that's returned where they
provide a clear 1:1 typed mapping of an RDBMS table to your programming
languages Type.

The Types provide a contract your app logic can bind to and given they can map
to clean disconnected POCOs/POJOs they can be reused to develop declarative,
safe, typed Web Services that can be inferred from the Type's schema saving
you the effort from having to implement it:
[http://docs.servicestack.net/autoquery-
rdbms](http://docs.servicestack.net/autoquery-rdbms) as well as automatically
generating the UI to query it:
[https://github.com/ServiceStack/Admin](https://github.com/ServiceStack/Admin)

Disclaimer: I've developed the above.

If your ORM is causing you friction by all drop down to custom SQL, but don't
use Stored Procedures unless you've identified situations where they provide
clear benefits over their trade-offs. They're essentially free text commands
without the support or capability of a proper programming language that splits
your logic from your system making it harder to reason about it in isolation
that doesn't benefit from the investments around maintaining source code, e.g.
development environments, source control, CI, static analysis & compiler
feedback, fast unit testing, REPLs, etc.

I wouldn't recommend using an ORM to save you from learning SQL, but rather to
leverage ORM's to save the effort and boilerplate from interfacing your
programming language with your RDBMS and provides an "in code" contract
representation of your RDBMS Tables that your App logic can bind to.

~~~
empthought
I wonder why Git, CircleCI, test runners, and IDEs all have that “reject
stored procedure code” logic built into them...

> doesn't benefit from the investments around maintaining source code, e.g.
> development environments, source control, CI, static analysis & compiler
> feedback, fast unit testing, REPLs, etc.

------
EGreg
ORMs were pretty limited at the time I began writing my first platform,
phponpie.com . I remember looking at Propel and Doctrine at the time.

Since then, I've written our own ORM which works in both PHP and Node.js, but
we're the only ones that use it. It's been battle tested, though, with
millions of users and variations. I would say that many of the issues the
author brings up were things we had to face, and we solved them.

1) Schema – the ORM should have a script to regenerate base classes from the
database, so that your schema only lives in once place. The nice thing is,
after that, your IDE can help you out instead of writing sql by hand. It can
use your language syntax to catch unbalanced parentheses, and more.

2) Adapters – the ORM should be modular so you can hook in adapters for MySQL,
PostGres, SQLite, MongoDB, and various key-value stores.

3) Joins – the ORM is supposed to be smart enough to describe relationships
and automatically write the most optimized JOIN queries for you. For example
$article->getTags() . You could, of course, implement this stuff yourself
manually but it gets tedious, when the code could easily be autogenerated with
stuff like $article->hasMany('tags', ...) kind of like this:
[https://qbix.com/platform/guide/models#relations](https://qbix.com/platform/guide/models#relations)

4) Insight – using an ORM makes you pass actual values in a structured way,
instead of interpolating them in a string. Thus you don't make the
catastrophic mistake of forgetting to escape them, allowing SQL injections by
Mr Bobby Tables. Also our ORM can do SHARDING in the app layer, especially
useful in Node.js where it can issue simultaneous queries to several databases
and combine the results. Although I recommend using CockroachDB these days :)

5) Flexibility – the ORM should support fetching partial objects, but with the
Primary Key so they can be saved back. Recently we even added support for
vector-valued lists, something we needed for extra flexibility.

6) Transactions – the ORM should be smart enough to handle transactions, in
fact support nested transactions on various shards. Since the database engine
usually does not support nested transactions, you need to emulate that in the
app layer. For instance, when you start a session, you might want to begin a
transaction and lock the session for update.

7) Methods – objects which are fetched can have user-friendly methods added,
like $stream->exportToClient() and so on.

It's free and open source. Here are examples of usage:

[https://qbix.com/platform/guide/database](https://qbix.com/platform/guide/database)

[https://qbix.com/platform/guide/models](https://qbix.com/platform/guide/models)

------
tzahola
“What compilers have taught me: just learn assembly”

------
jlebrech
when you learn how to use views and stored procedures you'll speed up your
application so much.

* and temporary tables, etc.

------
ux-app
fuck ORMs. Here's the thing, the DB queries will make up less than 0.1% of
your typical codebase. Spend a week and write a good fucking query instead of
relying on your bullshit magic blackbox to do it for you. Even a shitty query
is going to perform better than the 100s or (literally!!) 1000s of lines of
ORM code which need to happen to generate your "select * from users" query.

seriously. grow up. learn the amount of SQL you need to and napalm any ORM
that comes within arm's reach.

and if you're not sure about the SQL you've come up with then just go a few
cubicles down and ask your DBA what they think. Chances are they'll write
something 1000x better than you came up with an you'll have learned something
along the way. win-win.

