I don't want to learn your query language (2018)

https://erikbern.com/2018/08/30/i-dont-want-to-learn-your-garbage-query-language.html

by nudpiedo • 5 years ago

297 218 5 years ago

chrisma0• 5 years ago

I would actually applaud the proposal to have the EU mandate that SaaS products must expose all my data in a "plug-and-play thing" (in a format queryable using SQL?). I can already download it on most services, but usually get some collection of JSON files...

vardump• 5 years ago

That might not be feasible, if the data is never in any form resembling a relational database.

snovv_crash• 5 years ago

Download your personal data as a sqlite database would be awesome.

thinkingkong• 5 years ago

Author seems to take for granted that not everyone knows sql. A friend of mine recently hired a couple devs and asked them to build a web component. They didnt know html. But they were proficient with react.

nudpiedo2• 5 years ago

how can't they know html and were proficient react? it must be an exageration or perhaps they just knew enough HTML or missed propper CSS knowledge.

Also as far as I know React has native components, not sure how much HTML decoupled are these.

AlexC042• 5 years ago

This would be my guess. There are a lot of things in CSS that need specialist knowledge.

macspoofing4• 5 years ago

I question their proficiency with React (for web). There are some abstractions you just have to be aware of. If you're a web developer you have to have a good mental model of the DOM and how web frameworks interact with it ... because even if you work on React exclusively, the React component abstraction is not perfect and the DOM is going to leak through.

Author seems to take for granted that not everyone knows sql.

Then ORM is incredibly dangerous for them to use. ORM frameworks are useful but their abstraction of SQL is very leaky. You have to have a reasonable understanding of SQL in order to use an ORM framework effectively, otherwise you will get yourself in trouble. I bet this is where a lot of ORM criticism comes from ... namely developers who don't know SQL and therefore use ORM as crutch when working with a relational database. They can create a very nice object model with an ORM framework that is shit when it comes to the actual queries it generates.

arcturus174• 5 years ago

I seriously doubt you can be proficient with React without knowing HTML.

Even if you use a framework like MaterialUI which abstracts all the HTML tags into React components, working on layout creates a recurring feedback loop between source and output code in the browser / devtools.

If you've done that for some time, you end up knowing a fair bit of semantic HTML.

So I suspect either your friend was exaggerating, the devs were lying about their React proficiency, or maybe they work on everything but layout in React (highly unlikely). At any rate, forgive my cynicism, but I can't help but feel that this is a classic HN cheap shot at React developers at large.

ORM to SQL may be another matter. I've been using the Django ORM in the last couple of months and it's true that I haven't touched SQL but once. I know SQL but I've barely felt my skills progress since.

ZephyrBlu• 5 years ago

It seems most likely that they actually aren't proficient in React, which is not really surprising to me.

hmsimha• 5 years ago

I really like this article discussed here recently, which breaks down the new front-end work into front-of-front-end and back-of-front-end: https://news.ycombinator.com/item?id=26160538

knowing html is a somewhat nebulous claim, there are so many elements, attributes, and relationships, and the specification is incredibly dense. A design-focused front-end developer may almost exclusively use parts of HTML that a 'back-of-front-end' developer might not even know exist.

I do think you could do back-of-front-end very competently and have only a vague understanding of HTML. However, you'd likely also be interacting strictly with a subset of React.

n_parks• 5 years ago

Old discussion on HN (2018) for the same article here: https://news.ycombinator.com/item?id=17890760

dang• 5 years ago

Yup - two in fact (the other one is small but good):

I don't want to learn your query language - https://news.ycombinator.com/item?id=17890760 - Sept 2018 (153 comments)

I don't want to learn your garbage query language - https://news.ycombinator.com/item?id=17888930 - Aug 2018 (9 comments)

wccrawford• 5 years ago

I understand wanting to stick with a tried and true query language, but it doesn't surprise me at all that a new database technology has a new language. Sometimes things are just not going to work the same.

ORMs, though... I tried to get on board with those, and it's done nothing but bite me. As the article says, I end up spending more time in the documentation, and then my boss ends up questioning the choice as well when things (like in the article) happen.

In the end, I think the ORM has been as much pain as help, and I'd have been better off just not bothering.

pizza234• 5 years ago

I'm a "hardcore" SQL developer, but still deem ORMs [commonly] necessary. Importantly:

Their alleged benefit is they cut down development time
Let's dispel with the myth that ORMs make code cleaner

This is very narrow view of what ORMs do; without them, access to the DB layer becomes a black box.

Actually, not even the black box definition is appropriate - APIs to access the DB layer are needed in one way or another, so one ends up writing their own ORM.

ORM APIs do a lot more than just executing queries. The first thing that pop into my mind is persistence/state management, but also, composing queries is much more than bashing together a series of strings.

EDIT: elaborated on ORM APIs role.

nudpiedo2• 5 years ago

or maybe the ORM of the day doesn't support all the database features you need and you end up using leacky abstractions and less seniors colleagues end up with an OOP-ORMed database mess that would make cry anyone who did in fact learn the DB layout.

macspoofing4• 5 years ago

I think that's where the majority of ORM criticism comes from ... namely the idea (that some may hold) that you don't need to have a good mental model of SQL if you just use ORMs. I can see some people who are intimidated by SQL use ORM as a crutch to work with relational databases.

OOP and SQL are different paradigms. An OOP abstraction over SQL is never going to be perfect so to properly and responsibly use an ORM framework you have to understand what's going on under the hood. If you don't, then yes, you will create a mess for anything other than the most trivial use-cases.

gnfargbl• 5 years ago

The underlying problem as I see it is that SQL is itself a black box, in the sense that the database execution plan does not always trivially follow from the SQL as written. That is a very powerful property for an ad-hoc query language because it means that the user describes what they want, and the database itself takes care of the best way to get it to them. For programmers though, who usually want something predictable and repeatable to happen, it is not always ideal.

ORMs try to make SQL seem more programmer-oriented by adding an extra layer of indirection over SQL. Unfortunately, what they actually tend to do is to add an additional black box enclosing SQL's black box, and that usually makes everything worse because it is now almost impossible to reason about how the database is actually going to execute a particular query.

There doesn't seem to be an ideal solution to this problem. I think it is why so many databases tend to tweak SQL into something that better fits their implementation detail, and we end up with the explosion of almost-SQL languages that the author is complaining about. Personally, the least-bad solution I have found is the one you mention in your post: write your own SQL abstraction layer every time. At least that way, you can poke your screwdriver into the box and reason about what is going on in there.

akra• 5 years ago

There's been times where I've had to spend quite a bit of time trying to change the SQL in subtle ways to get an index to be used and do the join algorithm type I wanted; when it didn't want to do so. Usually improved performance by a massive factor for the datasets I was working with (e.g. 10x). The predictable performance as well was a big factor - I would prefer predictable and adequate performance over peak performance but high variability every time.

In the end after all this frustration I wished I could have written the query plan directly, especially when I used Postgres with no query hints. And yes I'm aware of Postgres and all the tricks that you can do to make it do certain types of joins and such and I employed many of them (adding statistics, loose index scans, all the index types and others). IMO the potential this could open up is quite large given many databases all have the indexes/algorithms and many data structure types these days. Gluing them together in a performant way where you use the appropriate algorithm/data structure index for the data on tables/JSON blobs/etc seems to be the hard part right now that requires a lot of trial and error and learnings of the SQL optimizer to get right.

leetrout3• 5 years ago

I never get the ORM hate for CRUD apps.

It gives a centralized place to understand what data is in play in the application and adding a new column to the database means updating a class / struct / type in one location and checking the use of that within the code base.

Without an ORM and with poor discipline I end up having to go find every query in the app referencing that table and check if I need to update the query which usually means changing the columns, the ? value placeholder and putting the correct value in the associated values container.

It all seems like a lot of tedium and ripe for simple typos in the queries that is easily avoided by using beneficial tooling.

amelius• 5 years ago

The hate for CRUD apps stems from it being too simple to be called a "high-tech" solution, that's all.

arcturus174• 5 years ago

