Writing Portable SQL

Concept

Perfect portability is not a goal worth chasing - it costs readability and rules out useful features. The practical aim is different: use the standard form when there is one, and confine the rest to a small, known set of places.

Safe almost everywhere

-- Core querying
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
INNER / LEFT / RIGHT JOIN, subqueries, EXISTS, IN, UNION

-- Standard scalar functions
COALESCE(a, b)
NULLIF(a, b)
CAST(x AS type)
CASE WHEN ... THEN ... ELSE ... END
EXTRACT(YEAR FROM date_col)
CURRENT_DATE, CURRENT_TIMESTAMP
CHAR_LENGTH(s), UPPER(s), LOWER(s), TRIM(s), SUBSTRING(s FROM p FOR n)

-- Aggregates
COUNT, SUM, AVG, MIN, MAX

-- DDL and constraints
CREATE TABLE, PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT

-- Transactions
COMMIT, ROLLBACK, SAVEPOINT

-- Modern SQL, in every current major version
WITH, WITH RECURSIVE, window functions with OVER

Never portable - isolate these

Practical rules

1. Avoid quoted identifiers entirely

-- Needs different quoting per product
SELECT "order", "user" FROM "group";

-- Needs none, anywhere
SELECT order_id, user_id FROM user_groups;

Plain lower case snake_case names that avoid reserved words are portable by construction. This single habit removes an entire category of porting work.

2. Prefer the standard function

COALESCE(a, b)                 -- not IFNULL, ISNULL or NVL
CAST(x AS CHAR)                -- not CONVERT with product specific style codes
EXTRACT(YEAR FROM order_date)  -- not YEAR() or DATEPART()
CHAR_LENGTH(s)                 -- not LEN() or MySQL's byte counting LENGTH()

3. Be explicit where defaults differ

-- NULL ordering differs; say what you want
ORDER BY location ASC NULLS LAST;      -- PostgreSQL, Oracle, SQLite

-- MySQL has no NULLS LAST; emulate it
ORDER BY (location IS NULL), location ASC;

-- Decimal division, whatever the integer division rule is
SELECT total * 1.0 / NULLIF(quantity, 0) AS unit_price FROM order_items;

4. Put the non portable parts in one layer

Every application already has a data access layer. Make it the only place product specific SQL appears - the paging clause, the identity read, the upsert. Then a port is a known, bounded piece of work instead of a search through the whole codebase.

5. Do not fake portability with an ORM

An ORM hides dialect differences for simple CRUD and stops helping the moment you need a window function, a recursive CTE or an index hint. Treat it as a convenience, not a portability guarantee, and test the raw queries against the real target.

How much portability do you need?

Important rules

• Standard SQL is a baseline, not the whole language; every product needs its extensions eventually.
• Behavioural differences - isolation level, empty string, GROUP BY strictness - matter more than syntax differences.
• Version is part of the target: "PostgreSQL" is not a specification, "PostgreSQL 15" is.
• Portable SQL is often slower, because it cannot use product specific optimisations.

Common mistakes

• Writing deliberately portable SQL for an application that will only ever run on one database.
• Assuming an ORM makes the application portable.
• Testing on SQLite and deploying on PostgreSQL.
• Scattering paging syntax through fifty files.
• Using reserved words as identifiers and needing product specific quoting forever.

Best practices

• Name the target product and version in the project README, and say it in code review.
• Use snake_case identifiers that never need quoting.
• Prefer standard functions when the cost is zero.
• Confine paging, identity and upsert to the data access layer.
• Run the test suite against the production database product and version.

Practice

1. Rewrite SELECT IFNULL(email, 'none') FROM employees LIMIT 10 in the most portable form you can, and say what still cannot be made portable.
2. Write a MySQL ORDER BY that puts NULLs last without NULLS LAST.
3. List the files in a project you know that would need changing to switch database products.