
To ORM or Not to ORM - ingve
https://eli.thegreenplace.net/2019/to-orm-or-not-to-orm/
======
bunderbunder
I've come to a couple conclusions, over the years.

First, when you get down to it, the most-valued feature of ORMs is not the
"writing queries in some language other than SQL" feature, it's the "not
having to write a mess of mapping code" feature.

Second, the biggest drawbacks to ORMs all derive from the "writing queries in
some language other than SQL" feature.

Fortunately, there are tools out there that solve the "mapping tables to
objects" problem without trying to control all access to the database: So-
called lightweight ORMs like Dapper and Sql2o. To me, these hit the sweet
spot: They give you most the increase in productivity while steering clear of
most the increase in complexity (and, by extension, decrease in
maintainability).

~~~
ozim
Actually "writing queries in some language other than SQL" which has static
typing and catching issues in compile time is quite big for me. Add automatic
database migrations that are also keeping types in line with code and whole
bunch of "mess of mapping code" goes away. Though I use .NET EntityFramework
which by now is really mature and heavily invested into by MS. Not sure how it
is with other environments but I think I have it too easy.

~~~
SOLAR_FIELDS
Lack of adequate support for versioning your schema and in general bad support
for anything versioning with RDBMS is one of the main dislikes I have for SQL.
I find SQL to be a beautifully expressive language when you get the hang of
it, but no RDBMS that I know of has been able to adequately tackle the
versioning problem at both the schema and the script level. Everyone's schema
evolves over time, why is it so difficult for the RDBMS to try to capture the
idea of database evolutions? This is the single biggest selling point for me
in ORM land, because with regular (read: non-SQL) code you have all the
typical version control mechanisms to help you manage changes to the schema.
It's still not ideal, but a lot better than trying to manage this kind of
thing in any of the tools that mainstream RDBMS have.

~~~
mikeryan
This is a big thing for me. Most ORMs I’ve used have decent migration tools
which is a huge benefit for managing your schema. We enforce changing Schemas
via migrations as opposed to any sort of auto-migrations some packages offer.
Makes life a ton easier being able to put these changes into a build pipeline.

~~~
SOLAR_FIELDS
Migrations (or evolutions, depending on the language you are using) are a joy
when implemented properly. Alembic and Play Evolutions are two frameworks that
I have dealt with specifically that are pretty great and hard to go back from.

------
out_of_protocol
For everyone complaining about orm-something - take a look at elixir's Ecto.
It basically allows writing sql via native code. Good for composability, type
casting, stuff like that.

    
    
        Account 
        |> where(active: true)
        |> join(:left, [a], p in Post, on: p.account_id == a.id)
        |> where([a, p], a.foo == "bar" or p.bar == "foo")
        |> group_by([a, p], a.id)
        |> select(...)
        |> limit(10)
        ...
        |> Repo.all()
    

Plus there are changesets for data validations (can e.g. catch and return
uniquness errors via unique index)

P.S. For update/insert queries it doesn't make sense to write UPDATE statement
by hand

    
    
       UPDATE accounts SET ... 50 fields ... WHERE id = 1
    

P.P.S. changeset example:

    
    
        def changeset(record, attrs) do
          record
          |> cast(attrs, [:number, :note])
          |> validate_required([:number])
          |> validate_format(:number, number_format())
          |> unique_constraint(:number, name: "foo_bar_index")
        end
    

will return nice error if number is missing (_before_ running sql query) or
already taken (_after_ running a query, on DB error)

~~~
udfalkso
Came here to say this. Ecto is solid. Also, the results come back as elixir
structs and not bloated objects so it’s much faster than ORMs in many other
languages. Additionally, the functional approach makes everything much less
magical.

Changesets can take a little while to grok, but they work.

------
hardwaresofton
While I learn towards not using an ORM, the productivity gains (at the very
least early on in development) are undeniable. What I've always looked for are
frameworks that give you an ORM _but also_ make lower level queries very easy,
normally via a query builder, allowing you to go back and forth between levels
of abstraction. If I had to choose, I prefer libraries that give you the lower
level of abstractions first and then build upon those to offer an OOP-based
ORM approach (which is what I believe most people think of when they say
"ORM").

It's a spectrum to me:

raw (parametrized) queries ------ query builder + serialiation/deseralization
---------------- OOP-based ORM

