I don't want to learn your query language (2018)(erikbern.com)
erikbern.com
I don't want to learn your query language (2018)
https://erikbern.com/2018/08/30/i-dont-want-to-learn-your-garbage-query-language.html
222 comments
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.
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)
If you always want the vendor specific SQL string, use DSLContext.render(QueryPart)
> 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.
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!
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.
LINQ is still very much alive and well. I think you're thinking of LINQ to SQL which was an ORM in its own right.
Then along came entity framework which also supports LINQ querying syntax (or you can use expressions).
Then along came entity framework which also supports LINQ querying syntax (or you can use expressions).
SQLC could be a good fit. It generates type-safe Go code from SQL.
https://github.com/kyleconroy/sqlc
https://github.com/kyleconroy/sqlc
Wow, that's interesting. Thanks for sharing, I will look into it!
Their is actually a programing language that has this feature.
It's called ABAP[1] and it has build in SQL Support with OpenSQL[2]. OpenSQL is just a SQL dialect which is translated to the equivalent SQL needed for the DB specific underlying DB.
Sadly ABAP is a proprietary programming language only available on the SAP Netweaver stack.
[1] https://en.wikipedia.org/wiki/ABAP
[2] https://help.sap.com/viewer/fe24b0146c551014891ad42d6b2789e5...
[1] https://en.wikipedia.org/wiki/ABAP
[2] https://help.sap.com/viewer/fe24b0146c551014891ad42d6b2789e5...
Thanks, I never heard of this. That looks like a weird mix between COBOL and SQL?!
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.
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.
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.
Do you have some examples of SQLAlchemy core layer you're talking about, just to have an idea of what you have in mind?
Do you have some examples of SQLAlchemy core layer you're talking about, just to have an idea of what you have in mind?
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).
[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...
[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...
Thank you :)
> 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:
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:
sql_t stmt = /SELECT #1, #2 FROM #3, #4 WHERE #3.#1 = #4.#2/;
sql_res_t res_cursor = sql_exec (stmt, t1_col1, t2_col2, T1, T2);
Ur/Web has something like this (example: http://www.impredicative.com/ur/demo/sql.ur.html).
> 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.
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 =
myFun param =
for each param do...
Databases are generally better at storing data, not logic.
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.
I just can't get behind a sentiment that amounts to, "Hey guys, stop trying to innovate."
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.
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.
I literally said to keep trying!
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.
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.
> 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.
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.
Interesting. I've been looking at https://github.com/bokwoon95/go-structured-query but I'll have to look at reform too, now. Thanks.
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.
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.
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.
Do you have a link to Intellij documentation for this feature? That sounds interesting.
Here you go:
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).
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).
Thank you!
> 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.
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.
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.
There's a lot of air between "I need a query string sanitizer and/or a PreparedStatement construct" vs. "I need a full blown ORM."
> these tools protect us from so many footguns present with raw query string manipulation.
cough Parler springs to mind.
cough Parler springs to mind.
I just checked and Hibernate, the granddaddy of Java ORMs, is 19 years old.
> 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 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.
[0] https://en.wikipedia.org/wiki/Query_plan
[0] https://en.wikipedia.org/wiki/Query_plan
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.
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.
(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.
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.
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.
> (1) that it's unstructured, so getting data into or out of SQL involves strings
Structured types are in SQL since 1999.
Structured types are in SQL since 1999.
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.
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. 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?
How else would you do it, short of a binary query format?
Another vote for jOOQ, it's just so good. You can really see it's made by a true SQL fan.
> 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 :-)
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.
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.
> 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,
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.
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.
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.
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.
Very well-written and thoughtful.
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 :-)
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.
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.
Oracle MySQL's official one (Connector/J).
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:
EDIT: Postgres does the same thing: https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main...
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;
}
EDIT: Postgres does the same thing: https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main...
> 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.
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.
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.
You might like the approach I took with pggen[1] which was inspired by sqlc[2]. You write a SQL query in regular SQL and the tool generates a type-safe Go querier struct with a method for each query.
The primary benefit of pggen and sqlc is that you don't need a different query model; it's just SQL and the tools automate the mapping between database rows and Go structs.
[1]: https://github.com/jschaf/pggen
[2]: https://github.com/kyleconroy/sqlc
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
Thanks for sharing!
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.
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...
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...
Oh god we’re back there again are we?
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
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...
That might not be feasible, if the data is never in any form resembling a relational database.
Download your personal data as a sqlite database would be awesome.
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.
how can't they know html and were proficient react? it must be an exageration or perhaps they just knew enough HTML or missed propper CSS knowledge.
Also as far as I know React has native components, not sure how much HTML decoupled are these.
Also as far as I know React has native components, not sure how much HTML decoupled are these.
This would be my guess. There are a lot of things in CSS that need specialist knowledge.
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.
>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.
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.
It seems most likely that they actually aren't proficient in React, which is not really surprising to me.
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.
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.
Old discussion on HN (2018) for the same article here: https://news.ycombinator.com/item?id=17890760
Yup - two in fact (the other one is small but good):
I don't want to learn your query language - https://news.ycombinator.com/item?id=17890760 - Sept 2018 (153 comments)
I don't want to learn your garbage query language - https://news.ycombinator.com/item?id=17888930 - Aug 2018 (9 comments)
I don't want to learn your query language - https://news.ycombinator.com/item?id=17890760 - Sept 2018 (153 comments)
I don't want to learn your garbage query language - https://news.ycombinator.com/item?id=17888930 - Aug 2018 (9 comments)
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.
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.
EDIT: elaborated on ORM APIs role.
> 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.
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.
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.
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.
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.
I never get the ORM hate for CRUD apps.
It gives a centralized place to understand what data is in play in the application and adding a new column to the database means updating a class / struct / type in one location and checking the use of that within the code base.
Without an ORM and with poor discipline I end up having to go find every query in the app referencing that table and check if I need to update the query which usually means changing the columns, the ? value placeholder and putting the correct value in the associated values container.
It all seems like a lot of tedium and ripe for simple typos in the queries that is easily avoided by using beneficial tooling.
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.
The hate for CRUD apps stems from it being too simple to be called a "high-tech" solution, that's all.
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.
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.
Yeah, as long as it's well designed, like LINQ.
ORM is necessary, otherwise we will find ourselves in the old days again, a large part of string concatenations, security problems, etc,.
ORM is necessary, otherwise we will find ourselves in the old days again, a large part of string concatenations, security problems, etc,.
There is so much code out there to help you do typed params and to stop you having to concat strings to make queries. You don't need an orm for this.
There is a tradeoff here, as usual.
The hate for ORMs comes from more advanced applications, not simple CRUD apps. When you have 5 tables with no relations, or maybe a simple one-to-many relation at most, an ORM is the perfect solution.
The problem comes when you have 25 tables, complex relations, complex updates and need to optimize your queries for better performance. You now need to know the inner workings of you ORM, and pray that there are sane ways of configuring these things. Knowing the inner workings of these enormous beasts are not always easy – at least not easier than writing the SQL yourself.
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.
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.
Yes, but as I say in the sibling comment, could this not be the best of both worlds?
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.
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.
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.
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.
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.
Exactly this.
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.
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.
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.
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.
> you should never use positional semantics in your SQL
If I'm understanding you, that along with your mention of a SQL builder means, for example, in Python, you'd expect
CreateUser(name="blah")
As opposed to something like
db.Exec("INSERT INTO users (name) values (?)", "blah")
Is that correct? I think we're agreeing on reducing the amount of raw SQL being tossed around within the code.
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.
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.
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.
> The abstraction that is an ORM is leaky AF
Add to that, as someone mentioned above ORMs tend to be "common least set of features". And if you for some reason want to use a specific feature of your RDBMS you have to jump through hoops and the ORM "illusion" ends.
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.
I have not. I assumed it was analogous to Django / SQLAlchemy where you could always pull the escape handle use raw SQL when it was truly necessary.
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
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.
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.
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'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.
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.
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.
> your app doesn't talk in SQL, it talks in Objects
Speak for yourself. My app talks in dataframes.
Speak for yourself. My app talks in dataframes.
Then maybe ORM isn't for you?
Exactly.
I'm just complaining about the "everything is a webapp backend written in a scripting language" mentality that pervades everything in IT now.
I'm just complaining about the "everything is a webapp backend written in a scripting language" mentality that pervades everything in IT now.
But aren't dataframes just objects where each attribute returns an array of values instead of a scalar?
My app often talks in Objects, but my scripts talk in SQL.
> 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.
"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.
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
SQL is not merely syntax. There is a relational model that is the actual gem of "70s".
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.
Fair enough. Cue Christopher Date, et al.:
https://www.dbdebunk.com/
https://www.dbdebunk.com/
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.
What about this: http://root.rupy.se
You can view source to see the HTTP parameters or just WireShark it.
You can view source to see the HTTP parameters or just WireShark it.
This is not as self-explanatory as you seem to think...
:D I guess, did you understand anything?
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.
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.
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.
> I just want my SQL back.
Which of them? There are as many SQLs as there are implementations. You can't take an SQL schema from one DBMS and drop it into a different one, without conversion. Also you can't take a query written for particular SQL implementation and expect it to work and return the same results, except for very simple queries.
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.
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.
You probably meant SQL injection, not dependency injection
I can count the number of times I've seen an app move from one RDBMS to another on zero fingers. This is really not a good motivation for an ORM.
I did it once.
The first step was ditching the ORM and replacing it with a well-defined repository layer. Because the ORM was encouraging interaction with the database to be scattered everywhere, and that was making it impossible to understand scope of impact.
With the data access layer, though, we had a relatively small, clearly defined chunk of code that we could isolate and test, and that made the migration manageable.
We didn't need to ditch the ORM, of course. And, at first, we weren't going to. But we saw that, with things so clearly isolated like that, the ORM library wasn't really making the code any easier to understand, so we decided it wasn't really worth the added indirection or the performance cost.
I also worked on a product that simultaneously supported two different DBMSes. It didn't use an ORM, either.
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 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.
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.
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] }
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.
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.
[deleted]
“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.
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.
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
That's awesome. Especially when graphing I desperately wish I could map values in ES.
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.
SaaS providers who come up with their own unique query languages are evil. I’m looking at you, New Relic, with your NRQL bullshit.
Seriously, what is so ambiguous with “GROUP BY” that you had to invent your own keyword for it?
Seriously, what is so ambiguous with “GROUP BY” that you had to invent your own keyword for it?
"There should be a 30 year moratorium on inventing new query languages."
That! Totally agree!
That! Totally agree!
This article would have a lot more impact if it was an unveil for a solution
ha yea, give me your solutions not your problems!
And while we're at it, I don't want to learn your programming language. Everyone should just use Java for everything, FFS. /s
Seriously, the idea that everything maps well onto relational databases is incredibly misplaced. Not everything is tables, tuples, and relationships. Most RDBMSs graft a million slightly incompatible features onto SQL to attempt to handle all the things SQL doesn't understand, like GIS and full text search.
Seriously, the idea that everything maps well onto relational databases is incredibly misplaced. Not everything is tables, tuples, and relationships. Most RDBMSs graft a million slightly incompatible features onto SQL to attempt to handle all the things SQL doesn't understand, like GIS and full text search.
Fulltext search is its own special thing, but most the other add-ons tend to be performance improvements or syntactic sugar, not filling in feature gaps.
The thing that's nice about RDMSes, and the reason why they've been so successful, is that they're built on top of a strong theoretical basis. The relational model is a bit like the lambda calculus. It's simple and relatively easy to understand, but can still provably handle just about anything.
The thing that's nice about RDMSes, and the reason why they've been so successful, is that they're built on top of a strong theoretical basis. The relational model is a bit like the lambda calculus. It's simple and relatively easy to understand, but can still provably handle just about anything.
There are actually a number of data models that the relational model doesn't handle well, and it's unclear if it's really even the best model. Back when I was in database research most of our projects focused on domains where the relational model was not a good fit. Some examples of unnatural mappings to the relational model include:
- Documents
- Spatio-temporal
- Graphs
- K/V
- Trees
- CRDTS
- Images
- Arrays
I don't understand the original article as stating that all data maps well onto an RDBMS, but rather than if you are using an RDBMS, you should use SQL rather than adding additional layers.
If you want to use a non-RDBMS, then by all means, SQL won't be a good fit. I use Amazon's DynamoDB all day, every day, and I wouldn't dream of using SQL there.
If you want to use a non-RDBMS, then by all means, SQL won't be a good fit. I use Amazon's DynamoDB all day, every day, and I wouldn't dream of using SQL there.
An ORM is a tool. They have their place: They aren't a replacement for knowing how to use a database; and you need to understand the performance / development time tradeoffs.
I had to fight a battle to remove a very slow ORM that was used for a SQLite database with 4 tables. After I removed the ORM from a critical code path, startup went from 24 hours to about 2 minutes. Raw SQL was "worth it" because the schema was brain-dead simple.
I more recently optimized some C# Entity Framework code that took minutes to run and brought it down to seconds. In this case, there were many tables and many joins, so going down to raw SQL wasn't "worth it." All I had to do was recognize that the code was relying on lazy-loading, and then I just pre-loaded the entities.
BUT: I should point out that ORMs can be extremely powerful. Using a tool like Linqpad with C# and Entity Framework, you can write ad-hoc queries much more easily than SQL.
I had to fight a battle to remove a very slow ORM that was used for a SQLite database with 4 tables. After I removed the ORM from a critical code path, startup went from 24 hours to about 2 minutes. Raw SQL was "worth it" because the schema was brain-dead simple.
I more recently optimized some C# Entity Framework code that took minutes to run and brought it down to seconds. In this case, there were many tables and many joins, so going down to raw SQL wasn't "worth it." All I had to do was recognize that the code was relying on lazy-loading, and then I just pre-loaded the entities.
BUT: I should point out that ORMs can be extremely powerful. Using a tool like Linqpad with C# and Entity Framework, you can write ad-hoc queries much more easily than SQL.
A long time ago, I designed and created an ORM for Java. I brought it to a startup and we made some money selling it. One of the key features was that it went a long way to solving the N+1 problem, which was a unique feature at the time.
Obviously, I bought into the whole ORM concept, but I did have doubts in the back of my mind, some of which showed up in this article. You have to retrieve all of the columns needed to constitute an object. There is no subtlety in controlling the SELECT clause. Getting object identity right is hard, (e.g. you don't want to distinct Customer objects representing the same real-world Customer just because you ran two queries that happened to identify the same Customer). Caching is hard, especially when there are stored procedures doing updates and your ORM has no insight into them. Sometimes it would be nice to just write the damned SQL. Schema and/or object model changes are difficult, because you need to keep the two in sync. When I wrote my own database applications, I didn't want an ORM, not even mine. I wanted to write SQL. Having some tools to manage PreparedStatements and Connections would have been nice, but for my own work, I really preferred writing my own SQL.
But what finally changed my mind completely about ORMs was a hallway conversation with a developer at a prospect on Wall Street. (I think it was one of the firms to go under in the 2008 crash.) She told me that her resume looks a lot better with "5 years Oracle experience", than with "5 years XYZ ORM experience". This, combined with the doubts that were accumulating in my mind, finally pushed me over the edge.
In later years, playing with various web development frameworks, I dreaded dealing with ORMs. It's as the article said: I don't want to learn yet another query language, only to have that translated, badly, into SQL. Just let me write the damned SQL, it isn't that hard. I know exactly what SQL I want to run. Why should I create work for myself in convincing the ORM to do what I already know how to do?
Obviously, I bought into the whole ORM concept, but I did have doubts in the back of my mind, some of which showed up in this article. You have to retrieve all of the columns needed to constitute an object. There is no subtlety in controlling the SELECT clause. Getting object identity right is hard, (e.g. you don't want to distinct Customer objects representing the same real-world Customer just because you ran two queries that happened to identify the same Customer). Caching is hard, especially when there are stored procedures doing updates and your ORM has no insight into them. Sometimes it would be nice to just write the damned SQL. Schema and/or object model changes are difficult, because you need to keep the two in sync. When I wrote my own database applications, I didn't want an ORM, not even mine. I wanted to write SQL. Having some tools to manage PreparedStatements and Connections would have been nice, but for my own work, I really preferred writing my own SQL.
But what finally changed my mind completely about ORMs was a hallway conversation with a developer at a prospect on Wall Street. (I think it was one of the firms to go under in the 2008 crash.) She told me that her resume looks a lot better with "5 years Oracle experience", than with "5 years XYZ ORM experience". This, combined with the doubts that were accumulating in my mind, finally pushed me over the edge.
In later years, playing with various web development frameworks, I dreaded dealing with ORMs. It's as the article said: I don't want to learn yet another query language, only to have that translated, badly, into SQL. Just let me write the damned SQL, it isn't that hard. I know exactly what SQL I want to run. Why should I create work for myself in convincing the ORM to do what I already know how to do?
"One of the key features was that it went a long way to solving the N+1 problem, which was a unique feature at the time."
In case someone else is wondering:
https://stackoverflow.com/questions/97197/what-is-the-n1-sel...
In case someone else is wondering:
https://stackoverflow.com/questions/97197/what-is-the-n1-sel...
Thank you for this. It's a time saver for a lot of people. Yesterday someone was throwing around "HKT" without definition. Wikipedia disambiguated it into 5 different things, none of which applied. I figured out it was Higher Kindred Types in Haskell, but imagine 10,000 people all wasting 5 minutes like I did.
When talking in the context of type-systems, this is a common abbreviation though. I agree that it is annoying, but at some point you have to learn abbreviations. If it was in a different context, then it would have probably been better written out though.
It was in the context of Rust async being broken
https://news.ycombinator.com/item?id=26406989
https://news.ycombinator.com/item?id=26406989
Thank you, I should have included a definition or link myself.
It however should be noted that JPA can run native (SQL) queries and even lets you map the result onto entities if those are a fit for the result set. I've used that with success even for partial results (not all columns selected).
It's just not typed but that is true of JPQL as well. And the criteria API is, well, ...special.
It's just not typed but that is true of JPQL as well. And the criteria API is, well, ...special.
Honestly the main reason I still use an ORM right now (Gorm, it's not the best), is to solve a number of annoyances I would otherwise have (and I may yet switch to straight SQL): mapping result sets to structs, updating relationships (treating nested objects as a single document), and database migrations.
I really don't mind SQL, I mind all the perceived boilerplate around it.
I really don't mind SQL, I mind all the perceived boilerplate around it.
There is sqlx for that: https://github.com/jmoiron/sqlx
Yes, exactly. When I write SQL applications now, I usually start by implementing a little database interface to take care of the boilerplate. The application can then provide SQL as needed.
[deleted]
I use everyone's favorite punching bag - Hibernate - a lot. I write all my queries in... SQL. It works pretty well this way.
The "ORM/SQL dichotomy" is greatly exaggerated.
The "ORM/SQL dichotomy" is greatly exaggerated.
You seem to be in agreement with the article and my top-level comment, since you aren't using the Object/Relational Mapping part of your favorite ORM. It is not surprising that the ORM/SQL dichotomy is exaggerated if you ignore the ORM.
I make queries in SQL and those queries are mapped to objects. Sounds like ORM to me.
Furthermore, those objects are "live" and editing them updates the database. Navigating the object graph navigates the database. All this is very convenient. When it's not convenient or performant, I write updates or tuple queries by hand. But mostly the approach of "select entities using complex SQL queries but use the ORM approach afterward" is a sweet spot.
Furthermore, those objects are "live" and editing them updates the database. Navigating the object graph navigates the database. All this is very convenient. When it's not convenient or performant, I write updates or tuple queries by hand. But mostly the approach of "select entities using complex SQL queries but use the ORM approach afterward" is a sweet spot.
I believe it depends on what problem you are trying to solve. Surprisingly nobody has mentioned this in the thread, but domain-driven design (DDD) encourages you to split your problem domain into different bounded contexts (BC) and use the right architecture for each BC.
Classic example is an event booking system:
(a) Event Management is basic CRUD, so you use an ORM everywhere. There are no complicated queries here so ORM objections don't count.
(b) Ticket Management has multiple users, race conditions, performance requirements, etc. so maybe go the command-query separation (CQRS) route and use a full ORM for your write model and raw SQL, possibly a micro-ORM, for your eventually-consistent read model.
Why use a full ORM? You get to write your domain logic in your high level language with full unit testing, and the ORM worries about mapping this back to the database, dealing with concurrency and retry strategies automatically. For this to work you have to carefully design your business transactions against small aggregates containing a handful of entities, which is difficult to get right initially but has many advantages.
(c) Payments/Refunds are transactional, reliant on external services, and need a full audit history so maybe consider an event sourced approach with no ORM visible.
(d) Reports have no write model, so an ORM holds little advantage. Use SSRS, Power Bi, QlikView, etc.
etc.
Classic example is an event booking system:
(a) Event Management is basic CRUD, so you use an ORM everywhere. There are no complicated queries here so ORM objections don't count.
(b) Ticket Management has multiple users, race conditions, performance requirements, etc. so maybe go the command-query separation (CQRS) route and use a full ORM for your write model and raw SQL, possibly a micro-ORM, for your eventually-consistent read model.
Why use a full ORM? You get to write your domain logic in your high level language with full unit testing, and the ORM worries about mapping this back to the database, dealing with concurrency and retry strategies automatically. For this to work you have to carefully design your business transactions against small aggregates containing a handful of entities, which is difficult to get right initially but has many advantages.
(c) Payments/Refunds are transactional, reliant on external services, and need a full audit history so maybe consider an event sourced approach with no ORM visible.
(d) Reports have no write model, so an ORM holds little advantage. Use SSRS, Power Bi, QlikView, etc.
etc.
There is also the alternative of not using classical OOP but a functional style, where your types map much easier to what is in your database. Then you still use a tool to write in your language so that it is typesafe, but otherwise there is no magic mapping.
Abstraction is useful though. Don't tie yourself to specific database system.
This is, of course, good advice. But I'd add that query builder / micro ORM libraries are a better solution as they solve the (mostly common) interface, connection pooling, and multiple db driver parts.
This is plenty.
One's I've used:
knex - node.js
sqlx - rust
This is plenty.
One's I've used:
knex - node.js
sqlx - rust
So this article is muddy.
The author has a beef with two things and claims to have a problem with a third. The author seems to dislike ORMs for their abstractness and general inefficiency when interacting with a SQL database (this is the nature of an ORM, either stop writing OOP code and as a result stop requiring an ORM, or use a graph database for your graph data (object state)). The author also seems to dislike SaaS query languages or something like that, a topic I don't know much about.
This gets all rounded up under the topic of "Query DSLs".
When I hear "Query DSL" I think SQLAlchemy Core. A library designed to provide an API to a SQL database which looks like you're writing python (implemented through overloading tricks and some fancy OOP metaprogramming tricks). SQLAlchemy Core is great, the author would not hate it because you're effectively writing SQL without all the footguns.
SELECT * FROM user; -> select(m.user)
SELECT * FROM user WHERE name == 'test' ORDER BY surname; -> select(m.user).where(m.user.c.name == 'test').order_by(m.user.c.surname)
By having python native code you can generate SQL statements using idiomatic python. If you do some kind of coverage tests (no idea what the kids these days call these but the idea is to run all the python code to ensure there isn't a low hanging fruit which would get caught at runtime) you can find 'select' typoed as 'slect' early on in development rather than it ruining your Friday when such a bug gets into production. This also makes parametrized queries painless which prevents many potential security issues with generating SQL.
If you want to you can also use the generic subset of SQL this way and SQLAlchemy Core will iron out the creases. But personally I think it's pointless to make a choice of SQL database if you're then going to not use all its special features. It's like writing some kind of polyglot-lang which can be translated to C, Java, Pascal, prolog, Lua, scheme and rust. You pick a language for the task, pick a database for the task too.
So it's important to isolate these features mentioned above from "ORM" and whatever the other thing the author was talking about. People use ORMs and they realise they suck and it's important to remember that although the core concept of an ORM is flawed, not all the features which come bundled with an ORM are bad ideas (or at least it doesn't mean that those ideas can't be done well when you are not trying to implement an ORM).
The author has a beef with two things and claims to have a problem with a third. The author seems to dislike ORMs for their abstractness and general inefficiency when interacting with a SQL database (this is the nature of an ORM, either stop writing OOP code and as a result stop requiring an ORM, or use a graph database for your graph data (object state)). The author also seems to dislike SaaS query languages or something like that, a topic I don't know much about.
This gets all rounded up under the topic of "Query DSLs".
When I hear "Query DSL" I think SQLAlchemy Core. A library designed to provide an API to a SQL database which looks like you're writing python (implemented through overloading tricks and some fancy OOP metaprogramming tricks). SQLAlchemy Core is great, the author would not hate it because you're effectively writing SQL without all the footguns.
SELECT * FROM user; -> select(m.user)
SELECT * FROM user WHERE name == 'test' ORDER BY surname; -> select(m.user).where(m.user.c.name == 'test').order_by(m.user.c.surname)
By having python native code you can generate SQL statements using idiomatic python. If you do some kind of coverage tests (no idea what the kids these days call these but the idea is to run all the python code to ensure there isn't a low hanging fruit which would get caught at runtime) you can find 'select' typoed as 'slect' early on in development rather than it ruining your Friday when such a bug gets into production. This also makes parametrized queries painless which prevents many potential security issues with generating SQL.
If you want to you can also use the generic subset of SQL this way and SQLAlchemy Core will iron out the creases. But personally I think it's pointless to make a choice of SQL database if you're then going to not use all its special features. It's like writing some kind of polyglot-lang which can be translated to C, Java, Pascal, prolog, Lua, scheme and rust. You pick a language for the task, pick a database for the task too.
So it's important to isolate these features mentioned above from "ORM" and whatever the other thing the author was talking about. People use ORMs and they realise they suck and it's important to remember that although the core concept of an ORM is flawed, not all the features which come bundled with an ORM are bad ideas (or at least it doesn't mean that those ideas can't be done well when you are not trying to implement an ORM).
For what it’s worth YQL was pretty neat back when Yahoo still had some decent engineering going on. Single SQL-like query language for accessing all of their services. Way way underutilized for how amazingly powerful it was.
I actually still had a little bit of code in production using it when they shut down.
I actually still had a little bit of code in production using it when they shut down.
I don't care if other people use ORMs or not, if they don't force me, but yes please on this: 'Every SaaS product should offer a plug-and-play thing so that I can copy all the data back into my own SQL-based database.' Some nice way to incrementally copy/sync my data from some saas into my database would be great. Even a full data dump from some JSON endpoint is good enough if it has stable unique keys on all entities, thanks to UPSERT being pretty good for this kind of data syncing.
I agree with this so much omg.
More apt: Can we agree on a simple, modern query language that is better than SQL, which is fine but a little antiquated? Because the issue is standardisation more than anything.
I sympathize with the author -- I also choose plain old SQL whenever possible. But SQL is not a good option. It is the least bad option. I even worked on my own alternate query language, Plisqin: https://docs.racket-lang.org/plisqin/index.html I was never expecting it to gain adoption. But if any SQL alternative ever does gain adoption (in the same way Typescript has for JS), I really hope it looks at least a little bit like Plisqin. Specifically, when joins are values and not language constructs, things get a lot better. Also, please satisfy these 3 rules: https://docs.racket-lang.org/plisqin/research-language.html#...
I enjoy using the SQLAlchemy ORM. It makes it trivial to divide a query in several parts that you can mix and match.
In my experience, features such as a search engine that supports many filters are much easier to maintain if they are written with an ORM rather than with raw SQL. I don't have a preference for simple queries.
On the other hand, I find MongoDB's query language and its popular Python ORMs (Pymongo and Mongoengine) painful to work with.
In my experience, features such as a search engine that supports many filters are much easier to maintain if they are written with an ORM rather than with raw SQL. I don't have a preference for simple queries.
On the other hand, I find MongoDB's query language and its popular Python ORMs (Pymongo and Mongoengine) painful to work with.
I found that Rails' way of doing data access -- which completely hides the SQL from you -- to be really confusing, but people seem to absolutely love it once they get it under their feet. I prefer to write SQL/sprocs. What's the consensus these days?
This is an essay version of xkcd 'standards'. It's not wrong, but it's not the entire picture.
The author is correct, sql does everything they need, cause it's so expressive. (Mysql8 is turing complete with it's domain specific syntax duh-duh-daah...) But that power comes at the cost of scrutiny and composition.
ORMS are fish in a barrel--they fail because they are the wrong abstraction. Queries shouldn't be the language, they should be the verbs in the language.
With a very restricted query AST, introspection and composition are possible. If queries point to the same table, you can combine them into a new query whose result cells are union or intersection of the input result cells. You can automate joins and subquery indexing. You can recover from a dropped column by replacing references to it with null, and limp by with partial results instead of no results.
I agree with the author's hate on DSLs as a substitute for sql... when they have the same level of abstraction. But if you want queries to be lego bricks that snap together, the bricks can't have NP hard surface area. DSLs fill that need.
The author is correct, sql does everything they need, cause it's so expressive. (Mysql8 is turing complete with it's domain specific syntax duh-duh-daah...) But that power comes at the cost of scrutiny and composition.
ORMS are fish in a barrel--they fail because they are the wrong abstraction. Queries shouldn't be the language, they should be the verbs in the language.
With a very restricted query AST, introspection and composition are possible. If queries point to the same table, you can combine them into a new query whose result cells are union or intersection of the input result cells. You can automate joins and subquery indexing. You can recover from a dropped column by replacing references to it with null, and limp by with partial results instead of no results.
I agree with the author's hate on DSLs as a substitute for sql... when they have the same level of abstraction. But if you want queries to be lego bricks that snap together, the bricks can't have NP hard surface area. DSLs fill that need.
I recently installed ClickHouse (part of my self-hosted Plausible setup) and had to modify a record inside the database. At this point my only knowledge of ClickHouse was that it comes with an SQL interface. So I opened up a client and typed "SHOW DATABASES" and guess what - it showed me a list of all databases. Then I typed "USE mydatabase" and I was connected to my database. I typed "SHOW TABLES" and got a list of tables, followed by "DESCRIBE TABLE users" and "UPDATE users SET email_verified = true" (FYI I was trying to avoid having to set up SMTP credentials for Plausible). I was able to use ClickHouse without any prior knowledge because the authors decided to based it on a well-known and fairly simple standard instead of inventing their own.
It felt as good as building Ikea furniture without checking the manual and it's what user/developer experience should be about.
It felt as good as building Ikea furniture without checking the manual and it's what user/developer experience should be about.
You've used MySQL, I would guess. Personally I like the superficial similarities to basic MySQL syntax in ClickHouse. MySQL and Sybase T-SQL have always struck me as the friendliest SQL dialects.
I like SQL. The problem with SQL is that it is a string and simple errors, like an extra comma, are not caught by the compiler/interpreter. Are there good well-thought out libraries that are isomorphic to SQL but can be used with a language like Python or Clojure?
For example, I want to write simple SQL queries this way:
For example, I want to write simple SQL queries this way:
(-> "table" (cols col1 col2 col3) (where (= col1 10)) (order-by col3 :desc))I wrote such a C++ implementation during my days at Facebook around 8 years ago:
https://engineering.fb.com/2016/03/18/data-infrastructure/dr...
There is an example under "Functional programming primitives".
It was a C++ implementation that fell victim to Greenspun's 10th rule. So I wrote a specification for it, first in Clojure and then in python.
The C++ execution engine is open source:
https://github.com/facebookarchive/iterlib/
Main problems writing such code:
* The output of SQL is generally flat. GraphQL makes it nested, but doesn't support all the operators SQL does natively. * Anyone trying to implement such an engine needs to ensure a fundamental property - the shape of the output can be inferred from the shape of the query. * Writing async code in C++ with futures and promises is like pulling teeth. At some point the complexity explodes and the code becomes unmaintainable.
My most recent attempt is here:
https://adsharma.github.io/fquery/
It shouldn't be hard to put a wrapper around fquery that looks like the query string you present above. In fact, there is a s-expression parser in iterlib/python above that could be tweaked to do this.
I'm hoping that we can build a community around such languages and once the specification is agreed on, a more performant implementation can be written in a systems programming language.
https://engineering.fb.com/2016/03/18/data-infrastructure/dr...
There is an example under "Functional programming primitives".
It was a C++ implementation that fell victim to Greenspun's 10th rule. So I wrote a specification for it, first in Clojure and then in python.
The C++ execution engine is open source:
https://github.com/facebookarchive/iterlib/
Main problems writing such code:
* The output of SQL is generally flat. GraphQL makes it nested, but doesn't support all the operators SQL does natively. * Anyone trying to implement such an engine needs to ensure a fundamental property - the shape of the output can be inferred from the shape of the query. * Writing async code in C++ with futures and promises is like pulling teeth. At some point the complexity explodes and the code becomes unmaintainable.
My most recent attempt is here:
https://adsharma.github.io/fquery/
It shouldn't be hard to put a wrapper around fquery that looks like the query string you present above. In fact, there is a s-expression parser in iterlib/python above that could be tweaked to do this.
I'm hoping that we can build a community around such languages and once the specification is agreed on, a more performant implementation can be written in a systems programming language.
If you love C++ Template magic look at https://github.com/rbock/sqlpp11
This allows "normal" C++ code, which by the compiler is converted into the query string, allowing code like
This allows "normal" C++ code, which by the compiler is converted into the query string, allowing code like
for (const auto& row : db(select(all_of(foo)).from(foo).where(foo.hasFun or foo.name == "joker")))
{
int64_t id = row.id; // numeric fields are implicitly convertible to numeric c++ types
}
I believe Honey SQL might be just what you are looking for:
https://github.com/seancorfield/honeysql
https://github.com/seancorfield/honeysql
The problem with SQL is that it is a string and simple errors, like an extra comma, are not caught by the compiler/interpreter.
Why not test it at the Clojure REPL?
Why not test it at the Clojure REPL?
Spark lets users query via regular SQL or DSL (Scala, PySpark).
The DSLs are generally better to work with. Complex queries can be broken up into composable functions that can be mixed and matched to perform different analyses. Functions are easier to unit test, document, deal with quoting better, can be type safe, etc.
Some super-complex queries are still better to write with regular SQL.
New query DSLs don't need to reinvent the wheel. They can implement their query capabilities using the Row / Column / DataFrame abstractions that are elegant and familiar in popular projects like Spark. We need an ANSI query engine DSL.
The DSLs are generally better to work with. Complex queries can be broken up into composable functions that can be mixed and matched to perform different analyses. Functions are easier to unit test, document, deal with quoting better, can be type safe, etc.
Some super-complex queries are still better to write with regular SQL.
New query DSLs don't need to reinvent the wheel. They can implement their query capabilities using the Row / Column / DataFrame abstractions that are elegant and familiar in popular projects like Spark. We need an ANSI query engine DSL.
heck, what if we just make the database an immutable graph, and use functional programming on it in our language of choice? oh wait, that's Datomic. Except, Java is quite lame. Let's rewrite Datomic in Rust please!
A response to this article, I Don't Want to Teach my Garbage DSL, either, was discussed here on HN almost two years ago: https://news.ycombinator.com/item?id=20059029
In that discussion, I made this comment¹, which I still stand by:
If you’re developing a DSL which is just a query language, you are reinventing the wheel, and you should ask yourself if any benefit of your language over SQL is worth the effort of all your users to learn your new query language. It may be worth it of your data can not be usefully be modeled by tables; e.g. document query languages like XQuery and even simple XPath are useful and can not be easily replaced with SQL.
1) https://news.ycombinator.com/item?id=20059337
In that discussion, I made this comment¹, which I still stand by:
If you’re developing a DSL which is just a query language, you are reinventing the wheel, and you should ask yourself if any benefit of your language over SQL is worth the effort of all your users to learn your new query language. It may be worth it of your data can not be usefully be modeled by tables; e.g. document query languages like XQuery and even simple XPath are useful and can not be easily replaced with SQL.
1) https://news.ycombinator.com/item?id=20059337
DSLs are modern day version of language explosions in a niche ecosystem (CS) in a short period.
Interested in how DLS like expression evolving on other industries like Music, Science, etc
Interested in how DLS like expression evolving on other industries like Music, Science, etc
One thing that ORMs shine in is query composition. For example, starting from a base definition for retrieving a list of entities, you can _dynamically_ compose orthogonal operators that modify the base query, e.g. projection, filtering, sorting, grouping, joins, etc. without having to manually write SQL for every combination of those operators; the ORM takes care of generating the SQL code for you.
That being said, ORMs still don't enjoy the level of trust that optimizing compilers have enjoyed for decades. That's always going to be a barrier for wide adoption, especially from folks who are experienced in SQL. It's similar to how in the 1950s (and maybe somewhat 1960s) there was resistance for "automatic coding" by compilers; those who were experienced in assembly thought that compilers will always produce sub-optimal code. But it's clear that high-level languages have won (at least outside embedded/low-level drivers).
The point is: any form of code generation will always find resistance until it proves itself.
That being said, ORMs still don't enjoy the level of trust that optimizing compilers have enjoyed for decades. That's always going to be a barrier for wide adoption, especially from folks who are experienced in SQL. It's similar to how in the 1950s (and maybe somewhat 1960s) there was resistance for "automatic coding" by compilers; those who were experienced in assembly thought that compilers will always produce sub-optimal code. But it's clear that high-level languages have won (at least outside embedded/low-level drivers).
The point is: any form of code generation will always find resistance until it proves itself.
I agree they shine at dynamic query composition, but I find the sort of UI that necessitates this dynamism are universally terrible. They end up being data dumps that allow the user to filter and sort themselves instead of having a UI that actually understands their workflow and the data the need to accomplish it. It's like sending the user an excel file and saying "here, you can manipulate it how you want".
This is also something light weight orms or query generators handle fairly well, if not better.
This is also something light weight orms or query generators handle fairly well, if not better.
There are three fairly independent points being made here:
1) ORMs (which are used in cases where you have control over the DB) are bad and you should just use SQL
2) DSLs (in non-SQL DBs) are bad and the devs should have just exposed SQL
3) DSLs (in opaque services) are bad and the devs should have just exposed SQL
I tend to agree with #1, though it's complicated and there's lots to be said on both sides.
#2 seems semi-reasonable, if the semantics are close enough to those of a SQL DB, but the problem is that DBs like Mongo have wildly different semantics from SQL.
#3 is harder because an opaque service usually specifically wants to insulate users from its implementation details. The great majority will not want to give you direct access to a SQL database. So the only alternative would be to "fake it" and pretty much base their DSL on SQL, which may or may not line up with the semantics of their service.
For both #2 and #3, exposing SQL when the underlying semantics may or may not be a good match for the query language seems questionable at best. It encourages assumptions to be carried over that may not hold, especially around performance characteristics (which the OP specifically mentions as an advantage, surprisingly). I just don't see how this is tractable in the general case.
1) ORMs (which are used in cases where you have control over the DB) are bad and you should just use SQL
2) DSLs (in non-SQL DBs) are bad and the devs should have just exposed SQL
3) DSLs (in opaque services) are bad and the devs should have just exposed SQL
I tend to agree with #1, though it's complicated and there's lots to be said on both sides.
#2 seems semi-reasonable, if the semantics are close enough to those of a SQL DB, but the problem is that DBs like Mongo have wildly different semantics from SQL.
#3 is harder because an opaque service usually specifically wants to insulate users from its implementation details. The great majority will not want to give you direct access to a SQL database. So the only alternative would be to "fake it" and pretty much base their DSL on SQL, which may or may not line up with the semantics of their service.
For both #2 and #3, exposing SQL when the underlying semantics may or may not be a good match for the query language seems questionable at best. It encourages assumptions to be carried over that may not hold, especially around performance characteristics (which the OP specifically mentions as an advantage, surprisingly). I just don't see how this is tractable in the general case.
Nobody seems to have posted Stuart Halloway's Narcissistic Design yet, so I'll just leave this here.
https://www.youtube.com/watch?v=LEZv-kQUSi4
Has some great insights and quips. "Nothing says 'screw you' like a DSL"
https://www.youtube.com/watch?v=LEZv-kQUSi4
Has some great insights and quips. "Nothing says 'screw you' like a DSL"
What about Elasticsearch that invents and deprecates features for it's query language on every major release. Then you have several ways of doing the same query with all kind of filters, all of them which can't tell you if a certain field is NULL.
The ElasticSearch language is so wordy and obtuse and difficult to remember, that I just translate SQL to it and go from there.
https://sqltoelasticsearch.azurewebsites.net/
https://sqltoelasticsearch.azurewebsites.net/
That is a good idea. Elasticsearch is going on that direction, they have admitted what was obvious: https://www.elastic.co/what-is/elasticsearch-sql
This can be abstracted to a general critique with frameworks overall.
On smaller scale projects, cut out as many frameworks as possible. Stick with the bare minimum of tools. Pure Ruby, versus Rails. Pure Javascript versus jQuery. SQL queries versus ORM. Etc. It's less to maintain, less to learn, AND it forces you to learn the underlying technology.
On larger-scale and longer-term projects where you expect to work with others and/or will need to hop back into the code on a regular basis for years, cautiously introduce frameworks (and ORMs). In those cases, frameworks solve more problems in the long run. I'm usually annoyed early on, as frameworks force you to learn their DSLs (etc). But there's always a point where the benefits start to outweigh the initial negatives.
On smaller scale projects, cut out as many frameworks as possible. Stick with the bare minimum of tools. Pure Ruby, versus Rails. Pure Javascript versus jQuery. SQL queries versus ORM. Etc. It's less to maintain, less to learn, AND it forces you to learn the underlying technology.
On larger-scale and longer-term projects where you expect to work with others and/or will need to hop back into the code on a regular basis for years, cautiously introduce frameworks (and ORMs). In those cases, frameworks solve more problems in the long run. I'm usually annoyed early on, as frameworks force you to learn their DSLs (etc). But there's always a point where the benefits start to outweigh the initial negatives.
I totally agree with the views of the author. That is basically why I started to build an ORM that does not include a query builder. Best of both worlds.
Now the design was far from easy, and keeping things reasonably simple without abstracting the SQL part was my biggest challenge. And this would not have been possible without the template-string tagging feature of modern JS, so it wouldn't be doable for many other languages.
https://github.com/Seb-C/kiss-orm
Now the design was far from easy, and keeping things reasonably simple without abstracting the SQL part was my biggest challenge. And this would not have been possible without the template-string tagging feature of modern JS, so it wouldn't be doable for many other languages.
https://github.com/Seb-C/kiss-orm
I don't see this anywhere in this thread: http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...
That is essential reading for anyone arguing against or for ORMs.
That is essential reading for anyone arguing against or for ORMs.
there are so many intersecting problems here, that I feel like we argue about different ones at the same time.
Are you a human and want to query a database? SQL is probably it. (a SQL of some sort, but a pretty good shared understanding) Even if it's not relational exactly, it probably comes with some project with things like records/rows and columns/fields.
Do you have some kind of relational model that needs accessing? SQL sounds pretty good, and you know it from writing queries as a human...
But should you construct queries with string concatenation? No! Absolutely not.
Do you have to write the queries yourself? Like what if you want to stick an object in a database? Can't it write the query for you? It's appealing, but leads to sadness and anger at some point. (Joins? Conditions of more than 2 degrees? lots of dark corners very close by)
AND whatever model objects are used become a defacto database schema, so you can't really refactor them in the same way you would other models. So you better not expose them past whatever datastore touching interface you have. So why not just define them in SQL DDL in the first place?
What if you want to write the query (or need to to be sure you're getting exactly what you expect) but type-safely? LINQ? Absolutely! jOOQ? Sounds good! Arel? Sure! And maybe you get some amount of programmatic composability as well with any of these. Thankfully they're not really ORMs or extremely thin ones at that.
Are you a human and want to query a database? SQL is probably it. (a SQL of some sort, but a pretty good shared understanding) Even if it's not relational exactly, it probably comes with some project with things like records/rows and columns/fields.
Do you have some kind of relational model that needs accessing? SQL sounds pretty good, and you know it from writing queries as a human...
But should you construct queries with string concatenation? No! Absolutely not.
Do you have to write the queries yourself? Like what if you want to stick an object in a database? Can't it write the query for you? It's appealing, but leads to sadness and anger at some point. (Joins? Conditions of more than 2 degrees? lots of dark corners very close by)
AND whatever model objects are used become a defacto database schema, so you can't really refactor them in the same way you would other models. So you better not expose them past whatever datastore touching interface you have. So why not just define them in SQL DDL in the first place?
What if you want to write the query (or need to to be sure you're getting exactly what you expect) but type-safely? LINQ? Absolutely! jOOQ? Sounds good! Arel? Sure! And maybe you get some amount of programmatic composability as well with any of these. Thankfully they're not really ORMs or extremely thin ones at that.
I 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.