Many global apps start as CRUD apps, and only become high-tech with scale. And even at hyperscale companies a lot of software engineering work is probably "basic CRUD" - reading from one system and writing into another, without applying a radical transformation to the data in the process. And even doing that well is quite challenging, which is why the average software developer gets paid well.

It's great if your talent allows you to do much more than that, but I wouldn't deride it, and much less hate it, when your own success is likely going to have to rely on competent people around you doing that job extremely well.

nobody0• 5 years ago

Yeah, as long as it's well designed, like LINQ.

ORM is necessary, otherwise we will find ourselves in the old days again, a large part of string concatenations, security problems, etc,.

jonnypotty• 5 years ago

There is so much code out there to help you do typed params and to stop you having to concat strings to make queries. You don't need an orm for this.

bendiksolheim2• 5 years ago

There is a tradeoff here, as usual.

The hate for ORMs comes from more advanced applications, not simple CRUD apps. When you have 5 tables with no relations, or maybe a simple one-to-many relation at most, an ORM is the perfect solution.

The problem comes when you have 25 tables, complex relations, complex updates and need to optimize your queries for better performance. You now need to know the inner workings of you ORM, and pray that there are sane ways of configuring these things. Knowing the inner workings of these enormous beasts are not always easy – at least not easier than writing the SQL yourself.

pmontra2• 5 years ago

This and the infamous question "how do I write this complex SQL query in ${ORM}?"

I ended up using raw queries in many of the projects I worked on for more than 2 or 3 years. It's more cost effective than trying to find the right combination of ORM statements.

arcturus174• 5 years ago

For complex data models, I find the ORM provides me with a lot of help in the form of IDE hints. I find the ORM schemas also provide documenting/referential value with less code than the raw SQL equivalent.

For the scenario you're describing, you could still use a mix of an ORM and raw SQL as needed.

jnsie• 5 years ago

But any ORM I have ever used supports raw queries. So, for boilerplate (which is often a significant portion of queries) the ORM does the heavy lifting, and for everything else you have the power to write the SQL yourself. This is something commonly buried in ORM discussions and I never understand why.

jordanab• 5 years ago

It's not just 'simple CRUD apps' vs 'advanced apps', I find it's also language/ecosystem specific. I've worked on Java and .NET projects (typed languages) where ORM's are a fact of life, and actually make it a lot easier to navigate & refactor your codebase. I've also worked on Python and Node.js projects, where ORM's often did not significantly improve the developer workflow (navigation, refactoring etc.) because of the lack of strict types in these languages, and ORM's and their usage were therefore a point of daily team discussion.

ggregoire2• 5 years ago

When you have 5 tables with no relations, or maybe a simple one-to-many relation at most, an ORM is the perfect solution.

Perfect solution to what problem?

blacktriangle2• 5 years ago

Here's the root of my problem: ORMs always force their world view onto the database, which is completely backwards from how things should work. The database is the most important part of the application, it will probably outlive your current code base. Yet your data model ends up getting warped to support a single ORMs worldview.

New table columns should never cause problems for existing code because you should never use `SELECT *` and you should never use positional semantics in your SQL, put a linter on it.

As for typos, that's what SQL builder libraries are for, they allow you to work with your own language constructs instead of mashing together strings manually.

ORMs are this thing that developers seem to think is a life preserver but as time goes on morphs into an anchor.

fiedzia• 5 years ago

ORMs always force their world view onto the database, which is completely backwards from how things should work.

I have different experience. There are situations where you work with database given of course, but in many projects ORM enforces good and unified practices (rows have unique id, constraints and indexes are properly named, relationships are done in always the same way), and generally database is just a servant to developer idea of data model, not a god to worship and bend to.

leetrout3• 5 years ago

you should never use positional semantics in your SQL

If I'm understanding you, that along with your mention of a SQL builder means, for example, in Python, you'd expect

CreateUser(name="blah")

As opposed to something like

db.Exec("INSERT INTO users (name) values (?)", "blah")

Is that correct? I think we're agreeing on reducing the amount of raw SQL being tossed around within the code.

soco• 5 years ago

If all you do is CRUD then fine. However databases are meant to do so much more than that. SQL means structured query language, it supports and enables complex queries. You could argue all those queries can be done in code and you'd be right, while paying the price of inefficient big data transfers and duplicating logic in possibly quite slower ways.

falcolas2• 5 years ago

My distaste is simple: The abstraction that is an ORM is leaky AF. Optimizing an automatic query is a PITA. Doing more than simple joins across tables is hard. Falling back to pure SQL quickly becomes untenable across an application (because each instance quickly becomes an ad-hoc workaround).

And to be frank, CRUD apps created with an ORM that remain as simple CRUD apps are the vast minority of the work that I’ve done in my career. Everything else evolves into something where we inevitably end up working around the ORM more than using it as-is.

The solution in my opinion: SQL generators (like linq and such). ORMs often include these as an after thought, but pulling in a full ORM for the sql generation is like pulling in a semi truck for its generator; it’s too easy to start experimenting with the toys stored in the trailer.

xtracto• 5 years ago

The abstraction that is an ORM is leaky AF

Add to that, as someone mentioned above ORMs tend to be "common least set of features". And if you for some reason want to use a specific feature of your RDBMS you have to jump through hoops and the ORM "illusion" ends.

NDizzle• 5 years ago

Have you ever seen what EntityFramework produces for queries? I wish I could share some of the beautiful disasters that it has produced for my company.

leetrout3• 5 years ago

I have not. I assumed it was analogous to Django / SQLAlchemy where you could always pull the escape handle use raw SQL when it was truly necessary.

nightski• 5 years ago

It produces what you tell it. The thing is Linq is a more general language than SQL. If you tell EF to generate something that would be really hard to translate to SQL then of course it's going to do a bad job. Just by taking a few seconds to think how the query you are writing will translate into SQL goes a long way towards helping EF produce good, clean queries. EF still provides many benefits on top of this so imo it's worth it but at the end of the day whether you are writing SQL or EF you have to understand the basics of how databases work.

MaKey• 5 years ago

I have. Recently I was looking at an Azure SQL DB and a long running query caught my eye. "Beautiful disaster" is an apt description for the 123 line query I saw.

scottious• 5 years ago

ORMs are great for the simple CRUD case. That's how people get lured in.

It's when things get complicated and performance is important that they start to feel like a hindrance. I've had to wrestle with ORMs only to figure out that it's generating bad queries or lots of queries when it could do it with one query. I've usually had to end up subverting the ORM or throwing it away entirely in almost every case.

cmckn• 5 years ago

I end up having to go find every query in the app referencing that table

This sounds like bad design, why would you have SQL queries for the same table littered across a project, rather than in context with each other in the same file/class/interface/etc.?

Most of the CRUD experience I have is in Java, and I've preferred using JDBI over ORM's like Hibernate. You write a CRUD interface for the table and annotate the methods with (something very close to) plain old SQL. I can define how a SQL row gets mapped to a POJO, though in many cases the default mapping logic is fine. I know exactly what SQL will be executed, at compile time. I use Liquibase to manage the database schema. It's about as painless as it gets; I've had many obtuse errors to resolve attempting to use an ORM. I think most importantly, I'm familiar enough with the schema that if and when I need to do an ad-hoc query to give some data to an analyst, or debug an issue, I know what I'm working with.

mdpopescu2• 5 years ago

Yes, it is bad design, because you have one Sql.RunQuery method returning a DataTable and it's used every-freaking-where. Then you have to convert the data in every place back to the proper objects, paying special attention to the difference between DBNull and regular null.

I've used both ORMs and direct ADO queries (in C#), and while ORMs can trip you in special cases like the N+1 SELECT problem, the raw queries are a pain everywhere. It's just to easy to say "ah, I need a quick SELECT here, I'll just return this bunch of records and I know that record[5] is a string with the value I need". Hello, versioning hell.

klingon78• 5 years ago

I was sure this post would be about SPARQL; the web as a distributed DB is genius, but a new query language with tuples and specialized servers weren’t the answer I’d imagined.

