Hacker News Reader

Comment by Joeboy | Hacker News Reader

In Django, for me the killer feature of the ORM is that it's (mostly) database agnostic, which means that you can use Postgres in production and in-memory sqlite when testing, which makes testing a gajillion times faster.

If you start writing custom SQL you have to introduce horrible bodges to work with whatever database is in use.

View on HN

That makes testing faster, sure, but then when you deploy your app to production you can get lots of weird errors because SQLite is weakly typed and Postgres is strongly typed, for example. Using the same DBMS in dev and prod is a best practice for this reason, even if it makes your test suite run slower.

Database portability is a poor argument for using ORM in my opinion.

In the real world, most apps don't change database engines during their lifetime. You wait for the rewrite and then you pick a new engine.

If you stick to ANSI compliant SQL, you should be fine when porting over databases. It's not perfect but it'll get you most of the way.

> In the real world, most apps don't change database engines during their lifetime.

Perhaps not, but a lot of times you may have to support multiple database engines at once (this is more true of e.g. middleware than an end-user application obviously).

> Database portability is a poor argument for using ORM in my opinion.

Especially since SQL abstraction layers exist that are not also ORMs (e.g., Sequel for Ruby -- which, yes, has an ORM included, as well.)

> If you stick to ANSI compliant SQL, you should be fine when porting over databases.

This would be true if most real RDBMSs implemented a superset of any particular ANSI SQL standard, but that's not actually the case. Most real SQL-based RDBMSs support different subsets of the standard plus different extensions, so its not all that common that non-trivial code ports well without conversion.

I like this in theory, but in practice I have found that the differences between databases make this not work as easily as it should. I often end up seeing subtle behavior differences when using different DBs, which can be problematic when something works fine in development and fails in production.

Have you run your (mostly) database agnostic app at a large scale?

When your app is running at scale, did you require any custom SQL or non-standard to the ORM stuff to get it running well?

In my experience with Rails, the "database agnostic" features are for prototyping, and by the time you're a real app moving towards some idea of scale, there is nothing "agnostic" about your ORM and database code...

A fair point, I might be unduly influenced by having done a lot of work on a huge, unwieldy internal app. Running the tests in postgres took literally half a day, and there's no way we could have worked like that.

In that case trading some db efficiency for actually being able to do work was a pretty easy decision, but horses for courses I guess.

I've combined Postgres views and set returning functions in rails and had a lot of success with good performance and good maintainability, too. No custom SQL required in the application itself.

That's supposedly to be the ideal case. The problem is that in reality, once you get to the more advanced features and each SQL differs each other slightly, it's anything but agnostic (if it's even there to start with).

Take UPSERT for example, Postgres doesn't have upsert until lately (I'm not actually sure if it's in stable yet), and the upsert's equivalent of MYSQL is INSERT ... ON DUPLICATE KEY, which behaves different than a normal upsert. The ORM solution? Don't implement them at all. In the end, you often end up with a mixture of bizarre ORM code + SQL.

The best compromise I've seen is probably Korma for Clojure (http://sqlkorma.com/). It's not really an ORM, since object mapping isn't particularly necessary in clojure (with map manipulation and all that), but still provides you with some stuffs of normal ORM (implicit relational joins). And yet still, many times once you get to the truly rarely used part of the db ... there is nowhere but raw SQL query to go.

Reply on news.ycombinator.com