> 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.
> 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.
> 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.
> 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.
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.
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?
> 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 :-)
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.
> 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.
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.
> 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 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.
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'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.
> 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.
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.
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).
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)
> 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.
> 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.
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.
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.
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
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 ...
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...
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...
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.