thrower123• 5 years ago

I immediately thought of Salesforce and their almost-but-not-quite SQL query language that they have.

Or Microsoft's Graph API, which is kind of SQL-y, kind of LDAPy, but not systematized enough that you can be confident anything you try will work without reading the doc on the particular resource you're trying to get.

macspoofing4• 5 years ago

I understand the author's frustration, but his railing against ORMs is misplaced. First, if you don't want to use an ORM or your application doesn't warrant one, then don't use one. Also, all ORMs let you do raw SQL because there will be times where the ORM abstraction will be too cumbersome.

But to be clear, either you use an ORM or you're going to invent a new ORM because your app doesn't talk in SQL, it talks in Objects - so whatever you do, there will be some mapping between the SQL query response, and objects that you will need to create to pass around in your app. If your app is expected to work with different databases, each with their own SQL flavor, you're going to have abstract that as well.

And of course, ORMs tend to have good sets of defaults built in (like proper escapes). So whatever ORM-like framework you build, you'll have to take care to cover that case, otherwise you're back in the bad old world of SQL injection.

otabdeveloper42• 5 years ago

your app doesn't talk in SQL, it talks in Objects

Speak for yourself. My app talks in dataframes.

macspoofing4• 5 years ago

Then maybe ORM isn't for you?

TuringTest• 5 years ago

But aren't dataframes just objects where each attribute returns an array of values instead of a scalar?

pwinnski6• 5 years ago

My app often talks in Objects, but my scripts talk in SQL.

qudat• 5 years ago

Also, all ORMs let you do raw SQL because there will be times where the ORM abstraction will be too cumbersome.

"Learn how to master {flavor} ORM and when you've spent hours digging through documentation, stack overflow posts, and src code to find it isn't simple to do in {flavor}, use raw SQL."

This argument that "you can always drop into raw SQL" skips the fact that the code will not be merged if it can be done in the ORM because ThisIsTheWay(tm).

I find query builders to be a nice blend between raw SQL and the ability to write programmatic queries. The APIs tend to be relatively consistent between libraries since they map directly to SQL statements.

lelanthran4• 5 years ago

your app doesn't talk in SQL, it talks in Objects

Maybe that's the problem? It might just be that OOP is a poor fit for large datasets :-/

Using OOP for results from a RDBMS is banging a square peg into a round hole: with a big enough hammer you'll eventually get it in, but the results are not going to be pretty.

If all the data is relational, and you're trying to enforce some sort of hierarchy on it, whether you're using an ORM or not is irrelevant, you've already lost the aesthetics war.

frettchen• 5 years ago

The author notes that SQL has been around since, roughly, 1974 - but MultiValue Query Language/ENGLISH has been around since the the 60s. The choice is clear. /s

eternalban2• 5 years ago

SQL is not merely syntax. There is a relational model that is the actual gem of "70s".

grzm2• 5 years ago

At the same time, it’s important to understand that the relational model is independent of (and prior to) SQL. SQL is by far the most commonly used language implementation and is important because of that, but it also deviates from the relational model in subtle (and sometimes not so subtle) ways. Understanding those differences and how to accommodate for them can be important to understanding how to write schema and queries in more holistic and powerful ways.

bullen2• 5 years ago

What about this: http://root.rupy.se

You can view source to see the HTTP parameters or just WireShark it.

oftenwrong• 5 years ago

This is not as self-explanatory as you seem to think...

bullen2• 5 years ago

:D I guess, did you understand anything?

spacemanmatt• 5 years ago

When I work with non-OO languages, an ORM isn't missed. Rather, flexible language-native data structures are gained. When I first stepped outside the OO world after years in, I couldn't help but notice how much "technology" was just compensating for problems imposed by OO languages.

dgellow11• 5 years ago

I just want my SQL back. It's a language everyone understands, it's been around since the seventies, and it's reasonably standardized. It's easy to read, and can be used by anyone, from business people to engineers.

I rely a lot on SQL and in general advocate for it, but that’s too simplistic of a view IMHO. SQL makes it easy to write and read simple queries, and ridiculously complicated and arcane to write slightly more complex logic.

More than once I’ve been asked to help fix giant SQL queries written by a business or analytics team and that’s a terrible experience.

Also, the tooling around SQL is often terrible and almost didn’t evolve during the past 15+ years. SQL queries from another programming language without an ORM means that you do everything by manipulating strings by hand, with zero type safety.

Edit: My favorite library in Go is reform, a generator that generates types and implement the Scanner/Valuer interfaces. You annotate your structs, generate the types, add the generates files to git, done. That way you have very limited ORM magic but gain some type safety. And you still have complete control over your SQL queries (which is often frustrating to do with classic ORMs).

https://github.com/go-reform/reform

Edit 2: the most promising ORM I’ve seen is Prisma, https://www.prisma.io/. I haven’t tried yet, I’m still quite attached to writing most of my SQL by hand because that’s what I know well, but their pitch seems good to me.

fouric• 5 years ago

SQL makes it easy to write and read simple queries, and ridiculously complicated and arcane to write slightly more complex logic.

This is consistent with my experience, and exactly backward of what you want a language to be - which is good scaling of solution complexity as a function of problem complexity, even (although ideally not) at the cost of upfront effort required to learn.

dgellow11• 5 years ago

Just to add a thought: really what I would like to have is a language that has native support for SQL. (I mean, I also want a better alternative to SQL, but the post author is right that it is the standard we currently have and it will not disappear or be replaced soon)

That sounds absurd, but my dream would be to write Go (just my personal favorite), then whenever I want to interact with the DB I can directly write actual SQL, not as a string, but as an actual valid expression. I have zero idea how that would work in practice but that’s the dev experience I would love to have. SQL as a DSL, written in my go file, without the need to change context, with syntax highlighting, type check, linting, etc.

Instead of having SQL as a second-class language it could be first class and that would be fantastic.

Not that something like this will ever happen, I’m well aware that standard SQL isn’t an actual thing in the real world and all the other issues around that idea, but I would LOVE this!

et1337• 5 years ago

LINQ from .NET was pretty close to this. You could write a variant of SQL directly inline with your C#. I think technically there was also an ORM layer (Entity Framework) but for the most part it was just a query builder.

davidspiess• 5 years ago

SQLC could be a good fit. It generates type-safe Go code from SQL.

https://github.com/kyleconroy/sqlc

Hir0ki• 5 years ago

Their is actually a programing language that has this feature. It's called ABAP[1] and it has build in SQL Support with OpenSQL[2]. OpenSQL is just a SQL dialect which is translated to the equivalent SQL needed for the DB specific underlying DB. Sadly ABAP is a proprietary programming language only available on the SAP Netweaver stack.

[1] https://en.wikipedia.org/wiki/ABAP

[2] https://help.sap.com/viewer/fe24b0146c551014891ad42d6b2789e5...

BiteCode_dev• 5 years ago

This is why good ORM like SQLAlchemy actually are split in 2 parts: the declarative layer, and the core layer.

The declarative layer gives you the handy ORM syntax for simple operations.

The core layer is lower level, and allows to composes all possible SQL operations you can dream, but from the comfort of your programming language, including typing, completion, and so on.

Does something like that exist for Go ? I don't know the golang ecosystem very well.

thewakalix• 5 years ago

