Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

wozniak.ca

Post reply on HN
Print view View original

191–200 of 654 posts

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

#191

Post by ken »

daenz wrote: post #14

ORMs lure you in with a false sense of neat abstraction. They have nice intuitive examples on their home pages. But then you use them in the real world, doing gnarly queries, and you realize that doing anything powerful and fast in the ORM requires its own completely separate abstractions, which are often difficult for the uninitiated to follow. It's also often a big pain to debug the raw SQL that gets compiled after…

As someone who’s been using mostly Clojure recently, wouldn’t this apply to programming language based object systems, too? Maybe the problem with the object relational impedance mismatch is the objects.

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

#192

Post by taffer »

BowBun wrote: post #175
jayd16 wrote: post #170

Earlier quoted context omitted.

Its really not though. C is just sugary Assembly that abstracts some things but the design patterns are aligned. ORMs promote a fundamental mistake of coupling your object model to your normalized data model.

Great comment. Whereas typical object models have many relationships and out of these, nested data, the optimization for SQL lies essentially in the other direction. Your DB should be optimized for speed, your code for use and readability. These are concepts, IMO, that are a classic trade-off against one another and it doesn't make sense to combine them into a single abstraction.

The basic idea of the relational model was actually data independence, i.e. optimization for flexibility. If you are interested you can have a look at the original paper from 1970: https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf

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

#193

Post by timdev2 »

Glyptodon wrote: post #150
daenz wrote: post #101

Earlier quoted context omitted.

Running raw user SQL isn't a prerequisite of an ORM needed to make it an "ORM", it's a useful feature that most ORMs try to include because the authors recognize the many shortcomings. Also, by writing raw engine-specific SQL, you automatically invalidate one of ORMs biggest selling points which is being SQL-database agnostic. And by "drop into", this typically means writing custom stitching code that stitches the SQ…

I've always thought the "being SQL database agnostic" theory of ORMs was more about a development team being able to choose from some common choices than about apps being portable in practice.

Right. That. An ORM for (only) PostgreSQL would seem odd -- though I do wonder if that might pay off in some ways.

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

#194

Post by philwelch »

falcolas wrote: post #155
timdev2 wrote: post #119

Earlier quoted context omitted.

> you automatically invalidate one of ORMs biggest selling points which is being SQL-database agnostic. I haven't heard anyone talk seriously about database-agnosticism since the very early 2000s. Maybe some commercial products still try (choose MS or Oracle!), but it's rare nowadays. The primary selling point of an ORM is that it abstracts marshaling/un-marshaling rows to/from entities. Instantiating and persisting…

> I haven't heard anyone talk seriously about database-agnosticism since the very early 2000s. Do you use the same database engine for your unit and integration testing as you do production? I don't. I use sqlite for unit and local integration testing, and aurora-mysql for production. As a side note, I quite literally can't use aurora-mysql for local unit and integration testing. It doesn't exist outside AWS.

Using a DB inside your unit tests is an antipattern and arguably a violation of the concept of a unit test in the first place.

Integration tests should run against a test environment, otherwise, what integration are you testing? I don't see the value in writing integration tests that test the integration between my code and a one-off integration test DB that exists solely for the purpose of integration testing.

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

#195

Post by micksabox »

bob1029 wrote: post #51

Swapping from EF6 to Dapper was one of the best choices we ever made with our project stack. It is so relieving to be able to hand-tune queries and transactions now. Initially, we were sold on the apparent simplicity of EF6, but as with many things there is a cost for an abstraction like this. In our case, the performance penalties and opaqueness were dealbreakers after a while. We saw an average speedup of 10x on al…

Some commenters have asked what kind of business would deploy this kind of technique.

I’ve done it. For MLS syncing software, there are lots of properties, not thousands but can be hundreds. And each MLS RETS has its own Schema, so for any kind of logic portability this is necessary.

