Comment by abustamam
1 day ago
Interesting. The reason I like ORMs is because of type generation in TypeScript. I've never worked in a company that didn't use an ORM which is likely why I prefer it. But if I ever did work in a company that used raw SQL I'd probably just suck it up and learn better SQL. Maybe then I'd be able to make a more informed opinion.
With regards to SQL being low level, I primarily work with TypeScript so a language that talks directly with the DB (SQL) seems pretty low level compared to TS. I'm not sure what you mean by an ordinary programming language though (obviously not machine code).
I spent 5 years working at a place without an ORM. Due to sharding and scalability issues, an ORM wasn't possible (the tech was nearly 20 years old when I left, so they didn't get some of the later database scaling tech). When I went to a company with an ORM, I had problems.
Namely, the ORM got in my way so much. I knew exactly which query to run and how to word it efficiently, but getting the ORM to generate sane SQL was nearly impossible. I eventually had to accept my fate of generating shitty SQL at every company since then...
That being said, I'll always advocate for ditching an ORM if given the chance and the expertise is available. If nobody knows why you generally wouldn't want to put an index on a boolean column, we're probably good. If people think it will help performance on a randomly set boolean field, we should probably stick with an ORM.
Most ORMs I've worked with have a special method that lets you write raw SQL. Probably not the most ergonomic way, but it is an escape hatch. Which ORM were you using?
Most teams I've been on will automatically reject a PR writing raw sql due to maintainability concerns. I would never consider it in a professional context unless it can be done in a way that guarantees it is easy to maintain without using concatenation.
Most ORMs don't have the SQL tools we did to sanitize variables when putting them into queries. Some do, but not all.
Java, Python, JavaScript, TypeScript are more or less the same and on level below SQL (when it comes to querying structured data).
The SQL is declarative query language. You describe the query, and database engine automatically builds a plan to execute the query. This plan automatically uses statistics, indices and so on. You don't generally specify that this query must use this index, then iterate over this table, then sort it, sort another table, merge them, the database engine does it for you.
Imagine that you have few arrays of records in JavaScript and you need to aggregate them, sort them, in an efficient way. You'll have to write your logic in an imperative way. You'll have to write procedures to maintain indices, if necessary. SQL does it better.
It it an interesting exercise to imagine programming in a language with built-in RDBMS (or object database system) for local or global variables. For example React Redux uses structures, which are somewhat similar to database. I don't really know if it would be useful or not, to write SQL instead of functional API (and get performant execution, not just dumb "table scan") but I'd like to try. C# have similar feature (LINQ), but it's just API, no real engine behind it.
Thanks! That makes sense.