[rondon2]

I'm sure anyone can come up with specific situations where writing SQL is much better than using ORM. If I had infinite time and dev resources I would never use an ORM. In reality Entity Framework makes abstracting my data-layer very easy and increases my productivity. It also makes me feel much more comfortable about switching databases if necessary. Even if I'm taking a 5% performance hit it is totally worth it to me.

[jon-wood]

> It also makes me feel much more comfortable about switching databases if necessary.

This is one of the big features touted by ORMs, but I wonder how often it actually comes into play. I've been using Rails with ActiveRecord for most of the last 10 years now, and I've not once changed databases after starting a project. There's one project where I'd like to, but despite the use of ActiveRecord, there's enough MySQL specific stuff in the code that its not going to be trivial to just copy the data over to a Postgres database and flip the switch.

[spacemanmatt]

> but I wonder how often it comes into play.

Databases are not interchangeable, though sharing a common query language makes the skill set somehat interchangeable. I think this is part of the nuance often lost in the discussion of ORMs and database-portability.

I have worked with many developers who actively deny this basic reality, in favor of trying to bury all that variability under an ORM, rather than exploit any of the proprietary features. I understood it as a crutch when I was still in the flat part of that learning curve, but I am a lot happier building a rich SQL application interface (non-ORM!) these days.

[mercurial]

> Databases are not interchangeable, though sharing a common query language makes the skill set somehat interchangeable. I think this is part of the nuance often lost in the discussion of ORMs and database-portability.

I see two realistic reasons for why having an ORM/avoiding DB-specific queries is good idea:

- your product supports multiple databases

- you want to use something like sqlite in memory for tests

[spacemanmatt]

> your product supports multiple databases

In this case, I tend to see pretty light loads. You can do this with fairly vanilla (ORM-generated) SQL and design. When your business begins to lean heavily on the database for operations (I saw this in finance) around the clock, and you have to start being more judicious about your queries, these applications are usually the first to buckle.

> you want to use something like sqlite in memory for tests

Only if I have to support sqlite in the field!

[towelguy]

It's not so much switching databases during a project, but using the same skillset across projects regardless of the database engine used. SQL syntax is different enough across databases to require re-learning it.

[wylee]

> I wonder how often it actually comes into play

I've wondered the same thing in the past, but just recently I converted two Django projects at work from MySQL to PostgreSQL. The transition was pretty much seamless--I didn't have to change any application code.

One of the projects was converted to add spatial capabilities via PostGIS. The other was converted due to an issue with how MySQL stores data (it ended up being easier in a time crunch to dump and reload into PostgreSQL than fix the issue with MySQL).

[Demiurge]

I made another comment already how it's trivial to start a web project with SQLite and migrate to a 'real' database, or continue to use it interchangeably for local development.

But I also just realized, there is a whole ecosystem of ORM based apps that can run on any of the supported databases. If you ever used an MVC framework and re-used apps, you have "switched databases" without even knowing, depending on what the original developers used.

[rondon2]

I'm in the process of moving some of my data storage from oracle to redis. Having already used Linq has made this easier. I had a pretty small data-model though. I think that in a large project switching databases is a large effort with or without ORM.

[crdoconnor]

>I'm sure anyone can come up with specific situations where writing SQL is much better than using ORM.

You are supposed to bypass every ORM sometimes. One measure of a good ORM is whether it makes that easy for you or difficult.

Regex queries are definitely one example of a niche SQL query that I would bypass an ORM altogether in order to use.

95% of my SQL queries are not that, though, and for the very standard ones I use an ORM to heavily restrict the amount of code I have to write and maintain type safety.

[joelthelion]

The problem is that this 5% hit very often becomes a 1000% hit when you let the ORM in the hands of people who don't understand it and they do a thousand queries when a single one would be enough.

[JustSomeNobody]

I always hear (one of) the argument for ORMs being "If I want to switch databases...", but in reality, after having questioned many many devs, nobody has ever _actually_ done this.

[spamizbad]

As one of the few devs who has actually switched databases (SQL Server to Postgres) -- without an ORM -- the biggest timesink was regression testing sanely and exporting/importing the data. Which you would have to do with an ORM anyway. The amount of time I spent re-writing a total of ~20 non-ANSI SQL queries and re-creating a handful of views (some of which were quite complex) was no more than 3 hours. And that would have gone by much faster had I been more familiar with Postgres at the time.

[Demiurge]

> regression testing sanely and exporting/importing the data. Which you would have to do with an ORM anyway

You can export data into ORM specific, database agnostic format, such as Django fixtures or SQLAlchemy fixtures, and test against the ORM. This is actually one of the key ORM features to leverage.

[Demiurge]

Pretty much ever other django project I work on starts with sqlite, on developers machine and only later gets migrated to PostgreSQL. I'm also dealing with legacy mysql website that didn't use ORM and I really wish they used an ORM. They hard coded column names, relationships, in absolutely most random places. Yes, this could be fixed with having better programmers, but if they used an ORM I could simply ignore a whole class of legacy!

[acveilleux]

I dunno, I've seen ORM absolutely abused. Never underestimate the effect a tool can have in the hands of a clueless person.

That said, there are probably fewer SQL injections in ORM code than typical mysql messes.

[simcop2387]

My previous company is actually in the process of switching from Oracle to MySQL. That said doing so is of nightmarish proportions because they have 10-15 years of legacy behind them of building their own ORM, using a modern one and just raw Oracle-SQL (no ANSI joins there!) to either replace, rewrite or drop.

[spacemanmatt]

Oracle -> MySQL? You have my most profound sympathies. Even before the ORM nonsense.

[ChuckMcM]

We switched from MySQL to Postgres on our analytics database and it would have been a lot easier with an ORM.

[dennisgorelik]

How do you know it would have been easier with ORM?

I'm looking for a single example of ORM-based project switching RDBMS. It looks like it almost never happens.

[jdmichal]

Agree that ORMs like Entity Framework are very good at taking basic SELECT and INSERT/UPDATE statements and jamming them into objects without a lot of code. However, consider that perhaps your position is a judgement of how few features of a database system you have needed to use, and not necessarily a judgement on the robustness of ORMs.

[matwood]

I have found that ORMs get you up and running more quickly, but that time saved is eventually given back working through ORM performance issues. As someone who has written complex SQL for many years, it is simply faster and easier to just write optimized queries up front. Something like jOOq makes this nice with typing and some minimal object mapping.

[67726e]

That's ultimately true of any framework, but ORMs are a beast all their own. Having a light abstraction framework on top of SQL, that ultimately respects it, makes a huge difference at the end of the day. No magic, just results. I've watched coworkers spend days tracking down obscure performance issues related to mapping in Hibernate that just wouldn't occur when using something like jOOQ. The only overhead I get is possibly having some DTOs to map into via a simple interface. Hardly more effort than one would expend configuring Hibernate mappings what with its myriad options.

[joesmo]

I find the opposite to be true. I have to learn a whole bunch of new methods and concepts to use an ORM like Doctrine 2 and even a whole new query language for when the basics fail which is most of the time. I can't imagine a way to make development slower than adding an ORM.