Ur/Web has something like this (example: http://www.impredicative.com/ur/demo/sql.ur.html).

pwinnski6• 5 years ago

SQL makes it easy to write and read simple queries, and ridiculously complicated and arcane to write slightly more complex logic.

This is the thing that makes ORMs sound appealing: let's make the complicated things simple! In my experience, the complicated things are truly-complicated, not just because SQL makes them seem to be. So by the time you make Use Case A "simple," you've run into Use Case B and Use Case C, and by the time your ORM handles all of those, well, there's a good chance it still doesn't handle all of them, and however many it does handle, the syntax ends up being... complicated.

There is so much to be said for a known and well-supported standard, and we are so quick to assume we can do better. Most of them, we really can't.

But hey, keep trying! Coming up on 50 years, we should manage to surpass SQL at some point, right? And in the meantime, we'll have our five-thousandth ORM or DSL to reach the limits of and have to revert to SQL anyway for just those last two or three queries.

workingworkguy• 5 years ago

I'm one of the analysts causing issues for the OP, but a common example to me of something that's cake in a "real" language and frustrating in sql (ms in my case) is expanding a function to take a dynamic amount of params.

myFun param =

    for each param do...

Something like that has given me WAY more headaches than i ever expected, and that's before i saw vendor code (for systems that are used by millions) handling business logic in the stored proc with a 70 case switch statement...

carlps• 5 years ago

I don't think anyone is going to argue with making complicated things simple. But from my experience, if it's complicated in SQL it's going to be a nightmare in the ORM. Generally, complicated SQL means that the underlying data was not modeled to solve the use case. Exactly as you mentioned, trying to simplify things in the ORM layer will be on a use case by use case level.

jredwards• 5 years ago

I just can't get behind a sentiment that amounts to, "Hey guys, stop trying to innovate."

eitland4• 5 years ago

So by the time you make Use Case A "simple," you've run into Use Case B and Use Case C, and by the time your ORM handles all of those, well, there's a good chance it still doesn't handle all of them, and however many it does handle, the syntax ends up being... complicated.

Or you can do it the smart way:

Save an awful lot of time and hassle by using an ORM for all the boring stuff and have a couple of plain SQL queries for when you need it.

And in the meantime, we'll have our five-thousandth ORM or DSL to reach the limits of and have to revert to SQL anyway for just those last two or three queries.

Or you can do it the smart way as I mentioned above and also just continue to use JPA.

slcjordan• 5 years ago

Interesting. I've been looking at https://github.com/bokwoon95/go-structured-query but I'll have to look at reform too, now. Thanks.

dgellow11• 5 years ago

Reform documentation isn't always that good but the implementation and generated code is not too difficult to read when trying to understand how everything works.

Thanks for sharing go-structured-query, I didn't know about it.

stickfigure3• 5 years ago

SQL queries from another programming language without an ORM means that you do everything by manipulating strings by hand, with zero type safety.

This is not true at all. If you connect IntelliJ to your database (which you should - the query tools are fantastic), it becomes incredibly good at validating SQL strings in code. Even when doing crazy conditional construction.

I like ORMs. I use Hibernate a lot. But I write all my queries in native SQL.

dgellow11• 5 years ago

Do you have a link to Intellij documentation for this feature? That sounds interesting.

ragnese7• 5 years ago

I rely a lot on SQL and in general advocate for it, but that’s too simplistic of a view IMHO. SQL makes it easy to write and read simple queries, and ridiculously complicated and arcane to write slightly more complex logic. More than once I’ve been asked to help fix giant SQL queries written by a business or analytics team and that’s a terrible experience. Also, the tooling around SQL is often terrible and almost didn’t evolve during the past 15+ years. SQL queries from another programming language without an ORM means that you do everything by manipulating strings by hand, with zero type safety.

But this is all beside the point. Yes, SQL kind of sucks. But what sucks more is having a leaky query language on top of SQL such that you have to learn the query language AND SQL. Because- let's face it- you almost always end up printing out the SQL that the damn thing generated to figure out why it's doing something weird. You almost always end up needing to drop to SQL anyway.

And next year there will be a new awesome ORM/query-language that you're expected to learn all the pitfalls of.

dgellow11• 5 years ago

It's not beside the point, it's the other face of the discussion. Building and calling SQL queries from a programming language without an ORM often sucks. SQL is not the simple language the author seems to talk about. ORMs have leaky abstractions. All of this is true.

JeremyNT• 5 years ago

And next year there will be a new awesome ORM/query-language that you're expected to learn all the pitfalls of.

This may be true in some lines of work, but as a Rails developer I find it amusing. I've been happily using ActiveRecord for over a decade. I get that chasing the new shiny can be fun and look good on the resume, but the ORM shouldn't be a fad you chase that you need to swap out every couple of years.

You're not wrong that sometimes the ORM gets in the way and slows you down. There are certainly times I've been frustrated by having to figure out the right incantations to satisfy the APIs of ActiveRecord or Arel. However, this tradeoff is well worth it, because these tools protect us from so many footguns present with raw query string manipulation.

eitland4• 5 years ago

And next year there will be a new awesome ORM/query-language that you're expected to learn all the pitfalls of.

Or you can just stick with a JPA implementation (like Hibernate) which is highly performant and has been around for over a decade.

Someone will probably mention poor performance and yes, if you routinely query multiple millions of rows you'll probably want to hand code som SQL (something you can easily do without ditching JPA).

Otherwise, if you just happen to do the famous n+1 query, just get over it and actually learn how to use your ORM.

(Sibling comment also mentions ActiveRecord. I think the canonical .Net ORM has been stable for a few years already. Not everything is a Javascript/NoSQL :-)

geocar2• 5 years ago

But what sucks more is having a leaky query language on top of SQL such that you have to learn the query language AND SQL.

I think this speaks to the sad state of ORMs, but not the value of SQL.

I think SQL is rubbish: The two biggest things I hate about SQL are (1) that it's unstructured, so getting data into or out of SQL involves strings, (2) the syntax of those strings. Seriously. I really don't want to program in COBOL either.

ORMs are better on both these points, but as you point out, they are rife with pitfalls and I'll add I find their APIs absolutely reek of the SQL implementation they hide. I certainly want for better.

What we really need are languages to be better at dealing with data (especially data that is backed by permanent storage or distributed across multiple machines), but it's difficult to do this without (basically) creating a whole new language, and I think the decision to bring a new language into the world isn't one to be taken lightly.

Especially when you're asking people to trust their data to it. Bad software just gets turned off and on, and it resets, but people don't put up with a bad database for very long.

sa46• 5 years ago

You might like the approach I took with pggen[1] which was inspired by sqlc[2]. You write a SQL query in regular SQL and the tool generates a type-safe Go querier struct with a method for each query.

The primary benefit of pggen and sqlc is that you don't need a different query model; it's just SQL and the tools automate the mapping between database rows and Go structs.

[1] https://github.com/jschaf/pggen

[2] https://github.com/kyleconroy/sqlc

dgellow11• 5 years ago

Thanks for sharing!

incog_nit0• 5 years ago

Anecdata but our design agency decided to branch out beyond fully local Wordpress sites to something more cutting edge using Prisma + React.

It had a great developer experience but was slow and had poor pagespeed and usability scores compared to the standard Wordpress sites they were providing clients. Their next site was a bog standard all local Wordpress job although they've since moved onto React/Gatsby.

namdnay3• 5 years ago

What types of sites are these? I’d say React and co should be kept for webapps, not websites. If it’s just static content or a simple shopping site, Wordpress is going to beat any client-side system hands down

namdnay3• 5 years ago

Back in my Oracle days, I remember wishing there was a way to directly code “execution plans”, instead of faffing around with SQL, which was just (from my perspective at least) a complicated abstraction on top of them ...

Scarbutt3• 5 years ago

I really don't see what's promising about prisma. Lots of condescending stuff in their docs making SQL sound as something only a few can learn. It's extremely over-hyped, immature and buggy. But they are a startup so...

keithnz• 5 years ago

in C# (using Rider) I do all my queries in .sql files, fully connected to a real database with full auto complete (including auto writing your join), there's a section to define all the variables the query is expecting, and then, then I have a wee open source lib for embedding all your queries into your program and an easy way to get them out then use things like Dapper or RepoDB to ruin the queries with parameters. No string based sql at all. works well. https://github.com/keithn/katoa.queries

magicpointer• 5 years ago

A JVM library in this space I recently started using seriously and fell in love with: jOOQ. It's not an ORM, rather a query builder but an extremely smart one.

In the codegen mode, it scans your DB schema and generates record classes + a lot of utilities. If the DB is well done (and it should be), it interprets many constructs, including relationships, domain types and various constraints. It can also generate activerecord-like classes if needed.

It allows far better safety and composability than raw strings and a lot of control on the query. Most DSL functions are called the same as in standard SQL, and the docs always shows the DSL next to the SQL version.

Everyone in the Java world seems to reach for JPA directly, but for me working with something closer to the DB is really a breath of fresh air. The DB-firat approach really works wonderfully.

gregopet2• 5 years ago

Another jOOQ fanboy here - it's not just the manual that shows DSL->SQL, you can just .toString() most of jOOQ constructs in your code and get the raw SQL out. In fact, I believe you can just take that .toString() and put it straight into your prepared statement if you don't want jOOQ to run your queries and just use it for query composition.

projectileboy• 5 years ago

It’s probably just a function of the apps I happen to have worked on, but I have rarely (maybe never?) seen ORMs pay for themselves by the end. The classic joke about the business is that they see all of the costs and none of the value. But the developers tend to see all of the value and none of the costs. And the costs of ORM on a project have a very long tail. For most projects, if you really want to control what and how the developers interact with the database, a better approach is to have one or more separate db developers who create an API for the data via stored procedures.

mtberatwork• 5 years ago

What's worse than data silos? Data silos that invent their own query language.

I can't agree more. Bespoke query languages and back-ends are generally only good for two groups: the vendors and consultants. SaaS apps with their own query languages and NoSQL flavored back-ends end up being nothing but headaches for the poor FTEs that have to spend their days in the drudgery of having to figure out anything beyond a basic query.

jusssi• 5 years ago

I just want my SQL back.

Which of them? There are as many SQLs as there are implementations. You can't take an SQL schema from one DBMS and drop it into a different one, without conversion. Also you can't take a query written for particular SQL implementation and expect it to work and return the same results, except for very simple queries.

yoz-y• 5 years ago

This. The author also acknowledges that some of the languages they complain about are SQL dialects. Often they are simplified versions of the "generic SQL", e.g. AWQL does not have JOINs. Supporting all standard SQL features might be worthless and/or impossible for some domains.

AlexC042• 5 years ago

It is a bit falicous to imply SQL is the same everywhere. If you move from postgres to sql server to mysql to oracle you'll see there are major differences in how things are done.

Sure, your select and join is mostly the same, maybe with a few different commas here or there. Once you get into complex large data operations though it's a whole new ballgame.

Imports happen in wildly different ways, temp tables, choices of inserts vs. updates is a per-platform choice in some cases.

On top of all that, an ORM like entity framework (C#) handles things like making sure the statements are prepared in a way that "handles" major security issues like dependency injection out of the box.

If you make your team use an ORM you don't have to worry quite as much about someone named ';DROP tables ' signing up to your system. (Or whatever)

ORM has it's place. SQL isn't a monolith that everyone* knows.

The discussion is worth having, but I think the OP's argument lacks nuance.

CommonGuy• 5 years ago

You probably meant SQL injection, not dependency injection

geophile5• 5 years ago

I can count the number of times I've seen an app move from one RDBMS to another on zero fingers. This is really not a good motivation for an ORM.

mumblemumble3• 5 years ago

I did it once.

The first step was ditching the ORM and replacing it with a well-defined repository layer. Because the ORM was encouraging interaction with the database to be scattered everywhere, and that was making it impossible to understand scope of impact.

With the data access layer, though, we had a relatively small, clearly defined chunk of code that we could isolate and test, and that made the migration manageable.

We didn't need to ditch the ORM, of course. And, at first, we weren't going to. But we saw that, with things so clearly isolated like that, the ORM library wasn't really making the code any easier to understand, so we decided it wasn't really worth the added indirection or the performance cost.

I also worked on a product that simultaneously supported two different DBMSes. It didn't use an ORM, either.

xmodem• 5 years ago

I've worked on services that needed to run on the customer's choice of RDBMS, IMO Hibernate helped us out a lot. But it's not something I'd recommend doing if you could avoid it.

mdpopescu2• 5 years ago

Huh. I had a client once who complained that I was adding database indirection layers to some application. "When are we ever going to change databases?"

I had to remind him that, in the few years I worked for him, we had actually used five different databases - MS SQL, VistaDB, Excel-as-a-database (using ODBC), LiteDB (a NoSql database) and Sqlite. He knew about each and everyone of those, but the "we're never going to change databases" reaction is so ingrained apparently that he just went with it.

And just in case you're going to argue "sure, you used different databases but in different applications" - I just had a programmer change Sqlite to LiteDB, because Sqlite wasn't working properly on his system and it was faster to just change the database.

mumblemumble3• 5 years ago

Imports happen in wildly different ways, temp tables, choices of inserts vs. updates is a per-platform choice in some cases.

All of these bits of SQL, though, are things that ORMs typically handle by simply not using them. If you're limiting yourself to the subset of SQL that you can access with a DSL, it's pretty standard.

I agree that ORMs help defend against SQL injection, but so does any linter worth its salt.

To me, the real advantage of these DSLs is that they can get you some compile-time guarantees that your queries have some minimum level of validity. Raw SQL needs to be explicitly tested, otherwise you don't even have a guarantee that it's syntactically valid. I'm personally on the fence about whether that outweighs the cost of having a custom DSL, but I'm biased - I tend to avoid ORMs, anyway, so that I can have nice things like common table expressions and temp tables and PIVOT.

But if you're using a standard DSL such as .NET's LINQ, which everyone working on the platform already needs to know, anyway, then I can definitely see the attraction. That also eliminates a lot of the core complaint in TFA.

hn_throwaway_99• 5 years ago

There are much better solutions than ORMs that let you use native SQL but guarantee that you can't have SQL injections. This blog post, https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41..., is by the author of Slonik, of which I'm a huge fan. It takes advantage of tagged template literals in Javascript to make it feel like you're just concatenating strings, but actually you are creating prepared statements. E.g.

sql`SELECT foo FROM bar WHERE id = ${userEnteredId}`

gets converted into an object that is basically:

{ query: 'SELECT foo FROM bar WHERE id = ?', params: [userEnteredId] }

madsbuch• 5 years ago

I am not sure if this is a case against DSLs in general?

Eg. the security rules and query lang around Firestore locks down what the develop can ask for in a way that keeps queries run fast. Not doing so could pose a security risk. However, for an on premise setup with just a limited number of trained people it seems reasonable that they should have full power.

Ie. I am pro DSLs as they allow to mitigate other concerns.

luxuryballs2• 5 years ago

“Maybe European Union can mandate this” tongue-in-cheek? Maybe not but either way, NO! Keep the government out of my computer pls. Oh almost forgot to ask, do you agree to accept all of my cookies?

sharpercoder• 5 years ago

It is very high time for a successor on sql that solves sql problems. Verbosity, local functions, reversed order table/field select, reusability and other big problems should be solved by sql itself. A newer better version, perhaps compiling to backward compatible sql.

eatonphil2• 5 years ago

ORMs and SQL at least have the same goal which is to join datasets and perform operations on them.

SaaS platforms build _filter languages_ that are (sometimes vaguely) inspired by SQL. I really wish there was a standard among filter languages that was optimized for interactive search. Interactive search is different from application search because in interactive search you don't know what fields there are so you are mostly counting on substring matches. But you still want to be able to include multiple substring matches (AND) and exclude things as you find them that aren't what you're looking for (AND NOT).

I think Google's search language is one of the best filter languages out there in terms of simplicity/effectiveness. Splunk's language is good as well (but maybe they're just making up for it in good typeahead support).

I'd like a standard for Google's search language with implementations for in-memory filtering in every major programming language and implementations for generating SQL from the filter for every major programming language.

Until this happens there's not really much hope for standardizing on filters across applications.

dmitriid• 5 years ago

Splunk's language is good as well (but maybe they're just making up for it in good typeahead support).

Not just typeahead. A lot of Splunk's power comes from data transformations and filters.

  get_logs 
  | apply_transform 
  | merge with other logs (which can also be log|transform|filter|transform) 
  | apply more transforms 
  | filter 
  | expose as a specific structure (that is, transform)
  | filter more

This would be anywhere from pain to impossible with SQL.

eatonphil2• 5 years ago

That's awesome. Especially when graphing I desperately wish I could map values in ES.

jacquesm• 5 years ago

The bigger problem with ORMs is not that they require you to learn a new language, but that they are abstractions and very leaky ones at that. Typically they add a lot of complexity, caching (which seems like a great idea until you have a lot of fast updating data), make it harder to scale horizontally and so on. They also usually do not have a good impedance match with the underlying storage layers resulting in a lot of tricks to do stuff that would have been easier done by bypassing the ORM, but that then gives you inconsistent data because now you are also bypassing the cache of objects.

falcolas2• 5 years ago

SaaS providers who come up with their own unique query languages are evil. I’m looking at you, New Relic, with your NRQL bullshit.

Seriously, what is so ambiguous with “GROUP BY” that you had to invent your own keyword for it?

aquir• 5 years ago

"There should be a 30 year moratorium on inventing new query languages."

That! Totally agree!

weeboid• 5 years ago

This article would have a lot more impact if it was an unveil for a solution

luxuryballs2• 5 years ago

ha yea, give me your solutions not your problems!

ghc3• 5 years ago

And while we're at it, I don't want to learn your programming language. Everyone should just use Java for everything, FFS. /s

Seriously, the idea that everything maps well onto relational databases is incredibly misplaced. Not everything is tables, tuples, and relationships. Most RDBMSs graft a million slightly incompatible features onto SQL to attempt to handle all the things SQL doesn't understand, like GIS and full text search.

mumblemumble3• 5 years ago

Fulltext search is its own special thing, but most the other add-ons tend to be performance improvements or syntactic sugar, not filling in feature gaps.

The thing that's nice about RDMSes, and the reason why they've been so successful, is that they're built on top of a strong theoretical basis. The relational model is a bit like the lambda calculus. It's simple and relatively easy to understand, but can still provably handle just about anything.

ghc3• 5 years ago

There are actually a number of data models that the relational model doesn't handle well, and it's unclear if it's really even the best model. Back when I was in database research most of our projects focused on domains where the relational model was not a good fit. Some examples of unnatural mappings to the relational model include:

    - Documents
    - Spatio-temporal
    - Graphs
    - K/V
    - Trees
    - CRDTS
    - Images
    - Arrays

I'm sure there are plenty of others but those come to mind first. Most DB researchers I know would argue that if any database model is equivalent to the lambda calculus it would be graph theoretic model, because all other models can be expressed by graphs, but other models cannot universally express the graph model.

pwinnski6• 5 years ago

I don't understand the original article as stating that all data maps well onto an RDBMS, but rather than if you are using an RDBMS, you should use SQL rather than adding additional layers.

If you want to use a non-RDBMS, then by all means, SQL won't be a good fit. I use Amazon's DynamoDB all day, every day, and I wouldn't dream of using SQL there.

ghc3• 5 years ago

TFA complains about MongoDB and Lucene -- neither of which are RDBMSs -- having their own query language.

gwbas1c• 5 years ago

An ORM is a tool. They have their place: They aren't a replacement for knowing how to use a database; and you need to understand the performance / development time tradeoffs.

I had to fight a battle to remove a very slow ORM that was used for a SQLite database with 4 tables. After I removed the ORM from a critical code path, startup went from 24 hours to about 2 minutes. Raw SQL was "worth it" because the schema was brain-dead simple.

I more recently optimized some C# Entity Framework code that took minutes to run and brought it down to seconds. In this case, there were many tables and many joins, so going down to raw SQL wasn't "worth it." All I had to do was recognize that the code was relying on lazy-loading, and then I just pre-loaded the entities.

BUT: I should point out that ORMs can be extremely powerful. Using a tool like Linqpad with C# and Entity Framework, you can write ad-hoc queries much more easily than SQL.

geophile5• 5 years ago

A long time ago, I designed and created an ORM for Java. I brought it to a startup and we made some money selling it. One of the key features was that it went a long way to solving the N+1 problem, which was a unique feature at the time.

Obviously, I bought into the whole ORM concept, but I did have doubts in the back of my mind, some of which showed up in this article. You have to retrieve all of the columns needed to constitute an object. There is no subtlety in controlling the SELECT clause. Getting object identity right is hard, (e.g. you don't want to distinct Customer objects representing the same real-world Customer just because you ran two queries that happened to identify the same Customer). Caching is hard, especially when there are stored procedures doing updates and your ORM has no insight into them. Sometimes it would be nice to just write the damned SQL. Schema and/or object model changes are difficult, because you need to keep the two in sync. When I wrote my own database applications, I didn't want an ORM, not even mine. I wanted to write SQL. Having some tools to manage PreparedStatements and Connections would have been nice, but for my own work, I really preferred writing my own SQL.

But what finally changed my mind completely about ORMs was a hallway conversation with a developer at a prospect on Wall Street. (I think it was one of the firms to go under in the 2008 crash.) She told me that her resume looks a lot better with "5 years Oracle experience", than with "5 years XYZ ORM experience". This, combined with the doubts that were accumulating in my mind, finally pushed me over the edge.

In later years, playing with various web development frameworks, I dreaded dealing with ORMs. It's as the article said: I don't want to learn yet another query language, only to have that translated, badly, into SQL. Just let me write the damned SQL, it isn't that hard. I know exactly what SQL I want to run. Why should I create work for myself in convincing the ORM to do what I already know how to do?

coldtea• 5 years ago

"One of the key features was that it went a long way to solving the N+1 problem, which was a unique feature at the time."

In case someone else is wondering:

https://stackoverflow.com/questions/97197/what-is-the-n1-sel...

okareaman2• 5 years ago

Thank you for this. It's a time saver for a lot of people. Yesterday someone was throwing around "HKT" without definition. Wikipedia disambiguated it into 5 different things, none of which applied. I figured out it was Higher Kindred Types in Haskell, but imagine 10,000 people all wasting 5 minutes like I did.

geophile5• 5 years ago

Thank you, I should have included a definition or link myself.

chopin• 5 years ago

It however should be noted that JPA can run native (SQL) queries and even lets you map the result onto entities if those are a fit for the result set. I've used that with success even for partial results (not all columns selected).

It's just not typed but that is true of JPQL as well. And the criteria API is, well, ...special.

Cthulhu_• 5 years ago

Honestly the main reason I still use an ORM right now (Gorm, it's not the best), is to solve a number of annoyances I would otherwise have (and I may yet switch to straight SQL): mapping result sets to structs, updating relationships (treating nested objects as a single document), and database migrations.

I really don't mind SQL, I mind all the perceived boilerplate around it.

marvinblum• 5 years ago

There is sqlx for that: https://github.com/jmoiron/sqlx

geophile5• 5 years ago

Yes, exactly. When I write SQL applications now, I usually start by implementing a little database interface to take care of the boilerplate. The application can then provide SQL as needed.

stickfigure3• 5 years ago

I use everyone's favorite punching bag - Hibernate - a lot. I write all my queries in... SQL. It works pretty well this way.

The "ORM/SQL dichotomy" is greatly exaggerated.

geophile5• 5 years ago

You seem to be in agreement with the article and my top-level comment, since you aren't using the Object/Relational Mapping part of your favorite ORM. It is not surprising that the ORM/SQL dichotomy is exaggerated if you ignore the ORM.

ryanjshaw• 5 years ago

I believe it depends on what problem you are trying to solve. Surprisingly nobody has mentioned this in the thread, but domain-driven design (DDD) encourages you to split your problem domain into different bounded contexts (BC) and use the right architecture for each BC.

Classic example is an event booking system:

(a) Event Management is basic CRUD, so you use an ORM everywhere. There are no complicated queries here so ORM objections don't count.

(b) Ticket Management has multiple users, race conditions, performance requirements, etc. so maybe go the command-query separation (CQRS) route and use a full ORM for your write model and raw SQL, possibly a micro-ORM, for your eventually-consistent read model.

Why use a full ORM? You get to write your domain logic in your high level language with full unit testing, and the ORM worries about mapping this back to the database, dealing with concurrency and retry strategies automatically. For this to work you have to carefully design your business transactions against small aggregates containing a handful of entities, which is difficult to get right initially but has many advantages.

(c) Payments/Refunds are transactional, reliant on external services, and need a full audit history so maybe consider an event sourced approach with no ORM visible.

(d) Reports have no write model, so an ORM holds little advantage. Use SSRS, Power Bi, QlikView, etc.

etc.

valenterry2• 5 years ago

There is also the alternative of not using classical OOP but a functional style, where your types map much easier to what is in your database. Then you still use a tool to write in your language so that it is typesafe, but otherwise there is no magic mapping.

lwhi• 5 years ago

Abstraction is useful though. Don't tie yourself to specific database system.

lucasyvas• 5 years ago

This is, of course, good advice. But I'd add that query builder / micro ORM libraries are a better solution as they solve the (mostly common) interface, connection pooling, and multiple db driver parts.

This is plenty.

One's I've used:

knex - node.js

sqlx - rust

Arch-TK• 5 years ago

So this article is muddy.

The author has a beef with two things and claims to have a problem with a third. The author seems to dislike ORMs for their abstractness and general inefficiency when interacting with a SQL database (this is the nature of an ORM, either stop writing OOP code and as a result stop requiring an ORM, or use a graph database for your graph data (object state)). The author also seems to dislike SaaS query languages or something like that, a topic I don't know much about.

This gets all rounded up under the topic of "Query DSLs".

When I hear "Query DSL" I think SQLAlchemy Core. A library designed to provide an API to a SQL database which looks like you're writing python (implemented through overloading tricks and some fancy OOP metaprogramming tricks). SQLAlchemy Core is great, the author would not hate it because you're effectively writing SQL without all the footguns.

SELECT * FROM user; -> select(m.user)

SELECT * FROM user WHERE name == 'test' ORDER BY surname; -> select(m.user).where(m.user.c.name == 'test').order_by(m.user.c.surname)

By having python native code you can generate SQL statements using idiomatic python. If you do some kind of coverage tests (no idea what the kids these days call these but the idea is to run all the python code to ensure there isn't a low hanging fruit which would get caught at runtime) you can find 'select' typoed as 'slect' early on in development rather than it ruining your Friday when such a bug gets into production. This also makes parametrized queries painless which prevents many potential security issues with generating SQL.

If you want to you can also use the generic subset of SQL this way and SQLAlchemy Core will iron out the creases. But personally I think it's pointless to make a choice of SQL database if you're then going to not use all its special features. It's like writing some kind of polyglot-lang which can be translated to C, Java, Pascal, prolog, Lua, scheme and rust. You pick a language for the task, pick a database for the task too.

So it's important to isolate these features mentioned above from "ORM" and whatever the other thing the author was talking about. People use ORMs and they realise they suck and it's important to remember that although the core concept of an ORM is flawed, not all the features which come bundled with an ORM are bad ideas (or at least it doesn't mean that those ideas can't be done well when you are not trying to implement an ORM).

donatj• 5 years ago

For what it’s worth YQL was pretty neat back when Yahoo still had some decent engineering going on. Single SQL-like query language for accessing all of their services. Way way underutilized for how amazingly powerful it was.

I actually still had a little bit of code in production using it when they shut down.

megous• 5 years ago

I don't care if other people use ORMs or not, if they don't force me, but yes please on this: '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.' Some nice way to incrementally copy/sync my data from some saas into my database would be great. Even a full data dump from some JSON endpoint is good enough if it has stable unique keys on all entities, thanks to UPSERT being pretty good for this kind of data syncing.

bxrxdx• 5 years ago

I agree with this so much omg.

jariel• 5 years ago

More apt: Can we agree on a simple, modern query language that is better than SQL, which is fine but a little antiquated? Because the issue is standardisation more than anything.

default-kramer• 5 years ago

I sympathize with the author -- I also choose plain old SQL whenever possible. But SQL is not a good option. It is the least bad option. I even worked on my own alternate query language, Plisqin: https://docs.racket-lang.org/plisqin/index.html I was never expecting it to gain adoption. But if any SQL alternative ever does gain adoption (in the same way Typescript has for JS), I really hope it looks at least a little bit like Plisqin. Specifically, when joins are values and not language constructs, things get a lot better. Also, please satisfy these 3 rules: https://docs.racket-lang.org/plisqin/research-language.html#...

mkl95• 5 years ago

I enjoy using the SQLAlchemy ORM. It makes it trivial to divide a query in several parts that you can mix and match.

In my experience, features such as a search engine that supports many filters are much easier to maintain if they are written with an ORM rather than with raw SQL. I don't have a preference for simple queries.

On the other hand, I find MongoDB's query language and its popular Python ORMs (Pymongo and Mongoengine) painful to work with.

thehappypm• 5 years ago

I found that Rails' way of doing data access -- which completely hides the SQL from you -- to be really confusing, but people seem to absolutely love it once they get it under their feet. I prefer to write SQL/sprocs. What's the consensus these days?

GLGirty• 5 years ago

This is an essay version of xkcd 'standards'. It's not wrong, but it's not the entire picture.

The author is correct, sql does everything they need, cause it's so expressive. (Mysql8 is turing complete with it's domain specific syntax duh-duh-daah...) But that power comes at the cost of scrutiny and composition.

ORMS are fish in a barrel--they fail because they are the wrong abstraction. Queries shouldn't be the language, they should be the verbs in the language.

With a very restricted query AST, introspection and composition are possible. If queries point to the same table, you can combine them into a new query whose result cells are union or intersection of the input result cells. You can automate joins and subquery indexing. You can recover from a dropped column by replacing references to it with null, and limp by with partial results instead of no results.

I agree with the author's hate on DSLs as a substitute for sql... when they have the same level of abstraction. But if you want queries to be lego bricks that snap together, the bricks can't have NP hard surface area. DSLs fill that need.

lipanski• 5 years ago

I recently installed ClickHouse (part of my self-hosted Plausible setup) and had to modify a record inside the database. At this point my only knowledge of ClickHouse was that it comes with an SQL interface. So I opened up a client and typed "SHOW DATABASES" and guess what - it showed me a list of all databases. Then I typed "USE mydatabase" and I was connected to my database. I typed "SHOW TABLES" and got a list of tables, followed by "DESCRIBE TABLE users" and "UPDATE users SET email_verified = true" (FYI I was trying to avoid having to set up SMTP credentials for Plausible). I was able to use ClickHouse without any prior knowledge because the authors decided to based it on a well-known and fairly simple standard instead of inventing their own.

It felt as good as building Ikea furniture without checking the manual and it's what user/developer experience should be about.

hodgesrm3• 5 years ago

You've used MySQL, I would guess. Personally I like the superficial similarities to basic MySQL syntax in ClickHouse. MySQL and Sybase T-SQL have always struck me as the friendliest SQL dialects.

asimjalis• 5 years ago

I like SQL. The problem with SQL is that it is a string and simple errors, like an extra comma, are not caught by the compiler/interpreter. Are there good well-thought out libraries that are isomorphic to SQL but can be used with a language like Python or Clojure?

For example, I want to write simple SQL queries this way:

  (-> "table" (cols col1 col2 col3) (where (= col1 10)) (order-by col3 :desc))

johannes1234321• 5 years ago

If you love C++ Template magic look at https://github.com/rbock/sqlpp11

This allows "normal" C++ code, which by the compiler is converted into the query string, allowing code like

    for (const auto& row : db(select(all_of(foo)).from(foo).where(foo.hasFun or foo.name == "joker")))
    {
        int64_t id = row.id; // numeric fields are implicitly convertible to numeric c++ types
    }

(Random pick from the README)

adamkl• 5 years ago

I believe Honey SQL might be just what you are looking for:

https://github.com/seancorfield/honeysql

adsharma• 5 years ago

I wrote such a C++ implementation during my days at Facebook around 8 years ago:

https://engineering.fb.com/2016/03/18/data-infrastructure/dr...

There is an example under "Functional programming primitives".

It was a C++ implementation that fell victim to Greenspun's 10th rule. So I wrote a specification for it, first in Clojure and then in python.

The C++ execution engine is open source:

https://github.com/facebookarchive/iterlib/

Main problems writing such code:

* The output of SQL is generally flat. GraphQL makes it nested, but doesn't support all the operators SQL does natively. * Anyone trying to implement such an engine needs to ensure a fundamental property - the shape of the output can be inferred from the shape of the query. * Writing async code in C++ with futures and promises is like pulling teeth. At some point the complexity explodes and the code becomes unmaintainable.

My most recent attempt is here:

https://adsharma.github.io/fquery/

It shouldn't be hard to put a wrapper around fquery that looks like the query string you present above. In fact, there is a s-expression parser in iterlib/python above that could be tweaked to do this.

I'm hoping that we can build a community around such languages and once the specification is agreed on, a more performant implementation can be written in a systems programming language.

Scarbutt3• 5 years ago

The problem with SQL is that it is a string and simple errors, like an extra comma, are not caught by the compiler/interpreter.

Why not test it at the Clojure REPL?

MrPowers• 5 years ago

Spark lets users query via regular SQL or DSL (Scala, PySpark).

The DSLs are generally better to work with. Complex queries can be broken up into composable functions that can be mixed and matched to perform different analyses. Functions are easier to unit test, document, deal with quoting better, can be type safe, etc.

Some super-complex queries are still better to write with regular SQL.

New query DSLs don't need to reinvent the wheel. They can implement their query capabilities using the Row / Column / DataFrame abstractions that are elegant and familiar in popular projects like Spark. We need an ANSI query engine DSL.

bionhoward• 5 years ago

heck, what if we just make the database an immutable graph, and use functional programming on it in our language of choice? oh wait, that's Datomic. Except, Java is quite lame. Let's rewrite Datomic in Rust please!

teddyh• 5 years ago

A response to this article, I Don't Want to Teach my Garbage DSL, either, was discussed here on HN almost two years ago: https://news.ycombinator.com/item?id=20059029

In that discussion, I made this comment¹, which I still stand by:

If you’re developing a DSL which is just a query language, you are reinventing the wheel, and you should ask yourself if any benefit of your language over SQL is worth the effort of all your users to learn your new query language. It may be worth it of your data can not be usefully be modeled by tables; e.g. document query languages like XQuery and even simple XPath are useful and can not be easily replaced with SQL.

1) https://news.ycombinator.com/item?id=20059337

neduma• 5 years ago

DSLs are modern day version of language explosions in a niche ecosystem (CS) in a short period.

Interested in how DLS like expression evolving on other industries like Music, Science, etc

khaledh• 5 years ago

One thing that ORMs shine in is query composition. For example, starting from a base definition for retrieving a list of entities, you can _dynamically_ compose orthogonal operators that modify the base query, e.g. projection, filtering, sorting, grouping, joins, etc. without having to manually write SQL for every combination of those operators; the ORM takes care of generating the SQL code for you.

That being said, ORMs still don't enjoy the level of trust that optimizing compilers have enjoyed for decades. That's always going to be a barrier for wide adoption, especially from folks who are experienced in SQL. It's similar to how in the 1950s (and maybe somewhat 1960s) there was resistance for "automatic coding" by compilers; those who were experienced in assembly thought that compilers will always produce sub-optimal code. But it's clear that high-level languages have won (at least outside embedded/low-level drivers).

The point is: any form of code generation will always find resistance until it proves itself.

flukus• 5 years ago

I agree they shine at dynamic query composition, but I find the sort of UI that necessitates this dynamism are universally terrible. They end up being data dumps that allow the user to filter and sort themselves instead of having a UI that actually understands their workflow and the data the need to accomplish it. It's like sending the user an excel file and saying "here, you can manipulate it how you want".

This is also something light weight orms or query generators handle fairly well, if not better.

brundolf• 5 years ago

There are three fairly independent points being made here:

1) ORMs (which are used in cases where you have control over the DB) are bad and you should just use SQL

2) DSLs (in non-SQL DBs) are bad and the devs should have just exposed SQL

3) DSLs (in opaque services) are bad and the devs should have just exposed SQL

