
What ORMs have taught me: just learn SQL (2014) - BerislavLopac
https://wozniak.ca/blog/2014/08/03/What-ORMs-have-taught-me-just-learn-SQL/
======
JesseAldridge
This was my position for a while. ORMs introduce a layer of magic which
obscures what's actually going on under the hood. I decided I would just make
raw SQL queries and handle mapping data explicitly.

I quickly ended up with a lot of duplicated code. So then I thought, "Well ok,
I should add a bit of abstraction on top of this..." I started coding some
simple functions to help map the tabular data to objects. One thing led to
another and suddenly I looked at what I had done and said, "Wait a minute..."

~~~
kccqzy
There is a big difference between just writing helper functions to construct
SQL and convert data types, and OO-style magical auto-persisted objects. The
latter is what I don't like about ORMs but the former is fine. I feel that
this is an important distinction to make.

As an example, the sqlalchemy docs[0] make this very clear: there's an ORM,
but there's also just a core expression library that simply helps you connect
to the database and construct queries.

[0]: [https://docs.sqlalchemy.org/en/13/](https://docs.sqlalchemy.org/en/13/)

~~~
gmac
Agreed. Helpers (and indeed types) can make working with SQL an actual
pleasure. You do need to learn the SQL, though. (My TypeScript/Postgres
solution, in this vein: [https://github.com/jawj/mostly-
ormless/blob/master/README.md](https://github.com/jawj/mostly-
ormless/blob/master/README.md)).

~~~
timmy-turner
Wow this is great! Very well written README.

What just blew me away is the thing with the `JOIN` and the
`to_jsonb(authors)`, all with complete typing support for the nested author
object. I was actually looking to use a classical, attribute driven query
generator (with the sort of chaining API everyone is used to:
`tableName.select(...coumns)` etc.) for my next project involving to maybe
replace/wrap/rewrite a Rails app and its ORM with Typescript and Node. Maybe
I'm trying this instead I'm already half sold. Just worried about forcing
colleagues having to learn SQL instead of using a fancy wrapper.

~~~
kilburn
I'm also awed by this!

> Just worried about forcing colleagues having to learn SQL instead of using a
> fancy wrapper.

My current team is pretty junior, and I don't see any problem with this.
Simple SQL queries are really easy to learn, and complex queries are _harder_
to understand with ORMs than in raw SQL.

Moreover, knowing SQL is a useful, marketable skill that will stay relevant
for many years to come. If there's some resistance, I can easily convince the
team that going this route will benefit them personally.

Back to the README, there are two questions I'd like to see addressed:

1\. Whether `Selectable[]` can be used to query for a subset of fields and
how.

2\. In the `to_jsonb(authors)` example, what would you get back in the
`author` field if there were multiple authors with the same `author.id` value?
An array of `author.Selectable` objects? This part is awesome but brittle,
isn't it?

I would love to see this move forward! I will definitely play with it and
consider it for my next project.

~~~
gmac
_I 'm also awed by this!_

:)

 _1\. Whether `Selectable[]` can be used to query for a subset of fields and
how._

Right — this is not (currently) supported. I guess if you had wide tables of
large values, this could be an important optimisation, but it hasn't been a
need for me as yet.

 _2\. In the `to_jsonb(authors)` example, what would you get back in the
`author` field if there were multiple authors with the same `author.id` value?
An array of `author.Selectable` objects? This part is awesome but brittle, isn
't it?_

Multiple authors with the same id isn't going to happen, since id is intended
as a primary key, so I'd argue that the example as given isn't brittle. On the
other hand, there's a fair question about what happens for many-to-many joins,
and since my use case hasn't yet required this I haven't given it much
thought.

~~~
gmac
OK, I gave the one-to-many queries a bit more thought, and the converse join
query (getting each author with all their books, rather than all books each
with their author) works nicely with a GROUP BY:

    
    
        type authorBookSQL = s.authors.SQL | s.books.SQL;
        type authorBookSelectable = s.authors.Selectable & { books: s.books.Selectable };
    
        const
          query = db.sql<authorBookSQL>`
            SELECT ${"authors"}.*, jsonb_agg(${"books"}.*) AS ${"books"}
            FROM ${"books"} JOIN ${"authors"} 
              ON ${"authors"}.${"id"} = ${"books"}.${"authorId"}
            GROUP BY ${"authors"}.${"id"}`,
    
          authorBooks: authorBookSelectable[] = await query.run(db.pool);
    

