Tips on writing SQL for multiple databases

Question

Different databases have differences in SQL support & implementation. Sometimes there is a difference in SQL syntax, sometimes support for some SQL commands is missing, sometimes the database has a feature that other databases do not have.

What are considered to be good practices in writing SQL queries that are good for different databases (MySQL, PostgreSQL, Oracle, MSSQL, SQLite) taking in account that the developer uses a framework (like CakePHP, Codeigniter, Zend etc.) that provides a database abstraction layer? What SQL syntax should the developer try to avoid?

Answer 1

You can use an ORM for example which will essentially abstract away the details from each database. Although you have to make sure your ORM supports all the databases needed.

Doctrine and Propel are good friends of php. Check either out.

If you cannot find an ORM that supports all your DB's then perhaps find one that covers most and extend php to handle the last. Although I doubt this will be the case.

Answer 2

You are looking for Object-relational mapping (ORM).

Object-relational mapping (ORM, O/RM, and O/R mapping) in computer software is a programming technique for converting data between incompatible type systems in object-oriented programming languages. This creates, in effect, a "virtual object database" that can be used from within the programming language.

You can go for famous Doctrine out there. Also have a look at:

• Getting Started with ORM in PHP

Answer 3

As quantumSoup alluded to, just don't. If you take a look at each of the frameworks you listed, you'll notice that they all use ORM or some kind of database abstraction layer for inserting/extracting data. This allows you to write db-neutral code that works irrespective to the desired data source. The ORM then uses the correct data source "drivers" to convert your intentions into commands understood by each data source.

So the trick is 1.) defining a universal interface for your ORM or database abstraction layer; and then 2.) writing the appropriate drivers for the ORM. Then each time you want to use a new type of data source (including flat files or CSV), it's a simple matter of adding a new driver.

Answer 4

Then you dig into using ORM, you'll find that for complex queries - it doesn't perform. It's hard enough for people to write SQL that performs well - I don't expect a DB abstraction layer fair any better. Most ORMs support native stored procedures... which defeats the purpose of using ORM.

ANSI SQL is striving to make SQL more portable amongst databases, but adoption varies from vendor to vendor. And ANSI syntax doesn't necessarily mean it performs as well as native syntax (IE: COALESCE vs native ISNULL/IFNULL/NVL/etc).

The reality is for getting the best performing database interaction, you need to write custom code for each vendor involved. Some would use this as a point to why the database should be nothing more than basic persistence because it's easier to maintain a central application. But this pales when you deal with high usage applications, who suffer because of multiple trips between the application and the database, poor data typing and table design. Frankly, it's a waste of a database...

Answer 5

"Cautiously use ANSI SQL" is the most direct answer to your question.

However, keep in mind these words from Jeremy Zawodny, especially:

Good engineers try to select the best tools for the job and then do everything they can to take advantage of their tool's unique and most powerful features. In the database world, that means specific hints, indexing, data types, and even table structure decisions. If you truly limit yourself to the subset of features that is common across all major RDBMSes, you're doing yourself and your clients a huge disservice.

What people are really looking for with ORM is a non-relational data store that can be easily transformed into programming language data structures (e.g. Ruby objects). If you need this, you might want to examine one of the many "NoSQL" options out there (MongoDB, CouchDB are two of the more mature ones).