How do you write your applications to be database independent?

Question

My boss asks me to write only ANSI SQL to make it database independent. But I learned that it is not that easy as no database fully ANSI SQL compatible. SQL code can rarely be ported between database systems without modifications.

I saw people do different way to make their program database independent. For example:

1. Externalize SQL statements to resource files.
2. Write many providers class to support different database.
3. Write only simple SQL, and keep away from advance functions/joins.

Do you always write your code "any database ready"? Or do it only if needed? If yes, how do you achieve it?

Answer 1

To decouple the database engine from your application, use a database abstraction layer (also data access layer, or DAL). You didn't mention what language you use, but there are good database abstraction libraries for all the major languages.

However, by avoiding database-specific optimizations you will be missing out on the advantages of your particular brand. I usually abstract what's possible and use what's available. Changing database engines is a major decision and doesn't happen often, and it's best to use the tools you have available to the max.