I tend to agree with #1, though it's complicated and there's lots to be said on both sides.

#2 seems semi-reasonable, if the semantics are close enough to those of a SQL DB, but the problem is that DBs like Mongo have wildly different semantics from SQL.

#3 is harder because an opaque service usually specifically wants to insulate users from its implementation details. The great majority will not want to give you direct access to a SQL database. So the only alternative would be to "fake it" and pretty much base their DSL on SQL, which may or may not line up with the semantics of their service.

For both #2 and #3, exposing SQL when the underlying semantics may or may not be a good match for the query language seems questionable at best. It encourages assumptions to be carried over that may not hold, especially around performance characteristics (which the OP specifically mentions as an advantage, surprisingly). I just don't see how this is tractable in the general case.

echlebek• 5 years ago

Nobody seems to have posted Stuart Halloway's Narcissistic Design yet, so I'll just leave this here.

https://www.youtube.com/watch?v=LEZv-kQUSi4

Has some great insights and quips. "Nothing says 'screw you' like a DSL"

edumucelli2• 5 years ago

What about Elasticsearch that invents and deprecates features for it's query language on every major release. Then you have several ways of doing the same query with all kind of filters, all of them which can't tell you if a certain field is NULL.

starik36• 5 years ago

The ElasticSearch language is so wordy and obtuse and difficult to remember, that I just translate SQL to it and go from there.

