3 ms·

Writing raw anything is not necessarily your job. However, a lot of people have an interesting view of things like frameworks, ORMs, and other abstractions. To

by jchw 5y ago

Writing raw anything is not necessarily your job. However, a lot of people have an interesting view of things like frameworks, ORMs, and other abstractions. To some, it feels like pure all-or-nothing; but what if it didn’t have to be? Even the biggest hater of SQL database abstractions will admit there are reasons why someone would want them. But I wonder if there is an answer somewhere in the middle that could go both ways. For example, sqlc[1] provides basically all of the type safety that overbearing database frameworks might, but it lets you write plain old SQL just as you normally would, and integrates cleanly with your existing migrations solution. It doesn’t solve everything, but I have a feeling more people would like tradeoffs like these: an enormous amount of complexity does exist, to say, parse SQL and emulate DDL transactions for the sake of typing, but it stays entirely in the tool itself, and the resulting code and interfaces are completely simple and obvious code. I believe that there are multiple better middle grounds waiting to be discovered in any given niche where the options currently feel like barely-worth-it trade-offs. [1]: https://sqlc.dev https://sqlc.dev

jseban 5y 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 5y 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 5y 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.
shroompasta 5y ago
I found it significantly easier to set up table structures and migrations through ORMs and abstractions as opposed to raw sql scripts. Moreover, many ORMs give you the tooling to validate data before hitting the db, which is also another huge plus. I mainly work with Python on the backend, and I've recently switched from SQLAlchemy + marshmallow + alembic to SQLModel and both have been amazing to work with. I would never dream of setting up tables through plain raw sql ever again. If it is the case that I'm moving onto a different language, as Go may be it, I'm willing to learn the ORM, granted that it deals with migrations and validations out of the box.

by jchw 5y ago

jseban 5y 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 5y 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 5y 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.

shroompasta 5y ago

I found it significantly easier to set up table structures and migrations through ORMs and abstractions as opposed to raw sql scripts. Moreover, many ORMs give you the tooling to validate data before hitting the db, which is also another huge plus. I mainly work with Python on the backend, and I've recently switched from SQLAlchemy + marshmallow + alembic to SQLModel and both have been amazing to work with. I would never dream of setting up tables through plain raw sql ever again. If it is the case that I'm moving onto a different language, as Go may be it, I'm willing to learn the ORM, granted that it deals with migrations and validations out of the box.