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

91–100 of 245 posts

Re: What ORMs have taught me: just learn SQL

#91

Post by Xorlev »

For those of us on Java, I've grown to love http://jdbi.org/. JDBI has a lot of features that are convenient (e.g. auto-mapping of columns to a POJO), but synthesizes DAOs for you from interfaces annotated with SQL queries, e.g.

  @RegisterMapperFactory(BeanMapperFactory.class)
  public interface TripDAO {
      @SqlQuery("SELECT trip_start AS start, MAX(timestamp) AS end FROM location_updates WHERE trip_start = :start GROUP BY trip_start ORDER BY trip_start DESC")
      public Trip trip(@Bind("start") Date tripId);
  }
And allows you to plug in providers for mixing in Optional types, PGgeometries, whatever you can fit into an interface.

Re: What ORMs have taught me: just learn SQL

#92

Post by jimktrains2 »

terhechte wrote: post #73
Fishkins wrote: post #46

Earlier quoted context omitted.

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…

Postgres indeed doesn't have Upsert yet, so I'm going the default way of locking the table, and implementing it via a slightly more complex query. I was just too lazy to explain that in my earlier comment. The problem is the same: The syntax below can't really be represented well in a ORM. BEGIN; LOCK TABLE search_tracking IN SHARE ROW EXCLUSIVE MODE; WITH upsert AS (UPDATE search_tracking SET count=count+1 WHERE key…

You can use writable CTEs as an upsert in postgres. http://dba.stackexchange.com/questions/13468/most-idiomatic-...

Re: What ORMs have taught me: just learn SQL

#93

Post by elithrar »

learc83 wrote: post #71
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…

Is there something like YeSQL for Go?

Not that I've seen, but I've love to see it. You could probably build it with something like text/template and write:

    SELECT id, title, expiry_date FROM posts WHERE id = {{ id }} AND expiry_date > {{ date }} ORDER BY expiry_date
This would give you some flexibility--the template tags would be replaced by ? or $1 or :name depending on the database driver set on parse--and leans on the stdlib. This would tie in nicely with https://github.com/jmoiron/sqlx and its struct tag/marshaling behaviour.

Access could be handled by a map[string]string, with the key as the filename and the value as the SQL (as a first thought).

Re: What ORMs have taught me: just learn SQL

#94

Post by snarfy »

The first ORM I used was basically a bunch of C++ macros for Oracle, about 16 years ago. I've been using both SQL and ORMs ever since in various forms. I'm not sure how anyone could ever use an ORM effectively without knowing SQL.

Some people only know how to administer their box through X11 apps, but others know the command line. It feels like that when working with devs that do not know SQL very well. They'd be lost without their ORM.

Re: What ORMs have taught me: just learn SQL

#95

Post by larsmak »

Xorlev wrote: post #91

For those of us on Java, I've grown to love http://jdbi.org/ . JDBI has a lot of features that are convenient (e.g. auto-mapping of columns to a POJO), but synthesizes DAOs for you from interfaces annotated with SQL queries, e.g. @RegisterMapperFactory(BeanMapperFactory.class) public interface TripDAO { @SqlQuery("SELECT trip_start AS start, MAX(timestamp) AS end FROM location_updates WHERE trip_start = :start GROUP…

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.

Re: What ORMs have taught me: just learn SQL

#96

Post by guscost »

These annoyances are familiar enough, but I think that the author is underselling the benefits of this kind of abstraction. For one project I'm currently using an ORM (NHibernate) with several patterns depending on the requirements and the context:

- With single-table queries and simple joins, having a repository available means that these are a joy to write and maintain with LINQ. And with some preparation the mappings can prevent all sorts of runtime issues.

- Complicated queries which might be tricky to generate optimized SQL for can be written in HQL, meaning that the data access code does not have to be coupled to one brand of persistence store.

- Really quirky stuff that only works in Oracle, for example, can be written raw with CreateSQLQuery(). Here the ORM is basically functioning as an extremely bloated DB session manager, but this is not very often.

I definitely agree that familiarity with the database query language is needed to build non-trivial applications, but at the same time I would not enjoy building many of those applications without the option to query a table in one line of type-safe code. Some folks might say that I'm not using all the features of the ORM unless I have every single foreign key relationship mapped out to lazy-loaded collections and such, but I'm not really comfortable enough with the technology to want to do that, and I don't really mind the extra bit of housekeeping required to handle those cases.

Don't get me wrong, if you must prioritize raw performance or need all the flexibility of your query language, the right decision might be to ditch your ORM. With my current project, stability and maintainability are much more important, so I'll be sticking with the convenient tools for now.

Re: What ORMs have taught me: just learn SQL

#97

Post by jurre »

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…

This is simply not true, lots of tools are able to tell you exactly which line of code is responsible for which query.

Re: What ORMs have taught me: just learn SQL

#98

Post by crazytony »

jdotjdot wrote: post #76
dharbin wrote: post #40

Earlier quoted context omitted.

I'm loving all the momentum towards writing templated-sql, in fact, I wrote a library for this myself[1]. By leveraging jinja2/django-style template inheritance, you can even bring some advantages of ORMs (composition, reuse, and extending) into the raw-sql world. The OP also intimated that he's taking a templated approach: "“In these cases, I've elected to write queries using a templating system and describe the tab…

If you're at all interested in opening a kick starter for such a templating library for Django, I'd back it. I have attribute creep all the time and actually generally prefer raw SQL with the exception of its verbosity. The problem is, migrations are awful and SQL injection mistakes easy to come by. Would be great to have the best of both worlds in a SQL templating engine + sort-of ORM wrapper that auto-generates via…

not sure I follow? Most sql templates I have seen behave like Django's .raw() function (which inspects the resultset and auto binds to the object)

Re: What ORMs have taught me: just learn SQL

#99

Post by marcosdumay »

"I much prefer to keep the data definition in the database and read it into the application. It doesn't solve the problem, but it makes it more manageable. I've found that reflection techniques to get the data definition are not worth it and I succumb to managing the redundancy of data definitons in two places."

My experiency is almost the oposite of that. I've found that automatic migrations are one of the best features of ORMs (only on par with getting reflection on the data structure), and the OO syntax much better for defining data structure than DDL.

Nowadays I favor ORMs even when I don't use the OO data interface, just because it's easier to maintain.

Re: What ORMs have taught me: just learn SQL

#100

Post by aikah »

meritt wrote: post #49

ORMs are great for simple CRUD operations. As soon as you want to do anything mildly complex or desire efficiency, you need to write SQL. As long as the ORM helps me with the former and gets out of my way for the latter, I'm totally happy to utilize then.

that's absolutely not true.Sure you cant write a procedure with an ORM. But good ORM have query builders that with allow you not to write ANY sql.At all.ORMs are not just about CRUD at all.
Post reply on HN