What ORMs have taught me: just learn SQL (2014)

Join the discussion

• Hacker News
• In my opinion Elixir Ecto is ORM done right:

  1. the functional/immutable nature of Elixir makes read and writes much more explicit and there is no need to magically track deep mutations of nested objects to translate them back into UPDATE/INSERT queries

  2. Elixirs support for lisp-like macros allows for an ergonomic embedded query languages that is syntax and schema checked, mirrors raw SQL really well and, frees you from string-oriented query building

  3. the query builder DSL addresses one of the main weaknesses of SQL query statements not being composable

  4. The automatic conversion between JOINed tables (on the DB side) and nested structs (on the Elixir side) is done on the right abstraction level to work reliable and and being explicit enough to generate predictable queries.

  by laszlokorte
• As someone who started their programming journey with SQL, it just feels so odd hearing about learning SQL being presented as an useful option. I get it, it just feels odd. SQL was considered table stakes in the financial IT world - if you said you didn't know SQL, people would look at you funny.
  by noisy_boy
• Still applies today in data science, one is expected to master SQL alongside Python and Excel.
  by pjmlp
• I was working with a "full stack" engineer and needed to do some ad hoc data manipulation so I wrote some SQL inserts and updates. He was like "whoa, I didn't know you could do that with SQL!" I was shocked. Like, how have you been working on projects using databases this long without knowing basic SQL? I still don't think they know about DDL at all.
  by globular-toast
• It's very strange too. You can learn something like ~90% of useful SQL in an afternoon. The remainder is stuff that you only really need for extremely performance sensitive operations
  by bluefirebrand
• Back in the 90s when I was in university, SQL (and databases in general) sounded like a boring topic that appealed to people who wanted to go into accounting/finance or some consultancy. I didn't study CS to learn to use an application! So, I took other practical curriculum options like operating systems, compiler writing, and graphics.

  Then I went off and did distributed systems and HPC work for a decade or two, and the closest I got to "databases" was when we had to interact with LDAP. But, eventually our R&D contracts shifted and we were mixing with bioinformatics people. Then, we had a need for structured metadata management, and RDBMS seems like the right tool. So I finally had a reason to teach myself SQL, with a range of OLTP and analytics sorts of workloads on PostgreSQL.

  I have found the existing ORMs in our Python landscape to be really alien and off-putting. I much prefer using the lower-level DB connector and doing my own SQL query building. We also do a bunch of generic/polymorphic work, defeating the main theses of ORMs. Mostly, our schemas are not known at development time, rather they change dynamically. There is no sense in mapping schema to classes, since a developer would have no contact with such classes. Instead, our code has to do "metaprogramming" about table definitions, keying, and reference patterns at runtime.

  by saltcured
• My first job was at a financial services software company. They put everyone through multiple weeks of training on sql. That experience has been paying dividends for 25 years.
  by le-mark
• It should be table stakes for any SWEs working on backend, but it's not. The DB and the code directly interacting with it are way more important than anything you're going to write on top. I keep ending up in situations where I'm the only SWE in the room who really knows SQL, let alone proper schema design, and I have to speak up or else they're going to build an abomination.
  by frollogaston
• Best solution: Learn SQL and understand the relational model. Learn data modelling and normalization. Then choose a good ORM which does not get in the way, but saves a bunch of boilerplate code.
  by bazoom42
• An ORM only saves you boilerplate if you’re mapping relationships to objects. And if you’re doing that, you haven’t learned good data modelling and normalisation.

  ORMs are for storing objects.

  SQL is for correctly modelled data.

  by simondotau
• The big problem is that raw SQL has pretty bad type inference and linting support in most editors. A query builder can still give you a lot of type safety benefits.
  by valzam
• Which is why one is better off using IDEs, especially those from DB vendors.
  by pjmlp
• I think the bigger problem is that SQL is in almost every language a second-class citizen. And even calling it second-class can be seen as a stretch.
  by dadie
• Use testcontainers and make sure you have an integration test for every query..
  by therealdrag0
