4 ms·

> Even the biggest hater of SQL database abstractions will admit there are reasons why someone would want them. There is only one reason, and it's that people

by jseban 4y ago

> Even the biggest hater of SQL database abstractions will admit there are reasons why someone would want them. There is only one reason, and it's that people don't understand how to use SQL, and that's because it's not taught in computer science degrees, and this also has strong ties to the battle between sun and oracle. And this makes database developers expensive and hard to hire. There is no reason to use an ORM if you know SQL and database modelling. The relational model is by far the most powerful tool to solve CRUD use cases, which "web development" is, and that's just simply a fact. What the ORM will do for you is automate some boilerplate mapper code, which might be a bit boring to write, but is very simple. And the downside is that it will make all of your database modelling, querying, access etc orders of magnitude more obscure and complex compared to just using SQL. And now you will waste enormous amounts of time fighting with the framework to debug the ORM and find out which kind of weird SQL statements it generates, where you would have complete control over this if you just wrote your own. The obvious solution that would benefit everyone, is to simply train your developers to use SQL, if they don't already know it, and let them take a course if they need to. Not to slap on some stupid framework and make a huge unmanageable mess of the whole system.

jchw 4y ago
> There is only one reason, and it's that people don't understand how to use SQL Unfortunately, I immediately think you have misunderstood. While ORMs and other database abstractions may be a boon to folks who don’t know SQL, or at least some may think that, it is not the only reason that people use them. It may not even be one of the bigger reasons. Type safety is first and foremost a great example. Wouldn’t it be great if you could literally not ship SQL code that has typos, syntax errors, or other query errors? Unit tests catch those, but you have to actually write them, and they still can’t help if you hold things wrong. (Like using sprintf for SQL queries, god help us. Yes, that is a mistake no professional should make. But if you add enough layers of complexity, you might just fool someone who knows better into doing basically that with extra steps. There are some ways to combat this of course, like PEP 675[1].) In some environments you might accomplish this with compile-time linting or advanced metaprogramming. However, ORMs also do this right out of the box generally, at least in languages where it can be done. But even then, that doesn’t scratch the surface of what’s possible. Mind you, with sqlc, we’re still talking about making raw SQL queries. But those SQL queries contain tons of information that gets duplicated by the programmer. For example, it is possible to statically know what types of inputs and output columns any given query will have based on just the schema and the query itself. sqlc gives you this information. It generates correct wrappers for SQL code using only raw SQL queries as input. This is not the only benefit of abstractions of course. And sqlc doesn’t give you all of the benefits of an ORM. However, that’s exactly what I’m trying to say: a lot of people have an idea in their head about what an abstraction must entail, but there’s probably better tradeoffs that are genuinely not susceptible to the same issues that you’re imagining. Case in point, I have a feeling you interpreted my post to only be regarding ORMs, but even the text you quoted is more general—I said “database abstractions” for a reason :) [1]: https://peps.python.org/pep-0675/ https://peps.python.org/pep-0675/
shroompasta 4y ago
I disagree. How would you be able to validate data that you can map to your tables without hitting the db? If you wanted to add a new row of data to one of your tables, you would have to hit the db, get row columns and make sure that data fits. Or you could write out your table structure separately outside of the db, and validate against that, but that would just leave you with two sources of truth which is an antipattern. Or you could just use an ORM. I'm fluent in SQL, and understand the solutions that ORMs solve - it's not just a pure abstraction over sql queries, but the tooling and utility provided wrapping around that is what makes it so powerful.
scarface74 4y ago
I’ve known SQL for close to 25 years. Composing queries programmatically is much easier and less error prone than raw sql when using an ORM. In the case of C# Entity Framework, the ORM abstraction- LINQ - is a first class language feature with full compile time type checking and auto complete. The same LINQ query can work across databases and in memory collections including RDMS’s and Mongo. Eventually in any object oriented language, you’re going to end up doing “something” to convert relational data to objects.
- jseban 4y ago
  > Composing queries programmatically is much easier and less error prone than raw sql when using an ORM. This is only true for the most trivial of use cases that will not really bring any value to your users. As soon as you get more advanced feature requests, you need the power of the relational model, which is now abstracted away behind a much less powerful interface. Edit: more advanced, or even just a higher number of orthogonal use cases against the same data.
  - scarface74 4y ago
    Have you used LINQ? LINQ is basically just a slight variant of SQL. All data is related, the only difference between a document type model like Mongo or ElasticSearch is how it stored and retrieved.
    - jseban 4y ago
      I've only been introduced to it, but I did get the impression that LINQ is probably the best SQL abstraction, and that it's much better than Hibernate, which is what I have the most experience with. I think the whole framework situation in general is much better in microsoft land because it's one company that designs the whole ecosystem, and overall experience, and also the people with probably the most experience in software development. I even think VB6 is superior to the standard open source web development stack used today. The only part where MS seems to be behind is version control.

