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.
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.
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
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!
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).
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.
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.
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.
That said, there are probably fewer SQL injections in ORM code than typical mysql messes.
I'm looking for a single example of ORM-based project switching RDBMS. It looks like it almost never happens.