• Autocomplete is making me lazy. If I don't see what I'm about to type within two or three characters, I feel like the IDE isn't doing its job of helping me. So being able to type `db.Cust` and autocomplete Customers is really nice. I do know SQL, but yes, the language servers usually have a harder time connecting the SQL to my backend code, whatever language it's in, without quite a lot of config fiddling that pretty much obviates any time savings I would have gained from autocomplete.
  by rmunn
• A query builder is not an ORM.

  ORMs build queries for you, but a query builder does not need to be an ORM.

  by Arch-TK
• I'm totally on board with the idea that ORMs create a variety of inefficiencies, pain points, and make it really easy to create bad queries or querying strategies. But I use them anyways because the convenience of mapping a row to a code object makes writing programs feel fast and simple. And if you know how ORMs can cause problems and how to watch out for them, you can still get a lot of mileage out of them.

  That being said, what's the closest alternative that satisfies this - "mapping rows to a code object" - that doesn't suffer the same problems as an ORM? A middle ground between an ORM (like SQLAlchemy, for example) and "your rows are returned as a key/value dictionary where the column names are keys" type approach like Python's DB-API's DictCursor or PHP's mysqli_fetch_assoc. Is there a middle ground here?

  by dpedu
• > the convenience of mapping a row to a code object makes writing programs feel fast and simple.

  Even when you had to do this manually, it was a very minor effort. A one time thing. These days of course any half decent LLM will produce this code without much fanfare. The argument just melts away.

  Otherwise, ORMs just layer abstractions on abstractions. You end up with these weird half implied joins resulting in absolutely terrible actual joins happening. Unless you actually understand what you are doing, in which case you could be hammering out those joins manually. And of course the underlying SQL is usually a bit richer than this one size fits all nonsense ORMs do in order to work across sqlite, mysql, postgresql, etc. and pretend that it's all the same.

  Another issue with ORMs is the object impedance mismatch where a junior wannabe coder thinks it's all just objects and classes and you end up with these gazillions of completely pointless tables that then necessitate a huge amount of joins. Often the right amount of tables is a lot smaller.

  Also, if you aren't querying on it, does it really need its own column? I end up using my databases as document stores quite often. Gets you the best of both worlds. You get to query on nice indexed columns and then you deserialize the big blob of json or whatever into your rich object structure. Simple CRUD for objects shouldn't require a whole lot of engineering. It's only when every little object needs its own little table that shit gets complicated. And another benefit is that this usually results in more stable table structures that don't need a whole lot of database migrations. Getting rid of those removes a lot of needless faff from day to day deployments.

  by jillesvangurp
• No, there is no middle ground. You can either maintain relations throughout the full application or you can transform them into application-native structures, the latter of which is ORM.

  The article seems to be confusing ORM with query builders. Query builders are where you might avoid writing SQL. ORM is a data transformation technique.

  by win311fwg
• Yes, there is a middle ground. Elixir's Ecto does this well.

  Database rows map to structs. But it doesn't try to figure out how to mutate the data for you to keep the struct in sync with the database. All mutations are explicit using changesets (which can also be used for other non-database purposes, like validating user input for an API.)

  There is no implicit preloading of data. You have to explicitly preload.

  Data is never fetched implicitly. You have to call Repo.all or Repo.one or something.

  It has a query DSL that's a thin wrapper over SQL. It's well-designed and I've never had a problem with it.

  by taylorlunt
• In .NET, I think Dapper comes closest to what you are describing. It does the object mapping, but you still write the queries as SQL.

  https://github.com/DapperLib/Dapper

  by bsuvc
• The happiest middle ground I've found in .NET has been LinqToDb.

  It's more of a Micro ORM, -but- has a Linq DSL, as well as DSLs for lots of DB bits. CTEs, Window functions, Bulk copy, 'treat this in memory collection as an input rowset', certain DB Specific bits... and if you need some special sauce to deal with brownfield jank [0] it's very easy to wire-up custom SQL bits into your queries via attributes if needed.

  If you use method syntax rather than linq query syntax, you will have minimal surprises with the SQL generated. Typically if it does generate something I didn't expect, I dig in and what it did was indeed both correct and better than what I was trying to do anyway.

  [0] - Fun nasty case I ran into on a brownfield project; 'If this number has a decimal point, it is a direct percentage rate. If the number does NOT have a decimal point, it is the FK to a lookup table that has the percentage rate'

  by to11mtm
