3 ms·

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 h

It is a bit falicous to imply SQL is the same everywhere. If you move from postgres to sql server to mysql to oracle you'll see there are major differences in how things are done. Sure, your select and join is mostly the same, maybe with a few different commas here or there. Once you get into complex large data operations though it's a whole new ballgame. Imports happen in wildly different ways, temp tables, choices of inserts vs. updates is a per-platform choice in some cases. On top of all that, an ORM like entity framework (C#) handles things like making sure the statements are prepared in a way that "handles" major security issues like dependency injection out of the box. If you make your team use an ORM you don't have to worry quite as much about someone named ';DROP tables ' signing up to your system. (Or whatever) ORM has it's place. SQL isn't a monolith that everyone* knows. The discussion is worth having, but I think the OP's argument lacks nuance.

CommonGuy 6y ago
You probably meant SQL injection, not dependency injection
geophile 6y ago
I can count the number of times I've seen an app move from one RDBMS to another on zero fingers. This is really not a good motivation for an ORM.
- mumblemumble 6y ago
  I did it once. The first step was ditching the ORM and replacing it with a well-defined repository layer. Because the ORM was encouraging interaction with the database to be scattered everywhere, and that was making it impossible to understand scope of impact. With the data access layer, though, we had a relatively small, clearly defined chunk of code that we could isolate and test, and that made the migration manageable. We didn't need to ditch the ORM, of course. And, at first, we weren't going to. But we saw that, with things so clearly isolated like that, the ORM library wasn't really making the code any easier to understand, so we decided it wasn't really worth the added indirection or the performance cost. I also worked on a product that simultaneously supported two different DBMSes. It didn't use an ORM, either.
- xmodem 6y ago
  I've worked on services that needed to run on the customer's choice of RDBMS, IMO Hibernate helped us out a lot. But it's not something I'd recommend doing if you could avoid it.
- mdpopescu 6y ago
  Huh. I had a client once who complained that I was adding database indirection layers to some application. "When are we ever going to change databases?" I had to remind him that, in the few years I worked for him, we had actually used five different databases - MS SQL, VistaDB, Excel-as-a-database (using ODBC), LiteDB (a NoSql database) and Sqlite. He knew about each and everyone of those, but the "we're never going to change databases" reaction is so ingrained apparently that he just went with it. And just in case you're going to argue "sure, you used different databases but in different applications" - I just had a programmer change Sqlite to LiteDB, because Sqlite wasn't working properly on his system and it was faster to just change the database.
  - ric2b 6y ago
    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.
mumblemumble 6y ago
> Imports happen in wildly different ways, temp tables, choices of inserts vs. updates is a per-platform choice in some cases. All of these bits of SQL, though, are things that ORMs typically handle by simply not using them. If you're limiting yourself to the subset of SQL that you can access with a DSL, it's pretty standard. I agree that ORMs help defend against SQL injection, but so does any linter worth its salt. To me, the real advantage of these DSLs is that they can get you some compile-time guarantees that your queries have some minimum level of validity. Raw SQL needs to be explicitly tested, otherwise you don't even have a guarantee that it's syntactically valid. I'm personally on the fence about whether that outweighs the cost of having a custom DSL, but I'm biased - I tend to avoid ORMs, anyway, so that I can have nice things like common table expressions and temp tables and PIVOT. But if you're using a standard DSL such as .NET's LINQ, which everyone working on the platform already needs to know, anyway, then I can definitely see the attraction. That also eliminates a lot of the core complaint in TFA.
hn_throwaway_99 6y ago
There are much better solutions than ORMs that let you use native SQL but guarantee that you can't have SQL injections. This blog post, https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf410349856c 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] }

CommonGuy 6y ago

You probably meant SQL injection, not dependency injection

geophile 6y ago

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

mumblemumble 6y ago
I did it once. The first step was ditching the ORM and replacing it with a well-defined repository layer. Because the ORM was encouraging interaction with the database to be scattered everywhere, and that was making it impossible to understand scope of impact. With the data access layer, though, we had a relatively small, clearly defined chunk of code that we could isolate and test, and that made the migration manageable. We didn't need to ditch the ORM, of course. And, at first, we weren't going to. But we saw that, with things so clearly isolated like that, the ORM library wasn't really making the code any easier to understand, so we decided it wasn't really worth the added indirection or the performance cost. I also worked on a product that simultaneously supported two different DBMSes. It didn't use an ORM, either.
xmodem 6y ago
I've worked on services that needed to run on the customer's choice of RDBMS, IMO Hibernate helped us out a lot. But it's not something I'd recommend doing if you could avoid it.
mdpopescu 6y ago
Huh. I had a client once who complained that I was adding database indirection layers to some application. "When are we ever going to change databases?" I had to remind him that, in the few years I worked for him, we had actually used five different databases - MS SQL, VistaDB, Excel-as-a-database (using ODBC), LiteDB (a NoSql database) and Sqlite. He knew about each and everyone of those, but the "we're never going to change databases" reaction is so ingrained apparently that he just went with it. And just in case you're going to argue "sure, you used different databases but in different applications" - I just had a programmer change Sqlite to LiteDB, because Sqlite wasn't working properly on his system and it was faster to just change the database.
- ric2b 6y ago
  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.

mumblemumble 6y ago

> Imports happen in wildly different ways, temp tables, choices of inserts vs. updates is a per-platform choice in some cases. All of these bits of SQL, though, are things that ORMs typically handle by simply not using them. If you're limiting yourself to the subset of SQL that you can access with a DSL, it's pretty standard. I agree that ORMs help defend against SQL injection, but so does any linter worth its salt. To me, the real advantage of these DSLs is that they can get you some compile-time guarantees that your queries have some minimum level of validity. Raw SQL needs to be explicitly tested, otherwise you don't even have a guarantee that it's syntactically valid. I'm personally on the fence about whether that outweighs the cost of having a custom DSL, but I'm biased - I tend to avoid ORMs, anyway, so that I can have nice things like common table expressions and temp tables and PIVOT. But if you're using a standard DSL such as .NET's LINQ, which everyone working on the platform already needs to know, anyway, then I can definitely see the attraction. That also eliminates a lot of the core complaint in TFA.

hn_throwaway_99 6y ago

There are much better solutions than ORMs that let you use native SQL but guarantee that you can't have SQL injections. This blog post, https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf410349856c 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] }