One of the best libraries I've ever seen get this right was TypeORM[0]. It was
easy to get started with, includes consideration for migrations, allows you to
use both the query building and annotated-class approaches where appropriate,
also allowing for use of the repository pattern if you're comfortable with
that, and has pretty great support for lots of different backends (I've used
postgres the most though). All of this from a F/OSS project (~2 years ago I
was also involved in a C# project during the switch from .NET 4.x to .NET
core/standard and was very very annoyed that things I could easily do with
TypeORM weren't available/worked out yet in EF core at the time).

[0]: [https://github.com/typeorm/typeorm](https://github.com/typeorm/typeorm)

~~~
gmac
I loved TypeORM initially, but came to the conclusion that it was written by
people who really got TypeScript, but didn't really get SQL.

For example, we were converting from Sequelize, where we were catching unique
constraint violations and responding appropriately, and we got quite confused
that in TypeORM those errors never got thrown ... until we discovered that
TypeORM decides unilaterally (and almost unbelievably) to _replace_ your
original record in that situation.

We have since ripped out all third-party ORMs and migrated to a system of
typed helper functions and tagged templates (where all the typings are
generated automatically at compile-time by inpecting the database). This is
really rather wonderful — we now have full access to all sorts of goodies like
native UPSERT, we know exactly what SQL we're going to get every time we touch
the database, and yet the ergonomics are excellent, because everything going
in and out of the DB is fully typed, including even column and table names in
basically-raw SQL. A blog post (and perhaps eventually a library) is in the
works ...

~~~
Benjamin_Dobell
I couldn't agree with this more. I've just started using TypeORM and the
TypeScript aspect is great. However, here I am trying to do a simple distinct
left join, but it's _extremely_ difficult to write because the query APIs
consistently get in the way.

We're regularly receiving invalid SQL errors, which from a typed language/API
seems particularly odd.

We're doing this exact same query on the exact same DB in ActiveRecord/Ruby
and it's trivial to both write and understand.

------
henning
I wish people did not think the choice was solely between "write raw SQL with
raw strings" and "try to pretend the database is object-oriented when it is
not."

The third approach is to safely wrap the database and its columns with code in
a way that is composable.

In Python, SQLAlchemy has an ORM, but it is optional, and you can just work
with tables and columns if you want.

~~~
wvenable
The real third approach is that you can safely pretend the database is object-
oriented for manipulation and simple lists and still use SQL for complex
queries. Most ORMs let you safely mix and match both methods easily.

This ORM or not ORM is the wrong question. Use an ORM to save you headaches
where it's appropriate and use direct SQL when it's not.

~~~
jweir
You are absolutely correct. Using both is a very valid options.

We use ActiveRecord a lot, and then have custom SQL queries using
`find_by_sql` for very complex, optimized joins. It works very well. Rails
gets out of the way when we need it to.

~~~
philwelch
Two caveats with find_by_sql: it’s read-only, so no insert or update commands,
and it still does column-to-instance-variable monkeypatching on the object
level, as opposed to the class-level monkeypatching that’s applied to normal
ActiveRecord classes as soon as the DB schema is read.

~~~
Falling3
For the former, there's always ActiveRecord::Base.connection.execute. For the
latter, I think it's more complicated than that. There also is object-level
mapping even for regular AR usage. If you do something like Foo.select("true
as bar"), your Foo objects will have a bar variable available to them.

~~~
philwelch
This is true.

------
dliff
I currently work on a project that is based on Django ORM. Before this, I
almost exclusively hand-wrote all SQL.

I think you can quickly outgrow the limits of ORM... at least Django's.
Whether it's needlessly fighting with ORM to get joins correct, ORM deciding
it's going to loop through n records instead of joining on DB server, simply
doing complex aggregates that ORM won't support, or doing DB-specific stuff.
Postgres has some amazingly powerful features that many don't know about
because they only learn ORM.

My experience: It can end up being double work because you spend an hour
getting the query to work with ORM. Then, a week later, the requirements
change and you have to add 1 thing for which it's just not feasible to use
ORM. Then you're rewriting it completely with hand written SQL.

ORM seems really popular with web/mobile api now, and hand-in-hand a trend of
not learning SQL. I think it's too bad as it creates another layer or
separation to knowing how your app truly works. It's a layer that is still
important to be familiar with, especially as a project grows.

Using custom SQL with Django has been a mixed bag for me. If you dump JSON
from your query, some fields won't be consistently formatted with endpoints
that still use the ORM. You can "load" the result into a Django model, but it
can be difficult to make it work if you have custom fields you're returning
from your custom SQL since Django doesn't know how to format them. (maybe
there is a solution to this I haven't found?).

I like the approach of using a basic query-builder for routine insert/update
and maybe even select.

~~~
wolf550e
I'm a former Oracle DBA and I love the Django ORM. After you learn it, it does
almost anything I want, and when I want custom SQL I just write custom SQL.

Learning it well is important though, and inspecting the SQL that was
generated for your test cases after you've implemented a feature is also
important.

It can do multiple joins with same table when needed, it can do filtered
prefetch, it can do subqueries, it can do exists(), it can do group by, it
lets you use SQL functions Django doesn't know about out of the box, etc.

But I think the documentation is not intuitive, and in the complex cases the
ORM code looks complicated and I think I prefer writing SQL in that case
because there is greater chance that the next developer will know SQL than
that of them knowing advanced Django ORM features. Also, more chance of
getting stack-overflow help for SQL.

------
echelon
In my experience ORM always shoots you in the foot when your use case exceeds
what an ORM can provide. ActiveRecord has been a terrible mess for us because
the facade masks the underlying behavior (transactional behavior) or often
doesn't do what it advertises (commit hooks, timestamps, auto-increment).

ORM is great for startups trying to build out an MVP, but beyond that scale
it's an anti-pattern.

The best solution I've ever seen is jOOQ, which is essentially typified SQL.
Your queries have to pass compilation, but the DSL is essentially SQL itself.
It's an added layer of security yet still manages to feel like it's easier to
write than raw SQL.

More languages need a jOOQ. It's so good that I'd choose Java just for the
ability to leverage jOOQ.

~~~
cies
I came here to say that any article discussing the benefits/downsides of ORMs
should include JOOQ. It takes such an interesting spot in the design space:
not an ORM, but a super flexible, type-safe query builder with lots of helpers
so you dont write more code than necessary.

~~~
Noumenon72
I work at a place with sort of a homebuilt ActiveRecord system made of
generated code. JooQ did all the same mapping and object creation just by
inspecting my database with a bunch of boilerplate Maven XML, so I wouldn't
have to rely on the homebuilt version of SQL join.

------
hyperman1
I recently put a lot of refactoring work in a java app. The enterprise
architects made a list of decisions that amounted to forbidding an ORM, so a
lot of JDBC/ResultSet boilerplate was included. The program had a tendency to
UPDATE a few of the columns, then SELECT the same data right back a few lines
later. Plenty of inconsistencies in the logic. This means refactoring went
like this:

First step: Create for each table generic methods to insert, update,select all
fields. Now at least memory and DB are consistent.

Next step: All these methods are mostly identical and there is a lot of
commons-beanutils in there. So add an annotation to the relevant getters and
generate most SQL statements on the fly.

Next step: Replace the incoherent commit/rollback mess with clear boundaries.
Either it succeeds or it fails.

Next step: To stop the never ending reloads of data already in memory, SELECT
data only when it's not already there. Flush all caches on commit.

All of this gives great results. Batch run time went down from 20+ minutes to
a few seconds. Most weird crashes disappear. The need for manual data fixup
after crashes evaporates. And then it hits me: I just wrote a custom ORM.

------
philwelch
The ORM discussion reminds me of something Rich Hickey said in his talk,
“Simple Made Easy”. He distinguished between “simple” (when a system is
inherently low in complexity) and “easy” (when a system is made more complex
so it is theoretically easier to use).

ORM’s are easy, but not simple. If your system uses a relational database but
not an ORM, you have to understand your particular database and also SQL to
understand your data layer. If you add an ORM, you aren’t actually saved from
having to understand those things, you just also have to understand your ORM
on top of all that. There is some positive tradeoff you get in return, since
you don’t necessarily have to grapple with the added complexity all of the
time.

The main saving grace of ORM (or at least query builders) seems to be that the
alternatives aren’t well-supported in tooling, so the “raw SQL” alternatives
often end up implemented as string concatenation hell, which is admittedly
terrible. Installing parameterized SQL on the DB itself in stored procedures
works great, but you have to go out of your way to do it; it never really
comes across as a plug-and-play option even though in principle it easily
could be.

~~~
idoubtit
> ORM's are easy but not simple.

ORM libraries may not be simple, but what matters is the code that uses them.

My experience was that the main code was often easier and simpler. Easier
because small tasks are made easy. Simpler because it makes the code more
consistent: for instance, once you know the ORM, you don't have to deal with
hundreds of specific cases that insert or update various records.

Promoting stored procedures as an alternative to the complexity of an ORM
seems strange to me. They still have to be carefully written like any raw SQL.
There is no canonical way to keep them in sync with the code. They are vendor-
specific. As far as I know, you need to learn specific tools to debug them or
to analyze their performance.

~~~
yawaramin
> Simpler because it makes the code more consistent: for instance, once you
> know the ORM, you don't have to deal with hundreds of specific cases that
> insert or update various records.

That's not what 'simpler' means in this context. Simpler means conceptually
independent things aren't coupled together. For example, ORMs couple together
object-oriented code and relational objects. That's the essential complexity
that they introduce.

> There is no canonical way to keep them in sync with the code.

There's still the old-fashioned way of checking them into the repo as part of
migrations.

> They are vendor-specific.

Most people will almost certainly stay with a single vendor anyway. If you
never take advantage of your platform for fear that you'll migrate away from
it, you're throwing away a lot of potential benefits for some perceived future
risk.

> As far as I know, you need to learn specific tools to debug them or to
> analyze their performance.

No more than you would with an ORM–in fact, with an ORM you'd need to learn
two different stacks to tune performance or troubleshoot.

~~~
philwelch
Pretty much agreed. Two things to add:

> Simpler means conceptually independent things aren't coupled together. For
> example, ORMs couple together object-oriented code and relational objects.
> That's the essential complexity that they introduce.

I don't actually agree that this is where the complexity comes from. The
complexity comes from what makes ORM so appealing in the first place. Your ORM
can dynamically generate an infinite variety of queries based on how you use
it, which makes it a big piece of machinery with many moving parts, the
behavior of which you will have to understand and manage even if you didn't
build it yourself. This machinery may break, it may behave in undesired or
unpredicted ways, it may consume compute resources inefficiently, and if it
does any of those things, you're still on the hook to take care of it because
it affects the behavior of your product.

Ultimately, you will always have to map some behavior in your service code to
an intended SQL query and then map the result set back to in-memory data.
Doing so in a consistent way can potentially _reduce_ complexity, though in a
lot of cases, you can probably get away with treating a SQL result set as a
list of associative arrays, or even a list of objects as long as you tell it
what class to deserialize into (which is what many ORMs do anyway).

> There's still the old-fashioned way of checking them into the repo as part
> of migrations.

Yes, even many ORM-based services have DB migrations in the service repo
already.[1]

You could also version-control the stored procedures, database migrations,
etc. as its own software artifact. You can think of a relational database as a
service (microservice?) that speaks SQL instead of HTTP or GraphQL or GRPC.
It's listening to a port somewhere on your network, it consumes computational
resources, it will be deployed independently of your service in such a way
that you have to worry about backwards compatibility, it can become
unavailable to your service, and so forth. And like most services, you may
prefer to have a defined, optimized, versioned interface to your DB instead of
just trusting your consuming services to execute arbitrary (SQL) code. This is
not necessarily the right or wrong solution for you, but it's reasonable
enough.

[1] One possible niche might be an ORM that builds all of your DB manipulation
commands into parameterized SQL at compile time and installs those SQL
statements as stored procedures via the migration mechanism. Then you really
can write raw SQL when you need to, by hardcoding your stored procedure
instead of compiling it. This would work best on a DB that you could migrate
whenever you felt like it, but I've heard Postgres is one of those.

~~~
yawaramin
> You can think of a relational database as a service (microservice?) that
> speaks SQL

Exactly. As a colleague of mine once said: stored procedures are the original
microservices. A bit tongue-in-cheek, but the point is you can treat stored
procs as these lightweight services. You don't even have to write the SQL,
just call the procs with the right arguments. That's one scenario, anyway.

------
ordinaryperson
Sample size one 1 but ORM drives me crazy. Why can't we just use SQL? How does
it save time when I have to learn the ORM language, which probably has a lot
less support and users?

The OP here says it "reduces boilerplate" \-- rarely have I created an
application and thought its biggest problem was too much boilerplate.

But everyone at work loves them so I must be wrong somehow.

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

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

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

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

~~~
ethbro
I look at this as not playing to core competencies.

SQL's strength is that it's declarative.

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

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

------
rspeele
I put a ton of work into an ORM that statically typed SQL a couple years ago.
I always thought this would be a cool way to go.

[https://github.com/rspeele/Rezoom.SQL/](https://github.com/rspeele/Rezoom.SQL/)

But I never got to use it at work, and thus lost interest. The biggest thing
missing with it was that you lost type safety if you had to dynamically build
a query. These days its main problem is lack of compatibility with .NET Core,
which somebody else was working on for a while.

I think the flat row model for querying data from related tables is just not
very good. I'd MUCH rather work with an object model like LINQ, where I get a
list of Foos and each one has foo.Bars nested within it. As opposed to the
output of a join where I get one row for each Bar, and parent Foo's columns
are duplicated across each row.

Entity Framework does an excellent job of mapping the LINQ model to the SQL
one, but since they really are different under the covers, it's easy to
produce hefty queries. For example a LINQ "group by" frequently cannot be
translated to a SQL "group by".

I tried to keep it simple and not deviate too far from straight SQL. But I
still didn't want to force processing those damn row-based outputs onto the
programmer, so I at least had to add notation for breaking out the columns of
a top-level result set into an object hierarchy:

[https://rspeele.gitbooks.io/rezoom-
sql/doc/Language/Navigati...](https://rspeele.gitbooks.io/rezoom-
sql/doc/Language/NavigationProperties.html)

~~~
Risord
Great work. I used rezoom in couple of minor projects and like most that I
could really trust that query runs if it compiles. Unlike in Entity Framework
where you have unlimited possiblities write code which compiles just fine but
crash runtime. In principle I don't like an idea that your primary language
get compiled to SQL due it's very leaky abstraction. Instead I like expressing
queries as data (ideally compile time checked).

In rezoom query composition part wasn't the strongest part. When for example
some model needed additional join you had to modify N queries. This kind of
composition could be done of course with dynamics but like you say, you lose
biggest selling point: compile-time safety.

However these compositions are usually all known at compile time so you should
be able to just build them before rezoom checks them. But out of the box
compile time programming in F# is not there yet...

~~~
rspeele
Thanks. I agree composition was a pain point. I had various ideas to make
things better, like adding support for "erased" views/functions/TVFs that
would be inlined at compile time, but it always felt like it'd be hacky and
still not solve enough problems.

Type providers are such a cool language feature, but the way developing one
works is too damn confusing. Especially when you try to publish one as an
easy-to-use package and simple stuff like loading dependencies feels like
uncharted territory. When it comes to my precious free time I hate, hate, hate
figuring out packaging/deployment type stuff, I just want to focus on my code.
So that's a big part of why I haven't done a great job maintaining it.

~~~
Risord
I feel your pain although I haven't ever implemented type provider to be
precise. You have no reason to feel guilty! It's out and it's runs without any
major bugs(!). Hopefully community will carry it and at very least it's one
nice show case for compile time computing of F# (and maybe add motivation to
develop that side of language further).

------
localhostdotdev

        rails g model Post published_at:datetime title content:text
        rails g model Comment post:references author published_at:datetime content:text
        rails g model Tag name
        rails g model PostTag post:references tag:references
    
        class Post < ApplicationRecord
          has_many :comments
          has_many :post_tags
          has_many :tags, through: :post_tags
        end
    
        class Tag < ApplicationRecord
          has_many :post_tags
          has_many :posts, through: :post_tags
        end
    

from there it's just regular rails:

    
    
        Tag.find_by(name: "something").posts
        Post.joins(:tags).where(tags: { name: "something" })
        Tag.create(name: "something")
        Post.create(...)
        Tag.first.posts << Post.all.sample(2)
    

made a little repo if people want to play with it:
[https://github.com/localhostdotdev/bug/tree/orm-or-not-
orm](https://github.com/localhostdotdev/bug/tree/orm-or-not-orm)

~~~
davidcuddeback
You forgot to add an index on tags.name and null constraints on the rest of
the columns.

> _from there it 's just regular rails_

That's the problem. Examples like this focus on the first few minutes of
development. Not the subsequent years of maintenance.

~~~
vinceguidry
> Not the subsequent years of maintenance.

You're gonna have to pry the Rails from my cold dead fingers if you want me to
maintain a web app's database abstraction layer long-term. No other tool works
half as well as ActiveRecord. You could _maybe_ convince me to try out
[https://rom-rb.org/](https://rom-rb.org/) but only on a brand new project and
only if Rails isn't appropriate.

If it's a project that wasn't built with Rails, I'm going to want
[https://github.com/jeremyevans/sequel](https://github.com/jeremyevans/sequel)
if ActiveRecord is too much trouble to introduce.

~~~
oogway8020
I have been lucky to be able to pick what I want to use, so I pick non-rails
stack (Sinatra/Padrino and now Roda + Sequel). No other tool works as half as
Sequel. You're gonna have to pry Sequel from my cold dead fingers.

~~~
vinceguidry
Don't get me wrong, I love Sequel. But ActiveRecord is far more polished. And
that starts to matter once you start needing to go 'off-script'.

------
ilovetux
I like the Django ORM. This is likely for two reasons:

1\. I only use Django for small use cases where I rarely see any sort of scope
creep. There was no real conscious decision about this, just kind of the way
it happens.

2\. The Django ORM is fairly mature and makes it quite easy to get a small
project out the door.

I regularly use SQL directly at work and wouldn't want to try to replace any
of it with an ORM even on new projects because we are all just used to working
with it and we wouldn't want to add any complexity to our workflow without
real, tangible benefits.

I would not, however, be opposed to working on a larger Django project (ORM
and all) if the opportunity presented itself.

~~~
marcosdumay
For me, the best feature of the Django ORM is that it integrates with
everything all the way to the frontend forms.

In fact, missing the database integration wouldn't even be that large loss.
Sometimes I wonder if it isn't even holding the framework back.

~~~
ilovetux
I agree the ModelForm class is great.

------
0x70dd
I've wanted to like ORMs but they always get in the away as requirements start
to get more complex. On the other hand, I like writing in plain SQL, but
that's often hard to comprehend by other people - you cannot just glance
through to know what's happening under hood.

I find Ecto to be the perfect balance of expressiveness, flexibility and
clarity. In fact it's the single reason to have picked up Elixir.

~~~
jtfairbank
Have to agree. been really enjoying Elixir / Ecto / Phoenix.

I find Ecto lets me be as expressive as I need to with Queries, and handles
the mapping into models very well for the 90% of easy cases that I need it to.

------
kasey_junk
One thing I really miss from the JVM world was something like jOOQ.

I don't want an ORM (like specifically the object relational mapping stuff)
for most of my use cases but I do want an abstraction above text for
interacting with SQL.

gorm's sql builder is alright but something better would be really nice.

~~~
cryptonector
I don't even want an abstraction for building SQL unless I need to build
queries dynamically from some other (presumably simpler) query language.

Put all the SQL in .sql files and make all queries parametrized. Or wrap all
queries in VIEWs or functions at the RDBMS. This makes maintenance much
easier.

~~~
dragonwriter
> Or wrap all queries in VIEWs or functions at the RDBMS.

This is like the oldest best practice for RDBMS use, for security, and for
decoupling consuming apps from each other and the DBs low level implementation
(so that app views and base tables can evolve independently to the extent
possible), and for maintainability: all app access to the DB should be through
views adapted to the apps needs.

~~~
cryptonector
Exactly. And it works very well.

I recommend PostgREST to export a RESTful interface to a single PG schema's
VIEWs and functions, and RLS and INSTEAD OF triggers as needed.

------
victor106
From the creator of Hibernate Gavin King

"Just because you're using Hibernate, doesn't mean you have to use it for
everything.”

[https://mobile.twitter.com/javaooq/status/504184043765002240](https://mobile.twitter.com/javaooq/status/504184043765002240)

This is how I use it 1\. Use Hibernate for most updates. 2\. Use JOOQ for
reads. Gives you everything from compiler checks to type safety.

ORM is a tool. Just like any other tool in CS or non-CS you have to know when
to use it and when not to use it and what other options exist.

------
jandrewrogers
I don't have any issue with ORMs in principle, and even wrote an ORM once.
However, in almost every place I've seen them used they've become a way for
developers to avoid understanding how databases work, inevitably leading to
inexplicable data models and poor performance. In practice, ORMs tend to end
up creating crippling technical debt that is difficult to fix.

If ORMs were typically used by developers that fully understood the
implications for the underlying database, there would be few issues with them
and they would be a valuable tool. Unfortunately, they seem to be most popular
with developers that are the opposite of that description.

~~~
Daishiman
Frankly that says more about the people you've worked with than the underlying
technology.

I work with devs that have over a decade of Django experience. We use the ORM
because it's just ridiculously easier to write queries on it and because SQL
is impossible to compose without substantial problems.

90% of the code we write is CRUD and API endpoints. There's no reason to write
SQL by hand except for the complex aggregations that comprise 5% of our
queries, with luck.

~~~
bpyne
"and because SQL is impossible to compose without substantial problems."

Would you mind providing an example of what you mean?

~~~
marcosdumay
A few features that I miss on SQL that hinder composition:

\- A good module system

\- View-like variables

\- Scoped singletons (related to the module system)

\- First class functions

\- First class symbols (that can be used as object names)

\- Composable queries (related to the view-like variables and first class
symbols)

If I wasn't on vacation I would have a few more fresh on memory. Different
ones each day.

~~~
bpyne
Ah, I see what you mean. I was thinking in terms of union'ing and the like
where you can use set theory to compose sets of information from various
queries.

~~~
marcosdumay
Well, I'm not the OP. AFAIK, he may be thinking the same as you.

SQL lacks some power on negative and consolidated joins, forcing one to write
more complex queries than necessary. It is this way for good reasons, because
those are exactly the kinds of joins that indexes help you least and that most
hinder parallelism, so they should be avoided if possible. On my experience,
it's not a large drawback, but YMMV.

------
hugi
I believe that People who hate ORMs haven't used a good ORM, so what they
really hate is the tool, not the concept. I like using my ORM of choice when
it's appropriate, I like just using SQL statements when it's appropriate.
Choose the tool appropriate for the task.

~~~
strictfp
I've used many ORMs. I don't hate them, but I think their net utility is
negative.

While you initially might think that interfacing with the db is going to be
very tedious and labor intensive, it usually turns out to not be that bad.

Battling the ORM to make it do what you want can on the other hand be very
tedious.

When you have the SQL in the code it's much more obvious what the performance
profile and potential concurrency problems are. That's very important to solve
real problems which arise in almost all projects with some scale.

If I were to choose my tools, I would pick some sql-based schema versioning
system, and potentially a simple data mapper for moving data back and forth
between db records and entities.

But I would be very reluctant to use a full-blown ORM nowadays.

~~~
hugi
I tend to disagree. To me a good ORM totally (ok, not totally, but as totally
as possible) abstracts the database away from your code. That's it's purpose.
You just want to work with data. You want to fetch data, persist data and
delete data - you don't really want to care about how that's done. That's
where a good ORM can really help you out.

However, if the ORM gets in your way, either because of API complexity,
performance issues or other reasons, and you start to "battle" it as you said,
you immediately move out of that context and start thinking in a more data
oriented context. That's where SQL comes in.

As I said before, I think it's a question of using the appropriate tool. I've
written hundreds of applications using multiple different ORMs and just plain
SQL—and I'll tell you I'll pick a good ORM over raw SQL for basic CRUD
operations every time.

However, I'll give you that I'll pick plain SQL over a bad ORM any day.

------
slaymaker1907
An ORM is a technology that has the problem of trying to make easy things
easier (CRUD) while making difficult things more difficult (complex joins, SQL
lock management, indices, etc.). Why spend so much $$$$$ on a fancy database
if you are going to make 70-90% of its features impossible to use?

~~~
Johnny555
_Why spend so much $$$$$ on a fancy database_

You need to answer that question first. What feature do you really need from
that fancy database?

~~~
slaymaker1907
Fine grained control over locks, transaction levels, JSON indexing, etc. In
fact, some databases (older versions of Postgres, Oracle, etc.) do not
actually have true serializability so explicit locks may be the only way to
implement certain logic correctly. I picked on explicit locks since it is
quite useful as well as being generally poorly supported by ORMs.

------
Grollicus
I'm surprised with so many mentions of Django nobody mentioned the migration
system.

I've hand-written sql (the good old days of mysql_real_escape_string) and I've
used some ORMs.

Django stands out because of their code first-approach for models: You define
models in Python and they generate migrations based on that.

That makes updating very easy and also quite robust, as a simple
makemigrations call in the CI ensures your DB is not out of sync.

~~~
dinkleberg
I'm in total agreement with you. I've tried different ORMs in different
languages and I am always disappointed in how lackluster they feel in
comparison to the Django ORM.

There are several things that bother me about using Django, but because of the
ORM and it's tight integration with the rest of the system I don't think there
is a more productive environment for me.

That being said I'm not a great developer. As a business-minded person who
codes out of necessity, I find the Django tooling to be the right way to work
most of the time.

Not having to deal with migrations is a thing of beauty.

------
mekane8
Learning and using an ORM is not an excuse for not learning and knowing SQL +
Database concepts. (I'm not saying the author argued that, I just want to
sweep away an straw men that say not having to learn SQL is an argument in
favor of using an ORM).

For me the biggest benefit of using an ORM is separation of concerns. I truly
hate it when SQL and query building get mixed into controllers and other parts
of the application. However, I also hate the layered complexity that the
author talked about, which definitely means ORM's are not appropriate for all
use cases! But if you don't use an ORM you need to be diligent with using some
other pattern and code organization to keep your code clean and your concerns
separated.

I worked at a consulting company for a number of years and we got immense
value out of Symfony, which includes the Doctrine ORM. Many of our projects
boiled down to custom Content Management Systems, so we got the huge benefit
of being able to define the data model in code and not worry about any of the
SQL while also not having very complex queries. So we got pretty much maximum
benefit and minimal exposure to the down-sides.

I will say that these systems almost always wanted some detailed reporting,
and that I usually got tasked with writing them. They always had some
complicated queries and I almost always made use of the "raw sql" option to
just write my own queries and spit out CSV. Which was always a much different
use case than the rest of the application.

------
halis
I was pretty excited when I originally learned about ORMs. But I quickly found
that their usefulness is very limited in the enterprise, where most of my
career has been spent.

You will likely find, as I did, that enterprises literally have thousands of
databases of all kinds and many crazy data models.

They have tomes of huge SQL procedures that are still to this day running
their business processes. Maybe even a nice older gent that is the only one
that really understands how any of that shit works?

If this sounds familiar to you, then don't waste too much time on ORMs. In my
experience, they're only good for mapping out your POJO or POCO objects and
performing simple CRUD operations.

Use them to stand up simple REST routes that do CRUD on your entities. That
can be the API that you use to do development and testing, and possibly even
use those CRUD routes to do larger business operations.

But in reality, the business "requirement" is going to come down to you, the
developer, in the form of some old terrible blob of shit SQL. Two thousand
lines. Ten thousand lines.

You're probably not going to bother deciphering all that and re-writing it.
The ORM is going to barf all over itself if you even try to use its API to
execute that query. Even if you could express all those joins and case
statements via the ORM, it is going to pick an execution plan where the query
finishes sometime after the next Hanukkah.

At this point, don't bother with the ORM. Drop down to some driver where you
can just run raw SQL and run the raw SQL.

Create a REST route that just calls this whack query directly.

This all may sound cynical and it is. But I know what I'm talking about. Do
yourself a favor and remember what I said.

~~~
imtringued
I have written several queries that span hundreds of lines, primarily for
"advanced" search functionality that lets you search by a huge array of
potential parameters. Without using the query builder provided by the ORM the
code would become intractable to mortals quickly. Having several dozen
different parameters that are only set under certain special conditions is
much easier if you can use your regular programming language to evaluate those
conditions as opposed to writing everything in one big SQL statement even with
named parameters.

Writing code like this here really gets old:

q = query(SELECT a FROM table WHERE (:PARAMETER_1_DISABLED OR table.field <
:PARAMETER_1), ... 20 other parameters)

q.setParameter(PARAMETER_1_DISABLED, condition)

q.setParameter(PARAMETER_1, parameter)

.. 20 other parameters

compared to just

Type.list() {

    
    
      if(condition) {
    
        field < parameter
    
      }
    
      .. 20 other parameters
    

}

Also please don't tell me to avoid this problem by concatenating the SQL
conditions to dynamically create the query... The end result is still ugly and
might even introduce security problems.

------
ccarse
I want to write sql but have my library map between my languages built-in
types and my sql types. I feel like something like Dapper is the correct
abstraction for communicating with a database.

------
pezo1919
My favourite orm is Peewee for python.

I really like how clean it is.

grandma = Person.get(Person.name == 'Grandma L.')

or

query = Pet.select().where(Pet.animal_type == 'cat')

for pet in query:

    
    
      print(pet.name, pet.owner.name)
    

I am looking for something similar for javascript and lately for Typescript,
but I could not find anything with similar readibility and logic.

Do you have any idea?

[http://docs.peewee-
orm.com/en/latest/peewee/quickstart.html#...](http://docs.peewee-
orm.com/en/latest/peewee/quickstart.html#quickstart)

~~~
ketzo
I really like Sequelize after using it in a couple of small projects. Not
_quite_ that level of concise, but I find it to be a really nice balance of
concision and flexibility.

Person.findOne({where: {name: "Grandma L."}})

Person.findAll({where: {age: 30}})

Particularly, I really like how associations are handled. If I have a one-to-
many association between Posts and Comments, I can do something like this:

Post.findOne({where: {title: "My Article}})

    
    
      .then( article => {
    
        article.getComments()
    
          .then( comments => {
    
          // do something with all comments of Post "My Article"
    
          });
    
      });
    

[http://docs.sequelizejs.com/manual/](http://docs.sequelizejs.com/manual/)

------
bkq
Personally I think an active record style ORM for Go like gorm is a poor fit
for a language that doesn't come across as inherently OOP. Going through some
of the documentation for gorm, it seems to rely heavily on method chaining
which for Go seems wrong considering how errors are handled in that language.
In my opinion, an ORM should be as idiomatic to the language as possible.

I've used sqlx[1] before, and it feels pretty idiomatic to Go. You tag your
structs with their respective database columns, write up a query, and hand it
to sqlx to perform the deserialisation of the data. I've also come across
squirrel[2] too, though I haven't used it, it does look rather interesting.

[1] - [https://github.com/jmoiron/sqlx](https://github.com/jmoiron/sqlx)

[2] -
[https://github.com/masterminds/squirrel](https://github.com/masterminds/squirrel)

~~~
gen220
For my 2¢, We use squirrel at work (I haven't touched it myself though).
People here seem to like it.

It's not an ORM per se, but it seems to occupy the sweet spot you're
describing. It takes away some of the more tedious parts of using SQL, but
allows you to still reason about what's happening under the hood without
committing tons of documentation to memory.

Suits the language quite well, I think!

------
beders
Just say no to ORM. Apart from the usual problems with impedance mismatch,
there's one thing that is rarely talked about: Waste.

Most ORMs work like that: You get a request, you start a transaction, you
begin instantiating lots of objects because you need those to do anything
useful.

You then either serialize parts of that object tree into JSON or whatever, or
you change a few objects, which then create UPSERT statements, the transaction
commits (or not) and then...you are throwing it all away again!

You might save some state in some second level cache, but other than that, you
just created a partially populated object graph, probably loaded way too much
stuff, and then you do that AGAIN for the next transaction.

I've used JDO, Hibernate etc. before and nowadays it feels like extremely
wasteful with limited productivity gains that quickly disappear if your
objects become more complex.

~~~
asdfman123
We should band together and form the NoORM movement.

------
electrotype
My opinion is to stick to plain SQL, but to use tools around if you like:
query builders, transactions utilities, mappers.

But no ORM for me, thank you. I have already given.

~~~
tabtab
I tend to agree, but in static languages, reflection engines are usually
needed to automate or simplify mapping, and reflection can get messy. Static
languages are not designed for CRUD, in my opinion (without a revolution in
frameworks, at least). Look at the square-bracket "annotations" or
"attributes" in C#. Isn't that kind of info that classes or object instances
were supposed to define, such as fieldx.maxLength=30;? Square brackets are a
kludge.

------
vbsteven
Both... I prefer a hybrid approach when it comes to ORM's. In most projects I
will use an ORM to avoid boilerplate for simple entity queries (save,
findByid, findByX, delete) and converting a database row to a class instance
but I avoid mapping complex relations.

An ORM like Spring Data with JPA/Hibernate in the Java/Kotlin world works well
for this. I write my db schema by hand, create an entity class with the same
fields and an empty repository interface extending CrudRepository. This gives
me simple CRUD access to the table with almost no code.

When I need complex queries I inject a JdbcRepository which allows me to query
the DB using standard SQL and a RowMapper lambda.

Best of both worlds.

~~~
mindcrime
Yep, the Spring Data CrudRepository interface based stuff is an amazing time
saver. Just extend an interface, and BOOM, you've got the basic CRUD
operations ready to go. And then you just use HQL in annotations for more
specialized queries. Other people's mileage may vary, but I've found this to
be a tremendous boon.

~~~
vbsteven
And there is more:

* you don't have to use HQL, if you pass `nativeQuery=true` in the @Query annotations you can write standard SQL instead of HQL.

* You can add an AccountRepositoryImpl Bean so you have a class in which you can inject dependencies like JdbdTemplate for full JDBC database access.

------
rawoke083600
Apart from the well thought out and "proper arguments" like "You don't know
what queries it does underneath...". Its usually the "insane-funny-sub-
language" you have to learn to work the said ORM which only works for 50% of
the queries before you really have to get weird with it.

My not correct and sub optimal solution over the years is to have some sort of
"god-class" that has a big collection of sql-related queries with some query-
reuse(error checking etc) like "student_insert_update(namedList,namedValues)"

or "student_get_all_subjects(student_id,semester)"

I know its not perfect :/

------
he0001
I think the most common problem with ORMs is that people doesn’t know when to
use the ORMs functionality or when to write their own query. This always leads
to N+1 problems and unmaintainable code, because when you eventually finds
those problems it’s usually quick fixed because there’s no time for fixing all
of it. It leads to ad hoc code for the next guy trying to change. This is
generally not the ORMs fault, but always the programmer and that they don’t
understand those limitations. Just replacing the same code with “raw” sql
would produce the same thing but you need to write it all instead.

------
fareesh
It's nice to have an interface that runs my query and puts the results into a
nice typed structure.

If the query itself is an object, I can chain it with other query objects and
have a cleaner syntax.

Those are my usual reasons for liking ORMs.

------
sandreas
Another advantage that i've come across and that has not been mentioned so far
is, that an ORM can help you a LOT, when implementing role based access
control (RBAC).

Mapping the objects and having all the metadata had huge advantages over pure
SQL, if convention over configuration is used to control access to the given
objects with a User/Role Entity - and object inheritance gives you the ability
to add a "created_by", "modified_by" to each table for controlling even access
to the row level...

------
philjackson
My preference has been this sort of tool:
[https://github.com/krisajenkins/yesql](https://github.com/krisajenkins/yesql)

~~~
dkersten
Or HugSQL, which is similar, but actively maintained. Coupled with Clojure's
data-centric focus, there's no need for a traditional ORM and you get to work
directly in SQL. Yesql and Hugsql made me enjoy using databases for the first
time ever.

------
gremlinsinc
I like to mix orm/non-orm.. for anything involving multiple joins and more
complex stuff I'll just write raw sql...

Using laravel as an example I'll usually make a $sql var to hold, then just
run DB::select(DB::raw($sql))); It still keeps things nice and succinct, I let
DB handle some of the setup/connection boilerplate, and I just figure out the
sql myself, but for 80% of the time I rely on Eloquent..

The benefit of relying on Eloquent is I tend to like to 'tie' into the event
system, so for example you have to explicitly be aware when adding/deleting a
user that might have other related tables created at the same time as 'setup',
you can tap into the 'creating/created/saving/saved/etc' events and when a new
user is 'created' you could create a profile for example.

For me I think the killer feature is keeping an event cycle when it matters,
sure you can explicitly remember to update/map everything when you code your
sql, but is everyone on the team going to do that? If you for example update
profile>fullname whenever user->firstname or user->lastname is updated, it's
easy using an orm. not so much when 15 different devs are updating things in
different places in different ways using sql only. Everytime ->save or
->create( is called it handles the events you put in play. (This is just an
example, I'd probably just do a setter or getter to grab the first/lastname
and combine them instead of having duplicate data, but again this would only
work if you're using eloquent to grab the data in the first place, because
it's model related)

------
nikolasburk
What a great article, this paragraph really reasonated with me:

> Any situation where complex functionality is wrapped in another layer runs
> the risk of increasing the overall complexity when the wrapping layer is
> itself complicated. This often comes along with leaky abstractions - wherin
> the wrapping layer can't do a perfect job wrapping the underlying
> functionality, and forces programmers to fight with both layers
> simultaneously.

I think we can all agree that a mapping layer from a relational DB to your
language is needed in almost every application, so the question is whether to
build it yourself or use an off-the-shelf solution.

I work at prisma.io and we've set out to make database workflows as easy as
possible for developers, this includes database access (= ORM) and schema
migrations.

With polyglot persistence becoming a standard pattern in modern applications,
today's ORMs fall short since they only map from relational databases. Our
goal at Prisma is to provide a _type-safe_ mapping layer to all your databases
at once. This will allow developers to focus on their application logic and
not worry about how to access the data from the various data sources.

------
pnathan
One of the classic bikesheds.

I've wound up writing a low key system in, at the time, Scala, that did
something to the effect of:

    
    
        class Select {
          fields: Either[List[String], All] 
          table: String
          wheres: List[Clauses]
          orderBy: Option[String]
    
          def render(): String
        }
    
    

The render() method generated SQL; the Select class allowed a _fairly typed_
input; with some evolution, you can get shared queries quite nicely.

This works _reasonably well_. Part of _why_ I went this route is that I tend
to use Postgres in a very serious way: it's not just a fancy set of
spreadsheets with FK linking: I have indexes, check constraints, enums, pg-
specific types, triggers, etc. So being able to directly interact with the
database gives you a rich control surface that ORMs tend to exclude you from.

By the way, this was an evolution in concept from a different database
oriented system I wrote in Python. Both systems used Postgres rich types, the
class/render system was implemented in Scala and was Very Nice.

------
syntheticcdo
Somewhere in the middle is best for me. Not quite ORM, not quite raw SQL, but
a query builder.

In the Node world knexjs.org fits the bill.

~~~
frosted-flakes
I agree with that sentiment. Concatenating strings is miserable and error-
prone, especially for highly dynamic queries.

But there's still another step between a full-blown ORM and a query builder,
where you still define the models and the relations between them, but fall
back to a plain query builder for constructing queries. That way, you're not
dealing with plain arrays, etc., but you're still essentially writing SQL.
Like Objection.js[0], which is built on Knex.

[0]
[https://vincit.github.io/objection.js/](https://vincit.github.io/objection.js/)

~~~
simplify
What's wrong with working with plain arrays? Genuine question, they seem to
work fine for me so far.

------
keithnz
In F#, you get type providers like

[https://fsprojects.github.io/SQLProvider/](https://fsprojects.github.io/SQLProvider/)

or

[https://fsprojects.github.io/FSharp.Data.SqlClient/](https://fsprojects.github.io/FSharp.Data.SqlClient/)

What is not obvious if you have never seen this before, is that this
dynamically connects your database WHILE you are coding and automatically
gives you auto completion, fully understands the types, and will give compile
errors if your code doesn't match the database. Feels like Magic (or LinqPad
if you have ever used that )

The only downside is you need a DB available for your CI build tools ( which
is not too difficult to do ). Some F# people will also opt for a light weight
mapper like Dapper.

------
talkingtab
The number one reason to use SQL is not a reason not to use an ORM. SQL is
extremely powerful - it can make your data sit up, roll over, play dead and
even speak. Learning how to harness that power is worth while. So maybe use an
ORM now, while you are learning SQL.

~~~
thymanl23
Or use a "pure orm" (mapping to plain business objects) without a query
builder - thus writing native sql and receiving nicely structured objects from
the results (eg [https://github.com/craigmichaelmartin/pure-
orm](https://github.com/craigmichaelmartin/pure-orm))

------
cjdell
I fell out with a lot of the popular ORMs and decided to create a very
lightweight query library with the features I really wanted, namely an
ENTIRELY statically typed query interface.

Link to fully working sample: [https://github.com/cjdell/ts-
sql](https://github.com/cjdell/ts-sql)

The idea is that it should be impossible to write an invalid query, and also
to ensure the returning data type is exactly representative of what has been
queried.

Currently using knex.js to actually generate the SQL behind the scenes. It's
experimental but I'm already using it in a couple of projects. I enjoy the
fact the my queries have so little boilerplate now. :-)

------
taffer
For a toy project I used Postgres to generate JSON objects for me and a helper
function in ruby (db_helper) that parses the JSON into ruby data structures so
that the OP example can be written with much less boilerplate code as:

    
    
      def db_all_posts_in_tag(tag_id)
        db_helper("
          SELECT json_agg(r)
            FROM (
                  SELECT post.post_id, post.published, post.title, post.content
                    FROM post
                         INNER JOIN post_tag ON post.post_id = post_tag.post_id
                   WHERE post_tag.tag_id = $1
                 ) AS r;
        ", [tag_id])
      end

------
lowbloodsugar
The ORM discussion is really just a proxy for "Have you had lots of experience
managing data in production" (in which case against ORM), and also "Have you
had lots of experience managing data with a team of green engineers" (who most
all want to use ORM because it's cool, but also, hell no).

Now, about the only way for a developer to understand how awful they are is to
really spend some time going all out with them. So my advice would be to
embrace ORMs whole heartedly, as soon in your career as possible. As you debug
it, you'll learn lots of wonderful things.

------
thymanl23
The desire for the object mapping (to pure objects, not db-aware objects),
while having an aversion to query building APIs led me to use
[https://github.com/craigmichaelmartin/pure-
orm](https://github.com/craigmichaelmartin/pure-orm) \- where native,
unobstructed SQL is written in a "data access layer", which returns pure
"business objects" to be used in the app's business layer.

------
alkonaut
I quite like the middle ground of micro orm. Gets rid of the trivial
boilerplate like populating fields from query results, and still offers any
custom queries.

------
34r45sdg
I am quite disappointed that both the article and the comments dont mention
application security. USE AN ORM. Unless you are a hotshot SQL dev who knows
the intricacies of every RDBMS, you should be delegating this to the ORM. Its
there to prevent you from blowing your foot off by introducing SQLi vectors.
Use an ORM, avoid SQLi.

~~~
abraae
Using an ORM to enforce security is like wearing your motorbike helmet down to
the store in case a bird shits on your head.

Yes, it will help prevent bad developers from introducing sql injection
vectors, but with a whole lot of extra baggage coming along for the ride.

And there are other application security scenarios involving database where
the ORM gives you nothing, such as always forcing a "tenant = " filter in a
SaaS scenario (which some database engines do support).

------
dvliman
I personally prefer tools like
[https://github.com/jkk/honeysql](https://github.com/jkk/honeysql)

It helps build queries programmatically but doesn't abstract / get too much in
your way.

On the "mapping" side, the resultset is just a map.

This is where languages that expose data-first shines

------
pjmlp
Not to ORM.

Something like myBatis, jOOQ or Dapper are more than good enough for the
boilerplate of building queries and data mapping.

No need to have needless generated SQL queries, with data going around the
pipe, when it should be processed at the server side, taking advantage of the
RDMS capabilities.

------
viach
When you are restricted with only 2 choices, probably they both suck, if
applied separately.

------
leowoo91
Liking to write SQL can easily make you think ORM is tend to have more
mistakes or become out-of-control, but in fact, you can fix certain
bottlenecks later and combine with raw queries without having to get non-ORM
overall.

------
ChicagoDave
You should decide on the type of data storage for a given domain and then
decide on the patterns required to support that domain. The danger of using an
ORM is that you can easily hide critical business functionality.

------
barking
Sorry for being off topic but is there an ORM or any other tool that can
rewrite messy SQL, ideally replacing right joins with left ones at the same
time? I have several hundred of these that I am afraid to touch.

------
audiometry
Is there a solution in Python/Postgres where I don't have to have ORM'd
tables, but that much of the boilerplate SQL CRUD-type things can be reduced?
Basically just beyond writing raw sql.

------
gigatexal
Go with an ORM when your schema is still in flux but transition to raw SQL
when you can the tech debt and performance overhead is just insane otherwise.

~~~
collyw
Only if you don't know how to use your ORM properly.

~~~
dkersten
In my experience, this is (almost) everyone I've ever worked with, in multiple
teams, with multiple ORM's, in multiple industries.

~~~
gigatexal
I’d love to hear any war stories.

~~~
dkersten
In the project I'm currently working on, objects are fetched from the database
using ORM queries and then the results are iterated over, filtered further and
additional queries are made to fetch more data. This is then all sent to the
frontend, where only about two or three of the couple dozen fields are
actually used.

I found this code because I was wanted to fix an issue where if you sort the
list in the UI, it only sorted the current page (because the sorting happened
in the frontend only). So I wanted to change the query so that sorting and
paging are both done in the database. What a rabbit-hole that was! I was able
to refactor the code to not over-fetch quite as badly, and to move the
filtering into the initial query (so only additional data fetching happens
after the initial query now, but it does still over-fetch fields and that's
not easy to change since this function is called by many endpoints and the set
of fields they all want varies). I had to log all of the queries so that I
could construct a new query and then convert that back to ORM code. It was a
very tedious task, but I managed to get the query from taking 9 seconds to
under a second.

In a previous project, I was tasked with optimising the database, because
requests were incredibly slow. The issue was that no care had been taken 1)
with indexes -- indexed fields were too large, and 2) with locking. The
locking was the big one as there were some requests that caused entire tables
or large amounts of rows to become locked, which in turn would prevent other
transactions from completing, so otherwise fast queries were now also slow,
which in turn... you get the idea. This was in a large well established
codebase, so it was not easy to fix and it still wasn't fully fixed when I
left that team.

Many of these issues could be avoided by considering the data and access
patterns when designing your database model (which doesn't have to be
identical to your application model -- database should IMHO be modeled around
query patterns and application model around domain features), with some care
taken for indexing and locking. You want to make sure that different users
usage patterns overlap as little as possible to avoid locking, that your
indexes are small, that your queries fetch only what you need (both in terms
of columns and rows).

None of these issues are the ORM's fault per se, but the ORM encourages
developers to think in terms of their application model and primary
programming languages OO facilities, so people (in my personal experience, at
least) tend to mix database queries and application logic too much (like what
I described above: pull data from DB, do some filtering that maybe should have
been in the query, do more queries that maybe or maybe not should have been
joins), tend to overfetch (often the ORM pulls in too many columns unless you
make it not do so, which often is not done), using indexes as an afterthought
and not considering locking at all. I think because ORM make it look like
"just some more application code", these things are often overlooked, while
with SQL queries, its a little more obvious that it's executed differently. I
would have said its down to junior developers or whatever who just haven't
learned to take care when writing queries, but I've only ever worked with two
people who actually did this and I can't believe that the rest of all of the
teams were just inexperienced.

I'm no database expert, but I've managed big wins with small amounts of care
and by understanding what queries I'm running.

PS: The book _SQL Performance Explained_ is quite good, in my opinion, and if
you use Postgres, then _Mastering PostgreSQL 11_ is great (and it starts off
talking about transactions and locking).

~~~
gigatexal
This fits my experience as well. Tracing the queries SQLAlchemy builds based
on the models of our Python application the DB is returning all of the columns
for every model instead of just the columns that will then be returned via the
API to the client. They’re not even just the queries to compute the relation
but all the columns from every relation involved and then at return time only
a small subset of columns is used. Its terrible for query perf as we almost
never do just index scans because we also have to pull data from the other
columns. It’s a waste. I’m not sure how to fix that just yet without breaking
the ease of use. The idea of being able to say model.select_one_or_none()...
or something similar I can easily tell is quick and painless and easy to train
about but doesn’t do the Adan justice. And then I hear the DB is slow! It’s a
battle I’m losing but I hope to help improve where I can.

~~~
dkersten
This sounds _exactly_ like my current project. The ORM is pulling all fields
for the model objects and related objects instead of the two or three it
actually needs and there’s no easy fix because too many unrelated features
make use of the same query code, but use different fields. I’ll slowly change
it to using multiple single purpose queries probably, but it’s going to be a
tedious task.

As an experiment, I changed one to only fetch the fields that it needs and the
query ran in approx. 15% of the time. That’s a pretty big improvement! Sigh.

------
rplst8
I find it amusing how many devs accept climate change science but deny the
inordinate amount of work done on SQL.

------
alexnewman
Everytime I show up at a company with flask-sqlalchemy, step #1 is to remove
it. Raw sqlalchemy is better, but we always end up with SQL. It's probably my
personal bias of writing 4 SQL parsers, but I find it better than the
alternative. Although it's very nice to have something which can safely map
the types

------
adrianlmm
To me is simple, to never ORM, it saves lots of wasted hours in the future.

------
kevin_thibedeau
I wouldn't have pegged Eli as the gormless type.

------
podliy16
Actually, what you have done are both ORM. So the question is "To ORM with an
external library or hand made ORM".

------
git-pull
I wrote an opinion on ORMs here in 2017:
[https://news.ycombinator.com/item?id=14661391](https://news.ycombinator.com/item?id=14661391)

Most of the stuff I said still sticks for me (after reading article, and after
using ORM this time).

I haven't programmed much golang. But with Python + Django I can use
`./manage.py shell_plus` and can drop immediately into a shell with history,
readline support, syntax highlighting, and tab completion, with all model
objects in local scope.

I can then do lookups and annotations (Yes, they're expensive, not for
production, but save lots of time during the day in a pinch):

Lookup:

    
    
        Library.objects.filter(books__author__name='Book author')
    

Annotation:

    
    
        Author.objects.annotate(total_books=Count('books')).filter(total_books__gt=5)
    

When the data model of a project gets bigger, writing queries by hand gets
harder to think about. Perhaps its due to my own overreliance on ORM's: but I
can't fathom how I'd manage without them. Because in practice the data models
are far more complex than above, and it'd take a _lot_ of time (not to mention
mental energy that could be put to use elsewhere)

Also, there are ways around performance problems: For one, it may entail
rejecting features offered by the ORM. Such as django's content types, multi-
table inheritance to instead use abstract inheritance (basically just reusing
common fields) and even plain one-to-one relations. They're just too
complicated and slow at scale.

Same goes for extending the ORM with stuff like django-polymorphic (and
django-model-utils). I've gotten nice performance out of django-polymorphic
(and cleaner code), but it's hiding a ton of metaprogramming, there's still a
penalty "upcasting" naive models, and they're very burdensome to maintain if
APIs fall out of date.

Next is trimming down queries with prefetching, only(), and doing direct ID
lookups. This prevents multiple queries from piling up by doing the join ahead
of time, only getting fields asked for, and doing the fastest lookup for
objects after a more expensive "filtering" query has ran.

Here's an example of the query earlier, with .only():

    
    
        Author.objects.annotate(total_books=Count('books')).filter(total_books__gt=5).only('id')
    

And to print the SQL query out:

    
    
        print(Author.objects.annotate(total_books=Count('books')).filter(total_books__gt=5).only('id').query)
    

That would then be used in something like:

    
    
        BookSet.objects.filter(book__in=Author.objects.annotate(total_books=Count('books')).filter(total_books__gt=5).values_list('id', flat=True))
    

And finally, for debugging certain types of query performance, django-debug-
toolbar is nice. If its API calls django-silk can "look back" on background
requests.

The article was fair IMO, but I'd wager ORM's payoff depends on the ecosystem.
Software being about tradeoffs: the convenience outweighs the downsides
everytime for me. Maybe I'm to be humbled and find I'm not getting the big
picture, and it could be just plain learnt-dependence, but it'd be a step back
in productivity for me not to have an ORM.

------
return1
Guys, stop commenting. It's a rhetorical question.

------
jp_sc
Relevant Keynote by DHH [https://www.youtube.com/watch?v=zKyv-
IGvgGE](https://www.youtube.com/watch?v=zKyv-IGvgGE) (tl;dr: ORMs are the
best)

------
dicroce
not. there that was simple.

------
lukifer
As true now as it was 12 years ago: ORMs are the Vietnam of computer science.
[http://blogs.tedneward.com/post/the-vietnam-of-computer-
scie...](http://blogs.tedneward.com/post/the-vietnam-of-computer-science/)

(tl;dr: there is an impedance mismatch / leaky abstraction between relational
data and OOP for all but the most trivial use cases, guaranteeing that
whatever problems an ORM might solve will be traded for new ORM problems
instead)

~~~
zzzeek
I wonder how people of Vietnamese heritage would feel that you are
characterizing their country as synonymous with war and disaster? IIUC Vietnam
had been a relatively peaceful country for almost 50 years.

Not coincidentally ORMs have been doing great since that silly article was
written so many years ago.

------
rodmena
Of course I will never use ORM again. Sqlalchemy made my life so miserable
that I felt I am the only stupid person in the entire planet. What ORM guys
don't think is that SQL IS NOT OBJECT ORIENTED. Why the hell you want to
pretend something completely fake?

The problem does not end here, Using ORM make you think object orient which
the worst corrosive idea since the dawn of computer science. It made you code
like spaghetti.

You read your code 90% of the time and you write code only 10% of the time,
that's why clean functional code will result to better software.