• Related:

  What ORMs have taught me: just learn SQL - https://news.ycombinator.com/item?id=28812506 - Oct 2021 (24 comments)

  What ORMs Have Taught Me: Just Learn SQL (2014) - https://news.ycombinator.com/item?id=24845300 - Oct 2020 (291 comments)

  What ORMs have taught me: just learn SQL (2014) - https://news.ycombinator.com/item?id=21031187 - Sept 2019 (634 comments)

  What ORMs have taught me: just learn SQL (2014) - https://news.ycombinator.com/item?id=15949144 - Dec 2017 (348 comments)

  What ORMs have taught me: just learn SQL (2014) - https://news.ycombinator.com/item?id=11981045 - June 2016 (295 comments)

  What ORMs have taught me: just learn SQL - https://news.ycombinator.com/item?id=8133835 - Aug 2014 (234 comments)

  by dang
• Seems like we needed an annual meditation on this topic until 2021, then we took a 5 year hiatus? What happened?
  by getnormality
• I was curious how have sentiments changed over time. Brief LLM-based analysis: https://ampcode.com/threads/T-019f32ac-3b1e-74be-ad63-5f175d...

  Overall, seems like it got more nuanced over time - even though it's still broadly in favor of SQL. Favor for ORMs (flagged also as a term that can mean many things to different people) is more in terms of type safety, mapping, migrations, etc. so more a library/utility rather than a framework that fully abstracts away the database.

  by weiliddat
• I used to love ORMs so much that I built one for Java, in the early 90s, and it was one of the main offerings of a startup that I joined. I have come around 180 degrees. My rethink started when a developer at a Wall Street bank said: having Oracle on my resume is valuable. Having your ORM on my resume is not.

  And then there’s the “now you have two problems” dynamic. You not only have to write high-performing queries, but you have to get the ORM to generate that query for you. And sometimes you don’t want objects. And the schema mapping has to track schema changes.

  Just write the damned SQL, it’s not that difficult.

  by geophile
• > built one for Java, in the early 90s

  So was your ORM for Oak? Java didn't hit the public sphere until 1995 IIRC

  by jghn
• ORMs are so incredibly finicky. I still remember using old Linq-to-SQL (not Entity Framework) and I had to write the linq query in the reverse order of what I expected or it created 3 nested subqueries instead of just joining the tables together. That was when I learned to instantly double check every ORM query I wrote.
  by runevault
• I generally like ORMs but recognize that they have a lot of problems. The most common problem that I've seen is when an ORM makes it easy to select records in a way that looks efficient but really is not. Strictly speaking, this isn't a failure of the ORM itself -- it's the fault of the developer who is using the ORM and also the developer that didn't catch it in code review. But it's a case where the ORM is making work for everyone and obscuring legibility into the code instead of saving time and providing clarity.

  I've written complicated stuff where an ORM isn't appropriate, but if I'm honest, a large fraction of what I've done in my career is just making boring software to automate menial clerical work, and ORMs are good enough for those kinds of projects.

  by vandahm
• > Strictly speaking, this isn't a failure of the ORM itself -- it's the fault of the developer

  You've got that backwards. If a tool obscures complexity such that a developer using it could be tricked into thinking their efficient-appearing code is actually inefficient, the problem is the tool. A well-designed tool makes inefficiencies explicit. "You're holding it wrong" is not engineering advice.

  > ORMs are good enough for those kinds of projects.

  It's all good as long as you have properly abstracted it away from your core application. The trouble with some ORM toolkits is that they encourage you to move database logic into the rest of the application and that's when the messes begin. The old school PHP programmers will know well that SQL in raw doesn't automatically mean proper separation of concerns either, but it is more likely to push you in that direction.

  by win311fwg