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?

Rosamondrosamund

possible duplicate of Database design for database-agnostic applications – Cranky

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.

Quinquefid

How do you do a DAL, without fully test it on another database? e.g., some database may have non-common behavior on locks, transactions and exception that could break everything. I mean, apps should be tested even when a version upgrade on database? Is it possible that "write once, run every DB"? – Rosamondrosamund

The point of a DAL is to seperate the actual database calls from the interface. You define a generic query/escape/insert/update/delete etc. interface, and implement different adapters for different db brands. All the major db abstraction layers come with support for multiple brands built in, – Quinquefid

and you can implement more if you need it. If the rest of your application uses the DAL interface, it is not coupled to a specific database brand (except for database specific syntax, which sometimes can be emulated as well). – Quinquefid

Answer 2

You could use one of the many Object/Relational Mapper tools, like Hibernate/NHibernate, LLBLGen, and so forth. That can get you a long way to database portability. No matter what you do, you need to have some abstraction layer between your model and the rest of your code. This doesn't mean you need some sort of dependency injection infrastructure, but good OO design can get you pretty far. Also, sticking with plain SQL and thinking that will get you portability is rather naive. That would be true if your application was trivial and only used very trivial queries.

As for always writing an application to be "any database ready," I usually use some sort of abstraction layer so it is not hard to move from one database system to another. However, in many circumstances, this is not required, you are developing for the Oracle platform or SQL Server or MySQL whatever so you shouldn't sacrifice the benefits of your chosen RDBMS just for the possibility of an entirely seamless transition. Nevertheless, if you build a good abstraction layer, even targeting a specific RDBMS won't necessarily be too difficult to migrate to a different RDBMS.

Alack

Answer 3

Tell your boss to mind his own business. No, of course one can't say such things to one's boss, but stay tuned.

What's interesting is what business value is supposed to be supported by this requirement. One obvious candidate seems to be that the database code should be ready for working on other database engines than the current. If that's the case then that's what should be stated in the requirement.

From there it's up to you as an engineer to figure out the different ways to achieve that. One might be writing ANSI SQL. One might be using a database abstraction layer.

Further it's your responsibility to inform your boss what the costs of the different alternatives are (in terms of performance, speed of development, etcetera).

"Write ANSI SQL"... gah!

Riproaring

It sounds like classic 'ex-technical boss' behaviour. I had to make a database-agnostic app once at orders of a similar sounding boss. Awful idea as it turned out. – Leontineleontyne

Yeah, sometimes it's an ex-technical boss. But often it's just a lack of understanding from us technicians that it's our responsibility to dig behind such requirements and expose the cost of the different choices in a way that the suits can digest and make good business decisions. – Riproaring

And, yes, I've too spent lots of energy and creativity in keeping systems database agnostic and it has never, ever been a good idea. – Riproaring

Answer 4

Just for the record. There is a similar question here on Stackoverflow:

Database design for database-agnostic applications

Veator

Answer 5

Being 100% compliant to ANSI SQL is a difficult goal to meet, and yet it doesn't guarantee portability anyway. So it's an artificial goal.

Presumably your boss is asking for this in order to make it easy and quick to switch database brands for some hypothetical purpose in the future (which actually may never come). But he's trading that future efficiency for a greater amount of work now, since it's harder to make the code database-neutral.^*

So if you can phrase the problem in terms of the goals your manager should be focusing on, like finishing the current project phase on time and on budget, it may be more effective than just telling him it's too difficult to make the code database-neutral.

There is a scenario when you need to make truly database-neutral code, that is when you are developing a shrink-wrap application that is required to support multiple brands of database.

Anyway, even if you currently support only one brand, there are certainly cases where you have a choice to code some SQL using proprietary features, but there also exists a more portable way to achieve the same result. You can treat these as "low-hanging fruit" cases, and you can make it easier to port the code in the future if the need arises. But don't limit yourself either, use proprietary solutions if they give good value. Perhaps add a note in the comments that this deserves review if/when you need to make a port.

---

^* I prefer the word "neutral" instead of "agnostic" when talking about platform-independence. It avoids the religious overtone. :-)

Gastrotrich