First time it was a home-grown layer on top of the DBMS (MS SQL Server at the time). After an investment we've got a requirement to switch to Oracle. We anticipated that (or something like it) when designing the system, so it went smoothly.
Second time it was using MySQL from Sun workstations, with the idea of eventually switching to Oracle. That's why "almost" - we knew about the requirement in advance, so we didn't create hard dependencies on MySQL. Yet we didn't constantly test our system vs. Oracle either.
Admittedly the latest of these happened more than 10 years ago.
I'm not sure I understand the question. I've used an ORM with a codebase that used different databases in development and production, so in that sense, yes. But if you mean "swapping" a production database for another, that's not really how I'd describe it. If you're switching from one database system to another, that sounds like a major migration, not just a "swap." You can't exactly just drop a SQL Server database into PostgreSQL and assume all is well, can you? So I'm not sure where the ORM angle comes into this — a major database migration is a task in itself. (Or maybe I'm just a bad sysadmin? Entirely possible, I suppose.)
to try to be a little clearer - I meant swap amongst MySql/Oracle/SQLServer/whatever. It seems that this was (still is?) one of the purported advantages of an ORM in general, and I was just curious how often it really even comes into play.
The benefit of using an ORM is that if you did want to change the type of database, most of your code would be able to go unchanged (theoretically). If you did switch, you should be able to just change a config file to let the ORM know which database you are now using. The ORM will then run the correct underlying queries.
This usually comes with a hit to performance though. Raw queries are generally quite a bit faster. So if you dont plan on changing databases on a project, it might be beneficial to skip the ORM where possible.
The ORM also doesnt help you with migrating the records from one database type to another. So if you already have established records that is a separate issue.
I've "swapped" H2 and Sybase in a Java/JPA/Hibernate project. I use quotes because H2 was an in-memory DB for the unit tests, but it has the same table structure in both cases. And it wasn't really swapped so much as built at the same time, but the same code did work on both.
I do it all the time in early stages of app development, moving between SQLite/MySQL/Postgres.
Once you get into production it's a different story. A good ORM makes it easy to swap to a different DB without need for code changes. What it does not help with is the much bigger problem of how to migrate the data, indicies, backups, replication, etc. that you'd have in place for a production database.
I love the idea of an ORM but I always find myself relying on database specific features and end up stuck anyway. Last time it was my use of geospatial indexes in MongoDB :/
We provide a downloadable offline version of the site that syncs with the main web site.
Production runs on PostgreSQL and the offline version uses SQLite. Other than a very thin shim layer around database migrations all the code is the same and uses NHibernate
The unit tests run 50/50 against PostgreSQL instances and SQLite
Occasionally I have to stop myself from trying some cool new PostgreSQL specific feature. But generally everything 'just works'
11 comments
[ 3.9 ms ] story [ 34.8 ms ] threadFirst time it was a home-grown layer on top of the DBMS (MS SQL Server at the time). After an investment we've got a requirement to switch to Oracle. We anticipated that (or something like it) when designing the system, so it went smoothly.
Second time it was using MySQL from Sun workstations, with the idea of eventually switching to Oracle. That's why "almost" - we knew about the requirement in advance, so we didn't create hard dependencies on MySQL. Yet we didn't constantly test our system vs. Oracle either.
Admittedly the latest of these happened more than 10 years ago.
This usually comes with a hit to performance though. Raw queries are generally quite a bit faster. So if you dont plan on changing databases on a project, it might be beneficial to skip the ORM where possible.
The ORM also doesnt help you with migrating the records from one database type to another. So if you already have established records that is a separate issue.
Once you get into production it's a different story. A good ORM makes it easy to swap to a different DB without need for code changes. What it does not help with is the much bigger problem of how to migrate the data, indicies, backups, replication, etc. that you'd have in place for a production database.
Production runs on PostgreSQL and the offline version uses SQLite. Other than a very thin shim layer around database migrations all the code is the same and uses NHibernate
The unit tests run 50/50 against PostgreSQL instances and SQLite
Occasionally I have to stop myself from trying some cool new PostgreSQL specific feature. But generally everything 'just works'