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...
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Almost. The best of both worlds would be an ORM that can understand the SQL query and populate its objects/structs/whatever with the result. I have to manually process the results now.
If I had an ORM like that maybe I would write all SELECTs directly in SQL.
What makes that impractical in projects with multiple developers is that about half of them don't know SQL nowadays. They know how to use the ORM and the language to write CRUD queries and that's all. If they see SELECT * FROM invoices WHERE customer_id = ? they probably understand it. Nested queries, HAVING, GROUP BY... oops! CTEs... OMG! This means that sometimes they write three or four queries when one would be enough.
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.
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.
I think it comes from experiences of coming in to codebases and seeing that people are using the ORM for everything, even when it's obviously suboptimal and raw SQL would be better.
Compromise, best tool for the job, nuance, etc - all tend to get tossed out when people make tech decisions. Someone using an ORM may not even be aware that raw SQL is possible.
I wouldn‘t say its buried, at least not in the discussions I have been in, but the problem is that even with custom queries you are still in this weird place between fully managed ORM queries and raw SQL queries. How does the result map to data structures? How does it handle colliding column names in joins? Does the ORM handle over complete control when using custom SQL? There are tons of questions like these that are not uniformly answered by different ORMs.
I am not saying that ORMs never work. They sure do in a lot of cases, and some ORMs more than others. But an ORM is not a silver bullet – far from it. There are gotchas, and you need to invest time in understanding how they work. Just as you need to understand how SQL works.
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.
In Python/Node: I'd never use an ORM for handling 5 tables. I'm just fine writing/maintaining my 5-10 queries in plain SQL. Centralize all the queries in a single file and that's it.
In Java/C#: Do I even have the choice? Even for 1 table it's probably easier to let the ORM do its job than fighting the typing system.
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.
> 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.
SQL columns can be referred to either by name or by index. Avoiding positional semantics means using "ORDER BY transaction_date ASC" rather than "ORDER BY 3 ASC". On the calling code, your python library may return either a tuple or a dictionary. The dictionary is the right way to do it, because then you access parameters by name, and reordering or adding parameters to the query output doesn't break your script.
Less about the exact syntax and more about the tool, for example: https://github.com/sqlkata/querybuilder. I just chose that since it was on top of a search but the idea is the same. Your code generates raw SQL, so it's 100% interchangeable with writing SQL yourself however the builder library deals with the syntax, proper ordering, quoting, full attribute names, etc. Some such libraries even let you define your schema in code to make your SQL generation type safe.
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.
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.
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.
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.
You can do raw SQL with EF, but you lose a lot in the process.
EF Core -can- be better than the older, much more reviled EF as far as generated SQL, but in EF core 3 they did a pretty big breaking change on joins, bringing behavior closer to EF6's tendency to do a cartesian asplosion
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.
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.
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.
> 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.
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.
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.
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.
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.
> 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.
> 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.
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
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.
And to be fair, I wouldn’t have found the relational model without SQL. I wish it were more common to write about both in SQL tutorials and such, but I understand the motivation to write about what people can do with an implementation rather than the mathematical and logical underpinnings.
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.
> 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).
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.
> 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.
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!
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.
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.
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.
I couldn't find exactly an only Core example that you are looking for in the examples folder [0] so here is a more complicated tutorial query [1] and here's how I use Core in my project [2] (mostly short select statements so not really a comprehensive demonstration of SQLAlchemy Core's capabilities).
> My dream is to write actual SQL in the middle of my code, as a valid syntax and first-class construct. As I said, that's not really a realistic goal.
Sure, it is. See how regexes in some languages are first class datatypes, so you can have a regex literal in your code (with backreferences too, I believe).
There is no good reason that SQL statements can't also be a datatype, with SQL literals in the code like the way regexes do. I'm imagining something like this:
The stmt above is not a string, hence is part of the AST and can be checked at compile time. The execution might be a problem (returned columns have to be checked too).
> 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.
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...
That's what adhoc/dynamic SQL is for if you must, and power it with another language - you are trying to loops in a set based language, its the opposite of what its good at.
That sounds like switch abuse, but a better-written query builder seems like a good solution here. Use `for each param` to build a SQL query, and you're golden.
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.
47 years on, most experienced developers seem to agree that SQL is better extended than replaced, but innovations like graph databases and "NoSQL" document stores have been received very well.
I can. The churn causes a lot of wheel reinvention, bugs out the ass, security vulnerabilities, dependency hell, and I know others here could go on a rant about something I'm completely missing.
We shouldn't stop innovating but we should sure as hell should stop chucking half baked shit into production.
One measly example: Cypress. Everyone raves about it in blog posts, some dev immediately downloads it into their project and does a POC and calls it good. Fast forward to mid-late project and you are upgrading, downgrading, refactoring, and spending hours and hours trying to work around the issues. It's a great innovation, but my god. Self-induced stress.
> 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.
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.
> 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.
> 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.
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.
But my view at this point in my life is that SQL is SO not a simple language, that any and all ORMs are going to be way too restrictive and leaky. There's all kinds of weird crap that SQL does when it comes to nulls, empty strings, falsey/truthy things, dates as numbers or strings, etc. And that's not even thinking about unions, stored procedures, weird types of joins, etc.
My current comfort zone is if I can find a query builder that has enough static typing that it has all of the keywords of my preferred SQL flavor, has prepared statements with placeholders- mainly for safety/security, and basically returns a string when you're done.
At least then I'm just dealing with SQL instead of figuring out Hibernate's arcane caching feature or why in the FUCKING FUCK JDBC will return a `0` if a column was an int value that was `null` in the result set. IT WAS NULL- GIVE ME NULL.
Which JDBC implementation? I can write a crappy JDBC driver on top of text files and have it mangle your data in a myriad of ways. I doubt most of the people using JDBC have to deal with what you describe because they are using a mature driver for their particular database--most often developed by the database OEM.
Here's the code from the current version as of this message. It's in mysql-connector-java-8.0.23.jar. Class com.mysql.cj.jdbc.result.ResultSetImpl. Lines 814-818:
@Override
public int getInt(int columnIndex) throws SQLException {
Integer res = getObject(columnIndex, Integer.TYPE);
return res == null ? 0 : res;
}
Notice that the Java guys set the return type to `int` and not `Integer` in the `ResultSet` interface. So, if you call this method after retrieving a null value from a nullable column, you can either throw an exception or return something. I think it's absolutely insane to return a 0 here, but this is the way the MySQL connector has been forever.
> My current comfort zone is if I can find a query builder that has enough static typing that it has all of the keywords of my preferred SQL flavor, has prepared statements with placeholders- mainly for safety/security, and basically returns a string when you're done.
I'm intending to rewrite it ("the first one is always to throw away" - I put too much unnecessary functionality into it and not enough RDBMS server backends) but I've used it in a few projects (use the latest branch) and am happy with it for postgres or sqlite usage.
> 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.
> 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 :-)
I've used Hibernate and JPE for several projects. My first job was moving PL/SQL + C++ software to J2EE.
Recently I had a chance to work on a simple 6-months 4-developers Java project from scratch. We had to interface with legacy software through an Oracle db and I was getting ready to use Hibernate again but our team lead asked if we really want to use it or just do it cause it's the "best practice". The project was pretty simple from data POV and we were mostly fetching lots of records to be processed and bulk updating a few "status" fields, but I've seen simpler projects using Hibernate for no good reason.
Using SQL directly was a breath of fresh air and I estimate it cut at least a month from our schedule.
For me, the issue isn't performance or even n+1 (though I do think it's easily to accidentally do stuff like that, even if you "know what you're doing").
You're probably a Hibernate expert. I'm not. I used it once, several years ago. I'm a polyglot, Jack of all trades, developer- I know lots of languages reasonably well, I know several high profile frameworks well enough that I at least know what to look up when I need to, etc.
Hibernate has a steeper learning curve than you might remember if you've been using it extensively or for a long time. I can't give specific examples, because it's been some time, but I just vaguely remember it having some of the same rough edges as other annotation-heavy Java frameworks- some of the annotations didn't actually work together the way I expected, it's mostly only checkable at runtime, remembering to use `@Column(nullable = false)` instead of just `@NotNull` (I just looked that one up because I remembered it), etc.
I also vaguely remember some issue where I had accidentally defined a class field as `int` when the column in the database was nullable. Then, instead of exploding when the ORM pulled a null out, I think it just magically converted the null to 0 and set my field to 0. Took a while to figure out why we had such strange results for some data. I'm not totally positive if I'm remembering that right, though.
You can accuse me of laziness for not wanting to learn the intricacies, shortcomings, opinions, and foot-guns of an ORM like Hibernate (and keep up to date with it even when I'm not doing Java this month/year). But I don't see it that way. I see it as being defensive. There is no way I'm going to be able to remember Hibernate's weird parts, AND Symfony's weird parts, AND ActiveRecord's weird parts, AND whoever else's weird parts. If I can just put the thinnest possible layer over SQL, map the result columns to specific types, and then build my object(s) from those "by hand", I'm perfectly happy. And I don't believe that my dev speed is slowed down by a meaningful amount. Will that part of the code take me longer to write compared to an expert in Hibernate? Probably. But that's probably not a significant amount of time relative to the whole project. And it saves me a lot of ramp up and debugging time. This is what I've convinced myself of, anyway.
Plus there's basically always an inefficiency in modeling an entity class per table. What if one of my domain types is constructed by taking a subset of multiple tables' columns joined together? Do I have to define an Entity class for every subset of columns I plan to use? That would be tedious, and POSSIBLY even more so than just writing some SQL and getting a typed tuple out per result row. Or can Hibernate et al understand pulling partial entities somehow? Is this easy and simple or does it require me to spend a couple of hours reading documentation and figuring out tricky annotations?
Everyone talks about these 80% rules: ORMs make 80% of stuff trivial and then you just break out the SQL when you need it. To me, that's a losing proposition. That 80% was already trivial to me. Writing a basic JOIN is trivial. It's the more complex stuff that I'm worried about and it just doesn't seem like ORMs do much for us there.
Yes, on your case there's hardly any reason to use an ORM.
I have worked with Symfony but these days it is always .Net Core or Java it seems and I can live with that (thankfully none of the clients I work for use Javascript or on the backend. One used Python with plain SQL and secrets stored in the application code though :-)
> 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.
Right. Agreed. I'm definitely not trying to say anything positive about SQL.
ORMs are a doomed proposition. There is simply no way to write a database-agnostic ORM API while also taking full advantage of the underlying tech.
Very simple examples include whether to return rows that have been inserted. In Postgres, you can do that in a single query. In MySQL, it's two queries because INSERT does not return the inserted row. So if I'm using MySQL and a popular "agnostic" ORM I have to realize that I'm (probably) doing an extra query on every insert, even if I don't use the result of the second query.
Then, of course, some of these ORMs don't even implement any of the database-specific features, like MySQL's fulltext search, because they're catering to the least common denominator.
> ORMs are a doomed proposition. There is simply no way to write a database-agnostic ORM API while also taking full advantage of the underlying tech.
Not every application needs to take full advantage of the underlying DBMS. Sometimes it's just a place to put data and not lose it, e.g. a key-value store with transactions. ORMs handle that use case perfectly well.
(I'm not a fan of ORMs personally, but they seem to work fine for logs of applications.)
> Not every application needs to take full advantage of the underlying DBMS. Sometimes it's just a place to put data and not lose it, e.g. a key-value store with transactions. ORMs handle that use case perfectly well.
Okay, yes... But an ORM is a TON of baggage for such a trivial use case, is it not? And these data have no interesting relationships to each other at all?
Not to mention that transactions work differently across DBMSs, too. So what is your ORM's default? Is it the same default as your DBMS? Does it know that MySQL doesn't support nested transactions, but Postgres does? Do you (metaphorical "you")? Do you fully understand what happens if you try to nest transactions in your ORM?
Also, do you understand how your ORM is going to convert data types and which ones are supported? Remember that JavaScript numbers don't even have the full range of a MySQL/Postgres BIGINT? Probably your programming language doesn't even support DECIMAL types natively- is your ORM going to just cast a DECIMAL to a float for you, or give you an error? Which is worse (I have my opinion)?
What about Dates and Times? How will your ORM handle timezones? How does your DB handle timezones?
ORMs don't seem to help with any of this. In fact, it seems like it only complicates things even more because you have to figure out how your DBMS works AND what your ORM is going to do about it.
Sometimes it's the lesser of two evils. I'm working on ClickHouse SQLAlchemy driver support to enable better integration with Superset. Superset talks to a bunch of backends and chose SQLAlchemy as the API. It has many of the problems you mention, though since it's read-only there are perhaps fewer of them. Superset does workarounds on top, but they don't have to worry about basic stuff like listing table metadata, distinguishing between tables and views, etc.
Using an ORM seems like a reasonable choice for multi-platform use cases or at least some of them. The alternative would be to implement something more or less from scratch.
Also, all languages I used support parametrized queries so you do not have to mix data and query strings. Not converting data to strings is not an argument for ORMs, it's an argument for parametrized queries.
People should really look into JOOQ to see what a SQL friendly ORM looks like.
> Also, all languages I used support parametrized queries so you do not have to mix data and query strings. Not converting data to strings is not an argument for ORMs, it's an argument for parametrized queries.
Your "parameterized" queries are still serialized strings. Many language bindings for SQL interfaces don't have good support for serializing data types, so you will often see things like ?::datetime in the template string. Some don't have the ability to parameterize column names (or have subtle bugs) either.
> People should really look into JOOQ to see what a SQL friendly ORM looks like.
I've never heard of JOOQ. Do you know of a good introduction?
> Your "parameterized" queries are still serialized strings. Many language bindings for SQL interfaces don't have good support for serializing data types, so you will often see things like ?::datetime in the template string.
How else would you do it, short of a binary query format?
I think the first step to better languages is to go back to basics. SQL queries are turned into imperative query plans [0]. Expose that interface. And polish it as nice as possible. When that is done, that's when we can start building alternate abstractions on top of it.
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.
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.
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
There's a whole ecosystem around using React for static site development now, this is exactly what Gatsby is if I'm not mistaken. Use React as a more powerful templating system for authoring content, but generate most of it out as static HTML/CSS, while still having the capability for individual components to have interactivity / data fetching post load.
Also, I think this is what's going on with NextJS and React's server-side-components stuff. Having solved client-side webapps, the world has now turned to reinventing Cold Fusion...
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 ...
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...
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
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.
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.
Like any toString() implementation, it is meant for debugging. If the object you're calling toString() on is "attachable" (e.g. a Query), then you get the vendor specific string for additional convenience. If you just call substring(a, b, c).toString(), you'll get a generic rendering.
If you always want the vendor specific SQL string, use DSLContext.render(QueryPart)
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.
> 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.
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.
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.
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.
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.
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.
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.
I'm very curious about this, what were the reasons for changing between all those databases?
You can't be using any advanced features if you're supporting SQL, NoSQL and even Excel, so if you only need basic features why are you bothering with switching so much?
I know of one good reason to support multiple databases, when you're writing software for several enterprise clients and each of them wants to use whatever databases their teams are used to managing.
> 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.
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] }
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.
“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?
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.
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.
> 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.
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.
some of those SaaS products aren't "ORM"s and don't make sense to use SQL. Splunk is a good example. arguably it's not SaaS either (looks at massive server stacks full of splunky-town hosts)
222 comments
[ 7.1 ms ] story [ 270 ms ] threadAlso as far as I know React has native components, not sure how much HTML decoupled are these.
>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.
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.
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.
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.
> 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.
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.
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.
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.
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.
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.
ORM is necessary, otherwise we will find ourselves in the old days again, a large part of string concatenations, security problems, etc,.
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.
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.
If I had an ORM like that maybe I would write all SELECTs directly in SQL.
What makes that impractical in projects with multiple developers is that about half of them don't know SQL nowadays. They know how to use the ORM and the language to write CRUD queries and that's all. If they see SELECT * FROM invoices WHERE customer_id = ? they probably understand it. Nested queries, HAVING, GROUP BY... oops! CTEs... OMG! This means that sometimes they write three or four queries when one would be enough.
For the scenario you're describing, you could still use a mix of an ORM and raw SQL as needed.
Compromise, best tool for the job, nuance, etc - all tend to get tossed out when people make tech decisions. Someone using an ORM may not even be aware that raw SQL is possible.
I am not saying that ORMs never work. They sure do in a lot of cases, and some ORMs more than others. But an ORM is not a silver bullet – far from it. There are gotchas, and you need to invest time in understanding how they work. Just as you need to understand how SQL works.
In Python/Node: I'd never use an ORM for handling 5 tables. I'm just fine writing/maintaining my 5-10 queries in plain SQL. Centralize all the queries in a single file and that's it.
In Java/C#: Do I even have the choice? Even for 1 table it's probably easier to let the ORM do its job than fighting the typing system.
Perfect solution to what problem?
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.
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.
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.
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.
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.
EF Core -can- be better than the older, much more reviled EF as far as generated SQL, but in EF core 3 they did a pretty big breaking change on joins, bringing behavior closer to EF6's tendency to do a cartesian asplosion
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.
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.
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.
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.
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.
Speak for yourself. My app talks in dataframes.
I'm just complaining about the "everything is a webapp backend written in a scripting language" mentality that pervades everything in IT now.
"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.
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.
https://www.dbdebunk.com/
You can view source to see the HTTP parameters or just WireShark it.
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.
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.
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!
Then along came entity framework which also supports LINQ querying syntax (or you can use expressions).
https://github.com/kyleconroy/sqlc
[1] https://en.wikipedia.org/wiki/ABAP
[2] https://help.sap.com/viewer/fe24b0146c551014891ad42d6b2789e5...
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.
Do you have some examples of SQLAlchemy core layer you're talking about, just to have an idea of what you have in mind?
[0] https://github.com/sqlalchemy/sqlalchemy/tree/master/example...
[1] https://docs.sqlalchemy.org/en/14/core/tutorial.html#common-...
[2] https://github.com/atlasacademy/fgo-game-data-api/tree/maste...
Sure, it is. See how regexes in some languages are first class datatypes, so you can have a regex literal in your code (with backreferences too, I believe).
There is no good reason that SQL statements can't also be a datatype, with SQL literals in the code like the way regexes do. I'm imagining something like this:
The stmt above is not a string, hence is part of the AST and can be checked at compile time. The execution might be a problem (returned columns have to be checked too).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.
myFun param =
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...47 years on, most experienced developers seem to agree that SQL is better extended than replaced, but innovations like graph databases and "NoSQL" document stores have been received very well.
We shouldn't stop innovating but we should sure as hell should stop chucking half baked shit into production.
One measly example: Cypress. Everyone raves about it in blog posts, some dev immediately downloads it into their project and does a POC and calls it good. Fast forward to mid-late project and you are upgrading, downgrading, refactoring, and spending hours and hours trying to work around the issues. It's a great innovation, but my god. Self-induced stress.
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.
Thanks for sharing go-structured-query, I didn't know about it.
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.
https://blog.jetbrains.com/idea/2020/06/language-injections-... (Overview with link to the full docs)
It’s been a while since I’ve used it but it works great (and not just for SQL but pretty much anything IntelliJ knows about).
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.
My current comfort zone is if I can find a query builder that has enough static typing that it has all of the keywords of my preferred SQL flavor, has prepared statements with placeholders- mainly for safety/security, and basically returns a string when you're done.
At least then I'm just dealing with SQL instead of figuring out Hibernate's arcane caching feature or why in the FUCKING FUCK JDBC will return a `0` if a column was an int value that was `null` in the result set. IT WAS NULL- GIVE ME NULL.
Here's the code from the current version as of this message. It's in mysql-connector-java-8.0.23.jar. Class com.mysql.cj.jdbc.result.ResultSetImpl. Lines 814-818:
Notice that the Java guys set the return type to `int` and not `Integer` in the `ResultSet` interface. So, if you call this method after retrieving a null value from a nullable column, you can either throw an exception or return something. I think it's absolutely insane to return a 0 here, but this is the way the MySQL connector has been forever.EDIT: Postgres does the same thing: https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main...
Some self-promotion (shameless, I know): https://github.com/lelanthran/libsqldb/tree/v1.0.0-rc2
I'm intending to rewrite it ("the first one is always to throw away" - I put too much unnecessary functionality into it and not enough RDBMS server backends) but I've used it in a few projects (use the latest branch) and am happy with it for postgres or sqlite usage.
See https://github.com/lelanthran/libsqldb/blob/v1.0.0-rc2/src/s... for example usage, but the basic premise is:
1. Send parameterised string to DB.
2. Get back records one at a time as an array of strings (NULLs are empty strings, as are empty strings) no matter the datatype of the column.
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.
cough Parler springs to mind.
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 :-)
Recently I had a chance to work on a simple 6-months 4-developers Java project from scratch. We had to interface with legacy software through an Oracle db and I was getting ready to use Hibernate again but our team lead asked if we really want to use it or just do it cause it's the "best practice". The project was pretty simple from data POV and we were mostly fetching lots of records to be processed and bulk updating a few "status" fields, but I've seen simpler projects using Hibernate for no good reason.
Using SQL directly was a breath of fresh air and I estimate it cut at least a month from our schedule.
Sounds like a perfect example for when you just want to whip up a few SQL queries, yes.
I'm not against raw sql.
I'm just against people presenting it as either/or and claiming that there is no gopd use case for ORMs.
You're probably a Hibernate expert. I'm not. I used it once, several years ago. I'm a polyglot, Jack of all trades, developer- I know lots of languages reasonably well, I know several high profile frameworks well enough that I at least know what to look up when I need to, etc.
Hibernate has a steeper learning curve than you might remember if you've been using it extensively or for a long time. I can't give specific examples, because it's been some time, but I just vaguely remember it having some of the same rough edges as other annotation-heavy Java frameworks- some of the annotations didn't actually work together the way I expected, it's mostly only checkable at runtime, remembering to use `@Column(nullable = false)` instead of just `@NotNull` (I just looked that one up because I remembered it), etc.
I also vaguely remember some issue where I had accidentally defined a class field as `int` when the column in the database was nullable. Then, instead of exploding when the ORM pulled a null out, I think it just magically converted the null to 0 and set my field to 0. Took a while to figure out why we had such strange results for some data. I'm not totally positive if I'm remembering that right, though.
You can accuse me of laziness for not wanting to learn the intricacies, shortcomings, opinions, and foot-guns of an ORM like Hibernate (and keep up to date with it even when I'm not doing Java this month/year). But I don't see it that way. I see it as being defensive. There is no way I'm going to be able to remember Hibernate's weird parts, AND Symfony's weird parts, AND ActiveRecord's weird parts, AND whoever else's weird parts. If I can just put the thinnest possible layer over SQL, map the result columns to specific types, and then build my object(s) from those "by hand", I'm perfectly happy. And I don't believe that my dev speed is slowed down by a meaningful amount. Will that part of the code take me longer to write compared to an expert in Hibernate? Probably. But that's probably not a significant amount of time relative to the whole project. And it saves me a lot of ramp up and debugging time. This is what I've convinced myself of, anyway.
Plus there's basically always an inefficiency in modeling an entity class per table. What if one of my domain types is constructed by taking a subset of multiple tables' columns joined together? Do I have to define an Entity class for every subset of columns I plan to use? That would be tedious, and POSSIBLY even more so than just writing some SQL and getting a typed tuple out per result row. Or can Hibernate et al understand pulling partial entities somehow? Is this easy and simple or does it require me to spend a couple of hours reading documentation and figuring out tricky annotations?
Everyone talks about these 80% rules: ORMs make 80% of stuff trivial and then you just break out the SQL when you need it. To me, that's a losing proposition. That 80% was already trivial to me. Writing a basic JOIN is trivial. It's the more complex stuff that I'm worried about and it just doesn't seem like ORMs do much for us there.
Yes, on your case there's hardly any reason to use an ORM.
I have worked with Symfony but these days it is always .Net Core or Java it seems and I can live with that (thankfully none of the clients I work for use Javascript or on the backend. One used Python with plain SQL and secrets stored in the application code though :-)
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.
ORMs are a doomed proposition. There is simply no way to write a database-agnostic ORM API while also taking full advantage of the underlying tech.
Very simple examples include whether to return rows that have been inserted. In Postgres, you can do that in a single query. In MySQL, it's two queries because INSERT does not return the inserted row. So if I'm using MySQL and a popular "agnostic" ORM I have to realize that I'm (probably) doing an extra query on every insert, even if I don't use the result of the second query.
Then, of course, some of these ORMs don't even implement any of the database-specific features, like MySQL's fulltext search, because they're catering to the least common denominator.
Not every application needs to take full advantage of the underlying DBMS. Sometimes it's just a place to put data and not lose it, e.g. a key-value store with transactions. ORMs handle that use case perfectly well.
(I'm not a fan of ORMs personally, but they seem to work fine for logs of applications.)
Okay, yes... But an ORM is a TON of baggage for such a trivial use case, is it not? And these data have no interesting relationships to each other at all?
Not to mention that transactions work differently across DBMSs, too. So what is your ORM's default? Is it the same default as your DBMS? Does it know that MySQL doesn't support nested transactions, but Postgres does? Do you (metaphorical "you")? Do you fully understand what happens if you try to nest transactions in your ORM?
Also, do you understand how your ORM is going to convert data types and which ones are supported? Remember that JavaScript numbers don't even have the full range of a MySQL/Postgres BIGINT? Probably your programming language doesn't even support DECIMAL types natively- is your ORM going to just cast a DECIMAL to a float for you, or give you an error? Which is worse (I have my opinion)?
What about Dates and Times? How will your ORM handle timezones? How does your DB handle timezones?
ORMs don't seem to help with any of this. In fact, it seems like it only complicates things even more because you have to figure out how your DBMS works AND what your ORM is going to do about it.
Using an ORM seems like a reasonable choice for multi-platform use cases or at least some of them. The alternative would be to implement something more or less from scratch.
Structured types are in SQL since 1999.
People should really look into JOOQ to see what a SQL friendly ORM looks like.
Your "parameterized" queries are still serialized strings. Many language bindings for SQL interfaces don't have good support for serializing data types, so you will often see things like ?::datetime in the template string. Some don't have the ability to parameterize column names (or have subtle bugs) either.
> People should really look into JOOQ to see what a SQL friendly ORM looks like.
I've never heard of JOOQ. Do you know of a good introduction?
How else would you do it, short of a binary query format?
[0] https://en.wikipedia.org/wiki/Query_plan
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
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.
Also, I think this is what's going on with NextJS and React's server-side-components stuff. Having solved client-side webapps, the world has now turned to reinventing Cold Fusion...
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.
If you always want the vendor specific SQL string, use DSLContext.render(QueryPart)
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.
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.
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.
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.
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.
You can't be using any advanced features if you're supporting SQL, NoSQL and even Excel, so if you only need basic features why are you bothering with switching so much?
I know of one good reason to support multiple databases, when you're writing software for several enterprise clients and each of them wants to use whatever databases their teams are used to managing.
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.
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] }
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.
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.
Not just typeahead. A lot of Splunk's power comes from data transformations and filters.
This would be anywhere from pain to impossible with SQL.Seriously, what is so ambiguous with “GROUP BY” that you had to invent your own keyword for it?
That! Totally agree!