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.
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).
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.