3 ms·
When you write SQL yourself you get 2 major advantages over (many) ORMs and query builders. 1. There's no surprises in the SQL generated (or when it's generate
by cogman10 5y ago
When you write SQL yourself you get 2 major advantages over (many) ORMs and query builders.
1. There's no surprises in the SQL generated (or when it's generated) and when you are interacting with the DB. In the worst case for ORMs, simply changing a field on a ORM controlled model can result in IO with the DB. That can be pretty surprising
2. SQL, once learned, is fairly straight forward and easy to read. Once you are comfortable with it, even complex CTEs don't take too much effort to grok. I'd argue that there are minor readability gains from ORMs (in general). The biggest value add to ORMs is integration with things like intellisense. However, IDEs like Intellij are becoming context sensitive such that you can still get that intellisense even when writing SQL in something like Java.
Right tool and job and everything. However, writing SQL is often treated as if it were assembly or something. SQL is not that. It's a fairly high level DSL for set operations.
- hpen 5y agoNice so I actually do know SQL somewhat and use it extensively at work. I have started using other options in my side projects and found it to be more streamlined. Just curious where ORMs fail really.
- cogman10 5y agoI definitely understand the draw of ORMs. The big issue with SQL in most programming languages is the pretty large impedance. It's like writing go in a Java source file. Doable, yes, but not something anyone really likes to do. However, ORMs can easily cut the other way. So, it's all about making sure you know what you are doing.
- nhoughto 5y agoYep, a migration script (if you have data you care about) is very important, it should be reviewed before being excluded. It has the power to blow away your data if done poorly. So having an explicit SQL statement (whether hand crafted or generated and exported) that is visible is super important, it means you are thinking about this important step, so its visible and harder to forget. The classic problem with letting your ORM manage migration is if you are updating your database entity model as part of a wider change, in your ORM and you rename a column, does it know its a rename? or is it going to DROP COLUMN + ADD COLUMN? and lead to data-loss? To cover these cases and all the infinite other permutation you end up needing to give the ORM more and more state about the past, making it just a more complex migration. Making the migration explicit SQL and decoupled from the ORM forces thought, a human is unlikely to write DROP COLUMN..