Portable SQL that works in SQL Server, mySQL and postgreSQL

Question

Am wondering if there are guidelines somewhere to write SQL code which is compatible in SQL Server, mySQL as well as postgreSQL. I am writing a program and the user has a choice of backend database. It will be really difficult for me to create separate queries for each and every db. I do not want to use an ORM or anything like that.

Thank you for your time.

My worry would be that you will lose out on a lot of optimisation gains (or optimisation opportunity!) if you dont cater for separate platforms. There will be things that MSSQL can do much faster in a stored procedure, than from dynamic SQL, for example - but I dont reckon MS Stored Procedure syntax is portable to much if anything else! — Kristen
– Kristen, Commented Dec 3, 2009 at 13:14

Milen A. Radev · Accepted Answer · 2009-12-03 16:52:49Z

3

Well, the best path you can choose consist in adopting the SQL ISO Standard.

Postgres and MySQL are heavy supporters, but I don't know if you could say the same about SQL Server.

Anyway, i think that an ORM solution could be the best for you, because usually they abstract these differences very well.

But if you don't wanna use it you can always store your queries in different repositories, each one for a specific database and load them at runtime.

edited Dec 3, 2009 at 16:52

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

answered Dec 3, 2009 at 13:08

Kico Lobo

4,4044 gold badges38 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

goodside Over a year ago

Just a clarification for others: The ISO standard for SQL is not the "intersection" of the common elements in the various proprietary SQLs. It's what they should be, but not what they are. No DBMS implements all of ISO SQL, and every DBMS I've ever seen has at least some non-ISO extensions. If the situation were a Venn diagram in the space of all SQL features, with each SQL dialect/standard as a circle, no circle would fit entirely inside any other.

Yada · Accepted Answer · 2009-12-03 13:21:54Z

1

Follow SQL:2003 standards.

Do unit tests when porting code from one system to another.

Don't use functions without looking how they are implemented in the dbms and what they are accepting as parameters.

Stuff to watch out for: Some SUBSTRING functions are 0-based in one system and 1-based in another. DATEDIFF works differently too. Some dbms have microseconds for datetime data type some don't.

With that being said, writing truly portable SQL is a dream... but does it really matter to be truly portable? A skilled DBA should know the little differences and port SQL codes quickly.

answered Dec 3, 2009 at 13:21

Yada

31.5k25 gold badges105 silver badges145 bronze badges

Collectives™ on Stack Overflow

Portable SQL that works in SQL Server, mySQL and postgreSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related