https://sqltoelasticsearch.azurewebsites.net/

edumucelli2• 5 years ago

That is a good idea. Elasticsearch is going on that direction, they have admitted what was obvious: https://www.elastic.co/what-is/elasticsearch-sql

nickelcitymario• 5 years ago

This can be abstracted to a general critique with frameworks overall.

On smaller scale projects, cut out as many frameworks as possible. Stick with the bare minimum of tools. Pure Ruby, versus Rails. Pure Javascript versus jQuery. SQL queries versus ORM. Etc. It's less to maintain, less to learn, AND it forces you to learn the underlying technology.

On larger-scale and longer-term projects where you expect to work with others and/or will need to hop back into the code on a regular basis for years, cautiously introduce frameworks (and ORMs). In those cases, frameworks solve more problems in the long run. I'm usually annoyed early on, as frameworks force you to learn their DSLs (etc). But there's always a point where the benefits start to outweigh the initial negatives.

Seb-C• 5 years ago

I totally agree with the views of the author. That is basically why I started to build an ORM that does not include a query builder. Best of both worlds.

Now the design was far from easy, and keeping things reasonably simple without abstracting the SQL part was my biggest challenge. And this would not have been possible without the template-string tagging feature of modern JS, so it wouldn't be doable for many other languages.

https://github.com/Seb-C/kiss-orm

