Developing cross-DB applications

Question

So far I've always coded using raw/embedded, dialectic SQL. This seems obviously wrong to me because it ties you to concrete technologies so your software can't be deployed to any machine - especially servers.

In general (independently of the programming language and RDBMS), what options does one have in this case? How good are each one of them? What would you say is the standard approach?

Answer 1

Generally what you should be looking to do is abstract your database code into its own layer (assembly, dll or whatever).

In your application code you then call the interface which "behind the scenes" works out which database code you need and calls that to do the work. Depending on how your application is configured this could be either something decided at compile time, install time or run time.

A key technology to look up is Inversion of Control or Dependency Injection. This will allow you to exercise this control over your application and decide which version of the code you need.

Answer 2

Frankly as a database person, using the specific flavor of code for your database backend is the most efficient code you can write. In my 30 years of working with databases, we have never changed the database backend for an application and the only situations where it seems necessary are when you start with a poor choice to begin with (such as Access when you need a large database) or if you sell Commercial off-the-Shelf (COTS) software that needs to support whatever backend the client has. Sacrificing performance today in the idea that someday you might need to change backends is not a good way to go. There is a lot more to changing the backend of a database after it has many records than just changing the application code and the smart dba prefers not to take the risk.

Answer 3

ORM. http://en.wikipedia.org/wiki/Object-relational_mapping

Works really, really well for divorcing an application from SQL.

Answer 4

(This answer has to do with the SQL statements themselves, and not with how they're deployed.)

I don't think I'd call it standard, but the most common approach I've seen is to use the "lowest common denominator" subset of SQL features that all your target platforms support. For example, if one of your target platforms doesn't support common table expressions, then you never use common table expressions.

That approach trades off some SQL flexibility and some performance for deployment flexibility.

Some SQL variations can be masked by macro substitution at build time. A macro might expand to a DATE_ADD() function on one platform, and to a datetime value + an interval value on another. This can get you closer to optimal performance on all your target platforms for a slightly larger subset of SQL language features, but it can be a real pain to maintain.

The brute-force approach is to maintain a set of platform-specific differences for all your SQL statements. Simple statements like SELECT * FROM TABLENAME can be shared on all platforms; more complex statements that leverage platform-specific features or syntax have different versions for each platform. In my experience, this approach requires a lot of automated testing to make sure all platforms behave the same.

I haven't thought much about it, but I think that the more platform-agnostic you want to be, the more likely you are to end up near the brute-force approach. How hard is that? Well, the most likely candidates are database CASE tools and database schema utilities, and there aren't very many of them that target more than a handful of current platforms. I reckon it's pretty danged hard.

Answer 5

Others have suggested abstracting out the database layer, this is fine, but in the end you will have different database code for different database platforms, especially if the database code is using specific database platform technologies. This can be maintainable, but if the application has 400 tables and 1000 stored procs on Oracle and you need to port that SQL Server, its a big mountain to climb.

An alternative approach is to write one SQL statement that will run on all the DB platforms. SQL itself is standardized (sq-89, sql-92, sql3) etc. If all the platforms support sql-92, make sure the data, queries, and updates use sql-92 compliant code. Then the abstracted data layer runs one set of queries, regardless of DB platform.

If doing this approach, some sacrifices will have to be made and you may have more application code to support the data layer. Some databases don't support stored procedures etc. but the sql that is sent in and out should be universally accepted, then it is cross DB.

Answer 6

I've worked on a large application that switched databases (from SQL Server to MySQL). There's a lot I learned in that time. I think that if you want to develop a cross database application you have to have a few things in mind. First, have a list of supported databases. You won't have the resources to develop and test against all databases. Second, either use an ORM that supports all the databases you are targeting, or (and this might be preferrable) stick to basic SQL that works across all your targeted databases. Only use different SQL for each database if you have a performance issue. Depending on the application, you can get pretty far with "standard" Select, Join, Insert, Update, and Delete statements.