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

What ORMs have taught me: just learn SQL

wozniak.ca

Post reply on HN
Print view View original

201–210 of 245 posts

Re: What ORMs have taught me: just learn SQL

#201

Post by pramalin »

namero999 wrote: post #107
larsmak wrote: post #95

Earlier quoted context omitted.

Been using http://commons.apache.org/proper/commons-dbutils for the same purpose. Works well when I don't need the slede-hammer a full ORM-framework can be. Will look into JDBI as well next time.

I've found the nirvana with MyBatis https://code.google.com/p/mybatis/ I've benchmarked it and it adds roughly a 3% on top of raw JDBC, and allows different styles of usage. You can have your pojos annotated and get mapping for free, or (what I like) you can extract your SQL queries in XML files, name them and refer them from code with sql.insert("namedQuery", params); It is super smart when it comes to mapping/aggre…

I have also used MyBatis in few projects and recommend it for applications that are database centric. I would use ORM only if the requirements call for object persistence and be DB/SQL agnostic.

Re: What ORMs have taught me: just learn SQL

#203

Post by walshemj »

terhechte wrote: post #27

I used to write raw SQL for many years, then, around 2005 switched over to ORMs in order to be able to target different databases, have a nice model, etc. Lets be honest here, the ease of justing doing: p.username = "Carl" p.age = 33 p.save instead of "update users set username=:username, age=:age where id=:id" has a ton of advantages. For one, some sort of syntax or type checker is actually trying to understand your…

If you have been writing raw sql for years I hope you did not use use select star

Re: What ORMs have taught me: just learn SQL

#204

Post by ssmoot »

Fishkins wrote: post #46
terhechte wrote: post #27

I used to write raw SQL for many years, then, around 2005 switched over to ORMs in order to be able to target different databases, have a nice model, etc. Lets be honest here, the ease of justing doing: p.username = "Carl" p.age = 33 p.save instead of "update users set username=:username, age=:age where id=:id" has a ton of advantages. For one, some sort of syntax or type checker is actually trying to understand your…

Thanks for sharing your experience. I've been meaning to try Slick, and YeSQL sounds like a nice way to reduce some boilerplate with no real downside. I go back and forth about how I feel about ORMs. I think everyone can agree you'll need to learn SQL for any non-trivial project, even if you end up using some abstraction on top of it. On a tangent: you mentioned Upserts in Postgres features. I thought Postgres didn't…

Side Note: Slick has basic parameterized queries/statements/DDL.

eg:

  val cachedPurchasesQuery = Q[String, (Int, AccountId, Timestamp)] + """
                                                                          |SELECT pi.purchasable_item_id, u.account_id, pi.created_at
                                                                          |FROM purchased_items AS pi
                                                                          |INNER JOIN users u ON pi.user_id = u.id
                                                                          |WHERE purchasable_item_type = ? AND u.account_id IS NOT NULL
                                                                        """.stripMargin
Then you call that like:

  val magazinePurchases = cachedPurchasesQuery("Magazine").list
Or `foreach`, `firstOption`, etc. Most of the normal collection-y stuff.

I prefer this over the Table mapping DSL and for-comprehension stuff personally. But I've only used it in production on smaller projects that are limited to under a dozen queries/statements or so.

Re: What ORMs have taught me: just learn SQL

#205

Post by dragonwriter »

zak_mc_kracken wrote: post #197
dragonwriter wrote: post #145

Earlier quoted context omitted.

You've just broadened the definition of ORM so that any SQL abstraction layer in an OO language becomes an ORM. That's a rather nonstandard usage.

ORM is "Object Relational Mapper". If you are taking data out of a relational database and mapping it into objects, you are implementing an ORM. Seems like I'm sticking to the exact definition of an ORM, aren't I?

> If you are taking data out of a relational database and mapping it into objects, you are implementing an ORM.

No, ORM is a particular approach to doing that; the query abstraction approach described upthread is closer to the DAO pattern, to which ORM is an alternative. People were using RDBMSs to provide a persistence layer for OO programs before ORM was a thing, but as you have broadened the term any use of an RDBMS to store/retrieve data used in an OO program would be "ORM".

Re: What ORMs have taught me: just learn SQL

#206

Post by walshemj »

makmanalp wrote: post #33