Actually, storing the raw data as a blob is a flexibility technique and is a separate concern than the number of fields. As I can’t predict the future set of optimized queries I’ll need, and I don’t want to constantly sync and resync (some MLS will rate limit you), then this way I can store the raw data once, and parse plus update my tables/indexes very quickly.

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

#196

Post by Daishiman »

falcolas wrote: post #155
timdev2 wrote: post #119

Earlier quoted context omitted.

> you automatically invalidate one of ORMs biggest selling points which is being SQL-database agnostic. I haven't heard anyone talk seriously about database-agnosticism since the very early 2000s. Maybe some commercial products still try (choose MS or Oracle!), but it's rare nowadays. The primary selling point of an ORM is that it abstracts marshaling/un-marshaling rows to/from entities. Instantiating and persisting…

> I haven't heard anyone talk seriously about database-agnosticism since the very early 2000s. Do you use the same database engine for your unit and integration testing as you do production? I don't. I use sqlite for unit and local integration testing, and aurora-mysql for production. As a side note, I quite literally can't use aurora-mysql for local unit and integration testing. It doesn't exist outside AWS.

> Do you use the same database engine for your unit and integration testing as you do production? I don't. I use sqlite for unit and local integration testing, and aurora-mysql for production.

Yeah, don't do that.

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

#197

Post by amedvednikov »

I agree. Unfortunately each database has its own SQL dialect with small differences, so migrating to a different database becomes a problem when using plain SQL.

You also lose compile-time checks.

I'm trying to combine the best of the two approaches in the V language. It has a built-in ORM that uses SQL-like syntax:

  uk_customers := db.select from Customer where country == 'uk' && nr_orders > 0
  println(uk_customers.len)
  for customer in uk_customers {
      println('id: $customer.id; name: $customer.name')
  }
https://vlang.io/docs#orm

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

#198

Post by Daishiman »

daenz wrote: post #90
madhadron wrote: post #73

Earlier quoted context omitted.

> They are for making manipulating the entities This is where I disagree. I dislike having a value which is the entity. The basic lesson from relational databases and later data oriented design is that you don't have an entity. All you have are aspects that are related.

This is also tapping into one of the many optimizations you have to know about when using ORMs. Can you "select" 10M rows from a table? Is an object instantiated for each one? If they're lazily created, when are they destroyed, and where is the buffer of rows held, client side or server side? How do you efficiently update each one without incurring a sql statement for each update? All of these questions require deep…

Not really. It isn't a SQL issue; it's a database driver problem.

The Django ORM takes take a whole of an hour to read, and after that you have 90% of the use cases covered. Considering that I've been using that framework for many years now, the overhead of that knowledge is irrelevant.

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

#199

Post by echelon »

amedvednikov wrote: post #197

I agree. Unfortunately each database has its own SQL dialect with small differences, so migrating to a different database becomes a problem when using plain SQL. You also lose compile-time checks. I'm trying to combine the best of the two approaches in the V language. It has a built-in ORM that uses SQL-like syntax: uk_customers := db.select from Customer where country == 'uk' && nr_orders > 0 println(uk_customers.le…

The solution is to use something closer to a compile-time SQL or DSL, such as jOOQ or SqlAlchemy (sans the ORM pieces). These are terrific technologies that improve upon SQLs weaknesses and achieve some level of portability.

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

#200

Post by codr7 »

ianamartin wrote: post #140
codr7 wrote: post #122

Me thinks someone needs to have a go at maintaining a 2Mloc accounting package built using only embedded SQL statements and stored procedures, including migrating the whole mess between major database vendors. I guess one advantage is you have to learn, it but I really prefer some kind of ORM for more mundane repetitive CRUD. More to get a structured (ha!) interface between the database and the application than for t…

I deal with that. Millions of lines of SQL stored procs. It's awful, but not anything an ORM could help with.

Not all of it, but I'm sure most statements follow some kind of CRUD pattern.

Getting that out of the way is exactly what an ORM can help with.

Post reply on HN