
I don't want to learn your query language - nreece
https://erikbern.com/2018/08/30/i-dont-want-to-learn-your-garbage-query-language.html
======
zaptheimpaler
Yeah I think the only way you can use plain, vanilla SQL to query a system is
IF its a relational database. Most of the examples are not.. they solve
different problems and have vastly different architectures under the hood.

Lucene for example doesn't use SQL because its really solving a different
problem - text search. Its a language dedicated to what could only be
expressed using something like `LIKE` and regexes in SQL.

Same with Splunk, it addresses a different domain and solves different
problems that cant be easily expressed in SQL.

Same with MongoDB.. its not a relational db. Just because there is a mapping
from some SQL queries to some Mongo queries, does not mean they are identical
databases.

The query language directly impacts the AST generated, which is necessarily
tied very closely to the exact capabilities/internals of a system. This post
just feels like its based on a cursory glance of what the systems do.

If all Lucene had was regexes, it would not perform much better than a SQL
database throwing regexes at strings. Its precisely because the query language
is finer-grained that it can be better optimized for that usecase.

~~~
ddebernardy
> Lucene for example doesn't use SQL because its really solving a different
> problem - text search. Its a language dedicated to what could only be
> expressed using something like `LIKE` and regexes in SQL.

There are much better options than LIKE and regexes in SQL for text search. To
mention but two:

    
    
        SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery;
    