This exploits the fact that selecting all fields is, logically enough,
permitted when grouping by primary key
([https://www.postgresql.org/docs/current/sql-
select.html#SQL-...](https://www.postgresql.org/docs/current/sql-
select.html#SQL-GROUPBY) and
[https://dba.stackexchange.com/questions/158015/why-can-i-
sel...](https://dba.stackexchange.com/questions/158015/why-can-i-select-all-
fields-when-grouping-by-primary-key-but-not-when-grouping-b))

I'll update demo.ts and README shortly.

------
725686
Each time I see someone complain about ORMs I remember Greenspun's tenth
rule[1], which adapted to ORM would be:

"Any sufficiently complicated program contains an ad-hoc, informally-
specified, bug-ridden, slow implementation of half of a decent ORM."

ORMs are hard for a reason. Using an ORM doesn't mean you can't or shouldn't
use plain SQL where the situation calls for it. You can mix and match
perfectly fine.

[1]
[https://en.wikipedia.org/wiki/Greenspun%27s_tenth_rule](https://en.wikipedia.org/wiki/Greenspun%27s_tenth_rule)
Edit: typo.

~~~
yawaramin
> ORMs are hard for a reason.

Yes, the object-relational impedance mismatch. It's the classic case of having
a hammer (OOP) and trying to make everything look like a nail.

~~~
delusional
I would actually let OOP off the hook here. I think what did the harm in this
case was the java generation. The generation of programmers that were told
that in the future they would only have to write the "business logic", and
everthing else would just happen. They were taught javabeans, orms, gigantic
frameworks. They completely forgot that their code actually needed to execute,
and no one cared about their "business logic" if the application didn't do
what it was supposed to do.

This generation has only ever used ORM's, and so to them those tools must
solve some hard problem, they are so complex after all. SQL must be hard.

It turns out that SQL is actually much simpler than ORM's. The failure modes
are much simpler, and the implementations more robust. Sure, writing the code
can be tedious, but tedeious is not hard. Writing brainless code every once in
a while gives you time to reflect on the design of your system, and think
about the larger context.

~~~
goatlover
> It turns out that SQL is actually much simpler than ORM's.

Are they simpler when it comes to matching the data to the application's data
structures? One advantage of ORMs is that they encourage this setup from the
start.

~~~
dragonwriter
> Are they simpler when it comes to matching the data to the application's
> data structures?

That depends on the modelling approaches taken by the application developer
and DB developer.

------
daenz
ORMs lure you in with a false sense of neat abstraction. They have nice
intuitive examples on their home pages. But then you use them in the real
world, doing gnarly queries, and you realize that doing anything powerful and
fast in the ORM requires its own completely separate abstractions, which are
often difficult for the uninitiated to follow. It's also often a big pain to
debug the raw SQL that gets compiled after the ORM does its magic.

The argument I've made before when going down the path of ORMs has been: do we
forsee needing to use this model code on a different database engine? Outside
of simple toy applications, or needing to support different engines with the
same code, I agree that ORMs are more trouble than they're worth.

~~~
wvenable
You don't use ORMs for gnarly queries -- that's not what they are for! They
are for making manipulating the entities easier -- reading the data out of the
database in a way that makes easy to modify.

You can (and should) use them for simple queries. You have a list of entities
you want to query and filter, that's going to be fine. Joins are fine. But if
you're doing some complex analysis, an ORM is the wrong tool. That doesn't
mean it's a poor abstraction, or difficult to follow, or something to be
avoided. It's not the right tool _for that job_. For the job it's designed
for, it's going to save a lot of effort.

SQL is great for analysis -- it's pretty much what it's designed for. But for
bringing data into your app and modifying it, SQL is cumbersome and verbose.
If you're loading data into objects then you're just creating your own
personal ORM anyway.

~~~
geophile
ORMs make the simple things simple, and the complicated things impossible.

~~~
wvenable
ORMs let you drop into SQL whenever you need, usually in a way that is fully
compatible with the model, so that's entirely false.

~~~
daenz
Running raw user SQL isn't a prerequisite of an ORM needed to make it an
"ORM", it's a useful feature that most ORMs try to include because the authors
recognize the many shortcomings. Also, by writing raw engine-specific SQL, you
automatically invalidate one of ORMs biggest selling points which is being
SQL-database agnostic.

And by "drop into", this typically means writing custom stitching code that
stitches the SQL cursor results back into the models again. It's rarely
straightforward.

~~~
Glyptodon
I've always thought the "being SQL database agnostic" theory of ORMs was more
about a development team being able to choose from some common choices than
about apps being portable in practice.

~~~
dkersten
Yeah, I’ve never seen any complex applications, using ORM’s, that were easy to
port to another database. Hell, one company I was at switched from MySQL to
MariaDB and even that took some work despite that they should be almost the
dame thing. If you switch to a more substantially different database, eg, from
MySQL to Postgres, then its even harder. I’m also a believer in using a
databases features when it makes sense and not limiting myself to the
standardised subset of SQL just in case I might want to change databases
later.

------
nickjj
Since 2014 we've gotten new tools to play with.

For example, Ecto isn't technically an ORM but it lets you do ORM-like things.
It's Elixr's data mapping and query language tool. It happens to be one of the
nicest "I need to work with data" abstractions I've ever used.

It's a bit more typing than ActiveRecord and even SQLAlchemy, but you feel
like you're at a good level of abstraction. It's high enough that you're quite
productive but it's low enough that it doesn't feel like a black box.

You get nice benefits of higher level ORMs too such as being able to compose
queries, so you can design some pretty compact and readable looking functions,
such as:

    
    
        def eligible_discounts(package_id, code) do
            __MODULE__
            |> for_package(package_id)
            |> with_discount()
            |> active()
            |> usage_count_less_than_usage_limit()
            |> after_starts_at()
            |> before_ends_at()
            |> maybe_code(code)
        end
    

Each of those function calls is just a tiny bite sized query and in the end it
all gets composed into 1 DB query that gets executed.

I think Ecto's biggest win was having the idea of changesets, schemas and
repos as separate things. It really gives you the best of everything. A way to
ensure your data is validated but also flexible enough where you can separate
your UI / forms from your underlying database schema. You can even choose not
to use a database backend but still leverage other pieces of Ecto like its
changesets and schemas, allowing you to do validate and make UIs from any
structured data and then plug in / out your data backend (in memory structs or
a real DB, etc.).

------
acd
Will tend to agree with the author.

Initially ORMs can save time when developing as you get an easy mapping
between objects and the database.

However in practise ORM tends to give you quite horrible JOINs that quite
frankly are hard to understand for humans.

Further more I think that ORM can lead to a bad practice in the sense that you
do not need to think about your data layout first. But for database
performance data layout is of utter most importance. One need to have data in
lay out in a form that makes the application run fast. ORMs does not
necessarily provide that. One need to normalize the database.

ORM save you time during initial development but you pay later in the
maintenance phase when what is complex queries that humans may not understand
are hard to optimize for performance.

Database normalization
[https://en.wikipedia.org/wiki/Database_normalization](https://en.wikipedia.org/wiki/Database_normalization)
[https://en.wikipedia.org/wiki/Third_normal_form](https://en.wikipedia.org/wiki/Third_normal_form)

~~~
Daishiman
This is completely false. The joins I do in the ORM are completely transparent
to me as a developer and there is no mismatch whatsoever. The data layout and
data migrations are well described.

And I have, at most, a half dozen difficult queries which require serious
optimization, and that optimization isn't defined by the query but by the
indexing and storage strategy for the tables in question.

~~~
jtdev
> The joins I do in the ORM are completely transparent to me as a developer
> and there is no mismatch whatsoever.

Care to provide any examples with comparisons to ANSI SQL or any major SQL
platform?

*take note that “SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987.” the same cannot be said for any ORM.

~~~
BoorishBears
Namedropping ANSI and ISO to back your side in a technical argument, haha,
never change HN... or maybe do just a little...

------
invokestatic
This topic pops up frequently here on HN and every time I’m shocked at how
many people have issues with ORMs! I’ve been using Hibernate/Spring Data for
several years now and never ran into any issues. If I need to write a complex
query, I can easily write a @Query annotation in HQL and it neatly fits right
in to the repository class. I also develop with query logging enabled so I
have better understanding of the queries actually ran on the DB. I think it
really boils down to using the right tools, the right way.

~~~
qes
> I’m shocked at how many people have issues with ORMs

Simple inexperience. I'd bet most of those people are mid-level developers who
have used ORMs enough to hit the rough edges but not enough, or with enough
independent agency, to have worked through how to play to ORM's strengths
while avoiding their weaknesses. People that were given a hammer and are just
understanding that their hammer doesn't work very well to install bolts, and
maybe don't have the authority to say maybe I should use a wrench or the
flexibility to try this new crescent shaped idea and see if that works better.

~~~
tigershark
Or maybe you worked only on “toy” projects and never understood that you are
doing more work that could be expressed more elegantly in a functional SQL
expression instead of using the imperative statements of the ORM engine? The
craziest thing in this discussion is that I have to defend SQL that is
probably my least favourite language... I never expected this honestly.

~~~
Daishiman
My "toy" project with several hundred tables and tens of thousands of users
does fine.

I have, at most, a couple dozen "complex" queries in this project.

Whereas I have an order of magnitude more queries that need to be composed
from several different query criteria, a task for which SQL is very poorly
optimized for and most ORMs excel at.

I have used my ORM for so long that writing a report in SQL or the ORM
language is basically the same to me. Neither technology is something I would
consider to be "hard", as most of the problems encountered in practice are
well-trodden.

Nontheless, a simple ORM query is 20% the length of an equivalent SQL query.
And I can compose them trivially. And then I use the model code for the _hard_
part: dealing with the rest of the business logic for template rendering,
email sending, API interactions, and so on, for which SQL is completely
useless.

~~~
pknopf
Your "toy project" doesn't have 10+ team members, ranging in experience.

~~~
Daishiman
Yes it does.

Seriously using an ORM is just not hard. Especially if actually do code
reviews with your junior team members, which you should always be doing.

------
spullara
Even if you use an ORM you should probably know SQL so you can use them
efficiently. Also, if you use an ORM, make sure it "starts with database
schema" not with objects. Otherwise the mismatch will probably be horrible for
you. A dozen years ago I published this article on generating your domain
objects from the database, still a reasonable thing to do if you want the
added query expressiveness the ORMs have over most SQL generators.

[https://javarants.com/generate-jpa-or-gorm-classes-from-
your...](https://javarants.com/generate-jpa-or-gorm-classes-from-your-
database-for-java-and-grails-234d223311a1)

~~~
blackflame7000
A feature of Netbeans I always liked was the ability to create a Hibernate
Java Object directly from a database table. It does make sense to me to start
at the DB first because the application is just a broker between the front end
and the DB so it's more flexible to make the adaptations there.

------
geebee
This article was well balanced. I find title just a bit misleading, since
"What ORMs have taught me: just learn SQL", to me, carries a slightly
different message than "What ORMs have taught me: learn SQL".

The second sentence is a little more compatible with the first line, "they can
be used to nicely augment working with SQL in a program, but they should not
replace it."

That's certainly how I use them. ORMs can save you a lot of irritating typing
where it comes to insert and update statements. Aside from that, I write a lot
of raw SQL.

I have seen what I would describe as ORM-induced, yaml-induced database
damage. This isn't meant as a criticism of these tools per se, they're
perfectly compatible with a well designed app. But I have noticed that people
sometimes create databases that are useful only in the context of their
configuration-file/ORM heavy app. Essentially, the programmers conceive of
their data as a set of objects, and they use the config file to store global
constants and the ORM to persist files, almost as if they're pickling and
retrieving objects back into the system.

The result is a database that can't really be queried with SQL, more or less
useless outside the context of the application. I firmly agree with developers
who maintain that information will outlive an application, a database will
outlive the software that was originally designed to use it (perhaps in
parallel with it). I think a SQL database should be useful all on its own as a
data source. If you got rid of the app, you'd lose a lot of operations on that
data, a lot of UI, a lot of valuable things, but you'd be able to get at and
use your data. If that's not the case, I'd seriously reconsider the design.

Kind of hard to do that without understanding SQL, so yeah, definitely learn
it.

------
iamleppert
I too have gone back to SQL after working with ORMs for 10+ years. Having
worked with them on a wide range of projects and teams, I can say, without any
reservation, they are not worth it.

Not worth it for the small projects nor the large projects. They significantly
complicate the development workflow and add another layer of (often times,
cumbersome) abstraction between the user and the data.

If you encounter any issues (and better pray you don't), expect to spend hours
stepping through reams of highly abstracted byzantine code, all the time
feeling guilty when you just want to open a database connection and send the
SQL string you developed and tested in minutes.

~~~
jillesvangurp
I've ripped out broken ORM on multiple projects with over-engineered domain
models designed by people with no apparent knowledge of how to do a proper
database design. This is the key problem with ORM. It leads to lots of
unnecessary joins just so you can pretend databases do inheritance or all
those tiny objects you will never query on need dedicated tables with indexed
columns. It's stupid. It's also stupidly slow, fragile, and hard to maintain
such systems.

I've been on a project where we had 20+ tables. After I made the point that
the sole purpose of this database was producing json documents through
expensive joins that were indexed and searched in Elasticsearch (i.e. this was
a simple document db), we simplified it to a handful of tables with basically
an id and a json blob; got rid of most of the joins and vastly simplified the
process of updating all this with simple transactions and indexing this to
elasticsearch with a minimum of joins and selects.

We also ripped out an extremely hard to maintain admin tool that was so
tightly coupled to the database that any change to the domain made it more
complicated and hard to use because the full madness of the database
complexity basically leaked through in the UI.

ORMs don't have to be a problem but they nudge people into doing very sub
optimal things. When the domain is simple, the interaction with the database
should be simple as well. We're talking a handful of selects and joins and
simple insert/update/delete statements for CRUD operations. Writing this
manually is tedious but something you do only once on a project. With modern
frameworks, you don't end up with more lines of code than you'd generate with
an orm. All those silly annotations you litter all over the place to say "this
field is also a column" or "this class is really a table" get condensed in
nice SQL one liners that are easy to write, test, and maintain.

~~~
shkkmo
> ORMs don't have to be a problem but they nudge people into doing very sub
> optimal things.

I don't think good ORMs do any nudging. The issue arises when people assume
that because they are using an ORM they don't have to learn the underlying DB.
ORMs should be treated as tools that sit on top of your SQL knowledge and
allow you to do certain types of things easier.

Like any tool, there are inappropriate uses cases. On one side you have people
using an ORM to implement a document store, on the other side you have people
that end up hand-rolling a crappy ORM because they thought they didn't need
one.

~~~
jillesvangurp
I don't do object relational mappings generally. I instead query by id or a
few other columns and construct objects from json documents stored in text
columns.

Frameworks for that are awesome and a lot easier to deal with and
serializing/deserializing overhead is typically minimal. Columns in databases
only have two purposes: indexed columns for querying (ids, dates, names,
categories, etc.) with or without some constraints, and raw data (json or for
simple structures some primitive values. Some databases even allow you to
query the json directly but in my experience this is kind of fiddly to set up
and not really worth the trouble. The nice thing is that most domain model
changes don't require database schema changes this way because the only thing
affected is your json schema. This makes iterating on your domain model a lot
easier. You still have to worry about migrations of course.

The added value of using a database is being able to manipulate them safely
with transactions and query them efficiently. Bad ORM ends up conflicting with
both goals and the added value of well implemented ORM is usually fairly
limited. At best you end up with a lot of tables and columns you did not
really need mapped to your objects and classes.

A good table structure often makes for a poor domain model and vice versa. The
friction you get from the object relational impedance mismatch is best avoided
by treating them as two things instead of one. Bad ORM shoves this under the
carpet and in my experience does not address this (other than by providing the
illusion this is not a problem).

~~~
shkkmo
> I instead query by id or a few other columns and construct objects from json
> documents stored in text columns.

> Columns in databases only have two purposes: indexed columns for querying
> (ids, dates, names, categories, etc.) with or without some constraints, and
> raw data (json or for simple structures some primitive values.

You are basically describing a sort of ad-hoc document store with potentially
limited ability to query. You've lost many of the benefits provided by a
relational DB. Your ability to do run large update queries or reports will be
limited (unless your DB provides native json support, which as you said, is
fiddly).

If you are going to do this, why not use a NoSQL document store with support
for transactions? Then you will get a tool that is designed to work with your
use case.

Edit: If you use an ORM, a hybrid approach is possible. Where you store some
properties as separate columns and then store the less frequently accessed (or
more dynamically structure) data in a json field (which you can deserialize on
hydration or on request). The main downside of this hybrid approach is that
moving a propery out of the json field into a normal column would require
using that fiddly native json support or a fairly slow migration that would
need to go through and serialize each json field.

> A good table structure often makes for a poor domain model and vice versa.

Can you clarify what you mean? This has not at all been my experience so I am
curious and would love to see some examples.

~~~
jillesvangurp
Transactional semantics are problematic with a lot of nosql databases but I've
used a few and you can work around this if you have some kind of consistency
checks using content hashes. Postgres is pretty nice these days for a wide
variety of use cases; including nosql ones. And it does transactions pretty
nicely.

Regarding the object relational impedance mismatch, check here:
[https://en.wikipedia.org/wiki/Object-
relational_impedance_mi...](https://en.wikipedia.org/wiki/Object-
relational_impedance_mismatch)

In short, there are lots of things you'd do different in an OO domain model
vs. properly normalized tables. A lot of what ORMs do is about taking object
relations and mapping those to some kind of table structure. You either end up
making compromises on your OO design to reduce the number of tables or on the
database side to end up with way too many tables and joins (basically most
uses of ORM I've encountered in the wild).

For reporting, you can of course choose to go for a hybrid document/column
based approach. I've done that. In a pinch you can even extract some data from
the json in an sql query using whatever built in functions the database
provides. Kind of tedious and ugly but I've done it.

Or you can use something that actually was built to do reporting properly. I
do a lot of stuff in Elasticsearch with aggregations and it kind of blows most
sql databases out of the water for this kind of stuff if you know what you are
doing. In a pinch, I can do some sql queries and I've also used things like
amazon athena (against json or csv in s3 buckets) as well. Awesome stuff but
limited. Either way, if that's a requirement, I'd optimize the database schema
for it.

But for the kind of stuff people end up doing where they have an employee and
customer class that are both persons that have addresses and a lot of stuff
that is basically only ever going to be fetched by person id and never queried
on, I'll take a document approach every time vs. doing joins between a dozen
tables. I also like to denormalize things into documents. Having a category
table and then linking categories by id is a common pattern in relational
databases. Or you can just decide that the category id is a string that
contains some kind of urn or string representation of the category and put
those directly in in a column or in the json. You lose the referential
integrity check on the foreign key of course; but then you should not rely on
your database to do input validation so that check would be kind of redundant.

~~~
shkkmo
> Postgres is pretty nice these days for a wide variety of use cases;
> including nosql ones.

um... what? Are you meaning to say that Postgres does a pretty good job as a
document store? (not synonymous with "nosql")

Despite that that wikipedia article says, most (if not all) of the "impedence
mismatches" described apply to most document stores as well. I would be
curious to hear which of the mismatches described in that article you think
are avoided by using Postgres as a document store. In my mind, the reason for
using a document store is to have flexibility in the structure of your data
(which can be a positive or negative depending on your needs).

> Or you can use something that actually was built to do reporting properly. I
> do a lot of stuff in Elasticsearch...

Of course there are document stores with good reporting. I was talking
specifically about the downside of using Postgres as a document store given
your complaints about its native json support being fiddly.

> But for the kind of stuff people end up doing where they have an employee
> and customer class that are both persons that have addresses and a lot of
> stuff that is basically only ever going to be fetched by person id and never
> queried on, I'll take a document approach every time vs. doing joins between
> a dozen tables. I also like to denormalize things into documents.

I often de-normalize addresses in my tables, but that choice is based on how
you will want to store and update that data. A separate address table is good
if you want to be able to automatically propagate address edits between
records. A de-normalized address is good if you want keep records of that
address for the purpose for which it was used. De-normalization is always an
option with a relational DB, but normalization is not always easy some
document stores.

> Having a category table and then linking categories by id is a common
> pattern in relational databases. Or you can just decide that the category id
> is a string that contains some kind of urn or string representation of the
> category and put those directly in in a column or in the json. You lose the
> referential integrity check on the foreign key of course; but then you
> should not rely on your database to do input validation so that check would
> be kind of redundant.

I'm not quite sure what you are on about here. You can use constraints on
columns that are strings and you can have tables that are composed entirely of
an indexed string column to point that constraint towards. Integer Ids are
primarily used just to save space. (I don't really see how this is relevant.)

I don't see anything here to justify your assertion:

> A good table structure often makes for a poor domain model and vice versa.
> The friction you get from the object relational impedance mismatch is best
> avoided by treating them as two things instead of one.

To be frank, it sounds to me like you ran across a bunch of poorly designed DB
schemas (or schemas you didn't understand the design decisions for) and
decided that it must be impossible to design good DB schemas and so you just
use unstructured document stores instead.

------
stickfigure
Beginning programmer: ORMs let me write code without learning SQL!

Intermediate programmer: ORMs just get in the way! SQL isn't that hard after
all.

Advanced programmer: I write a lot of SQL, but I use ORMs to cut out most of
the boilerplate.

~~~
sjwright
Even more advanced programmer: writing the ‘boilerplate’ queries out manually
takes barely more time than composing them in an ORM, means less indirection,
saves me a major dependency, and encourages me to think intelligently about
each query no matter how boilerplate they might seem at the surface.

Super-advanced programmer: allowing my database structure to be influenced by
the needs of an off-the-shelf ORM will make it worse.

~~~
fastball
What ORM do people use that influences the structure of their database? The
ORM I'm currently using the most can do whatever I need with my Postgres DB.

Also, the ORM allows me to specify models that are not only used for
structuring the database, but also for validation of incoming JSON requests
and easily serialize queries back to JSON.

~~~
taffer
> What ORM do people use that influences the structure of their database?

Hibernate and JPA encourage designing your domain classes first and then
generate the DDL from that.

> Also, the ORM allows me to specify models that are not only used for
> structuring the database, but also for validation of incoming JSON requests
> and easily serialize queries back to JSON.

Postgres has great JSON support, does the ORM something with JSON that
Postgres cannot do?

~~~
shkkmo
> Hibernate and JPA encourage designing your domain classes first and then
> generate the DDL from that.

This is a feature, not requirement or need of the ORM. It seems pretty silly
to let the existence of a feature prevent you from making designing the
structure of your DB correctly.

> does the ORM something with JSON that Postgres cannot do?

Postgres's json functionality is used for manipulating and querying data
stored in the DB.

I believe the poster is talking about deserializing and validating json from
REST requests and serializing json for REST responses using the mapping
defined for the ORM.

~~~
taffer
> I believe the poster is talking about deserializing and validating json from
> REST requests and serializing json for REST responses using the mapping
> defined for the ORM.

These are also things that the json functionality of Postgres can do. For
example, look at to_json and json_agg.

~~~
stickfigure
In my current company I use Postgres JSONB with Hibernate extensively. One of
the benefits of the ORM is that json fields can be constrained to a fixed
schema. For example, a tag list field can be a SortedSet of strings.

A couple other things I've learned:

* Never re-use complex types in both your API and your schema. These things evolve at different paces and you should never have to worry that a change to your schema will break an API (or vice-versa). The minimal extra typing to have dedicated API types is well worth it.

* Storing untrusted client-submitted JSON in your database is a terrible idea. This is a great attack surface, either by DOSing your system with large blobs or by guessing keys that might have meaning in the future.

------
bartread
There's a sensible middle ground here, although I agree with the thrust of the
message because using an ORM doesn't obviate the need to learn SQL - something
I think a lot of developers forget.

The other extreme from using an ORM "for everything" is using SQL "for
everything", either via loads of handwritten ad hoc SQL, or stored procedures,
UDFs, views, or a mix of all of these. This is just a _different_ nightmare.
And don't be fooled: it really is still a nightmare.

A sensible approach blends use of an ORM with handwritten SQL where needed. In
fact most ORMs will allow you to do things like build collections of objects
from custom SQL anyway, so there's really no need to shy away from it.

One other thing I'd say: I wouldn't necessarily trust my ORM to adequately
design my database for me via a code first approach. It's more work but
thinking about the data model and explicitly designing the database often
yields better results, and you have more control. Code first is OK for simple
stuff, but often even simple stuff becomes complex over time so I tend to shy
away from it.

~~~
pknopf
> A sensible approach blends use of an ORM with handwritten SQL where needed.
> In fact most ORMs will allow you to do things like build collections of
> objects from custom SQL anyway, so there's really no need to shy away from
> it.

This is really where it's at. Seriously people. Nobody should be writing raw
SQL.

Give me just enough of an ORM/abstraction to give me type-safety, leave the
rest at the door.

~~~
jtdev
> Nobody should be writing raw SQL.

What?!? That’s an absolutely foolish and naive assertion. Please don’t give
any of your fellow junior devs that “advice”...

~~~
pknopf
Are you advocating that I put a bunch of magic strings in my codebase? That
would be a maintenance nightmare.

Unless you think I'm suggesting that no devs should ever write SQL at all in
their career? Which isn't the case.

~~~
shkkmo
> Are you advocating that I put a bunch of magic strings in my codebase? That
> would be a maintenance nightmare.

Not a bunch, just the large batch queries and reports that use complicated
joins. There a places where it makes sense to take the trade-off between
maintainability and performance.

> Unless you think I'm suggesting that no devs should ever write SQL at all in
> their career? Which isn't the case.

It sure appears to be what you are suggesting. Perhaps you should clarify what
you were trying to say?

------
_bxg1
In my opinion this whole problem is one of the strongest condemnations of OOP.
Even if OOP really were a great way to model your data, to express
relationships and property types and such (setting aside all the
questionability of that claim), _it 's all just going to end up in a database
anyway_. Unless you're using a denormalized database like Mongo, or the bulk
of your application state is non-persistent, your perfectly thought-out OOP
model is at best going to be a terrible approximation of the _real_ data model
and you're much better off using a language/paradigm that lets you just treat
data as data.

~~~
Daishiman
What makes you think that the DB will always by the dominant data store for a
business object?

Business objects need a lot more, from rendering, processing, email sending,
notifications, etc.

All those things are well approximated by an object and poorly approximated by
SQL.

~~~
_bxg1
Everything you can build with OOP can be done with FP and vice-versa. So the
question is which one's strengths you'll most benefit from.

OOP's strengths surround modeling nested object structures and encapsulating
state and logic within opaque containers. If you're primarily writing data
to/reading data from a relational database, everything gets flattened and
exposed. So you have to ask yourself what you're getting from it at that
point.

Rendering and processing are well-suited to FP. "Email sending" and
"notifications" are vague, but I see nothing about SQL + FP that makes those
harder.

------
dang
Thread from 2017:
[https://news.ycombinator.com/item?id=15949144](https://news.ycombinator.com/item?id=15949144)

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

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

~~~
Roboprog
I know, this is a recurring discussion.

Both sides continue to distrust the other, though.

~~~
DoreenMichele
Listing prior discussions is not a reprimand suggesting we can't discuss it
again. It's a service to the community to let us see what's already been said,
if we so desire.

~~~
Roboprog
Ah, thanks.

------
bob1029
Swapping from EF6 to Dapper was one of the best choices we ever made with our
project stack. It is so relieving to be able to hand-tune queries and
transactions now. Initially, we were sold on the apparent simplicity of EF6,
but as with many things there is a cost for an abstraction like this. In our
case, the performance penalties and opaqueness were dealbreakers after a
while. We saw an average speedup of 10x on all SQL operations after rewriting
using direct SQL with Dapper handling mapping of result sets into models.

Writing your own SQL migrations is actually incredibly straightforward if you
just think for a few minutes about how you would do it if ORMs didn't exist.
Many database systems have ways to store metadata like schema versions outside
the scope of any table structure, so you can leverage these really easily in
your scripts/migration logic. EF6 uses an explicit migration table which I was
never really a huge fan of.

One thing we did do that dramatically eased the pain of writing SQL was to use
JSON serialization for containing most of our complex, rapidly-shifting
business models, and storing those alongside a metadata row for each instance.
Our project would be absolutely infeasible for us today if it weren't for this
one little trick. Deserializing a JSON blob to/from a column into/from a model
containing 1000+ properties in complex nested hierarchies is infinitely faster
than trying to build up a query that would accomplish the same if explicit
database columns existed for each property across the many tables.

~~~
hdfbdtbcdg
I am seriously glad I don't work on this project!

~~~
bob1029
Care to elaborate on which aspect noted above is adverse from your
perspective? I would be happy to provide more context and explain in more
detail some of the reasoning involved in our decisions.

~~~
hdfbdtbcdg
> Deserializing a JSON blob to/from a column into/from a model containing
> 1000+ properties in complex nested hierarchies

> complex, rapidly-shifting business models

Your business logic classes have 1000+ properties. And you plan to not migrate
them when the schema changes but leave many instances with old versions of the
schema sitting in the datastore. Your application logic is going to get nasty!

~~~
bob1029
The other aspect here is that the lifetime of these objects is very brief for
our application. Typically 10-60 minutes. Schema changes, while breaking, are
tolerable along business cycle boundaries.

~~~
hdfbdtbcdg
Why use SQL at all? It sounds like you needed a key value store?

------
sword_smith
I use Entity Framework for C# and I have grown to appreciate it. I get
libraries for in-memory databases which makes it easy to write thorough unit
tests, the `Include` function uses join to include foreign key objects in an
optimal manner, and scaffolding tools make it easy to map from SQL to C#
classes. The resulting SQL from the Linq expressions is logged which makes it
easy to see what is going on, if you already know SQL. The only problem is
that I think the writeback to the database of manipulated or new objects is
not clear but that might just be because I haven't read the documentation for
that thoroughly.

~~~
eknkc
EF is fantastic to use. I think it's because while other ORMs try to integrate
their logic into a language, EF was developed alongside linq, IQueryable stuff
and language extensions. Basically, they changed the language to better
accomodate stuff like EF. And it shows.

------
kbenson
Advocating for the use of SQL over an ORM in every case is like advocating for
the use of Assembly over C in every case.

In both cases, one is a higher level abstraction over the lower level
capabilities, which can provide a quite large gain in usability and ability to
easily understand what is going on at the level you are working at, for the
loss of hand optimizing at a low level to get just what you want in _every_
case.

Similarly to with Assembly and C, you can often drop to the lower level as
needed for speed or other very specific needs.

In both cases a good understanding of the lower level language will help you
both know when it's appropriate to drop to a lower level for performance or
for a special feature, and when it doesn't matter because the ops/SQL
generated is rear optimal anyways or the gains are almost definitely less than
the problems caused from a maintainability perspective.

I'm perfectly happy to use an ORM for 95% of my DB needs. Just the query
builders that generally ship with them are worth the price of their inclusion
IMO (at least for the good ones), as it can _greatly_ simplify queries that
are variable based on different parameters you may have per run.

~~~
taffer
> Advocating for the use of SQL over an ORM in every case is like advocating
> for the use of Assembly over C in every case.

Comparing SQL to Assembly is probably one of the worst comparisons you can
make. SQL is a very high-level and powerful declarative language that I would
compare, if at all, with functional languages and not with assembly. A few
lines of SQL are counting, sorting, grouping, aggregating, and merging data
that would take dozens of lines if written row by row in a procedural
language.

~~~
celticmusic
an analogy is not about comparing, it's about communicating an idea.

pointing out that the comparison is not perfect does not invalidate or refute
anything. Every analogy, by definition, is not perfect. If the analogy were to
ever become perfect, it would stop being an analogy and would instead be a
tautology.

"an ORM is like an ORM".

IOW, the differences are _WHY_ analogies are useful.

~~~
taffer
The point was not that the comparison was not perfect, but that it was utterly
wrong. SQL is not at a lower level of abstraction than procedural code, but at
a higher level. Abstraction should make complex things easier, not harder.

~~~
celticmusic
you're still missing the point.

An analogy cannot be wrong because it is not comparing anything. it's
communicating an idea.

~~~
jtdev
okay, you win... let’s call it an illogical or even obtuse analogy.

------
wvenable
I use a data mapping ORM and I've literally not have any of these concerns.
They are all non-issues.

The first item about querying is relevant but all ORMs allow you to drop into
SQL to execute a complex reporting-style query. It's not really necessary if
you are just querying in objects to manipulate (which is what ORMs are good
for).

~~~
celticmusic
A data mapper is not an ORM, it's a data mapper. It's a different access
pattern.

But I do agree that data mappers tend to be better.

~~~
moojd
This is the 'data mapper' vs 'active record' debate and I would argue both are
sub-categories of 'Object-relational mapper.' I've seen multiple people in
this thread even include query builders in their definition of ORM. I'm
starting to wonder if much of the disagreement in this thread is caused by
everyone having their own definition of ORM.

~~~
celticmusic
My comment wasn't meant to be a refutation of anything, merely an observation.

------
speedplane
I'm okay with ORMs, but I can't stand lazy evaluation. It often leads to
situations where it's entirely unclear when the program is making a database
call.

For example, given the small django program:

    
    
      dbcall = models.Purchases.objects.filter(amount = 100)
      if dbcall:
        do_something()
        if len(dbcall) > 5:
          do_something_else()
        third_thing(dbcall[0])
    

Does the above app make 1, 2 or 3 database requests? There is an answer, but
it's not at all clear to the developer.

~~~
Nicksil
>There is an answer, but it's not at all clear to the developer.

A cursory reading of the documentation is usually a first step in acclimating
to an otherwise unfamiliar system.

It just so happens, for your example here, a complete explanation[1] can be
found at the very top of what's likely the most vital subsystem's
documentation. During due diligence, this information will be among the first
encounters.

[1][https://docs.djangoproject.com/en/stable/ref/models/queryset...](https://docs.djangoproject.com/en/stable/ref/models/querysets/#when-
querysets-are-evaluated)

~~~
speedplane
> A cursory reading of the [django] documentation is usually a first step in
> acclimating to an otherwise unfamiliar system. It just so happens, for your
> [lazy evaluation] example here, a complete explanation can be found at the
> very top ...

The "very top" of the django lazy evaluation documentation you pointed to
hardly sheds much light on the issue. The documentation just says that certain
operations cause database queries, and that some are cached. It's not clear
whether doing two similar operations consecutively will result in two database
queries (e.g., checking len(dbquery) twice, or checking for truthiness then
pulling the first object from a database query).

The django documentation does differentiate between evaluated and non-
evaluated database queries, but to the developer, it's the same object. Would
be much better if an "unevaluated query" and an "evaluated query" were two
different types with different semantics.

Worse, these lazy evaluation semantics are not terribly pythonic. Checking the
length or truthiness of a string, list, set, dict, or tuple is an O(1)
operation. Checking the length of a database query may (or may not) make
network calls, and may have O(N) performance or worse.

Databases queries can give rise to performance bottlenecks and race
conditions, and hiding what's actually happening from the developer is a
recipe for all sorts of problems.

------
conroy
If you use Go and PostgreSQL, I’ve been working on a tool to help you “just
use SQL” called sqlc[0]. It generates methods and structs for your queries,
automatically. It’s made a dramatic difference in my day-to-day workflow.

[0] [https://github.com/kyleconroy/sqlc](https://github.com/kyleconroy/sqlc)

~~~
brianm
I really like this! I particularly like that it is oriented around a useful
workflow, rather than for just "small code at rest".

It looks like it parses the schema to find type information (2 minute skim, so
please forgive me if I got it wrong!) Hoe does it handle schema changes?

~~~
conroy
sqlc understands ALTER statements, so it can parse your migrations directory.

------
fabiosussetto
I think there's often an underlying confusion between different tools and what
they are supposed to do.

An ORM - as the acronym says - is helpful to map database records to objects
in the system. The meaning of the acronym already says that an ORM is not
really designed for scenarios like aggregations and reporting. Within those
contexts, you don't normally reason in terms of list of "objects" and
"relationships" between them.

A "SQL builder" gives you a nice programming interface to build and manipulate
SQL statements. Manually building complicated SQL strings is tedious, error
prone and it makes it hard to reuse the same queries. With a SQL builder
instead you can easily add dynamic conditions, joins etc, based on the logic
of your application. Think of building a filterable Rest API that needs to
support custom fields and operators passed through the URL querystring:
concatenating strings would be hard to scale in terms of complexity. Some
people prefers to use templates instead of SQL builder to add conditions,
dynamic values, select fields etc. I personally find that this approach is
like a crippled version of a proper SQL builder interface. I prefer to use the
expressiveness of a real programming language instead of some (awkward?)
template engine syntax.

I think the confusion between these two different tools is caused by the fact
that in some popular frameworks as Django or Rails you just get to use the
ORM, even if behind the scenes the ORM uses some internal query builder.

Other ORMs like SQLAlchemy instead gives you both tools. You can indeed use
SQLAlchemy as a ORM and you can also use it directly as a SQL builder when the
ORM abstraction doesn't really work.

Normally, if someone tells me that it's better to write SQL queries by
concatenating strings, I'd ask them how they'd build a webpage that filters
products in the catalog with a series of filters specified by the user (by
price, by title, by reviews, sorting, etc.). Try and build that concatenating
raw SQL bits, without making a huge mess.

Also, the "just learn SQL" may apply to ORMs, but certainly not to a SQL
builder.

~~~
breatheoften
Query builders can be so good at making it easy to work with the database ...
the popularity of ORMs over query builders is a really big collective
reasoning failure in my opinion.

With a good query builder in hand - it is very unclear to me why anyone would
ever want to use an orm.

~~~
Glyptodon
I like query builders but if everything were done with query builders there'd
still be an awful lot of DRY pertaining to business logic that needs to go
somewhere. Maybe you replace the ORM with some sort of 'results act as
<something>' abstraction, but things like that often work out much better with
consistent scaffolding of some sort, which is mostly what successful ORMs seem
to be.

~~~
AlphaSite
This is why I love Sqlalchemy. We use it as half orm, half query builder and
it’s pretty great.

------
joelS
Surprised to see no one has mentioned query builders (like
[http://knexjs.org](http://knexjs.org) \- no affiliation). All the niceties of
not writing raw queries but no abstraction leaks... Although I have found for
anything more interesting (recursive queries, etc) there is no escaping raw
queries and every developer needs to bite the bullet and learn SQL.

~~~
bob1029
I see query builders as a learning tool for fresh developers, and as a job aid
for business analysts. For some this can be the final destination, but for any
developer I would push hard to get them writing SQL by hand ASAP.

A few weeks of suffering through DIY SQL is really the only way to
fundamentally understand how the database is working for (or against) you.
Once you learn it, it really does become like a second language. You can fade
in and out of proficiency based on recency of exposure, but it is mostly like
riding a bicycle. One other advantage is you can take it across any SQL
platform without a second thought. Many ORMs have provider-specific
compatibility woes to contend with.

~~~
syntheticcdo
I disagree. Knex isn't an abstraction over SQL itself, as much as an
abstraction over the tedious process of writing literal strings, which let's
the developer treat SQL queries as code.

My process is generally - write SQL to find the best query, then convert the
SQL to Knex for integration.

As a simple example, say you are making a simple query to the db, but in some
edge cases you need another column to be selected. Knex lets you do this:

    
    
        let query = KNEX("table").select("col_1")
        
        if(weirdCondition) {
          query = query.select("col_2")
        }
    
        const rows = await query
    

Which naturally compiles into either

    
    
        SELECT col_1 FROM table
    

or

    
    
        SELECT col_1, col_2 FROM table

------
tracker1
On a recent project, it was a weird inversion in terms of access... in order
to keep the middle tier thin, and meet requirements that all data access
happen through stored procedures... we pretty much standardized an interface
with one input parameter (@json) and two output parameters (@result,
@errorResult). In the end, all input/output was JSON and the database handled
all data internally.

I don't really like it much, but it did make the API service super thin, and
the "database guys" were happy to do it all in SQL. Of course, testing that
beast isn't so fun, and there be dragons...

In the end, I tend to favor creating APIs with scripted languages that require
less translation to work with the database side, and structure responses to
match the expected data layouts for the API side. With node, I usually
create/use a simple abstraction...

    
    
        var result = await db.query`SELECT ... WHERE foo=${bar}`;
        or
        var result = await db.exec('sprocname', {...});
    

In either case, not really a need for a formal ORM here.

~~~
Nican
It was not the first time I heard the requirement about "all data access
happen through stored procedures", and I find it ludicrous.

Does anyone know how such a paradigm came to exist? What problem is this
solving?

~~~
qaq
Security, decoupling calling code from db schema

~~~
tracker1
Don't API services do that?

~~~
qaq
How do they do that in the context of ORM vs sps :)?

~~~
tracker1
Calling code is the front end... API services separate that calling code from
the backend.

~~~
qaq
your backend is still storing data somewhere right?

~~~
tracker1
Yes, but I'm not sure I get the benefit of decoupling the schema, I'm still
coupling to the stored procedure interfaces, and still have to deal with the
shape of input and results.

~~~
qaq
If schema needs to be changed in many cases sp interface will stay the same
e.g. I can do changes/optimizations to schema without changing the calling
code.

~~~
tracker1
But, if it's a schema change where you have to update the SP, you still have
to change code... it's that the code is in (PL/T)SQL vs in another language..
You still have to update code either way.

------
jkdufair
I would have generally agreed with this sentiment until we took a leap of
faith and chose Entity Framework 4-5 years ago. That, coupled with the power
of LINQ has allowed us to do things we simply wouldn’t be able to do
otherwise. Pass IQueryables around without realizing them right away. This
leads to functional, composable queries. And the queries we generate would
simply not be possible to be written by hand. If we need to write SQL, we can
and do. But with good design and good indexes, we rarely, if ever, do. I was
skeptical, but EF is a tool we have not regretted.

~~~
ratww
Entity Framework and LINQ are amazing feats of engineering.

Its query builder is super transparent, consistent and flexible, thanks to
LINQ, but still generates very performant queries.

------
blacksoil
The problem on the article is because the author seems to be using ORM as a
complete replacement of SQL query. When it's used like that, for sure it's
gonna be a hindrance.

How I use ORM is to help automating some of the things, while on top of that,
I'd still write raw queries for the more complex things. For example, I create
base tables using ORM, and then lay off all the joins and more complexity in a
handwritten views. For query, I use ORM for something simple such as direct
single-table queries or straight forward joins. When things get more complex
such as joining a bunch of tables, or doing nested queries, I just use raw
query.

The thing about ORM is that it should always be used as a complementary tool.
Feel free to mix and match it with raw SQL queries as needed. Trying to do
everything in ORM is just too much. One would end up spending too much time
learning about the inner working of the ORM, instead of getting things done.
So yeah, use it as a complement to raw queries, not as a substitution.

------
philwelch
I’ve come to the same conclusion even working with ActiveRecord. SQL is very
literally a domain-specific language for working with relational data; why
would we go so far out of our way to eschew writing code in it?

~~~
matthewmacleod
Because the process of building that SQL, executing it, and bringing the data
it returns into the object oriented universe is a tedious and fiddly pain in
the arse.

~~~
philwelch
1\. “Building SQL”? It’s not clear to me what that means in a world where you
are writing raw SQL rather than using ORM’s and query builders.

2\. “Executing it”—this is called a SQL client library. If you install your
SQL statements as stored procedures you can execute those procedures by name,
for example. Not that hard.

3\. “Bringing the data it returns into the object oriented universe”: I am
typically satisfied with an array of hash tables. Funny story: what do you get
when you use ActiveRecord’s find_by_sql with a query that returns columns not
in the original table? You get a collection of objects _each with the extra
column monkey-patched to the individual object_. In other words, a slower and
stupider hash table that happens to have dot syntax rather than bracket syntax
and a bunch of do-they-even-still-work-in-this-context instance methods
attached.

------
residentfoam
I used ORMs JPA/Hibernate in several projects/teams and the outcome is always
the same: things always get messy and overcomplicated, few reasons :

\- they push developers to design super-normalized db schemas that look
beautiful on paper but are horrible in practice

\- the average developer has a very superficial knowledge of how ORMs work and
this often leads to bad code/performance

\- soon or later you will find yourself fighting the "framework" because what
you are trying to do does not fit their model (e.g: upsert)

In my experience, this whole idea of abstracting from the DB is faulty at its
root. You want your code to be close to the DB so that you can use all the
greatest and latest functionalities without waiting for the framework X to
support it.

I have found that JOOQ or simply Spring JdbcTemplate in most cases are more
than enough.

~~~
kjeetgill
Agreed! I've found JOOQ to be excellent.

Generally ORMs try to abstract away the database entirely. This is mostly fine
for CRUD stuff where you really just want to persistently stash away something
basic off host. If you could write perfect uncrashing programs you'd probably
just keep them in memory.

As soon as you need to _find_ things, especially based on their relationships,
you'll need to be aware of what indexes you have available at least.
Eventually you'll want to have control of the precise query

JOOQ lets you compose queries programmatically without really hindering you
from producing any query you want but not forcing you to glue strings together
either.

------
lallysingh
Wholeheartedly agree. It's amazing how much work you can save by just having
the database do it via a SQL query/view.

~~~
givehimagun
We used to show the new developers on the team a query generated by Entity
Framework...23 pages long. We replaced it with a well formatted query that fit
on half a page. When I switched the team to Java + JPA, we started writing our
own queries if wasn't a simple CRUD and it has been wonderful for the entire
team.

I think it's easier to learn SQL than learn an abstraction of a DSL.

~~~
threecreepio
And SQL has been relevant for about half a century, odds are good you'll
continue to find use for that knowledge for the remainder of your career..
Well worth the time investment.

------
jasonkester
I don't know. I've been building database backed stuff for 25 odd years now,
and I've never experienced this Object/Relational Impedance Mismatch that
everybody talks about in any of my designs. I sometimes wonder if it's just
the approach I take that has ended up dodging that bullet somehow.

My initial design is always done in the database. Whether it's a little
feature or a green field new project on a blank sheet of paper, that sheet of
paper is the Schema Designer of my db (or a schema.sql if I'm in postge/mysql
land).

Once the schema is nailed down, the object structure flows out easily. You can
follow foreign keys, many-to-many tables and non-identity primary keys to
figure out your children, relations, and inheritance. It's so well defined
that for my own stuff I just point my code generator at it to get a pile of
base classes and stored procedures for all the CRUD. (And re-run it at build
time to ensure that everything always matches up.)

So when it comes to pulling down records, modifying them, saving them,
grabbing sets of them to spin through, etc. There's never any mismatch because
what you get from the db will naturally look just like what you need. Because
you designed it to be that way.

I may hazard a guess as to why so many people do run into issues, and it's
because I notice that nearly every ORM I've seen in the wild expects you to
define your schema someplace other than the database. It'll have some wacky
XML config file that you're supposed to keep up to date, with even wackier
"migrations" for when that changes. And it'll then either build your db schema
for you or expect you to have something in place that matches what it wants.

But that's silly.

There's already a perfectly good place to keep your schema. In the database.

And I guess it follows that if you don't design your system with a sensible
relational data model in mind, you might find that your object structure
doesn't in fact fit in a database correctly. Could it be that that's what
people are describing when they talk about Impedance Mismatch?

~~~
spion
So if you have to change the schema afterwards (with lots of data already in
the table), what do you do?

~~~
jasonkester
Change the schema, and run a data migration script if necessary. And, of
course, check the change script into source control so that your build system
knows to run it on the staging and prod dbs when the time comes.

Then simply build your project and fix any compile-time errors that arrived
when the base classes were all blown away and rewritten.

Extra points for keeping your column names in string enumerations so you can't
ever get runtime errors from typos or having renamed a column. (all handled by
the code generator, of course)

But yeah, even on my mature projects I still find myself changing the schema
all the time. It's as easy to do as adding/changing a class in the project
itself.

~~~
spion
I believe you've just described migrations (scripts that modify the schema and
run data migrations, checked into source control).

Generating the ORM layer from the database schema seems OK to me. It does
preclude generalizing certain subsets of the schema like e.g. lets say you
want to publish a "comments module" reusable across projects that would
install its own subset of tables in the DB, as well as provide functions
(procedures) to create new tables that link comments to other entities on
demand.

The problem with relational databases is that SQL has such poor facilities for
abstractions. Whereas the typical language today has higher order functions
(some even have higher order classes!) stored procedures are quite limited.

------
trjordan
Code thinks in objects and functions and values and pointers.

Databases think in tables and columns and rows and queries and indexes.

If you don't pick an ORM to help manage this translation layer, then you'll
end up re-implementing your own. Maybe this is OK, because yours will be
simpler for quite some time.

What else are you going to do? Stored procedures? Concatenated strings?

~~~
geophile
No, you don't end up re-implementing your own, you end up implementing 10% of
one. The 10% you need. Maybe it maps rows to objects, but doesn't handle
object identity right, because that's not what you need.

The ideal for me is something that manages connections, lets me write the SQL,
and gives me easy access to ResultSets, perhaps in the form of an object.

And yes, stored procedures are extremely useful, especially for minimizing
chatter between the server and the database. Use them appropriately. (Although
I don't see how they are an alternative to ORMs.)

------
Roboprog
Yep. I’m an ORM-hater, too. Feels like “leaky obstruction”, to me.

But there’s got to be some reason people keep embracing it, even if I’m not
one of them.

Possible reasons, which you can rephrase as productivity virtues, if you are
so inclined:

* I don’t spell well

* I don’t test much

* I don’t care how much time and bandwidth it uses

* I only know Java (or C#, or whatever)

Sounds despicable to me, but could be many small scale Enterprise projects, I
guess.

~~~
dsego
That's a load of FUD.

> * I don’t spell well

I don't want to lose minutes or hours debugging a stupid typo.

> * I don’t test much

Writing meaningful tests with my object model without having to care about
bizarre SQL semantics.

> * I don’t care how much time and bandwidth it uses

Because ORM will optimize the queries for me, cache results and perform lazy
loading when possible.

> * I only know Java (or C#, or whatever)

Using the full power of expressive languages to work with my object models,
keep things DRY and reuse code, and have support for different storage engines
and sql flavors.

------
BerislavLopac
My personal gripe about ORMs is that they have two main usage patterns, and
each of them has major drawbacks.

The first pattern, common in frameworks like Django, is to cram the business
logic into the ORM instance objects. This creates a tight coupling between the
two separate concerns (business logic and data persistence), and will cause
problems as soon as the two structures deviate from each other.

The second pattern is to keep the ORM as a purely data storage layer, which is
much more flexible but commonly causes duplication of nearly identical
structures in separate layers.

~~~
ianamartin
That's only a pattern though, and it's not actually coupled to the ORM. It's a
convention. It's a bad habit.

No one should do that.

The second pattern is also awful. Why would you do that.

------
jmnicolas
I learned SQL at school. When I was hired and suggested to use an ORM, the
boss didn't know what an ORM was, so he said no.

I have no problem with SQL, I actually like the language it's very powerful.
However mapping query results to objects is soul crushing.

So a few year ago when I became the sole dev in the company, I decided I would
try Entity Framework (Microsoft .net ORM)on a new project that needed to be
delivered yesterday.

To get even more speed I didn't even design the database as I do usually, I
went with the code first approach. It was magical, so much less work to do.

Then something went wrong with Entity Framework it didn't retrieve the right
data I think (sorry I don't remember what exactly, but it was a show stopper)
and I couldn't find a solution to my problem online.

Then I had to do everything by hand in a rush, I was very late with the
project.

I kind of lost interest in ORMs after that but I thought there should be a way
to map queries to objects. So I started to develop my own micro ORM with
Tuples. I went nowhere fast but while I was searching I discovered that what I
wanted to do already existed and was actually named a micro ORM.

Now I use Dapper (Stackoverflow .net micro ORM) and I'm very satisfied with
it. Its level of magic is relatively low, so if it doesn't work I can quickly
replace it with a manual query without losing much time.

------
cryptica
It amazes me how much consensus there is now about ORMs being an anti-pattern.
I had figured this out over a decade ago; back then ORMs were so common and
popular that even the most senior developers thought I was a fool.

There was literally no choice; I had to work for several companies which were
using ORMs. I dealt with the reality of the industry by specializing on the
front end.

Those were the dark ages for back end development.

Nowadays front end development has taken the lead when it comes to insanity;
React, Babel, GraphQL, Webpack, CoffeeScript and TypeScript... All
tools/frameworks which add negative long term value.

I switched back again to back end development a few years ago to escape the
front end madness... But now back end development is also starting to degrade;
pure functional programming and languages which transpile-to-JavaScript (to
run on Node.js) are the latest diseases. Thankfully back end development is
more fragmented and there is more room for different tools.

If this continues, I will have to give up on software development and switch
to consulting; I will be forced to sell complex (but popular) tools which
create problems for businesses and then offer to sell them a solution to solve
some of the problems which I created. It's not a joke though, business people
really are becoming THAT retarded.

------
kuon
I mostly agree. But lately I've been using Ecto, and I like the approach. It's
mostly an SQL generator. So far it's the sanest approach to ORM I've used.

~~~
honkycat
Agreed. Awesome abstraction that lets you build on top of it while still using
low level SQL when warranted

------
hardwaresofton
ORMs are a leaky abstraction -- there is a sweet spot (mostly basic
queries/relationships) where they provide lots of value, but there is a
veritable infinity of scenarios beyond where they are a hindrance. Also they
tend to introduce _lots_ of hidden complexity when they try to introduce
flexibility to match the landscape.

I'd argue that most of the time what you really need is a query builder with
the right combinators, and maybe some higher order combinators to wrap those
together for even better ergonomics.

For those writing NodeJS one of the best ORMs that I've found is typeorm[0],
check it out.

Here are some reasons I like it:

\- Typescript-first

\- Provides the repository pattern (ex. repository.findOne) & the entity
manager pattern (ex. manager.findOne) so you can use whichever you prefer

\- Annotation-based entity markup

\- Ability to drop down to raw SQL query easily at any time

\- Query building ability, special handling for relation queries, it's even
got support for building with where params like and and or

\- Great documentation

\- Ability to programmatically use the introspection that it does for you
(accessing the metadata from code is trivial)

\- Handles migrations (you can generate them, run them, etc), easy to use
programmatically as well

\- Supports some low level concepts from my own favorite RDBMS, postgres

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

~~~
TylerE
That's why you use something like SQLAlchemy that gives you all the good
parts, but lets you painlessly drop down to native SQL when you have to.

------
honkycat
One of the many reasons I love Elixir Ecto: it is a pretty small abstraction
on top of SQL. Gets out of your way and let's you write SQL in elixir

~~~
cultofmetatron
OMG YES, I LOVEE Ecto!.

it lets you create beautiful sql like declarations

``` payment_types = from(s in Ecto.assoc(order_cycle, :splits)) |> join(:left,
[s], p in assoc(s, :payments)) |> select([s, p], %{ type: p.type, card_type:
p.card_type }) |> Repo.all() ```

its the first database library that makes queries easy to compose together. On
top of that transactions are a snap to put together thanks to Ecto.Multi

------
cmrdporcupine
Codd's relational model on its own is a more beautiful, flexible, and concise
system of data representation than the object oriented model. SQL is a rather
poor implementation of it, but it will do, in most instances, for the sake of
compromise.

What it is missing is a model of _behaviour_.

I've always (ok well, not always, but for the last 15 years probably?) felt
the missing piece in what we call 'middleware' isn't to resolve the impedance
between object orientation and the relational model, but to somehow come up
with a method of representation behaviour/execution for the relational model
itself. And do away with all these hierarchies of classes and whatnot.
Something conceptually like what we call a method/multimethod in OO, but
consistent with the mathematical, set-based lingo of the relational algebra.

But I'm probably not smart enough to figure that out. I hope someone does. I
don't think 'stored procedures' is even close to it. Maybe Datalog has it, but
I haven't grokked it well enough to say.

------
solidsnack9000
One of the biggest problems with ORMs is that they encourage unstructured SQL
access. What queries does the application run? With an ORM, it's impossible to
know, because relation-fronting objects are available everywhere in the code,
and that means any module could be composing or adjusting queries.

A logical thing to do is restrict these kinds of actions to a database module,
which exposes a function for each query we want to run against the database.
This is a lot like the stored procedure model, with the procedures in the app
(and adjustable from the app) instead of in the database. With a structure
like this, it isn't so bad to write all queries as SQL files with some
template parameters. Maybe there is repeated logic but there is usually a SQL
templater for your language that lets you template in table names. There is
abstraction, there is clarity about what code is being run, there is control
over performance, there is syntax highlighting.

------
JimmyRuska
We used a third party tool for mailers and it was having severe performance
problems after moving the server to a cloud based solution.

We confirmed what the DBAs said, the latency was only 34 seconds round trip
and the database was pretty fast. I installed wireshark to see what was going
on. It was querying a table with 80,000 entries, and then for each entry it
would do another, "select ... limit 1" query to get the meta data on each
record. This loop was abstracted away in an ORM and the third party had very
few clients with tables of our size for that particular table.

When it was run on prem it wouldn't take too long, but when it was on cloud it
would take 45 minutes. Insignificant amounts of time being spent thousands of
times ends up being significant time spent, and this can creep up on you as
more things move to be serverless / API driven. A bunch of money and time was
spent on figuring out the source of the issue.

~~~
james_s_tayler
This doesn't fully make sense to me...

I'm kind of curious about what data the select limit 1 queries were returning
that the ORM couldn't get in a single query and had to go back per record. Can
you shed any light on this?

ORMs have a degree of flexibility. It's possible to write n+1 queries
accidentally, especially for developers who are new to the ORM. It's also
often possible to address those issues. Sometimes trivially, sometimes not.

The other thing that comes to mind here is that maybe the query was hitting a
non-covering index and that was triggering the lookups? In that case a
covering index would have fixed the issue?

Curious about this one...

~~~
runin2k1
It is almost certainly someone using lazy-loading when they should have
disabled it for that query.

Though... since it sounds like the query was returning the entire table at
once eager loading will cause issues of another variety at some point.

Either way, I'd chalk it up to bad design rather than bad tools.

------
lemmsjid
I've followed ORM like/hate flamewars as long as I've been in the industry,
and I think I'll be following them for as long as I continue. It's really an
interesting and ultimately irresolvable tug of war between abstraction,
automation, and a number of other issues.

I think people who have used ORMs a long time do not see them as a SQL
replacement or as a total database abstraction layer, but as an automation
tool for CRUD operations, with some capabilities of doing interesting things
with querying, and potentially a methodology for managing database schema and
migrations (depending on the tool). At their best, ORMs can tremendously
reduce one's requirements for unit tests in particular areas because they have
enough structural metadata to typecheck (automatically or compiler time) all
the way down to forms in interfaces.

BUT, without a doubt, ORMs are indeed a tremendously leaky abstraction. That
said, I would argue that every data store interface is, in that sense, a leaky
abstraction. No matter the datastore you're using, be it RDBMS or a
specialized NoSQL / search store, you need to learn the hows and whys of how
it is structured. Not only should a professional learn SQL, but they should
get at least a layperson's knowledge of how the database underneath the SQL
works. However, the argument still stands, because when debugging a complex
ORM query, you are debugging how it puts SQL together, and then you need to
debug what the generated SQL is doing. So you're now multiple layers away from
the actual thing you're managing.

Hence the 'final form' of my day-to-day ORM stance: I like to have an ORM
around because of the massive automation around entity manipulation, but I
think of the ORM in terms of the database and how the database should be
structured, rather than as a generic domain model that happens to be mapped to
some behind-the-scenes database. Furthermore, I believe it is quite valid to
drop down to SQL for the very complicated stuff, especially if you want to
trust to the query planning of the database. Once you do, you've broken the
total safety of the abstraction, hence my ultimately seeing ORMs as automation
tools rather than abstraction interfaces. I have no blame for people who
refuse to utilize them, though I'd argue in a design meeting for their use,
and hope I wouldn't have to take on all the CRUD queries if I lost the
argument, because I'd start writing code to generate them, which then would
become a terribly under-engineered faux-ORM :).

------
carapace
What SQL has taught me: Just learn Prolog.

"I'm seriously you guys." I was recently working with a Prolog-SQLite binding
when it suddenly dawned on me that I should just skip SQL and use the Prolog
engine directly as the DB. Modern Prolog systems can handle millions of
records, for a lot of applications that's all you need. On the other hand they
also have e.g. ODBC bindings.

SWI Prolog can internalize data in flatfiles "Managing external tables for
SWI-Prolog"

[https://www.swi-
prolog.org/pldoc/doc_for?object=section(%27p...](https://www.swi-
prolog.org/pldoc/doc_for?object=section\(%27packages/table.html%27\))

There's also a Prolog graph database coming out in October: TerminusDB.

[https://medium.com/terminusdb](https://medium.com/terminusdb)

------
ne01
It’s like advising someone with an arrow in his heart to not smoke—who cares,
he has a few moments left anyways.

OOP is enough of complexity to kill most projects—and yet we discuss the
dangers of ORM.

“Object-oriented programming offers a sustainable way to write spaghetti
code.” -PG

------
hashkb
SQL just isn't composable. I know this article is old, but these days it's not
black and white. In the space between ORM and raw SQL there are things like
AREL which can save a ton of dev effort without the "impedance mismatch".

~~~
bjoli
Can you elaborate? SQL queries compose just fine, it is just that most
developers don't understand the relational part.

~~~
laszlokorte
Composing in this context means that one part of your application (eg the list
controller) builds part of a query (the select from) and another part (eg the
filter controller) builds another part of the query (the where part) and yet
another part of you application (the paginator) alters there where part, adds
limits and offset and build another query based on the same filter conditions
to calculate the total row count. All that without the different controllers
knowing each others in advance. This is not possible without complicated
string manipulation or building some not-sql-query-algebra.

Some api like prepared statements (supported by the db itself) for building up
complex queries step by step would be nice. Does something like this exist?

~~~
bjoli
As the other answer to you says: subqueries will do that. DBs automagically
unnesting subqueries has been a thing for a long time, so usually you won't
get any performance impact (With a big warning for correlated subqueries).

------
smitty1e
> 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.

I feel the same way about Wiki syntax as ORMs.

While SQLAlchemy is great for getting the DDL done, my SQL-fu is such that I
wind up just using SQLAlchemy as a glorified connection manager while I
construct the SQL strings directly rather than muddy up a sophisticated query
by translating it into Python.

The same holds true for Wiki syntax. If I'm already proficient at HTML, it
buys me precious little to use this 'easier' syntax if sometimes I'm in
Redmine, sometimes I'm in Confluence, or wherever else I land.

Just let me code the page already!

~~~
philwelch
In terms of wiki syntax, HTML is just so damned noisy that I'm fine with
Markdown--but mostly because Markdown is fairly standardized and I don't have
to learn a totally different syntax for everything.

~~~
smitty1e
"Fairly"

So I spend as much time faffing about with how this particular tool does
anchors as I would just doing the "a" tag outright in HTML.

~~~
philwelch
The real question is why aren’t you using the WYSIWYG editor most wikis have
these days.

------
headmelted
_In these cases, I’ve elected to write queries using a templating system and
describe the tables using the ORM. I get the convenience of an application
level description of the table with direct use of SQL. It’s a lot less trouble
than anything else I’ve used so far._

I was with the spirit of the article save for this.

Recently I've been developing some work for one of my client's in the .NET
world. There had been ongoing discussions about developers wanting to use
Entity Framework as an ORM vs. using stored procedures.

The client already uses SQL projects and DACPACs (effectively a system for
specifying the DB in SQL then diffing it between versions to alter the
database).

The arguments for and against ORM were largely naive from each side of the
fence - the developers wanted strong typing (doesn't require an ORM) while the
DBAs wanted to be able to review query execution and suggest changes if
there's an issue (you can see the generated SQL for the ORM).

The solution I came up with was to use reflection on the build server as part
of the CD process to map the inputs and outputs of the stored procedures into
strongly-typed C# methods and objects, generate code for it, and
build/pack/push a nuget package back to our feed. It's similar to what EF
provides without maintaining the EDMX (which we can do as we don't need to
cover every possible data access scenario like EF does). It means that once
the database project is checked and the build green-lights, an updated Nuget
package that constitutes the DAL is automatically waiting on the internal
Nuget feed.

I've found it gives us the best of both worlds. We can do anything we need to
in t-SQL and the C# wrapper only cares about the ultimate input/output. I can
force the use of parameter sanitization in the wrapper (by not providing any
other way to call the procs), and the DBAs can review/amend whatever they want
without the developers needing to change their code as the interfaces don't
break.

We also don't have to write DAL boilerplate or worry about inexperienced
developers getting it wrong and opening injection attack surfaces at the DAL
layer.

There's usually a solution to your use case if you look for it is my point.

~~~
Shorel
Make it a product and sell it/github it.

~~~
headmelted
I'd happily github it if it would fit more use cases, is it something you
think others would be interested in?

~~~
Shorel
One or two blog articles featured here, clearly explaining the difference in
philosophy/architecture, and the advantages it has over ORMs, and you will
have an audience.

------
nikolasburk
This is a great article, I've really enjoyed reading it! I want to react to a
few of the points made in the post, and point out some potential techniques
for solving them!

Most ORMs are heavily inspired by object-oriented programming which fosters
the object-relational impedance mismatch. In addition to logic for storing and
retrieving data, models often also implement business logic (ActiveRecord is a
good example here). This makes for bloated and complex objects that are
difficult to work with in the application. A solution can be to lower the
level of abstraction and use a more lightweight query builder (like knex.js
for Node.js). These kind of tools give you more control in constructing your
queries as well as the ability to optimize them.

These tools still require you to understand quite a bit of SQL though and the
productivity leap compared to writing manual SQL isn't as high. I believe that
query builders are the best compromise we have today for accessing a database
from an application.

Regarding the dual schema dangers that are mentioned by the author, I strongly
believe that these can be alleviated using code generation tooling that helps
to keep your database in sync with your application models (approaches like
SQLBoiler in Go where application code is generated based on the database
schema are an example here).

------
psetq
These things don't have to be mutually exclusive. Know your tools and when to
use them.

~~~
commandlinefan
In my experience, though, the cases where ORM's seem to be a good fit end up
growing to cases where they're no longer a good fit - and getting away from
them ends up hurting more than just starting without one to begin with.

------
ummonk
You have to distinguish between heavyweight ORMs that impose their own data
model and lightweight ORMs which just provide transparent query builders and
static typing for query calls from your given programming language.

The former is frustrating to use in my experience but the latter tends to make
SQL easier to work with without taking away any of the power and
expressiveness of SQL.

------
noobquestion81
Composible ORMs (for me, AR+Arel, this was about 4 yrs back) let me write some
of the most magical, legible query code I have ever seen. I could create and
reason about subqueries with highly dynamic filter options and pass them
around and wrap them with outer queries, all in. perfectly readable code.
Glorious.

Of course, for complex analysis like in specific periodic data reports, where
the filter parameters are mostly known and don’t change constantly with the
model, there are diminishing returns to this.

However, when you start writing code that writes other code using string
concatenation, big-picture wise I think you are doing it wrong. Look at HTML
and how it has developed towards client heavy apps as other example. Encoding
is hard, dangerous stuff, and having a library do it for you (like the w3c DOM
APIs, or higher abstractions like react) can be invaluable and can make the
difference between spaghetti code and gorgeously understandable functional
statements.

------
brettnak
I think a lot of this is just some weird expectations. You cannot _use_ a
database without knowing sql. ORMs work great, but you have to know sql to use
them properly. What if you want to load a relationship with a join? What if
you want only to load certain columns?

I would consider part of the profession to be knowing sql. This whole "orms
are bad / long live the orm" split attitude is ridiculous. ORMs are fine. Some
orms are bad, just like some code is bad, and some frameworks are poorly
thought out. I have worked with developers in the past, who I _desparately_
wish were forced to use a good ORM like ActiveRecord, so that they could
understand just how far you can get with a solid and good pattern. I've also
worked with developers in the past who used an ORM like a 15kg sledgehammer,
and had absolutely no idea what was going on.

------
methehack
Have any of the naysayers used rails' ActiveRecord? I used to not like ORMs
much, but honestly it's a whole different ballgame. Very easy to slip into SQL
for bits (or big pieces) as well. People don't really complain about in the
rails world (my respect only increases) and it has a lot of miles.

------
otakucode
In my prior job, I worked with a few (really skilled) interns that were just
finishing up their CS degrees. I found out that they didn't learn any SQL at
all and their interactions with databases was severely limited overall. This
really surprised me, as I had a whole databases course when I went through
college (different school, but in the same state, just 20 years earlier). Is
not teaching SQL or relational database concepts no longer common at colleges?
I should mention that the school they went to otherwise was excellent and
taught a great many things my own schooling never touched on and I had to pick
them up later myself. I'm just curious if it's a matter of just that school,
or if other courses have been prioritized across the academic field generally
for some reason.

------
jtdev
SQL is to much software development what rivets, bolts, or even beams are to
the construction of skyscrapers... if you’re going to be in the software
business, you should really get comfortable with SQL, after which you’ll
likely find the friction and headaches caused by ORMs to be a deal breaker.

------
orware
I've looked at ORMS mostly on the periphery for years and years now, but
haven't really been able to use them in our environment and this might be
similar to the situation for others as well.

One thing I noticed early on, is that even using a query builder complicated
things a bit as well since it meant that the original SQL string was then
broken up into multiple method calls to construct the SQL string. Since the
full query wasn't built until the very end, this meant that a helper line
needed to be added into the code to get that full SQL query if any issues were
found down the road and then taken over into our program of choice (in this
case, SQL Developer, since we're dealing with Oracle queries in most cases)
and running our additional tests over there.

Our campus ERP is pretty complicated table-wise, so our queries (developed
either by myself or by our systems analysts) tend to be fairly complex,
requiring multiple joins and other complex logic that I feel would have a very
difficult time being translated into an ORM format.

A query builder is still somewhat usable, but for the most part I just stick
to mostly straight up SQL queries and make use of prepared statements to help
avoid SQL injection and keep life simple so it's easier to move back and forth
between the application side and testing the query on the database side :-).

On a side note, I'm not sure if it's mentioned here in the discussion (and it
might be less of an issue now than in years past) but I have noted some ORM
usage doesn't always choose the most efficient mechanism for things (e.g.
pulling results using small, but relatively expensive multiple SQL queries
rather than being contextually savvy enough to know that a set-based query
would be better suited to retrieve the entire set of results at once).

Overall though, working with SQL and coming up with solutions for things is
probably one of the funner aspects of my current position and while using an
ORM has sounded like fun in the past, it just hasn't seemed like it's the best
fit for our particular workflow/environment.

------
Animats
Rather than having a full ORM, a SQL system that didn't put in variables by
embedding strings would be useful. You can almost do this. SQL has variables,
but they're more persistent than needed for this.

    
    
      SELECT a,b,c FROM tab WHERE a=@mysearchkey;
    

An API should look something like

    
    
       result = sql->command("SELECT a,b,c FROM tab WHERE a=@mysearchkey;",
           {"@mysearchkey": val })
    

and the result should be a key/value form. In some languages, you might get a
typed structure back. No more string escapes. No more forgetting the string
escapes. If you required that the command had to be a constant, SQL injection
attacks would be a thing of the past.

~~~
wvenable
Almost every single native RDBMS API provides parameterized queries / prepared
statements where the parameters are sent separately from the query text.
Here's one from MySQL:

[https://dev.mysql.com/doc/refman/5.7/en/mysql-stmt-bind-
para...](https://dev.mysql.com/doc/refman/5.7/en/mysql-stmt-bind-param.html)

String escapes should have been dead a few decades ago -- I don't think any
modern platform requires it; they all support parameterized queries natively.

------
gwn7
> I’ve found that reflection techniques to get the data definition are not
> worth it and I succumb to managing the redundancy of data definitons in two
> places.

Not sure if much has changed regarding the "reflection techniques" since 2014,
but I think Postgres does a fine job reflecting. (Don't know much about
others)

For example my favorite library Massive.js (a data mapper) depends completely
on reflection. It allows its users to access tables, views, functions,
extensions, and even enum types from its Javascript API, without the need for
models. This completely solved the data definition redundancy problem for me.

I even made a small layer on top of it to get the constraint information too
using the information_schema, and everything is working like a charm.

------
QuadrupleA
As a refugee of Hibernate etc. I definitely agree with this. Python in
particular already has data types that map nicely to relational data - dict,
list, tuple, iterators - so mixing SQL calls directly in code works great.

Plus you get full access to the particular database's features, direct control
& understanding of what's being executed when, easier query & performance
tuning, no special 2nd pseudo-sql dialect to learn, no big extra stack of
leaky abstractions to troubleshoot, etc.

One of the big pitches of ORMs is "you can change your DBMS mid project!"
Which sounds cool and has its occasional applications but is something I've
never actually needed in over 20 years of development.

~~~
ratww
I worked in some projects where we had to support multiple databases. Entity
Framework and a few Database Views helped us keep it agnostic. It was very
common in the pre-cloud enterprise world.

Uber did it recently, too. They changed from Postgres to MySQL. But I don't
know if ORMs helped them or not.

------
kizer
There is friction here because it’s at the meeting of two languages instead of
implementation-interface, or implementation-service. People either try to hide
the underlying SQL entirely on the one pole, or attempt to embed a SQL-like
DSL within the primary language. I think that modern programming language
ought to offer first class support for embedding other languages or DSLs - an
idea behind Language Oriented Programming.

SQL offers the domain appropriate syntax, while the “host” language allows
access to in-scope variables, functions, etc.

Of course there would be some more work in allowing a “mix” of two syntaxes.
Another option is a query language as a subset of the language, sort of like
C# and Linq.

------
braindongle
"...They are an acceptable way to represent a data definition, but a poor way
to write queries and a bad way to store object state." Rails perspective: Pump
your brakes there, friend. This claim depends completely on your application's
needs. I write web apps for research that don't need to scale and don't need
lighting performance. I have built apps that ended up needing some optimized-
SQL-versus-ActiveRecord experimentation, and _sometimes_ , writing raw queries
is certainly worth it. AR has been evolving for 15 years, is extremely useful,
and all that is needed for _many_ use cases. We're not all trying to do the
same thing.

------
xienze
I feel like there should be a better term to distinguish an ORM framework from
the kind of ORM that pretty much every competent developer uses (a hand-rolled
one).

These “you don’t need an ORM!!!” posts always annoy me because even if you
don’t think you’re using an ORM, you probably are. You aren’t mixing raw SQL
statements into your business logic, are you? Probably not. You’re probably
writing wrapper classes that “map” the “relational” data into the “objects”
that your business logic uses, otherwise known as an ORM.

But there’s a big difference between that and an ORM framework that generates
SQL. Realistically speaking, you _do_ need an ORM in all cases, but not
necessarily an ORM framework.

------
amedvednikov
I agree. Unfortunately each database has its own SQL dialect with small
differences, so migrating to a different database becomes a problem when using
plain SQL.

You also lose compile-time checks.

I'm trying to combine the best of the two approaches in the V language. It has
a built-in ORM that uses SQL-like syntax:

    
    
      uk_customers := db.select from Customer where country == 'uk' && nr_orders > 0
      println(uk_customers.len)
      for customer in uk_customers {
          println('id: $customer.id; name: $customer.name')
      }
    

[https://vlang.io/docs#orm](https://vlang.io/docs#orm)

~~~
echelon
The solution is to use something closer to a compile-time SQL or DSL, such as
jOOQ or SqlAlchemy (sans the ORM pieces). These are terrific technologies that
improve upon SQLs weaknesses and achieve some level of portability.

------
goto11
The "ORM or not" discussion quickly runs off track, because there are so many
different ORMs with different features and working on different levels of
abstraction.

Recently I have most experience with EF Core (from .net) which does not have
the "select * " issue the article describe (you can select whole entities, but
you can also project individual columns if you want.) It uses Linq for
expressing queries, which means it is _more_ concise end express relational
algebra clearer than SQL.

On the other hand I have tried Hibernate, which had such a verbose and
cumbersome query builder syntax that it made you long for plain SQL.

------
redact207
I've found that DDD and a read/write separation of concerns simplifies things
greatly.

On the write side, the ORM just returns an aggregate, usually based on the PK
of the root. Thats's trivial for any ORM.

On the read side, simple queries can be modelled with ORM syntax if you're
just trying to fetch a graph of existing objects. Complex queries can be
returned with raw SQL that map to custom read models. I tend to wrap both
styles in integration tests that ensure the query logic doesn't change, and
that the actual mappings don't break.

Both ORMs and SQL have their usages, and they're not mutually exclusive.

------
whitehouse3
> In these cases, I’ve elected to write queries using a templating system and
> describe the tables using the ORM. I get the convenience of an application
> level description of the table with direct use of SQL. It’s a lot less
> trouble than anything else I’ve used so far.

This feels like good balance. I want to express my database schema via OO
classes. It eases db migrations as the application grows if you use tools like
Alembic (same developers as SQLAlchemy).

But use care to avoid SQL injection risk with template queries. SQLAlchemy
makes this easy using .bindparams, as does .NET via SqlCommand.

------
codeulike
I'm old enough to remember when pretty much every business app developer knew
SQL. ORMs didn't arrive because people didn't want to learn SQL, they arrived
because there was a mismatch between 'relational database' and the emerging
trend to 'put your business logic in an OO-powered independent middleware
layer'. Unless you want to code all your logic into stored procedures and
whatnot, you end up needing some sort of abstraction of the database structure
that can manage business rules for you.

------
EamonnMR
ORMs can be nice if you're using the database for a single application in a
very conventional CRUD pattern, but that's about it. And that's if you have
the luxury of building a new DB to go with your new App. If you find yourself
doing anything interesting (ie not just CRUDing single rows by ID) you should
not only learn SQL, you really need to become familiar with databases. The
promise of ORMs (at least when I first encountered them) was "you're not going
to have to do SQL" but in reality, you will.

------
codr7
Me thinks someone needs to have a go at maintaining a 2Mloc accounting package
built using only embedded SQL statements and stored procedures, including
migrating the whole mess between major database vendors.

I guess one advantage is you have to learn, it but I really prefer some kind
of ORM for more mundane repetitive CRUD. More to get a structured (ha!)
interface between the database and the application than for the convenience.

I do wish most of them would stop insisting on putting the cart in front of
the horse and make code the primary representation.

~~~
ianamartin
I deal with that. Millions of lines of SQL stored procs.

It's awful, but not anything an ORM could help with.

~~~
codr7
Not all of it, but I'm sure most statements follow some kind of CRUD pattern.

Getting that out of the way is exactly what an ORM can help with.

------
time0ut
I've found that you need to first understand basic SQL very well. Then once
you've mastered the ORM, it can dramatically improve your productivity. But
you need to understand both to reason about what you are doing.

ORMs have their place. Simple CRUD microservice? An ORM can help tremendously.
Complex reporting system? Probably not the right tool.

Be careful though. I've run into issues where once you're scaled way up and
need the ORM to get out of the way, it can be a beast to detangle if you
weren't disciplined.

------
ineedasername
I work a lot with SQL, not at all ORM's. (I'm on the data extraction,
modeling, analysis side). So what's the counter argument here, where to ORM's
excel?

~~~
ojglees
The counter-argument is that for a lot of applications you have a bunch of
tables and a bunch of objects and there's a lot of repetitive, boring code to
write just to get simple CRUD working. The hobby Java ORM tool I wrote
([http://hrorm.org](http://hrorm.org)) only does simple CRUD. Everything else
it leaves to you using JDBC and SQL, since the problems being discussed here
are real. But for managing persistence of simple object models, I think ORMs
are a big time saver.

------
danenania
The way I'd put it is: "learn SQL _then_ use an ORM". If you understand
exactly what SQL is being generated and what the implications are, you can use
an ORM effectively and benefit from it in many ways (readability, security,
composability, etc.). You'll also know when to use it and when it's better to
just drop down into SQL. It's when people don't really know what the ORM is
doing underneath that they get in trouble.

~~~
GiorgioG
And therein lies the problem. These ORMs (by and large) have become so complex
and abstract that it's nearly impossible to understand what they're doing
underneath (I'm looking at you Entity Framework (in .NET))

You wind up having to have a deep understanding of the ORM and SQL, at which
point I would argue why bother with the ORM at all? For .NET, I'm much happier
with a very very thin layer over the base .NET database client library called
Dapper. Unfortunately most shops use Entity Framework by default.

~~~
danenania
"You wind up having to have a deep understanding of the ORM and SQL, at which
point I would argue why bother with the ORM at all?"

I agree that ORMs are often too complicated, but imho it's still worth it to
learn and use one. I'm using ORM loosely though in that I don't think heavy
'Object' and 'Mapping' layers are so important. Mainly you just need a
reasonable way to parameterize and compose queries so you can avoid injection
and deduplicate logic in a sane way. So I guess my argument is more "use a
library" than "use an ORM".

In my experience everyone who says they'll use pure SQL ends up adding gnarly
string building logic at some point because the duplication gets out of
control. It's better to just find a decent lib that works at whatever level of
abstraction you're comfortable with and use that.

------
zmmmmm
Writing SQL is a lot of work, not type safe (in the programming language
static typing sense), often not re-usable, and hard to test. There are
libraries that solve these parts for you (or at least help with them). For me
the optimum is libraries that do only these parts but actively try to NOT
introduce any new layer of abstraction over the database model itself. The
problem is that in this whole ORM discussion, the baby gets thrown out with
the bathwater.

~~~
brianpan
I’ve had success in Java with Freemarker. It’s a templating engine so you’re
just producing strings. But you can add typing; use variables, loops, and
conditionals; and include other templates for sub query reuse.

IntelliJ has a plug-in.

It’s a nice compromise between crafting strings vs SQL DSL.

As for the objects, you can get very far with everything being a Map until you
_really_ need to add a class or two. :)

~~~
zmmmmm
Absolutely. One of the things I like about Groovy is that it solves the other
half of that equation because it has the built in map constructor and built in
SQL APIs that work with it. So you can actually do

    
    
        class Foo {
            Long id
            String name
            ...
        }
    
        Foo foo = new Foo(db.firstRow("select * from foo limit 1"))
    

And it all just works if the database columns match the fields of Foo. And if
you just do

    
    
        Foo foo = new Foo(db.firstRow("select name from foo limit 1"))
    

Then you get a `foo` with only the name populated, etc, and you got type
safety, easy direct, efficient SQL queries and the ability to test your code
without hitting the database, all without imposing any "leaky" abstractions
that cause all the problems.

------
gigatexal
ORMs makes it easy to stand up databases and make doing migrations a lot
easier. That being said it’s my opinion that after using them in the person
space the best ones do the boring stuff but then are best used a as query
building tools so you can have some control over what the query looks like and
actually does. That being said if done right I prefer thin abstractions over
my data layer and raw SQL — I don’t care for the data layer to be a black box

------
anthony_doan
You should learn SQL first then ORM afterward.

Most modern ORM have escape hatch to let you write raw SQL.

Your favorite web dev language will have a dominant ORM. C#, Python, Elixir,
Ruby all have a popular ORM that works within a popular framework.

ORM will make working with database and web framework easier.

I do agree with post that querying in ORM can be hard and sometime not
possible without raw SQL. Writing query for Ecto, Elixir's most popular ORM,
can be tricky when you want to do dynamic query.

------
james_s_tayler
What ORMs have taught me:

Use an appropriate combination of ORMs and raw SQL.

------
nurettin
ORM is like a regex. When you use it, you've got two problems. The problem
with having two problems is that we love solving problems! Yay OCD! So if an
ORM doesn't let us do something, instead of just getting the work done with a
couple of quoted SQL strings, we try bending the ORM to our will. I know I've
done this with the entity framework. And when we do that, the perpetual debate
starts all over again.

~~~
cutler
Why do I have two problems if I use a regex? "Programming Perl" and "Mastering
Regular Expressions" were what drew me into programming in the early 2000s.
Whilst ORMs are optional regular expressions are not so I don't get your
point.

~~~
nurettin
Pretty sure regex is not the only answer to parsing problems. Maybe it is for
someone who has preferred using it for the past 15 years.

~~~
cutler
Ok, what's your alternative for validating an email address?

~~~
nurettin
To use a parser that can validate RFC5322 p 3.4.1

~~~
cutler
A parser such as ....?

------
chousuke
I think that my primary issue with ORMs is that they often allow you to code
as if the database doesn't exist, instead of explicitly structuring code to
handle network and database failures at the points where you deal with
external data.

It is a problem if potentially _any_ part of your program can just fail, and
that happens if you allow magic ORM-objects to leak onto code paths that
aren't written to handle them.

------
dokka
In my career, I have used Entity Framework, Active Record, Sequelize.js and
Hibernate. I haven't had a problem with any of them. Especially not with
postgres and mysql. I've worked in teams of 10+ and personal projects that
I've completed in a couple of days. My advice is to only use the best of
ORM's. I won't use an ORM unless it's at least 5 years old.

------
floki999
Agree with the article - always found ORM’s over-engineered and too
opinionated for my liking. I’ll stick to SQL and keep total control thanks.

------
ydnaclementine
Personally, I feel like a very similar article could have been written called
'What CoffeeScript has taught me: just learn Javascript'.

I have nothing against coffee or typescript (and other alternatives) and think
they're very useful, but at the end of the day it's really just javascript.
But I guess you could make that argument about anything until you get down to
machine code.

------
calvinchengx
This is a false dichotomy isn't it?

Most ORMs allow the developer to bypass the ORM and use raw SQL when the need
arises, so I don't really see the point of avoiding ORMs.

Across the lifespan of a project, in early stages, a developer would discover
that ORMs provide code maintainability and depend heavily on it.

As project requirements increase in complexity, raw SQLs will be required
because of performance reasons.

------
kyllo
Learn SQL, yes, and then use your ORM as a handy SQL generator library because
life is short and you do not want to write that much SQL.

------
talkingtab
For simple cases it is almost always a good decision to use an ORM if you
don't know SQL. The question is whether and how long the ORM shoe will
continue to fit. Databases are essential for many use cases so the chances are
that an ORM will not always work. So decide on that basis when to learn SQL.
Maybe not today, maybe not this project.

The other issue is how central

------
NicoJuicy
I use EF, it helps me with migrations and quick coding.

If I need something extra, I just write the SQL.

All in all, in general, I program much faster than other people who don't have
this setup/flow.

I recreated an API someone was working on for 2-3 hours in 9 minutes. This was
the "senior" developer of 55 years old.

Including creating the project, creating the migrations, creating the api and
a small test.

------
RangerScience
For anything complex, definitely. For anything simple, eh.

It also feels to me like he's talking about a _specific_ ORM - I know
ActiveRecord has it's fair share of issues, but from what I know of AR usage
and implementation, it either doesn't do what he's (legitimately!) complaining
about or does do it in the way he's suggesting.

~~~
blackflame7000
I think he's talking about Hibernate.

~~~
RangerScience
Ah, Java. Tooting my own horn a bit, I wrote a custom YAML/XML/JSON parser for
C# specifically because the existing parsers required me to turn the data into
objects before being able to anything at all, rather than allowing me to
interact with the data just as data. Say, cleaning up and/or validating the
data.

This is a common problem I've run into in strongly typed languages; can't
"just have some data", have to have an object :/

[https://www.newtonsoft.com/json](https://www.newtonsoft.com/json) vs
[https://github.com/rangerscience/maptionary](https://github.com/rangerscience/maptionary)

------
leowoo91
Using both should be fine.. SQL alone encourages early optimization that might
slow down the development, especially when you don't have the complete non-
functional requirements. It sounds like a pitfall to compare it against using
ORM. The way I see it, ORM stands good when it's understood as an additional
capability.

------
neilobremski
Whether or not you love/hate ORM, I think the advice at the very end is still
applicable: "If you’re using an RDBMS, bite the bullet and learn SQL." This
knowledge will help you write SQL if that's what you're doing or debug the SQL
that the ORM writes for you. Either way ... learning SQL is a good thing!

------
jakedub4d2
Working with SQL was nicer in PHP. It gets more difficult for some reason in
Node/JS. SQL injection would always be a concern by default when writing raw
queries. Though I think I could avoid writing flawed queries/endpoints, it's
harder to expect that from a team at scale. #foodforthot

------
karmicthreat
ORMs have taught me to not rely on ORMs for critical storage. I have one
product that uses ruby DataMapper. With my particular product (embedded
industrial equipment) it’s terrible. I have to constantly fight it to correct
MySQL problems.

If they had just used basic sql it would be much easier to refactor.

------
elamje
What are some good rule-of-thumbs for when to use ORM vs SQL?

I have worked with SQLAlchemy and Entity Framework, before and like them, but
haven’t been able to find that magical demarcation line for when to go raw
SQL.

Does anyone have basic rules they use for determining this? Applicable to MVP
or enterprise level products

~~~
nouveaux
The more joins or when you need more performance, drop into SQL.

SQLAlchemy's expression language is a good middle ground. I will start there
if I need to do more than a couple of joins.

As some people have noted here, I think the biggest problem is that people who
only know ORMs will have trouble because their understanding of the database
will be limited by their lack of SQL knowledge.

------
astine
An ORM is not a substitute for learning SQL. It's just a nice tool for more
tightly integrating your database and your application code. An ORM can make
you like much easier, but if you don't know SQL or how a relational database
works, your ORM isn't going to save you.

------
zem
I don't want an orm to keep from learning SQL, I want one to save me from
having to write reams of boilerplate code copying fields one at a time from an
SQL result to my language objects/structs/etc and then back again when I want
to save them to the DB.

------
jackschultz
When I started web work ~9 years ago, it was with Rails and ActiveRecord,
which turns out to be incredibly good for basic queries and pretty basic apps.
So good to the point where I never bothered to go too far into SQL until years
later, which was a mistake.

When doing work in python, I don't feel it has a comparable ORM, to where I
kind of write my own files that have things like finders and updaters and
creators. In most cases, I've found it's much better than SQLAlchemy. Dealing
with joins, and things like math, meaning averages, sums, distributions,
division, is so much better to be done in the query rather than more basic
queries and looping through the results.

There are of course cases like injections to look at, but lots of things I
have are calculations of data and showing it, so we're able to handle it with
raw queries. Also, ActiveRecord has ways to enforce no injections.

In lots of cases, we've found that using an ORM to start, finding slowness,
and moving towards raw queries is a great way to go.

~~~
thenewnewguy
Current rails developer here, I've found that using ActiveRecord for basic
queries and moving to SQL for more complex stuff to be a very good
combination.

I agree that ORM users should learn SQL, but SQL and an ORM are not mutually
exclusive.

~~~
RangerScience
Yep, also current rails dev here and this is also what I do :)

Learning SQL with an ORM does remind me a bit of learning memory management
with a garbage collector: helps you make better choices as to how you put
things together, even if you never directly use the know-how.

~~~
look_lookatme
Good to see y'all Rails developers out there, enjoying work and getting things
done.

~~~
RangerScience
Ayup :) Rails ain't dead, it's just mature.

------
toasterlovin
FWIW, I think the sweet spot for most web apps is to use an ORM on top of
database views. This gives you the power and performance of SQL, but avoids
the impedance mismatch of the ORM, since you’re defining what data will be on
your objects at the database level.

------
holri
ORMs often implement only the lowest common denominator of all supported
databases. Easy changing databases is in a free software world seldom a
necessary requirement. If you use a advanced feature rich database often a lot
of it is not supported by the ORM.

------
ReD_CoDE
I have four questions related to SQL

1\. PostgreSQL or MySQL? And why?

2\. Is it possible to build a hybrid database schema? For example,
SQLite+JSON?

3\. Is it possible to convert XML(XMI) schema to SQLite schema automatically?

4\. Is it possible to build a custom file format based on SQLite or hybrid one
based on SQLite+JSON?

------
gfiorav
I knew sql before using ORMs. It can never substitute that.

What it can do is abstract most common operations to do with your dB, and also
help with mocking some tests.

I really don’t see the point in these articles. It’s not supposed to save you
from learning SQL

------
mmusc
I don't know.. when starting out or prototyping a new feature I find ORMS
useful for moving fast, and the performance good enough. You can always slowly
migrate to pure sql for critical parts of the system.

------
Mountain_Skies
What happened to object databases? They seemed to have a brief moment in the
spotlight during the 90s but haven't made much noise since. Bad product or
just too much inertia with relational databases?

------
sergiotapia
Just use a mixture of both, why the frothing at the mouth from either side?

~~~
ratww
Yeah, I don't understand why developers feel they should use one over the
other in every single case.

I guess there's a certain appeal to only using nails or only using screws, but
at some point it doesn't make sense to compromise the quality of your code
over ideological purity.

------
lukaslalinsky
Using ORM if you don't know SQL is a recipe for disaster. And after you learn
SQL, you will likely decide to go back to ORMs, because now you know how to
use it efficiently to save your time.

------
deepak1990
[https://gadgetsreviews.org/walmart-black-friday-sales-
deals/](https://gadgetsreviews.org/walmart-black-friday-sales-deals/)

------
Koshkin
The thang is, what is an object in the context of the relational model has
been generally grossly misunderstood. For a detailed analysis, see C. J.
Date’s _The Third Manifesto_.

------
danschumann
I pretty much agree... I roll my own ORM usually, though... mostly for
niceties like firstOrCreate, and such. I always keep a sense of the actual
MySQL being ran behind the scenes.

------
xxxpupugo
Can't we all agree ORMs are just leaky wrappers of SQL? Almost all of them
need a backdrop workaround to issue raw SQL to the backend DB to avoid being a
blocker at some point.

------
dsego
> if you need to know SQL, just use SQL

Are ORMs leaky abstraction? Absolutely. Is this the reason to avoid them? Not
even slightly. ORMs provide plenty of ergonomic benefits over SQL.

------
peteretep
I used SQLAlchemy and would not reach for it again, but you can prise
DBIx::Class from my cold dead fingers, so I think _which_ ORM is an important
consideration too.

~~~
mst
Every time I hear somebody say that I grin.

Fun things are coming soon btw, I finally figured out a couple of design
problems that have been annoying me for years and the code is close to stable
:)

------
brlewis
The article gives specific examples of difficulties using an ORM. It would be
nice if people advocating ORMs here gave specific examples of how to overcome
them.

------
tarsinge
My experience is that ORM are great for avoiding boilerplate IF you already
know SQL and don’t consider them as an abstraction layer but as syntax sugar.

------
ianamartin
ORMs were never about not using or not knowing SQL. They are a convenience for
people who already do.

If you don't know SQL, don't interact with an SQL database.

------
elchief
An ORM is pretty darn useful for CRUD

Use a query tool like QueryDSL or Jooq for querying.

It's not surprising that you fail if you don't use the right tool for the job

------
guggle
Apps should not have access to the underlying schema and should just interact
with the database through stored procs or views anyway.

------
qaq
A good alternative is libs like [https://pugsql.org/](https://pugsql.org/)

------
tenroc2o0o
It seems like most of the points made in this article are either resolved by
or don’t apply to Entitiy Framework and LINQ?

------
jwmoz
Having to use Doctrine with Symfony and PHP was genuinely one of the main
reasons I quit using PHP and went with Python.

------
Koshkin
I’ve seen organizations that had ORM _groups_. You had to file a formal
request when you needed to map a column.

------
lichtenberger
By the way, not directly related, but I wonder if there are at least libraries
to build type safe XQuery statements?

------
fortran77
I just need something that takes the result from the query and puts it in a
language-compatible data structure.

------
jbogard
Just learn both? Use ORM for writes, SQL for reads, if you need to. ORMs do
not obviate the need to learn SQL.

------
Koshkin
ORM or not ORM, LINQ to SQL is amazing!

------
wolco
Active record is a great pattern.

Where things fall apart is when you want to do things like a subquery on a
select field.

~~~
dsego
Like this? [https://laravel.com/docs/6.x/eloquent#advanced-
subqueries](https://laravel.com/docs/6.x/eloquent#advanced-subqueries)

Seems more like a limitation of particular implementation than a fundamental
problem with the pattern.

------
hackerbabz
Is it so hard to say ORMs are great for 90% of grunt work but sometimes you
need to write your own SQL?

------
qaq
One of the problems with many ORMs is they are dumbed down and try to support
multiple RDBMs.

------
vladsanchez
I said it once, I'll say it forever: ORMs are people who don't know SQL.

------
foobar_
Time and time again you will poop projects promise utopia but fail miserably.

------
dictatorsunion
Isnt SQL backed by Relational Algebra, which is why it’s so flexible?

------
known
In SQL we can't move the cursor back and forth with Index;

------
breatheoften
Query builders are awesome. Orms really really aren’t ...

------
buboard
ORMs look like sonething that violates Occam s razor

------
rodmena
it's obvious. ORMs are result of those OO people who want to make even their
carpet Object Oriented.

------
SQL2219
ORM=Rube Goldberg Machine?

------
cryptonector
Just use YesSQL.

------
dfilppi
Amen brother

------
Isinlor
If you put unnecessary abstraction in front of perfectly good solution the you
will end up with problems.

Object oriented programing is about clearly expressing business logic. There
is no complex business logic in dumping big tables of data.

So, it's not about ORMs, it is object oriented programing that is poor fit for
doing reporting.

Relational databases, declarative SQL, functional pure programming are good
solutions for reporting.

And you should certainly learn SQL if you want to do any applications with ORM
or without. I recommend Joe Celko's SQL for Smarties: Advanced SQL
Programming.

Object oriented languages shine when there is a need to create a precise
language that facilitates fast and robust communication between domain experts
and developers. In situations where you have little data, but a lot of
intricate logic. This is where relational databases are simply no good.

Relational algebra and SQL is not a very expressive natural language. SQL
limits your vocabulary to 4-5 verbs unless you start writing procedural code
in procedures etc. but SQL is not a good procedural language. Relational
databases are a solution to specific technical problems of scale, execution
speed, atomicity, consistency, isolation, and durability (ACID). They excel at
that, not at communicating intention.

You should use ORMs (preferably Data Mapper) if your goal is to solve problem
of expressing complex domain specific logic. You use relational databases in
that situation because they just work. Data Mapper allows you to isolate your
domain model from tricky technical aspects of data storage like indexing
and/or not corrupting files during power outage. ORM works very well as long
as you will actually be able to ignore technical aspects of speed etc. in your
domain model.

You can do the data mapping, querying, migrations, and all this technical
cruft manually with a handwritten SQL if you want, but SQL certainly will not
address very well the goal of creating an expressive domain model that
facilitates robust communication between developers and business experts.

So, given that we addressed the elephant in the room, some other points:

Dual schema dangers: "I much prefer to keep the data definition in the
database and read it into the application."

That's perfectly good solution, if you have a lot of data and amount of logic
related to data is minimal. If you have little data and a lot of logic you
will not be able to store data definition exclusively in database whether you
are using ORM or not. Even if you just store SQL queries in your code, then
you do store schema structure with SQL, just not explicitly, but implicitly.

Data migrations, rolling release etc. are tricky whatever you do. I use my ORM
to dump me SQL that is needed to move structure from point A to point B, since
ORM knows the schema it can do that, and then I manually adjust it as needed
to massage data etc.

Identities: Not sure if I follow here. It seems like you have issues with auto
incremented ids. Auto incremented ids with ORMs are annoying, indeed. My
advice is to use UUID generated in the code, then you will have no need to hit
database. Also a side note here: if you use ORMs, do not use anything that has
any real world meaning for ids. Use UUID, so that you are sure that no domain
expert will want to mess with that.

Transactions: The same situation as with speed and migrations. Getting
technical details of transactions is hard whether you use ORM or not. You can
use stored procedures, but I'm curious what will you do e.g. when you will
need external REST API to get in sync or to copy user files to assure full
transaction from the user perspective. There is no magic bullet, it's just
hard.

------
ltbarcly3
I have fought with orms for many, many years. I think if you sit behind
someone and watch them build an application with an ORM, and you sit behind
the same person and watch them use SQL and data access functions and other
boring things, the person using the ORM will spend many, many hours fighting
to get the ORM to do what they want, or fixing bugs that pop up because the
ORM didn't do what they thought it was going to do in subtle ways.

Generally, with threadlocal sessions and an application passing orm data class
instances around the code freely (which is by far the most common pattern of
use), the application will end up doing 10,000x more queries than the
programmer would have guessed (this is a literal number and not an
exaggeration). Trying to tell the ORM to preload the tree of objects that is
going to be accessed is nearly impossible, since the instances go up and down
the stack from function to function, each potentially accessing an attribute
of an instance loaded as an attribute of an instance many levels back to the
original object intentionally pulled from the database.

ORMs make writing the application 90% faster for the first 2 weeks and then
50% slower from then on.

That doesn't mean you are stuck writing straight SQL queries and passing
around rows of data, you can sit for a bit and build data access functions
that make your life easier and write classes that represent entities from the
data, but without your data going through tens of thousands of lines of
(extremely well engineered and thoughtful) ORM code that you have no hope of
ever understanding well enough that you will avoid catastrophic mistakes that
are extremely hard to fix.

And you will make catastrophic mistakes, mistakes you would probably never
make with SQL. SqlAlchemy has 5 states that orm objects can be in. If you are
using SqlAlchemy and you can't instantly tell me what those 5 states are, and
the detailed description of each, you are already making huge mistakes and
corrupting data.

To quote one section from the many pages of SQLAlchemy documentation about
'session state management' (and if you don't know all this stuff by heart you
will end up learning a lot of it the hard way):

==============================================

The SELECT statement that’s emitted when an object marked with expire() or
loaded with refresh() varies based on several factors, including:

The load of expired attributes is triggered from column-mapped attributes
only. While any kind of attribute can be marked as expired, including a
relationship() - mapped attribute, accessing an expired relationship()
attribute will emit a load only for that attribute, using standard
relationship-oriented lazy loading. Column-oriented attributes, even if
expired, will not load as part of this operation, and instead will load when
any column-oriented attribute is accessed.

relationship()- mapped attributes will not load in response to expired column-
based attributes being accessed.

Regarding relationships, refresh() is more restrictive than expire() with
regards to attributes that aren’t column-mapped. Calling refresh() and passing
a list of names that only includes relationship-mapped attributes will
actually raise an error. In any case, non-eager-loading relationship()
attributes will not be included in any refresh operation.

relationship() attributes configured as “eager loading” via the lazy parameter
will load in the case of refresh(), if either no attribute names are
specified, or if their names are included in the list of attributes to be
refreshed.

Attributes that are configured as deferred() will not normally load, during
either the expired-attribute load or during a refresh. An unloaded attribute
that’s deferred() instead loads on its own when directly accessed, or if part
of a “group” of deferred attributes where an unloaded attribute in that group
is accessed.

For expired attributes that are loaded on access, a joined-inheritance table
mapping will emit a SELECT that typically only includes those tables for which
unloaded attributes are present. The action here is sophisticated enough to
load only the parent or child table, for example, if the subset of columns
that were originally expired encompass only one or the other of those tables.

When refresh() is used on a joined-inheritance table mapping, the SELECT
emitted will resemble that of when Session.query() is used on the target
object’s class. This is typically all those tables that are set up as part of
the mapping.

=========================================

Yep, sounds like an ORM makes life a lot simpler!

------
Mc_Big_G
2014

------
marble-drink
Don't just learn SQL. An ORM like SQLAlchemy will allow you to save time and
write cleaner, more comprehensible code.

But do learn SQL. Do not use an ORM before you have learnt SQL.

------
yegor256a
You may find this relevant: [https://www.yegor256.com/2014/12/01/orm-
offensive-anti-patte...](https://www.yegor256.com/2014/12/01/orm-offensive-
anti-pattern.html)

------
meesterdude
> I do know one thing: I won’t fall into the “ORMs make it easy” trap. They
> are an acceptable way to represent a data definition, but a poor way to
> write queries and a bad way to store object state. If you’re using an RDBMS,
> bite the bullet and learn SQL.

I don't think i'm alone in saying I'd rather hire a developer who is native
with an ORM and can dive into SQL when things get thorny, than hire one who's
going to fight me on the very merits of an ORM at all.

I mean, hey. To each his own. But lets be clear: this is ridiculous, and if
you think it's a position you want to take up, make sure you can get hired
holding onto it.

~~~
Roboprog
Alas, so true.

I tolerate an ORM at work. Secretly loathing it. Like much of industrial
programming.