by jseban 4y ago

jchw 4y ago

> There is only one reason, and it's that people don't understand how to use SQL Unfortunately, I immediately think you have misunderstood. While ORMs and other database abstractions may be a boon to folks who don’t know SQL, or at least some may think that, it is not the only reason that people use them. It may not even be one of the bigger reasons. Type safety is first and foremost a great example. Wouldn’t it be great if you could literally not ship SQL code that has typos, syntax errors, or other query errors? Unit tests catch those, but you have to actually write them, and they still can’t help if you hold things wrong. (Like using sprintf for SQL queries, god help us. Yes, that is a mistake no professional should make. But if you add enough layers of complexity, you might just fool someone who knows better into doing basically that with extra steps. There are some ways to combat this of course, like PEP 675[1].) In some environments you might accomplish this with compile-time linting or advanced metaprogramming. However, ORMs also do this right out of the box generally, at least in languages where it can be done. But even then, that doesn’t scratch the surface of what’s possible. Mind you, with sqlc, we’re still talking about making raw SQL queries. But those SQL queries contain tons of information that gets duplicated by the programmer. For example, it is possible to statically know what types of inputs and output columns any given query will have based on just the schema and the query itself. sqlc gives you this information. It generates correct wrappers for SQL code using only raw SQL queries as input. This is not the only benefit of abstractions of course. And sqlc doesn’t give you all of the benefits of an ORM. However, that’s exactly what I’m trying to say: a lot of people have an idea in their head about what an abstraction must entail, but there’s probably better tradeoffs that are genuinely not susceptible to the same issues that you’re imagining. Case in point, I have a feeling you interpreted my post to only be regarding ORMs, but even the text you quoted is more general—I said “database abstractions” for a reason :) [1]: https://peps.python.org/pep-0675/ https://peps.python.org/pep-0675/

shroompasta 4y ago

I disagree. How would you be able to validate data that you can map to your tables without hitting the db? If you wanted to add a new row of data to one of your tables, you would have to hit the db, get row columns and make sure that data fits. Or you could write out your table structure separately outside of the db, and validate against that, but that would just leave you with two sources of truth which is an antipattern. Or you could just use an ORM. I'm fluent in SQL, and understand the solutions that ORMs solve - it's not just a pure abstraction over sql queries, but the tooling and utility provided wrapping around that is what makes it so powerful.

scarface74 4y ago

I’ve known SQL for close to 25 years. Composing queries programmatically is much easier and less error prone than raw sql when using an ORM. In the case of C# Entity Framework, the ORM abstraction- LINQ - is a first class language feature with full compile time type checking and auto complete. The same LINQ query can work across databases and in memory collections including RDMS’s and Mongo. Eventually in any object oriented language, you’re going to end up doing “something” to convert relational data to objects.

jseban 4y ago
> Composing queries programmatically is much easier and less error prone than raw sql when using an ORM. This is only true for the most trivial of use cases that will not really bring any value to your users. As soon as you get more advanced feature requests, you need the power of the relational model, which is now abstracted away behind a much less powerful interface. Edit: more advanced, or even just a higher number of orthogonal use cases against the same data.
- scarface74 4y ago
  Have you used LINQ? LINQ is basically just a slight variant of SQL. All data is related, the only difference between a document type model like Mongo or ElasticSearch is how it stored and retrieved.
  - jseban 4y ago
    I've only been introduced to it, but I did get the impression that LINQ is probably the best SQL abstraction, and that it's much better than Hibernate, which is what I have the most experience with. I think the whole framework situation in general is much better in microsoft land because it's one company that designs the whole ecosystem, and overall experience, and also the people with probably the most experience in software development. I even think VB6 is superior to the standard open source web development stack used today. The only part where MS seems to be behind is version control.