[https://www.postgresql.org/docs/current/static/textsearch-
in...](https://www.postgresql.org/docs/current/static/textsearch-intro.html)

    
    
        SELECT * FROM articles
        WHERE MATCH (title,body)
        AGAINST ('database' IN NATURAL LANGUAGE MODE);
    

[https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-
lan...](https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-
language.html)

All main engines support full text search in one way or another. TSQL, Oracle,
DB2... even SQLite.

What more, these engines all support inverted tree indexes in one form or
another - i.e. the same type of index Lucene uses - to make this efficient.
This is completely different from the typical LIKE or regex query against a
column with a BTree index.

~~~
Xylakant
If you truly believe that MySQLs MATCH comes close to be plethora of different
queries you can fire at an ES or Solr server, you’re missing some fundamental
part of the picture. Sure, it might have been possible to squeeze Lucenes or
elasticsearchs query language into something that looks like SQL, but it would
still remain a custom SQL dialect which would require substantial domain
knowledge to handle. The fundamental problem in that domain is not the query
language, it’s knowing which query on which underlying field definition will
produce the desired result. Squeezing the query language into some sql-like
shape will provide marginal gain at best.

~~~
ddebernardy
I can't speak for MySQL, as I haven't used it in a while, but I'd pick a
properly configured Postgres full text over ES in a heart beat, if only
because it means one less dependency (tree) to worry about. (As a bonus, the
beauty of Postgres allows to use ES as an index type if you so wish [0].)

Also:

> Squeezing the query language into some sql-like shape will provide marginal
> gain at best.

... is disingenuous at best. In both examples I raised (and in the ones you'll
find if you query the others) the engines have functionality to accept user
input pretty much as is - meaning as your typical mom would type it in a
search field.

If you need extra criteria from there and the dialect's full text syntactic
features, you can use regular SQL conditions (and joins, and aggregates, etc.)
on the subset of records found. The most discriminating criteria/index will be
the one related to the search query in most cases. And when not, lucky you -
your query planner did not hammer everything with the same sledgehammer, thus
demonstrating why you should be using SQL.

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

~~~
smsm42
I'm not sure what "properly configured" means but ES has huge array of options
for search which something simple like _MATCH (title,body) AGAINST (
'database' IN NATURAL LANGUAGE MODE)_ can't even begin to coming close to
cover. Look at their docs, there are literally hundreds of options, dozens of
search modes, dozens of analyzers and filters, scoring configurations, and so
on and so forth. I am not familiar specifically with Postgres but I highly
doubt Postgres supports all that or even a serious part of that - at least
natively.

What I see at your zombodb link is just a way to make Postgres proxy requests
to ES - complete with ES syntax and/or Query DSL. The only difference is that
instead of connecting to ES directly you connect to Postgres and then add
"SELECT" thing in front of your ES Query DSL. If it makes something easier,
fine, but it's not "Postgres full text over ES".

------
roel_v
I get why people hate on ORM's, but let's not throw out the baby with the
bathwater here - I don't want to go back to the time where you had to write
object definitions for each table the database, and manually sync them. Nor do
I want to manually write trivial 'select name from foo where id=1' queries all
day, and do all the plumbing to execute it. So when hating on ORM's and
unnecessary abstraction layers, please qualify that with 'ORM's should not try
to be too smart' and 'ORM's should provide fall throughs into whatever they're
abstracting for non-trivial use cases', and not give people the idea to ditch
ORM's all together.

~~~
saas_co_de
> const selectBy = (table, column, value) => db.query(`SELECT * FROM :table
> WHERE :column = :value`, {table, column, value})

> const selectOneBy = (table, column, value) => { const res = selectBy(table,
> column, value); return res && res.shift() }

Two or three lines gives you about 80% of the value in any ORM and avoids the
-20% value found in the other 100k LOC.

~~~
idoubtit
> Two or three lines gives you about 80% of the value in any ORM and avoids
> the -20% value found in the other 100k LOC.

I have a hard time believing this when the two lines above are obviously
wrong. You can't use :column to escape the name of a column in SQL. So your
code has to protect it, which is not easy if you wan't to be portable. MySQL
will generally use `column` but may be configured to use the standard
"column".

Anyway, selectAllBy() and selectOneBy() are not enough to be confortable. I
appreciate the static completion in Something.find({id: 1}).complete, the
relative queries like Post.find({id: 1}).author, and many other things that
help against typos, help code faster and make the code more readable.

I agree with the OP that fallthroughs are much needed, because writing custom
SQL is sometimes simpler, and sometimes much more performant. Usually, its
mostly about writing SomeModel.findAllBySql() which most ActiveRecord
implementations provide.

------
ThemalSpan
That “smart kids say no to DSLs” meme is funny but also a garbage take if
taken at face value. DSLs are important because specializing will generally
make you / your tool more effective. Splunk has a special query language that
reflects the product’s architecture. If you want to become productive with a
piece of software you need to learn its language.

Edit: To clarify my position, Domain specific languages mean domain specific
ideas. As soon as a product is no longer meant for general queries, but for
queries over specific ki day of information in a specific kind of system, we
can use that knowledge to make a powerful language in that domain.

~~~
Dowwie
More effective at what, other than using the DSL?

The article was about ORMs, not Splunk, and consequently their DSLs

~~~
yomly
serious question: what is the alternative as an interface to something like
this that _isn 't_ a DSL? Is something that looks a bit like a familiar query
language (like SQL) better? Is finding yourself in the uncanny valley of a
language a feature or a bug?

------
alkonaut
I think micro ORMs like Dapper are a perfectly reasonable compromise between
too much abstraction vs too much abstraction. Hand populating data structures
from database query results is error prone and tedious. Using a “big” ORM like
hibernate or EF is a leaky and expensive (over-)abstraction. Not just because
they typically come with a query dsl but they also come with performance
considerations and architectural consequences that are hard to predict. You
end up in a situation where estimating work is hard because you don’t know if
the ORM is going to play along (making a feature trivial to implement) or hit
a snag (making it very time consuming). With smaller abstractions the
estimates may be higher in the best case but they vary less. This is one of
the most important qualities about a library or stack. Not the average, but
the variance in difficulty.

On the topic of query DSL’s - the only acceptable ones are those that are
native to your programming language. A good example is the F# SQL type
provider. If that’s sufficient for your needs, it’s actually quite elegant as
far as query languages go - and it’s all F# meaning it’s not a _specific_
language for querying.

~~~
jordanab
I think that the current iteration of EF is still a great fit for small to
medium sized projects. Besides offering strongly typed queries (LINQ) and
updates, it also provides some additional functionalities such as migrations
that such projects really can benefit from.

------
threeseed
I had to check the date because this could've been written 20 years ago.

Developers don't need other developers to tell them when or when not to use
ORM. We know how it works. Some use cases are better for a quick inline SQL
whilst others where you have hundreds of tables would lend well to ORM. It's
not one size fits all and I'm sure we've all written a few apps in our time to
know this to be the case.

And this idea you can just learn "one SQL" is pretty laughable. There is a
subset of SQL that works across many (but not all) relational databases. But
each database has its extensions which you really should be using to get
maximum performance. ORMs automatically take advantage of these just to
mention. And the behaviours you will get can differ immensely.

~~~
scarface74
_But each database has its extensions which you really should be using to get
maximum performance. ORMs automatically take advantage of these just to
mention. And the behaviours you will get can differ immensely._

But if you use vendor specific extensions you will have “vendor lock-in”. What
if someday on a whim your CTO decides to change the database your entire multi
billion dollar organization from depending on Microsoft/Oracle and wants to go
open source?

Yes I’m being sarcastic. In reality, statistically no company makes those
types of sweeping changes because the risks are too high and the rewards are
too low.

~~~
avmich
Company may very well not have a choice. A couple of weeks ago a customer
requested Oracle instead of MS SQL - now somebody is looking into all places
checking if SQL is good enough.

To me the saying "ORM is a Vietnam of computer science" rings true too often.
Somehow projects which I deal with don't really benefit from ORM... so I could
live completely with SQL alone.

~~~
scarface74
If I were writing apps that were sold to customers, that would be even more of
a reason to use LINQ - not most ORMs though.

I know about a software package that was written in C# that officially
supported Sql Server,Oracle, and Mongo and he was able to support all three
with basically the same codebase using Linq and runtime translated
Expression<Func<T>> statements.

His LINQ queries and expressions were the same across data stores.

------
cryptica
It never made sense to me how popular ORMs were. The very first time I used
one, I understood straight away that it was a bad idea and I never changed my
mind about it (though I tried).

Why is it that open source software which adds unnecessary complexity and
slows down development tends to become popular but open source software which
takes away complexity and speeds up development tends to not get any
attention. It's like there is some kind of conspiracy.

~~~
justaaron
webpack and the entire "modern" js frontend...

all because of an inability to manage versioning change across the standard
browser js api...

great for a giant megacorp looking to waste money on busywork and divide the
workload across more employees (front-end devops?!?)

less useful when you already have to compile a backend, don't really have a
problem with ES5, and understand that you are basically only tasked with
making your dom updates dependent upon data model changes, YMMV

~~~
gear54rus
so because you don't see the obvious problem with being stuck on a single
version of anything forever, webpack, representing progress in this case, is
bad? nice try

what does versioning has to do with bubbling, wtf?

~~~
coldtea
> _so because you don 't see the obvious problem with being stuck on a single
> version of anything forever, webpack, representing progress in this case, is
> bad?_

In fact, the parent does see (and called) that "obvious problem with being
stuck on a single version of anything forever".

That's why he doesn't cheer for the band-aid solution that's webpack and
doesn't thing it "represents progress".

------
matthewmacleod
I disagree with the ORM hate generally, being a massive fan of ActiveRecord
for making it easy to build queries and still drop down to SQL as required.

But I’m right on board with the “stop inventing rubbish pseudo query
languages” idea. I’ve rarely seen an example that wouldn’t be better if it
were it just “SQL subset with some additional features”.

~~~
mb_72
XPO (Devexpress' ORM for Windows) fan here; I've used this for years to
deliver solutions for a number of clients, and it's still possible to use SQL
directly in the few percentage of cases where it's necessary. I'm afraid I've
given up reading arguments against ORMs as none of the cons seem to have any
relevance to the work I do now or will do in the future, but I admit I am a
'enterprise' developer / consultant.

------
tragic
I've worked on large projects using ORMs and others using parameterised SQL
strings. My experience, FWIW, is that the advantages listed in the article are
all real, on top of which there are some more esoteric SQL features (like
window functions) that may not be well supported by a given ORM.

On the other hand, there is a significant cost in terms of duplication. In ORM
code it is trivial to decide a where clause at run time. Not so much in plain
SQL. So you end up with a lot of copies of the same query, or else some sort
of query assembly layer, at which point you're halfway to an ORM anyway. I am
undecided on the issue.

~~~
blattimwind
I'm convinced there are two classes of ORMs. Those that try to abstract the
relational model and those who _are_ the relational model.

The former category includes things like Django's ORM: very limited in what
you can do, it doesn't actually "get" SQL, mismatch between the ORM API and
the database. The latter category includes fewer software; sqlalchemy is my
familiar example. Because sqlalchemy does not abstract the relational model,
instead mapping it into your application language, it has ~few mismatches
between its API and the database.

The former category is what "kinda works well enough for CRUD apps and I don't
need to know anything about nothing" and then become a major problem, the
latter category presumes you know how SQL works but then allows _you_ to work.

Random snippet just because I feel like it.

    
    
        def all_children(self):
            cte = session.query(Tag).filter(Tag.parentid == self.tagid).cte(recursive=True)
            cte = cte.union_all(session.query(Tag).filter(Tag.parentid == cte.c.tagid))
            return session.query(Tag).select_entity_from(cte).all()

~~~
mst
I tend to refer to the latter as ROMs. Ruby's Sequel is also somewhat this. My
(perl5) DBIx::Class is somewhat this (modulo all the design mistakes).

------
mping
Meh. If the backing store is not SQL, why would a SaaS provide a (much harder
to implement) SQL layer with all its standards (92, 99, 2003) and quirks
instead of writing a (probably) much simpler custom query language?

~~~
jaggederest
I think what he's asking is that people allow a flat file export, which you
would have to map and import yourself. This seems reasonable to me.

And I agree. I wasn't involved in writing NRQL but statistics queries at New
Relic during my time were already approaching turing completeness, so it was
just a matter of time. Timeseries data in particular really doesn't map well
to SQL at all, sadly, as a SQL fan working with a lot of timeseries data over
the decade+ now.

~~~
threeseed
Some companies do allow flat file export.

Others don't because sometimes it's not that simple to fetch all of the data
to give you with one click. Many of these companies are just rolling up the
metrics in some online database and hiding the original files away in cold
storage.

------
Too
SQL reasonably standardized? You can't even write hello world without running
into vendor differences. Microsoft has SELECT TOP FROM, while Mysql has SELECT
FROM LIMIT.

------
ChrisSD
And yet LINQ seems to be so popular...

Of course some standardisation amongst ORMs would be good. That would also
help them be less "garbage".

~~~
scarface74
LINQ is more than just an ORM. Linq is just as it’s name implies a built in
way to standardardize querying across a wide range of domains. It just
translates your embedded query statements into expression trees that a third
party can do with what it will. The Mongo LINQ driver for instance is
excellent.

------
zzzeek
"Let’s dispel with the myth that ORMs make code cleaner. Join the embedded-SQL
movement and discover a much more readable, much more straightforward way to
query databases."

Any argument whatsoever for this? Would love to see an example of how when you
do away with the whole object persistence layer, and you just have a SQL
string (which ORMs allow you to do anyway), how do you get your parameters
over, how do you get your rows back, how do you manage your transactional
scope, how do you get your rows in and out of your objects? all of which has
nothing to do with a SQL string.

Well let's read on:

"Erik Bernhardsson... is the CTO at Better, which is a startup changing how
mortgages are done. I write a lot of code, some of which ends up being open
sourced, such as Luigi and Annoy. "

Open source stuff! Let's go there and see an example of this "write raw SQL
and don't use any persistence libraries and your code is readable and
straightforward", because nobody ever seems to actually want to illustrate
this and how they don't end up writing their own ORM anyway, and we see, oh
[https://github.com/spotify/luigi/blob/master/luigi/db_task_h...](https://github.com/spotify/luigi/blob/master/luigi/db_task_history.py#L48),
it's SQLAlchemy ORM.

Foiled again in my search to see this elusive super clean and simple raw SQL
with no ORM that doesn't reinvent an ORM anyway. Which is the real "myth" ?

~~~
daveFNbuck
To be fair, he didn't write the code you're linking to. Luigi has a lot of
contributors. I was a pretty heavy contributor/user for years and I was never
able to get the task history feature to work.

~~~
zzzeek
sure. but seriously. "Can we just dispel with this myth about ORMs?" What
myth? can you provide any evidence or argument of any kind? How about if I
said, "Can we just dispel with this myth that Erik Bernhardsson has any idea
what he is talking about"? (to make it clear, this would _not_ be a fair
statement either because i know nothing about this person) and just ended the
blog post there. Because I actually looked at his own open source projects for
evidence of this deep wisdom he has and found none. not a very high quality
post.

~~~
daveFNbuck
He provided an argument. It's the 4th paragraph of the blog post, ending with
"and I end up with bloated higher level data classes rather than easy to
understand tuples or dicts that contain the data in a dumb simple format that
is trivial to introspect."

~~~
zzzeek
That is in fact not an argument about ORMs and especially not about DSLs, that
would be an argument against object oriented programming overall. Where again,
let's see the examples. When you write enough tuple business logic in a non-FP
language you get into a new set of problems with code organization. And you
still have to marshal those tuples in and out of SQL statements in a way that
makes sense vs your transactional scope and there's a lot to think about
there.

~~~
daveFNbuck
It's about how ORMs result in less clean code by forcing a clunky generic OOP
paradigm on any DB interaction. You may not agree with this argument, but it
is an argument.

~~~
zzzeek
one lazy sentence with no examples does not allow you to declare "Let’s dispel
with the myth that ORMs make code cleaner. ". Make an argument, show examples,
be specific. For example. I don't like Python's asyncio. I don't just blog,
"can we just dispel with this myth that asyncio makes code faster?" and just
bask in my upmods. I made a very well researched and tested argument and spent
many hours making sure, if I was going to make a statement like "technology X
that tens of thousands of you use every day is a myth", or more accurately
"not quite as worthwhile as you might think", I took the time to thoroughly
illustrate this: [http://techspot.zzzeek.org/2015/02/15/asynchronous-python-
an...](http://techspot.zzzeek.org/2015/02/15/asynchronous-python-and-
databases/) Whether or not people agree with what I wrote (though nobody
seemed to really disagree much), I'm not just trolling for upmods, I'm making
a tangible contribution to the subject. What this person posted is basically
non-debatable because no debatable points were made. Those of us with a lot of
time invested in DSLs and ORMs would prefer a real discussion with real things
we can debate, not just who can rant more loudly. I can debate this blog post
very simply that there's tens of thousands of developers who use DSLs and ORMs
to great success every day and the burden of proof is on this poster to prove
them all wrong.

This is just a very low quality rant that appeals to base cheerleading
instincts from whoever happens to agree with this person already, and I will
choose the vast community of working software over this post in order to
determine what exactly is a "myth".

~~~
daveFNbuck
It wasn't one lazy sentence, it was a paragraph. I don't think he was claiming
that you can't use DSLs or ORMs successfully. This is just a quick opinion
piece. Not all writing needs to be a thoroughly researched debate defense.

I personally didn't already agree with what he wrote, but there was enough in
it to make me think about the topic a little more and shift more toward his
position. I already know what ORM code and raw SQL calls look like without him
providing examples.

~~~
zzzeek
> I already know what ORM code and raw SQL calls look like without him
> providing examples.

I do too and code that not only uses raw SQL (not that big a deal in and of
itself) but also reinvents the whole persistence layer (which is the whole
part of "ORM" that has nothing to do with writing SELECT statements or using
DSLs) is a complete mess.

I really want to see the examples so I can learn from them and perhaps have it
contribute back to my own ORM project (SQLAlchemy).

------
gabordemooij
Shameless plug here, but this is exactly the reason why I did not create a
query builder in the RedBeanPHP ORM. At the time (2009) query languages were a
real 'hype' (as well as noSQL) but I have always loved SQL and I struggled
with all the SQL-alternatives offered by other ORMs. So, when I created
RedBeanPHP I decided to complement SQL rather than trying to replace it. Yes,
there is some room for improvement, notably in the integration with the
programming language (in my case PHP). So the ORM class has some methods to
indicate you would like to apply a condition, but the condition itself is just
written in pure SQL.I see my ORM more as a bridge between SQL and PHP than a
carpet that hides the SQL.

Not all DSLs are necessarily bad though. Personally I like to create some very
basic and straightforward DSLs to replace horribly complex GUIs with tons of
nested menus (aimed at professionals). I think DSLs can be useful for educated
computer users who don't know SQL (like people in finance, medicine,
statistics). Also there should be a limit to the complexity of a DSL. Not
every DSL needs to be turing complete. Simple DSLs that cover the required
functionality are the most successful in my experience with customers.

However replacing SQL just does not feel right, it's already an awesome
language to query a relational database so why replace it? So, for the most
part I agree with the author - although I miss the nuance a bit.

~~~
nojvek
I loved redbean, such a nice library.

Don’t do much php nowadays but Django is a great db - object abstraction. You
can go raw sql if you want quite easily.

------
Dowwie
I agree with the author's sentiment, in the context of relational databases. I
can't get back the time and effort spent porting SQL to query builder and/or
orm syntax. The best I can do is learn from my lapse of sound judgment and
instead use parameter binding against raw SQL and never interpolate strings of
SQL.

I regret not adopting this practice sooner.

Most of my substantial work resides in that raw SQL feature. So, I use a
library that offers good support of that.

I have written hundreds of SQL using query builders and ORMs.

~~~
threeseed
Every ORM (and I mean every one) has the ability to let you either (a) use raw
SQL, (b) use raw SQL interpolated with column names or (c) has a syntax which
is very close to SQL.

Porting should not have been some onerous or career destroying activity. As
Job would say I think you've made a huge mistake.

~~~
Dowwie
Those who don't learn from mistakes will repeat them. I've learned from my
mistakes and share a lesson today.

Most of my substantial work resides in that raw SQL feature. So, I use a
library that offers good support of that.

I have written hundreds of SQL using query builders and ORMs.

------
tjpnz
This reminds me of something that happened in a previous gig where our team
was working primarily with SQL. The company rockstar one-day decided to rid us
all of our perceived misery by introducing a REST interface over our existing
databases complete with it's own query language that he managed to foist upon
us with the clout he had with upper management. The query language was limited
at best even when compared to some of the examples in the article. Initially
all it provided was basic matching against several columns and the ability to
do rudimentary paging across resultsets. Naturally we had problems with this
as a big part of our job was writing reports relying heavily on joins and
aggregation - we were left in a situation familiar to anyone whose used NoSQL
joining everything in code but without all of the niceties that come with
maturity. It's a given that team productivity and general morale took a bit of
a dive at this point.

My current theory to the proliferation of these query languages is ignorance.
Aside from a severe case of NIH the developer behind our query language
exhibited a flawed understanding of SQL. If you're planning to roll your own
query language you probably shouldn't without first considering SQL, your
intended users and the use cases that they're trying to satisfy.

~~~
scarface74
_Initially all it provided was basic matching against several columns and the
ability to do rudimentary paging across resultsets. Naturally we had problems
with this as a big part of our job was writing reports relying heavily on
joins and aggregation - we were left in a situation familiar to anyone whose
used NoSQL joining everything in code but without all of the niceties that
come with maturity. It 's a given that team productivity and general morale
took a bit of a dive at this point._

Yes being forced to use a REST interface over reports is a bad idea but that
has nothing to do with joins over a NoSQL data store.

Mongo does joins via the $lookup function and the C# Mongo driver will
translate the LINQ code to MongoQuery for you.

[https://www.axonize.com/blog/joining-collections-mongodb-
usi...](https://www.axonize.com/blog/joining-collections-mongodb-using-c-
driver-linq/)

Worse case even if you do get data sources from separate places, it’s just as
easy to do joins on two lists as it is to do in Sql using LINQ.

------
scoom
Great, so force SQL to 'join' things that aren't tables and 'select' things
that aren't rows.

SQL wasn't built for querying every possible data store.

~~~
threeseed
If only there was a popular example of this. Wait there is: PostgreSQL.

They bastardised SQL to support JSON data types just like every other vendor
has to do because SQL is for relational and only relational data stores. Is
this the utopia that the author is after ?

[https://www.postgresql.org/docs/9.0/static/hstore.html](https://www.postgresql.org/docs/9.0/static/hstore.html)

~~~
oulu2006
I thought they were, for the most part, fairly logical extensions to SQL.

You don't have to use the JSON/JSONB column types, they are optional.

We use them extensively in production and haven't had much difficulty learning
them.

------
gesman
There are reasons different programming languages exists. And so as query
languages. Different stacks, different ways to handle, optimize and accelerate
queries over structured (SQL) and unstructured, schema-on-the-fly (SPL)
datasets.

If you don’t want to learn - it’s a personal choice.

You can ride a bicycle that works everywhere while others prefer driving sport
cars on a highways.

------
lazyjones
To avoid obscure ORM syntax and resulting garbage queries, as well as
cumbersome inline SQL blocks, just implement your complex queries as stored
procedures / views etc.

~~~
fmajid
Amen. You can also declare those stored procedures SECURITY DEFINER and deny
access to the underlying tables to the client role for much Better security.
For example if you have a table of users with login and hashed password, a
check_login stored procedure allows checking password but if(when?) the app
server gets compromised, the attacker can't even dump the list of valid logins
because they don't have SELECT privileges on the table.

~~~
angersock
Careful, this advice is dangerously close to proper DBA knowledge.

------
gregopet
I really really like what jOOQ has done for SQL under Java (and other JVM
languages). It's basically a type safe interface to SQL that anyone who knows
SQL can grok in minutes and it provides constructs to help bridge the gap
between SQL and the programming language. Among other features it has tools to
help migrate between different databases (and more are planned) - something
that is usually considered to be a strong point of ORMs.

I've used a couple of ORMs and jOOQ blows them all away easily. It's not the
first product that tried this approach but jOOQ does it really well. I've
partly or fully converted at least 3 Hibernate projects to jOOQ and all my
recent JVM projects use it exclusively. In my opinion products like jOOQ make
ORMs redundant for most use cases.

------
jcadam
> "It’s not like people learn an ORM instead of learning SQL anyway."

BWAHAHAHAHA!!! No. I have to use ORMs at work because they are forced on me.
Same thing with Eclipse, and GWT, and a million other things that I detest
because my employer has structured their development processes around hiring
average talent and getting average(ish), repeatable results (I'm serious, I
work with a lot of developers who only know how to code in Java, in Eclipse --
it's maddening). I'm the malcontent who doesn't fit in because I say things
like "Let's just use SQL." or "I promise you, this architecture is not
scalable, regardless of your seniority over me."

------
manish_gill
This resonated strongly with me. Almost all of the heavy DB interaction
projects I've done in the last couple of years - be it in Python, Go or Java,
the SQL layer has almost always been raw queries going to the DB. Makes things
so much easier.

I don't miss SQLAlchemy where I would trawl through documentation to figure
out how to do X or Y.

As for SQL standardization, +1 to that. Learning a new pseudo-SQL is only fine
if there are additional features on top of the DB, but I've encountered
databases where the SQL support was present in the name only and extremely
limited, with people making do with UDFs in order to achieve basic things.

~~~
dahdum
I’ve had the same experience, and as soon as the interaction became very
complex (multi table, multi row updates) we just moved it all into stored
procedures.

~~~
Udik
Never understood what's the thing with stored procedures. You soon find that
an indeterminate amount of your business logic is written in stored
procedures, and in an opaque language that is fit for manipulating data, not
for implementing business logic; not to mention the annoyance of running
updates on the database, which works ina fundamentally different way from
deployable code.

~~~
zzzeek
what it usually means is, "we gave the DBA the job to do and now we don't deal
with it at all"

------
TomK32
Also, learn just one programming language. Lisp obviously.

~~~
jcore24
Unironically this. I feel like a zealot saying it but having eDSLs with
painless interoperability makes lisp a joy to work with.

------
_Codemonkeyism
Mongo is one of the most painfull, it's inconsistent, simple queries take
dozens of $ and { }, and the db.tablename.<...> is annoying.

------
nojvek
I’m surprised no one yet had mentioned Facebook’s osquery tool. Even though
most things aren’t relational databases, a uniform way to query them and join
different datasources together is a very powerful idea.

Using sql to query a whole bunch of stuff has been very nice.

I just wish the excellent TablePlus client could use osquery under the hood to
show a lot of system level things in a nice pretty UI.

------
culturedsystems
The custom query language I find most annoying (possibly just because it's the
one I deal with the most), is JPQL, the query language of the Java Persistence
Architecture. JPA explicitly targets relational databases, so there's no
obvious reason why you couldn't just use SQL. You might think the advantage of
a custom query language would be that it could abstract over differences
between SQL implementations, but JPQL is underspecified and just gives you the
underlying database behaviour in many cases. I spent a day trying to figure
out why my tests passed but the staging environment was claiming that 0 was
greater than 0; turns out there was a difference in rounding behaviour between
the in-memory database used for tests, and the real database, which JPA was
cheerfully glossing over.

------
tomcam
OP's frustrations are quite understandable but I can think of a couple of
areas where ORM-like concepts have worked well. PyDAL[0] and Linq are both
incredibly expressive and PyDAL is also low overhead (it's not an ORM, it's a
data access language, which means it doesn't have the baggage of an object-
oriented layer).

Another reason to use a DAL like PyDAL is that, properly written, it can
abstract away the data layer such that you can switch from (for example) MySQL
to PostGres to Google Cloud SQL with essentially no retooling required.

Finally, using a DAL can help keep inexperienced programmers on track with
security, accessibility, and performance requirements.

[0] [https://github.com/web2py/pydal](https://github.com/web2py/pydal)

~~~
tannhaeuser
> _Finally, using a DAL can help keep inexperienced programmers on track with
> security, accessibility, and performance requirements._

This is a sure way they'll never learn the real thing. I know since I'm
sitting next to a (young but talented) guy who knows only Spring and JPA, and
freaks out all the time because the developer of the legacy system had dared
to assemble SQL queries manually in the code (which are complex enough I
personally hadn't bothered using JPQL in a trial-and-error development model
either).

Wasn't the post exactly about not wasting novice and other developer's time
with crap criteria APIs and such? Just compare the sheer size of the SQL spec
or reference manual against the shallow description of JPQL to judge which
language you should be using for a project with any kind of depth.

------
vbezhenar
I still yet to find sane JDBC wrapper library for Java. JDBC itself has
terribly verbose API. And any popular library I've encountered is not very
nice to use. I end up buliding half-baked JDBC wrapper for every project I'm
writing. And Hibernate at least have good enough API.

------
dec0dedab0de
This is how SQL injection makes a comeback. No thank you, I'll stick with my
orm.

~~~
avmich
SQL standard defines a couple of special characters within strings - like '
and * . Not too complex. If you escape input - say, using a library function -
you won't get injection problem.

------
unixhero
He forgot the Jira Query Language. To be fair, it really works!

~~~
joaomacp
I was so mad when I wanted to make a simple filter on Jira tickets and I had
to use "JQL"...

------
wheresvic1
In my experience ORMs work well for smaller projects and at the beginning.
Once the code matures, it is much more performant and simpler to use direct
SQL.

One thing that really resonated with me from the article is the following:

> Every SaaS product should offer a plug-and-play thing so that I can copy all
> the data back into my own SQL-based database (in my case,
> Postgres/Redshift).

Totally agree, a data dump should really be a standard feature (and not take
ages to produce either).

~~~
mst
Having written assorted data layers: If you can't replace a generated query
with a chunk of literal SQL when optimisation or raw power indicates it's the
best option, the data layer sucks.

Sadly, being software, "the data layer sucks" is true of most data layers.

------
GedByrne
Erik may know SQL but there’s an entire generation of developers who don’t.

These developers find it easier to dip into shallow DSLs instead of learning
the depths of SQL.

~~~
TimJYoung
If they can be developers, they can learn SQL. Apart from DDL statements, it's
literally 3 DML statements, of which most of the clauses operate exactly the
same way across all 3 statements.

~~~
TimJYoung
Sorry, that should be _4_ DML statements...

------
nailer
> I just want my SQL back. It’s a language everyone understands

I'm sure lots of people do understand SQL. But if you started programming post
2010 it's entirely possible you've never had to deal with a row database. My
case:

\- GAE datastore

\- MongoDB

\- RethinkDB

Again not discounting those who've been programming longer, it's just that
JSON-stores are becoming default and the amount of people who don't know SQL
is probably more than you think.

~~~
nojvek
I really loved rethinkdb rql. Was a very elegant language to deal with json
datastores over mongodb

------
biggio
SQL is syntactic sugar for first order logic. It is much easier to query
relational database with SQL than abstracting relations with ORM.

~~~
scarface74
How is

“select * from customer where age > 18 and age < 65”

Any more of an abstraction and easier than

from context.Customer where c.Age > 18 && c.Age < 65

With one it’s a magic string where you could have typos. With the other you
get compile time syntax checking.

~~~
CuriouslyC
If you use a modern IDE and extract your SQL into separate files you can get
syntax checking, and even the ability to run the query on-server directly and
get a table of results in a table-formatted tab with a single click. How much
do you have to do to get your ORM to run your query, and are the results as
easy to work with?

~~~
scarface74
With LINQ (I hate most other ORMs) you get objects back and you use the same
query syntax for working with RDMS’s, NoSql data stores, REST APIs, in memory
collections, etc.

If you had an in memory list you would write:

var seniors = from c in customers where c.Age > 65

If you were using an RDMS you would write:

var customers = context.Customers

var seniors = from c in customers where c.Age > 65

If you were using Mongo you would write:

var customers = database.GetCollection<Customers>(“Customers”)

var seniors = from c in customers where c.Age > 65

In each case, the query would be translated appropriately - as in memory code,
SQL, or MongoQuery respectively.

------
AnaniasAnanas
I agree, say no to garbage query languages, including SQL. Datalog has been a
thing for aeons, why not use it?

~~~
a-saleh
Aggregation is awkward. It is not that composable. Sub-queries are not a
thing. Prolog conventions make things awkward. The ammount of datatypes seems
to be limited.

If you encounter datalog in the wild it will be something pretty-custom, like
datomic, pydatalog, or even differential dataflow (that feels more like map-
reduce with fix-points?)

~~~
YeGoblynQueenne
>> Prolog conventions make things awkward.

Hey. What do you mean? Which Prolog conventions?

(I write a lot of Prolog and I'm always curious what other people think about
it).

~~~
a-saleh
Maybe I haven't used datalog enough, but the convention of lowercase data,
uppercase-starting Variable strikes me as limiting.

------
gonyea
This is some A+ bah-humbuggery. You want companies to parse sql and translate
that to backend api calls? What a nightmare. Data isn’t always co-located, and
not all data is allowed to be fetched together (eg, protecting user privacy in
Google Analytics).

------
jmartrican
This is why I really like JOOQ for Java. Its ORM but with SQL. Best of both
worlds IMHO.

~~~
oftenwrong
jOOQ is my favourite Java library. The code generation effectively extends
type checking down to the db types and makes it easy to build your persistence
layer without messy object mapping... if used properly; I've seen some setups
that misused jOOQ.

------
pmontra
I'll be happy to write my own SQL (I do for complex queries) but I don't want
to extract results manually from results and tweak the code every time I
change the schema. At least those parts of ORMs must stay.

------
jakoblorz
I believe that sqlx for go is amazing in bridging the gap between orm and
plain SQL!

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

~~~
swah
That does almost nothing... I _enjoy_ writing SQL and Go with sqlx, but
testing other designs/ideas becomes so slow compared to Python/Django.

I'll be using Gorm on my next project, because life is so short..

------
supermatt
And I’m assuming your toolbox just contains a hammer? Granted, there’s a lot
of duplication of effort around, but in many of the examples you cite they are
trying to solve different problems.

~~~
TomK32
There's a claw hammer, ball pein, mallet, bronze hammer, rubber hammer if you
don't want to band your head on the table, and of course the mighty sledge
hammer.

~~~
supermatt
So just hammers :)

------
mothsonasloth
I've used Hibernate's HQL for many years but now I've started using raw SQL in
my Java microservices with libraries like JDBI.

------
luord
Agreed on using SQL under the hood for every system, but I don't have a
problem with ORMs.

------
Udik
Just to go slightly off-topic, but anybody here felt it would be a dream if
Excel allowed pure SQL instead of those absurd formulas like "sum if" etc?

(I believe that it's (relatively) recently become possible to define tables as
SQL data sources, not sure how practical that is or if it's used at all.)

~~~
jodrellblank
"define tables as SQL data sources" ?

Excel can query external data sources with SQL queries, and pull the results
back into the spreadsheet.

And via ODBC you can use .xls files as data sources, and query them with SQL
style syntax from other programs. (I don't know how far beyond the basic
"SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]" it can
support).

Both of those date back - [https://www.connectionstrings.com/microsoft-excel-
odbc-drive...](https://www.connectionstrings.com/microsoft-excel-odbc-driver/)
\- most look like Excel 2000 or Excel 97.

