Database portability (sql server to mysql, postgresql)

Question

I am working on a business app (asp.net). Right now I am using sql server. But I plan to support at least mysql and postgresql down the road. What are the issues that I should consider to avoid future headaches? Especially about datatypes (column types). E.g. I think BIT column is not supported on some dbs so I use tinyint?

I mostly use plain sql (no entity framework or linq, etc) and try to keep it as simple as I can. I am NOT using things like triggers, etc. I do use stored procedures but they can be replaced with plain sql if I have to.

Answer 1

Make sure you write all your client code using the abstract IDbConnection, IDbCommand, IDataReader instead of the concrete. You will also have to keep your SQL statements in check all the time to ensure you use only compatible syntax.

You can also try connecting via the OdbcConnection/OdbcCommand components and use generic ODBC syntax and generic ODBC data types (ie. the {fn SUBSTRING(...)} stuff, aka. the ODBC Escaped Syntax).

As an alternative what I prefer to do is to isolate the data access and create specific DAL classes for each back end. I use XML and XSLT to generate the DAL code. Similar to this the technique of integrating XSLT code generation from my blog, but with XSLTs geared specifically for each back-end specific code.

Answer 2

Your only hope is to separate data access into a proper data access layer, as Remus Rusanu suggests. The data access layer can have one consistent interface to the rest of your code, and be changed out for other versions for each DB platform. Keeping the SQL fairly standard will help, but it's not really possible to write one body of SQL code and have it work everywhere (the SQL standard isn't that well implemented.)

Answer 3

Consider (with some costs in term of learning curve) the adoption of a Domain Model and a data access layer based on an OR/M like NHibernate (https://www.hibernate.org/343.html)