lelanthran4• 5 years ago

I don't see this anywhere in this thread: http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...

That is essential reading for anyone arguing against or for ORMs.

dsimms• 5 years ago

there are so many intersecting problems here, that I feel like we argue about different ones at the same time.

Are you a human and want to query a database? SQL is probably it. (a SQL of some sort, but a pretty good shared understanding) Even if it's not relational exactly, it probably comes with some project with things like records/rows and columns/fields.

Do you have some kind of relational model that needs accessing? SQL sounds pretty good, and you know it from writing queries as a human...

But should you construct queries with string concatenation? No! Absolutely not.

Do you have to write the queries yourself? Like what if you want to stick an object in a database? Can't it write the query for you? It's appealing, but leads to sadness and anger at some point. (Joins? Conditions of more than 2 degrees? lots of dark corners very close by)

AND whatever model objects are used become a defacto database schema, so you can't really refactor them in the same way you would other models. So you better not expose them past whatever datastore touching interface you have. So why not just define them in SQL DDL in the first place?

What if you want to write the query (or need to to be sure you're getting exactly what you expect) but type-safely? LINQ? Absolutely! jOOQ? Sounds good! Arel? Sure! And maybe you get some amount of programmatic composability as well with any of these. Thankfully they're not really ORMs or extremely thin ones at that.

I don't want to learn your query language (2018)

Related Stories