4 ms·

> SQL is very hard to learn properly, with all of its gotchas and inconsistencies. I don't understand why though? I've been using SQL (Postgres for the most pa

by isatty 6y ago

> SQL is very hard to learn properly, with all of its gotchas and inconsistencies. I don't understand why though? I've been using SQL (Postgres for the most part but with a smattering of MySQL thrown in) for around 8(?) years, which isn't much in the grand scheme of things but I have not had anything that couldn't be resolved. I've written small straightforward queries to over 200 loc and never had a problem understanding it if you read it slowly/broke it down into smaller queries. In fact, ORMs have been a massive headache because I can think in SQL but not in whatever the creator of the ORM was thinking in. Those giant queries that I was talking about - there's no way to represent them in ORM form. SQL works in a language agnostic way, you can explain analyze your SQL queries and run it through whatever medium you prefer. Typical experience with an ORM goes like so: 1. Lets use an ORM because it'll be easier 2. It's not actually easier and it's a complex mess now, but let's stick with it anyway 3. Figure out a way to log the queries that the ORM made up for you/printf it 4. Run that through EXPLAIN ANALYZE 5. Can't make the ORM do that, file a bug report that'll be buried 6. Use native query while you wait 7. Tech debt etc;

fogetti 6y ago
In my experience people who have trouble with ORM usually try to use it for something which it is not well suited for: namely OLAP. ORM can really get in the way when you want to express groupings, aggregates, and all kind of joins sprinkled with let's say stored procedures. But this has nothing to do with the ORM itself. It's just a fact that many people don't understand/consider the tradeoffs before jump into acting on something.
viraptor 6y ago
I'd rearrange the list a bit to what I see experienced devs do, and I don't see a problem with it: 1. Lets use an ORM because it'll be easier (skipping 2, because it's not really a mess; skipping 3, because they know that before getting to this point) 4. Run that through EXPLAIN ANALYZE 5. Can't make the ORM do that 6. Use native query 7. Profit There's nothing wrong with having `Users.active.find(id)` where you want it and writing out the complex query as SQL where it gets complicated. They can live next to each other just fine and still improve your life. Going extreme in any direction is going to cause problems. (whether 100% ORM, or SQL purity) It's fine to use different approaches where they're appropriate.
zvrba 6y ago
> In fact, ORMs have been a massive headache because I can think in SQL but not in whatever the creator of the ORM was thinking in. Yes, fully agree on ORMs. They DO have one nice feature though: simple CRUD operations are way less verbose than constructing SQL statements. What we lack is a better integration between the host language and the databse. Constructing a prepared statement from a string, setting parameters, executing, fetching rows from the result set and mapping back to fields... all is a major, repetitive PITA. And yes, I find it easy to think in SQL and often wonder WTH an ORM is going to generate. Just recently I improved performance of an application by going from ORM to SQL; first I reduced number of round-trips (ORM/efcore first wants you to fetch an entity before you can update it), second, I batched updates into a single session/transaction. Win! :) [Oh, and don't get me started ranting about ORM and transactions.]
- fogetti 6y ago
  Just to let you know, those things are not needed at all if you use a different type of ORM, similar to ActiveRecord or Django ORM. It all depends on what you choose. So probably the first mistake was that you didn't do the homework of exploring ORMs maybe?

by isatty 6y ago

fogetti 6y ago

In my experience people who have trouble with ORM usually try to use it for something which it is not well suited for: namely OLAP. ORM can really get in the way when you want to express groupings, aggregates, and all kind of joins sprinkled with let's say stored procedures. But this has nothing to do with the ORM itself. It's just a fact that many people don't understand/consider the tradeoffs before jump into acting on something.

viraptor 6y ago

I'd rearrange the list a bit to what I see experienced devs do, and I don't see a problem with it: 1. Lets use an ORM because it'll be easier (skipping 2, because it's not really a mess; skipping 3, because they know that before getting to this point) 4. Run that through EXPLAIN ANALYZE 5. Can't make the ORM do that 6. Use native query 7. Profit There's nothing wrong with having `Users.active.find(id)` where you want it and writing out the complex query as SQL where it gets complicated. They can live next to each other just fine and still improve your life. Going extreme in any direction is going to cause problems. (whether 100% ORM, or SQL purity) It's fine to use different approaches where they're appropriate.

zvrba 6y ago

> In fact, ORMs have been a massive headache because I can think in SQL but not in whatever the creator of the ORM was thinking in. Yes, fully agree on ORMs. They DO have one nice feature though: simple CRUD operations are way less verbose than constructing SQL statements. What we lack is a better integration between the host language and the databse. Constructing a prepared statement from a string, setting parameters, executing, fetching rows from the result set and mapping back to fields... all is a major, repetitive PITA. And yes, I find it easy to think in SQL and often wonder WTH an ORM is going to generate. Just recently I improved performance of an application by going from ORM to SQL; first I reduced number of round-trips (ORM/efcore first wants you to fetch an entity before you can update it), second, I batched updates into a single session/transaction. Win! :) [Oh, and don't get me started ranting about ORM and transactions.]

fogetti 6y ago
Just to let you know, those things are not needed at all if you use a different type of ORM, similar to ActiveRecord or Django ORM. It all depends on what you choose. So probably the first mistake was that you didn't do the homework of exploring ORMs maybe?