Over and over I keep finding that just an ORM is not enough, but raw SQL is hideous in a different way. ORMs map nicely when you are indeed modifying objects, but somethings don't map well that way. So don't map them that way! What we need is a low level abstraction layer alongside the ORM. The main problem with raw SQL is that what you really want is a genuine programming language. You almost want programmatic acces…

Have you not heard of PL/SQL? or its equivalent for other variants of SQL.

Re: What ORMs have taught me: just learn SQL

#207

Post by buro9 »

The one thing I like about ORMs (where I control the ORM): I can prevent any SQL query from being run that doesn't have a WHERE statement.

Especially when dealing with anything that changes data, lack of WHERE is very dangerous.

That said, I write raw SQL. It's the only way to get the performance. As an abstraction it's already great and I don't feel I need another abstraction on top of it.

An example of the kind of query that an ORM is bad at:

    SELECT id
          ,value_from_row_based_functions(some_column) AS foo
      FROM bar
     WHERE some_column = 'blah'
     ORDER BY id DESC
     FETCH FIRST 25 ROWS ONLY
It's bad because the ORM will merrily do this even though the row based function may be expensive. Over hundreds of rows this isn't an issue, but over millions or tens of millions it kills your query (if it doesn't timeout it will merrily carry on for hours).

Good SQL always reduces the amount of work involved, you tailor it as you would your regular code to avoid doing work that isn't needed (reading rows you will discard through a filter, etc).

The above could be written as:

    SELECT id
          ,value_from_row_based_functions(some_column) AS foo
      FROM (
                SELECT id
                      ,some_column
                  FROM bar
                 WHERE some_column = 'blah'
                 ORDER BY id DESC
                 FETCH FIRST 25 ROWS ONLY
           ) AS something
And now the row based function is only applied to the 25 rows that are going to be returned.

If you look at the very bottom of this: http://www.postgresql.org/docs/9.2/static/textsearch-control... you'll see this is a real-world problem.

ORMs can stitch together your SQL in a certain way that is fairly optimal for most common scenarios... but you will encounter a scenario in which raw SQL is the only way to go.

Re: What ORMs have taught me: just learn SQL

#208

Post by walshemj »

Daishiman wrote: post #108
dicroce wrote: post #7

Here's a thought experiment. Lets say we lived in a world without SQL and the default way to talk to DB's was through an ORM.... And then someone came and said: "I created this concise and super flexible language for querying data." Would people want it? I think they would, and we'd see tons of articles about vast forests of objects being replaced by small snippets of SQL.

When I look at SQL through the lenses of hindsight I see a language that's not amenable to IDEs (it's harder to autocomplete columns if you must write those before the table name, as an example), and has questionable and verbose syntax. While straight relational algebra is actually quite readable, despite all the efforts of most the anti-ORM crowd, at the end of the day the business logic that works on business objec…

Sorry Toad or Work manager and the MYSQL tools are perfectly acceptable IDE's for SQL development.

Re: What ORMs have taught me: just learn SQL

#209

Post by Anonymous_me »

bkeroack wrote: post #23

Another problem with ORMs is that they make performance diagnostics much harder. DB-side, you might have a list of worst-performing queries and examining it reveals a huge, hundred-line monstrosity of a query. Because the queries are ORM-generated and are not usually very readable it isn't exactly clear which part of the application is generating it (or why). Even further, if your DBA says you could make the query mo…

> So you have the choice of abandoning the ORM in performance-sensitive paths and inserting raw SQL (making the code ugly) or living with poor performance.

Most people ask themselves the same question and don't care much about performance. I'm glad you choose performance over code aesthetic. Code aesthetic should only come second to how the system performs. As for the ORM, it has it's downfalls. I've never heard of the N+1 query problem in the plain SQL world. It's a problem brought forth by ORM, because it's just stupid to even think about N+1 when you're trying to accomplish as much as you can in 1 query.

Re: What ORMs have taught me: just learn SQL

#210

Post by JohnCohorn »

estebank wrote: post #172
Joeri wrote: post #154

Earlier quoted context omitted.

Writing sql by hand doesn't have to mean you abandon things like autocomplete and automatic highlighting of typo's. SQL can be inspected by a proper ide just like any other language.

To be fair, SQL has a syntax that is hard to provide (for example) autocompletion for, as the table comes after the fields, and the field names can be ambiguous.

IntelliJ does SQL completion quite nicely based on a connection it can make with your DB: http://www.jetbrains.com/idea/features/database_tools.html
Post reply on HN