Take the 2-minute tour ×

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?

share|improve this question
That's what ORMs are there for. –  quantumSoup Aug 22 '10 at 0:02
Sometimes ORMs do not handle the query correctly. F.ex. FULL JOIN in MySQL, RIGHT JOIN in SQLite. I mean the syntax of the query is good but the database does not support that. –  bancer Aug 23 '10 at 2:03
add comment

5 Answers

up vote 9 down vote accepted

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...

share|improve this answer
I completely agree. An abstraction layer will only ensure that your application runs equally slow on all DBMS –  a_horse_with_no_name Aug 22 '10 at 21:05
add comment

"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).

share|improve this answer
In practice, you are probably better off to pick a particular DBMS and then freely use any proprietary features that strike your fancy. Of course this isn't always possible, like if you're trying to write a package to be sold to people whose DBMS choices you can't control (or you don't want to limit your market). –  Jay Aug 22 '10 at 1:03
add comment

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.

share|improve this answer
add comment

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


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

share|improve this answer
I thought ORM is implemented in those frameworks. –  bancer Aug 22 '10 at 0:30
add comment

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.

share|improve this answer
Sometimes I can create queries with syntax that is not supported by currently used database. F.ex. FULL JOIN in MySQL. I was able to create the query that passed through DB abstraction layer and that was looking okay. But the current database did not support that syntax. –  bancer Aug 22 '10 at 0:27
add comment

Your Answer


